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

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. 100% Results Messages First Name Last Name Faculty_ID Total Revenue 1 Mike Bloomberg 1 918836 2 Bill Shakespeare 3 179384 3 Einstein 5 345515