Advanced SQL Practice Problems

Consider this schema from the textbook for a university database:

Write the following queries in SQL, using this schema. You can test your answers at https://www.db-book.com/db7/university-lab-dir/sqljs.html. The expected output is given for each query when run on large-university.db (download this file and run SQLite locally via sqlite3 large-university.db or upload to the browser-based version).

1. Find the names of those departments whose budget is higher than that of Astronomy. List them in alphabetic order.¹

   dept_name
   Athletics
   Biology
   Cybernetics
   Finance
   History
   Math
   Physics
   Psychology
   

2. Display a list of all instructors, showing each instructor's ID and the number of sections taught. Make sure to show the number of sections as 0 for instructors who have not taught any section.²

   ID	number_of_sections
   16807	0
   31955	0
   35579	0
   37687	0
   4034	0
   50885	0
   52647	0
   57180	0
   58558	0
   59795	0
   63395	0
   64871	0
   72553	0
   74426	0
   78699	0
   79653	0
   95030	0
   96895	0
   97302	0
   15347	1
   25946	1
   4233	1
   42782	1
   48507	1
   48570	1
   50330	1
   65931	1
   73623	1
   80759	1
   90376	1
   90643	1
   14365	2
   28097	2
   28400	2
   3335	2
   63287	2
   81991	2
   19368	3
   34175	3
   41930	3
   3199	4
   43779	4
   95709	4
   36897	5
   74420	6
   77346	6
   79081	6
   99052	9
   6569	10
   22591	13
   

3. For each student who has retaken a course at least twice (i.e., the student has taken the course at least three times), show the course ID and the student's ID. Please display your results in order of course ID and do not display duplicate rows.³

   course_id	ID
   362	        16480
   362	        16969
   362	        27236
   362	        39925
   362	        39978
   362	        44881
   362	        49611
   362	        5414
   362	        69581
   362	        9993
   

4. Find the names of Biology students who have taken at least 3 Accounting courses.⁴

   name
   Michael
   Dalton
   Shoji
   Wehen
   Uchiyama
   Schill
   Kaminsky
   Giannoulis
   

5. Find the sections that had maximum enrollment in Fall 2010.⁵

   course_id	sec_id
   867	        2
   

6. Find student names and the number of law courses taken for students who have taken at least half of the available law courses. (These courses are named things like 'Tort Law' or 'Environmental Law').⁶

   name	    course_count
   Nakajima    4
   Nikut	    4
   Hahn-	    4
   Nanda	    4
   Schinag	    4
   

7. Find the rank and name of the 10 students who earned the most A grades (A-, A, A+). Use alphabetical order by name to break ties. Note: the browser SQLite does not support window functions.⁷

   rank	name
   1	Neuhold
   2	Greene
   3	Hons
   4	Lepp
   5	Lingamp
   6	Mandviwall
   7	Drig
   8	Fabregas
   9	Haigh
   10	Heilprin
   

select X.dept_name
from department as X, department as Y
where Y.dept_name = 'Astronomy' and X.budget > Y.budget
order by X.dept_name;

select ID, count(sec_id) as number_of_sections
from instructor natural left outer join teaches -- a natural join joins on all attributes with the same name
group by ID
order by number_of_sections

select distinct course_id, ID
from takes
group by ID, course_id
having count(*) > 2
order by course_id;

select name
from student
where student.dept_name = 'Biology'
and student.ID in 
(select ID from takes, course 
 where dept_name = 'Accounting' and takes.course_id = course.course_id
 group by dept_name, ID
 having count(*) > 2);

with enrollment as
(
  select takes.course_id, takes.sec_id, count(ID) as enroll
  from takes, section
  where takes.course_id = section.course_id
  and takes.sec_id = section.sec_id
  and takes.semester = 'Fall'
  and takes.year = 2010
  group by takes.course_id, takes.sec_id
)
select course_id, sec_id
from enrollment
where enroll = (select max(enroll) from enrollment);

with law_course as
(
  select course_id
  from course
  where title like '% Law'
)
select name, count(*) as course_count
from student, takes
where student.ID = takes.ID and takes.course_id in law_course
group by student.ID
having count(*) >= (select (count(*) + 1) / 2 from law_course)
order by course_count desc;

with grade_count as 
(
  select name, count(*) as a_count 
  from student natural join takes 
  where grade like 'A_' 
  group by student.ID
) 
select row_number() over (order by a_count desc, name) as rank, name 
from grade_count 
limit 10;