SQL 102.

Concepts

• Primary Key vs Foreign Key
• Scale
• Schema
• Records/Rows
• Fields/Columns
• Using Alias to shorten SQL syntax

SELECT RTrim(name) + ' ( ' + RTrim(country) + ')' AS 
title
FROM Students
ORDER BY name;

SELECT name, contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'THISID';

Using JOIN

// to retrieve all order from the customer who ordered
// '123' 
SELECT id, name
FROM orderlist
WHERE id = (SELECT id
            FROM orderlist
            WHERE id = '123');

// Using JOIN for the same query
SELECT p1.id, p1.name
FROM orderlist AS p1, orderlist AS p2
WHERE p1.id = p2.id
  AND p2.id = '123';

// Standard Join/Inner Join vs Outer Join
// Natural Joins eliminate repeated columns from the inner join
SELECT c.*, o.order_num, o.order_date, oi.prod_id, 
       oi.quantity, OI.item_price
FROM customers AS c, orders AS o, orderitems as oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'FB';

// Use outer Join to include rows that have no related rows
// INNER JOIN example
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
  ON customers.cust_id = orders.cust_id

// OUTER JOIN
//    must use RIGHT or LEFT keyword to specify 
//    which table to include all rows
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id;

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
  ON orders.cust_id = customers.cust_id;

// Simplified OUTER JOIN
SELECT customers.cust_id, orders.order_num
FROM customers, orders
WHERE customers.cust_id *= orders.cust_id;

// Using JOIN with Aggregate Functions
SELECT customers.cust_name, 
       customers.cust_id,
       Count(orders.order_num) AS num_order
FROM customers LEFT INNER JOIN orders
  ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_name, 
         customers.cust_id;

SELECT customers.cust_name, 
       customers.cust_id,
       Count(orders.order_num) AS num_order
FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_name, 
         customers.cust_id;

SubQuery and Combined Queries Using UNION

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price >= 5 OR vend_id IN (1001,1002);

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price >= 5 
// without UNION ALL, SQL eliminate duplicate rows
UNION     
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);
// ORDER BY vend_id, prod_price;

Full-Text Searching

EXEC sp_fulltext_database 'enable';
CREATE FULLTEXT CATALOG catalog_my;
CREATE FULLTEXT INDEX ON productnotes (note_text)
KEY INDEX key_productnotes
ON catalog_my;
// KEY INDEX is used to provide the name of the table's primary key.

ALTER FULLTEXT CATALOG catalog_my REBUILD;
SELECT * FROM sys.fulltext_catalogs;

// FulltextCatalogProperty() function
//    accepts a catalog name and the property to be checked
//    IndexSize
//    PopulateStatus

// FREETEX - simple search, matching by meaning or exact text match
// CONTAINS - search for phrases, synonyms
SELECT note_id, note_text
FROM notes
WHERE note_text LIKE '%bird food%';

SELECT note_id, note_text
FROM notes
WHERE FREETEXT(note_text, 'bird food'); // look for anything that
                                        // could mean bird food

// WHERE CONTAINS (note_text, '"iron*"'); // match anything with iron
// WHERE CONTAINS (note_text, 'bird food');
//    CONTAINS is functionally identical to LIKE note_text = '%match%'
//    CONTAINS search typically is quicker, especially as table size
//        increases.

// More CONTAIN examples
WHERE CONTAINS (note_text, 'safe AND sound');
WHERE CONTAINS (note_text, 'bird AND NOT food');
WHERE CONTAINS (note_text, 'grass NEAR cheese');
// look for any words that share the same stem as 'vary', such as 'varies'
WHERE CONTAINS (note_text, 'FORMSOF (INFLECTIONAL, vary)');

// Ranking
// The following query use FREETEXTTABLE function to return a table that
//     contain words meaning bird and food and gives the table an alias 
//     of 'f'
SELECT f.rank, note_id, note_text
FROM notes,
    FREETEXTTABLE (notes, note_text, 'bird food') f
WHERE notes.note_id = f.[key]
ORDER BY rank DESC;