SQL 101.

COMMANDS

• SELECT <column-1> [, column-2] ...
• FROM <table-1>
     { INNER | LEFT OUTER | RIGHT OUTER} JOIN table-2
     ON table-1.column-1 {=|<|>|<=|>=|<>} table-2.column-2
• WHERE selection-criteria
• ORDER BY column-1 [ASC | DESC] [, column-2 [ASC | DESC]] ...
• INSERT INTO <table-name> [(column-list)]
• VALUES (value-list)
• UPDATE <table-name>
• SET  <expression-1> [, expression-2] ...
• WHERE selection-criteria
• DELETE FROM <table-name>

SELECT

SELECT ProductCode, Description, Price
FROM Products
WHERE Price > 50
ORDER BY ProductCode ASC

SELECT * FROM PRODUCTS

SELECT p.ProductCode, p.Price, li.Quantity,
            p.Price * li.Quantity AS Total
     FROM Products p
           INNER JOIN LineItems li
           ON p.ProductCode = li.ProductCode
     WHERE p.Price > 50
     ORDER BY p.ProductCode ASC;

SELECT p.ProductCode, p.Price, li.Quantity,
             p.Price * li.Quantity AS Total
     FROM Products p, LineItems li
     WHERE p.ProductCode = li.ProductCode AND p.Price > 50
     ORDER BY p.ProductCode ASC;

SELECT TOP(5) p.prod_name FROM products p, LineItem li
TABLESAMPLE (3 ROWS)
ORDER BY p.prod_price;

SELECT products.prod_name FROM products
ORDER By products.prod_price DESC, products.prod_name
TABLESAMPLE (50 PERCENT);

// Use [] to delimit the column name when there is space btwn
// and alias it using 'AS' to be 'LastName'
SELECT [Last Name] AS LastName

INSERT

INSERT INTO Products (ProductCode, Description, Price)
VALUES ('casp', 'ASP.NET Web Programming with C#', 54.50)

// without the column list
INSERT INTO Products
VALUES ('casp', 'ASP.NET Web Programming with C#', 54.50)

UPDATE

// Update a single row
UPDATE Products
SET Description =
       'Murach''s ASP.NET Web Programming with C#',
    Price = 49.50
WHERE ProductCode = 'casp'
// Update multiple rows
UPDATE Products
SET Price = 49.95
WHERE Price = 49.50

DELETE

DELETE FROM Products WHERE ProductCode = 'casp'
DELETE FROM Invoices WHERE AmountDue = 0
DELETE FROM Invoices

WHERE, IN, NOT

SELECT prod_name, prod_price
FROM products p, LineItem li
TABLESAMPLE (3 ROWS)
WHERE prod_price <> 10, prod_name = 'fuses'
ORDER BY price;

...
WHERE price BETWEEN 5 AND 10;
WHERE vend_id = 1002 AND price <= 10;
WHERE vend_id = 1002 OR vend_id = 2001 AND price <= 10;
WHERE prod_name LIKE 's%e'; // % is wildcard, 
WHERE prod_name LIKE 'jet%'; // matching any 'jet' in the beginning
WHERE prod_name LIKE '%anvil%'; // %...%, match anywhere

WHERE name LIKE '_ley'; // _ match one char
WHERE name LIKE '[EJ]%'; // match E or J as the start char
WHERE name LIKE '[^EJ]%'; // ^ NOT match, any name not begin with E or J
WHERE NOT name LIKE '[EJ]%'; // same as '[^EJ]%'

// WHERE OPERATOR:
// =, <>, !=, <, <=, !<, >, >=, !>, BETWEEN, IS NULL

Fields/Column Concatenation

SELECT name + ' (' + age + ')'
FROM students
ORDER BY name;

// T-SQL RTrim: trim all space from the right
//       LTrim: trim all space from the left
SELECT RTrim(name) + ' (' + LTrim(age) + ')' AS
student_title
FROM students
ORDER BY name;

Using MATH

SELECT id, quantity, price,
    quantity * price AS total_price
FROM orderlist
WHERE order_num > 2000;
// T-SQL math operators: + - * / %

Using Function

// Common Text Functions
// CharIndex()
// Left(), Right()
// Len()
// Lower(), Upper()
// LTrim(), RTrim()
// Replace()
// Soundex() : soundex value when they sound similar
// Str()
// SubString()

SELECT name, UPPER(name) AS name_upper
FROM orderlist
WHERE order_num > 2000;

// Common Data and Time functions
// DateAdd()
// DateDiff()
// DateName()
// DatePart()
// Day()
// GetData()
// Month()
// Year()

// Supported Date Parts:
// day (dd or d)
// dayofyear (dy or y)
// hour (hh)
// millisecond (ms)
// minute (mi or n)
// month (m or mm)
// quarter (q or qq)
// second (ss or s)
// week (wk or ww)
// weekday (DatePart() only), (dw)
// year (yy or yyyy)

SELECT name, 
    DatePart(weekday, orderdate) AS weekday
FROM orderlist;

// to return named weekdays instead of numbered day
SELECT name, 
    DateName(weekday, DatePart(weekday, orderdate) ) AS weekday
FROM orderlist;

// Day(),          Month(),         and Year() are shortcuts for 
// DatePart(day,), DatePart(month,) and DatePart(year,)

// when comparing dates, always use DateDiff()
SELECT name, id
FROM orderlist
WHERE DateDiff (month, order_date, '2005-09-01') = 0;

...
Where Year(order_date) = 2005 AND Month(order_date) = 9;

// Numeric Functions
// Abs()
// Cos()
// Exp()
// Pi()
// Rand()
// Round()
// Sin()
// Sqrt()
// Square()
// Tan()

// SQL Aggregate Functions
// Avg(), Count(), Max(), Min(), Sum()
SELECT Avg(price) AS avg_price // Null columns are ignored by Avg()
FROM products
WHERE id = 1003;

SELECT Count(*) AS num_customers // count all rows
FROM customers;
SELECT Count(customer_email) AS num_customers // count only those with emails
FROM customers;
SELECT Sum(price*quantity) AS total_price
FROM orderlist
WHERE order_num = 2000;

// Use ALL (default) or DISTINCT for unique items
SELECT Avg(DISTINCT price) AS avg_price_ofunique
FROM products
WHRE id = 1002;

Filtering Groups

// group by vend_id and calcute all products from that
// vendor
SELECT vend_id, Count(*) AS num_prods
FROM products
GROUP BY vend_id;

SELECT cust_id, Count(*) AS orders
FROM orderlist
WHERE price >= 10
GROUP BY cust_id
HAVING Count(*) >= 2;

// HAVING support all WHERE operators
// HAVING filters after data is grouped.
// WHERE filters before data is grouped 
//    (rows eliminated by WHERE are not included in the group)
//    (This can change the calculated values used in HAVING clause.

// ORDER BY
//    sorts generated output, can use any column, not required
// GROUP BY
//    groups rows but may not be in order,
//    only selected columns or expression columns may be used
//    every selected column expression must be used
//    required if using columns (or expressions) with Count/Avg/Sum/Min/Max

SELECT order_num, Sum(quantity*price) AS ordertotal
FROM orderlist
GROUP BY order_num
HAVING Sum(quantity*price) >= 50;
// ORDER BY ordertotal; // optional

SQL Servre 2005

% USE mydatabase;
% sp_databases;
% sp_tables;
% sp_tables NULL, dbo, mydatabase, "'TABLE'";
% sp_columns customers;
% sp_server_info;
% sp_space_used;
% sp_statistics;
% sp_helpuser;
% sp_helplogins;