CS336 - Principles of Information and Data Management - Homework 3 - Solved

Given the following tables:

students(sid,name,age,gpa) courses(cid,deptid, name)

professors(ssn,name,address,phone,deptid) enrollment(sid,cid,section,grade, foreign key (sid) references students, foreign key (cid) references courses, foreign key (cid,section) references teaches)

teaches(cid,section,ssn, foreign key (cid) references courses, foreign key (ssn) references professors)

Domain
cid is in {’198:11’,’640:151’,’198:112’,...} deptid is in {’cs’,’math’,’music’,...} grade is in {’A’,’B’,’C’,...} section, age, ssn are an integers address, phone, name are strings gpa is float

Provide SQL instructions for each of the following questions
1.        Create the database schema.

2.        Find the name of professors that work for the cs department.

3.        Find those students (sid) enrolled in courses in the cs department.

4.        List ssn and name of professors that work for the cs department, but are not teaching any cscourses.

5.        List the number of courses offered by each department. Just the number of courses (not sections).

6.        List of those departments that offer more than 10 courses.

7.        Produce a list of the name of those students whose professor’s name starts with an M. Your result must have no duplicates.

1

8.        Assume that small sections have less than 30 students, medium sections have at least 30 students but less than 80, and large sections have at least 80 students.

Your result table should have the following rows and columns:

deptid
small
medium
large
cs
 
 
 
math
 
 
 
...
...
...
...
Each table entry must have the number of sections of a given size offered by each department.

9.        List of professors that work for departments with more than 20 faculty members and that offermore large sections than small and medium sections combined.

10.   Assume grades are A, B, C, D, F where D and F are failing grades. For each course (section) find the percentage of students that failed the course.

11.   Find the name of the professor with the maximum percentage of students that failed his course.

12.   On average what percentage of students fail a course? (total number of students that failed acourse / total number of enrolled students).

13.   Find a list of courses (sections) where the percentage of students with D or F is greater thanaverage.

14.   Write a query that produces the following table:

deptid
SPS
% A
% B
% C
% D
% F
cs
 
 
 
 
 
 
math
 
 
 
 
 
 
...
...
...
...
...
...
...
Where SPS is the average number of students in each section and column %A has the percentage of students that got an A, and so on, over all the courses offered by each department.