Enhancements for Querying a Single Table

Order By

Follows WHERE and (GROUP BY)

SELECT * 
   FROM SKU_DATA
   ORDER BY Buyer;

Give a list of fields to sort on
The first will be the primary key,
The second, will be the secondary key ...

SELECT * 
   FROM SKU_DATA
   ORDER BY Buyer, Department;

ASC (by default) or DESC

SELECT * 
   FROM SKU_DATA
   ORDER BY Buyer, Department DESC;

Ranges in the WHERE clause

BETWEEN value AND value

SELECT * 
     FROM SKU_DATA 
     WHERE SKU BETWEEN 200000 AND 300000 
     ORDER BY Department,  SKU_Description DESC;

SELECT * 
    FROM SKU_DATA  
    WHERE Buyer BETWEEN 'D' AND 'K' 
    ORDER BY Department,  SKU_Description DESC;

You could do these with an and

SELECT * 
    FROM SKU_DATA  
    WHERE Buyer >= 'D' AND BUYER <= 'K' 
    ORDER BY Department,  SKU_Description DESC;

Wild Cards
- Reference Page
- "%" matches any set of characters, including 0
- "_" matches any single character.
- Keyword LIKE or NOT LIKE
- ```
SELECT *
    FROM SKU_DATA
    WHERE BUYER LIKE "N%";
	 
```
- I was somewhat surprised when this worked
- ```
SELECT * 
    FROM SKU_DATA 
    WHERE SKU LIKE "%1%";
	 
```
- What does this do?
- ```
SELECT * 
    FROM SKU_DATA 
    WHERE SKU_DESCRIPTION LIKE "%_, %";
	 
```
- Try this one with NOT LIKE
- I know I have a problem with Pete, somewhere there is an error.
- ```
SELECT * 
    FROM SKU_DATA 
    WHERE Buyer LIKE "P_te %";
	 
```
- Like almost everything else in MySQL the single characters seem to be case insensitive.

AS in the select command.

SELECT field AS alias

SELECT Buyer AS Person
   FROM SKU_DATA;

You can then use the alias in GROUP BY, ORDER BY and HAVING clauses.

SELECT Buyer AS Person
    FROM SKU_DATA 
    ORDER BY Person;

Calculations in Queries

You can use built in functions as part of a query.

SELECT SUM(QuantityOnHand)
   FROM INVENTORY;

SELECT SUM(QuantityOnHand) AS "Total Items"
   FROM INVENTORY;

SELECT AVG(QuantityOnHand) AS "Average"
   FROM INVENTORY;

SELECT AVG(QuantityOnHand) AS "Average"
     FROM INVENTORY 
     WHERE QuantityOnHand > 0;

SELECT SUBSTRING(Buyer,POSITION(' ' IN Buyer))
     FROM SKU_DATA;

SELECT DISTINCT SUBSTRING(Buyer,POSITION(' ' IN Buyer)+1) AS LastName
    FROM SKU_DATA 
    ORDER BY LastName;

SELECT COUNT(*) AS "Cindy's Items"
     FROM SKU_DATA 
     WHERE Buyer LIKE "% Lo";

SELECT SKU, Buyer 
     FROM SKU_DATA 
     WHERE Substring(Buyer,Position(' ' IN Buyer)+1)="Lo"
     ORDER BY SKU;

Five are grouping functions

Count, Sum, Avg, Min, MAX
You can not use such functions with other fields in the select clause
- ```
SELECT COUNT(*),SKU FROM SKU_DATA;
```
- Some DBMS will warn you of this, MySQL apparently does not.

You can not use a computation in the WHERE clause

SELECT SKU,Buyer 
    FROM SKU_DATA 
    WHERE Count(Substring(Buyer,Position(' ' IN Buyer)+1)) > 2;
	                 Mysql: ERROR 1111 (HY000): Invalid use of group function

More computations

SELECT DISTINCT CONCAT(Department,": ", Buyer) AS "Department/Buyer"
    FROM SKU_DATA;

SELECT SKU, QuantityOnHand, QuantityOnOrder, QuantityOnHand + QuantityOnOrder AS "Count Total" 
     FROM INVENTORY;

SELECT Quantity, Price, Quantity*Price,  ExtendedPrice 
    FROM ORDER_ITEM;

SELECT Quantity, Price, Quantity*Price , ExtendedPrice 
     FROM ORDER_ITEM 
     WHERE Quantity*Price = ExtendedPrice;