NIIT SQL LAB@HOME 5 ~ GNIIT HELP

sql lab @ home 5

1.   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=SalesTerritory.TerritoryID


2.  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 st
on s.Territory ID=st.Territory ID

3.   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


4.   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 Slaes.SalesOrderHeader s
JOIN Sales.SalesTerritory t
ON s.TerritoryID=t.TerritoryID


5.  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=datepart(qq,OrderDate)
FROM Sales.SalesOrderHeader s
JOIN Sales.SalesTerritory t
ON s.TerritoryID=t.TerritoryID

6.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

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

SELECT c.CountryRegionCode,TerritoryID
FROM Sales.SalesTerritory s
RIGHT OUTER JOIN Sales.CountryRegionCurrency c
ON s.CountryRegionCode=c.CountryRegionCode

8.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.SalesOrderHeader

9.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.SalesOrderHeader s
JOIN Sales.SalesTerritory t
ON t.TerritoryID=s.TerritoryID

10. 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

11.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
ON ContactCreditCard.CreditCardID=CreditCard.CreditCardID
WHERE CardType='Vista'

12.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 SalesOrderHeader)


13.Write a query to display the sales order IDs of the orders received from the Northeast territory.
(Use the AdventureWorks database)


Select SalesOrderID,FROM Sales.SalesOrderHeader WHERE TerritoryID=(Select TerritoryID FROM Sales.SalesTerritory WHERE Name='NorthEast')

14.Write a query to display the sales order IDs of the orders that have been paid through a
SuperiorCard. (Use the AdventureWorks database)

 Select SalesOrderID,FROM Sales.SalesOrderHeader WHERE CreditCardID in (Select CreditCardID FROM Sales.CreditCard WHERE CardType='SuperiorCard')

15. 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. (Use the
AdventureWorks database)

Select SalesOrderID,SalesOrderDetailID,LineTotal FROM Sales.SalesOrderDetail WHERE LineTotal> ALL(SELECT LineTotal FROM Sales.SalesOrderDetail WHERE SalesOrderID=43662)

16.Write a query to display the details of those orders for which no discount was offered. (Use the
AdventureWorks database)


Select  *FROM Sales.SalesOrderDetail WHERE SpecialOfferID=(Select SpecialOfferID FROM  SalesSpecialOffer WHERE  Type='No Discount')

17.Write a query to display the sales order IDs and the credit card IDs of those cards which are
expiring in the year 2007. (Use the AdventureWorks database)

Select  SalesOrderID,CreditCardID FROM Sales.SalesOrderHeader WHERE CreditCardID in (Select CreditCardID FROM Sales.CreditCard WHERE ExpYear=2007)

18.  Write a query to display the average rate of the Australian Dollar, where the CurrencyRateDate is
July 1, 2004. (Use the AdventureWorks database)


Select  AverageRate FROM Sales.CurrencyRate WHERE CurrencyRateDate='2004-07-01' AND ToCurrencyCode=(Select CurrencyCode FROM Sales.Currency WHERE Name='Australian Dollar')