SQL 1) Using a view for a SELECT statement Using YOUR adventureWorks DB’s view [

Question

SQL

1) Using a view for a SELECT statement

Using YOUR adventureWorks DB’s 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.

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

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

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

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

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] doesn’t 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

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 database’s [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 Sales·Customer.CustomerID = Sal Person.Person ON Sales Customer.PersonlD Person.Person.BusinessEntitylD r CustomeriD INNER JOIN

Explanation / Answer

Answer:

1).

BEGIN TRANSACTION;

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

from Sales.vSalePerson where

SalesYTD < = 2000000

OR

SalesLastYear > = 2000000;

2) PART 2).

Select
Title
,FirstName
,MiddleName
,LastName
,Suffix
,Name
,OrderDate
,Finance_Account_Number
,SubTotal
,TaxAmt
,Freight
,TotalDue
  
from vwCustomerOrderSummary where

TotalDue > = 10000

And

EXTRACT(YEAR FROM OrderDate ) = 2013

ORDER BY LastName,FirstName,SalesOrderNumber;

3).

Select

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

from Person p INNER JOIN PersonPhone pp on p.BusinessEntityID = pp.BusinessEntityID
INNER JOIN PhoneNumberType ppt on p.BusinessEntityID = ppt.BusinessEntityID

where
Person.Title like 'Mr.' and Person.PhoneNumberType.Name like 'Work'

ORDER BY LastName, FirstName, MiddleName;

4).Select (Select FirstName,
MiddleName,
LastName from Person) as 'FullName' from Person.Person
  
INTERSECT
  
Select Suppliers.contactname from Suppliers;

5).

Select ProductID from production.workOrder
Where
startDate BETWEEN 2011-Sep-01 AND 2011-Sep-30

EXCEPT

Select ProductID from production.workOrder
Where
startDate BETWEEN 2011-Oct-01 AND 2011-Oct-30
AND
OrderQty > = 50

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.