SQL Summary

The used database for the exercises:

Select Statement:

-- Only first and last names 
SELECT c.first_name, c.last_name
FROM customer as c

SELECT statement with a WHERE statement:

-- ALl customers whose first name is Kelly
SELECT c.first_name, c.last_name 
FROM customer AS c 
WHERE c.first_name = 'KELLY'

Simplify the column names:

-- Simplifies the table names to first and second 
SELECT c.first_name AS first,
c.last_name AS last 
FROM customer AS c

Union between two tables with same attributes

-- All the fist names and second names of customers and staff:
SELECT c.first_name AS first, c.last_name AS second 
FROM customer AS c
UNION
SELECT s.first_name AS first, s.last_name AS second 
FROM staff AS s

Difference between two tables with the same attributes:

-- Customers, that are not staff
SELECT c.first_name, c.last_name 
FROM customer AS c
EXCEPT
SELECT s.first_name, s.last_name 
FROM staff AS s

Intersection between two tables

-- Customers that are actors 
SELECT c.first_name, c.last_name 
FROM customer AS c 
INTERSECT 
SELECT a.first_name, a.last_name 
FROM actor AS a

Inner Join: Returns only the rows from both tables that have matching values in the specified column (or columns)

SELECT * FROM A 
INNER JOIN B 
ON A.KEY = B.KEY

Left Join: Also known as a left-outer join is another operation to combine two tables. A left join returns all the rows from the left table (first table specified in the query). Additionally it retrieves all the values matching those in the right table.

SELECT * FROM A 
LEFT JOIN B 
ON A.KEY = B.KEY

Special case, where B.KEY = NULL:

SELECT * FROM A 
LEFT JOIN B 
ON A.KEY = B.KEY 
WHERE B.KEY IS NULL

Right Join: All Rows from Right Table: A right join returns all rows from the right table (the second table specified in the query), regardless of whether there is a matching row in the left table.

SELECT * FROM A 
RIGHT JOIN B 
ON A.KEY = B.KEY