Week 9 Practical
This week’s practical involves a little research ON how TO perform a couple OF complex tasks:
1.  Creating a Stored PROCEDURE that returns records IN a specific ORDER
2.  A rather complex UPDATE query involving a TABLE join.  This TYPE OF query IS often used TO fix errors WHERE a mistake has been made that affects data across a whole TABLE (the amount the customer paid IN this CASE).
Question 1: Find the NUMBER OF customers WITH the FIRST name Lisa, Anne OR Mary
Answer
CREATE PROCEDURE NumberOfCustomersWithName

AS

SELECT c.firstName, COUNT(c.customerID) AS customerCount

FROM Customers AS c

WHERE c.firstName = 'Lisa' OR c.firstName = 'Anne' OR c.firstName = 'Mary'

GROUP BY c.firstName

EXECUTE NumberOfCustomersWithName




Question 2: CREATE a Stored PROCEDURE that accepts a search parameter AND finds customer FIRST names OR LAST names that START WITH the entered VALUE.  IF no search text IS provided, ALL customer names are returned.  ORDER the results BY those WITH a matching firstName ascending FIRST (AND WHEN no search text IS provided), THEN LAST name ascending SECOND AND BY DEFAULT.
AS an example, IF you pass ‘Lis’ you should get records matching ‘Lisa’ FIRST AND ‘Lisandra’ SECOND!
Answer
CREATE PROCEDURE searchCustomers
@fName VARCHAR(100),
@lName VARCHAR(100)

AS

IF (@fName IS NULL AND @lName IS NULL)
BEGIN
    SELECT c.firstName, c.lastName

    FROM Customers AS c

    ORDER BY c.firstName ASC, c.lastName ASC
END

ELSE
BEGIN
    SELECT c.firstName, c.lastName

    FROM Customers AS c

    WHERE c.firstName LIKE @fName + '%' OR c.lastName LIKE @lName + '%'

    ORDER BY c.firstName ASC, c.lastName ASC
END

EXECUTE searchCustomers NULL,NULL



Question 3: UPDATE query WITH a TABLE join!
The profit margins ON the sales IN our store are way too small.  Your task IS TO “cook the books” AND inflate the amounts paid TO make it more realistic!  You will need TO research how TO “UPDATE a TABLE using a join” (you will find loads OF example via Google).  TO further complicate the issue, the UPDATE will be different depending ON the price OF the item (ie, you will need a CASE statement AS well!). The other thing you need TO take INTO account IS the qty OF an item purchased during the transaction.
USE the following mark-up guidelines FOR your UPDATE PROCEDURE:
1.  Price <= $15 THEN the sale price will be 5 times the CURRENT price
2.  Price > $15 AND <= $25, the sale price will be 3.5 times the CURRENT price
3.  Price > $25 AND <= $50, the sale price will be 2.5 times the CURRENT price
4.  Price > 50, the sale price will be 2 times the CURRENT price
It IS suggested you get the price calculation working using a SELECT - CASE query FIRST, THEN try TO incorporate the result INTO an UPDATE query.  Have your SELECT query checked before moving TO including it INTO the UPDATE query
Price SELECT Query
ANSWER
SELECT si.price,
        (
            CASE
            WHEN si.price <= 15
            THEN si.price*5

            WHEN si.price > 15 AND si.price <= 25
            THEN si.price*3.5

            WHEN si.price > 25 AND si.price <= 50
            THEN si.price*2.5

            ELSE
            si.price*2
            END
        )
        AS calc
        FROM ShopItems AS si
Price UPDATE Query
ANSWER
UPDATE CustomerPurchases
    SET CustomerPurchases.totalDue =
    (
        SELECT si.price,
        (
            CASE
            WHEN si.price <= 15
            THEN si.price*5

            WHEN si.price > 15 AND si.price <= 25
            THEN si.price*3.5

            WHEN si.price > 25 AND si.price <= 50
            THEN si.price*2.5

            ELSE
            si.price*2
            END
        )
        AS calc
        FROM ShopItems AS si
    )
    FROM CustomerPurchases JOIN ShopItems
    ON CustomerPurchases.itemID = ShopItems.itemID