Complex SQL Queries
Emp_id Emp_name Emp_add Emp_salary Emp_DOB Permanent dept_name
YN
101 Sam Malad 50000 1999-03-15 Y HR HR 3
102 Jill Dadar 80000 1996-05-18 Y MRKT MKRT 2
IT 2
103 Mack Bandra 30000 1992-05-12 Y MRKT
104 Tim Kandivali 40000 1992-08-05 N HR
105 Sid Bandra 50000 1994-07-25 Y IT
Steps
106 Kim Dadar 100000 1987-12-09 N IT
1. Form a group
107 Ann Malad 150000 1985-08-17 Y HR 2. Apply aggregate function
Group By Clause
• To divide rows into groups group by clause is used.
• The GROUP BY clause divides the rows returned from
the SELECT statement into groups. For each group, you
can apply an aggregate function.
• column_2, ...;
Group By Clause
• Group by clause is used to group the results of a SELECT query based on one or more columns. It is also used with
SQL functions to group the result from one or more tables.
• Syntax for using Group by in a statement.
SELECT column_name, function(column_name)
FROM table_name
GROUP BY column_name;
SELECT dept_name, sum(salary)
FROM employee
GROUP BY dept_name;
SELECT dept_name, count(*)
FROM employee
GROUP BY dept_name;
Find the average salary of instructors in each department
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name;
Having clause
• Having clause is used with SQL Queries to give more precise condition for a statement. It is used to
mention condition in Group by based SQL queries, just like WHERE clause is used with SELECT query.
• Syntax for HAVING clause is,
SELECT column_name, function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition
SELECT dept_name, count(*)
FROM employee
GROUP BY dept_name
HAVING count(*) < 3 ;
SQL Set operations
• The SQL Set operation is used to combine the two or more SQL SELECT statements.
• Types of Set Operation
1.Union
2.Union All
3.Intersect
4.Minus/Except
Syntax
SELECT query1
<set operation type>
SELECT query2;
Union
• The SQL Union operation is used to combine the result of two or more SQL SELECT
queries.
• In the union operation, all the number of datatype and columns must be same in both
the tables on which UNION operation is being applied.
• The union operation eliminates the duplicate rows from its resultset.
Syntax:
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;
table1 table2
ID name ID name
1 Jack 3 Jackson
2 Harry
4 Jim
3 Jackson
4 Jim 5 David
SELECT ID FROM table1
UNION
SELECT ID FROM table2;
o/p: 1,2,3,4,5,
Union All operation is equal to the Union operation. It returns the set without removing
duplication and sorting the data.
SELECT ID FROM table1
UNION ALL
SELECT ID FROM table2;
o/p: 1,2,3,4,5,3
Intersect
• It is used to combine two SELECT statements. The Intersect operation
returns the common rows from both the SELECT statements.
• In the Intersect operation, the number of datatype and columns must
be the same.
• It has no duplicates and it arranges the data in ascending order by
default.
SELECT ID FROM table1
INTERSECT
SELECT ID FROM table2;
o/p: 3,4
EXCEPT
• It combines the result of two SELECT statements.
EXCEPT operator is used to display the rows which are
present in the first query but absent in the second
query.
• It has no duplicates
SELECT ID FROM table1
EXCEPT
SELECT ID FROM table2;
o/p:1,2
To access data from multiple tables
• Select * from table1,table2;
• Display all attributes of table1 and table2 with all
combinations(cartesian product).
table1 table2
ID name ID name
1 Jack 3 Jackson
2 Harry
4 Jim
3 Jackson
4 Jim 5 David
emp1 emp2
EMP_NAME STREET CITY EMP_NAME BRANCH SALARY
Ram Civil line Mumbai Ram Infosys 10000
Shyam Park street Kolkata Shyam Wipro 20000
Ravi M.G. Street Delhi Kuber HCL 30000
Hari Nehru nagar Hyderabad Hari TCS 50000
Select emp1.emp_name, emp1.city, emp2.branch from emp1,emp2;
Select emp1.emp_name, emp1.city, emp2.branch,emp.salary from emp1,emp2;
Select * from emp1,emp2 where emp1.emp_name=emp2.emp_name;
Types of join operation
• A Join operation combines related tuples from different relations, if and only if a given
join condition is satisfied.
• SQL Join is used to fetch data from two or more tables, which is joined to appear as
single set of data. It is used for combining column from two or more tables by using
values common to both tables.
• JOIN Keyword is used in SQL queries for joining two or more tables.
• Types of Join operations
• Inner join (natural join)
• Outer join
• Left outer join
• Right outer join
• Full outer join
Natural join
• A natural join is the set of tuples of all combinations in R
and S that are equal on their common attribute names.
• Example: Let's use the emp1 and emp2:
SELECT * FROM emp1 NATURAL JOIN emp2;
SELECT * FROM emp1 JOIN emp2 on emp1.emp_name=emp2.emp_name;
; EMP_NAME STREET CITY BRANCH SALARY
Ram Civil line Mumbai Infosys 10000
Shyam Park street Kolkata Wipro 20000
Hari Nehru nagar Hyderabad TCS 50000
emp1
EMP_NAME STREET CITY EMP_NAME BRANCH SALARY
Ram Civil line Mumbai Ram Infosys 10000
Shyam Park street Kolkata Shyam Wipro 20000
Ravi M.G. Street Delhi Kuber HCL 30000
Hari Nehru nagar Hyderabad Hari TCS 50000
emp2
Outer join
• An outer join is basically of three types:
a.Left outer join
b.Right outer join
c.Full outer join
Left outer join:
• This type of join operation returns all the rows of the table on the left
side of the join and matching rows for the table on the right side of join.
The rows for which there is no matching row on right side, the result-set
will contain null. LEFT JOIN is also known as LEFT OUTER JOIN.
SELECT * FROM emp1 natural left outer join emp2;;
EMP_NAME STREET CITY BRANCH SALARY
Ram Civil line Mumbai Infosys 10000
Shyam Park street Kolkata Wipro 20000
Ravi M.G. Street Delhi [null] [null]
Hari Nehru nagar Hyderabad TCS 50000
Right outer join:
• This join returns all the rows of the table on the right
side of the join and matching rows for the table on the
left side of join. The rows for which there is no matching
row on left side, the result-set will contain null.
SELECT * FROM emp1 natural right outer join emp2;;
EMP_NAME STREET CITY BRANCH SALARY
Ram Civil line Mumbai Infosys 10000
Shyam Park street Kolkata Wipro 20000
Kuber [null] [null] HCL 30000
Hari Nehru nagar Hyderabad TCS 50000
• Full outer join:
• FULL JOIN creates the result-set by combining result of
both LEFT JOIN and RIGHT JOIN. The result-set will
contain all the rows from both the tables. The rows for
which there is no matching, the result-set will
contain NULL values.
SELECT * FROM emp1 natural full outer join emp2;;
EMP_NAME STREET CITY BRANCH SALARY
Ram Civil line Mumbai Infosys 10000
Shyam Park street Kolkata Wipro 20000
Kuber [null] [null] HCL 30000
Hari Nehru nagar Hyderabad TCS 50000
Ravi M.G. Street Delhi [null] [null]
View
• Views in SQL are kind of virtual tables. A view also has
rows and columns as they are in a real table in the
database. We can create a view by selecting fields from
one or more tables present in the database. A View can
either have all the rows of a table or specific rows based
on certain condition.
select emp_name, city from emp1;
View definition
• A view is defined using the create view statement which has the form
conditions];CREATE VIEW view_name AS SELECT columns FROM tables [WHERE
Once a view is defined, the view name can be used to refer to the virtual relation that the view generates.
create view v1 as select emp_name, city from emp1;
create view v2 as select emp_name, salary from emp2 where salary
>25000;
Update of a View
• Add a new tuple to v1 view
update v1 set emp_name='ravi' where city='Delhi’;
insert into v1 values('om','Jaipur’);
This insertion must be represented by the insertion into the emp1 relation
changes reflected to emp1 relation
To delete view query is:
drop view <view_name>;
drop view v1;
Nested Subqueries
• SQL provides a mechanism for the nesting of subqueries.
• A subquery is a select-from-where expression that is nested within
another query.
• A common use of subqueries is to perform tests for set membership,
set comparisons, and set cardinality.
• E.g. emp
Emp_id Emp_name Emp_add Emp_salary Emp_DOB Perma dept_name
nentYN
101 Sam Malad 50000 1999-03-15 Y HR
102 Jill Dadar 80000 1996-05-18 Y MRKT
103 Mack Bandra 30000 1992-05-12 Y MRKT
104 Tim Kandivali 40000 1992-08-05 N HR
105 Sid Bandra 50000 1994-07-25 Y IT
106 Kim Dadar 100000 1987-12-09 N IT
107 Ann Malad 150000 1985-08-17 Y HR
• Select * from table_name where <condition>
(select column_name from table_name)
Inner query
Nested Subqueries
• Write a query to display maximum salary from emp.
Select emp_name,max(salary) from emp;
• Write a query to display the emp name who is taking maximum salary.
select name from emp
where salary = (select max(salary) from emp);
• (150000)
Nested Subqueries
• Write a query to display the second highest salary from emp.
Step 1: retrieve the salaries excluding max salary.
Select salary from emp where salary <>(select max(salary) from emp);(150000)
Step 2: select the max salary tuple from the output.
Select max(salary) from emp where salary <>(select max(salary) from emp)
Step 3: To display the information of second highest salaried emp.
select * from emp
where salary = (Select max(salary) from emp
where salary <>(select max(salary) from emp);
Nested Subqueries
• Write a query to display highest salary department wise and name of
the emp who is taking that salary.
• Select emp_name from emp where salary in(Select max(salary) from
emp group by dept_name);
( 150000(HR),80000(MKRT),100000(IT))
E_id Ename Address emp
1 Sam Jaipur
2 Jill Delhi
3 Mack Pune
4 Tim Bangalore
5 Sid Jaipur
E_id P_id Pname Location project
1 P1 IOT Bangalore
2 P2 Big Data Delhi
3 P3 Retail Mumbai
4 p4 Android Hydrabad
Set Membership (in and not in)
• Find the customers whose salary is above 45000.
1. Select * from emp where salary >45000;
2. Select *
from customer
Where id In (select id from customer
where salary > 45000);
Find customers whose salary is below 45000.
Select * from customer where salary<45000;
Select *
from customer
Where id not in (select id from customer
where salary > 45000);
Nested Subqueries(in/not in)
• Find the name of the emps who are working on a project.
Step 1: find the emps who are working on projects.
select E_id from project;
Step 2: Display the emp names
select Ename from emp where E_id In (select E_id from project);(1,2,3,4)
Customer
ID Name age Salary
1 Ramesh 35 20000
2 Ram 25 15000
3 Kaushik 23 25000
4 Chaitali 25 65000
5 Hardik 27 85000
6 Komal 22 45000
7 John 24 100000
Set Comparison(some and all)
• Find names of emp with age greater than that of some (at least one)
customer whose salary is above 70000.
Same query using > some clause
select name
from customer
where age > some (select age
from customer
where salary > 70000);
(25, 27, 24)
Name
Ramesh
Ram
Chaitali
Hardik
Example Query: all
• Find the names of all customers whose age is greater than the age of
all customers having salary greater than 70000.
select name
from customer
where age > all (select age
from customer
where salary > 70000);
(25,27, 24)
Test for Cardinality or Test for Empty Relations
• The exists construct returns the value true if the argument subquery is
non-empty.
• exists
• not exists
Select name from Customer where not exists(select age from customer
where age > 50)
Select name from Customer where exists (select age from customer where age
> 25)
Select name from Customer where not exist (select * from customer where age
> 30)
