Introduction to SQL (SQL Server Version)
This tutorial should be considered an addendum to the previous "Introduction to SQL" tutorial, which used MS-Access as the vehicle to run the examples. This tutorial assumes the use of SQL Server 2008 R2 or higher; the examples can been run in the Query Editor window of SQL Server Management Studio.
To use the SQL Server version of the NWINDVBP database, download the NWINDVBP.BAK file here. This file is in SQL Server database backup format; to use it, it should be restored to your local database server. To do this, follow the steps below:
1. If you haven't done so already, download the database backup file; move it to a folder location of your choice.
2. In SQL Server Management Studio, right-click on the Databases node of your local server, and choose "Restore Database ..."
3. In the Restore Database window, type "NWINDVBP" in the "To database" textbox; check the "From device" radio button, then click the ellipsis (...) button:
4. In the Specify Backup window, click the "Add" button:
5. In the "Locate Backup File" window, navigate to the folder where you saved the downloaded database backup file, select that file, and click OK.
6. The selected file should now show up in the "Backup location" area of the Specify Backup window. Click OK.
7. The selected file should now show up in the Restore Database window, in the "Select the backup sets to restore" list. Check the box in the "Restore" column, and click OK:
8. The Progress area of the Restore Database window should spin for a brief moment, followed by a message telling you that the database has restored successfully.
9. Under the Databases node of your local server, the NWINDVBP database should now show up. (Note: You will typically have to "refresh" the database tree before you see a new item item appear. To do so, right-click on the Databases node and choose "Refresh".)
The NWINDVBP database is now ready to use. To run queries in the database window, right-click on the NWINDVBP database and choose "New Query".
A blank window will appear where you can type your queries:
In any case, make sure "NWINDVBP" is selected in the drop-down of available databases:
At this point, the examples can be run ...
Part I: Single-Table Queries
The Basic SELECT Query
The SELECT query retrieves one or more columns (fields) from a table. The basic syntax is:
SELECT field1 [, field2, ... fieldn] FROM tablename
Notes: If more than one field is specified, each field must be separated with a comma. If you want to select ALL fields from a table, you can use an asterisk (*) instead of a list of all the fields.
Example 1:
Retrieve all columns and all rows from the Employees table.
SELECT * FROM EMPLOYEES
Example 2:
Retrieve the Product ID, product name, and unit price from all rows of the Products table:
SELECT ProductID, ProductName, UnitPrice
FROM Products
Note: SQL statements can be entered on multiple lines, as in the query above. A recommended practice is to start each clause of the SQL statement on a separate line. This is a style consideration only; it has no effect on the interpretation of the statement.
Adding a WHERE Clause
By adding an appropriate WHERE clause to a SELECT statement, you can limit the number of rows (records) returned by the query. The WHERE clause specifies a condition that must be met in order for that row be returned.
Note: In the WHERE clause,
numeric literals have no delimiters; string and date literals are delimited by
single quotes (').
Example 3:
What is the name for product ID 19?
SELECT ProductName
FROM Products
WHERE ProductID = 19
Example 4:
Which customers are from Mexico? (Show Customer ID, company name, contact name, and contact title in the results.)
SELECT CustomerID, CompanyName, ContactName, ContactTitle
FROM Customers
WHERE Country = 'Mexico'
Example 5:
Which employees were hired on October 17, 1993? (Show employee first and last name, and title in the results.)
SELECT FirstName, LastName, Title
FROM Employees
WHERE HireDate = '10/17/1993'
Compound Conditions (Using AND and OR)
Example 6:
Which meat/poultry products (CategoryID = 6) have less than 10 units in stock? (Show product ID, product name, category ID, and units in stock in the results.)
SELECT ProductID, ProductName, CategoryID, UnitsInStock
FROM Products
WHERE CategoryID = 6 AND UnitsInStock < 10
Example 7:
Which items in the Product table are classified as meat/poultry or have less than 10 units in stock? (Show product ID, product name, category ID, and units in stock in the results.)
SELECT ProductID, ProductName, CategoryID, UnitsInStock
FROM Products
WHERE CategoryID = 6 OR UnitsInStock < 10
You can join multiple conditions using AND and OR. When both AND and OR are used in the WHERE clause, "AND" takes precedence. This precedence can be overridden by using parentheses. Examples 8 and 9 illustrate this:
Example 8:
Show the product name, units in stock, category ID, and unit price for seafood products (category ID = 8) that have at least 100 units in stock, or for any item whose unit price is greater than $50.
SELECT ProductName, UnitsInStock, CategoryID, UnitPrice
FROM Products
WHERE UnitsInStock >= 100
AND CategoryID = 8
OR UnitPrice > 50
Example 9:
Show the part product name, units in stock, category ID, and unit price for products that have at least 100 units in stock, and that are either classified as seafood (category ID = 8) or have a unit price greater than $50.
SELECT ProductName, UnitsInStock, CategoryID, UnitPrice
FROM Products
WHERE UnitsInStock >= 100
AND (CategoryID = 8
OR UnitPrice > 50)
Using NOT
Example 10:
Show the company name, contact name, and country for all non-US suppliers.
SELECT CompanyName, ContactName, Country
FROM Suppliers
WHERE NOT (Country = 'USA')
Note: This query could also be coded by using the "not equal" (<>) operator:
SELECT CompanyName, ContactName, Country
FROM Suppliers
WHERE Country <> 'USA'
Using BETWEEN / AND
Example 11:
Show the Product ID, product name, and unit price for all products that have a unit price between $10 and $15 (inclusive):
SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE UnitPrice BETWEEN 10 AND 15
Note: This query could also be coded as:
SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE UnitPrice >= 10 AND UnitPrice <= 15
Example 12:
Show the Order ID, Customer ID, and order date for orders that occurred between February 1 and February 7, 1995.
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '2/1/1995' AND '2/7/1995'
Queries based on dates become slighltly more involved if the dates have a time component to them (the dates in this database do not have a time component, but they could, because Date/Time fields store both date and time together). If the OrderDate field contained a time component, you would have to write the WHERE clause of the above query like this:
WHERE OrderDate BETWEEN '2/1/1995' AND '2/7/1995 11:59:59PM'
- or -
WHERE OrderDate >= '2/1/1995' AND OrderDate < '2/8/1995'
Using Computed Columns (Expressions)
A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators.
Example 13:
Show the OrderID, ProductID, UnitPrice, Quantity, Discount, and line item total for order line items that total at least $300.
SELECT OrderID, ProductID, UnitPrice, Quantity, Discount, UnitPrice * Quantity * (1 - Discount)
FROM [Order Details]
WHERE (UnitPrice * Quantity * (1 - Discount)) >= 300
Note that the column header for the computed column is labeled "(No column name)". A computed column can be given a name by using an alias for the column name via the AS clause (see the next example).
Note also that the table "Order Details" has an embedded space in its name. Although this is not good practice, SQL Server will allow it for table and column names. When a table or column name contains embedded spaces (and other odd characters), you must enclose it square brackets as was done in the example above.
Example 14:
Repeat Example 13, except call the computed column "LineItemTot".
SELECT OrderID, ProductID, UnitPrice, Quantity, Discount, UnitPrice * Quantity * (1 - Discount) AS LineItemTot
FROM [Order Details]
WHERE (UnitPrice * Quantity * (1 - Discount)) >= 300
Note: Some database systems do not require the word AS to specify an alias (in those systems, the expression is separated from the alias name with just a blank space).
Using LIKE
A WHERE clause condition can use the pattern-matching capabilities of the LIKE operator. The LIKE operator uses a percent sign (%) for a wildcard character.
Example 15:
Show company name, contact name, and title for customers whose contact person is considered a "manager".
SELECT CompanyName, ContactName, ContactTitle
FROM Customers
WHERE ContactTitle LIKE '%Manager%'
Using IN
The IN operator can be used in a WHERE clause condition to specify that a field must be equal to any of several values.
Example 16:
Show company name and address information for all suppliers located in France, Germany, and Italy.
SELECT CompanyName, Address, City, Country, PostalCode
FROM Suppliers
WHERE Country IN ('France', 'Germany', 'Italy')
Note: This query could also be coded as:
SELECT CompanyName, Address, City, Country, PostalCode
FROM Suppliers
WHERE Country = 'France'
OR Country = 'Germany'
OR Country = 'Italy'
(As you can see, using IN in this case is much more concise.)
Sorting with the ORDER BY clause
You can sort the results of your query on any field or fields by using an ORDER BY clause.
Example 17-a:
Show the category ID, unit price, and product name for all products, sorted by unit price (low to high).
SELECT CategoryID, UnitPrice, ProductName
FROM Products
ORDER BY UnitPrice
Note: By default, ORDER BY sorts the data in ascending (low to high) sequence. You can specify an ascending or descending sort by using the keywords ASC or DESC, respectively. The last line of the above query could have been written:
ORDER BY UnitPrice ASC
Example 17-b:
Show the category ID, unit price, and product name for all products, sorted by unit price (high to low).
SELECT CategoryID, UnitPrice, ProductName
FROM Products
ORDER BY UnitPrice DESC
Example 18:
Show the category ID, unit price, and product name for all products, sorted by unit price (high to low) WITHIN category ID.
SELECT CategoryID, UnitPrice, ProductName
FROM Products
ORDER BY CategoryID, UnitPrice DESC
Using SQL Functions
The COUNT Function
The COUNT function in a SELECT statement returns a count of the number of records in a table (if the statement has a WHERE clause, it returns only the number of records that meet the specified condition).
The syntax for the COUNT function requires that the word COUNT be followed by a field name in parentheses; normally, you would place an asterisk between the parentheses to signify "any field": COUNT(*). The COUNT function counts records; it is not dependent on any particular field within a record.
Example 19:
How many products are classified as beverages (category ID 1)?
SELECT COUNT(*) AS BeverageCount
FROM Products
WHERE CategoryID = 1
The SUM Function
The SUM function in a SELECT statement returns the sum of a numeric field or expression for all of records in a table (if the statement has a WHERE clause, it sums that field only for the records that meet the specified condition).
Example 20:
How many order lines do we have, and what is their total value?
SELECT COUNT(*) AS OrderLineCount, SUM(UnitPrice * Quantity * (1 - Discount)) AS OrderLineTotal
FROM [Order Details]
The AVG, MAX, and MIN Functions
An AVG, MAX, or MIN function in a SELECT statement returns the average value, maximum value, or minimum value, respectively, of a numeric field for all of records in a table (if the statement has a WHERE clause, the function is performed for that field only for the records that meet the specified condition).
Example 21:
Display the total order line value, average order line value, highest order line value, and lowest order line value.
SELECT SUM(UnitPrice * Quantity * (1 - Discount)) AS OrderLineTotal,
AVG(UnitPrice * Quantity * (1 - Discount)) AS AverageOrderLine,
MAX(UnitPrice * Quantity * (1 - Discount)) AS HighestOrderLine,
MIN(UnitPrice * Quantity * (1 - Discount)) AS LowestOrderLine
FROM [Order Details]
Using DISTINCT
Example 22-a:
As a verification, run the query below to see that any particular customer may have more than one order in the Orders table:
SELECT * FROM Orders ORDER BY CustomerID
Example 22-b:
Use the DISTINCT keyword to show each customer in the ORDERS table only once.
SELECT DISTINCT CustomerID FROM Orders
Example 22-c:
Suppose you are asked "how many customers have orders?" You can combine the DISTINCT and COUNT in a query to get the answer:
SELECT COUNT(DISTINCT(CustomerID)) FROM Orders
Subqueries
On the right-hand side of a conditional expression in a WHERE clause, you can use a subquery to return a comparison value. The subquery, which must return only one column, is executed first. The result of the subquery is then incorporated into the main query. If the subquery can only return a single value (one row, one column), then a relational operator (like = or >) can be used, as in the next two examples.
Example 23:
Show all fields of Order table for orders that were placed on the most recent order date.
SELECT *
FROM Orders
WHERE OrderDate = (SELECT MAX(OrderDate) FROM Orders)
The above query works as follows: the subquery
SELECT MAX(OrderDate) FROM Orders
will execute and return one value (the date 6/5/1996). You can verify that result by looking at the Order table – you'll see that the latest date for any order is 6/5/1996. That result (6/5/1996) is then used as the comparison value in the outer WHERE clause, effectively making it read:
WHERE OrderDate = '6/5/1996'"
Note: The dates in this table do not have a time component to them. If they did, the query would have to do a little more work to specify the desired date.
Example 24:
Show the OrderID, ProductID, UnitPrice, Quantity, Discount, and line item total for order lines that exceed the average order line total.
SELECT OrderID, ProductID, UnitPrice, Quantity, Discount,
UnitPrice * Quantity * (1 - Discount) AS OrderLineTotal
FROM [Order Details]
WHERE (UnitPrice * Quantity * (1 - Discount)) > (SELECT AVG(UnitPrice * Quantity * (1 - Discount))
FROM [Order Details])
The above query works as follows: the subquery
SELECT AVG(UnitPrice * Quantity * (1 - Discount)) FROM [Order Details]
will execute and return one value ($669.66). You can verify that by looking at the results of example 21. That result ($669.66) is then used as the comparison value in the outer WHERE clause, effectively making it read:
WHERE (UnitPrice * Quantity * (1 - Discount)) > 669.66
If the subquery can return multiple values (multiple rows, one column each), then the keyword IN must be used instead of a relational operator. This type of query is typically used in multi-table queries, which are presented in Part II of this document. The next example is a "sneak preview" of a multi-table query that uses a subquery that returns multiple values.
Example 25:
Show the orders that will be shipped by either Speedy Express or Federal Shipping.
SELECT *
FROM Orders
WHERE ShipVia
IN (SELECT ShipperID FROM Shippers WHERE CompanyName IN ('Speedy Express', 'Federal Shipping'))
The above query works as follows: the subquery
SELECT ShipperID FROM Shippers WHERE CompanyName IN ('Speedy Express', 'Federal Shipping')
will execute and return a result set consisting of two rows (one row containing the ShipperID for Speedy Express, the other containing the ShipperID for Federal Shipping). This result set will then supply the values to the IN clause, as if the query was written as follows:
SELECT *
FROM Orders
WHERE ShipVia IN (1, 3)
Grouping Data with the GROUP BY Clause
When you group data, you create a query that produces one record for each unique value of a field (or combination of fields) in a table (limited by the WHERE clause, if there is one). Any of the SQL functions discussed earlier (COUNT, SUM, AVG, etc.) apply to the grouped record.
Example 26:
Show the order ID and order total for each order.
SELECT OrderID, SUM(UnitPrice * Quantity * (1 - Discount)) AS OrderTotal
FROM [Order Details]
GROUP BY OrderID
Using the HAVING clause
Just as the WHERE clause can be used to limit the rows that are included in the result of a SQL command, the HAVING clause can be used to limit the groups that are included.
Example 27:
Show the order number and order total for each order, but include only orders that have a total greater than $1000.
SELECT OrderID, SUM(UnitPrice * Quantity * (1 - Discount)) AS OrderTotal
FROM [Order Details]
GROUP BY OrderID
HAVING SUM(UnitPrice * Quantity * (1 - Discount)) > 1000
FYI: Even when you use an alias for a computed column, you must still use the expression, not the alias, in the HAVING clause (this also applies to the WHERE and ORDER BY clauses – if a computed column is used with either of these, it is the expression, not the alias, that must be specified.)
Example 28:
For each order, show the number of order lines that make up that order.
SELECT OrderID, Count(*) AS OrderLineCount
FROM [Order Details]
GROUP BY OrderID
Example 29:
For each order, show the number of order lines that make up that order; but only include orders containing three or more items.
SELECT OrderID, Count(*) AS OrderLineCount
FROM [Order Details]
GROUP BY OrderID
HAVING Count(*) >= 3
Example 30:
For each order, show the number of order lines that make up that order, but only for orders that include product IDs 4 or 5 (a "Chef Anton" product).
SELECT OrderID, Count(*) AS OrderLineCount
FROM [Order Details]
WHERE OrderID IN (SELECT OrderID FROM [Order Details] WHERE ProductID IN (4,5))
GROUP BY OrderID
In this example, the SQL interpreter must perform two "passes" behind the scenes. First it applies the WHERE clause to the table. From that resulting set of records, the grouping is performed.
Example 31:
For each order, show the number of order lines that make up that order, but only for orders that include product IDs 4 or 5 (a "Chef Anton" product) and that have at least three or more total items.
SELECT OrderID, Count(*) AS OrderLineCount
FROM [Order Details]
WHERE OrderID IN (SELECT OrderID FROM [Order Details] WHERE ProductID IN (4,5))
GROUP BY OrderID
HAVING Count(*) >= 3
As in the previous example, the SQL interpreter must perform a number of passes behind the scenes. First it applies the WHERE clause to the table. From that resulting set of records, the grouping is performed. From the grouped set of records, the HAVING clause is then applied.
Part II: Multiple-Table Queries (Joins)
One of the results of normalizing your tables is that most queries will draw on several tables in order to get the desired result. As a result, most of the queries you create will need to combine two or more tables. Tables are joined by specifying which fields of the tables you want to match on, either in a WHERE clause or in a JOIN clause.
Let's do an example first, then resume with a further explanation of how the join works.
Example 32-a:
List the order ID and product name for each order line on the Order Details table.
SELECT OrderID, ProductName
FROM [Order Details], Products
WHERE [Order Details].ProductID = Products.ProductID
ORDER BY OrderID
In the syntax for the above query, note the following:
· Since we needed to draw from both the Order Details table and the Products table, both of those tables were specified in the FROM clause, separated by a comma.
· When you join two or more tables and those tables share common field names, those field names must be qualified in the query – this is the case in the query above for the ProductID field (both the Order Details and Products tables have a field with that names). To qualify a field name, use the syntax tablename.fieldname.
· To specify how the tables should be joined, a WHERE clause can be used to specify a matching (equal) condition between the fields that link the two tables (usually, these are the fields that were involved in establishing a one-to-many relationship between the two tables).
To understand why the query works the way it does, let's explore a little relational database theory:
· The relational model specifies that the first step in creating a join is to generate the Cartesian product of the two tables. The Cartesian product for two tables is another (virtual) table contains records made up of every possible combination of records from the two joined tables. In the case of the Order Details and Products tables, the Cartesian product would be a virtual table of 165935 records (2155 Order Details records X 77 Products records): i.e., the data for each and every Products row would be joined with each and every Order Details row.
· From the Cartesian product, the records meeting the WHERE clause are extracted (thus, only records where the ProductID field matches are selected).
· From the matched records, the desired columns (as specified in the SELECT clause) are extracted, resulting in the final result set.
To reduce the amount of typing required by qualifying the field names with the table names, you can use a short alias on the table names with the AS clause, as in the following example:
Example 32-b:
Modify the query in 32-a to use table name aliases:
SELECT OrderID, ProductName
FROM [Order Details] AS OD, Products AS P
WHERE OD.ProductID = P.ProductID
ORDER BY OrderID
The above will produce the same results as example 32-a.
Note: The word AS is optional when specifying an alias.
Using INNER JOIN
As an alternative to specifying the join criteria in the WHERE clause, you can specify the join criteria with the INNER JOIN clause, which is part of the FROM clause. The basic syntax is:
SELECT fields
FROM table1 INNER JOIN table2 ON table1.field = table2.field
Example 32-c:
List the order ID and product name for each order line on the Order Details table. Use the INNER JOIN clause to specify the join criteria.
SELECT OrderID, ProductName
FROM [Order Details] AS OD INNER JOIN Products AS P ON OD.ProductID = P.ProductID
ORDER BY OrderID
The above will produce the same results as examples 32-a and 32-b.
WHERE vs. INNER JOIN
Specifying join criteria with the WHERE clause is universally acceptable on all database systems that use SQL - however, it is considered "old school". INNER JOIN is preferred, because this format makes the join criteria explicit, rather than inferring it from the WHERE clause – the WHERE clause is then reserved for selection criteria, rather than doing dual-duty as a join specifier.
Future examples in this tutorial will show joins using both formats.
Example 33:
List the order ID, customer ID, and order date together with the sales rep first name and last name for the sales rep who made the order for all orders made in the month of September, 1994.
SELECT OrderID, CustomerID, OrderDate, FirstName, LastName
FROM Orders AS O, Employees AS E
WHERE O.EmployeeID = E.EmployeeID
AND OrderDate BETWEEN '9/1/1994' AND '9/30/1994'
ORDER BY OrderID
-- or –-
SELECT OrderID, CustomerID, OrderDate, FirstName, LastName
FROM Orders AS O INNER JOIN Employees AS E ON O.EmployeeID = E.EmployeeID
WHERE OrderDate BETWEEN '9/1/1994' AND '9/30/1994'
ORDER BY OrderID
In addition to joining tables with the WHERE clause or INNER JOIN clause, you can also join tables by using IN with a subquery. The next example illustrates this.
Example 34:
Find the description for every product included in order number 10250.
SELECT ProductName
FROM [Order Details] AS OD, Products AS P
WHERE OD.ProductID = P.ProductID
AND OD.OrderID = 10250
-- or --
SELECT ProductName
FROM [Order Details] AS OD INNER JOIN Products AS P ON OD.ProductID = P.ProductID
WHERE OD.OrderID = 10250
-- or --
SELECT ProductName
FROM Products
WHERE ProductID IN (SELECT ProductID FROM [Order Details] WHERE OrderID = 10250)
The next example joins three tables together and shows three different ways to get the results (joining with WHERE, INNER JOIN, or nested subquery):
Example 35:
Find the order ID and order date for every order that includes a product with "Chef Anton" in the product name.
SELECT DISTINCT O.OrderID, OrderDate
FROM Orders AS O, [Order Details] AS OD, Products AS P
WHERE O.OrderID = OD.OrderID
AND OD.ProductID = P.ProductID
AND P.ProductName LIKE '%Chef Anton%'
-- or --
SELECT DISTINCT O.OrderID, OrderDate
FROM (Orders AS O INNER JOIN [Order Details] AS OD ON O.OrderID = OD.OrderID)
INNER JOIN Products AS P ON OD.ProductID = P.ProductID
WHERE P.ProductName LIKE '%Chef Anton%'
-- or --
SELECT DISTINCT O.OrderID, OrderDate
FROM Orders AS O
WHERE OrderID IN (SELECT OrderID
FROM [Order Details]
WHERE ProductID IN (SELECT ProductID
FROM Products
WHERE ProductName LIKE '%Chef Anton%'))
Note: The DISTINCT keyword was used because if a particular order contained more than one "Chef Anton" product, that OrderID would be duplicated in the resultset. We only want to see the unique OrderIDs (and their dates) for all orders that have one or more Chef Anton products.
Comprehensive Example
Example 36:
This query incorporates most of the major SQL clauses discussed so far. This query joins four tables together to show the Order ID, order date, customer (company name), employee name and order total for all orders made in the month of January 1995 that totalled $1500 or more.
SELECT O.OrderID, O.OrderDate, C.CompanyName, E.FirstName, E.LastName,
SUM(OD.UnitPrice * OD.Quantity * (1 + OD.Discount)) AS OrderTotal
FROM Orders AS O, [Order Details] AS OD, Customers AS C, Employees AS E
WHERE O.OrderID = OD.OrderID
AND O.CustomerID = C.CustomerID
AND O.EmployeeID = E.EmployeeID
AND O.OrderDate BETWEEN '1/1/1995' AND '1/31/1995'
GROUP BY O.OrderID, O.OrderDate, C.CompanyName, E.FirstName, E.LastName
HAVING SUM(OD.UnitPrice * OD.Quantity * (1 + OD.Discount)) >= 1500
-- or --
SELECT O.OrderID, O.OrderDate, C.CompanyName, E.FirstName, E.LastName,
SUM(OD.UnitPrice * OD.Quantity * (1 + OD.Discount)) AS OrderTotal
FROM ((Orders AS O INNER JOIN [Order Details] AS OD ON O.OrderID = OD.OrderID)
INNER JOIN Customers AS C ON O.CustomerID = C.CustomerID)
INNER JOIN Employees AS E ON O.EmployeeID = E.EmployeeID
WHERE O.OrderDate BETWEEN '1/1/1995' AND '1/31/1995'
GROUP BY O.OrderID, O.OrderDate, C.CompanyName, E.FirstName, E.LastName
HAVING SUM(OD.UnitPrice * OD.Quantity * (1 + OD.Discount)) >= 1500
Outer Joins
The INNER JOIN retrieves records only when there is a match in both tables. In some cases, you may want to retrieve the values from one table even when there are no matching values in the other joined table. For example, suppose you wanted a list of all employees showing the orders that they placed in a given week (if any). To accomplish this, you could perform an outer join between the Employees table and the Orders table.
Instead of using the keywords "INNER JOIN", you use "LEFT OUTER JOIN" or "RIGHT OUTER JOIN" (the keyword "OUTER" is optional; the keyword "LEFT" or "RIGHT" implies an outer join; in fact, in some DBMS', the keyword OUTER is not allowed).
Example 37:
List the employee first name and employee last name for all employees, together with the order IDs and order dates for orders made by that employee (if any) for the week of September 26 through September 30, 1994. (If an employee made more than one order during that time period, that employee will show up more than once.)
Note: In order for this
query to produce the desired results, a table expression (sometimes
called an in-line view or in-line table) is used as the second
table being joined in the SQL statement. In many cases, most DBMS' will allow a
table expression to be used where a table name is expected. The table
expression SELECT * FROM Orders WHERE OrderDate BETWEEN '9/26/1994' AND '9/30/1994'
specifies a subset of records from the Orders table for the desired
date range.
SELECT E.FirstName, E.LastName, O.OrderID, O.OrderDate
FROM Employees AS E
LEFT JOIN (SELECT * FROM Orders WHERE OrderDate BETWEEN '9/26/1994' AND '9/30/1994') AS O
ON E.EmployeeID = O.EmployeeID
In the results above, for employees that don't have orders, the OrderID and OrderDate columns will contain NULL. This fact can be used in the type of query that asks the question, "which employees do NOT have orders?":
Example 38:
List the employee first name and employee last name for all employees for that had no orders for the week of September 26 through September 30, 1994.
SELECT E.FirstName, E.LastName, O.OrderID, O.OrderDate
FROM Employees AS E
LEFT JOIN (SELECT * FROM Orders WHERE OrderDate BETWEEN '9/26/1994' AND '9/30/1994') AS O
ON E.EmployeeID = O.EmployeeID
WHERE O.OrderID IS NULL
Self-Joins
It is possible to create a join between a table and a second instance of the same table. A common example is an Employees table in which the "ReportsTo" field (i.e., the employee's supervisor) contains EmployeeID values found in the Employees table itself.
The following example joins the Employees table to itself. The ReportsTo field in the first instance of the table is linked to the EmployeeID field in the second instance.
Example 39-a:
List the employee ID, last name, and first name for each employee, together with the last name and first name of that employee's supervisor.
SELECT E1.EmployeeID, E1.LastName, E1.FirstName,
E2.LastName, E2.FirstName
FROM Employees AS E1 INNER JOIN Employees AS E2
ON E1.ReportsTo = E2.EmployeeID
Because an inner join was specified, if the value in the ReportsTo field of any given record isn't also found in the EmployeeID field, the record isn't selected. This would leave out records where the ReportsTo field is NULL, perhaps indicating that the employee is a supervisor and doesn't report to another employee in the Employees table (as is the case for Andrew Fuller).
Example 39-b:
Revise the query written in exercise 41-a to use a LEFT JOIN, so that all employee records will be selected, whether or not they have a supervisor:
SELECT E1.EmployeeID, E1.LastName, E1.FirstName,
E2.LastName, E2.FirstName
FROM Employees AS E1 LEFT JOIN Employees AS E2
ON E1.ReportsTo = E2.EmployeeID
UNION Queries
A union query is one which creates a result set consisting of the results of two or more queries appended to one another with any duplicates removed.
Example 40:
List the employee first name and employee last name for employees that either placed orders on the week of September 26 through September 30, 1994, or employees that ever had an order containing a Chef Anton product (product IDs 4 or 5), or both. Include in the listing a column indicating which of these conditions was met.
SELECT FirstName, LastName, 'Had orders week of 9/26' AS ConditionMet
FROM Employees AS E INNER JOIN Orders AS O ON E.EmployeeID = O.EmployeeID
WHERE OrderDate BETWEEN '9/26/1994' AND '9/30/1994'
UNION
SELECT FirstName, LastName, 'Sold Chef Anton'
FROM (Employees AS E INNER JOIN Orders AS O ON E.EmployeeID = O.EmployeeID)
INNER JOIN [Order Details] AS OD ON O.OrderID = OD.OrderID
WHERE OD.ProductID IN (4, 5)
The rules for union queries in Access SQL are as follows:
· All queries in a UNION must request the same number of fields, but they don't have to be the same size or data type (this restriction does exist in other database systems).
· Use aliases only in the first SELECT statement, because they are ignored in all others
· You can use a GROUP BY or HAVING clause in each query argument to group the returned data
·
You can use an ORDER BY clause at
the end of the last query argument to display the returned data in a
specified order. In the ORDER BY clause, refer to fields by what they are
called in the first SELECT statement.
Part III: Updating Data and DDL Queries
It goes without saying that caution should be exercised whenever you run a query that can change or delete data in a table. It is a good idea to backup your database or to work with test tables when you are in a testing mode.
The UPDATE Query
Example 41:
Change the contact name for Customer ID "AROUT" to "John Smith"
· First, run this SELECT query to look at the existing data:
SELECT CustomerID, ContactName
FROM Customers
(Note that the contact name for Customer ID "AROUT" is '"Thomas Hardy".)
· Next, run the UPDATE query:
UPDATE Customers
SET ContactName = 'John Smith'
WHERE CustomerID = 'AROUT'
· Finally, run another SELECT query to verify the change that you made:
SELECT CustomerID, ContactName
FROM Customers
(Note the change)
Note: Without the WHERE clause in the UPDATE, ALL records would have the ContactName field updated to 'John Smith' (and that wouldn't be good).
Example 42:
Due to an increase in the price of sugar, you must increase the unit price of all products in the "Confections" category (Category ID 3) by 3%.
· First, run a SELECT query to look at the existing data:
SELECT * FROM Products ORDER BY CategoryID
· Next, run the UPDATE query:
UPDATE Products
SET UnitPrice = UnitPrice * 1.03
WHERE CategoryID = 3
· Finally, run another SELECT query to verify the changes that you made:
SELECT * FROM Products ORDER BY CategoryID
Although not shown in the examples, you can change multiple fields with the SET clause by separating each "column = value" item with a comma:
UPDATE Customers SET ContactName = 'John Smith', ContactTitle = 'Sales Manager', FAX = '123-456-7890' WHERE ...
Combining UPDATE with INNER JOIN
On occasion, the need will arise to update one table from another. For example, suppose price updates for certain products were entered into a file from a separate system on a daily basis. The data from the file is then loaded into the PriceUpdate table every night. The task at hand then is to update the UnitPrice field of the Products table with those new prices. You could run a query to update the UnitPrice field of the Products table with the UnitPrice field of the PriceUpdate table by joining on the ProductID field of both tables.
The contents of the PriceUpdate table can be viewed by running a SELECT statement on it. It shows that four products are ready for updating.
SELECT * FROM PriceUpdate
Example 43:
Update the unit price data in the Products table with data for the corresponding products in the PriceUpdate table.
· First, run a SELECT query to look at the existing data (for the sake of the example, we know that we are going to update four particular products):
SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE ProductID IN (4, 5, 14, 17)
· Next, run the UPDATE query:
UPDATE Products
SET Products.UnitPrice = PriceUpdate.UnitPrice
FROM PriceUpdate INNER JOIN Products
ON PriceUpdate.ProductID = Products.ProductID
· Finally, re-run your SELECT query from above to see the results of your changes:
SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE ProductID IN (4, 5, 14, 17)
The DELETE Query
Example 44:
Mario Pontes from the Hanari Carnes company calls to tell you to remove the Manjimup Dried Apples from the order that was placed on August 8, 1994. You determine that this is order ID 10250 and that the product in question is Product ID 51.
· First, run this SELECT query to look at the existing data:
SELECT * FROM [Order Details]
WHERE OrderID = 10250
· Next, run the DELETE query:
DELETE FROM [Order Details]
WHERE OrderID = 10250
AND ProductID = 51
Note that you must specify BOTH OrderID AND the ProductID so that you delete the one exact row form the table. Otherwise, you will delete too much data (and your other customers will not be happy!)
· Finally, re-run the SELECT query from above to verify the results:
SELECT * FROM [Order Details]
WHERE OrderID = 10250
Warning: If you issue a DELETE statement without a WHERE clause, you will delete ALL records in that table (the table itself and its structure will remain intact). To completely remove a table, use the DROP TABLE statement.
The INSERT Query
The INSERT statement has two basic formats. To insert one row into a table, you can use the INSERT INTO ... VALUES format; to insert multiple rows into a table from another table, you can use the INSERT INTO ... SELECT format. The INSERT INTO ... VALUES format will be shown first.
Example 45:
Insert a record for our new shipper, International Parcel Service. Their phone number is (800) 888-9999.
INSERT INTO Shippers (CompanyName, Phone)
VALUES ('International Parcel Service', '(800) 888-9999')
To check out the results, do a SELECT query:
SELECT * FROM Shippers
Note that in the INSERT statement above, the ShipperID was NOT specified. This is because the ShipperID field was defined with the IDENTITY attribute, thus, whenever a new record is added to the Shippers table, the ShipperID field will automatically populate with the next available number.
In the next example, we will use the SQL DDL statement CREATE TABLE to create a new table. Then we will use INSERT INTO with the SELECT option to populate this new table with some data from the Customers table.
Creating a New Table
The CREATE TABLE command has this basic syntax:
CREATE TABLE tablename (
field1 type [(size)] [NOT NULL] [single-field CONSTRAINT clause]
[, field2 type [(size)] [NOT NULL] [single-field CONSTRAINT clause]
[, ...]]
[, mutliple-field CONSTRAINT clause [, ...]])
Example 46:
Create a new table called US_CUSTOMERS, which has the same structure as the existing Customers table.
If you want to create a new table that has the same or similar structure to an existing table, you can take advantage of SQL Server's automatic scripting options. In this case, we want to create a table called US_CUSTOMERS that has the same structure as the Customers table. To do so, first right-click on the Customers table in the database tree, and select the following sequence of menu options: Script Table as --> CREATE To --> New Query Editor Window (see the screen shot below):
In the Query Editor window, modify the query as instructed by the callouts:
Your query should then look like this:
Run the CREATE TABLE query:
Refresh the Tables node of the database tree:
Note that the newly created table, US_CUSTOMERS, now appears in the Tables list for the NWINDVBP database:
Example 47:
Populate the US_CUSTOMERS table with data from the Customers table. Include only those rows for which Country is "USA".
INSERT INTO US_CUSTOMERS (CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax)
SELECT CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
FROM Customers
WHERE Country = 'USA'
Check your results with a SELECT statement:
SELECT * FROM US_CUSTOMERS
Note: Although it is a "best practice" to specify the column names on both the INSERT part and the SELECT part of the query above, in the case where ALL fields from the source table will be inserted into the target table, you could get away with a shortened form of this query, as follows:
INSERT INTO US_CUSTOMERS
SELECT *
FROM Customers
WHERE Country = 'USA'
Changing the Table Structure
Adding a Column
Example 48:
Add a one-character field called CUSTOMER_TYPE to the US_CUSTOMERS table.
ALTER TABLE US_CUSTOMERS ADD RG_CODE NVARCHAR(1);
Verify your results by looking at the US_CUSTOMERS table in design view (you may need to refresh first):
To populate the new column, you can use an UPDATE statement. In our example, we want to set the RG_CODE to "W" for states on the west coast (California, Oregon, or Washington). The query is:
UPDATE US_CUSTOMERS SET RG_CODE = 'W'
WHERE Region IN ('CA', 'OR', 'WA')
Verify the results with a SELECT statement:
SELECT CustomerID, CompanyName, Region, RG_CODE
FROM US_CUSTOMERS
Changing a Column Definition
Example 49:
Change the maximum length of the Address field of the Customers table to 75 characters.
ALTER TABLE US_CUSTOMERS ALTER COLUMN Address NVARCHAR(75);
Verify your results by looking at the Columns node for NWINDVBP in the database tree. Note that the Address field is now defined with a maximum length of 75.
Deleting a Column
Example 50:
Drop the Fax column from the US_CUSTOMERS table.
ALTER TABLE US_CUSTOMERS DROP COLUMN Fax
Verify your results by looking at the Columns node for NWINDVBP in the database tree. Note that the Fax field is gone.
.
Dropping and Adding Constraints
The ALTER TABLE statement can also be used to add a constraint to or drop a constraint to an existing field in a table. In the next two exercises, we will add a primary key constraint to the US_CUSTOMERS table, then drop it.
Example 51:
Add the primary key constraint to the US_CUSTOMERS table.
ALTER TABLE US_CUSTOMERS
ADD CONSTRAINT US_CUST_PK PRIMARY KEY(CustomerID)
Verify your results by looking at the Keys node for NWINDVBP in the database tree. Note that the primary key has been created.
Example 52:
Drop the primary key constraint from the US_CUSTOMERS table.
ALTER TABLE US_CUSTOMERS
DROP CONSTRAINT US_CUST_PK
Verify your results by looking at the Keys node for NWINDVBP in the database tree. Note that the key is no longer present.
Deleting a Table
Example 53:
Get rid of the US_CUSTOMERS table.
DROP TABLE US_CUSTOMERS
Verify your results by looking at the Tables node for NWINDVBP in the database tree. Note that US_CUSTOMERS is gone.
It goes without saying that you should be VERY CAREFUL when dropping a table, or for that matter, when deleting data. Make sure you have a backup.