ContentRequires 2Exercise1: Working

Content

Requires 2
Exercise1: Working with Subqueries (90') 3
Exercise 2: Joining Data from multiple tables (70') 8

Requires:
For the following exercises:
- Print out respectively the screenshots to show the query results.
- SQL scripts for the exercises.
- Pack screenshots and SQL scripts into the zip file named Assignment3_AccountName.zip(for instance: Assignment3_NamNT.zip)

Exercise1: Working with Subqueries (90')
This exercise performs on AdventureWorks2008 database that included in the same folder with the assignment.

Query 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.

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 like the following.

Name
-----------------------------
LL Mountain Seat/Saddle
ML Mountain Seat/Saddle
HL Mountain Seat/Saddle
LL Road Seat/Saddle
ML Road Seat/Saddle
HL Road Seat/Saddle
LL Touring Seat/Saddle
ML Touring Seat/Saddle
HL Touring Seat/Saddle

(9 row(s) affected)

Query 2
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.
Mountain Bottle Cage
Road Bottle Cage
LL Bottom Bracket
ML Bottom Bracket
HL Bottom Bracket

(6 row(s) affected)

Query 3 Write a query that return all products that has the same price as the cheapest (lowest ListPrice) Touring Bike (ProductSubcategoryID = 3). Use the MIN() aggregate function in the subquery to return the lowest ListPrice to the outer query.

The result set should look like the following.

Name
--------------------------
Touring-3000 Blue, 54
Touring-3000 Blue, 58
Touring-3000 Blue, 62
………
Touring-3000 Yellow, 62
Touring-3000 Blue, 44
Touring-3000 Blue, 50

(10 row(s) affected)

Query 4
Part 1:
A list of countries (table: Person.CountryRegion, column: Name) that hosts less than ten instances of StateProvince in table Person.StateProvince is what your boss wants. Write a query that satisfies your boss.

Tip: a subquery using HAVING clause and aggregate function COUNT() can do the job.

The result set should look like the following.

Name
---------------------------
American Samoa
Australia
Germany
Micronesia
United Kingdom
Marshall Islands
Northern Mariana Islands
Palau
Virgin Islands, U.S.

(9 row(s) affected)

Part 2:
Rewrite the query as a JOIN, the same result set should be retrieved. Here HAVING and COUNT() are as useful as in the previous query.

Query 5
In this query we will put a subquery in an expression in the SELECT list. We would like to see a report on how the corporate salesmen are doing in their history of business (although some parameters in this exercise might be missing for a correct result).

Aggregate the average from column SubTotal in table Sales.SalesOrderHeader as a subquery (pay attention to the NULL values in column SalesPersonID), then substractthe grouped averages by SalesPersonID in the outer query. Also, in the outer query NULL values can cause confusing results. Remember that NULL in the SalesPersonID column means Internet sales, and those sales are of no interest for this query.

The result set should look like the following.

SalesPersonID SalesDiff
------------- ---------------------
284 2871,794
281 -7021,975
278 7022,1684
………
286 -5717,4173
289 8503,1384
283 1659,0548

(17 row(s) affected)

Query 6
In this exercise we will build the final query in three steps. The final result will show which bicycles that costs 400 to 800 less than the average bike. The final query will make use of a derived table to give us the answer.

Step 1:
Find out the average ListPrice value in table Production.Product. Restrict the rows you work on to values 1, 2 and 3 in the column ProductSubcategoryID.

The intermediate result set should be:

---------------------
1586,737

(1 row(s) affected)

Step 2:
Incorporate the entire previous query as a part of an expression in the new outer SELECT list you will write. Take column ListPrice from table Production.Product and subtract the previous query as the expression, give the new column the column alias ‘Diff’. Negative values indicate a cheaper bike.

The intermediate result set should look something like the following.

Name Diff
-------------------------------------------------- ----------

Content

Requires 2
Exercise1: Working with Subqueries (90') 3
Exercise 2: Joining Data from multiple tables (70') 8

Requires:
For the following exercises:
- Print out respectively the screenshots to show the query results.
- SQL scripts for the exercises. 
- Pack screenshots and SQL scripts into the zip file named Assignment3_AccountName.zip(for instance: Assignment3_NamNT.zip)

Exercise1: Working with Subqueries (90')
This exercise performs on AdventureWorks2008 database that included in the same folder with the assignment.

Query 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.

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’.