NIIT SQL LAB@HOME 2

1. Display the details of all customers

ANSWER: Select *
                   From Sales.Customer


2.Display the ID,type,number,and expiry year of the credit cards

ANSWER:  Select 'CreditCardID=CreditCardId, 'CreditCardType' =                 Cardtype,'Credit Card Number' = CardNumber
                    'Expiry Year'= ExpYear
                    From Sales.CreditCard


3.Display the Customer ID and the account number of all the customer who live in the TerritoryID 4

ANSWER: Select Customer Id,account number
                   From Sales.Customer
                   Where TerritoryID = 4


4.Display all the details of the sales order that have a cost exceeding $2000.

ANSWER: Select *
                   From Sales.SalesOrderDetail
                   Where LineTotal > 2000.00


5.Display the sales order details of the product named 'Cable Lock' for which the product Id is 843.

ANSWER: Select *
                   From Sales.SalesOrderDetail
                   Where Product Id =843


6.Display the list of all the orders placed on June 06,2004.

ANSWER: Select *
                   From Sales.SalesOrderHeader
                   Where Order Date = '06-06-2004'

                   
7.Display the Name,Country region code,and sales year to date for territory with Territory ID as 1.

ANSWER: Select Country Region Code,Sales YTD
                   From Sales.SalesTerritory 
                   Where Territory ID = 1


8.Display lists of all the sales orders in the price range of $ 2000 to $ 2100.

ANSWER: Select *
                   From Sales.SalesOrderDetail
                   Where Line Total between 2000 and 2100


9.Display the Sales territory details of Canada,France and Germany.

ANSWER: Select *
                   From Sales.SalesTerritory 
                   Where name IN ('Canada','France','Germany')


10.Display the details of the orders that have tax amount of more than $10000.

ANSWER:  Select *
                   From Sales.SalesOrderHeader
                   Where Tax amt >=10000

11.Generate a report that contains the IDs of sales persons living in the territory ID as 2 or 4.

ANSWER: Select Sales Person ID ,Territory ID
                   From Sales.SalesTerritoryHistory
                   Where Territory ID =2 OR Territory ID = 4

12.Display the detail of the vista credit cards that are expiring in the year 2006.

ANSWER: Select *
                   From Sales.CreditCard
                   Where Card type = 'Vista' AND ExpYear = '2006' 

13.Display the Orders placed on July 01,2001 that have a total cost of more than $ 10000.

ANSWER: Select 'Order Number' = Sales Order ID ,'Order Date' =                    Order Date,Status,'Total Cost' = Total Due
                   From Sales.SalesOrderHeader
                   Where Order Date = '07-01-2001' 
                   AND Total Due >10000

14.Display the details of the all orders that were shipped after
  July 12,2004.

ANSWER: Select *
                   From Sales.SalesOrderHeader
                   Where Ship Date >'2004-07-12'

15.Display a report of all the Orders.

ANSWER: Select 'Order ID' = Sales Order ID, 'Order Quantity' =                      Order Qty,'Unit Price' = Unit Price, Total Cost = Order                    Qty * Unit Price 
                   From Sales.SalesOrderDetail

16.Display the details of the orders that have been placed by customers online.

ANSWER: Select *
                   From Sales.SalesOrderHeader
                   Where OnlineOrderFlag = 1

17.Display the Order ID and the Total amount due of all the sales orders.Ensure that the order with the highest price is at the top of the list.

ANSWER: Select 'Order ID' = Sales Order ID, 'Total Due' = Total                      Due
                   From Sales.SalesOrderHeader
                   Order by Total Due desc

18.Display the Order ID and Tax amount for the sales orders that are less than $2000. The data should be displayed in ascending order.

ANSWER: Select Sales Order ID, Tax Amt
                   From Sales.SalesOrderHeader
                   Where Total Due <2000
                   Order by Total Due asc

19.Display the Order number and the total value of the order in ascending order of the total value.

ANSWER: Select Sales Order ID, Total Due
                   From Sales.SalesOrderHeader
                   Order by Total Due asc

20.Display the details of all the currencies that have the word 'Dollar' in their name.

ANSWER: Select *
                   From Sales.Currency
                   Where Name like '%Dollar%'