Part 01 In this part you will learn how to create and use triggers: a. Create a
ID: 3600633 • Letter: P
Question
Part 01 In this part you will learn how to create and use triggers: a. Create a table called Customer_Archives that has the same columns as customers table and it also has a column that is called “Deletion_Date” b. Create a Trigger on table Customers so that every time a record is deleted from table customers this same record is inserted in table Customer_Archives with the current date as Deletion_Date c. Now insert a new customer into table Customers and then delete it. Is the record inserted correctly into table Customers_Archive? Part 02 In this part we will learn how to use functions: a. Create a functions that has the Customer_id as input and returns a number that represents the total cost of orders for this customer. Notice that you will need to multiply the Order_Qty from table order_details and the Unit_Price from table items to get the total cost of each order. b. Now use the function: Write a select statement that selects all the customers and the total cost of the orders they have ever made. Part 03 Write a select statement that selects the second highest invoice_total. Part 04 Now let’s create a view: a. Create a view that holds information (name and phone numbet) for bad payers i.e. the payers that have a payment date that is later (larger) than the invoice due date or have a payment date that is null. Include also the invoice_id. b. Use the view: write a select statement to find the bad payers whose name starts with ‘B’. Part 05 In this part we create a small procedure: a. Write a procedure that has as input the customer_id. Then the procedure should delete the corresponding customer from table customers. b. Notice that customer_id is a foreign key on table orders. Therefore the customer cannot be deleted if he has placed orders. To delete a record from customers you should first delete the corresponding orders and to do that you need to first delete the corresponding order_details. c. Now execute the procedure to delete customer with id 1. If you have solved part 01 a record will be inserted into Customer_Archives. d. ROLLBACK; and all these changes will be undone! Part 06 Use the metadata tables for users to count how many tables and how many indexes you have created in your database.
Explanation / Answer
Answered first 4 questions
Part 01: Lets say Customers table has columns
CustomerID, CustomerName, Address, CreationDate
a. CREATE TABLE Customer_Archives(
CustomerID INT NOT NULL,
CustomerName VARCHAR(1000) NOT NULL,
Address VARCHAR(MAX),
CreationDate DateTime,
DeletionDate DateTime
)
b.
c.
VALUES (1,'Jane Doe','Park Street, London', GETDATE())
-- Deletion of records
DELETE FROM Customers WHERE CustomerID = 1
Part02:
a.
CREATE FUNCTION dbo.udfGetTotalCosts(@CustomerID int)
RETURNS decimal(10,2) AS
BEGIN
DECLARE @ret decimal(10,2);
SELECT @ret = SUM(od.Order_Qty*up.Price)
FROM order_details od
INNER JOIN Unit_Price up
ON od.UnitID = up.UnitID
WHERE od.CutomerID = @CustomerID
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
GO
b. SELECT CustomerID, CustomerName, dbo.udfGetTotalCosts(CustomerID)
FROM dbo.Customers