SQL Query Development for Database Online Sales (Sales)
The database used here for the query development is loosely related to the design case Online Ordering System. There a few diffrences. Entity (table) Customer, is in this system an individual (person) rather than a companny. There are two additional entities: US_State (state{pk},stateName,region, division) and Category(catid{pk},description). Entity US_State is linked to Customer in order to better describe attribute Customer.state {fk}. Entity Category is associated with entity Product, connecting Product.ctid {fk} with Catagory.catid. The following diagram shows a logical data model (EERD):
Sales-EERD
Here is a MySQL Workbench version of this design: Logical Design for OOS.
Notice that initially, entity US_State is not used. It is added later via SQL-maintenance statements. A partial physical design is implemented in SQL, using the following script: SQL Script for OSS - Part 1. Use this script to build the OOS database in MySQL. If necessary follow the instruction for Building MySQL Databases, Using an SQL Script. When done, the OOS database will be open so you will be able to develop your queries.
Single-table queries:
Query S01: Select all products (show all the columns of table Product), having Product.price below 50.00. How?
SELECT * FROM Product WHERE price < 50;
+-----+-------------------------------------------+-----------+------+------+------+-------+
| pid | description                               | brand     | ctid | rp   | qoh  | price |
+-----+-------------------------------------------+-----------+------+------+------+-------+
|   1 | 2.4G Nano Wireless Mouse                  | TeckNet   |    1 |   30 |   20 |  9.95 |
|   2 | G230 Stereo Gaming Headset                | Logitech  |    3 |   20 |    5 | 39.95 |
|   3 | 64GB USB 3.0 Flash Drive                  | Samsung   |    4 |   50 |   75 | 17.95 |
|   4 | External USB DVD/CD                       | Epartsdom |    5 |   20 |   10 | 12.45 |
|   5 | WaveRest Gel Mouse Pad                    | Belkin    |    2 |   20 |   25 | 15.95 |
|   6 | Blu-Ray/DVD External Burner Drive         | Epartsdom |    5 |   10 |    7 | 35.55 |
|   7 | 4GB Cool Pizza Style USB Flash Drive      | Panda     |    4 |   10 |   12 |  7.95 |
|   8 | Wireless Bluetooth Keyboard               | TeckNet   |    6 |   10 |    8 | 12.95 |
|   9 | Ultra-Compact 14W Dual-Port Solar Charger | TeckNet   |    7 |    5 |    8 | 39.95 |
|  11 | HD Webcam C310                            | Logitech  |    8 |   15 |   20 | 45.95 |
|  13 | 16GB Mini Camera Pen                      | Dragonpad |    8 |    5 |    7 | 21.95 |
|  15 | Duet Bluetooth Tracker                    | PROTAG    |   10 |   10 |   15 | 31.95 |
+-----+-------------------------------------------+-----------+------+------+------+-------+
This query does a full projection (all the collumns) and partial selection (not all the rows).
Query S02: Select products (show all the columns of table Product) having the quantity on hand (Product.qoh) either below some value or above another value. How?
SELECT * FROM Product WHERE qoh < 10 OR qoh > 50;
+-----+-------------------------------------------+-----------+------+------+------+-------+
| pid | description                               | brand     | ctid | rp   | qoh  | price |
+-----+-------------------------------------------+-----------+------+------+------+-------+
|   2 | G230 Stereo Gaming Headset                | Logitech  |    3 |   20 |    5 | 39.95 |
|   3 | 64GB USB 3.0 Flash Drive                  | Samsung   |    4 |   50 |   75 | 17.95 |
|   6 | Blu-Ray/DVD External Burner Drive         | Epartsdom |    5 |   10 |    7 | 35.55 |
|   8 | Wireless Bluetooth Keyboard               | TeckNet   |    6 |   10 |    8 | 12.95 |
|   9 | Ultra-Compact 14W Dual-Port Solar Charger | TeckNet   |    7 |    5 |    8 | 39.95 |
|  13 | 16GB Mini Camera Pen                      | Dragonpad |    8 |    5 |    7 | 21.95 |
+-----+-------------------------------------------+-----------+------+------+------+-------+
This SQL statement is equivelant to the following ones:
SELECT * FROM Product WHERE NOT (qoh >= 10 AND qoh <= 50);
SELECT * FROM Product WHERE NOT qoh BETWEEN 10 AND 50;
Notice that logical expressions qoh < 10 OR qoh > 50 and qoh >= 10 AND qoh <= 50 are complementary (when one is TRUE the other is FALSE and vice versa.
Logical expressions qoh >= 10 AND qoh <= 50 and qoh BETWEEN 10 AND 50 are equivalent.
Challenge: Analyze the first alternative query and find out what would happen if we dropped the parentheses.
This query does a full projection (all the collumns) and partial selection (not all the rows).
Query S03: Select product ID (Product.pid), description (Product.description) and quantity on hand (Product.qoh) having the quantity on hand between certain values, for example, between 10 and 50. How?
SELECT pid, description, qoh FROM Product WHERE qoh BETWEEN 10 AND 50;
+-----+--------------------------------------+------+
| pid | description                          | qoh  |
+-----+--------------------------------------+------+
|   1 | 2.4G Nano Wireless Mouse             |   20 |
|   4 | External USB DVD/CD                  |   10 |
|   5 | WaveRest Gel Mouse Pad               |   25 |
|   7 | 4GB Cool Pizza Style USB Flash Drive |   12 |
|  10 | Virtual Reality Headset              |   12 |
|  11 | HD Webcam C310                       |   20 |
|  12 | HD Pro Webcam C920                   |   18 |
|  14 | Moto 360 Sport - 45mm                |   10 |
|  15 | Duet Bluetooth Tracker               |   15 |
+-----+--------------------------------------+------+
This SQL statement is equivelant to the following one:
SELECT pid, description, qoh FROM Product WHERE qoh >= 10 AND qoh <= 50;
As you can see, the BETWEEN operator is inclusive. It includes the limits.
Of course, you could adopt the previous query (S02) by negating (NOT) its WHERE-clause's logical-expression:
SELECT pid, description, qoh FROM Product WHERE NOT (qoh < 10 OR qoh > 50);
This query does a partial projection (some collumns) and partial selection (some rows).
Query S04: Select products from table Product that contain string cam (for camera) in column description. How?
SELECT * FROM Product WHERE description LIKE '%cam%';
+-----+----------------------+-----------+------+------+------+-------+
| pid | description          | brand     | ctid | rp   | qoh  | price |
+-----+----------------------+-----------+------+------+------+-------+
|  11 | HD Webcam C310       | Logitech  |    8 |   15 |   20 | 45.95 |
|  12 | HD Pro Webcam C920   | Logitech  |    8 |   10 |   18 | 75.45 |
|  13 | 16GB Mini Camera Pen | Dragonpad |    8 |    5 |    7 | 21.95 |
+-----+----------------------+-----------+------+------+------+-------+
Expression '%cam%' uses wildcard character '%' that stands for any number of characters, including none. One can also use wildcard character '_' that meches just one character.
This query can be written, using a regular expression, as follows:
SELECT * FROM Product WHERE description REGEXP 'cam';
SELECT * FROM Product WHERE description RLIKE 'cam';
This query does a full projection and partial selection.
Query S05: Show first five purchase orders (shopping cart records). How?
SELECT * FROM ShoppingCart LIMIT 5;
+------+---------------------+------+
| scid | poDate              | cid  |
+------+---------------------+------+
|    1 | 2018-01-01 23:03:54 |    2 |
|    2 | 2018-01-01 19:53:52 |    2 |
|    3 | 2018-01-02 18:44:54 |    8 |
|    4 | 2018-01-02 18:20:24 |   12 |
|    5 | 2018-01-02 22:06:56 |    5 |
+------+---------------------+------+
Without clause LIMIT, all records would be returned.
This query does a full projection and partial selection.
Query S06: Show names of customers from California (CA). Combine the first and last names as one data token. How?
SELECT CONCAT(fn,' ',ln) AS californian
FROM Customer
WHERE state='CA';
+-------------+
| californian |
+-------------+
| Win Miggy   |
| Zak Beron   |
| Gin Rum     |
| Xer Xes     |
+-------------+
Function CONCAT() combines its arguments into one string. Here, the first name is concatenated with a space, followed by the last name.
This query does a partial projection and partial selection.
Query S07: Find products that are supposed to be restocked because the inventory level (qoh) is equal or below the reorder point (rp). How?
SELECT * FROM Product WHERE qoh <= rp;
+-----+-----------------------------------+-----------+------+------+------+-------+
| pid | description                       | brand     | ctid | rp   | qoh  | price |
+-----+-----------------------------------+-----------+------+------+------+-------+
|   1 | 2.4G Nano Wireless Mouse          | TeckNet   |    1 |   30 |   20 |  9.95 |
|   2 | G230 Stereo Gaming Headset        | Logitech  |    3 |   20 |    5 | 39.95 |
|   4 | External USB DVD/CD               | Epartsdom |    5 |   20 |   10 | 12.45 |
|   6 | Blu-Ray/DVD External Burner Drive | Epartsdom |    5 |   10 |    7 | 35.55 |
|   8 | Wireless Bluetooth Keyboard       | TeckNet   |    6 |   10 |    8 | 12.95 |
+-----+-----------------------------------+-----------+------+------+------+-------+
This query does a full projection and partial selection.
Query S08: Find products with unit price (Product.price) not greater than $10. How?
SELECT * FROM Product WHERE NOT (price > 10);
+-----+--------------------------------------+---------+------+------+------+-------+
| pid | description                          | brand   | ctid | rp   | qoh  | price |
+-----+--------------------------------------+---------+------+------+------+-------+
|   1 | 2.4G Nano Wireless Mouse             | TeckNet |    1 |   30 |   20 |  9.95 |
|   7 | 4GB Cool Pizza Style USB Flash Drive | Panda   |    4 |   10 |   12 |  7.95 |
+-----+--------------------------------------+---------+------+------+------+-------+
The logical expression of the WHERE clause is equivalent to the following:
price <= 10
This query does a full projection and partial selection.
Query S09: Find product description (Product.description), quantity on hand (Product.qoh), and price (Product.price) having brand Logitech. How?
SELECT description, qoh, price
FROM Product
WHERE brand = 'Logitech';
+----------------------------+------+-------+
| description                | qoh  | price |
+----------------------------+------+-------+
| G230 Stereo Gaming Headset |    5 | 39.95 |
| HD Webcam C310             |   20 | 45.95 |
| HD Pro Webcam C920         |   18 | 75.45 |
+----------------------------+------+-------+
Notice that MySQL string matching is case insensitive so that the WHERE clause condition could also be written as:
brand = 'logitech' or brand = 'LOGITECH'
This query does a partial projection and partial selection.
Query S10: Find the number of purchase orders (shopping cart records) per customer based on customer ID (Customer.cid). How?
SELECT cid, Count(*) numOfPurchaseOrders
FROM ShoppingCart
GROUP BY cid;
+------+---------------------+
| cid  | numOfPurchaseOrders |
+------+---------------------+
|    1 |                   2 |
|    2 |                   5 |
|    3 |                   1 |
|    5 |                   3 |
|    6 |                   1 |
|    8 |                   1 |
|   10 |                   4 |
|   11 |                   2 |
|   12 |                   1 |
+------+---------------------+
The algorithm used by this query is referred to as Control-Break Processing (CBP). In Excel, it is implemented by the Data > Subtotal tool. It counts the records until a break happens (change in the GROUP BY expression value—here cid). Consider an ordered list of the top 3 customer IDs (SELECT cid FROM ShoppingCart WHERE cid <= 3;):
+------+
| cid  |
+------+ Control Break (Start)
|    1 | Control + Break: Initialize Count (1)
|    1 | Control: Increase Count (2)
|    2 | Control + Break: Print Count, Initialize Count (1)
|    2 | Control: Increase Count (2)
|    2 | Control: Increase Count (3)
|    2 | Control: Increase Count (4)
|    2 | Control: Increase Count (5)
|    3 | Control + Break: Print Count, Initialize Count (1)
+------+ Control + Break (End): Print Count
The CBP algorithm breaks every time a change in cid happens. Except for the first and last breaks, CBP prints the previous count and sets the new one count to 1. The first break is just the count initialization and the last break—print. It is important to note that the non-aggregate column expressions of the SELECT clause must be consistent with the column expression of the GROUP BY clause. In this example, they are identical (cid). It would be incorrect, for example, to have column poDate in the SELECT expression, since there may be different order dates for the same customer (cid).
This query performs an aggragation. In a way, it is a partial projections and full selection.
Query S11: Show IDs for the customers who have placed at least three purchasing orders. How?
SELECT cid AS 'cid with at least 3 POs'
FROM ShoppingCart
GROUP BY cid
HAVING Count(*) >= 3 ;
+-------------------------+
| cid with at least 3 POs |
+-------------------------+
|                       2 |
|                       5 |
|                      10 |
+-------------------------+
Check the outcome of the previous query to see that, indeed, customers 2, 5, and 10 have placed at least 3 purchasing orders. As you can see, a condition imposed on an aggregate must be placed in the HAVING clause (not in the WHERE clause).
This query performs an aggragation. It is a partial projections and partial selection.