Structured Query Language

Data Definition Language: Data definition language is (DDL) is used to create, update and destroy database, database objects and structure or schema . These commands primarily used by the database administrator.

Commands of the DDL are CREATE, ALTER, DROP the table.

Data Manipulation Language: Data manipulation language is used to insert, delete and update the data in data base. This is the second label SQL command language.

Command under the DML are INSER, UPDATE, and DELETE the data.

Data Query Language: Data query language is used to retrieve the data from different tables of data base.

SELECT is the command of the DQL.

Data Control Language: Data control language is used to control access to the data in a data base.

Commands of the DCL are GRANT, REWOKE.

Transaction Control Language: Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT, SAVEPOINT, ROLLBACK, SET TRANSECTION are the commands of TCL.

EMP_Join

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
1	Narayan	Singh	02/11/84	04/01/08	1001
2	Vedu	Gopal	09/02/80	12/01/09	1002
3	Meera	Nayar	03/10/79	05/01/09	1003
4	Ali	Ahamad	08/28/85	09/01/10	1004
5	Usni	Mubarak	03/25/74	02/02/09	1005

EMP_Address

Emp_ID	Address	City	State	Contact_Number
1001	HN 22, Near badh ghar	Tiruantpuram,	Kerala	55555555
1002	133, home sweet	Jaipur	Rajasthan	1111111111
1003	9/122 Hello colony	Bhiwani	Haryana	333333333
1004	Ram colony	Lucknow	Uttar Pradesh	888888888
1005	New defense, colony	Allahabad	Uttar Pradesh	555555555

EMP_ProjectDetails

EMP_ID	Project_ID	Project_Neme
1001	X001	Google
1002	X002	Yahoo
1003	X003	Facebook
1004	X004	iPhone
1005	X005	ibibo

EMP_SalaryDetail

Project_ID	Basic	TA	DA	PF	TDS
X001	20000	10000	1500	100	500
X002	15000	10000	1090	520	500
X003	35000	1000	10050	230	500
X004	8000	1500	10045	400	500
X005	250000	1250	100	1000	500

Note: SQL is not case sensitive. SELECT is the same as select.

DDL:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML:

• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DQL:

• SELECT - retrieve data from the a database

DCL:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL:

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

CREATE table EMP_Join (SN number(3) NOT NULL, F_name varchar(20), L_name varchar(20), DateOfBirth DATE, DateOfJoinee DATE);

Output:

EMP_Join

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee

ALTER TABLE EMP_Join ADD emp_id int NOT NULL AUTO_INCREMENT PRIMARY KEY;

output:

EMP_Join

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID

ALTER TABLE EMP_Join DROP COLUMN Emp_ID;

output:

EMP_Join

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee

TRUNKATE TABLE EMP_Join;

Output:

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID

INSERT INTO EMP_Join (SN, F_Name, L_Name, DateOfBirth, DateOfJoinee, Emp_ID) VALUES (1, 'Narayan', 'Singh', '02/11/84', '04/01/08', 1001);

or

INSERT INTO EMP_Join VALUES (1, 'Narayan', 'Singh', '02/11/84', '04/01/08', 1001);

Output:

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
1	Narayan	Singh	02/11/84	04/01/08	1001

UPDATE EMP_Join SET L_Name='Sharma' WHERE SN=1;

Output:

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
1	Narayan	Sharma	02/11/84	04/01/08	1001

DELETE FROM EMP_Join WHERE Emp_ID=1001 AND Emp_ID=1003;

Output:

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
2	Vedu	Gopal	09/02/80	12/01/09	1002
4	Ali	Ahamad	08/28/85	09/01/10	1004
5	Usni	Mubarak	03/25/74	02/02/09	1005

DQL:

Table 1.1 EMP_Join

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
1	Narayan	Singh	02/11/84	04/01/08	1001
2	Vedu	Gopal	09/02/80	12/01/09	1002
3	Meera	Nayar	03/10/79	05/01/09	1003
4	Ali	Ahamad	08/28/85	09/01/10	1004
5	Usni	Mubarak	03/25/74	02/02/09	1005

Table 1.2 EMP_Address

Emp_ID	Address	City	State	Contact_Number
1001	HN 22, Near badh ghar	Tiruantpuram,	Kerala	55555555
1002	133, home sweet	Jaipur	Rajasthan	1111111111
1003	9/122 Hello colony	Bhiwani	Haryana	333333333
1004	Ram colony	Lucknow	Uttar Pradesh	888888888
1005	New defense, colony	Allahabad	Uttar Pradesh	555555555

Table 1.3 EMP_ProjectDetails

EMP_ID	Project_ID	Project_Neme
1001	X001	Google
1002	X002	Yahoo
1003	X003	Facebook
1004	X004	iPhone
1005	X005	ibibo

Table 1.4 EMP_SalaryDetail

Project_ID	Basic	TA	DA	PF	TDS
X001	20000	10000	1500	100	500
X002	15000	10000	1090	520	500
X003	35000	1000	10050	520	500
X004	8000	1500	10045	400	500
X005	250000	1250	100	1000	500

1. SELECT Statement:

The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set.
Select (asterisk) means user want to select all the columns of the table.

Source table, EMP_Join

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
1	Narayan	Singh	02/11/84	04/01/08	1001
2	Vedu	Gopal	09/02/80	12/01/09	1002
3	Meera	Nayar	03/10/79	05/01/09	1003
4	Ali	Ahamad	08/28/85	09/01/10	1004
5	Usni	Mubarak	03/25/74	02/02/09	1005

SELECT * FROM EMP_Join;

The result set will be,

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
1	Narayan	Singh	02/11/84	04/01/08	1001
2	Vedu	Gopal	09/02/80	12/01/09	1002
3	Meera	Nayar	03/10/79	05/01/09	1003
4	Ali	Ahamad	08/28/85	09/01/10	1004
5	Usni	Mubarak	03/25/74	02/02/09	1005

 

2. SELECT DISTINCT Statement:

Distinct statement is used to ignore the duplicate data of column. It returns the different data in result set.

Source table, EMP_Address

Emp_ID	Address	City	State	Contact_Number
1001	HN 22, Near badh ghar	Tiruantpuram,	Kerala	98555555
1002	133, home sweet	Jaipur	Rajasthan	9911111111
1003	9/122 Hello colony	Bhiwani	Haryana	733333333
1004	Ram colony	Lucknow	Uttar Pradesh	888888888
1005	New defense, colony	Allahabad	Uttar Pradesh	415555555

SELECT DISTINCT State FROM EMP_Address;

The result set will be

State

Kerala

Rajasthan

Haryana

Uttar Pradesh

 

3. SQL WHERE Clause:

The WHERE clause is used to extract only those records that fulfill a specified condition.

Source table, EMP_Address

Emp_ID	Address	City	State	Contact_Number
1001	HN 22, Near badh ghar	Tiruantpuram,	Kerala	98555555
1002	133, home sweet	Jaipur	Rajasthan	9911111111
1003	9/122 Hello colony	Bhiwani	Haryana	733333333
1004	Ram colony	Lucknow	Uttar Pradesh	888888888
1005	New defense, colony	Allahabad	Uttar Pradesh	415555555

SELECT Emp_ID, City FROM EMP_Address WHERE State=’Uttar Pradesh’;

The result set will be

Emp_ID	City
1004	Lucknow
1005	Allahabad

 

4. The AND & OR Operators:

The AND operator displays a record if both the first condition and the second condition is true.
The OR operator displays a record if either the first condition or the second condition is true.

 

Source table, EMP_SalaryDetails

Project_ID	Basic	TA	DA	PF	TDS
X001	20000	10000	1500	100	500
X002	15000	10000	1090	520	500
X003	35000	1000	10050	520	500
X004	8000	1500	10045	400	500
X005	250000	1250	100	1000	500

SELECT Project_ID, Basic, TDS FROM EMP_SalaryDetails WHERE DA=1090 AND PF=520;

Project_ID	Basic	TDS
X002	15000	500

SELECT Project_ID, Basic, TDS FROM EMP_SalaryDetails WHERE DA=1500 OR PF=520;

Project_ID	Basic	TDS
X001	20000	500
X002	15000	500
X003	35000	500

SELECT * FROM EMP_SalaryDetail WHERE PF=520 AND (20000 OR 35000);

Project_ID	Basic	TA	DA	PF	TDS
X003	35000	1000	10050	520	500

 

5. The ORDER BY Keyword:

Ordered by keyword is used to retrieve the data in result set in a descending or ascending order, by default it fetch the data in ascending order.

Source table, EMP_SalaryDetails

Project_ID	Basic	TA	DA	PF	TDS
X001	20000	10000	1500	100	500
X002	15000	10000	1090	520	500
X003	35000	1000	10050	520	500
X004	8000	1500	10045	400	500
X005	250000	1250	100	1000	500

SELECT * FROM EMP_SalaryDetails ORDER BY Basic;

Project_ID	Basic	TA	DA	PF	TDS
X004	8000	1500	10045	400	500
X002	15000	10000	1090	520	500
X001	20000	10000	1500	100	500
X003	35000	1000	10050	520	500
X005	250000	1250	100	1000	500

SELECT * FROM EMP_SalaryDetails ORDER BY Basic DESC;

Project_ID	Basic	TA	DA	PF	TDS
X005	250000	1250	100	1000	500
X003	35000	1000	10050	520	500
X001	20000	10000	1500	100	500
X002	15000	10000	1090	520	500
X004	8000	1500	10045	400	500

6. The LIKE Operator:

The LIKE operator is used to search for a specified pattern in a column.

Source table, EMP_Join

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
1	Narayan	Singh	02/11/84	04/01/08	1001
2	Vedu	Gopal	09/02/80	12/01/09	1002
3	Meera	Nayar	03/10/79	05/01/09	1003
4	Ali	Ahamad	08/28/85	09/01/10	1004
5	Usni	Mubarak	03/25/74	02/02/09	1005

SELECT * FROM EMP_Join WHERE F_Name LIKE ‘N%’ OR L_Name LIKE ‘N%’;

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
1	Narayan	Singh	02/11/84	04/01/08	1001
3	Meera	Nayar	03/10/79	05/01/09	1003

SELECT * FROM EMP_Join WHERE L_Name NOT LIKE ‘%bar%’ 

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
1	Narayan	Singh	02/11/84	04/01/08	1001
2	Vedu	Gopal	09/02/80	12/01/09	1002
3	Meera	Nayar	03/10/79	05/01/09	1003
4	Ali	Ahamad	08/28/85	09/01/10	1004

7. SQL Wildcards:

SQL wildcards must be used with the SQL LIKE operator.

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for exactly one character
[charlist]	Any single character in charlist
[^charlist] or [!charlist]	Any single character not in charlist

Source table, EMP_Join

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
1	Narayan	Singh	02/11/84	04/01/08	1001
2	Vedu	Gopal	09/02/80	12/01/09	1002
3	Meera	Nayar	03/10/79	05/01/09	1003
4	Ali	Ahamad	08/28/85	09/01/10	1004
5	Usni	Mubarak	03/25/74	02/02/09	1005

SELECT * FROM EMP_Join WHERE L_Name LIKE ‘__ra_a_’ 

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
1	Narayan	Singh	02/11/84	04/01/08	1001

The IN Operator:

IN operator is used to retrieves the multiple data from WHERE clause.

Source table, EMP_Join

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
1	Narayan	Singh	02/11/84	04/01/08	1001
2	Vedu	Gopal	09/02/80	12/01/09	1002
3	Meera	Nayar	03/10/79	05/01/09	1003
4	Ali	Ahamad	08/28/85	09/01/10	1004
5	Usni	Mubarak	03/25/74	02/02/09	1005

SELECT * FROM EMP_Join WHERE F_Name IN (‘Narayan’, ‘Ali’);

SN	F_Name	L_Name	DateOfBirth	DateOfJoinee	Emp_ID
1	Narayan	Singh	02/11/84	04/01/08	1001
4	Ali	Ahamad	08/28/85	09/01/10	1004