EXPERIMENT NO: 7
Date of Performance:
Date of Submission:
AIM:
Perform Nested and Complex queries
THEORY:
Subquery (Nested Query)
It is query within another SQL query and embedded within the WHERE clause.
Important Rule:
● A subquery can be placed in a number of SQL clauses like WHERE clause, FROM clause,
HAVING clause.
● You can use Subquery with SELECT, UPDATE, INSERT, DELETE statements along with
the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
● A subquery is a query within another query. The outer query is known as the main query, and
the inner query is known as a subquery.
● Subqueries are on the right side of the comparison operator.
● A subquery is enclosed in parentheses.
● In the Subquery, ORDER BY command cannot be used. But GROUP BY command can be
used to perform the same function as ORDER BY command.
Subqueries with the Select Statement
SQL subqueries are most frequently used with the Select statement.
SELECT column_name FROM table_name
WHERE column_name expression operator
(SELECT column_name from table_name WHERE ... );
Example
EMPLOYEE table
ID NAME AGE ADDRESS SALARY
1 John 20 US 2000.00
2 Stephan 26 Dubai 1500.00
3 David 27 Bangkok 2000.00
4 Alina 29 UK 6500.00
5 Kathrin 34 Bangalore 8500.00
6 Harry 42 China 4500.00
7 Jackson 25 Mizoram 10000.00
The subquery with a SELECT statement will be:
SELECT * FROM EMPLOYEE WHERE ID IN
(SELECT ID FROM EMPLOYEE WHERE SALARY > 4500);
Result:
ID NAME AGE ADDRESS SALARY
4 Alina 29 UK 6500.00
5 Kathrin 34 Bangalore 8500.00
7 Jackson 25 Mizoram 10000.00
Subqueries with the INSERT Statement
● SQL subquery can also be used with the Insert statement. In the insert statement, data
returned from the subquery is used to insert into another table.
● In the subquery, the selected data can be modified with any of the character, date functions.
Syntax:
INSERT INTO table_name (column1, column2, column3....)
SELECT * FROM table_name WHERE VALUE OPERATOR
Consider a table EMPLOYEE_BKP with similar as EMPLOYEE.
Use the following syntax to copy the complete EMPLOYEE table into the EMPLOYEE_BKP
table.
INSERT INTO EMPLOYEE_BKP
SELECT * FROM EMPLOYEE WHERE ID IN
(SELECT ID FROM EMPLOYEE);
Subqueries with the UPDATE Statement
The subquery of SQL can be used in conjunction with the Update statement. When a subquery is
used with the Update statement, then either single or multiple columns in a table can be updated.
Syntax
UPDATE table SET column_name = new_value
WHERE VALUE OPERATOR
(SELECT COLUMN_NAME FROM TABLE_NAME WHERE condition);
Example
Suppose an EMPLOYEE_BKP table available which is backup of EMPLOYEE table. The given
example updates the SALARY by .25 times in the EMPLOYEE table for all employee whose
AGE is greater than or equal to 29. This would impact three rows, and finally, the EMPLOYEE
table would have the following records.
UPDATE EMPLOYEE SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BKP WHERE AGE >= 29);
ID NAME AGE ADDRESS SALARY
1 John 20 US 2000.00
2 Stephan 26 Dubai 1500.00
3 David 27 Bangkok 2000.00
4 Alina 29 UK 1625.00
5 Kathrin 34 Bangalore 2125.00
6 Harry 42 China 1125.00
7 Jackson 25 Mizoram 10000.00
Subqueries with the DELETE Statement
The subquery of SQL can be used in conjunction with the Delete statement just like any other
statements mentioned above.
Syntax
DELETE FROM TABLE_NAME WHERE VALUE OPERATOR
(SELECT COLUMN_NAME FROM TABLE_NAME WHERE condition);
Example
Suppose an EMPLOYEE_BKP table available which is backup of EMPLOYEE table. The given
example updates the SALARY by .25 times in the EMPLOYEE table for all employee whose
AGE is greater than or equal to 29. This would impact three rows, and finally, the EMPLOYEE
table would have the following records.
DELETE FROM EMPLOYEE WHERE AGE IN
(SELECT AGE FROM EMPLOYEE_BKP WHERE AGE >= 29);
ID NAME AGE ADDRESS SALARY
1 John 20 US 2000.00
2 Stephan 26 Dubai 1500.00
3 David 27 Bangkok 2000.00
7 Jackson 25 Mizoram 10000.00
Complex Query
-- create
CREATE TABLE EMPLOYEE (
Id INTEGER,
NAME TEXT NOT NULL,
AGE INTEGER NOT NULL,
ADDRESS TEXT NOT NULL,
SALARY double(10,2) NOT NULL
);
INSERT INTO EMPLOYEE VALUES (1, 'John', 20, 'USA',2000.00);
INSERT INTO EMPLOYEE VALUES (2, 'Stephan', 26, 'Dubai',1500.00);
INSERT INTO EMPLOYEE VALUES (3, 'David', 27, 'Bangkok',2000.00);
INSERT INTO EMPLOYEE VALUES (4, 'Alina', 29, 'UK',6500.00);
INSERT INTO EMPLOYEE VALUES (5, 'Kathrin', 34, 'China',8500.00);
INSERT INTO EMPLOYEE VALUES (6, 'Harry', 42, 'Russia',4500.00);
INSERT INTO EMPLOYEE VALUES (7, 'Jackson', 25, 'Ukraine',10000.00);
INSERT INTO EMPLOYEE VALUES (1, 'John', 20, 'USA',2000.00);
--Query to find the second highest salary
SELECT MAX(SALARY) FROM EMPLOYEE
WHERE SALARY < (SELECT MAX(SALARY) FROM EMPLOYEE);
Output:
MAX(SALARY)
8500.00
--Query to find duplicate records in Table
SELECT ID, COUNT(ID) FROM EMPLOYEE GROUP BY ID HAVING COUNT(ID) > 1;
Using the GROUP BY and HAVING clauses, the duplicates in database can be found. Once it
is validated that the rows are the same, the duplicate(s) can be removed using the DELETE
statement.
--Query to fetch first record from the table
select * from EMPLOYEE order by empId ASC LIMIT 1
--Query to fetch last record from the table
select * from EMPLOYEE order by empId DESC LIMIT 1
--Query to fetch first 5 records from the table
select * from EMPLOYEE order by empId ASC LIMIT 5
--Query to fetch last 5 records from the table
select * from EMPLOYEE order by empId DESC LIMIT 5
--Query to get first highest salary
select distinct(Salary) from EMPLOYEE order by Salary DESC LIMIT 1;
--Query to get second highest salary
select distinct(Salary) from EMPLOYEE order by Salary DESC LIMIT 1 offset 1;
--Query to get third highest salary
select distinct(Salary) from EMPLOYEE order by Salary DESC LIMIT 1 offset 2;
--Query to get records with odd primary key value
select * from EMPLOYEE where mod(empId,2)<>0;
--Query to get records with even primary key value
select * from EMPLOYEE where mod(empId,2)=0;
--Query create table with same structure of Employee table
CREATE TABLE EMP as select * from EMPLOYEE where 1=2;
desc EMP;
--Query to get records where employee name is Dave or Ava
Select * from EMPLOYEE where name in('Dave','Ava');
--Query to get records where employee name is neither Dave nor Ava
Select * from EMPLOYEE where name not in('Dave','Ava');
--Query to find maximum salary of each department
Select dept, max(salary) from EMPLOYEE group by dept;
CONCLUSION:
Nested queries provide a powerful tool for extracting specific and meaningful data from
databases. By nesting one query within another, DBA can enhance filtering, improve
performance, and achieve greater flexibility in data retrieval. By understanding the structure,
syntax, and best practices of nested queries, DBA can leverage their potential to its fullest extent.
Keep exploring and experimenting with nested queries to unlock new possibilities in DBA’s data
analysis journey.
MARKS & SIGNATURE:
R1 R2 R3 R4 Total Signature
(3 Marks) (5 Marks) (4 Marks) (3 Marks) (15 Marks)
