CSCI 4333 Design of DB Systems: Query Exercise on a Toy University DB

CSCI 4333 Design of DB Systems: Query Exercise on a toy University DB

mysql> select * from Department;
+------+------+
| deptCode | deptName |
+------+------+
| ARTS | Arts |
| CSCI | Computer Science |
| HIST | History |
| MARK | Marketing |
| MATH | Mathematics |
| PHYS | Physics |
+------+------+ / mysql> select * from faculty;
+------+------+------+------+
| facId | name | deptCode | rank |
+------+------+------+------+
| F101 | Adams | ARTS | Professor |
| F105 | Tanaka | CSCI | Instructor |
| F110 | Byrne | MATH | Assistant |
| F115 | Smith | HIST | Associate |
| F221 | Smith | CSCI | Professor |
| F230 | Johnson | PHYS | Associate |
+------+------+------+------+

mysql> select * from Class;

+------+------+------+------+

| classNumber | facId | schedule | room |

+------+------+------+------+

| ART103A | F101 | MWF9 | H221 |

| CSC201A | F105 | TuThF10 | M110 |

| CSC203A | F105 | MThF12 | M110 |

| HST205A | F115 | MWF11 | H221 |

| MTH101B | F110 | MTuTh9 | H225 |

| MTH103C | F110 | MWF11 | H225 |

| PHY333A | F230 | MWF3 | H225 |

+------+------+------+------+

mysql> select * from Student;

+------+------+------+------+------+------+------+

| stuId | lastName | firstName | major | minor | credits | advisor |

+------+------+------+------+------+------+------+

| S1001 | Smith | Tom | HIST | MARK | 90 | F115 |

| S1002 | Chin | Ann | MATH | PHYS | 36 | F110 |

| S1005 | Lee | Perry | HIST | ARTS | 3 | F115 |

| S1010 | Burns | Edward | ARTS | CSCI | 63 | F101 |

| S1013 | McCarthy | Owen | MATH | CSCI | 0 | F110 |

| S1015 | Jones | Mary | MATH | CSCI | 42 | F110 |

| S1020 | Rivera | Jane | CSCI | MATH | 15 | F105 |

+------+------+------+------+------+------+------+

mysql> select * from Enroll;

+------+------+------+

| stuId | classNumber | grade |

+------+------+------+

| S1001 | ART103A | A |

| S1002 | ART103A | D |

| S1005 | ART103A | B |

| S1010 | ART103A | NULL |

| S1002 | CSC201A | F |

| S1005 | CSC201A | C |

| S1010 | CSC201A | A |

| S1015 | CSC201A | B |

| S1020 | CSC201A | B |

| S1001 | HST205A | C |

| S1005 | HST205A | A |

| S1002 | MTH101B | C |

| S1020 | MTH101B | A |

| S1002 | MTH103C | B |

| S1010 | MTH103C | NULL |

| S1020 | MTH103C | C |

+------+------+------+

Exercise:

1. Show all student names.
2. Show all information of students majoring in ‘MATH’.
3. Show the names and credits of students majoring in 'MATH'.
4. Show the names and credits of students majoring in 'MATH' and having 40 or more credits.
5. Show the id of students enrolled in 'CSC201A'.
6. Show the code of departments with faculty in the rank of 'Professor'.
7. Show the names of departments with faculty in the rank of 'Professor'.
8. Show the names of students who have enrolled in 'CSC201A'.
9. Show the names and major names of every student.
10. Show the names, major names, and advisor names of every student.
11. Show the student names and their major names for all students who have received a grade A in a class offered by a faculty from the MATH department.

12. Show the classNumber, schedule and room of all classes with student 'S1005' enrolled.
13. Show the classNumber, schedule and room of all classes with students 'S1005' or 'S1001'enrolled.
14. Show the classNumber, schedule and room of all classes with both student 'S1005' and 'S1001' enrolled.
15. Show the classNumber, schedule and room of all classes with student 'S1005' enrolled but not 'S1001' enrolled.
16. Show the ids and names of all inactive students (students not enrolled in any class).
17. Show the ids and names of students who are enrolled in classes offered in two or more rooms.
18*. Show the ids and names of students who are enrolledin classes offered only in the same room.
19. Show the names and major names of students who have taken both ‘MTH101B’ and ‘MTH103C’.
20. Show the id of students majoring in MATH but have not taken any course from faculty ‘F115’.
21. Show the id of students who have enrolled in the class ‘MTH101B’ with a grade B or better.
22*. Show the ids of students who have enrolled in all courses taught by faculty ‘F110’.

23. Show the number of students enrolled in the course ‘CSC201A’.
24. Show the number of students enrolled in each course that has at least one student in descending order of numbers of students.
25*. Show the number of students enrolled in each course in descending order of numbers of students. Include classes that have zero enrollments.
26. Show the numbers of students each faculty member (with facid) teaches. Only faculty with at least one student should be shown.
27. List the major department names and the total number of credits taken by all students in the major.
28. List the average number of credits students have taken.
29. List the average number of credits students have taken,in two decimal points.
30. List the major department names and the total number of credits taken by all students in the major. List only the department with a total number of credits more than 70.
31. List the class number of each class with student names enrolled in the class in a comma separator list.