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 intermediate SQL queries.
Create the tables described below:
Table Name: Emp
+-----------+------------------+------+--------+ | eid | ename | age | salary | +-----------+------------------+------+--------+ | 11564812 | John Williams | 35 | 74098 | | 90873519 | Elizabeth Taylor | 27 | 33055 | | 141582651 | Mary Johnson | 44 | 94011 | | 142519864 | Susan Martin | 39 | 56990 | | 159542516 | Matt Nelson | 33 | 48990 | | 242518965 | James Smith | 68 | 27099 | | 248965255 | Barbara Wilson | 48 | 95021 | | 254099823 | Patricia Jones | 28 | 42783 | | 287321212 | Michael Miller | 62 | 131072 | | 356187925 | Robert Brown | 28 | 35431 | | 486512566 | David Anderson | 20 | 25199 | | 489456522 | Linda Davis | 26 | 25971 | +-----------+------------------+------+--------+
Command:
create table Emp (eid int primary key, ename varchar(20), age int, salary int); insert into Emp values(142519864, 'Susan Martin', 39, 56990); insert into Emp values(242518965, 'James Smith', 68, 27099); insert into Emp values(141582651, 'Mary Johnson', 44, 94011); insert into Emp values(011564812, 'John Williams', 35, 74098); insert into Emp values(254099823, 'Patricia Jones', 28, 42783); insert into Emp values(356187925, 'Robert Brown', 28, 35431); insert into Emp values(489456522, 'Linda Davis', 26, 25971); insert into Emp values(287321212, 'Michael Miller', 62, 131072); insert into Emp values(248965255, 'Barbara Wilson', 48, 95021); insert into Emp values(159542516, 'Matt Nelson', 33, 48990); insert into Emp values(090873519, 'Elizabeth Taylor', 27, 33055); insert into Emp values(486512566, 'David Anderson', 20, 25199);
Table Name: Dept
+-----+------------+-------------+-----------+ | did | dname | budget | managerid | +-----+------------+-------------+-----------+ | 1 | Hardware | 1048572.12 | 141582651 | | 2 | Operations | 12099101.00 | 287321212 | | 3 | Legal | 222988.12 | 248965255 | | 4 | Marketing | 538099.56 | 548977562 | | 5 | Software | 400011.12 | 141582651 | | 6 | Production | 12099101.00 | 578875478 | | 7 | Shipping | 5.00 | 489456522 | +-----+------------+-------------+-----------+
Command:
create table Dept (did int primary key, dname varchar(20), budget float(12,2), managerid int); insert into Dept values(1, 'Hardware', 1048572.12, 141582651); insert into Dept values(2, 'Operations', 12099101.00, 287321212); insert into Dept values(3, 'Legal', 222988.13, 248965255); insert into Dept values(4, 'Marketing', 538099.54, 548977562); insert into Dept values(5, 'Software', 400011.12, 141582651); insert into Dept values(6, 'Production', 12099101.00, 578875478); insert into Dept values(7, 'Shipping', 5.00, 489456522);
Table name: Works
+-----------+------+------+ | eid | did | pct | +-----------+------+------+ | 142519864 | 2 | 100 | | 242518965 | 1 | 100 | | 141582651 | 1 | 50 | | 141582651 | 5 | 50 | | 141582657 | 1 | 25 | | 141582657 | 5 | 75 | | 11564812 | 3 | 100 | | 254099823 | 3 | 100 | | 356187925 | 2 | 100 | | 489456522 | 7 | 100 | | 287321212 | 2 | 100 | | 248965255 | 3 | 100 | | 159542516 | 4 | 100 | | 90873519 | 2 | 100 | +-----------+------+------+
Command:
create table Works (eid int, did int, pct int); insert into Works values(142519864, 2, 100); insert into Works values(242518965, 1, 100); insert into Works values(141582651, 1, 50); insert into Works values(141582651, 5, 50); insert into Works values(141582657, 1, 25); insert into Works values(141582657, 5, 75); insert into Works values(011564812, 3, 100); insert into Works values(254099823, 3, 100); insert into Works values(356187925, 2, 100); insert into Works values(489456522, 7, 100); insert into Works values(287321212, 2, 100); insert into Works values(248965255, 3, 100); insert into Works values(159542516, 4, 100); insert into Works values(090873519, 2, 100);
Exercise on Intermediate Queries
a) Print the names and ages of each employee who works in both the Hardware department and the Software department.
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) For each department with more than 20 full-time-equivalent employees (i.e. where the part-time and full-time employees add up to at least that many full-time employees), print the did together with the number of employees that work in that department.
Command: select did,sum(pct) from Works group by did having sum(pct) > 2000; Output: Empty set
c) Print the name of each employee whose salary exceeds the budget of all of the departments that he or she works in.
Command: select e.ename from Emp e natural join Works w natural join Dept d where e.salary > d.budget; Output: +-------------+ | ename | +-------------+ | Linda Davis | +-------------+
d) Find the managerids of managers who manage only departments with budgets greater than $1 million.
Command: select * from Dept where budget > 1000000.00; Output: +-----+------------+-------------+-----------+ | did | dname | budget | managerid | +-----+------------+-------------+-----------+ | 1 | Hardware | 1048572.12 | 141582651 | | 2 | Operations | 12099101.00 | 287321212 | | 6 | Production | 12099101.00 | 578875478 | +-----+------------+-------------+-----------+
e) Find the enames of managers who manage the departments with the largest budgets.
Command: select ename from Emp e, Dept d where e.eid = d.managerid and d.budget = (select max(budget) from Dept); Output: +----------------+ | ename | +----------------+ | Michael Miller | +----------------+
f) If a manager manages more than one department, he or she controls the sum of all the budgets for those departments. Find the managerids of managers who control more than $5 million.
Command: select managerid from Dept group by managerid having sum(budget)>5000000; Output: +-----------+ | managerid | +-----------+ | 287321212 | | 578875478 | +-----------+
g) Find the managerids of managers who control the largest amounts.
Command: select managerid from Dept group by managerid having sum(budget) = (select sum(budget) from Dept group by managerid order by sum(budget) desc limit 1); Output: +-----------+ | managerid | +-----------+ | 287321212 | | 578875478 | +-----------+
h) Find the enames of managers who manage only departments with budgets larger than $1 million, but at least one department with a budget less than $5 million.
Command: select ename from Emp e, Dept d where e.eid = d.managerid and d.budget >1000000 and d.managerid in(select managerid from Dept where budget < 5000000); Output: +--------------+ | ename | +--------------+ | Mary Johnson | +--------------+
