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;