SQL LAB at Home 2

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

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'=CreditCardID,
'card Type'=CardType,
'CardNumber'=CardNumber,
'Expyear'= 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
where LineTotal>2000.00

-------------------------------------------------------------------------------------------------------------------------------------

5. Display the sales order details of the product named 'Cable Lock' for which the ProductId is 843. (Use the AdventureWorks database)

elect * 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.SalesOrderHeader
where ShipDate>'2004-7-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,SalesYTD 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
where LineTotal
between 2000 and 2100

--------------------------------------------------------------------------------------------------------------------------------------

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

select * from Sales.SalesTerritory
where Name in ('Conada','France','Germany')

---------------------------------------------------------------------------------------------------------------------------------------

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
where TaxAmt>1000

------------------------------------------------------------------------------------------------------------------------------------

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.SalesOrderHeader
select 'sales person ID'=SalesPersonID,'Territory ID'=TerritoryID from Sales.SalesOrderHeader
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
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 Sales.SalesOrderHeader
where ShipDate>'2004-07-12'

-----------------------------------------------------------------------------------------------------------------------------------

14. Display the orders placed on July 01, 2001 that have a total cost of more than $ 10,000 in the following format. (Use the AdventureWorks database)

select * from Sales.SalesOrderHeader
select SalesOrderID,orderdate,status,subtotal from Sales.SalesOrderHeader
where ShipDate='2001-07-01'and SubTotal>10000

-------------------------------------------------------------------------------------------------------------------------------------

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

select * from Sales.SalesOrderHeader
where OnlineOrderFlag=1

-----------------------------------------------------------------------------------------------------------------------------------

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

select * from Sales.SalesOrderDetail
select 'Order ID'= salesOrderID,'Order Quantity'=OrderQty,'unit Price'=unitPrice,'Total cost'= OrderQty*UnitPrice from Sales.SalesOrderDetail

--------------------------------------------------------------------------------------------------------------------------------------

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 Sales.SalesOrderHeader
select SalesOrderID,TaxAmt from Sales.SalesOrderHeader
where TotalDue < 2000
 order by TotalDue 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 Sales.SalesOrderHeader
select 'Order Id'= SalesOrderID,'Total Due'=TotalDue from Sales.SalesOrderHeader
Order by TotalDue Desc

--------------------------------------------------------------------------------------------------------------------------------------

19.Display the details of all the currencies that have the word 'Dollar' in their name. (Use the AdventureWorks database)

select * from Sales.Currency
where Name 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 Sales.SalesOrderHeader
select SalesOrderId,TotalDue from Sales.SalesOrderHeader
order by TotalDue ASC

------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------

                                         GNIITSolution 

                               GNIIT/NIIT Solution: -Sonugiri0032@gmail.com