Question

Hi! I need help answering these 3 questions on SQLQuery. Please help! The database is below.

1. Count the number of courses taught by all adjunct and full time faculty members during the semester and their total enrollments for all classes they teach.

2. List first and last names only of all adjunct faculty members who are teaching more than 1 course , the total enrollment for those courses, and the number of courses the faculty members teach.

3. Display the last names of all full-time faculty members whether or not they are teaching a course. Display the number of courses each faculty member is teaching. Also display the overall total number of courses taught by full-time faculty members and the total revenue from those courses.

Using this database (faculty.sql)

GO

IF DB_ID('Faculty') IS NOT NULL
   DROP DATABASE Faculty
GO

CREATE DATABASE Faculty
GO

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

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

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

INSERT INTO Adjuncts VALUES ('a', 'Minshew', 'Gardner', 'Business', 'Kent');
INSERT INTO Adjuncts VALUES ('b', 'Conley', 'Chris', 'Economics', 'North');
INSERT INTO Adjuncts VALUES ('c', 'Lambo', 'Josh', 'English', 'Cecil');
INSERT INTO Adjuncts VALUES ('d', 'Marone', 'Doug', 'Music', 'Deerwood');
INSERT INTO Adjuncts VALUES ('e', 'Khan', 'Shad', 'Economics', 'South');
INSERT INTO Adjuncts VALUES ('f', 'Jack', 'Miles', 'Business', 'Kent');

Answer 1

Hi,

Here are your solutions

--First Question

select count(Course_ID) as No_of_Courses,c.Faculty_ID,Term,sum(Enrollment) as Total_Enrollment,FirstName,LastName, Department,Campus from course as c
join faculty as f
on f.Faculty_ID=c.Faculty_ID
group by c.Faculty_ID
union
select count(Course_ID) as No_of_Courses,c.Faculty_ID,Term,sum(Enrollment) as Total_Enrollment,FirstName,LastName, Department,Campus from course as c
join adjuncts as a
on a.Faculty_ID=c.Faculty_ID
group by c.Faculty_ID;

--Second Question

select  FirstName,LastName,sum(Enrollment) as Total_Enrollment,count(Course_ID) as No_Of_Courses from course as c
join adjuncts as a
on a.Faculty_ID=c.Faculty_ID
group by c.Faculty_ID 
having count(Course_ID) >1;

--Third Question

select LastName,count(Course_ID) as No_of_Courses,IFNULL(sum(TotRev), 0) as Total_Revenue from course as c
right join faculty as f
on f.Faculty_ID=c.Faculty_ID
group by c.Faculty_ID

Hope This Helps....... !!!