QUERYING THE DATABASE: QUERIES and VIEWS
Query: Statement that allows data
retrieval
View: A virtual table; a saved
query (the SELECT
statement, not the result)
SELECT statement (DML)
- 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
& ", " & Country AS [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
US and 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, DE
19716)
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 from three 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;
Notes:
The
number of joins is equal to the total number of tables (or views) minus
one.
A join condition (ON table1.col1 = table2.col2) must be specified for
each join.
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).
Tip: To analyze or troubleshoot a query in the
Access query window or in the command line utility in Oracle or MySQL,
try
breaking the statement as shown in the syntax diagram, with the
keywords at the
beginning of the lines; or copy and paste to a text editor (e.g.,
Notepad) and
rearrange there.
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
word UNION
- 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.
Examples
-
Update
values in a remote table
- Find the
next sequence number for a table (Oracle - uses the auxiliary table
DUAL in the
remote database)
- Run a query
using a function or calculation that cannot be performed in the local
database