Q.2. Consider the university database course(course_id, title, dept_name, credit

Question

Q.2. Consider the university database course(course_id, title, dept_name, credits) instructor(ID, name, dept name, salary) section(course_id, sec_id, semester, year, building, room_number, time_slot_id) student(ID, name, dept_name, tot_cred) a) Find all instructors earning the highest salary (there may be more than one with the same salary). b) Delete all courses that have never been offered (that is, do not occur in the section relation). c) Insert every student whose tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000. d) Display a list of all instructors, showing their ID, name, and the number of sections that they have taught. Make sure to show the number of sections as 0 for instructors who have not taught any section. Your query should use an outerjoin, and should not use scalar subqueries. e) Write the same query as above, but using a scalar subquery, without outer join.
(2.5 Marks)
Q.2. Consider the university database course(course_id, title, dept_name, credits) instructor(ID, name, dept name, salary) section(course_id, sec_id, semester, year, building, room_number, time_slot_id) student(ID, name, dept_name, tot_cred) a) Find all instructors earning the highest salary (there may be more than one with the same salary). b) Delete all courses that have never been offered (that is, do not occur in the section relation). c) Insert every student whose tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000. d) Display a list of all instructors, showing their ID, name, and the number of sections that they have taught. Make sure to show the number of sections as 0 for instructors who have not taught any section. Your query should use an outerjoin, and should not use scalar subqueries. e) Write the same query as above, but using a scalar subquery, without outer join.
(2.5 Marks)
Q.2. Consider the university database course(course_id, title, dept_name, credits) instructor(ID, name, dept name, salary) section(course_id, sec_id, semester, year, building, room_number, time_slot_id) student(ID, name, dept_name, tot_cred) a) Find all instructors earning the highest salary (there may be more than one with the same salary). b) Delete all courses that have never been offered (that is, do not occur in the section relation). c) Insert every student whose tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000. d) Display a list of all instructors, showing their ID, name, and the number of sections that they have taught. Make sure to show the number of sections as 0 for instructors who have not taught any section. Your query should use an outerjoin, and should not use scalar subqueries. e) Write the same query as above, but using a scalar subquery, without outer join.
(2.5 Marks)

Explanation / Answer

1. Select ID FROM INSTRUCTOR WHERE salary = (select max(salary) from instructor);

2. Delete from Course where course_id Not IN(select unique(course_id) from Section );

3. insert into instructor
select ID, name, dept name, 10000 from student
where totcred > 100

4.Select ID,name, count(course_id,section_id,year, semester) as 'Number of sections'
from Instructor natural left outer join teaches group by ID, name;

5. select ID, name,(select count(*) from teaches B
where B.id=F.id) as Number_of_sections from instructor F;

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.