Assignment:

Exercise Details

Define the terms

1. Subquery
2. Subquery search condition
3. Four ways to introduce a subquery in a SELECT statement
4. Correlated subquery
5. INTO

Exercise 1

1. Watch the following T-SQL video:

Introduction to Transact SQL (T-SQL) using Microsoft SQL Server by Steve Fox (youtube)

2. Complete the T-SQL Tutorial, Lesson1: Creating Database Objects. Select SQL Server 2012 from the "Other Versions" dropdown menu (See Figure 1).

3. After watching the T-SQL video and going through the tutorial, do the following(include screenshots for each):

1. Create a database

2. Create a table in the database

3. Insert data into the table

4. Update the data

Exercise 2

1. Write a query that filters data and return the column "Name" from table Production.Product. The filtering of rows is achieved by a WHERE clause that compares a single value from a subquery.

2. The inner subquery shall return a specific ProductSubcategoryID that the outer query uses as a filter of products to include in the report. The inner query will use its own WHERE clause to deliver its value, the ProductSubcategoryID, by retrieving it where the column "Name" in table Production.ProductSubcategory have the value of ‘Saddles'.

The result set should look similar to the following: Name
LL Mountain Seat/Saddle
ML Mountain Seat/Saddle
(9 row(s) affected)

Exercise 3 In this exercise, you can change the previous query to deliver the following result set. The WHERE clause in the subquery will now use the wildcard string ‘Bo%' for a comparison.

The result set should look like the following.

Name

Water Bottle - 30 oz.

LL Bottom Bracket

ML Bottom Bracket

(6 row(s) affected)