Prepare and execute each of the queries listed using the "Adventureworks2012" database in SQL Server. When all of your queries are complete, cut and paste the SQL Syntax into a word document to be submitted via SAKAI by the due date. In order to see what the column names are, you need to click on the table and then Columns to see the field names. Make sure to include column headings that make sense in the queries (use the as Field Name after the field selected).

Part 1: Single-table queries. 1. List the FirstName, LastName, and Title of all employees in the Adventureworks2012 database. Sort by last name (the syntax for the first query has been done for you below) you should get 19,972 records:

use adventureworks2012

select person.title as Title, person.firstname as First Name,

person.lastname as Last Name

from person.person

order by person.lastname

2. Select the sales for each Sales Person, from the Sales. Store table where the sales person ID is equal to 279, 281, or 288). Sort by the Sales Person ID, and then by the Business Name (make sure to list Sales Person ID, and Store Name). You should get 199 records.

3. For each Vendor with a credit rating of 2 or higher, list the Vendors Name, Account Number, and Credit Rating. Sort by the credit rating (descending), and then vendor name. You should get 20 records.

4. List the Sales Tax Rate, State Province ID, Tax Type, and Name for all sales tax rates of 7.50 or higher, and with a state province ID greater than 10. Put the list in order of tax rate and then company name. You should get 9 records.

5. List the Business ID, Pay Rate Change Date, Pay Rate, and Pay Frequency for all businesses with a pay rate greater than 10.00 and a pay frequency of 2. Put the list in order by rate, then by the rate change date. You should get 130 records.

6. Write a query to answer the following question. How many employees (from the HumanResources.Employee table, have an organizational level equal to three (3)? You should get 1 record with the total number of employees.

7. Write a query to answer the following question. How many parts from the Production.BillOfMaterials table have a ComponentID equal to or greater than 500, and less than or equal to 525. Sort by the component ID and then by the product assembly ID. List the Product Assembly ID, the Component ID, the BOM Level, Start date, and Modified Date. You should get 145 records.

8. Write a query using the sales.salesterritory table that lists all territories that have current year to date sales greater than last year sales. Sort by sales ytd. List the territory ID, Country region code, year to date sales, and last year sales. You should get 7 records.

9. Write a query to determine which SuperiorCard credit cards have an expiration year of 2005 or earlier. List the credit card ID, card type, the expiration month and year, sorted by expiration month and year, descending. (Use the Sales.CreditCard table). You should bet 1,201 records.

10. Write a query to find the average unit price for all sales from the Sales.SalesOrderDetail table. You will only have 1 record which contains the overall average unit price.

Part 2: Multi-table Queries (each query will require the access of at least 2 tables, maybe more):

11. Write a query to find all of the employees that work for Research and Development group, that have a start date after 01-01-2005. List their DepartmentID, StartDate, Department Name, and GroupName. Order by StartDate. You will need to use HumanResources.Department, and HumanResources.EmployeeDepartmentHistory tables for this query. You should get 4 records total.

12. Write a query to find the sales people that earned a bonus over $3,700. List the salespersons title, first name, last name, bonus and commission percentage. Order by bonus descending. You will need to use the Sales.SalesPerson and Person.Person tables. You should get 7 records.

13. Write a query to find out the number of female employees that make over $10 per hour (rate). List their job title, Gender, Marital Status, Hire Date, and Rate. Sort by rate and marital status. Use HumanResources.Employee and HumanResources.EmployeePayHistory. You should get 73 records.

14. Write a query to find out what sales people have a sales quota over 250,000 and are in territory 5. List the Business Entity ID, Territory ID, Sales Quota, Sales YTD, Sales Last Year, Customer ID, and Stored ID. Sort by customer id. Use Sales.SalesPerson and Sales.Customer tables. You should get 176 records.

15. Write a query to find out how many products had a scrapped quantity greater than 20. List the Product ID, Product Name, Product Number, Work Order ID, Order Quantity, Scrapped Quantity, and Scrap Reason ID. Sort by Scrapped Quantity, and then by Product ID. You should get 95 records.

16. Write a query to find out how many people have a Seattle address. List the Address ID, Address Lines (Street and Apt number if available), City, StateProvince ID, Name, and Business Entity ID. Use the Person.Address, Person.AddressType, and Person.BusinessEntityAddress tables. Sort by Address ID. You should get 141 records.

17. Write a query to find out the minimum order quantity greater than 100 for all vendors. List the vendor name, account number, credit rating, last receipt cost, maximum order quantity, and minimum order quantity. Soft by minimum order quantity and then by maximum order quantity. Use the Purchasing.ProductVendor and Purchasing.Vendor tables. You should get 38 records.

18. Write a query to find out how many Vista credit cards expire in 2008 or more recent (2009, 2010, etc.). List the Credit Card ID, Card Type, Card Number, Expiration Month, Expiration Year, Sales Order ID, and Customer ID. Sort by expiration year, and then expiration month. Use the Sales.Creditcard and Sales.SalesOrderHeader tables. You should get 1,708 records.

19. Write a query to find out how many Vista credit cards expire in 2008 or more recent (2009, 2010, etc.) from customer 11151, 11223, and 11750. List the Credit Card ID, Card Type, Card Number, Expiration Month, Expiration Year, Sales Order ID, and Customer ID. Order by Customer ID. Use the Sales.Creditcard and Sales.SalesOrderHeader tables. You should get 33 records.

20. Write a query to find out how many products had a scrapped quantity greater than 20 and an order quantity greater than 2000 . List the Product ID, Product Name, Product Number, Work Order ID, Order Quantity, Scrapped Quantity, and Scrap Reason ID. Sort by Scrapped Quantity, and then by Product ID. You should get 56 records.