T03 Tutorial 03

Question 1

Using the university schema, write an SQL query to find the names of those departments whose budget is higher than that of Physics. List them in alphabetical order.

select X.dept_name
from department as X, department as H
where H.dept_name = 'Physics' and X.budget > H.budget
order by X.dept_name

Question 2

Using the university schema, use SQL to do the following:
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.

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

Question 3

Using the university schema, write an SQL query to find the IDs of those students who have retaken at least three distinct courses at least once
(i.e, the student has taken the course at least two times).

select distinct ID
from (
  -- At lease one time--
  select course_id, ID
  from takes
  group by ID, course_id
  having count(*) > 1)
group by ID
-- Have at lease three distinct course, count the number inside ID -- 
having count(course_id) > 2

Question 4

Using the university schema, write an SQL query to find the ID and name of each instructor who has never given an A grade in any course she or he has taught.
(Instructors who have never taught a course trivially satisfy this condition.)

select ID, name
from instructor
except (
  select distinct instructor.ID, instructor.name
    from (
    (instructor join teaches using (ID))
    join takes using (course_id, year, semster, sec_id)
    where takes.grade = "A"
)

Question 5

Using the university schema, write an SQL query to find the number of students in each section. The result columns should appear in the order “courseid, secid, year, semester, num”. You do not need to output sections with 0 students.

select course_id, sec_id, year, semester, count(*) as num
from takes
group by course_id,sec_id,year,semester