NIIT SQL LAB@HOME 4
1.Display the Employee ID and the Hire Date of the Employees from the Employee table.The month and the year need to be displayed.
ANSWER: select Employee ID, 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(Line Total)
ANSWER: The code will give following error message:
Msg 8120 ,Level 16 State 1 Line 1 Column 'Sales.SalesOrderDetail.ProductID'
is invalid in the select list because it is not contained in
either an aggregate function or GROUP BY clause
The correct code is:
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.
ANSWER: select CONCAT(FirstName,MiddleName,LastName)as PersonName
from Person.Person
4.Display the details of all the orders
ANSWER: select 'Order No.'=SalesOrderID,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 $5000.
ANSWER: select 'Average Value'=AVG(LineTotal),SalesOrderID
from Sales.SalesOrderDetail
group by SalesOrderID Having AVG(LineTotal)>5000
6.Display the customer ID, name,and sales person ID for all stores.According to the requirement, only first 15 letters of the customer name should be displayed.
ANSWER: selectCustomerID,Name=LEFT(Name,15),
SalesPersonID
from Sales.Store
7.Display the total value of all the orders put together.
ANSWER: select 'Total Value of all order'=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?
Select SalesOrderID,ProductID,sum(LineTotal)
From Sales.SalesOrderDetail
Group by SalesOrderID
ANSWER: The code will give following error message:
Msg 8120 ,Level 16 State 1 Line 1 Column 'Sales.SalesOrderDetail.ProductID'
is invalid in the select list because it is not contained in
either an aggregate function or GROUP BY clause
The correct code is:
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.
ANSWER: select top 5 SalesOrderID,OrderDate
from Sales.SalesOrderHeader
where DATEPART(yyyy,OrderDate)=2001
Order by TotalDue desc
10.Display the maximum,minimum,and the average rate of sales orders.
ANSWER: 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.
ANSWER: 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 $5000.
ANSWER: 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)
ANSWER: The code will give following error message:
Msg 8120 ,Level 16 State 1 Line 1 Column 'Sales.SalesOrderDetail.ProductID'
is invalid in the select list because it is not contained in
either an aggregate function or GROUP BY clause
The correct code is:
select ProductID,SUM(LineTotal) as 'Total'
from Sales.SalesOrderDetail
Group by ROLLUP(ProductID)
14.Display a report containing the product ID and the total cost of products for the product ID whose total cost is more than $10,000.
ANSWER: 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 products price is between $360.00 and $499.00 and display the price in the following format:
The list price of "Product Name" is "Price"
ANSWER: select 'list price of'+ Name + 'is' + CAST(ListPrice as varchar(12))as PriceList
from Production.Product
where ListPrice BETWEEN 360.00 AND 490.00
16.Write a query to display a report that contains the Product ID and its availability status.
ANSWER: select ProductID,IIF(MakeFlag=1,'Available',
'NotAvailable')AS AvaibilityOfProduct
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 sales persons.
ANSWER: 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.
ANSWER: select ProductID,SUM(UnitPrice)as TotalUnitPrice, SUM(LineTotal)as TotalAmt
from Sales.SalesOrderDetail
where ProductID IN (777,774)
group by cube (ProductID)
