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’,’
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.
