A Quick Reminder of SQL Query
Summarize common sql query syntax.
Retrieving and Sorting Data
// select individual column
SELECT prod_name
FROM products;
// select multiple columns
SELECT prod_id, prod_name
FROM products;
SELECT *
FROM products;
// select distinct rows
SELECT DISTINCT vend_id
FROM products;
// limit results
SELECT TOP(5) prod_name
FROM products
SELECT TOP(25) percent prod_name
FROM products
SELECT prod_name
FROM products
LIMIT 100;
// sort results
SELECT prod_name
FROM products
ORDER BY prod_name
// it is also legal to sort data by a column that is not retrived
// sort by multiple columns
SELECT prod_id, prod_name, prod_name
FROM products
ORDER BY prod_price, prod_name
// the sort sequence is exactly as specified
// specify sort direction
SELECT prod_price, prod_name
FROM products
ORDER BY prod_price DESC;
// default is ASC
Filtering Data
// use the where clause
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;
// use where, between and, to check for a range of values
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
// combine where with operators
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;
// but notice that AND operators is processed before OR operators
// thus it is safer to use parentheses
SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
// the IN operator
SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;
// wildcard filtering, associated with the LIKE operator
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE ‘jet%’;
// within a search string, % means match any number of occurrences of any character
// the underscore _ matches only one single character
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE ‘_ ton anvil%’;
Calculated Fields
// concating fields, RTrim and using alias
SELECT RTrim(vend_name) + ‘ (‘ + RTrim(vend_country) + ‘)’ AS vend_title
FROM vendors
ORDER BY vend_name;
// other commonly used text manipulation functions such as
Len()
Lower()
Str()
Upper()
// date and time manipulation functions
DateAdd()
DateDiff()
DatePart()
GetDate()
...
Summarizing Data
// Aggregate functions
AVG()
Count()
// Count(*) includes NULL
// Count(column) will ignore NULL
Max()
Min()
Sum()
// Aggregate on distinct values
SELECT Avg(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
Grouping Data
// create groups
SELECT vend_id, Count(*) AS num_prods
FROM products
GROUP BY vend_id;
// group multiple columns will create nest groups
// filtering groups with HAVING, compared to where for rows
SELECT cust_id, Count(*) AS orders
FROM orders
GROUP BY cust_id
HAVING Count(*) >= 2;
// SELECT Clauses and their sequence
SELECT expressions
FROM tables
WHERE conditions
GROUP BY
HAVING
ORDER BY expression [ ASC | DESC ]
LIMIT number_rows [ OFFSET offset_value ];
Subqueries
// Subqueries are always processed starting with the innermost SELECT state- ment and working outward
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = ‘TNT2’));
Joining Tables
// inner joins
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;