For each building, find the number of different instructors who have taught at least one course section
Fantastic news! We've Found the answer you've been seeking!
Question:
- For each building, find the number of different instructors who have taught at least one course section in that building since the year 2008 (including 2008). Do not display the building if no instructors have taught there since 2008. The result should display the building name and the number of instructors (give a proper column name). Note: there is more than one table which has a building column, and you need to figure out which one to use.(join on)
- Find the ID and name of the instructors who taught at least one course in their own departments but did not teach any courses in other departments. Remove duplicates in the result.(join on)
- Find the instructors who earn the second lowest salary in their own departments. Display the name, department name, and salary of these instructors in the alphabetical order of instructor name.(join on)
- Find the course(s) taken by the fewest students in the year 2006. The result should display the course title and the number of students. If a student has taken the same course more than once, this student should be counted only once.(join on)
- Find the department(s) with the fourth lowest number of students. Display the department name and the number of students in the department (give a proper column name) in the result.(join on)
- Update the salaries of the instructors as follows.
- Increase salary by 6% for the instructors who taught at least 3 different courses (different course_id);
- Increase salary by 4% for the instructors who taught exactly 2 different courses;
- Increase salary by 2% for other instructors (including the instructors who did not teach).
Please use BEGIN TRAN and ROLLBACK properly (CREATE VIEW is not allowed to use; you may use the WITH clause within BEGIN TRAN and ROLLBACK). You must use the CASE expression.
You can see the table screen shot.
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date: