ONLY DO QUESTION 5 AND 6 !!

BELOW IS THE EXPECTED OUTPUT FOR 5 AND 6 IF DB_ID('FacultyAsst4') IS NOT NULL DROP DATABASE FacultyAsst4; GO

CREATE DATABASE FacultyAsst4; GO

USE FacultyAsst4;

CREATE TABLE Faculty (Faculty_ID VARCHAR(2), LastName VARCHAR(20), FirstName VARCHAR(20), Department VARCHAR(20), Campus VARCHAR(10));

INSERT INTO Faculty VALUES ('1', 'Bloomberg', 'Mike', 'Business', 'Kent'); INSERT INTO Faculty VALUES ('2', 'Smith', 'Adam', 'Economics', 'Kent'); INSERT INTO Faculty VALUES ('3', 'Shakespeare', 'Bill', 'English', 'South'); INSERT INTO Faculty VALUES ('4', 'Euler', 'Lynn', 'Math', 'Deerwood'); INSERT INTO Faculty VALUES ('5', 'Einstein', 'Al', 'Science', 'South');

CREATE TABLE Course (Course_ID CHAR(2), Ref_Number CHAR(5), Faculty_ID VARCHAR(2), Term VARCHAR(2), Enrollment INTEGER, TotRev FLOAT );

INSERT INTO Course VALUES ('1', '12345', 'a', 'A', 24, 12345.00 ); INSERT INTO Course VALUES ('2', '54321', '3', 'B', 18, 21435.00 ); INSERT INTO Course VALUES ('3', '13524', '1', 'B', 7, 1256.00 ); INSERT INTO Course VALUES ('4', '24653', '1', 'C', 29, 54421.00 ); INSERT INTO Course VALUES ('5', '98765', '5', 'A', 35, 246753.00); INSERT INTO Course VALUES ('6', '14862', '2', 'B', 14, 9876.00); INSERT INTO Course VALUES ('7', '96032', '1', 'C', 8, 863159.00); INSERT INTO Course VALUES ('8', '81256', '5', 'A', 5, 98762.00); INSERT INTO Course VALUES ('9', '64321', '2', 'C', 23, 2965.00); INSERT INTO Course VALUES ('10','90908', 'a', 'A', 45, 91724.00); INSERT INTO Course VALUES ('11','90908', '3', 'A', 23, 73725.00); INSERT INTO Course VALUES ('12','90908', '3', 'A', 16, 84224.00); INSERT INTO Course VALUES ('13','90908', 'b', 'A', 13, 42719.00);

CREATE Table Adjuncts (Faculty_ID Char(2), LastName VARCHAR(20), FirstName VARCHAR(20), Department VARCHAR(10), Campus VARCHAR(10));

INSERT INTO Adjuncts VALUES ('a', 'Rogers', 'Aaron', 'Business', 'Kent'); INSERT INTO Adjuncts VALUES ('b', 'Brady', 'Tom', 'Economics', 'North'); INSERT INTO Adjuncts VALUES ('c', 'Mahomes', 'Patrick', 'English', 'Cecil'); INSERT INTO Adjuncts VALUES ('d', 'Brees', 'Drew', 'Music', 'Deerwood'); INSERT INTO Adjuncts VALUES ('e', 'Goff', 'Jared', 'Economics', 'South'); INSERT INTO Adjuncts VALUES ('f', 'Lawrence', 'Trevor', 'Business', 'Kent');

4. List first and last names only of all full time faculty members who are teaching more than 2 courses and the total enrollment for those courses. Complete each of the following problems and submit the results in a Word document. For each problem, there should be a screen shot of your results and the code used to obtain your result. Please submit the code and screen shot for each problem on a separate page. All problems use tables from the Faculty-Asst5 database, posted in the Databases content area. 1. Display the number of courses with a total revenue more than $20,000, the total enrollment for those courses, and the average total revenue for those courses. Column heading should be as shown. 100 % Results Messages First Last Total Enrollment 1 Adam Smith 37 2 Einstein 40 3 Bill Shakespeare 57 4 Mike Bloomberg 44 Course 9 Enrolled Average Revenue 192 175213.911111111 1 2. Count the total number of courses taught by all adjunct and full time faculty members for A, B, and C terms and their total enrollments for all classes they teach. 5. Display the last names of all full time faculty members whether or not they are teaching. Display the number of courses each faculty member is teaching. Also display the overall total number of courses taught by adjuncts. 100 % Results al Messages First Name Last Name 1 Aaron Rogers 2 Adam Smith 3 AI Einstein 4 Bill Shakespeare 5 Mike Bloomberg 6 Tom Brady Courses 2 2 2 3 3 1 Enrolled 69 37 40 200 m Reute Messages Last one No colunan Boonberg 3 2 2 0 57 5 6 Smith NULL 2 10 44 13 6. Display the total number of courses taught by each faculty member each A, B, and C term, the total number of courses offered during each term, the total number of courses offered for all terms, and the total number of courses taught by each faculty member for all terms. 3. Only full-time faculty members with a total revenue of $15,000 or more for all their courses will be retained for the current semester. List faculty members by first name, last name, and faculty ID who will be retained for the semester along with their total revenues. 1 2 Faculty o 3 5 4 5 5 NULL 6 1 100 % Results Messages First Name Last Name Faculty_ID 1 Mike Bloomberg 1 2 Bill Shakespeare 3 3 AL Einstein 5 Total Revenue 918836 179384 345515 8 8 3 9 NULL 10 1 11 2 12 NULL 13 NULL 14 1 15 2 16 3 17 5 18 a 19 Tem Course A 2 A 2 A 2 A 1 A 7 8 1 1 B 1 B 3 2 1 C 3 NULL 13 NULL 3 NULL 2 NULL 3 NULL 2 NULL 2 NULL 1