Introduction
To Structured Query Language (SQL) – Part 2
Querying
the database – the SELECT statement
Topics
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 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
- result is a table (columns and
rows)
Single-table example (review):
Current Product List:
all data comes from the Products table
Exercises (review)
- List all orders that were shipped to Spain, showing order
number, customer ID, city, and country, sorted by customer ID (actually,
customer name in the Northwind database).
- Modify the query to show orders shipped to
SYNTAX
SELECT column list
FROM tablename
WHERE criteria
ORDER BY column list
Select from two
tables: Examples
Orders Query:
includes data from the Orders and Customers tables.
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;
JOIN OPERATOR
The JOIN
operator specifies how to relate tables in the query.
Types of
join:
INNER
OUTER (LEFT.
RIGHT, FULL)
CROSS
INNER
JOIN: Select only those rows that have
values in common in the columns specified in the ON clause.
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.
CROSS
JOIN: Select all possible combinations of rows and columns
from both tables (Not A Good Thing - may run for a very long time and produce a huge result set that may not be
useful.)
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 in MySQL and Oracle (CROSS JOIN, FULL
OUTER JOIN).
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 (omit columns if you
don't want to type so much), using the older style and table aliases, as
follows:
SELECT
o.OrderID, o.CustomerID, o.EmployeeID, o.OrderDate, o.RequiredDate,
o.ShippedDate, o.ShipVia, o.Freight, o.ShipName, o.ShipAddress, o.ShipCity,
o.ShipRegion, o.ShipPostalCode, o.ShipCountry, c.CompanyName, c.Address,
c.City, c.Region, c.PostalCode, c.Country
FROM
Customers c, Orders o
WHERE
c.CustomerID = o.CustomerID;
NOTE: Compare this query in design view with the
ANSI style query. MS Access runs this
query correctly but cannot represent it in the usual way In
the graphical query interface.
Other
examples:
Alphabetical List of Products: includes data from the Products and
Categories tables, in this case all columns from Products (Products.*) and one
from Categories
Order Details Extended:
includes data from the Order Details and Products tables and a
calculated column (Extended Price) that uses UnitPrice, Quantity, and Discount
from Order Details to come up with the discounted total for each line item.
Select from two
tables: Exercises
- Modify
the query from our last exercise (Orders from
- Text
manipulation: Modify the previous query
to show city and country in this format:
City, Country
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):
City
& ", " & Country AS ShippedTo,
or City & ", " &
- Similarly:
List
City, Region PostalCode (e.g.:
Is it
possible to sort (in the SQL statement) on the calculated column?
SELECT FROM TWO TABLES: SYNTAX
(Recommended,
ANSI-style)
SELECT column list
FROM tablename1
INNER JOIN tablename2
ON
tablename1.col1=tablename2.col2
WHERE criteria
ORDER BY column list
(Older,
theta-style)
SELECT column list
FROM tablename1, tablename2
WHERE tablename1.col1=tablename2.col2
AND other criteria
ORDER BY column list
- col1 in tablename1 is usually that table's primary key
- col2 in tablename2 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
- Sales by Category
- Invoices
Exercise
Create a list of products that shows the category name for
each and the contact name of the supplier.
Type the following statement in the SQL window:
SELECT Suppliers.CompanyName, Suppliers.ContactName,
Suppliers.Address, Suppliers.City, Suppliers.PostalCode, Suppliers.Country,
Products.ProductName,
CategoryName
FROM Categories
INNER JOIN (Suppliers
INNER JOIN Products
ON
Suppliers.SupplierID=Products.SupplierID)
ON Categories.CategoryID=Products.CategoryID;
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
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 where
we suspect data is missing or unknown.
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;
In MS
Access, this statement is similar to that generated by the Unmatched Query
Wizard.
Queries using set operations
Used to bring 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
- Variable number of SELECT statements linked by the key
word
- 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
- 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
Without
duplication:
SELECT statement1
SELECT statement2
...
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 or JDBC connection. The query written in the local database is "passed
through" 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)