DSO 428 Fall 2018 Lab #3 – SQL DML
For this lab, you will be creating queries based on the SQL Server database, AdventureWorks. This database is visible in Management Studio (MS) when you sign on to your student database area. Expand Databases and you will see AdventureWorks at the top of the database list. It is NOT in your space but is a “public” database, visible to all student users.
You can find more information about the database in the data dictionary here:
http://www.sqldatadictionary.com/AdventureWorks2012.pdf
https://technet.microsoft.com/en-us/library/ms124438(v=sql.100).aspx
Your deliverable for this lab will be a document containing your SQL DML statements and the output from executing the statements.
For each query assignment below, please
1)cut and paste your SQL code from MS into Microsoft Word and
2)type a statement indicating how many rows were returned.
Depending on questions, you may need to copy and paste several rows from the output table.
Be sure to name your document yourLastName_yourFirstName_Lab3.docx (or yourLastName_yourFirstName_Lab3.doc). Failing to follow this naming convention will be considered that you fail to satisfy one requirement. When you complete this lab assignment, submit your Word document via Blackboard.
Using the AdventureWorks database, please perform the following tasks:
1.Using the query design tool in MS, 1) create a query that lists all columns from the Production.ProductListPriceHistory table. 2) How many rows returned?
2.Using the query design tool in MS, 1) create a query that lists all Products from the Production.Product table where the color is ‘Silver’. 2) How many rows returned?
3.Using the query design tool in MS, 1) create a query that lists only the BusinessEntityID, AccountNumber, Name, and CreditRating in alphabetical order by Name from the Purchasing.Vendor table. 2) How many rows returned?
4.Using the query design tool in MS, create an Employee Directory by executing a query that lists employees’ LastName, FirstName, BirthDate, MaritalStatus, Gender, and HireDate from the HumanResources.Employee and Person.Person tables. Order the output by LastName. How many rows returned?
5.Using the query design tool in MS, create a query that lists LastName and FirstName (from Person.Person), OrderDate (from Sales.SalesOrderHeader), and ItemValue (created from OrderQty * UnitPrice from Sales.SalesOrderDetail) AND where OrderDate >= June 1, 2008. Order the output by LastName. How many rows returned? (HINT: This is a 3-table query.)
6.Using the query design tool in MS, create a query that computes the average of LineTotal (label the result as Avg_Item_Value) from the Sales.SalesOrderDetail table. What is the Avg_Item_Value returned?
7.Using the query design tool in MS, compute the total (use TotalDue column and label the SUM as Sum_of_Orders) of all orders from the Sales.SalesOrderHeader table from 12/1/2007 to and including 12/31/2007. What is the Sum_of_Orders returned?
8.'Sheela Word' changed departments multiple times. Using the query design tool in MS, list the name of the departments 'Sheela Word' worked for, and also list the date 'Sheela Word' started to work for each department. How many departments did 'Sheela Word' work for?
9.Using the query design tool in MS, list the total number of sales orders (use Sales.SalesOrderHeader) that is not paid with a credit card. What is the total number returned?
10.Using the query design tool in MS, count the number of addresses (use Person.Address) that contain 'Santa' as part of any attribute used to represent the addresses. What is the number returned?