Description
1. (1) Create a ‘university_midsem’ database. Import records from the
university_midsem.sql given.
2. (1+1+2) Create database admin ‘userdba’ with password ‘dbapwd’. Give complete
privilege on university_midsem to ‘userdba’. Logged in as ‘userdba’, create normal users
‘tom’ and ‘jerry’ with passwords of your choice. ‘tom’ should have the privilege to see
information about faculty members, but ‘jerry’ will not. Create another user ‘simba’ with
the same privilege as ‘jerry’ (you may use roles).
3. (2) Use a single statement to copy name and dept_name from instructor table into
onlynames6 table only for those records which have a salary more than 60000.
4. (2+2)
a. Create a view rank_stud which will list down ID, name, dept_name, tot_cred of
students in ascending order of their credits.
b. Update the tot_cred of Tanaka to 20 and show the records in stud_rank.
5. (3) Show a list of names of the departments for which the average salary of instructors in
the department is higher than average salary of instructors in the university.
6. (3) Show the title of the course which has prerequisites from a department which is
different from the department of the course.
7. (3) Select name, dept_name of the advisors advising two students.