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 TotalUnitPrice,sum (LineTotal) as TotalAmount from sales.salesOrderDetail
where productID in (777,774) group by cube(ProductID)

17. Write a query to display a report that contains the Product ID and its availability status in the following format.

The values in the Availability of Product column can be Available or Not Available. (Use the
AdventureWorks database)



select productID,iif(makeflag=1,'Available','NotAvailable') 
as 
AvailabilityOfProduct from Production.product

16. 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)

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(20)) as ListPrice from Production.Product
where ListPrice BETWEEN 360.00 and 499.00;

14. 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 Maxinum
from sales.salesorderDeteil
where LineTotal>5000
group by salesorderID

13. Display the total amount collected from the orders for each order date. (Use the AdventureWorks database)

select * from sales.salesOrderDitail
select sum (amount) as 'total' from sales.salesorderdilail
group by date
having date='2001-07-12'

12. 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 productID(productID)

11. 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. (Use the AdventureWorks database)

select ProductID,sum(LineTotal) as Total from sales.salesorderDetail
Group by productID
Having sum(LineTotal)>10000

10. 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 maximum, minimum, and the average rate of sales orders. (Use the AdventureWorks database)

select 'Maximum'=MAX(TotalDue),'Mininum'=MIN(TotalDue),'Average'=AVG(TotalDue) from sales.salesorderHeader

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

select 'Total value of the Order'=sum(TotalDue)
from sales.salesOrderHeader

7. 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

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

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 * from Sales.SalesOrderDetail
select 'sales order ID'= SalesorderID,'Average value'=Avg(lineTotal) from Sales.SalesOrderDetail
Group by SalesOrderID

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

select * from Sales.SalesOrderHeader
select 'Order Number'= salesOrderID,'Total Due'=TotalDue,'Day of order'=DATEPART(DD,OrderDate),'Week day'=DATEPART(DW,orderDate) from Sales.SalesOrderHeader

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

select * from Person.Contact
select concat(FirstName,MiddleName,LastName) AS person_Name from Person.Contact

2.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 * from HumanResources.Employee
select EmployeeID, MONTH=DATENAME(MM,HireDate),YEAR=DATENAME(YY,HireDate)
from HumanResources.Employee

1.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 * from Sales.SalesOrderDetail
select productID, SUM (LineTotal) as 'Total' from Sales.SalesOrderDetail
Group by cube (ProductID)

6. Each time the salary slip for an employee is generated, the referral bonus (if present) has to be calculated and printed in the salary slip. The following tables are used for solving the preceding query.

11. Consider the following Purchase_Details table.

Cust_ID and StoreID make the composite primary key in the table. Identify the partial dependency
in the table, if any. How can you remove the partial dependency to attain the next normal form?

10. Consider the following Student table.

The preceding table is in the first normal form. How can this table be converted into the second
normal form?

9. Consider the following Product table.

The preceding table is not normalized. How can this table be converted into the first normal form?

8. New Heights is a training institute that provides courses on various nontechnical subjects, such as personality improvement and foreign languages. Xuan, the Database Designer, has made the following relations to represent the data about students, batches, and modules covered in the batches: STUD-ID: Student's id (unique) NAME: Name of student BATCH-NO: Batch number (one student can belong to only one batch) SLOT: Time and day when the batch of students attends the class MODULE: Module or subject (one batch will do several modules) MARKS: Marks obtained in a module test Xuan now needs to simplify the above relations by normalizing them

Q.5.

select * from [Sales].[SalesOrderDetail]
where [ModifiedDate]='2004-06-06'

Q.4. NIIT SQL LAB@ HOME 2,

select * from [Sales].[SalesOrderDetail]
where [UnitPrice]>2000

Q.3. NIIT SQL LAB@ HOME 2,

select [CustomerID],[AccountNumber] from [Sales].[Customer]
where [TerritoryID]=4

Q.2 .NIIT SQL LAB@ HOME 2,

select * from [Sales].[Customer]

Q.1

select [CreditCardID] as 'Credit CardID',[CardType] as 'Credit Card Type',[CardNumber] as 'Credit Card Number',[ExpYear] as'Expiry Year' from [Sales].[CreditCard]

Display a report that contains the employee ID, login ID, and the title of employees. The report should display the records for 10 employees after excluding the records of the first five employees. (Use the AdventureWorks database)

select top 10 EmployeeID,LoginID,Title from HumanResources.Employee
where EmployeeID not between 1 and 5

Display the different types of credit cards used for purchasing products. (Use the AdventureWorks database)

select distinct CardType from Sales.CreditCard

Display the top three sales persons based on the bonus. (Use the AdventureWorks database)

select top(3) Bonus,SalesPersonID from Sales.SalesPerson

Display the SalesPerson ID, the Territory ID, and the Sales Quota for those sales persons who have been assigned a sales quota. The data should be displayed in the following format. (Use the AdventureWorks database)

select SalesPersonID as 'Sales Person ID',
TerritoryID as 'Territory ID',
SalesQuota as 'Sales Quota' from Sales.SalesPerson

Display all territories whose names begin with 'N'. (Use the AdventureWorks database)

select * from Sales.SalesTerritory
where Name like 'N%'

Display the details of those stores that have Bike in their name. (Use the AdventureWorks database)

select * from Sales.Store
where Name like '%Bike%'