01. Write a query to display the sales person ID, territory ID, and territory name of all the sales persons in the following format.

select 'person Id'=salesPersonID,
'territory ID'=s.TerritoryID,Name
from sales.salesperson s join
sales.salesterritory t
on s.territoryID = t.territoryID

02. Write a query to display the sales person ID of all the sales persons and the name of the territory to which they belong.

select salespersonID, Name
from sales.salesPerson
join sales.salesterritory
on salesperson.territoryID = salesTeriitory.TerritoryID

04. Write a query to display the sales order ID, the product ID, and the order date for all the products in the following format.

select 'Order ID'=  h.salesOrderID,
'Product ID'= d.productID,
'Order Date'= OrderDate
from sales.salesOrderHeader h
join sales.salesOrderDetail d
on h.salesOrderID= d.salesOrderID

05. Write a query to display the order number, territory name, order date, and the quarter in which each order was placed in the following format.

select salesOrderID,'territory name'= name
OrderDate, Quarter = detepart (qq,OrderDate)
from sales.salesOrderHeader s
join sales.salesTerritory t
on s.TerritoryID =t.TeriitoryID

06. Write a query to display the sales order ID, territory name, month, and year of all the sales orders in the following format.

select salesOrderID 'Territory name'= name,
month = datename(mm,OrderDate),
Year = Datename(yy,orderdate)
from sales.salesOrderHeader s
join sales.salesTerritorey t
on s.TerritoryID =t.TerritoryID

07. Write a query to display the total amount due for all the sales orders rounded off to a whole number. In addition, display the sales order ID and the type of credit card through which the payment was made.

select salesOrderID, CardType,
'Total Due'= Round(TotalDue,0)
from sales.salesOrderHeader s
join sales.CreditCard c
on s.CreditCardID= c.CreditCardID

08. Write a query to display all the country region codes along with their corresponding territory IDs.

select c.CountryReglonCode, territoryID
from sales.salesTerritory s
Right Outer join sales.CountryReglonCurrecy c
on s.CountryReglonCode = c.CountryReglonCode

09. Write a query to display the total amount due for all the orders in the AdventureWorks database in the following format:

select 'Order value'=
'The total amount due for the sales Order ID:'+
Convert(varchar(10),salesOrderID)+
'is $'+ Convert(varchar(10),TotalDue)
from sales.salesOrderHeadre

10. Write a query to display the order date along with the sales order ID and the territory name. The order date should be displayed in the dd/mm/yyyy format.

select salesOrderid,Name,
Convert(Char(10),OrderDate,103)
as
'Order Date' from sales.salesOrderHerder s join sales.salesTerritory t
on t.TerritoryID= s.TerritoryID

11. Write a query to display the sales order ID and the territory name of the orders where the month of the order is May and the year is 2004.

select salesOrderID,Name from  sales.salesOrderHeader
s join sales.salesTerritory t on s.TerritoryID =t.TerritoryID
where DateName(mm, OrderDate)= 'May'and DatePart(yy,OrderDate)=2004

12. Write a query to display the contact ID of the customers that have the 'Vista' credit card.

select ContactID,CardType
from sales.ContactCreditCard
join sales.CreditCard no ContactCreditCardID=
CreditCard.CreditcardID
where CardType='Vista'

13. Write a query to display the sales order IDs of the orders received from the Northeast territory.

select salesOrderID from sales.salesOrderHeader
where territoryID=(select TerritoryID
from sales.salesTerritory
Where Name='Nottheast')

14. Create a report containing the sales order ID of those orders where the total value is greater than the average of the total value of all the orders.

select salesOrderID, TotalDue
from sales.salesorderHeader
where totalDue > (select avg(TotalDue)
from sales.salesOrderheader)

15. Write a query to display the sales order IDs of the orders that have been paid through a SuperiorCard.

select salesOrderID from sales.OrderHeader
where CreditCardID in (select CreditCardID
from sales.CreditCard Where CardType='SuperiorCard')

16. Write a query to display the sales order ID, the order detail ID, and the total value of those orders where the total value is greater than the maximum of the total value of order ID 43662.

select salesOrderID, salesOrderDetailID,LineTotal
from sales.salesOrderDetail
where LineTotal >All (select Linetotal from sales.salesOrderDetail
Where salesOrderID=43662)

17. Write a query to display the details of those orders for which no discount was offered

select* from sales.salesOrderDetail
Where specialofferID=(select spcialofferID
from sales.specialoffer where type= 'No Discont')

18. Write a query to display the sales order IDs and the credit card IDs of those cards which are expiring in the year 2007.

select salesOrderIds, CreditcardID
from sales.salesOrderHeader
where CreditCardID in (select CreditcardID
from sales.CreditCard where ExpYear=2007)