introduction to sql new
DESCRIPTION
TRANSCRIPT
STRUCTURE QUERY LANGUAGEPREPARED BY
Asst.Prof.Santosh Kumar Rath
LAB-1
What is SQL?
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard
What Can SQL do?
SQL can execute queries against a database
SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a
database SQL can create views in a database SQL can set permissions on tables,
procedures, and views
SQL Data Types:
Byte: Allows whole numbers from 0 to 255 1 byte
Integer: Allows whole numbers between -32,768 and 32,767 2 bytes
Long: Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes
char (size): Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
varchar (size): Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters
SQL Data Types:
nchar: Fixed width Unicode string. Maximum 4,000 characters Defined width x 2
nvarchar : Variable width Unicode string. Maximum 4,000 characters
numeric(p,s): Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1.
The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.
date :Store a date only. From January 1, 0001 to December 31, 9999(3 bytes required)
Types of SQL Statements
The tables in the following sections provide a functional summary of SQL statements and are divided into these categories:
Data Definition Language (DDL) Statements
Data Manipulation Language (DML) Statements
Transaction Control Statements(TCL)Data Control Language(DCL)
SQL COMMANDSSQL
DDL
CREATE,ALTER,DROP
DML
INSERT,DELETE,UPDATE
DCL
GRANT,REVOKE
DQL
SELECT
TCLCOMMIT,ROLLBACK,SAVEPOIN
T
Data Definition Language (DDL)
Data definition language (DDL) statements let you to perform these tasks:
Create, alter, and drop schema objects
Data MANIPULATION Language (DML)
Data manipulation language (DML) Statements access and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction. The data manipulation language statements are:
delete, insert, update, select.
Data Control Statements(DCL)
A data control language (DCL) is syntax similar to a computer programming language used to control access to data stored in a database. In particular, it is a component of Structured Query Language (SQL).
Grant, Revoke, Truncate
TRANSACTION CONTROL Statements(TCL)
Transaction control statements manage changes made by DML statements. The transaction control statements are:
Commit, Rollback, Save point
LAB-2
CREATE COMMAND (DDL)
Syntax: CREATE TABLE TABLE_NAME(COL_NAME1 DATATYPE(SIZE),COL_NAME2 DATATYPE(SIZE),COL_NAME3 DATATYPE(SIZE),…………………………………………..)
EXAMPLE: TABLE NAME(STUDENT): CREATE TABLE STUDENT( ROLLNO NUMBER(10),NAME VARCHAR(25),AGE NUMBER(5), MARK NUMBER(5), ADDRESS VARCHAR(20))
INSERT COMMAND (DML)
Syntax: INSERT INTO TABLE_NAME VALUES( ‘COL_NAME1’,’COL_NAME2’,’COL_NAME3’, ‘COL_NAME4’,……………..‘COL_NAMEn’)
EXAMPLE: INSERT INTO STUDENT VALUES(100,’SATYA’,20, 80,’BANGALORE’)
SELECT COMMAND (DDL)
RETRIEVET THE RECORD FROM TABLE
SELECT * FROM TABLE_NANME
EXAMPLE: SELECT * FROM STUDENT
Q1. CREATE TABLE CLIENT_MASTER FIELDS ARE CLIENT_NO,NAME,ADDRESS,CITY,PINCODE,STATE,BAL_DUE
Q1.(a) INSERT 10 RECORDS INTO THE TABLE.
Q1.(b) Retrieve the entire contents of the client_master table
QUESTIONS
Q2. CREATE TABLE PRODUCT_MASTER FIELDS ARE PRODUCT_ID,DESCRIPTION, PROFITPECENT,QUANTITY, SELLPRICE,COSTPRICE
Q2.(A) INSERT 10 RECORDS INTO THE TABLE.
Q3.(B).RETRIEVE THE ENTIRE CONTENTS OF THE PRODUCT_MASTER TABLE
LAB-3
ALTER COMMAND •ADD COMMAND
•MODIFY COMMAND
•DROP COMMAND
ALTER COMMAND(DDL)
The ALTER TABLE Statement : The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
To add a column in a table, use the following syntax:
ALTER TABLE table_name ADD (column_name datatype(SIZE))
EXAMPLE: ALTER TABLE CLIENT_MASTER ADD(TELPHONE NUMBER(10))
ALTER DROP COMMAND
To delete a column in a table, use the following
Syntax :ALTER TABLE table_name DROP (column_name)
EXAMPLE: ALTER TABLE CLIENT_MASTER DROP(TELPHONE)
ALTER MODIFY COMMAND
To MODIFY column datatype and size in a table, use the following syntax
ALTER TABLE table_name MODIFY (column_name DATATYPE(SIZE))
EXAMPLE: ALTER TABLE CLIENT_MASTER MODIFY(TELPHONE NUMBER(20))
LAB-3 CONTINUE………
UPDTAE COMMAND DELETE COMMAND DROP COMMAND RENAME COMMAND
UPDATE COMMAND (DML)
The SQL UPDATE Statement :The UPDATE statement is used to update existing records in a table.
Syntax: UPDATE table_nameSET column1=value1,column2=value2,...WHERE some_column=some_value
EXAMPLE: UPDATE CLIENT_MASTER SET TELEPHONE=98657859 WHERE CLIENT_ID=‘C001’
DELETE COMMAND (DML)
The SQL DELETE Statement : The DELETE statement is used to delete rows in a table.
SQL DELETE Syntax DELETE FROM table_name
WHERE some_column=some_value;
EXAMPLE: DELETE FROM CLIENT_MASTER WHERE CLIENT_ID=‘C005’
DROP COMMAND (DDL)
The DROP TABLE Statement: The DROP TABLE statement is used to delete a table.
DROP TABLE table_name
EXAMPLE : DROP TABLE CLIENT_MASTER
RENAME COMMAND
RENAME TABLE TableName1 TO TableName2 Parameters
TableName1 Specifies the name of the table to be renamed.
TableName2 Specifies the new name of the table.
EXAMPLE: RENAME CLIENT_MASTER TO CLI_MASTER
CLAUSES USED IN SQL
WHERE CLAUSEDISTINCT CLAUSEORDER BY CLAUSEGROUP BY CLASUEHAVING CLAUSE
WHERE CLAUSE
The SQL WHERE Clause : The WHERE clause is used to extract only those records that fulfill a specified criterion.
SQL WHERE Syntax:SELECT column_name,column_nameFROM table_nameWHERE column_name operator value;
EXAMPLE: SELECT * FROM CLIENT_MASTERWHERE CLIENT_ID=‘C002’;
DISTINCT CLAUSE
The SQL SELECT DISTINCT Statement: In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values.
The DISTINCT keyword can be used to return only distinct (different) values.
SQL SELECT DISTINCT Syntax: SELECT DISTINCT column_name,
column_name FROM table_name;
ORDER BY CLAUSE
The SQL ORDER BY Keyword: The ORDER BY keyword is used to sort the result-set by one or more columns.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.
SQL ORDER BY Syntax:SELECT column_name,column_nameFROM table_name ORDER BY olumn_name,column_name ASC|DESC;
GROUP BY CLAUSE
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
SQL GROUP BY Syntax SELECT column_name,
aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name;
Example: SELECT Shippers.ShipperName FROM Orders GROUP BY ShipperName;
HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SQL HAVING Syntax: SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value;
SELECT Employees.LastName, Employees ON Orders.EmployeeID=Employees.EmployeeID) GROUP BY LastName HAVING Orders.OrderID > 10;
SQL PRIMARY KEY CONSTRAINTS
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.
SYNTAX: CREATE TABLE TABLE_NAME(CPLNAME DATATYPE(SIZE)PRIMARY KEY,COLNAME2 DATATYPE(SIZE)…….COLNAMEn DATATYPE(SIZE))
EXAMPLE OF PRIMARY KEY
EXAMPLE: CREATE TABLE Persons(P_Id int NOT NULL PRIMARY KEY,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255) )
SQL FOREIGN KEY CONSTRAINTS
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
SYNATX: CREATE TABLE TAB_NAME(COLNAME DATATYPE(SIZE) PRIMARY KEY,COLNAME2 DATATYPE(SIZE),……FOREIGNKEY(COLNAME) REFERENCES TABLENAME(COLNAME))
EXAMPLE:CREATE TABLE Orders( O_Id int NOT NULL, OrderNo int NOT NULL,P_Id int, PRIMARY KEY (O_Id),FOREIGN KEY (P_Id) REFERENCES Persons(P_Id))
FUNCTION IN SQL
AGGREGATE FUNCTIONNUMERIC FUNCTIONSTRING FUNCTION
AGGREGATE FUNCTION
Aggregate functions perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.
MAX: The MAX() function returns the largest value of the selected column.
SELECT MAX(column_name) FROM table_name;
MIN: The MIN() function returns the smallest value of the selected column.
SELECT MIN(column_name) FROM table_name;
AGGREGATE FUNCTION CONT.. AVG: The AVG() function returns the average
value of a numeric column: SELECT AVG(column_name) FROM table_name
SUM: The SUM() function returns the total sum of a numeric column.
SELECT SUM(column_name) FROM table_name;
COUNT: The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:
SELECT COUNT(column_name) FROM table_name;
NUMERIC FUNCTION
ABS(): Returns the absolute value of numeric
expression. ABS() Syntax: SELECT ABS(NO) FROM DUAL EXAMPLE: SELECT ABS(-12) FROM DUAL
CEIL(): Returns the smallest integer value that is not less than passed numeric expression
CEIL() Syntax: SELECT CEIL(NO) FROM DUAL EXAMPLE: SELECT CEIL(13.5) FROM DUAL
COS(): Returns the cosine of passed numeric expression. The numeric expression should be expressed in radians.
SELECT COS(NO) FROM DUAL EXAMPLE: SELECT COS(30) FROM DUAL
NUMERIC FUNCTION
EXP(): Returns the base of the natural logarithm (e) raised to the power of passed numeric expression.
SELECT EXP(NO) FROM DUAL EXAMPLE: SELECT EXP(2) FROM DUAL
FLOOR(): Returns the largest integer value that is not greater than passed numeric expression.
SELECT FLOOR(NO) FROM DUAL EXAMPLE: SELECT FLOOR(13.5) FROM DUAL LOG(): Returns the natural logarithm of the passed
numeric expression. SELECT LOG(NO) FROM DUAL EXAMPLE: SELECT LOG(10) FROM DUAL
NUMERIC FUNCTION
MOD(): Returns the remainder of one expression by diving by another expression.
SELECT MOD(NO) FROM DUAL EXAMPLE: SELECT MOD(10) FROM DUAL ROUND(): Returns numeric expression rounded
to an integer. Can be used to round an expression to a number of decimal points
SELECT ROUND(NO) FROM DUAL EXAMPLE: SELECT ROUND(10.19) FROM DUAL
POW(): Returns the value of one expression raised to the power of another expression
SELECT POW(NO,NO) FROM DUAL EXAMPLE: SELECT POW(10,2) FROM DUAL
STRING FUNCTION
LOWER() Returns the argument in lowercase SELECT LOWER(STRING) FROM DUAL EXAMPLE: SELECT LOWER(santosh) FROM
DUAL UPPER() Converts to uppercase SELECT UPPER(STRING) FROM DUAL EXAMPLE: SELECT UPPER(COMPUTER)
FROM DUAL
INITCAP() The first letter of the string is Capital
SELECT INITCAP(STRING) FROM DUAL EXAMPLE: SELECT INITCAP(santosh) FROM
DUAL
STRING FUNCTION
LENGTH() Returns the length of a string in bytes
SELECT LENGTH(STRING) FROM DUAL EXAMPLE: SELECT LENGTH(COMPUTER)
FROM DUAL
LPAD() Returns the string argument, left-padded with the specified string
SELECT LPAD(STRING) FROM DUAL EXAMPLE: SELECT LPAD('hi',4,'??');
FROM DUAL
STRING FUNCTION
RPAD() Appends string the specified number of times
SELECT RPAD(STRING) FROM DUAL EXAMPLE: SELECT RPAD('hi',4,’*');
FROM DUAL LTRIM() Removes leading spaces SELECT LTRIM(' STRING') FROM DUAL; EXAMPLE:SELECT LTRIM(‘SANTOSH’,3)
FROM DUAL; RTRIM() Removes trailing spaces SELECT RTRIM(' STRING') FROM DUAL; EXAMPLE:SELECT
RTRIM(‘SANTOSH’,3) FROM DUAL;
OPERATORS IN SQL
SQL AND & OR Operators : The AND & OR operators are used to filter records based on more than one condition. The AND operator displays a record if both the first condition AND the second condition are true. The OR operator displays a record if either the first condition OR the second condition is true.
AND Operator Example The following SQL statement selects all
customers from the country "Germany" AND the city "Berlin", in the "Customers" table:
Example : SELECT * FROM Customers WHERE Country='Germany‘ AND City='Berlin';
OR-OPERATOR
The following SQL statement selects
all customers from the city "Berlin" OR "München", in the "Customers" table
Example: SELECT * FROM CustomersWHERE City='Berlin‘ OR City='München';
OPERATORS IN SQL
The IN Operator:The IN operator allows you to specify multiple values in a WHERE clause.
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);
IN Operator Example: The following SQL statement selects all customers with a City of "Paris" or "London":
Example: SELECT * FROM Customers WHERE City IN ('Paris','London');
BETWEEN OPERATOR
The SQL BETWEEN Operator:The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.
SELECT column_name(s)FROM table_name WHERE column_name BETWEEN value1 AND value2;
OPERATORS IN SQL
NOT BETWEEN Operator Example :To display the products outside the range of the previous example, use NOT BETWEEN:
Example: SELECT * FROM ProductsWHERE Price NOT BETWEEN 10 AND 20;
The following SQL statement selects all products with a Product Name beginning with any of the letter BETWEEN 'C' and 'M':
Example : SELECT * FROM ProductsWHERE ProductName BETWEEN 'C' AND 'M';
OPERATOR IN SQL
LIKE The LIKE operator is used to compare a value to similar values using wildcard operators.
The SQL LIKE Operator : The LIKE operator is used to search for a specified pattern in a column.
SQL LIKE Syntax : SELECT column_name(s)FROM table_nameWHERE column_name LIKE pattern;
Example : SELECT * FROM Customers WHERE City LIKE ‘S%';