SQL

1) Using a view for a SELECT statement 3 Marks

Using YOUR adventureWorks DBs view [Sales].[vSalePerson], create a SQL query that returns the following columns for the sales people who have a [salesYTD] less than 2 million or [SalesLastYear] that were greater than 2 million:

[BusinessEntityID] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[JobTitle] ,[City] ,[StateProvinceName] ,[CountryRegionName] ,[SalesYTD] ,[SalesLastYear]

2) Part 1: Create a View using the designer and use it to select some data. 3 Marks

Create the following View in YOUR AdventureWorks database and name it vwCustomerOrderSummary. (HINT: Right-Click Views object\New View) Note how the links are created automatically for you.

2) PART 2: Use the view you created to query customer orders 5 marks

Add this query to the New Query window that has your SQL from Question #1

Use the view you created (vwCustomerOrderSummary) to list the following columns for customer orders made in the year 2013 that have a [TotalDue] greater than or equal to $10 000.00. Order the list by lastname, firstname, SalesOrderNumber.

Columns to return:

[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[Name] ,[OrderDate] ,[Finance_Account_Number] ,[SubTotal] ,[TaxAmt] ,[Freight] ,[TotalDue]

3) Create a SELECT that will JOIN tables 6 marks

Add this query to the New Query window that has your SQL from Question #1

Build a query of YOUR adventureWorks DB to display the following columns for all people with a Title of Mr. and a Work phone number. Order the results by LastName, FirstName, MiddleName. You will have to JOIN a few tables together. To find the joins you will need to use/view the database diagram and trace the relationships (See the main AdventureWorks2014 database diagram);

Person.Person.Title , Person.Person.FirstName , Person.Person.MiddleName , Person.Person.LastName , Person.Person.Suffix , Person.PersonPhone.PhoneNumber , Person.PhoneNumberType.Name

4) Find duplicates with an INTERSECT 3 Marks

Add this query to the New Query window that has your SQL from Question #1

Using an INTERSECT, find what [TSQL2012].[Production].[Suppliers].[contactname] values are in table [Person].[Person] in your adventureWorks database.

How are you going to match the two different data contents together? The [ContactName] is a full name but [Person].[Person] doesnt have a full name column! Hint: manufacture a full name column in-line expression as part of the adventureWorks portion of the query.

5) Find distinct with EXCEPT 3 Marks

Add this query to the New Query window that has your SQL from Question #1

Using EXCEPT (remember it returns records from the LEFT query that are not in the RIGHT query), return a listing of ProductIDs from your adventureWorks databases [production].[workOrder] table that have a startDate in sept 2011 that are not a work order (in the same [production].[workOrder] table) with an OrderQty greater than 50 that have a startDate in Oct 2011

Person (Person) Customer (Sal (All Columns) SalesOrderHeader (Sales) (All Columns) (All Columns) PersonType CustomerID PersonID Territory!D rowguid NameStyle OrderDate DueDate ShipDate Status vTitle StoreD FirstName MiddleName AccountNumber ModifiedDate Suffi SalesOrderNumber AdditionalContactinfo Demographics ModifiedDate CustomerID LSalesPersonID Territory|D rowguid BillToAddresslD ShipToAddresslD ShipMethodD Identically named columns! Use ALIAS to differentiate. HINT: Criteria Pane makes this easy! EStore (Sales) CreditCardApprova!Code CurrencyRatelD "(All Columns) SubTotal Name Salesperson!D Freight M TotalDue Comment rowguid ModifiedDate Mo (ifiedDate SELECT Person.Pson.PersonType, Person.Person.NameStyle, Person.Person.Title, Person.Person.FirstName, ntNumber AS Customer Account Number, Sales.Store Name Person.Person.EmailPromotion, Person.Person AdditionalContactinfo, Person.Person.Demographi Sales.SalesOrderHeader.RevisionNumber, Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.DueDate, Sales.SalesOrderHeader.ShipDate, Sales.SalesOrderHeader.Status, ales SalesOrderHeader.OnlineOrderFlag, Sales.SalesOrderHeader.SalesOrderNumber, Sales.SalesOrderHeader.PurchaseOrderNumber, Sales.SalesOrderHeader AccountNumber AS Finance Account Number Sales.SalesOrderHeader.SubTotalSlsSalesOrderHeader.TaxAmt, Sales.SalesOrderHeader.Freight, Sales.SalesOrderHeader.TotalDue, Sales.SalesOrderHeader.Comment FROM Sales Customer INNER JOIN SalesStore ON Sales.Customer.StorelD SalesStore. BusinessEntitylD INNER JOIN Sales.SalesOrderHeader ON SalesCustomer.CustomerID = Sal Person.Person ON Sales Customer.PersonlD Person.Person.BusinessEntitylD r CustomeriD INNER JOIN Person (Person) Customer (Sal (All Columns) SalesOrderHeader (Sales) (All Columns) (All Columns) PersonType CustomerID PersonID Territory!D rowguid NameStyle OrderDate DueDate ShipDate Status vTitle StoreD FirstName MiddleName AccountNumber ModifiedDate Suffi SalesOrderNumber AdditionalContactinfo Demographics ModifiedDate CustomerID LSalesPersonID Territory|D rowguid BillToAddresslD ShipToAddresslD ShipMethodD Identically named columns! Use ALIAS to differentiate. HINT: Criteria Pane makes this easy! EStore (Sales) CreditCardApprova!Code CurrencyRatelD "(All Columns) SubTotal Name Salesperson!D Freight M TotalDue Comment rowguid ModifiedDate Mo (ifiedDate SELECT Person.Pson.PersonType, Person.Person.NameStyle, Person.Person.Title, Person.Person.FirstName, ntNumber AS Customer Account Number, Sales.Store Name Person.Person.EmailPromotion, Person.Person AdditionalContactinfo, Person.Person.Demographi Sales.SalesOrderHeader.RevisionNumber, Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.DueDate, Sales.SalesOrderHeader.ShipDate, Sales.SalesOrderHeader.Status, ales SalesOrderHeader.OnlineOrderFlag, Sales.SalesOrderHeader.SalesOrderNumber, Sales.SalesOrderHeader.PurchaseOrderNumber, Sales.SalesOrderHeader AccountNumber AS Finance Account Number Sales.SalesOrderHeader.SubTotalSlsSalesOrderHeader.TaxAmt, Sales.SalesOrderHeader.Freight, Sales.SalesOrderHeader.TotalDue, Sales.SalesOrderHeader.Comment FROM Sales Customer INNER JOIN SalesStore ON Sales.Customer.StorelD SalesStore. BusinessEntitylD INNER JOIN Sales.SalesOrderHeader ON SalesCustomer.CustomerID = Sal Person.Person ON Sales Customer.PersonlD Person.Person.BusinessEntitylD r CustomeriD INNER JOIN