Query 1 (2 pts)
Find all courses taught by Mozart. For each such course, output the title, semester, and year.
Query 2 (2 pts)
Find the names of all students who took both Intro. to Computer Science and Game Design.
Query 3 (2 pts)
Find the names of all instructors in the computer science department who never taught Intro. to Computer Science.
Query 4 (2 pts)
For each student, find the number of courses taken by this student. Output a table consisting of two columns: name of student, number of courses.
Query 5 (2 pts)
For each semester, find the number of students who took at least one section in this semester. Output a table consisting of three columns: semester, year, number of students. Order rows in the resulting table by the number of students in descending order.
Query 6 (2 pts)
Find all instructors who taught at least two sections. Output a table consisting of two columns: name of instructor, number of sections. Order rows in the resulting table by the number of sections in descending order.
University Database student ID name dept_name tot_cred 00128 Zhang Comp. Sci. 102 12345 Shankar Comp. Sci. 32 19991 Brandt History 80 23121 Chavez Finance 110 44553 Peltier Physics 56 45678 Levy Physics 46 54321 Williams Comp. Sci. 54 55739 Sanchez Music 38 70557 Snow Physics 76543 Brown Comp. Sci. 58 76653 Aoi Elec. Eng. 60 98765 Bourikas Elec. Eng. 98 98988 Tanaka Biology 120 instructor ID name dept_name salary 10101 Srinivasan Comp. Sci. 65000 12121 Wu Finance 90000 15151 Mozart Music 40000 22222 Einstein Physics 95000 32343 El Said History 60000 33456 Gold Physics 87000 45565 Katz Comp. Sci. 75000 58583 Califieri History 62000 76543 Singh Finance 80000 76766 Crick Biology 72000 83821 Brandt Comp. Sci. 92000 98345 Kim Elec. Eng. 80000 department dept_name building budget Biology Watson 90000 Comp. Sci. Taylor 100000 Elec. Eng. Taylor 85000 Finance Painter 120000 History Painter 50000 Music Packard 80000 Physics Watson 70000 advisor S_ID LID 00128 45565 12345 10101 23121 76543 4455322222 45678 22222 7654345565 76653 98345 9876598345 9898876766 course course_id title dept_name credits BIO-101 Intro. to Biology Biology 4 BIO-301 Genetics Biology 4 BIO-399 Computational Biology Computational Biology Biology 3 CS-101 Intro. to Computer Science Comp. Sci. 4 CS-190 Game Design Comp. Sci. 4 CS-315 Robotics Comp. Sci. 3 CS-319 Image Processing Comp. Sci. 3 CS-347 Database System Concepts Comp. Sci. 3 EE-181 Intro. to Digital Systems Elec. Eng. FIN-201 Investment Banking Finance HIS-351 World History History 3 MU-199 Music Video Production Music PHY-101 Physical Principles Physics 4 prereq course_id prereq_id BIO-301 BIO-101 BIO-399 BIO-101 CS-190 CS-101 CS-315 CS-101 CS-319CS-101 CS-347 CS-101 EE-181 PHY-101 3 5 section course_id sec_id semesteryear building room_number time_slot_id BIO-101 1 Summer 2017 Painter 514 BIO-301 1 Summer 2018 Painter 514 CS-101 1 Fall 2017 Packard 101 H CS-101 1 Spring 2018 Packard 101 F CS-190 1 Spring 2017 Taylor 3128 CS-1902 Spring 2017 Taylor 3128 CS-315 1 Spring 2018 Watson 120 CS-3191 Spring 2018 Watson 100 CS-3192 Spring 2018 Taylor 3128 CS-347 1 2017 Taylor 3128 EE-1811 Spring 2017 Taylor 3128 FIN-2011 Spring 2018 Packard 101 HIS-3511 Spring 2018 Painter 514 MU-1991 Spring 2018 Packard 101 > Spring teaches ID course_id sec_id semesteryear 10101 CS-1011 Fall 2017 10101 CS-315 1 Spring 2018 10101 CS-347 1 Fall 2017 12121 FIN-2011 Spring 2018 15151 MU-1991 Spring 2018 22222|PHY-101 1 Fall 2017 32343 HIS-351 1 Spring 2018 45565 CS-101 1 Spring 2018 45565 CS-3191 Spring 2018 76766 BIO-101 Summer 2017 76766 BIO-301 1 Summer 2018 83821 CS-1901 Spring 2017 83821 CS-1902 Spring 2017 83821 CS-319 2 Spring 2018 98345 EE-1811 Spring 2017 1 Sumi takes ID course_id sec_id semester year grade 00128 CS-1011 Fall 2017 A 00128 CS-3471 Fall 2017 A- 12345 CS-101 1 Fall 2017C 12345 CS-1902 Spring 2017A 12345CS-315 2018 A 12345CS-347 1 Fall 2017 A 19991 HIS-351 1 Spring 2018 B 23121 FIN-201 1 Spring 2018 C+ 44553 PHY-101 1 Fall 2017 B- 45678 CS-101 1 Fall 2017 F 45678 CS-101 1 Spring 2018 B+ 45678CS-319 1 Spring 2018 B 54321 CS-101 2017 A- 54321 CS-1902 Spring 2017 B+ 55739 MU-199 Spring 2018 A- 76543 CS-101 1 Fall 2017 A 76543 CS-3192 Spring 2018 A 76653 EE-181 1 Spring 2017c 98765 CS-101 2017 C- 98765 CS-315 1 Spring 2018 B 98988 BIO-101 1 Summer 2017 A 98988 BIO-301 1 Summer 2018 11 Fall classroom building room_number capacity Packard 101 500 Painter 514 Taylor 3128 Watson 100 Watson 120 50 30 time slot time_slot_id daystart_hr start_minend_hrend_min M 8 0 8 50 w 8 8 50 F 8 8 50 M9 0950 w 90 950 F 909 M 11 0 11 W 110 F 11 11 50 M 13 0 13 50 w 13 13 50 F 13 0 13 50 T 10 30 R 10 30 T 14 30 15 45 R 14 30 15 45 M 16 0 16 50 W 16 o 16 50 F 16 O 16 50 H w 10 0 12 30 coolulululelea wwuolo I 45 145
