X


Intermediate SQL






Q. Demonstrate the use of 'on' condition. [Hint: Student (ID, name, dept_name, tot_cred) and takes (ID, course_id, sec_id, semester, year, grade )






Outer Joins







Left Outer Join






Q. Find all students who have not taken a course [Hint: Student (ID, name, dept_name, tot_cred) and takes (ID, course_id, sec_id, semester, year, grade )




Right Outer Join






Q. “Find all students who have not taken a course"






Full outer join




Q. Display a list of all students in the Comp. Sci. department, along with the course sections, if any, that they have taken in Spring 2009; all course sections fromSpring 2009 must be displayed, even if no student from the Comp. Sci. department has taken the course section. [Hint: Student (ID, name, dept_name, tot_cred) and takes (ID, course_id, sec_id, semester, year, grade )






Views





View Definition




Q. Create a view for a clerk who should not be allowed to access instructors salary [Hint : instructor (ID, name, dept_name, salary)





Q. To create a view that lists all course sections offered by the Physics department in the Fall 2009 semester with the building and room number of each section. [Hint: section(course_id, sec_id, semester, year, building, room_number, time_slot_id) ] and course(course_id, title, dept_name, credits) ]



Q. Using the view physics_fall_2009 given above, find all Physics courses offered in the Fall 2009 semester in the Churchill building





Q. Create a view that gives for each department the sum of the salaries of all the instructors at that department.



Q. Define a view physics_fall_2009_watson that lists the course_ID and room_number of all Physics courses offered in the Fall 2009 semester in the Churchill building using the physics_fall_2009 and without it:


Previous Next