SQL LAB at Home 2

SQL LAB at Home 2

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



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




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





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




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



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



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'





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'




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



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





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




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



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




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'




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





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



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




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




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

select * from Sales.Customer