IF DB_ID('FacultyAsst5') IS NOT NULL DROP DATABASE FacultyAsst5; GO

CREATE DATABASE FacultyAsst5; GO

USE FacultyAsst5;

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');

5. Write a query that will select the highest enrollment for each faculty member (including adjuncts) among courses taught during the term, and return the sum of the highest enrollments in a column Combined Highest EnrollmentsFull Time Faculty. Use a subquery to obtain the maximum enrollment for each full time faculty member. 100% Results Messages Combined Highest Enrollments-Full Time Facuty 168 6. Repeat #5, using a common table expressions (CTE) that consists of a derived table to select the highest enrollment of all classes for each faculty member. Use a SELECT statement that references the derived table to obtain a total of the highest enrollments. 100% Results al Messages Combined Highest Enrolments-Full Time Faculty 1 168