T-SQL Tutor Project - 01 Oct 2001

As part of the T-SQL Tutor column, I'll provide a commented script file online to demonstrate the syntax that appears in the article. Each article will also include a small amount of homework to help you improve your T-SQL syntax and reinforce the concepts in the article. You can find the answers online.

Using the TSQLTutorJoins database,

1. Create a database diagram (as I explained in the main article) of the four tables: Customer, Order, Category, and Product. Before running each of the following queries, draw a Venn diagram to estimate the maximum number of rows that the query will return.
2. Write a query to answer the question, Which customers have purchased a product? List only the customer name.
3. Write a query to answer the question, What were the ProductIDs of the products that these customers purchased? Add the ProductID to the query in Step 2.
4. Write a query to answer the question, Which category is each product in? List the ProductName and the CategoryName for each product, regardless of which customers bought which products.

Using the Pubs database,

Authors and Titles?

Titles and Publishers?

Publishers and Pub_Info?

1. Create a database diagram of the Authors, Publishers, Pub_Info, Titles, and TitleAuthor tables.
2. What's the relationship between
   1. One-to-one (1:1)
   2. One-to-many (1:M)
   3. Many-to-many (M:N)
   1. 1:1
   2. 1:M
   3. M:N
   1. 1:1
   2. 1:M
   3. M:N
3. Write a query to answer the question, Who published each title? List the publisher's name and the title for each book.
4. Write a query to answer the question, Which authors have written at least one title? List only the author's name.
5. Write a query to answer the question, For those authors who have written a title, what title_id has each author written? Add the TitleID to the query in Step 4.