COMP 1630 Relational Database Design and SQL SQL Project Using Microsoft SQL Server, write the SQL statements for each question necessary to generate the required result set. Clearly identify your...

COMP 1630

Relational Database Design and SQL

SQL Project

Using Microsoft SQL Server, write the SQL statements for each question necessary to generate the required result set. Clearly identify your answers to the questions. Save your work in the Desire2Learn
Drop box
for
SQL
Project.

Entity Relationship Diagram

Step 1 - Database and Tables

1. Run the script
SQLProjectData.sql
to create the database and tables listed below, and to load the data into the tables.

Customers 91 rows

Employees 9 rows

Shippers 3 rows

Suppliers 15 rows

Products 77 rows

Orders 1078 rows

OrderDetails 2820 rows

Part A - SQL Statements
(30 Marks)

1. List the products where the units in stock are less than the reorder level. Display the supplier id and supplier name from the Suppliers table, the product name, reorder level, and units in stock from the Products table, and the difference between the units in stock and the reorder level. Use the formula (UnitsInStock – ReorderLevel) to determine the difference, and display the difference as a positive value. Order the result set by the supplier name. The query should produce the result set listed below.

(18 row(s) affected)

2. List the order detail rows where the quantity is greater than or equal to 90. Display the order id from the OrderDetails table, the product id and product name from the Products table, the supplier id and supplier name from the Suppliers table, and a calculated cost of the order using the formula (OrderDetails.Quantity * OrderDetails.UnitPrice). Order the result set by the cost of the order. The query should produce the result set listed below.

OrderID

Cost___ ProductID ProductName_________ SupplierID Name

10052 486.00 75 Rhönbräu Klosterbier 12 Supplier L

10895 495.00 24 Guaraná Fantástica 10 Supplier J

10894 930.00 75 Rhönbräu Klosterbier 12 Supplier L

…..

11017 6050.00 59 Raclette Courdavault 8 Supplier H

10776 6360.00 51 Manjimup Dried Apples 14 Supplier N

10226 9526.00 29 Thüringer Rostbratwurst 12 Supplier L

(33 row(s) affected)

3. List the company name, contact name, phone number, and country from the Customers table, and the sum of the freight from the Orders table where the order date is between July 1, 1992 and June 30, 1993. Order the result set by the company name. The query should produce the result set listed below.

(82 row(s) affected)

4. List all the orders that have a shipped date of NULL and an employee that has a city of New Westminster in the Employees table. Display the customer id and phone number from the Customers table, the first name and last name from the Employees table, and the order id and order date from the Orders table. Format the employee’s name as the last name followed by a comma and a space followed by the first name. Format the order date as yyyy-MM-dd. Order the result set by the company name and order date. The query should produce the result set listed below.

(6 row(s) affected)

5. List the product id, product name, category, and unit price from the Products table, the supplier name from the Suppliers table, and calculate a new price by increasing the unit price by 15% using the formula (unit price * 1.15) where the unit price is greater than or equal to $20.00. Round the new price to 2 decimal points. Order the result set by the product name. Use a CASE statement to change the category id as follows:

Category ID value Change to

1 Beverage

2 Sauce & Spread

3 Dessert

4 Cheese

5 Bread

6 Meat

7 Miscellaneous

8 Seafood

ELSE

Unknown

The query should produce the result set listed below.

(38 row(s) affected)

6. List the customers with a country of Canada, Italy or UK, and suppliers with a supplier id greater than or equal to 3 and less than or equal to 8. Display the company name, contact name and phone from the Customers table, and the supplier id and name from the Suppliers table. Display ‘Customers’ for the rows from the Customers table and ‘Suppliers’ for the rows from the Suppliers table. Order the result set by company name. The query should produce the result set listed below.

TableName Name ContactName Phone

---------------- --------------------------------- ------------------------- -----------------------

Customers Around the Horn Thomas Hardy (71) 555-7788

Customers Bottom-Dollar Markets Elizabeth Lincoln (604) 555-4729

Customers B's Beverages Victoria Ashworth (71) 555-1212

…….

Suppliers Supplier F

Suppliers Supplier G

Suppliers Supplier H

(19 row(s) affected)

7. List the first name, middle name and last name from the Employees table, and the shipping city and their total number or count from the Orders table. Only display the shipping city if the count of the shipping city is greater than or equal to 7. Display the first name of the employee followed by a space followed by the middle name followed by a space followed by the last name but do not leave a space if the employee does not have a middle name. Order the result set by the employee name. The query should produce the result set listed below.

(12 row(s) affected)

8. List the orders where the shipped date is greater than or equal to January 1, 1994 and less than or equal to January 31, 1994, and calculate the length in days from the order date and the shipped date. Display the order id, and the shipped date from the Orders table, the company name from the Customers table, the calculated order cost by using the formula (Quantity * UnitPrice). Display the shipped date in the format MMM dd yyyy. Order the result set by the shipped date. The query should produce the result set listed below.

(140 row(s) affected)

9. List all the orders where the order date is the year 1994, and the cost of the order is greater than or equal to $2,500.00. Display the order id and a new shipped date calculated by adding 10 days to the shipped date from the Orders table, the product id from the Products table, the company name from the Customer table, and the cost of the order. Use the formula (OrderDetails.Quantity * Products.UnitPrice) to calculate the cost of the order. Format the calculated shipped date as MMM dd yyyy. Order the result set by the company name. The query should produce the result set listed below.

(16 row(s) affected)

10. List all the orders with a shipping country of Italy, and an order quantity greater than or equal to 30. Display the order id from the Orders table, the unit price and quantity from the OrderDetails table, the product name from the Products table, and the first name and last name from the Employees table. Format the employee`s name as the first name followed by a space followed by the last name. Order the result set by the unit price. The query should produce the result set listed below.

(11 row(s) affected)

Part B - INSERT, UPDATE, DELETE and VIEWS Statements
(30 Marks)

1. Create a view called suppliers_products_vw listing the products and their suppliers where the units on order is greater than 0. Display the product id, quantity per unit, units in stock, and units on order from the Products table, and the supplier name from the Suppliers table. Use the following query to test your view to produce the result set listed below.

SELECT *

FROM suppliers_products_vw

ORDER BY ProductID;

(17 row(s) affected)

2. Using a subquery, list all the orders that have a shipped date of NULL and an employee that has a city of New Westminster in the Employees table. Display the customer id, contact name and phone number from the Customers table, and the order id and order date from the Orders table. Format the order date as yyyy-MM-dd. Order the result set by the company name and order date. The query should produce the result set listed below.

(6 row(s) affected)

3. Using the INSERT statement, add two rows to the Employees table. The first row should have an employee id of 10, last name of Stevenson, first name of Joseph, and birth date of May 12, 1995. The second row should have an employee id of 11, last name of Thompson, first name of Mary-Beth, and birth date of September 10, 1999.

4. Create a view called employee_inform_vw to list the employees in the Employee table. Display the employee id, last name, first name, phone number, and birth date. Format the name as first name followed by a space followed by the last name. Display the phone number as opening bracket followed by the first 3 digits of the phone number followed by the closing bracket followed by the next 3 digits of the phone number followed by a dash followed by the last 4 digits of the phone number. Display spaces if the employee does not have a phone number. Display the birth date as MMM dd yyyy. Use the following query to test your view to produce the result set listed below.

SELECT *

FROM employee_inform_vw

WHERE EmployeeID IN ( 3, 9, 11 );

(3 row(s) affected)

5. Using the UPDATE statement, change the fax value to Unknown in the Customers table where the current fax value is NULL. There should be 22 rows affected.

6. Create a view called sales_by_customers_vw to list the total sales by customers and employees where the sum of the quantity is greater than or equal to 500. Display the company name from the Customers table, the employee’s first name and last name from the Employees table, and the sum of the quantity from the OrderDetails table. Format the employee’s name as the last name followed by a comma and a space followed by the first name. Use the following query to test your view to produce the result set listed below.

SELECT *

FROM sales_by_customers_vw

ORDER BY CompanyName;

(19 row(s) affected)

7. Using the DELETE statement, delete any rows with an employee having no phone number from the Employees table.

8. Using the UPDATE statement, add the phone number 6042537581 to the Employees table for the employee id of 10 but prevent the UPDATE from being written to the database.

9. Create a view called order_shipped_vw to list the orders where the difference between the shipped date and the order date is greater than 10 days, and where the year of the order date is greater than or equal to 1993. Display the order id, order date, shipped date, and shipper name from the Orders table, the first name and last name from the Employees table, the company name from the Shippers table, and the difference of the days between the shipped date and the order date (Shipped Date - Order Date). Format the employee name as the first name followed by a space followed by the last name. Display the order and shipped date in the format as yyyy-MM-dd. Use the following query to test your view to produce the result set listed below.

SELECT *

FROM order_shipped_vw

ORDER BY OrderDate;

(108 row(s) affected)

10. Using a subquery, list the orders with and order date greater than or equal to March 1, 1994 and shipped date of null, and the ordered product is discontinued in the Products table (Products.Discontinued value is 1). Display the order id and order date from the Orders table, the quantity from the OrderDetails table, and the company name, contact name, and phone number from the Customers table. Format the order date as yyyy.MM.dd. The query should produce the result set listed below.

(7 row(s) affected)

Part C - Stored Procedures, Triggers, and Functions
(40 Marks)

1. Create a stored procedure called orders_by_dates_sp displaying the orders shipped between specific dates. The start and end dates will be input parameters for the stored procedure. Display the order id, order date, and shipped date from the Orders table, the product id from the OrderDetails table, and the company name from the Customer table. Display the order and shipped date in the format MMM dd yyyy. Print the message ‘Please enter valid dates’ if either the start or end date is not supplied. Order the result set by the shipped date. Use the following query to test your stored procedure to produce the result set listed below.

EXECUTE orders_by_dates_sp; -- Both dates missing. Print message

EXECUTE orders_by_dates_sp '1994-03-01'; -- One date is missing. Print message

EXECUTE orders_by_dates_sp '1994-03-01', '1994-03-31'; --Print results below.

(195 row(s) affected)

CREATE PROCEDURE orders_by_dates_sp

@OrderDate DATETIME = NULL,

@ShippedDate DATETIME = NULL

AS

IF (@OrderDate IS NULL or @ShippedDate IS NULL)

BEGIN

PRINT'Enter a valid date'

END

ELSE

BEGIN

SELECT Orders.OrderID,

OrderDetails.ProductID,

Customers.CompanyName,

FORMAT(Orders.OrderDate, 'MMM dd yyyy') AS OrderDate,

FORMAT(Orders.ShippedDate, 'MMM dd yyyy') AS ShippedDate

FROM Customers INNER JOIN

Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN

OrderDetails ON Orders.OrderID = OrderDetails.OrderID

WHERE ShippedDate BETWEEN @OrderDate AND @ShippedDate

ORDER BY ShippedDate

END;

2. Create an INSERT trigger called insert_employee_tr on the Employees table to prevent the adding of a row with a phone number that is blank or NULL. Print the message 'Phone number is incorrect' is the phone number is not valid. Use the following query to test your trigger.

INSERT Employees -- Trigger should prevent the insert and print message.

VALUES( 20, 'Doe', 'Jane', 'Sally', '15 Pine Street', 'Vancouver', 'BC', 'V6X 4T6', NULL, '1975-05-23' );

INSERT Employees -- Trigger should prevent the insert and print message.

VALUES( 20, 'Doe', 'Jane', 'Sally', '15 Pine Street', 'Vancouver', 'BC', 'V6X 4T6', '', '1975-05-23' );

INSERT Employees --Trigger should allow the insert.

VALUES( 20, 'Doe', 'Jane', 'Sally', '15 Pine Street', 'Vancouver', 'BC', 'V6X 4T6', '6045552581', '1975-05-23' );

CREATE TRIGGER insert_employee_tr

ON Employees

AFTER INSERT

AS

SET NOCOUNT ON

IF ((SELECT phone FROM inserted) IS NULL)

BEGIN

PRINT'Phone number is incorrect'

ROLLBACK TRANSACTION

END;

3. Create a user defined function called OrderCost to calculate the cost of an order. The formula to calculate the cost of the order is ((1.0 – Discount) * (UnitPrice * Quantity)).

CREATE FUNCTION [dbo].[OrderCost](@i int)

RETURNS int

AS

BEGIN

DECLARE @OrderCost int

SELECT @OrderCost = SUM((1.0 - Discount) * (UnitPrice * Quantity))

FROM OrderDetails

RETURN @OrderCost

END

4. Create a stored procedure called total_cost_sp to list the order id from the OrderDetails table, the English name from the Products table, and the total cost of each order. The two input parameters for the stored procedure are the start and end order id values. To calculate the cost of the orders, use your user defined function OrderCost created in Part C question 3. Order the result set by the order id and English name. Use the following query to test your stored procedure to produce the result set listed below.

EXECUTE total_cost_sp 10700, 10750;

(136 row(s) affected)

CREATE PROCEDURE total_cost_sp7

@BeginOrderID int=5,

@EndorderID int=5

AS

IF (@BeginOrderID IS NULL or @EndorderID IS NULL)

BEGIN

PRINT 'Enter an order ID'

END

ELSE

BEGIN

DECLARE @OrderCost varchar

EXEC @OrderCost

SELECT OrderDetails.OrderID,

Products.EnglishName,

@OrderCost AS TotalCost

FROM OrderDetails INNER JOIN

Products ON OrderDetails.ProductID = Products.ProductID

WHERE OrderDetails.OrderID BETWEEN @BeginOrderID AND @EndorderID

ORDER BY TotalCost

END;

5. Create a stored procedure called discontinued_products_sp listing the products which may or may not be discontinued. The discontinued value will be an input parameter for the stored procedure. Display the product id, product name, and units in stock from the Products table, and the supplier name from the Suppliers table. Order the result set by the product name. If the discontinued option is not supplied, display the message ‘Specify discontinue option.’ Use the following query to test your stored procedure to produce the result set listed below.

EXECUTE discontinued_products_sp; -- Discontinued option missing. Print message.

EXEC discontinued_products_sp 1; -- Print result set below.

(8 row(s) affected)

6. Create an UPDATE trigger called check_shippeddate_tr on the Orders table to check that the shipped date is less than the required date of the order. Print the message 'Order was shipped after the required date' when the shipped date is greater than the required date, else print the message ‘Order was shipped successfully’. Allow all the update commands to process. Use the following queries to test your trigger.

UPDATE Orders -- Trigger should print message that shipped

SET ShippedDate = '1994-04-20' -- date late, and row will update.

WHERE OrderID = 11051

AND CustomerID = 'LAMAI'

AND EmployeeID = 7;

UPDATE Orders -- Trigger should print message that order

SET ShippedDate = '1994-04-10' -- shipped on time, and row will update.

WHERE OrderID = 11039

AND CustomerID = 'LINOD'

AND EmployeeID = 1;

7. Create an INSTEAD OF DELETE trigger called tr_supplier_delete that sets the name to ‘Do not use’ in the Suppliers table when a supplier row is deleted from the database, and the row should not be deleted from the table. Use the following code to test your trigger.

DELETE FROM Suppliers -- Trigger should update supplier name

WHERE SupplierID = 3; -- but row should remain in Suppliers table

8. Create a stored procedure called shipping_date_sp where the shipped date is equal to an input parameter of the shipped date for the stored procedure. Display the order id, order date, required date, and shipped date from the Orders table, and the company name and phone number from the Customers table. Display all the dates in the format MMM dd yyyy. Order the result set by the order date. If the shipped date is missing, display the message ‘Please enter a valid shipped date’. Use the following query to test your stored procedure to produce the result set listed below.

EXECUTE shipping_date_sp; --Date missing. Print message.

EXECUTE shipping_date_sp '1994-03-01'; -- Print result set below.

(3 row(s) affected)

9. Create a stored procedure called sales_by_employees_sp to list the cost of invoices by employees. To determine the order cost, the formula will be (OrderDetails.Quantity * OrderDetails.UnitPrice). Display the employee d, first name,last name from the Employees table, and the sum of the order cost. Format the employee’s name as the first name followed by a space followed by the last name. Order the result set by the last name of the employee. Use the following query to test your stored procedure to produce the result set listed below.

EXECUTE sales_by_employees_sp;

(9 row(s) affected)

10. Create a multi-table valued function called determine_discount having two input parameters for the quantity and the increase value of the discount. List the discount and new discount for customers where the product is not discontinued (Product. Discontinued value is 0), the current discount is greater than 0, and the quantity is equal to the input quantity parameter. The new discount value is calculated by adding the discount value from the OrderDetails table to the input parameter of the discount value. Display the customer id and company name from the Customers table, the discount from the OrderDetails table, and the calculated new discount value. Use the following query to test your function to produce the result set listed below.

SELECT *

FROM determine_discount( 60, 0.05 );

CustomerID CompanyName Discount NewDiscount

--------------- ------------------------------- ------------ -------------------

ERNSH Ernst Handel 0.20 0.25

VAFFE Vaffeljernet 0.05 0.10

FOLKO Folk och fä HB 0.25 0.30

…..

BOTTM Bottom-Dollar Markets 0.25 0.30

ERNSH Ernst Handel 0.15 0.20

FRANK Frankenversand 0.05 0.10

(28 row(s) affected)

Due date: Wednesday, June 27, 2018 at 10:00 p.m.

100 Marks