VIEWS: 20 PAGES: 2 POSTED ON: 11/14/2012
instructor (ID, name, dept_name, salary) teaches (ID, course_id, sec_id, semester, year) course (course_id, title, dept_name, credits) section (course_id, sec_id, semester, year, building, room_number, time_slot_id) student (ID, name, dept_name, tot_cred) takes (ID, course_id, sec_id, semester, year, grade) department (dept_name, building, budget) Figure 1 Write the following queries in SQL, using the university schema in Figure 1. Answer Questions 1-9 by writing the corresponding SQL queries, using the university schema in Figure 1. 1. Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicate names in the result. 2. Find the IDs of all students who were taught by an instructor named ``Einstein’’. 3. Find the names of all instructors who have the same salary as the instructor named ``Einstein’’. 4. Find the IDs and names of all students who have not taken any course offering before Spring 2009. 5. Find the enrollment (選課人數) of each section that was offered in Autumn (學期) 2009 (學年). 6. For each department, find the name of the instructor who has the maximum salary in that department. 7. Create a section of the course ``CS-001’’ in Autumn 2009, with section id of 1. 8. Increase the salary of each instructor in the Comp. Sci. department by 10%. 9. Delete all courses that have never been offered (that is, do not occur in the section relation). 10. Output the result of executing your answer of Question 6 against the following instructor relational instance.
Pages to are hidden for
"instructor _ID_ name_ dept_name_ salary_ teaches _ID_ course_id "Please download to view full document