Query 1 (2 pts)
Write a query that outputs another version of table of prerequisites, where courses are represented by their titles, not by their IDs. In the resulting table, rename the columns course_id and prereq_id to course and prerequisite respectively. Write this query without join or product.
Query 2 (2 pts)
Find two numbers: the number of departments with the budget greater than or equal to the average budget and the number of departments with the budget less than the average budget. Output a table with one record and two columns: average or higher, less than average. Write this query without join or product.
Query 3 (2 pts)
For each student, find the number of courses where she or he got an A grade. Output a table consisting of two columns: name of student, number of A grades. Write this query without using join or product.
Query 4 (2 pts)
For each course, find the number of students who took this course. Output a table consisting of two columns: title, number of students. Write this query without using join or product.
Query 5 (2 pts)
Find all departments with the maximum budget.
Query 6 (2 pts)
Find all instructors who teach the maximum number of sections. For each such instructor, output the name and the number of sections.
University Database student ID name dept_name tot_cred 00128Zhang 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 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 i ID 00128 45565 12345 10101 2312176543 44553 22222 45678 22222 76543 45565 76653 98345 98765 98345 9898876766 course course_id title dept_name credits BIO-101 Intro. to Biology Biology 4 BIO-301 Genetics Biology 4 BIO-399 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. 3 FIN-201 Investment Banking Finance HIS-351 World History History 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-319 CS-101 CS-347 CS-101 EE-181 PHY-101 section course_id sec_id semester year building room_number time_slot_id BIO-101 1 Summer 2017 Painter 514 Summer 2018 Painter 514 CS-101 1 Fall 2017 Packard 101 CS-101 1 Spring 2018 Packard 101 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 Fall 2017 Taylor 3128 EE-181 Spring 2017 Taylor 3128 FIN-2011 Spring 2018 Packard 101 HIS-351 1 Spring 2018 Painter 514 C MU-1991 Spring 2018 Packard 101 D PHY-101 1 Fall 2017 Watson 100 A teaches ID course_id sec_id semester year 10101 CS-101 1 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 1 Fall 2017 32343 HIS-351 Spring 2018 45565 CS-101 1 Spring 2018 45565 CS-3191 Spring 76766 BIO-101 1 Summer 2017 76766 BIO-301 1 Summer 2018 83821 CS-1901 Spring 2017 83821 CS-1902 Spring 2017 83821 CS-3192 Spring 2018 98345 EE-181 1 Spring 2017 2018 takes ID course_id sec_id semesteryear grade 00128 CS-1011 Fall 2017 A 00128 CS-347 1 Fall 2017 A- 12345CS-1011 Fall 2017 12345CS-1902 Spring 2017 A 12345 CS-315 1 Spring 2018 A 12345 CS-347 1 2017 A 19991 HIS-351 1 Spring 2018 B 23121 FIN-2011 Spring 2018 C+ 44553 PHY-101 1 Fall 2017 B- 45678 CS-101 1 Fall 2017 F 45678 CS-101 1 Spring 2018 B+ 45678 CS-3191 Spring 2018 B 54321 CS-101 1 Fall 2017 A- 54321 CS-190 2 Spring 2017 B+ 55739 MU-1991 Spring 2018 A- 76543 CS-101 1 2017 A 76543 CS-3192 Spring 2018 A 76653 EE-181 1 Spring 2017 98765 CS-101 1 Fall 2017 C- 98765 CS-3151 Spring 2018 B 98988 BIO-1011 Summer 2017 A 98988 BIO-301 1 Summer 2018 Fall classroom building room_number capacity Packard 101 500 Painter 514 10 Taylor 3128 Watson 100 Watson 120 50 olololol 9 50 50 50 | | time slot time_slot_id day start_hr start_min end_hr_end_min A M 8 0 8 A W 8 0 8 A F8 08 50 M90 w 9 0 F9 M 11 w 11 F 11 50 M 13 W 13 F 13 T 10 R 10 30 45 T 14 30 R 14 30 M 16 0 W 16 50 F 16 0 16 50 1 W 10 o 12 45 45 50 University Database student ID name dept_name tot_cred 00128Zhang 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 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 i ID 00128 45565 12345 10101 2312176543 44553 22222 45678 22222 76543 45565 76653 98345 98765 98345 9898876766 course course_id title dept_name credits BIO-101 Intro. to Biology Biology 4 BIO-301 Genetics Biology 4 BIO-399 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. 3 FIN-201 Investment Banking Finance HIS-351 World History History 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-319 CS-101 CS-347 CS-101 EE-181 PHY-101 section course_id sec_id semester year building room_number time_slot_id BIO-101 1 Summer 2017 Painter 514 Summer 2018 Painter 514 CS-101 1 Fall 2017 Packard 101 CS-101 1 Spring 2018 Packard 101 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 Fall 2017 Taylor 3128 EE-181 Spring 2017 Taylor 3128 FIN-2011 Spring 2018 Packard 101 HIS-351 1 Spring 2018 Painter 514 C MU-1991 Spring 2018 Packard 101 D PHY-101 1 Fall 2017 Watson 100 A teaches ID course_id sec_id semester year 10101 CS-101 1 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 1 Fall 2017 32343 HIS-351 Spring 2018 45565 CS-101 1 Spring 2018 45565 CS-3191 Spring 76766 BIO-101 1 Summer 2017 76766 BIO-301 1 Summer 2018 83821 CS-1901 Spring 2017 83821 CS-1902 Spring 2017 83821 CS-3192 Spring 2018 98345 EE-181 1 Spring 2017 2018 takes ID course_id sec_id semesteryear grade 00128 CS-1011 Fall 2017 A 00128 CS-347 1 Fall 2017 A- 12345CS-1011 Fall 2017 12345CS-1902 Spring 2017 A 12345 CS-315 1 Spring 2018 A 12345 CS-347 1 2017 A 19991 HIS-351 1 Spring 2018 B 23121 FIN-2011 Spring 2018 C+ 44553 PHY-101 1 Fall 2017 B- 45678 CS-101 1 Fall 2017 F 45678 CS-101 1 Spring 2018 B+ 45678 CS-3191 Spring 2018 B 54321 CS-101 1 Fall 2017 A- 54321 CS-190 2 Spring 2017 B+ 55739 MU-1991 Spring 2018 A- 76543 CS-101 1 2017 A 76543 CS-3192 Spring 2018 A 76653 EE-181 1 Spring 2017 98765 CS-101 1 Fall 2017 C- 98765 CS-3151 Spring 2018 B 98988 BIO-1011 Summer 2017 A 98988 BIO-301 1 Summer 2018 Fall classroom building room_number capacity Packard 101 500 Painter 514 10 Taylor 3128 Watson 100 Watson 120 50 olololol 9 50 50 50 | | time slot time_slot_id day start_hr start_min end_hr_end_min A M 8 0 8 A W 8 0 8 A F8 08 50 M90 w 9 0 F9 M 11 w 11 F 11 50 M 13 W 13 F 13 T 10 R 10 30 45 T 14 30 R 14 30 M 16 0 W 16 50 F 16 0 16 50 1 W 10 o 12 45 45 50
