SQL Final Exam Solution: Northwind Database Queries and Table Creation

This document presents a comprehensive solution to a SQL final exam. The assignment focuses on querying the Northwind database to retrieve specific information using various SQL commands, including subqueries to find products never ordered, selecting customers based on specific criteria, calculating employee salaries, listing top product prices, and filtering customers based on region. The solution also includes a script to create several tables for a university database, demonstrating database design principles, including primary and foreign key relationships. The document also contains explanations of the purpose of the enrollment table and addresses potential errors in the original script, providing corrected SQL code. Finally, it includes an extra credit section on calculating employee compensation based on sales and commissions.

/* Sonya Esclavon
SQL Final Exam
October 6th */
--1. Using a sub-query find all products that have never been ordered. Show the
product Id and the product name in your results.
SELECT ProductID, ProductName
FROM Products
WHERE ProductID NOT IN (SELECT ProductID FROM OrderDetails);
--2. List the customerId, Companyname and complete address for all customers that
have a customer ID that starts with M.
SELECT CustomerId, CompanyName, StreetAddress, City, State, Zip
FROM Customer
WHERE CustomerId LIKE 'M%';
--3. List each HOURLY employee’s first and last name and their estimated yearly
salary. Use 2080 as the number of hours worked annually. Concatenate the employee
first and last names. Name the columns appropriately.
SELECT empFName, empLName, (2080*amountPerHour) YearlySalary
FROM Employees
WHERE EmplyeeType = 'H';
--4. List the ten highest product prices and the product name.
SELECT TOP 10 ProductID, ProductName, Price
FROM dbo.ProductS
ORDER BY P.Price DESC;
--5. List all customers with no region.
SELECT*
FROM Customers
WHERE regionID IS NULL;
--6. List all products and the supplier. Order by supplier’s company name then
Product Name.
SELECT S.CompanyName, P.ProductName
FROM Suppliers AS S, Products AS P
WHERE S.SupplierId = P.SupplierId
ORDER BY S.CompanyName, P.ProductName;
--7. Run the Create table Script below and create the following tables. ( 16
Points)
/*Remember If you create the FK references in the Create table command you have
to
create the tables in the correct order for the FK references to work.
Alternatively you can
create the tables and code Alter Table statements to create the FK references
AFTER you
create the tables */
--Run this script prior to creating the tables for step 7
--Start Scipt
IF DB_ID('University') IS NOT NULL --check to see if the database exists
DROP DATABASE University; -- if it does drop the database
GO
Create Database University;--Creates the database

Go
Use University -- makes the database the active database
Go
--End Script
--Table Student
CREATE TABLE STUDENT
STUNUM- NUMBER, PRIMARY KEY VARCHAR (5),
STUFIRSTNAME VARCHAR (25),
STULASTNAME VARCHAR (25),
STUMAJOR VARCHAR (40).
--Table Enrollment
CREATE TABE ENROLLMENT,
CLASSCODE VARCHAR (5), PRIMARY KEY, FOREIGN KEY REFERENCES CLASS (CLASS CODE),
STUNUM VARCHAR (5), PRIMARY KEY, FOREIGN KEY REFERENCES STUDENT (STUNUM),
ENROLLDATE DATE/TIME.
--Table Class
CREATE TABLE CLASS,
CLASSCODE VARCHAR (5), PRIMARY KEY,
SECTIONNUMB VARCHAR (5),
INSTRUCTORID VAR CHAR (5), FOREGN KEY, REFERENCES (INSTRUCTOR TABLE),
LOCATION VARCHAR (40).
--Table Instructor
CREATE TABLE INSTRUCTOR,
INSTRUCTOR ID VARCHAR (5), PRIMARY KEY,
INS FNAME VARCHAR (30),
INS LNAME VARCHAR (30),
Phone VARCHAR (15)
--8. In a commented block in your Script explain the purpose of the enrollment
table. Why not create a PK/FK between student and Enrollment?
/* Data is supposed to be divided into separate tables to minimize the
duplication of data. Data that is duplicated wastes cache space, storage space
and makes it complex to maintain the database.
ENROLLDATE DATE/TIME.
--EXTRA CREDIT. Up to 10 additional points
--Northwind would like to know the total compensation paid to each employee in
2014
--List the employee last name, Commission paid, estimated salary and the total
compensation.
--Commission is earned on the pre-discount total of all sales completed by that
employee
--multiplied by the commission rate of 5% . Since salaried employees do not earn
a
--commission you should only show hourly employees-8. In a commented block in
your Script explain the purpose of the enrollment table. Why not create a PK/FK
between student and Enrollment? */
SELECT Total compensation empFName, empLName, (2080*amountPerHour) YearlySalary

FROM Employees
WHERE Employees IN (‘Last Name’, ‘Commission Paid (pre-discount total of all
sales * 5% commission) ’, ‘Estimated Salary’, ‘Total Compensation’)