Challenges

Write a SQL statement to retrieve all customer ids (cust_id) from the Customers table.

SELECT cust_id
FROM Customers;

The OrderItems table contains every item ordered (and some were ordered multiple times). Write a SQL statement to retrieve a list of the products (prod_id) ordered (not every order, just a unique list of products). Here’s a hint, you should end up with seven unique rows displayed.

SELECT DISTINCT prod_id
FROM OrderItems;

Write a SQL statement that retrieves all columns from the Customers table, and an alternate SELECT that retrieves just the customer id. Use comments to comment out one SELECT so as to be able to run the other. (And of course, test both statements).

SELECT *
# SELECT cust_id
FROM Customers;

Write a SQL statement to retrieve all customer names (cust_name) from the Customers table, and display the results sorted from Z to A.

SELECT cust_name
FROM Customers
ORDER BY cust_name DESC;

Write a SQL statement to retrieve customer id (cust_id) and order number (order_num) from the Orders table, and sort the results first by customer id, and then by order date in reverse chronological order.

SELECT cust_id, order_num
FROM Orders
ORDER BY cust_id, order_date DESC;

Our fictitious store obviously prefers to sell more expensive items, and lots of them. Write a SQL statement to display the quantity and price (item_price) from the OrderItems table, sorted with the highest quantity and highest price first.

SELECT quantity, item_price
FROM OrderItems
ORDER BY quantity DESC, item_price DESC;

What is wrong with the following SQL statement? (Try to figure it out without running it):

SELECT vend_name,
FROM Vendors
ORDER vend_name DESC;

Write a SQL statement to retrieve the product id (prod_id) and name (prod_name) from the Products table, returning only products with a price of 9.49.

SELECT prod_id, prod_name
FROM Products
WHERE prod_price = 9.49;

Write a SQL statement to retrieve the product id (prod_id) and name (prod_name) from the Products table, returning only products with a price of 9 or more.

SELECT prod_id, prod_name
FROM Products
WHERE prod_price >= 9;

Now you’ll test what you learned in Chapter 5 and this chapter. Lessons 3 and 4. Write a SQL statement that retrieves the unique list of order numbers (order_num) from the OrderItems table which contain 100 or more of any item.

SELECT DISTINCT order_num
FROM OrderItems
WHERE quantity >=100;

One more. Write a SQL statement which returns the product name (prod_name) and price (prod_price) from Products for all products priced between 3 and 6. Oh, and sort the results by price. (There are multiple solutions to this one and we’ll revisit it in the next lesson, but you can solve it using what you’ve learned thus far).

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 3 AND 6
ORDER BY prod_price;

Write a SQL statement to retrieve the vendor name (vend_name) from the Vendors table,  and returning only vendors in California. (This requires filtering by both country [USA] and state [CA]; after all, there could be a California outside of the United States.). Here’s a hint: The filter requires matching strings.

SELECT vend_name
FROM Vendors
WHERE vend_state = "CA" AND vend_country = "USA";

Write a SQL statement to find all orders where at least 100 of items BR01, BR02, or BR03 were ordered. You’ll want to return the order number (order_num), product ID (prod_id), and quantity for the OrderItems table, and filtering by both the product ID and quantity. Here’s a hint: Depending on how you write your filter, you may need to pay special attention to the order of evaluation.

SELECT order_num
FROM OrderItems
WHERE quantitiy >=100
 AND prod_id IN ("BR01", "BR02", "BR03")

Now let’s revisit a challenge from the previous lesson. Write a SQL statement that returns the product name (prod_name) and price (prod_price) from Products for all products priced between 3 and 6. Use the AND operator and sort the results by price.

SELECT prod_name, prod_price
FROM products
WHERE prod_price >= 3 AND prod_price <= 6
ORDER BY prod_price;

What is wrong with the following SQL statement? (Try to figure it out without running it).

SELECT vend_name
FROM Vendors
ORDER BY vend_name
WHERE vend_country = 'USA' AND vend_state = 'CA';

Write a SQL statement to retrieve the product name (prod_name) and description (prod_desc) from the Products table, returning only products where the word toy is in the description.

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%';

Now let’s flip things around. Write a SQL statement to retrieve the product name (prod_name) and description (prod_desc) from the Products table, returning only products where the word toy doesn’t appear in the description. And this time, sort the results by product name.

SELECT prod_name, prod_desc
FROM Products
WHERE NOT prod_desc LIKE '%toy%'
ORDER BY prod_name;

Write a SQL statement to retrieve the product name (prod_name) and description (prod_desc) from the Products table, returning only products where both the words toy and carrots appear in the description. There are a couple of ways to do this, but for this challenge use AND and two LIKE comparisons.

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%';

This next one is a little trickier. I didn’t show you this syntax specifically, but see if you can figure it out anyway based on what you have learned thus far. Write a SQL statement to retrieve the product name (prod_name) and description (prod_desc) from the Products table, returning only products where both the words toy and carrots appear in the description in that order (the word toy before the word carrots). Here’s a hint, you’ll only need one LIKE with 3 % symbols to do this.

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%carrots%';

Use regular expressions to return all products whose names that ends with a number.

SELECT prod_name
FROM Products
WHERE prod_name REGEXP ".*[[:digit:]]$"

In this Lesson chapter, you learned how to use REGEXP to match text containing digits. Can you figure out how to match only products with no digits in their names? Here’s a hint: You can negate the an entire match (as you learned in Lesson 7, “Advanced Data Filtering”).

SELECT prod_name
FROM Products
WHERE NOT prod_name REGEXP "[[:digit:]]"

This final one is a little trickier. Some of the products listed in products have names that are made up of more than one word. Use regular expressions to return only the products with names made up of three or more words. Here’s a hint: Look for spaces between the words.

SELECT prod_name
FROM Products
WHERE prod_name REGEXP ".+ .+ .+"

A common use for aliases is to rename table column fields in retrieved results (perhaps to match specific reporting or client needs). Write a SQL statement that retrieves vend_id, vend_name, vend_address, and vend_city from Vendors, renaming vend_name to vname, vend_city to vcity, and vend_address to vaddress. Sort the results by vendor name (you can use the original name or the renamed name).

SELECT vend_id,
        vend_name as vname,
        vend_address AS vaddress,
        vend_city AS vcity
FROM Vendors
ORDER BY vname;

Our example store is running a sale and all products are 10% off. Write a SQL statement that returns prod_id, prod_price, and sale_price from the Products table. sale_price is a calculated field that contains, well, the sale price. Here’s a hint, you can multiply by 0.9 to get 90% of the original value (and thus the 10% off price).

SELECT prod_id, prod_price,
       prod_price * 0.9 AS sale_price
FROM Products;

Our store is now online, and customer accounts are being created. All Every users needs a login, and the default login will be a combination of their a user’s name and city. Write a SQL statement that returns customer ID (cust_id), customer name (customer_name), and user_login, which is all uppercase and composed of the first two characters of the customer contact (cust_contact) and the first three characters of the customer city (cust_city). So, for example, my login (Ben Forta, living in Oak Park) would be BEOAK. Here’s a hint: For this one, you’ll use functions, concatenation, and an alias.

SELECT cust_id, cust_name,
        CONCAT(UPPER(LEFT(cust_contact, 2)), UPPER(LEFT(cust_city, 3))) AS user_login
FROM customers;

Write a SQL statement to return the order number (order_num) and order date (order_date) for all orders placed in October 2023, sorted by order date. You should be able to figure this out based on what you have learned thus far, but feel free to consult your DBMS documentation as needed.

SELECT order_num, order_date
FROM Orders
WHERE YEAR(order_date) = 2023 AND MONTH(order_date) = 10
ORDER BY order_date;

Write a SQL statement to determine the total number of items sold (using the quantity column in OrderItems).

SELECT SUM(quantity) AS items_ordered
FROM OrderItems;

Modify the statement you just created to determine the total number of products with an id (prod_id) BR01 sold.

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE prod_id = 'BR01';

This is a bit of a silly one, but imagine that a customer wanted wants to buy one of every single item in the Products table. Write a SQL statement to calculate the total price.

SELECT SUM(prod_price) AS total_price
FROM Products;

Write a SQL statement to determine the price (prod_price) of the most expensive item in the Products table which costs no more than 10. Name the calculated field max_price.

SELECT MAX(prod_price) AS max_price
FROM Products
WHERE prod_price <= 10;

The OrderItems table contains the individual items for each order. Write a SQL statement that returns the number of lines (as order_lines) for each order number (order_num) and sort the results buy order_lines.

SELECT order_num, COUNT(*) as order_lines
FROM OrderItems
GROUP BY order_num
ORDER BY order_lines;

Write a SQL statement that returns a field named cheapest_item which contains the lowest cost item for each vendor (using prod_price in the Products table), and sort the results from lowest to highest cost.

SELECT vend_id, MIN(prod_price) AS cheapest_item
FROM Products
GROUP BY vend_id
ORDER BY cheapest_item;

It’s important to identify the best customers, so write a SQL statement to return the order number (order_num in OrderItems table) for all orders of at least 100 items.

SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING SUM(quantity) >= 100
ORDER BY order_num;

Another way to determine the best customers is by how much they have spent. Write a SQL statement to return the order number (order_num in OrderItems table) for all orders with a total price of at least 1000. Hint, for this one you’ll need to calculate and sum the total (item_price multiplied by quantity). Sort the results by order number.

SELECT order_num, SUM(item_price*quantity) AS total_price
FROM OrderItems
GROUP BY order_num
HAVING SUM(item_price*quantity) >= 1000
ORDER BY order_num;

What is wrong with the following SQL statement? (Try to figure it out without running it):

SELECT order_num, COUNT() AS items
FROM OrderItems
GROUP BY items
HAVING COUNT() >= 3
ORDER BY items, order_num;

Using a subquery, return a list of customers who bought items priced 10 or more. You’ll want to use the OrderItems table to find the matching order numbers (order_num), and then the Orders table to retrieve the customer id (cust_id) for those matched orders.

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
                    WHERE item_price >= 10);

You need to know the dates when product BR01 was ordered. Write a SQL statement that uses a subquery to determine which orders (in OrderItems) purchased prod_id BR01, and then returns customer id (cust_id) and order date (order_date) for each from the Orders table. Sort the results by order date.

SELECT cust_id, order_date
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
                    WHERE prod_id = 'BR01')
ORDER BY order_date;

Now let’s make it a bit more challenging. Update the previous challenge to return the customer email (cust_email in the Customers table) for any customers who purchased item with a prod_id of BR01. Hint, this involves the SELECT statement, the innermost one returning order_num from OrderItems, and the middle one returning cust_id from Orders.

SELECT cust_email FROM Customers
WHERE cust_id IN (SELECT cust_id
                  FROM Orders
                  WHERE order_num IN (SELECT order_num
                                      FROM OrderItems
                                      WHERE prod_id = 'BR01'));

We need a list of customer ids with the total amount they have ordered. Write a SQL statement to return customer id (cust_id in Orders table) and total_ordered using a subquery to return the total of orders for each customer. Sort the results by amount spent from greatest to the least. Hint, you’ve used the SUM() to calculate order totals previously.

SELECT cust_id,
       (SELECT SUM(quantity * item_price)
        FROM OrderItems
        WHERE order_num IN (SELECT order_num
                            FROM Orders
                            WHERE Orders.cust_id = Customers.cust_id)) AS total_ordered
FROM Customers
ORDER BY total_ordered DESC;

Write a SQL statement that retrieves all product names (prod_name) from the Products table, along with a calculated named quant_sold containing the total number of this item sold (retrieved using a subquery and a SUM(quantity) on the OrderItems table).

SELECT prod_name,
       (SELECT Sum(quantity)
        FROM OrderItems
        WHERE Products.prod_id=OrderItems.prod_id) AS quant_sold
FROM Products;

Write a SQL statement to return customer name (cust_name) from the Customers table and related order numbers (order_num) from the Orders table, sorting the result by customer name and then by order number. Actually, try this one twice, once using simple equijoin syntax and once using an INNER JOIN.

-- Equijoin syntax
SELECT cust_name, order_num
FROM Customers, Orders
WHERE Customers.cust_id = Orders.cust_id
ORDER BY cust_name, order_num;

-- ANSI INNER JOIN syntax
SELECT cust_name, order_num
FROM Customers INNER JOIN Orders
 ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name, order_num;

Let’s make the previous challenge more useful. In addition to returning the customer name and order number, add a third column named OrderTotal containing the total price of each order. There are two ways to do this, you can create the OrderTotal column using a subquery on the OrderItems table, or you can join the OrderItems table to the existing tables and use an aggregate function. Here’s a hint, watch out for where you need to use fully qualified column names.

-- Solution using subqueries
SELECT cust_name,
       order_num,
       (SELECT Sum(item_price*quantity)
       FROM OrderItems
       WHERE Orders.order_num=OrderItems.order_num) AS OrderTotal
FROM Customers, Orders
WHERE Customers.cust_id = Orders.cust_id
ORDER BY cust_name, order_num;

-- Solution using joins
SELECT cust_name,
       Orders.order_num,
       Sum(item_price*quantity) AS OrderTotal
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND Orders.order_num = OrderItems.order_num
GROUP BY cust_name, Orders.order_num
ORDER BY cust_name, order_num;

Let’s revisit Challenge 2 from Lesson 14. Write a SQL statement that retrieves the dates when product BR01 was ordered, but this time use a join and simple equijoin syntax. The output should be identical to the one from Lesson 14.

SELECT cust_id, order_date
FROM Orders, OrderItems
WHERE Orders.order_num = OrderItems.order_num
 AND prod_id = 'BR01'
ORDER BY order_date;

That was fun, let’s try it again. Recreate the SQL you wrote for Lesson 11 Challenge 3, this time using ANSI INNER JOIN syntax. The code you wrote there employed two nested subqueries, and to recreate it you’ll need two INNER JOIN statements, each formatted like the INNER JOIN example earlier in this lesson. And don’t forget the WHERE clause to filter by prod_id.

SELECT cust_email
FROM Customers
 INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
 INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
WHERE prod_id = 'BR01';

One more, and to make things more fun we’ll mix joins, aggregates functions, and grouping, too. Ready? Back in Lesson 10 I issued you a Challenge to find all order numbers with a value of 1000 or more. Those results are useful, but what would be even more useful is the name of the customers who placed orders of at least that amount. So, write a SQL statement that uses joins to return customer name (cust_name) from the Customers table, and the total price of all orders from the OrderItems table.

Here’s a hint, to join those tables you’ll also need to include the Orders table (as Customers is not related directly to OrderItems, Customers is related to Orders and Orders is related to OrderItems). Don’t forget the GROUP BY and HAVING, and sort the results by customer name. You can use simple equijoin or ANSI INNER JOIN syntax for this one. Or, if you are feeling brave, try writing it both ways.

-- Equijoin syntax
SELECT cust_name, SUM(item_price*quantity) AS total_price
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND Orders.order_num = OrderItems.order_num
GROUP BY cust_name HAVING SUM(item_price*quantity) >= 1000
ORDER BY cust_name;

-- ANSI INNER JOIN syntax
SELECT cust_name, SUM(item_price*quantity) AS total_price
FROM Customers
 INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
 INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
GROUP BY cust_name
HAVING SUM(item_price*quantity) >= 1000
ORDER BY cust_name;

Write a SQL statement using an INNER JOIN to retrieve customer name (cust_name in Customers) and all order numbers (order_num in Orders) for each.

SELECT cust_name, order_num
FROM Customers
 JOIN Orders ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name;

Modify the SQL statement you just created to list all customers, even those with no orders.

SELECT cust_name, order_num
FROM Customers
 LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name;

Use an OUTER JOIN to join the Products and OrderItems tables, returning a sorted list of product names (prod_name) and the order numbers (order_num) associated with each.

SELECT prod_name, order_num
FROM Products LEFT OUTER JOIN OrderItems
 ON Products.prod_id = OrderItems.prod_id
ORDER BY prod_name;

Modify the SQL statement created in the previous challenge so that it returns a total of number of orders for each item (as opposed to the order numbers).

SELECT prod_name, COUNT(order_num) AS orders
FROM Products LEFT OUTER JOIN OrderItems
 ON Products.prod_id = OrderItems.prod_id
GROUP BY prod_name
ORDER BY prod_name;

Write a SQL statement to list vendors (vend_id in Vendors) and the number of products they have available, including vendors with no products. You’ll want to use an OUTER JOIN and the COUNT() aggregate function to count the number of products for each in the Products table. Pay attention, the vend_id column appears in multiple tables so any time you refer to it you’ll need to fully qualify it.

SELECT Vendors.vend_id, COUNT(prod_id)
FROM Vendors
 LEFT OUTER JOIN Products ON Vendors.vend_id = Products.vend_id
GROUP BY Vendors.vend_id;

Write a SQL statement that combines two SELECT statements that retrieve product id (prod_id) and quantity from the OrderItems table, one filtering for rows with a quantity of exactly 100, and the other filtering for products with an ID that begins with BNBG. Sort the results by product id.

SELECT prod_id, quantity FROM OrderItems
WHERE quantity = 100
UNION
SELECT prod_id, quantity FROM OrderItems
WHERE prod_id LIKE 'BNBG%'
ORDER BY prod_id;

Rewrite the SQL statement you just created to use a single SELECT statement.

SELECT prod_id, quantity FROM OrderItems
WHERE quantity = 100 OR prod_id LIKE 'BNBG%'
ORDER BY prod_id;

This one is a little nonsensical, I know, but it does reinforce a note earlier in this lesson. Write a SQL statement which returns and combines product name (prod_name) from Products and customer name (cust_name) from Customers, and sort the result by product name.

SELECT prod_name
FROM Products
UNION
SELECT cust_name
FROM Customers
ORDER BY prod_name;

What is wrong with the following SQL statement? (Try to figure it out without running it):

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state  = 'MI'
ORDER BY cust_name;
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'IL'
ORDER BY cust_name;

Write a SQL statement using that uses full-text searching to return all rows that contain the word safe but that do not contain the word handsaw.

SELECT note_text
FROM ProductNotes
WHERE Match(note_text)
      Against('+safe -handsaw' IN BOOLEAN MODE);

Write a SQL statement that uses using full-text searching to return all rows that contain the words drop, dropped, dropping, and … actually, any other word that begins with drop.

SELECT note_text
FROM ProductNotes
WHERE Match(note_text)
      Against('+drop*' IN BOOLEAN MODE);

Using INSERT and columns specified, add yourself to the Customers table.

-- Obviously replace the details with your own
INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_email)
VALUES(1000000042,
       'Ben''s Toys',
       '123 Main Street',
       'Oak Park',
       'MI',
       '48237',
       'USA',
       'ben@fortabbwpc.wpcomstaging.com');

Using INSERT SELECT, make backup copies of your Orders and OrderItems tables.

INSERT INTO Orders_Backup(order_num, order_date, cust_id)
SELECT order_num, order_date, cust_id FROM Orders;
INSERT INTO OrderItems_Back(order_num, order_item, prod_id, quantity, item_price)
SELECT order_num, order_item, prod_id, quantity, item_price FROM OrderItems;

In the United States, state name abbreviations should always be in upper case. Write a SQL statement to update all USA addresses, both vendor states (vend_state in Vendors) and customer states (cust_state in Customers) so that they are upper case. To do this, you’ll need a function that converts text to uppercase (refer to Chapter 11 if needed) and a WHERE clause to filter just U.S. addresses.

UPDATE Vendors
SET vend_state = UPPER(vend_state)
WHERE vend_country = 'USA';
UPDATE Customers
SET cust_state = UPPER(cust_state)
WHERE cust_country = 'USA';

Challenge 1 in Lesson 15 asked you to add yourself to the Customers table. Now delete yourself. Make sure to use a WHERE clause (and test it with a SELECT before using it in DELETE) or you’ll delete all customers!

-- First test the WHERE to make sure it selects only what you want to delete
SELECT * FROM Customers
WHERE cust_id = 1000000042;
-- Then do it!
DELETE Customers
WHERE cust_id = 1000000042;

Add a web site column (vend_web) to the Vendors table. You’ll want a text field big enough to accommodate a URL.

ALTER TABLE Vendors
ADD vend_web CHAR(100);

Use UPDATE statements to update Vendors records to include a website (you can make up any address).

UPDATE Vendors
SET vend_web = 'website.com'
WHERE vend_id = 'DLL01';

Create a view called CustomersWithOrders that contains all of the columns in Customers, but only includes those who have placed orders. Hint, you can JOIN the Orders table to filter just the customers you want. Then use a SELECT to make sure you have the right data.

CREATE VIEW CustomersWithOrders AS
SELECT Customers.cust_id,
       Customers.cust_name,
       Customers.cust_address,
       Customers.cust_city,
       Customers.cust_state,
       Customers.cust_zip,
       Customers.cust_country,
       Customers.cust_contact,
       Customers.cust_email
FROM Customers
JOIN Orders ON Customers.cust_id = Orders.cust_id;
SELECT * FROM CustomersWithOrders;

What is wrong with the following SQL statement? (Try to figure it out without running it):

CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM OrderItems
ORDER BY order_num;

Create a stored procedure that accepts an customer ID and returns all orders made by that customer.

DELIMITER //

CREATE PROCEDURE GetCustomerOrders (
   IN cust INT
)

BEGIN
   SELECT order_num
   FROM Orders
   WHERE cust_id = cust;
END //

DELIMITER ;

Different locations have different tax rates. The ordertotal stored procedure hard- coded the tax rate to as 6%. Update the that stored procedure so that it accepts the tax rate to use, if one is needed.

Here’s a hint: You actually don’t need another parameter, you but could replace the taxable flag so to accept a tax rate, with and 0 meanings no taxes (yay!).

DELIMITER //

-- Name: ordertotal
-- Parameters: onumber = order number
--             taxrate = 0 if not taxable, else tax rate
--             ototal  = order total variable

CREATE PROCEDURE ordertotal(
   IN onumber INT,
   IN taxrate INT,
   OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN

   -- Declare variable for total
   DECLARE total DECIMAL(8,2);

   -- Get the order total
   SELECT Sum(item_price*quantity)
   FROM orderitems
   WHERE order_num = onumber
   INTO total;

   -- Is this taxable?
   IF taxrate >0 THEN
      -- Yes, so add taxrate to the total
      SELECT total+(total/100*taxrate) INTO total;
   END IF;

   -- And finally, save to out variable
   SELECT total INTO ototal;

END //

DELIMITER ;