T-SQL Tutor Project - 17 Jan 2002

As a regular part of T-SQL Tutor, you'll find a commented script file to test the syntax in the article. The script for this article is NTableJoinsExamples.sql. In addition, each article includes a small amount of "homework" to help you to keep improving your T-SQL syntax. You can find the answers online in the script NTableJoinsHomework.sql.

Use the Northwind database to create a Venn diagram of the Order and Category tables. Then, use Enterprise Manager’s Database Diagramming Wizard to create multiple diagrams that differ by the number of levels they add automatically. Use the following steps to create the first diagram:

1. Open Enterprise Manager.
2. Connect to your local server and expand to the list of databases, then expand to the list of objects under the Northwind database.
3. Right-click Diagrams and choose Create New Database Diagram.
4. Read the Welcome dialog box and click Next.
5. In the Create Database Diagram Wizard dialog box, select Categories and Orders.
6. Select the Add related tables automatically check box, and leave the default of one level for How many levels of related tables.
7. Click Add to generate the list of tables to diagram, then click Next.
8. Click Finish to generate the diagram, then save it as Orders & Categories 1 Level.

Try a few other diagrams with two, three, and four levels added. Review the differences when you change the number of levels for related tables (Step 6).

Using the one-level diagram, review the tables related to Categories, Orders, and Customers. Then, write a query to answer the question, When and what categories of products have sold (and have already shipped) to which customers? Write this query in stages as I did in the main article:

SELECT CONVERT(varchar(10), ShippedDate, 101)
     AS 'Shipped MM/DD/YYYY'
     , CompanyName
     , CategoryName
     , ProductName

1. List the tables that should go in the FROM clause; use the diagram to help you logically order the tables.
2. Add the JOIN conditions.
3. Add the following SELECT list:
4. Sort the output so that it shows the most recently shipped orders first.