SQL LAB @ HOME 4 ~ NIIT POST

sql lab @ home 4

1.Display EmployeeID and HireDate of the employees from the Employee table. The month and the year need to be displayed. (Use the AdventureWorks database)

SELECT EmployeeID,
MONTH = DATENAME(MM,HireDate),
YEAR = DATENAME(YY,HireDate)
FROM HumanResources.Employee

2.Consider the following SQL query:
SELECT ProductID, LineTotal AS 'Total'
FROM Sales.SalesOrderDetail
Group By Cube(LineTotal)

Once executed, the preceding query generates errors. Identify the possible causes of such errors and rectify the same. (Use the AdventureWorks database)

SELECT ProductID, SUM(LineTotal) AS 'Total'
FROM Sales.SalesOrderDetail
GROUP BY CUBE(ProductID)


3.Write a query to display the full name of a person in a column named Person Name. (Use the AdventureWorks database)

SELECT CONCAT(FirstName,MiddleName,LastName)
AS PERSON_NAME FROM Person.Contact

4.Display the details of all orders in the following format. (Use the AdventureWorks database)


 SELECT 'Order Number' = SalesOrderID,
'Total Due' = TotalDue,
'Day of Order' = DATEPART(DD,OrderDate),
'Week Day' = DATENAME(DW,OrderDate)
FROM Sales.SalesOrderHeader

5.  Display a report containing the sales order ID and the average value of the total amount greater than $ 5,000 in the following format. (Use the AdventureWorks database)

SELECT 'Sales order ID' = SalesOrderID,
'Average Value' = AVG(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING AVG(LineTotal) > 5000

6.Display the customer ID, name, and sales person ID for all the stores. According to the requirement, only first 15 letters of the customer name should be displayed. (Use the AdventureWorks database)

 SELECT CustomerID, Name = LEFT(Name, 15),
SalesPersonID FROM Sales.Store

7. Display the total value of all the orders put together. (Use the AdventureWorks database)

SELECT 'Total value of all orders' = SUM(TotalDue)
FROM Sales.SalesOrderHeader

8.   What will be the output of the following code written to display the total order value for each order? (Use the AdventureWorks database)
SELECT SalesOrderID,ProductID,sum(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID


SELECT SalesOrderID,ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID, ProductID
9.  Display the Order ID of the top five orders based on the total amount due in the year 2001. (Use the AdventureWorks database)

SELECT TOP 5 SalesOrderID
FROM Sales.SalesOrderHeader
WHERE DATEPART(YYYY,OrderDate) = 2001
ORDER BY TotalDue DESC

10.    Display the maximum, minimum, and the average rate of sales orders. (Use the AdventureWorks database)

SELECT 'Maximum' = MAX(TotalDue),
'Minimum' = MIN(TotalDue),
'Average' = AVG(TotalDue)
FROM Sales.SalesOrderHeader
11.   Display the total amount collected from the orders for each order date. (Use the AdventureWorks database)

SELECT OrderDate, SUM(TotalDue)
FROM Sales.SalesOrderHeader
GROUP BY OrderDate


12.  Display the sales order ID and the maximum and minimum values of the order based on the sales order ID. In addition, ensure that the order amount is greater than $ 5,000. (Use the AdventureWorks database)

SELECT SalesOrderID,
MIN(LineTotal) AS 'Minimum',
MAX(LineTotal) AS 'Maximum'
FROM Sales.SalesOrderDetail
WHERE LineTotal > 5000
GROUP BY SalesOrderID

13.  Consider the following SQL query containing the ROLLUP operator:
SELECT ProductID, LineTotal AS 'Total'
FROM Sales.SalesOrderDetail
GROUP BY ROLLUP (ProductID)

The preceding query generates errors during execution. Identify the possible causes of such errors and rectify? (Use the AdventureWorks database)

SELECT ProductID, SUM(LineTotal) AS 'Total'
FROM SALES.SalesOrderDetail
GROUP BY ROLLUP (ProductID)
14.

SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > 10000

15.   Write a query to retrieve the list price of the products where the product price is between $ 360.00 and $ 499.00 and display the price in the following format: The list price of "Product Name" is "Price". (Use the AdventureWorks database)

SELECT 'The list price of ' + Name + ' is ' +
CAST(ListPrice AS VARCHAR(12)) AS ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 360.00 AND 499.00


16.  Write a query to display a report that contains the Product ID and its availability status in the following formatThe values in the Availability of Product column can be Available or Not Available. (Use the AdventureWorks database)

SELECT ProductID,
IFF(MakeFlag=1, 'Available', 'NotAvailable')
AS AvailabilityofProduct
FROM Production.Product

17.   Display the sum of sales amount earned by each sales person and the sum of sales amount earned by the all the salespersons. (Use the AdventureWorks database)

SELECT SalesPersonID, SUM(SalesQuota)
AS SalesQuota
FROM Sales.SalesPersonQuotaHistory
GROUP BY ROLLUP(SalesPersonID)

18.   Display the total unit price and the total amount collected after selling the products, 774 and 777. In addition, calculate the total amount collected from these two products. (Use the AdventureWorks database)

SELECT ProductID,SUM(UnitPrice) AS TotalUnitePrice,
SUM(LineTotal) AS TotalAmount
FROM SALES.SalesOrderDetail
WHERE ProductID IN (774,777)
GROUP BY CUBE(ProductID)