SQL
1. Find the most classes taken by students
2. Change the department of the facility who teach math
3. Change the department of the facility who teach math course to MCS
4. Find names of students who enrolled in all courses
5. Find the students by student ID who have taken the most class (counted by enrolled students) and not counting where an ‘F' was the grade.
6. Find the facility by facility id who teach math class (class that have math in their name)
7. Change the major of all students who are math majors and have taken math course from Byrne to MCS.
8. Display the student's ID for all students enrolled in math course by Byrne.
9. Find all students by student ID enrolled in math course taught by Byrne.
10. Change the major of all students who are math majors to MCS.
11. Add three credit to credit earned by students who have taken the most course.
12. Insert a new facility record with id of F330, name jones, department of CSC and rank of instructor.
13. Erase all enrollment records for jane rivers
14. Find the names of all students by first name and last name are not enrolled in CSC201A
15. Find all courses in which fewer than three students are enrolled
16. Find the sum of all credits that history major have
17. Find the number of departments that have facility in them.
18. For each major, find the sum of all the credits that students with that major have.
FIGURE 5.4 The University Database
| Student |
|
|
| stuId |
lastName |
firstName |
major |
credits |
|
|
| $1,001 |
Smith |
Tom |
History |
90 |
|
|
| S1002 |
Chin |
Ann |
Math |
36 |
|
|
| $1,005 |
Lee |
Perry |
History |
3 |
|
|
| S1010 |
Burns |
Edward |
Art |
63 |
|
|
| $1,013 |
McCarthy |
Owen |
Math |
0 |
|
|
| 51015 |
Jones |
Mary |
Math |
42 |
|
|
| S1020 |
Rivera |
Jane |
CSC |
15 |
|
|
| Faculty |
|
|
| facld |
name |
department |
rank |
|
|
|
| F101 |
Adams |
Art |
Professor |
|
|
|
| F105 |
Tanaka |
CSC |
Instructor |
|
|
|
| F110 |
Byrne |
Math |
Assistant |
|
|
|
| 1115 |
Smith |
History |
Associate |
|
|
|
| F221 |
Smith |
CSC |
Professor |
|
|
|
| Class |
Enroll |
| classNumber |
facld |
schedule |
room |
stuld |
classNumber |
grade |
| ART103A |
F101 |
MWF9 |
H221 |
51001 |
AR1IO3A |
A |
| CSC201A |
F105 |
TuTh110 |
M110 |
$1,001 |
HST205A |
C |
| CSC203A |
F105 |
MThF12 |
11110 |
S1002 |
ARTIO3A |
D |
| HST205A |
F115 |
MWF11 |
H221 |
S1002 |
CSC201A |
F |
| MTHIOIB |
F110 |
MTuTh9 |
H225 |
S1002 |
MTM103C |
B |
| MTHIO3C |
F110 |
MWF11 |
H225 |
S1010 |
ART103A |
|
|
|
|
|
S1010 |
MTHIO3C |
|
|
|
|
|
S1020 |
CSC201A |
B |
|
|
|
|
S1020 |
MTH101B |
A |