Question

SUBJECT: PROGRAMMING IN SQL

1. Create a view named vDepartmentInstructors that returns these columns:

1. the DepartmentName column from the Departments table
2. the LastName, FirstName, Status, and AnnualSalary columns from the Instructors table.

2. Write a SELECT statement that returns all the columns from the vDepartmentInstructors view that you created in question 1.

Return one row for each fulltime instructor in the English department.

3. Write an UPDATE statement that updates the vDepartmentInstructors view you created in question 1 so it increases the annual salary for each fulltime instructor in the English department by 10%. Then, run the SELECT statement you wrote in question 2 to be sure this worked correctly.

4. Create a view named vStudentCoursesMin that returns these columns:

1. the FirstName and LastName from the Students table
2. the CourseNumber, CourseDescription, and CourseUnits from the Courses table.

5. Write a SELECT statement that returns these columns from the vStudentCoursesMin view that you created in question 4:

1. LastName, FirstName, CourseDescription.

Return one row for each course with three units.

Sort the results by LastName and then by FirstName.

6. Create a view named vStudentCoursesSummary that uses the view you created in question 4.

1. This view should return some summary information about each student.
2. Each row should include these columns: LastName, FirstName, CourseCount (the number of courses the student is taking), and UnitsTotal (the total units for the student).

7. Write a SELECT statement that uses the view that you created in question 6 to

1. get the total units for the five students with the most units.

Return the LastName, FirstName and total units for each of those students.

Answer 1

Answer.1.

CREATE VIEW vDepartmentInstructors AS
SELECT Departments.DepartmentName, Instructors.FirstName, Instructors.LastName, Instructors.Status,
Instructors.AnnualSalary FROM Departments, Instructors ;

Answer.2.

SELECT *FROM vDepartmentInstructors ;

SELECT *FROM vDepartmentInstructors where DepartmentName='English' ;

Answer.3.

UPDATE vDepartmentInstructors
SET AnnualSalary=AnnualSalary+(AnnualSalary*10/100) WHERE DepartmentName='English' ;

running SELECT statement here--

SELECT *FROM vDepartmentInstructors ;

Answer.4.

CREATE VIEW vStudentCoursesMin AS
SELECT Students.FirstName, Students.LastName, Courses.CourseNumber, Courses.CourseDescription, Courses.CourseUnits FROM Students, Courses ;

Answer.5.

SELECT LastName, FirstName, CourseDiscription FROM vStudentCoursesMin ;

SELECT *FROM vStudentCoursesMin WHERE CourseUnits=3 ; // result having three CourseUnit

SELECT *FROM vStudentCoursesMin ORDER BY LastName ; // sorting result by Lastname

SELECT *FROM vStudentCoursesMin ORDER BY FirstName ; // sorting result by FirstName

Answer.6.

CREATE VIEW vStudentCoursesSummary AS
SELECT FirstName, LastName,
COUNT(DISTINCT CourseName) as CourseCount,
SUM(CourseUnits) as UnitsTotal FROM vStudentCoursesMin ;

Answer.7.

SELECT LastName, FirstName, UnitsTotal FROM vStudentCoursesSummary
WHERE (UnitsTotal IN (SELECT TOP(5) UnitsTotal FROM vStudentCoursesSummary as table1 GROUP BY UnitsTotal ORDER BY UnitsTotal DESC) ) ;