instructor _ID_ name_ dept_name_ salary_ teaches _ID_ course_id by fjzhangweiyun

VIEWS: 20 PAGES: 2

									         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.

								
To top