Personal GitHub

1 The schema of database embedded in the online SQL interpreter (for textbook Database system concepts by Henry F. Korth Abraham) is as follows:

SKS textbook database

2 SQL codes to get a list of:

2.1 Students IDs (hint: from the takes relation)

select distinct ID as student_ID
from takes

2.2 Instructors

select distinct ID as instructor_ID, name
from instructor

2.3 Departments

select distinct dept_name
from department

3 Write in SQL codes to do following queries:

3.1 Find the ID and name of each student who has taken at least one Comp. Sci. course; make sure there are no duplicate names in the result.

Method 1

select distinct S.ID as student_ID, 
                S.name,
                count(S.ID) as number_of_Comp_Sci_courses
from student S
inner join takes T
on S.ID = T.ID
inner join course C
on T.course_id = C.course_id
where C.dept_name = 'Comp. Sci.'
group by S.ID

Method 2

select distinct student.ID, name 
from student, takes, course
where course.dept_name = 'Comp. Sci.'
        and student.ID = takes.ID 
        and course.course_id = takes.course_id

3.2 Add grades to the list

Method 1

select distinct S.ID as student_ID, 
                S.name,
                C.title,
                T.grade
from student S
inner join takes T
on S.ID = T.ID
inner join course C
on T.course_id = C.course_id
where C.dept_name = 'Comp. Sci.'

Method 2

select distinct student.ID, name, grades 
from student, takes, course
where course.dept_name = 'Comp. Sci.'
        and student.ID = takes.ID 
        and course.course_id = takes.course_id

3.3 Find the ID and name of each student who has not taken any course offered before 2017.

select distinct s.ID as student_ID, 
                s.name 
from student s
where s.ID not in
(
    select distinct S.ID
    from student S
    inner join takes T
    on S.ID = T.ID
    where T.year < 2017);

3.4 For each department, find the maximum salary of instructors in that department. You may assume that every department has at least one instructor.

select dept_name, max(I.salary) as maximum_salary
from instructor I
group by dept_name

3.5 Find the lowest, across all departments, of the per-department maximum salary computed by the preceding query.

select max(I.salary) as maximum_salary
from instructor I
group by dept_name
order by maximum_salary
limit 1

3.6 Add names to the list

select dept_name, 
max(I.salary) as maximum_salary
from instructor I
group by dept_name
order by maximum_salary
limit 1

4 Find instructor (with name and ID) 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.)

Method 1

select ID, name 
from instructor 
except 
select distinct instructor.ID, instructor.name 
from instructor, teaches, takes 
where instructor.ID = teaches.ID 
    and teaches.course_id = takes.course_id 
    and teaches.year = takes.year 
    and teaches.semester= takes.semester 
    and teaches.sec_id= takes.sec_id 
    and takes.grade = 'A'
order by ID ASC;

Method 2

select distinct ID, name 
from instructor 
where ID NOT IN
(select distinct instructor.ID
from instructor, teaches, takes 
where instructor.ID = teaches.ID 
    and teaches.course_id = takes.course_id 
    and teaches.year = takes.year 
    and teaches.semester= takes.semester 
    and teaches.sec_id= takes.sec_id 
    and takes.grade = 'A')
order by ID ASC;

Method to double-check the results

select * 
from takes Ta
inner join teaches Te
on Ta.course_id = Te.course_id
    and Ta.sec_id = Te.sec_id
    and Ta.semester = Te.semester
    and Ta.year = Te.year
inner join instructor I
on I.ID = Te.ID
where i.ID = '32343'

5 Write 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.

Method 1

select distinct Se.course_id, Se.sec_id, 
        Se.year, Se.semester,
        count(S.ID) as number_of_students
from section Se
inner join takes Ta
on Ta.course_id = Se.course_id
    and Ta.sec_id = Se.sec_id
    and Ta.semester = Se.semester
    and Ta.year = Se.year
inner join Student S
on Ta.ID = S.ID
group by (Se.course_id, Se.sec_id, Se.year, Se.semester)

Method 2

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