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

Single table queries

SELECT OrderID, CustomerID, ShipCity, ShipCountry

FROM Orders

WHERE ShipCountry = “Spain"

ORDER BY CustomerID;

SELECT OrderID, CustomerID, ShipCity, ShipCountry

FROM Orders

WHERE ShipCountry = "Spain"

OR ShipCountry = "Mexico"

ORDER BY ShipCountry, CustomerID;

SELECT OrderID, CustomerID, ShipCity, ShipCountry

FROM Orders

WHERE ShipCountry IN ("Spain", "Mexico")

ORDER BY ShipCountry, CustomerID;

Multiple table queries

ANSI style

SELECT Orders.OrderID, Orders.CustomerID, Orders.ShipCity, Orders.ShipCountry, Employees.LastName, Employees.Extension

FROM Orders

INNER JOIN Employees

ON Orders.EmployeeID = Employees.EmployeeID

WHERE (((Orders.ShipCountry) In ("Spain","Mexico")))

ORDER BY ShipCountry, CustomerID;

ANSI style with table aliases

SELECT o.OrderID, o.CustomerID, o.ShipCity, o.ShipCountry, e.LastName, e.Extension

FROM Orders AS o

INNER JOIN Employees AS e

ON o.EmployeeID = e.EmployeeID

WHERE o.ShipCountry = "Spain"

OR o.ShipCountry = "Mexico"

ORDER BY o.ShipCountry, o.CustomerID;

JOIN in WHERE clause (theta style)

SELECT o.OrderID, o.CustomerID, o.ShipCity, o.ShipCountry, e.LastName, e.Extension

FROM Orders AS o, Employees AS e

WHERE o.EmployeeID = e.EmployeeID

AND (o.ShipCountry = "Spain"

OR o.ShipCountry = "Mexico")

ORDER BY o.ShipCountry, o.CustomerID;

Simple text manipulation

SELECT o.OrderID, o.CustomerID, o.ShipCity & ", " & o.ShipCountry AS [Shipped To], e.LastName, e.Extension

FROM Orders AS o, Employees AS e

WHERE o.EmployeeID = e.EmployeeID

AND (o.ShipCountry = "Spain"

OR o.ShipCountry = "Mexico")

ORDER BY o.ShipCountry, o.CustomerID;

SELECT o.OrderID, o.OrderDate, c.CompanyName, c.City & ", " & c.Region & " " & c.PostalCode AS CityStateZip

FROM Orders AS o

INNER JOIN Customers as c

ON o.CustomerID = c.CustomerID

WHERE (c.Country IN ("USA", "Canada"))

ORDER BY 4;

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;