SQL LAB @ HOME 2

                                     

SQL LAB @ HOME 2

1.   Display the details of all the customers. (Use the AdventureWorks database)ans:

select * from [Sales].[Customer]

=================================================================================

2.    Display the ID, type, number, and expiry year of all the credit cards in the following format. (Use
the AdventureWorks database)


select * from [Sales].[CreditCard]
  select CreditCardID,CardType,CardNumber,ExpYear from [Sales].[CreditCard]

=================================================================================

3.   Display the customer ID and the account number of all the customers who live in the TerritoryID 4.
(Use the AdventureWorks database)

select * from [Sales].[Customer]
  select CustomerID,AccountNumber from [Sales].[Customer] where TerritoryID=4
=================================================================================
4.    Display all the details of the sales orders that have a cost exceeding $ 2,000. (Use the
AdventureWorks database)

select * from [Sales].[SalesOrderDetail]
select UnitPrice from [Sales].[SalesOrderDetail] where UnitPrice>2000
=================================================================================
5.  Display the sales order details of the product named 'Cable Lock' for which the ProductId is 843.
(Use the AdventureWorks database)

select * from [Sales].[SalesOrderDetail]
 select SalesOrderDetailID from [Sales].[SalesOrderDetail] where ProductID=843
=================================================================================
6.   Display the list of all the orders placed on June 06, 2004. (Use the AdventureWorks database)

select * from [Sales].[SalesOrderDetail]
 select ModifiedDate from [Sales].[SalesOrderDetail] where ModifiedDate=2004-06-06 
=================================================================================
7.  Display the name, country region code, and sales year to date for the territory with Territory ID as
1. (Use the AdventureWorks database)


select * from [Sales].[SalesTerritory]
select Name,CountryRegionCode,SalesLastYear from [Sales].[SalesTerritory] where TerritoryID=1
=================================================================================

8. Display a list of all the sales orders in the price range of $ 2,000 to $ 2,100. (Use the
AdventureWorks database)

select * from [Sales].[SalesOrderDetail]
 select SalesOrderID,SalesOrderDetailID,UnitPrice from [Sales].[SalesOrderDetail] where UnitPrice>2000 AND UnitPrice<2100

=================================================================================
9.   Display the sales territory details of Canada, France, and Germany. (Use the AdventureWorks
database)

select * from [Sales].[SalesTerritory]
 select SalesYTD from  [Sales].[SalesTerritory]  where CountryRegionCode='CA' or CountryRegionCode='DE' or CountryRegionCode='FR'
=================================================================================

10.  Display the details of the orders that have a tax amount of more than $ 10,000. (Use the
AdventureWorks database)

select * from [Sales].[SalesOrderHeader]
  select SalesOrderID from [Sales].[SalesOrderHeader] where TaxAmt>=10000

=================================================================================
11.   Generate a report that contains the IDs of sales persons living in the territory with TerritoryID as 2
or 4. The report is required in the following format. (Use the AdventureWorks database)


 select * from [Sales].[SalesPerson]
  select SalesPersonID,TerritoryID from [Sales].[SalesPerson] where TerritoryID='2' or TerritoryID='4'

=================================================================================
12.   Display the details of the Vista credit cards that are expiring in the year 2006. (Use the
AdventureWorks database)

select * from [Sales].[CreditCard]
  select CardType,ExpMonth,ExpYear from [Sales].[CreditCard] where CardType='Vista' AND ExpYear=2006
=================================================================================

13.  Display the details of all the orders that were shipped after July 12, 2004. (Use the
AdventureWorks database)

select * from table
 select OrderDetails from Table Where receipt_date = '2004-07-12'

=================================================================================
14.  select * from table
 select OrderDetails from Table Where receipt_date = '2004-07-12'




select * from Table
  select OrderPlaced,TotalCost Where OrderPlaced='2001-07-01' AND TotalCost>=10000

=================================================================================
15.  Display the details of the orders that have been placed by customers online. (Use the
AdventureWorks database)

select * from Table
   select  Order where Order='online' 

=================================================================================
16.  Display a report of all the orders in the following format. (Use the AdventureWorks database)












select * from [dbo].[FactInternetSales]
   select SalesOrderNumber,OrderQuantity,UnitPrice,TotalProductCost from [dbo].[FactInternetSales]

=================================================================================
17.   Display the order ID and the tax amount for the sales orders that are less than $ 2,000. The data
should be displayed in ascending order. (Use the AdventureWorks database)

select * from [dbo].[FactInternetSales]
select OrderDateKey,TaxAmt from [dbo].[FactInternetSales] where UnitPrice<2000 ORDER BY 'OrderDateKey' ASC

=================================================================================
18.   Display the order ID and the total amount due of all the sales orders in the following format.
Ensure that the order with the highest price is at the top of the list. (Use the AdventureWorks
database)




select * from [dbo].[FactInternetSales]
select ProductKey,DueDateKey,SalesAmount from [dbo].[FactInternetSales]  ORDER BY 'SalesAmount' DESC
=================================================================================
19. Display the details of all the currencies that have the word 'Dollar' in their name. (Use the
AdventureWorks database)


select * from [dbo].[DimCurrency] where CurrencyName like '[Dollar]%'
=================================================================================
20.    Display the order number and the total value of the order in ascending order of the total value.
(Use the AdventureWorks database)

select * from[dbo].[FactInternetSales]
select OrderdateKey,TotalProductCost from [dbo].[FactInternetSales] ORDER BY TotalProductCost ASC