CTS 2437 Final Exam
CTS 2437
Final Exam--Part 2
Before providing a solution for any of the following problems, create the Faculty database. This is attached to the Final Exam—Part 2 link in Blackboard.
You should provide (1) a screen shot of your solution and (2) code for your solution, copied/pasted into this document. Each problem is worth 20 points.
1. Select the number of courses that have more than 20 persons enrolled, the total number of students in courses with more than 20 persons, and the average enrollment per each course with more than 20 persons.
SELECT 'COURSES' AS CourseSelection, COUNT(*) AS NumberOfEnrollment, AVG (Enrollment) AS AverageEnrollment, SUM (Enrollment) AS TotalEnrollment
FROM Course
Where Enrollment > 20
2. Select the course reference numbers and enrollments for courses with greater than the average enrollment of courses with more than 20 persons. Use a subquery.
SELECT Ref_Number, Enrollment
FROM Course
WHERE Enrollment > 20 and Enrollment > (SELECT AVG (Enrollment) FROM Course);
3. Repeat (2), using a join.
SELECT FirstName, Lastname, Ref_Number, Enrollment
FROM Course join Faculty ON Faculty.Faculty_ID = Course.Faculty_ID
Where Enrollment > 20 and Enrollment > (SELECT AVG (Enrollment) FROM Course)
4. Create a table called Customers that contains a person’s first name, last name, and social security number. Your table definition should
Require a first and last name.
Insure that SSN is in the form 999-99-9999.
Define SSN should as a unique key.
Have a primary key that is an identity field
CREATE TABLE Customers
( Customer_ID INT PRIMARY KEY IDENTITY, LastName varchar(20) NOT NULL, FirstName varchar(20) NOT NULL, SSN INT CHECK (SSN in ('999-99-9999')) UNIQUE,);
5. Create a view that includes the first and last names of full time faculty members, along with
Final Exam--Part 2
Before providing a solution for any of the following problems, create the Faculty database. This is attached to the Final Exam—Part 2 link in Blackboard.
You should provide (1) a screen shot of your solution and (2) code for your solution, copied/pasted into this document. Each problem is worth 20 points.
1. Select the number of courses that have more than 20 persons enrolled, the total number of students in courses with more than 20 persons, and the average enrollment per each course with more than 20 persons.
SELECT 'COURSES' AS CourseSelection, COUNT(*) AS NumberOfEnrollment, AVG (Enrollment) AS AverageEnrollment, SUM (Enrollment) AS TotalEnrollment
FROM Course
Where Enrollment > 20
2. Select the course reference numbers and enrollments for courses with greater than the average enrollment of courses with more than 20 persons. Use a subquery.
SELECT Ref_Number, Enrollment
FROM Course
WHERE Enrollment > 20 and Enrollment > (SELECT AVG (Enrollment) FROM Course);
3. Repeat (2), using a join.
SELECT FirstName, Lastname, Ref_Number, Enrollment
FROM Course join Faculty ON Faculty.Faculty_ID = Course.Faculty_ID
Where Enrollment > 20 and Enrollment > (SELECT AVG (Enrollment) FROM Course)
4. Create a table called Customers that contains a person’s first name, last name, and social security number. Your table definition should
Require a first and last name.
Insure that SSN is in the form 999-99-9999.
Define SSN should as a unique key.
Have a primary key that is an identity field
CREATE TABLE Customers
( Customer_ID INT PRIMARY KEY IDENTITY, LastName varchar(20) NOT NULL, FirstName varchar(20) NOT NULL, SSN INT CHECK (SSN in ('999-99-9999')) UNIQUE,);
5. Create a view that includes the first and last names of full time faculty members, along with