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%'
