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: Student
+------+---------+---------+-------+------+ | snum | sname | major | level | age | +------+---------+---------+-------+------+ | 101 | Charlie | CS | SR | 19 | | 102 | Smith | CS | JR | 20 | | 103 | Jacob | ECE | SR | 20 | | 104 | Tom | CS | JR | 20 | | 105 | Sid | CS | JR | 20 | | 106 | Harry | History | SR | 21 | | 107 | Hellen | CS | JR | 21 | | 108 | Bob | English | SR | 22 | | 109 | Andy | ECE | JR | 21 | | 110 | Charles | History | SR | 23 | +------+---------+---------+-------+------+
Command:
create table Student(snum int primary key, sname varchar(20), major varchar(20), level varchar(20), age int); insert into Student values(101, 'Charlie', 'CS', 'SR', 19); insert into Student values(102, 'Smith', 'CS', 'JR', 20); insert into Student values(103, 'Jacob', 'ECE', 'SR', 20); insert into Student values(104, 'Tom', 'CS', 'JR', 20); insert into Student values(105, 'Sid', 'CS', 'JR', 20); insert into Student values(106, 'Harry', 'History', 'SR', 21); insert into Student values(107, 'Hellen', 'CS', 'JR', 21); insert into Student values(108, 'Bob', 'English', 'SR', 22); insert into Student values(109, 'Andy', 'ECE', 'JR', 21); insert into Student values(110, 'Charles', 'History', 'SR', 23);
Table Name: Class
+--------+----------+------+------+ | cname | meets_at | room | fid | +--------+----------+------+------+ | CSC342 | Morning | R128 | 201 | | CSC343 | Noon | R128 | 203 | | CSC345 | Night | R154 | 204 | | ECE300 | Morning | R111 | 202 | | ECE301 | Noon | R111 | 203 | | ENG366 | Morning | R154 | 203 | | ENG367 | Evening | R111 | 205 | | HIS320 | Evening | R128 | 205 | +--------+----------+------+------+
Command:
create table Class(cname varchar(20) primary key,
meets_at varchar(20),
room varchar(20),
fid int);
insert into Class values('CSC342', 'Morning', 'R128', 201);
insert into Class values('CSC343', 'Noon', 'R128', 203);
insert into Class values('CSC345', 'Night', 'R154', 204);
insert into Class values('ECE300', 'Morning', 'R111', 202);
insert into Class values('ECE301', 'Noon', 'R111', 203);
insert into Class values('ENG366', 'Morning', 'R154', 203);
insert into Class values('ENG367', 'Evening', 'R111', 205);
insert into Class values('HIS320', 'Evening', 'R128', 205);
Table name: Enrolled
+------+--------+ | snum | cname | +------+--------+ | 101 | CSC342 | | 101 | CSC343 | | 101 | CSC345 | | 101 | ECE300 | | 101 | ENG366 | | 102 | CSC343 | | 102 | CSC345 | | 102 | ECE301 | | 103 | ECE300 | | 103 | ECE301 | | 104 | CSC342 | | 104 | ECE301 | | 105 | CSC345 | | 105 | ECE300 | | 106 | ENG366 | | 106 | HIS320 | | 107 | CSC342 | | 107 | ENG366 | | 108 | ENG367 | | 108 | HIS320 | | 109 | ECE300 | | 109 | ECE301 | | 110 | ENG366 | | 110 | HIS320 | +------+--------+
Command:
create table Enrolled(snum int, cname varchar(20)); insert into Enrolled values(101, 'CSC342'); insert into Enrolled values(101, 'CSC343'); insert into Enrolled values(101, 'CSC345'); insert into Enrolled values(101, 'ECE300'); insert into Enrolled values(101, 'ENG366'); insert into Enrolled values(102, 'CSC343'); insert into Enrolled values(102, 'CSC345'); insert into Enrolled values(102, 'ECE301'); insert into Enrolled values(103, 'ECE300'); insert into Enrolled values(103, 'ECE301'); insert into Enrolled values(104, 'CSC342'); insert into Enrolled values(104, 'ECE301'); insert into Enrolled values(105, 'CSC345'); insert into Enrolled values(105, 'ECE300'); insert into Enrolled values(106, 'ENG366'); insert into Enrolled values(106, 'HIS320'); insert into Enrolled values(107, 'CSC342'); insert into Enrolled values(107, 'ENG366'); insert into Enrolled values(108, 'ENG367'); insert into Enrolled values(108, 'HIS320'); insert into Enrolled values(109, 'ECE300'); insert into Enrolled values(109, 'ECE301'); insert into Enrolled values(110, 'ENG366'); insert into Enrolled values(110, 'HIS320');
Table name: Faculty
+-----+-----------+--------+ | fid | fname | deptid | +-----+-----------+--------+ | 201 | John | 301 | | 202 | M. Shanks | 302 | | 203 | I. Teach | 302 | | 204 | A. Zobrah | 303 | | 205 | M. Jensen | 303 | +-----+-----------+--------+
Command:
create table Faculty(fid int primary key, fname varchar(20), deptid int);
insert into Faculty values(201,'John', 301);
insert into Faculty values(202,'M. Shanks', 302);
insert into Faculty values(203,'I. Teach', 302);
insert into Faculty values(204,'A. Zobrah', 303);
insert into Faculty values(205,'M. Jensen', 303);Exercise on Advanced Queries
a) Find the names of all Juniours(Level = JR) who are enrolled in a class taught by I. Teach.
Command: select distinct a.sname from Student a natural join Class b natural join Enrolled c natural join Faculty d where a.level = 'JR' and d.fname = 'I. Teach'; Output: +--------+ | sname | +--------+ | Smith | | Tom | | Hellen | | Andy | +--------+
b) Find the age of the oldest student who is either a History major or enrolled in a course taught by I. Teach.
Command: select a.sname, a.age from Student a natural join Class b natural join Enrolled c natural join Faculty d where d.fname = 'I. Teach' and a.major = 'History' and a.age = (select max(age) from Student a natural join Class b natural join Enrolled c natural join Faculty d where d.fname = 'I. Teach' and a.major = 'History'); Output: +---------+------+ | sname | age | +---------+------+ | Charles | 23 | +---------+------+
c) Find the names of all classes that either meet in room R128 or have five or more students enrolled.
Command: select b.cname,count(*) from Student a natural join Class b natural join Enrolled c natural join Faculty d where b.room = 'R128' or b.cname in (select cname from Enrolled group by cname having count(*)>= 5) group by b.cname; Output: +--------+----------+ | cname | count(*) | +--------+----------+ | CSC342 | 3 | | CSC343 | 2 | | HIS320 | 3 | +--------+----------+
d) Find the names of all students who are enrolled in two-class that meet at the same time.
Command: select a.sname from Student a natural join Class b natural join Enrolled c group by a.sname, b.meets_at having count(*) >= 2; Output: +---------+ | sname | +---------+ | Bob | | Charlie | | Hellen | | Smith | +---------+
e) Find the names of faculty members who teach in every room in which some class is taught.
Command: select * from Faculty where fid in (select fid from Class group by fid having count(*) = (select count(distinct room) from Class)); Output: +-----+----------+--------+ | fid | fname | deptid | +-----+----------+--------+ | 203 | I. Teach | 302 | +-----+----------+--------+
f) Find the names of faculty members for whom the combined enrollment of the course that they teach is less than five.
Command: select * from Faculty where fid in (select fid from Class where cname in (select cname from Enrolled group by cname having count(*)<5)); Output: +-----+-----------+--------+ | fid | fname | deptid | +-----+-----------+--------+ | 201 | John | 301 | | 202 | M. Shanks | 302 | | 203 | I. Teach | 302 | | 204 | A. Zobrah | 303 | | 205 | M. Jensen | 303 | +-----+-----------+--------+
g) For each level, print the level and the average age of students for that level.
Command: select level,avg(age) from Student group by level; Output: +-------+----------+ | level | avg(age) | +-------+----------+ | JR | 20.4000 | | SR | 21.0000 | +-------+----------+
h) For all levels except JR, print the level and the average age of students for that level.
Command: select level,avg(age) from Student where level != 'JR' group by level; Output: +-------+----------+ | level | avg(age) | +-------+----------+ | SR | 21.0000 | +-------+----------+
i) For each faculty member that has taught class only in room R128 print the faculty member’s name and the total number of classes he or she has taught.
Command: select f.fname,count(*) from Faculty f natural join Class c natural join Enrolled e where c.room = 'R128' group by fname; Output: +-----------+----------+ | fname | count(*) | +-----------+----------+ | I. Teach | 2 | | John | 3 | | M. Jensen | 3 | +-----------+----------+
j) Find the names of students enrolled in the maximum number of classes.
Command: select * from Enrolled e natural join Student s group by e.snum order by count(*) desc limit 1; Output: +------+--------+---------+-------+-------+------+ | snum | cname | sname | major | level | age | +------+--------+---------+-------+-------+------+ | 101 | CSC342 | Charlie | CS | SR | 19 | +------+--------+---------+-------+-------+------+
