SQL statements for multiple table SELECT and UNION queries, SQL Part 2

Two-table queries

ANSI style

SELECT OrderID, Customers.CompanyName, EmployeeID, OrderDate, RequiredDate, ShippedDate, Freight, ShipAddress, ShipCity, ShipRegion, ShipPostalCode,ShipCountry
FROM Customers
    INNER JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID
WHERE Region = "OR"
ORDER BY CompanyName;

ANSI style with table aliases

SELECT o.OrderID, c.CompanyName, o.EmployeeID, o.OrderDate, o.RequiredDate, o.ShippedDate, o.Freight, o.ShipAddress, o.ShipCity, o.ShipRegion, o.ShipPostalCode,o.ShipCountry
FROM Customers c
    INNER JOIN Orders o
    ON c.CustomerID = o.CustomerID
WHERE o.Region = "OR"
ORDER BY c.CompanyName;

JOIN in WHERE clause (theta style)

SELECT OrderID, Customers.CompanyName, EmployeeID, OrderDate, RequiredDate, ShippedDate, Freight, ShipAddress, ShipCity, ShipRegion, ShipPostalCode,ShipCountry
FROM Customers c,  Orders o
WHERE c.CustomerID = o.CustomerID
AND Region = "OR"
ORDER BY CompanyName;

Simple text manipulation

SELECT o.OrderID, c.CompanyName, o.EmployeeID, o.OrderDate, o.RequiredDate, o.ShippedDate, o.Freight, o.ShipAddress, o.ShipCity & ", " & o.ShipCountry AS ShippedTo, o.ShipRegion, o.ShipPostalCode
FROM Customers c
    INNER JOIN Orders o
    ON c.CustomerID = o.CustomerID
WHERE o.ShipCountry = "Mexico"
OR o.ShipCountry = "Canada"
ORDER BY c.CompanyName;

SELECT o.OrderID, o.OrderDate, c.CompanyName, c.Address, c.City & ", " & c.Region & " " & c.PostalCode AS CityStateZip, c.Country
FROM Orders AS o
    INNER JOIN Customers AS c
    ON o.CustomerID = c.CustomerID
WHERE (c.Country IN ("USA", "Canada"))
ORDER BY 5;

Multiple table query: Product list

SELECT Suppliers.CompanyName, Suppliers.ContactName, Suppliers.Address, Suppliers.City, Suppliers.PostalCode, Suppliers.Country,
Products.ProductName,
Categories.CategoryName
FROM Categories

INNER JOIN (Suppliers

INNER JOIN Products

ON Suppliers.SupplierID=Products.SupplierID)
        ON Categories.CategoryID=Products.CategoryID;

Address list (employee last name only)

SELECT ContactName, Address, City, Region, PostalCode, Country

FROM Customers

UNION

SELECT ContactName, Address, City, Region, PostalCode, Country

FROM Suppliers

UNION

SELECT LastName, Address, City, Region, PostalCode, Country

FROM Employees

ORDER BY ContactName;

Address list with complete names

SELECT ContactName, Address, City, Region, PostalCode, Country

FROM Customers

UNION

SELECT ContactName, Address, City, Region, PostalCode, Country

FROM Suppliers

UNION

SELECT FirstName & ", " & LastName, Address, City, Region, PostalCode, Country

FROM Employees

ORDER BY ContactName;

Address list with roles

SELECT ContactName, Address, City, Region, PostalCode, Country, "Customer" AS Role

FROM Customers

UNION SELECT ContactName, Address, City, Region, PostalCode, Country, "Supplier" AS Role

FROM Suppliers

UNION SELECT LastName, Address, City, Region, PostalCode, Country, "Employee" AS Role

FROM Employees

ORDER BY ContactName;