Description
Assignment: Write the following queries in SQL, using the university schema. 1. Show student name, department name course title for course id the student has chosen and name of instructor who is teaching that student using joins. (Give proper names to columns) 2. (a)Using join show a student name,instructor name pair where the instructor advises student. (b) Create a view to show the students names and their advisors names (c) Update the student as ‘< Your name>’ for the advisor ‘Singh’ and write what happens to the view and advisor table (d) Delete the updated record and write what happens to the view and advisor table (e) Insert a record with values ‘Advisor1’,’’ into the view and write what happens to the view and advisor table 3. Display all student names,student id their advisor names and advisor id also including those students who are not advised by any advisor. 4. (a) Create view advisor_dept_budg containing advisors’ ID,name, dept_name, salary for those advisors from the departments with a total salary of more than 100000. (b) Increase the salary of each instructor by 10% and check in the view 5. Create a view to display the ID , instructor name, course_id, sec_id, semester, building,room_number of all the courses taught in the year 2009 6. Display student id, student name and their department name for all students who have not taken any course? 7. Create a view to display all the students(along with the corresponding courseID) who got an ‘A’ grade in the year 2009. 8. Using nested query display all the instructors who have taught two or more courses. 9. Create a view to display the instructors who doesn’t taught in the year 2010 ( without using set operators). 10. Using inner join on department table, display the list of all department name, building such that each building has more than one department. 11. Display the student-advisor pairs(names) in which both the student and instructor took atleast one course in the year 2010.