Write queries to solve the following problems. All problems require reference to two or more tables. Refer to the Completed Assignment Example in the Week 1 Notes for information on submitting each problem. You will be using the attached Faculty database which is at end. Select all fields in the Faculty table for full time faculty members with any courses scheduled. Order results by last name. List each faculty member only once.

1. Select first and last names of all full-time faculty members. If they have courses, list the reference number for each course. Otherwise, display NULL for the course reference number. Order by last name.

2. List all courses by reference number. If the course is taught by a full-time faculty member, list the first and last name of the faculty member. If the course is not taught by a full time faculty member, list just the reference number and NULL where appropriate. Order results by last name. Assume there can be multiple sections of a course and a single faculty member may teach more than one section.

3. Select the first/last names of all full-time faculty members along with the reference numbers of courses they are teaching. Display NULL for the reference number if the full-time faculty member is not teaching any courses. In the same result set, list all reference numbers for which there is no associated full time faculty member and NULL for the first/last name.

4. List campuses that are represented only by adjuncts, along with the names of the adjunct faculty members who teach at those campuses.

5. The dean wants to reassign all courses taught by adjuncts to full time faculty members that teach at the same campuses. List first and last names of all adjuncts that have campuses in common with full time faculty members. If the adjunct faculty member is scheduled to teach a course, list the reference number along with the name. If the faculty member is not scheduled to teach a class, display NULL. Assume there may be multiple sections of a course and a single faculty member may teach more than one section.

6. Display the first and last names of all faculty members (full time and adjunct) from the Kent campus who teach courses with a total revenue of more than $50,000.

7. Display all course reference numbers in the course table, along with one of the following in a column called Assessment: BIG Cash Cow!!! for courses with more than 100k in revenue Pretty good haul! for courses with between 10k and 100k revenue Hardly worth the effort! for courses with less than 10k revenue

here is the query doc

DROP TABLE Faculty;

CREATE TABLE Faculty

(Faculty_ID VARCHAR(2),

LastName VARCHAR(20),

FirstName VARCHAR(20),

Department VARCHAR(10),

Campus VARCHAR(10));

INSERT INTO Faculty VALUES ('1', 'Brown', 'Joe', 'Business', 'Kent');

INSERT INTO Faculty VALUES ('2', 'Smith', 'John', 'Economics', 'Kent');

INSERT INTO Faculty VALUES ('3', 'Jones', 'Sally', 'English', 'South');

INSERT INTO Faculty VALUES ('4', 'Black', 'Bill', 'Economics',

'Deerwood');

INSERT INTO Faculty VALUES ('5', 'Green', 'Gene', 'Business', 'South');

DROP TABLE COURSE;

CREATE TABLE Course

(Course_ID CHAR(2),

Ref_Number CHAR(5),

Faculty_ID VARCHAR(2),

Term Char(1),

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

DROP TABLE Adjuncts

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', 'Manning', 'Peyton', 'Economics',

'North');

INSERT INTO Adjuncts VALUES ('c', 'Drew', 'Maurice', 'English', 'Cecil');

INSERT INTO Adjuncts VALUES ('d', 'Griffin', 'Robert', 'Music',

'Deerwood');

INSERT INTO Adjuncts VALUES ('e', 'Goodell', 'Roger', 'Economics',

'South');

INSERT INTO Adjuncts VALUES ('f', 'Vilma', 'Jonathan', 'Business',

'Kent');