Which object uses specified criteria to pull data from one or more table?

- retrieves a limited set of data from one or more tables using criteria specified in the WHERE clause

- often used to perform calculations on the data selected

- the result set is displayed as a table (columns and rows)

Single-table example (review): 

Current Product List:  all data comes from the Products table

SYNTAX

            SELECT column list

            FROM tablename

            WHERE criteria

            ORDER BY column list

Select from two tables:  Example

Run the Orders Query (Orders Qry on the Query list):  It lists all orders for all customers, without going into line items (order details), by retrieving related data from the Orders and Customers tables.

Note the number of rows and columns; several columns are repeated more often than strictly necessary.

Use the drop-down list next to the View button (circled above) to switch to SQL view.  This is the SQL statement, separated into logical sections for ease of interpretation:

SELECT

Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry,

Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country

FROM Customers

INNER JOIN Orders

     ON Customers.CustomerID = Orders.CustomerID;

Note:  The table names need not be repeated unless the same column names exist in both tables.  The table names are only required in the FROM, JOIN, and ON clauses, and in the latter, only because the relating column, CustomerID, has the same name in both tables.  

The query syntax shown above follows ANSI (American National Standards Institute) rules and should work in the latest versions of all relational databases.  Older syntax includes the join condition in the WHERE clause (theta style).  Note the number of rows and columns in the result set for the Orders Query and try the same example (with fewer columns), using the older style and table aliases, as follows:

SELECT o.OrderID, o.EmployeeID, o.OrderDate, o.RequiredDate, o.ShippedDate, o.ShipVia, o.Freight, c.CompanyName, c.Address, c.City, c.Region, c.PostalCode, c.Country

FROM Customers c, Orders o

WHERE c.CustomerID = o.CustomerID;

Note for MS Access users:  Compare this query in design view with the ANSI style query.  MS Access runs the query correctly but cannot represent it in the usual way In the graphical query interface.

JOIN OPERATOR

The JOIN operator specifies how to relate tables in the query.  The JOIN operator is one of the set operations available in relational databases.

The following join types of join are available in most relational databases:

            INNER

            OUTER (LEFT. RIGHT, FULL)

            CROSS

Joins may be represented as Venn diagrams, as shown below along with other common set operations:

Result of applying these joins in a query:

INNER JOIN:  Select only those rows that have values in common in the columns specified in the ON clause.

LEFT, RIGHT, or FULL OUTER JOIN:  Select all rows from the table on the left (or right, or both) regardless of whether the other table has values in common and (usually) enter NULL where data is missing.  (Note:  FULL OUTER JOIN not implemented in Access.)

CROSS JOIN (not illustrated - not exactly a set operation):  Select all possible combinations of  rows and columns from both tables (Cartesian product). Not available in Access but can "happen" by not specifying relationships between tables or not setting up the appropriate joins in a query.  (Not A Good Thing - the query may run for a very long time  and produce a huge, not very useful result set.)

Access uses the ANSI  (American National Standards Institute) style, with the JOIN and ON keywords.  Access, MySQL, and Oracle all use similar syntax, with more join types and options and other set operations in MySQL and Oracle (CROSS JOIN, FULL OUTER JOIN, INTERSECT, MINUS).

Select from two tables:  More examples

•  Alphabetical List of Products:   Lists products that have not been discontinued and the product category, using all columns from Products (Products.*) and one from Categories:
  

SELECT Products.*, Categories.CategoryName

FROM Categories
    INNER JOIN Products
    ON Categories.CategoryID=Products.CategoryID

WHERE (((Products.Discontinued)=No));

• Order Details Extended:  Calculates the Extended Price, the discounted total for each line item (order detail) in all orders, using data from the Order Details and Products tables :
  

SELECT

 [Order Details].OrderID, [Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount,
CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice

FROM Products
    INNER JOIN [Order Details]
    ON Products.ProductID=[Order Details].ProductID

ORDER BY [Order Details].OrderID;

       Note:  The calculation is not as complex as it may seem.  It is simply unit price * quantity * discount, formatted as currency.

Select from two tables:  Exercises

•  Modify the Orders query  to show only customers from Oregon and list each customer's name  and address once only (i.e., remove redundant columns).   Use CompanyName from the Customers table, not from the Orders table (to avoid an Access-specific lookup).   Sort by customer (company) name.  (Result: 28 rows)

• Text manipulation:  Modify the previous query to list customers from Mexico and Canada, showing city and country in this format:

            City, Country      (e.g.,  Montreal, Canada) 

         with a column header such as ShippedTo or Shipped To.  To do this, replace the City and Country columns with one calculated column (comma at the end to separate from the next column if necessary):

            City & ", " & Country AS ShippedTo,

           or      City & ", " &CountryAS[Shipped To],

Note:  Be sure to find the correct names for the City and Country columns - they are different in the two tables.  (Result: 58 rows)

• Similarly:  List USand Canadian customer addresses along with their orders (number and order date), with city, state, and postal code in one column, with the header CityStateZip:

            City,  Region PostalCode                         (e.g.: Newark,DE19716)

         Can you sort on the calculated column in the SQL statement?  (Result:  152 rows)

SELECT FROM TWO TABLES:  SYNTAX 

(Recommended, ANSI-style)

            SELECT column list

            FROM table1

                     &nb sp;  INNER JOIN table2

                     &nb sp;  ON table1.col1=table2.col2

            WHERE criteria

            ORDER BY column list

(Older, theta-style)

            SELECT column list

            FROM table1, table2

                    WHERE table1.col1=table2.col2

                    AND other criteria

            ORDER BY column list

Note:          

- col1 in table1 is usually that table's primary key

- col2 in table2 is a foreign key in that table

- col1 and col2 must have the same data type and for certain data types, the same size

MULTIPLE-TABLE SELECT

Examples

• The Sales by Category query summarizes sales data ($ figures) for all products, sorted by category, using data fromthree tables (Products, Orders, and Order Details) and the Order Details Extended query (equivalent to a view). 
  

   Run the query - notice that there is one row per product.   Then switch to SQL view:

SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName, Sum([Order Details Extended].ExtendedPrice) AS ProductSales

FROM Categories
     INNER JOIN (Products
          INNER JOIN (Orders
               INNER JOIN [Order Details Extended]
               ON Orders.OrderID=[Order Details Extended].OrderID)
          ON Products.ProductID=[Order Details Extended].ProductID)
     ON Categories.CategoryID=Products.CategoryID

WHERE (((Orders.OrderDate) Between #1/1/1997# And #12/31/1997#))

GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName

ORDER BY Categories.CategoryName;

If the join is in the WHERE clause, the rules are the same - the minimum number of join criteria is equal to the number of tables (or views) minus one.

The GROUP BY clause summarizes data in subsets, in this case giving one row per product.  (Topic to be covered in detail in the third class)

The order of clauses in the SQL statement is important:  GROUP BY after WHERE (if present), ORDER BY last.

• The Invoices query pulls together data from all tables except Categories and Suppliers.  Run the query, then go to SQL view:

SELECT

Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID,
Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, [FirstName] & " " & [LastName] AS Salesperson,
Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,
Shippers.CompanyName,
[Order Details].ProductID,
Products.ProductName,
[Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount,
CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice,

Orders.Freight

FROM Shippers
     INNER JOIN  (Products
               INNER JOIN ((Employees
                 & nbsp;       INNER JOIN (Customers
                 & nbsp;            INNER JOIN Orders
                 & nbsp;            ON Customers.CustomerID=Orders.CustomerID)
                 & nbsp;            ON Employees.EmployeeID=Orders.EmployeeID)
                 & nbsp;                  INNER JOIN [Order Details]
                 & nbsp;                  ON Orders.OrderID=[Order Details].OrderID)
     ON Products.ProductID=[Order Details].ProductID)
     ON Shippers.ShipperID=Orders.ShipVia;
 

 Note:  Relationships among the six tables are not linear so it is harder to "see" them in the SQL statement.

Exercises

• Create a list of products that shows the category name for each and the contact name of the supplier  (77 rows)

• Copy and save either sample query above with a different name and rewrite with the join criteria in the WHERE clause (theta style).
  

OUTER JOINS: 

Used to find data in one table that is missing related data from another, for example a supplier from whom we have no products, or a product that hasn't been categorized, or a customer who has not placed an order.  

Principle:  Join the tables and find all the rows from one table whose corresponding rows in the other table have a null value (data missing or value unknown).

Example/exercise 

List the company name, contact person, and phone number of customers who have not placed orders.  Type the following statement in the SQL window:

SELECT CompanyName, ContactName, Phone

FROM Customers

     LEFT JOIN Orders

     ON Customers.CustomerID = Orders.CustomerID

WHERE Orders.CustomerID is null;

The result should be 2 rows.

In MS Access, this statement is similar to the SQL generated by the Unmatched Query Wizard.

Note the emphasis on "related data" above.   See whether an outer join is necessary to create a list like the one in the previous example of customers whose orders have not been shipped.  Why or why not?

Queries using set operations

UNION

A UNION query brings together in one result set data from two or more unrelated tables or queries that have identical structure (same number of columns with same data types occurring in the same order; not necessarily same column headers).

A UNION query cannot be built in the graphical query interface in Access.

Example

Customers and Suppliers by City:

SELECT City, CompanyName, ContactName, "Customers" AS [Relationship]
FROM Customers
UNION SELECT City, CompanyName, ContactName, "Suppliers"
FROM Suppliers
ORDER BY City, CompanyName;

- Variable number of SELECT statements linked by the key wordUNION

- Columns must be named (important if they were calculated)

- Optional additional column or columns to add information or to make table structures match

- No duplicates unless UNION ALL is specified (not obvious from this example)

- If the result set is to be sorted, only one ORDER BY clause at the end

UNION:  Exercises

• Create an address list for all employees, customer contacts, and supplier contacts, sorted by name.  For this exercise, use employee's last name only.

• Change the previous query to use the employee's last name and first name.  (More than one way to do this.)

• Change one of the previous queries to include an additional column (call it Role) where each person is designated as Employee, Customer, or Supplier.

SYNTAX

            SELECT statement1

           UNION

            SELECT statement2

           UNION

            [...]

            SELECT statement-last

            ORDER BY column list

• To include all rows, regardless of duplication:

            SELECT statement1

            UNION ALL

            [...]

            SELECT statement-last

            ORDER BY column list

Other set operators (Oracle):  INTERSECT and MINUS

PASS-THROUGH queries 

Used when linking one database to another through an ODBC (Open Database Connectivity) or JDBC (Java Database Connectivity  or Sun Java standard) connection.  The query written in the local database is "passed through" as is to the database on the server and processed by the remote database.

What retrieves specific data from one or more tables to answer a question?

What is used to retrieve selective data on a specified criteria from one or more tables?

Is a database object that allows you to retrieve data from one or more related tables that meet specific criteria?

Which sort order organizes data alphabetically from A to Z?