Entire Site
Free Trial
Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.
- Create BookmarkCreate Bookmark
- Create Note or TagCreate Note or Tag
- DownloadDownload
- PrintPrint
Chapter 3. Joins > Solutions
3.7. Solutions
This section provides solutions to the exercises for this chapter.
| 1-2 |
Producing multiple copies of rows can be achieved with a fundamental technique
that utilizes a cross join. If you need to produce five copies out of each employee
row, you need to perform a cross join between the Employees table and a table that has
five rows; alternatively, you can perform a cross join between Employees and a table
that has more than five rows, but filter only five from that table in the WHERE
clause. The Nums table is very convenient for this purpose. Simply cross Employees and
Nums, and filter from Nums as many rows as the number of requested copies (five in
this case). Here’s the solution query:
SELECT E.empid, E.FirstName, E.LastName, Nums.n FROM HR.Employees AS E CROSS JOIN dbo.Nums WHERE Nums.n <= 5 ORDER BY n, empid; |
| 1-3 |
This exercise is an extension of the previous exercise. Instead of being asked to
produce a predetermined constant number of copies out of each employee row, you are
asked to produce a copy for each day in a certain date range. So here you need to
calculate the number of days in the requested date range using the
DATEDIFF function, and refer to the result of that expression
in the query’s WHERE clause instead of referring to a constant. To produce the
dates, simply add n - 1 days to the date that starts the
requested range. Here’s the solution query:
SELECT E.empid, DATEADD(day, D.n - 1, '20090612') AS dt FROM HR.Employees AS E CROSS JOIN dbo.Nums AS D WHERE D.n <= DATEDIFF(day, '20090612', '20090616') + 1 ORDER BY empid, dt; The DATEDIFF function returns 4 because there is a four-day difference between June 12, 2009 and June 16, 2009. Add 1 to the result, and you get 5 for the five days in the range. So the WHERE clause filters five rows from Nums where n is smaller than or equal to 5. By adding n - 1 days to June 12, 2009, you get all dates in the range June 12, 2009 and June 16, 2009. |
| 2 |
This exercise requires you to write a query that joins three tables: Customers,
Orders, and OrderDetails. The query should filter in the WHERE clause only rows where
the customer’s country is USA. Because you are asked to return aggregates per
customer, the query should group the rows by customer ID. You need to resolve a tricky
issue here to return the right number of orders for each customer. Because of the join
between Orders and OrderDetails, you don’t get only one row per order—you
get one row per order line. So if you use the COUNT(*) function
in the SELECT list, you get back the number of order lines for each customer and not
the number of orders. To resolve this issue, you need to take each order into
consideration only once. You can do this by using COUNT(DISTINCT O.orderid) instead of
COUNT(*). The total quantities don’t create any special
issues because the quantity is associated with the order line and not the order.
Here’s the solution query:
Code View:
Scroll
/
Show All SELECT C.custid, COUNT(DISTINCT O.orderid) AS numorders, SUM(OD.qty) AS totalqty
FROM Sales.Customers AS C
JOIN Sales.Orders AS O
ON O.custid = C.custid
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
WHERE C.country = N'USA'
GROUP BY C.custid;
|
| 3 |
To get both customers who placed orders and customers who didn’t place
orders in the result, you need to use an outer join like so:
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON O.custid = C.custid;This query returns 832 rows (including the customers 22 and 57, who didn’t place orders). An inner join between the tables would return only 830 rows without these customers. |
| 4 |
This exercise is an extension of the previous one. To return only customers who
didn’t place orders, you need to add a WHERE clause to the query that filters
only outer rows; namely, rows that represent customers with no orders. Outer rows have
NULLs in the attributes from the nonpreserved side of the join (Orders). But to make
sure that the NULL is a placeholder for an outer row and not a NULL that originated
from the table, it is recommended that you refer to an attribute that is the primary
key, or the join column, or one defined as not allowing NULLs. Here’s the
solution query referring to the primary key of the Orders table in the WHERE
clause:
SELECT C.custid, C.companyname
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON O.custid = C.custid
WHERE O.orderid IS NULL;This query returns only two rows for the customers 22 and 57, who didn’t place orders. |
| 5 |
This exercise involves writing a query that performs an inner join between
Customers and Orders, and filters only rows where the order date is February 12,
2007:
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
JOIN Sales.Orders AS O
ON O.custid = C.custid
WHERE O.orderdate = '20070212';The WHERE clause filtered out Customers who didn’t place orders on February 12, 2007, but that was the request. |
| 6 |
This exercise builds on the previous one. The trick here is to realize two things.
First, you need an outer join because you are supposed to return customers who do not
meet a certain criteria. Second, the filter on the order date must appear in the ON
clause and not the WHERE clause. Remember that the WHERE filter is applied after outer
rows are added and is final. Your goal is to match orders to customers only if the
order was placed by the customer and on February 12, 2007. You still want to get
customers who didn’t place orders on that date in the output; in other words,
the filter on the order date should only determine matches and not be considered final
in regards to the customer rows. Hence the ON clause should match customers and orders
based on both an equality between the customer’s customer ID and the
order’s customer ID, and the order date being February 12, 2007. Here’s
the solution query:
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON O.custid = C.custid
AND O.orderdate = '20070212'; |
| 7 |
This exercise is an extension of the previous exercise. Here, instead of returning
matching orders, you just need to return a Yes/No value indicating whether there is a
matching order. Remember that in an outer join a nonmatch is identified as an outer
row with NULLs in the attributes of the nonpreserved side. So you can use a simple
CASE expression that checks whether the current row is an outer one, in which case it
returns ‘Yes’; otherwise, it returns ‘No’. Because technically
you can have more than one match per customer, you should add a DISTINCT clause to the
SELECT list. This way you get only one row back for each customer. Here’s the
solution query:
Code View:
Scroll
/
Show All SELECT DISTINCT C.custid, C.companyname,
CASE WHEN O.orderid IS NOT NULL THEN 'Yes' ELSE 'No' END AS [HasOrderOn20070212]
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON O.custid = C.custid
AND O.orderdate = '20070212';
|
