SQL Basics

• Fully qualified column names take time to type

SELECT Products.prod_id,
       Products.prod_name
FROM   Products; 

• An alias saves you from repeatedly writing the full table name when referencing a column
• Once an alias is defined, use that alias

SELECT P.prod_id,
       P.prod_name
FROM   Products AS P; 

• Table aliases substantially reduce typing when writing joins

SELECT C.cust_name AS KlantNaam,
       C.cust_contact AS KlantContact,
       P.prod_name AS ProductNaam,
       V.vend_name AS Leverancier
FROM   Customers AS C
       INNER JOIN Orders       AS O   ON C.cust_id = O.cust_id
       INNER JOIN OrderItems   AS OI  ON O.order_num = OI.order_num
       INNER JOIN Products     AS P   ON OI.prod_id = P.prod_id
       INNER JOIN Vendors      AS V   ON P.vend_id = V.vend_id
WHERE  OI.prod_id = 'RGAN01';							

• Oracle does not allow AS when defining a table alias
• In most DBMSs, AS is optional for table aliases

-- Oracle / MySQL / SQL Server
SELECT C.cust_name KlantNaam,
       C.cust_contact KlantContact,
       P.prod_name ProductNaam,
       V.vend_name Leverancier
FROM   Customers C
       INNER JOIN Orders O ON C.cust_id = O.cust_id
       INNER JOIN OrderItems OI ON O.order_num = OI.order_num
       INNER JOIN Products P ON OI.prod_id = P.prod_id
       INNER JOIN Vendors V ON P.vend_id = V.vend_id
WHERE  OI.prod_id = 'RGAN01';							

Experienced SQL users often omit AS for table aliases

• A self join joins a table to itself
• Aliases are required
• This can also be done with a subquery

Select all customer contacts who work for the same company as 'Jim Jones'

Using a subquery

SELECT   cust_id,
         cust_name,
         cust_contact
FROM     Customers
WHERE    cust_name = ( SELECT cust_name
                       FROM   Customers
                       WHERE  cust_contact = 'Jim Jones' ) ;

SELECT   c1.cust_id, c1.cust_name, c1.cust_contact
FROM     Customers c1, Customers c2
WHERE    c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';
	

SELECT   c1.cust_id, c1.cust_name, c1.cust_contact
FROM     Customers c1
         INNER JOIN Customers c2 ON c1.cust_name = c2.cust_name
WHERE    c2.cust_contact = 'Jim Jones';

Inner join

• An inner join returns rows where the keys match

SELECT <column1>, <column2>
FROM   <table1>
       INNER JOIN <table2> ON <table1>.<key> = <table2>.<key>	

• A left outer join returns matching rows plus all unmatched rows from the first table in FROM

SELECT <column1>, <column2>
FROM   <tableA> LEFT OUTER JOIN <tableB> ON <tableA>.<key> = <tableB>.<key>	

• A right outer join returns matching rows plus all unmatched rows from the second table in FROM

SELECT <column1>, <column2>
FROM   <tableA> RIGHT OUTER JOIN <tableB> ON <tableA>.<key> = <tableB>.<key>	

• LEFT and RIGHT refer to the order in which the tables are named in FROM
• RIGHT OUTER JOIN support in SQLite depends on the version. If unsupported, reverse the table order and use LEFT OUTER JOIN to obtain the same result

SELECT   <column1>,
         <column2>	
FROM     <tableA> LEFT OUTER JOIN <tableB> ON <tableA>.<key> = <tableB>.<key> 			

SELECT   <column1>,
         <column2>	
FROM     <tableB> RIGHT OUTER JOIN <tableA> ON <tableA>.<key> = <tableB>.<key> 			

• Both examples above return the same result: matching rows plus all unmatched rows from table A

Select the names of vendors supplying products priced below 6

-- Oracle / MySQL / SQL Server
SELECT	DISTINCT V.vend_name
FROM	Vendors V
	INNER JOIN Products P ON V.vend_id = P.vend_id
WHERE	P.prod_price < 6;

-- Oracle / MySQL / SQL Server	
SELECT	DISTINCT V.vend_name
FROM	Vendors V
	LEFT OUTER JOIN Products P ON V.vend_id = P.vend_id
WHERE	P.prod_price < 6;

Select the names of customers who have and have not placed an order, together with the corresponding order numbers.

-- Oracle / MySQL / SQL Server
SELECT C.cust_name,
       O.order_num
FROM   Customers C
       LEFT OUTER JOIN Orders O ON C.cust_id = O.cust_id;

• A full outer join returns matching rows plus all unmatched rows from both tables
• Full outer joins are used less often
• MySQL, MariaDB and MS Access do not directly support FULL OUTER JOIN; SQLite support depends on the version

SELECT <column1>, <column2>
FROM   <tableA> FULL OUTER JOIN <tableB> ON <tableA>.<key> = <tableB>.<key>	

• Choose the join type carefully. An INNER JOIN is often sufficient and less costly
• Match the keys correctly to avoid an unintended Cartesian product (cross join)
• Build small parts of a query before combining them into one complex query
• INNER and OUTER can be omitted from the corresponding join keywords
  • JOIN on its own means INNER JOIN
  • LEFT JOIN means LEFT OUTER JOIN

SELECT C.naam,
       T.onderwerp AS Cursusonderwerp
FROM   Cursist C
       JOIN Training T ON C.id = T.user_id	

SELECT C.naam,
       T.onderwerp AS Cursusonderwerp
FROM   Cursist C
       LEFT JOIN Training T ON C.id = T.user_id	

SELECT C.naam,
       T.onderwerp AS Cursusonderwerp
FROM   Cursist C
       RIGHT JOIN Training T ON C.id = T.user_id	

SELECT C.naam,
       T.onderwerp AS Cursusonderwerp
FROM   Cursist C
       FULL JOIN Training T ON C.id = T.user_id	

56. Select all customers and the number of orders they have placed (5)

57. Select the names and ordered quantities for both ordered and unordered products (9)

58. Select all customer IDs and names, and the total amount of each customer's orders (5)

59. Select all vendors that do not offer any products (3)

60. Select all vendors and the number of products they offer (6)