Given the Schema:Department (dept_name [primary key], building, budget)Course (course_id [primary key], title, dept_name, credits)Instructor (ID [primary key], name, dept_name, salary) Foreign Key is dept_name references DepartmentSection (course_id [primary key], sec_id [primary key], semester [primary key], year [primary key], building, room_number, time_slot_id) Foreign Key course_idreferences CourseTeaches ( ID [primary key], course_id [primary key], sec_id [primary key], semester [primary key], year [primary key]) Foreign key ID references Instructor.Foreign Key (course_id,sec_id,semester,year) references SectionWrite the following in SQL:a. Find the names of all students who have taken at least one CS course; make sure there are no duplicate names in the result.b. Find the IDs and names of all students who have not taken any course offering before Spring 2009.c. For each department, find the maximum salary of instructors in that department You may assume that every department has at least one instructor.d. Find the lowest, across all department,s of the per-department maximum salary computed by the preceding query.e. Create a new course "CS-001", titled "Weekly Seminar", with 0 credits.f. Create a section of this course in Autumn 2009, with sec_id of 1g. Enroll every student in the CS department in the above section.h. Delete enrollments in the above section where the student's name is chavez.i. Delete the course CS-001. What will happen if you run this delete statement w/o first deleting offerings(sections) of this course.j. Delete all takes tuples corresponding to any section of any course with the word 'database" as a part of the title; ignore case when matching the word with thetitle.
-
Engineering 2022-05-15 19:04:59