You will need to install SQL Server Express along with database services (there is one file that will install both), if you have not done so already, and you will need to attach the AdventureWorks2012 database file (AdventureWorks2012.mdf). To download SQL Management studio to run on Windows, here is the programs that you will need to install (if you have not already downloaded and installed). SQL Server Express 2016 (free) located at - https://www.microsoft.com/en-us/download/details.aspx?id=52679 download and install, use basic installation. SQL Server Management Studio Express (free) located at https://msdn.microsoft.com/enus/library/mt238290.aspx, select the Download SQL Server Management Studio July 2016 Hotfix update to download and install. Then, you will need to Right Click on the Databases icon and select Attach to attach the Adventureworks2012 database (to do Lab 5). To create a query, click on New Query and the following window will appear where you will type your SQL syntax (you can move the Object Explorer window around). Then click on Execute to run your SQL query. 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).

Server.http://msftdbprodsamples.codeplex.com/release/view/93587

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

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.

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. Use the Production.Product and the Production.Workorder tables. You should get 56 records.