SQL is a standard language for storing, manipulating and retrieving data in databases.
In this series of posts, we will discuss the SQL queries from very Basic to Advanced.
It is recommended to view the post in the following order:
1. SQL Basic Queries Example – I
2. SQL Basic Queries Example – II
3. SQL Queries Using Aggregate Functions – I
4. SQL Queries Using Aggregate Functions – II
5. SQL Intermediate Queries Example – I
6. SQL Intermediate Queries Example – II
7. SQL Advanced Queries Example – I
8. SQL Advanced Queries Example – II
In this post, we will look at some advanced SQL queries.
Create the tables described below:
Table Name: Worker
+-----------+------------+-----------+--------+---------------------+------------+ | worker_id | first_name | last_name | salary | joining_date | department | +-----------+------------+-----------+--------+---------------------+------------+ | 1 | Monika | Arora | 100000 | 2014-02-20 09:00:00 | HR | | 2 | Niharika | Verma | 80000 | 2014-06-11 09:00:00 | Admin | | 3 | Vishal | Singhal | 300000 | 2014-02-20 09:00:00 | HR | | 4 | Amitabh | Singh | 500000 | 2014-02-20 09:00:00 | Admin | | 5 | Vivek | Bhati | 500000 | 2014-06-11 09:00:00 | Admin | | 6 | Vipul | Diwan | 75000 | 2014-01-20 09:00:00 | Account | | 8 | Geetika | Chauhan | 90000 | 2014-04-11 09:00:00 | Admin | +-----------+------------+-----------+--------+---------------------+------------+
Command:
create table Worker (worker_id int primary key, first_name varchar(20), last_name varchar(20), salary int, joining_date datetime, department varchar(20)); insert into Worker values(1, 'Monika', 'Arora', 100000, '2014-02-20 09:00:00', 'HR'); insert into Worker values(2, 'Niharika', 'Verma', 80000, '2014-06-11 09:00:00', 'Admin'); insert into Worker values(3, 'Vishal', 'Singhal', 300000, '2014-02-20 09:00:00', 'HR'); insert into Worker values(4, 'Amitabh', 'Singh', 500000, '2014-02-20 09:00:00', 'Admin'); insert into Worker values(5, 'Vivek', 'Bhati', 500000, '2014-06-11 09:00:00', 'Admin'); insert into Worker values(6, 'Vipul', 'Diwan', 75000, '2014-01-20 09:00:00', 'Account'); insert into Worker values(8, 'Geetika', 'Chauhan', 90000, '2014-04-11 09:00:00', 'Admin');
Table Name: Title
+---------------+---------------+---------------------+ | worker_ref_id | worker_title | affected_from | +---------------+---------------+---------------------+ | 1 | Manager | 2016-02-20 00:00:00 | | 2 | Executive | 2016-06-11 00:00:00 | | 3 | Lead | 2016-06-11 00:00:00 | | 4 | Asst. Manager | 2016-06-11 00:00:00 | | 5 | Manager | 2016-06-11 00:00:00 | | 6 | Lead | 2016-06-11 00:00:00 | | 7 | Executive | 2016-06-11 00:00:00 | | 8 | Executive | 2016-06-11 00:00:00 | +---------------+---------------+---------------------+
Command:
create table Title (worker_ref_id int primary key, worker_title varchar(20), affected_from datetime); insert into Title values(1, 'Manager', '2016-02-20 00:00:00'); insert into Title values(2, 'Executive', '2016-06-11 00:00:00'); insert into Title values(8, 'Executive', '2016-06-11 00:00:00'); insert into Title values(5, 'Manager', '2016-06-11 00:00:00'); insert into Title values(4, 'Asst. Manager', '2016-06-11 00:00:00'); insert into Title values(7, 'Executive', '2016-06-11 00:00:00'); insert into Title values(6, 'Lead', '2016-06-11 00:00:00'); insert into Title values(3, 'Lead', '2016-06-11 00:00:00');
Table name: Bonus
+---------------+---------------------+--------------+ | worker_ref_id | bonus_date | bonus_amount | +---------------+---------------------+--------------+ | 1 | 2016-02-20 00:00:00 | 5000 | | 2 | 2016-06-11 00:00:00 | 3000 | | 3 | 2016-02-20 00:00:00 | 4000 | | 1 | 2016-02-20 00:00:00 | 4500 | | 2 | 2016-06-11 00:00:00 | 3500 | +---------------+---------------------+--------------+
Command:
create table Bonus (worker_ref_id int, bonus_date datetime, bonus_amount int); insert into Bonus values(1, '2016-02-20 00:00:00', 5000); insert into Bonus values(2, '2016-06-11 00:00:00', 3000); insert into Bonus values(3, '2016-02-20 00:00:00', 4000); insert into Bonus values(1, '2016-02-20 00:00:00', 4500); insert into Bonus values(2, '2016-06-11 00:00:00', 3500);
Exercise on Advanced Queries
a) Write an SQL query to fetch the departments that have less than five people in it.
Command: select * from Emp where eid in (select eid from Works where did in (select did from Dept where dname = 'Hardware' or dname = 'Software') group by eid having count(*) = 2); Output: +-----------+--------------+------+--------+ | eid | ename | age | salary | +-----------+--------------+------+--------+ | 141582651 | Mary Johnson | 44 | 94011 | +-----------+--------------+------+--------+
b) Write an SQL query to fetch worker names and total bonus amount earned with salaries >= 50000 and <= 100000.
Command: select w.first_name, w.last_name, sum(b.bonus_amount) from Worker w, Bonus b where w.worker_id = b.worker_ref_id and w.salary >= 50000 and w.salary <= 100000 group by b.worker_ref_id; Output: +------------+-----------+---------------------+ | first_name | last_name | sum(b.bonus_amount) | +------------+-----------+---------------------+ | Monika | Arora | 9500 | | Niharika | Verma | 6500 | +------------+-----------+---------------------+
c) Write an SQL query to print details of the Workers who are also Managers.
Command: select w.worker_id, w.first_name, w.last_name, t.worker_title from Worker w, Title t where w.worker_id = t.worker_ref_id and t.worker_title = 'Manager'; Output: +-----------+------------+-----------+--------------+ | worker_id | first_name | last_name | worker_title | +-----------+------------+-----------+--------------+ | 1 | Monika | Arora | Manager | | 5 | Vivek | Bhati | Manager | +-----------+------------+-----------+--------------+
d) Write an SQL query to determine the 5th highest salary.
Command: select worker_id, first_name, last_name, salary from Worker order by salary desc limit 4,1; Output: +-----------+------------+-----------+--------+ | worker_id | first_name | last_name | salary | +-----------+------------+-----------+--------+ | 8 | Geetika | Chauhan | 90000 | +-----------+------------+-----------+--------+
e) Write an SQL query to fetch the list of employees with the same salary.
Command: select w1.worker_id, w1.first_name, w1.last_name from Worker w1, Worker w2 where w1.salary = w2.salary and w1.worker_id != w2.worker_id; Output: +-----------+------------+-----------+ | worker_id | first_name | last_name | +-----------+------------+-----------+ | 5 | Vivek | Bhati | | 4 | Amitabh | Singh | +-----------+------------+-----------+
f) Write an SQL query to print the name of employees having the highest salary in each department.
Command: select w.first_name, w.salary, w.department from Worker w,(select max(salary) as salary, department from Worker group by department) as w2 where w.salary = w2.salary and w.department = w2.department; Output: +------------+--------+------------+ | first_name | salary | department | +------------+--------+------------+ | Vishal | 300000 | HR | | Amitabh | 500000 | Admin | | Vivek | 500000 | Admin | | Vipul | 75000 | Account | +------------+--------+------------+
g) Write an SQL query to print details of the Workers who have joined in Feb 2014.
Command: select * from Worker where month(joining_date) = 2 and year(joining_date) = 2014; Output: +-----------+------------+-----------+--------+---------------------+------------+ | worker_id | first_name | last_name | salary | joining_date | department | +-----------+------------+-----------+--------+---------------------+------------+ | 1 | Monika | Arora | 100000 | 2014-02-20 09:00:00 | HR | | 3 | Vishal | Singhal | 300000 | 2014-02-20 09:00:00 | HR | | 4 | Amitabh | Singh | 500000 | 2014-02-20 09:00:00 | Admin | +-----------+------------+-----------+--------+---------------------+------------+
