SQL Final Exam

/* 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