ankitwaretechstudies.blogspot.com
sonugiri.blogspot.com
1. Design an ER diagram for the following situation:
An organization has two types of employees, salaried and wage earning. Both the types of
employees have some common properties, such as employee code, employee name, and
employee address. However, the salaried employees have other additional properties: basic,
allowance, and House Rent Allowance (HRA). The wage earning employees have distinct
properties that are daily wage and overtime.
_____________________________________________________________________________
2. Shopping Spree is a leading departmental store in Shanghai. The store has a number of regular
customers who purchase bulk items. The store also conducts regular feedback sessions to
analyze customer satisfaction levels. Chen, the Customer Analyst of Shopping Spree, has to make
the ER diagram to represent the preceding situation, and then to map the ER diagram to the
corresponding tables. Help Chen to do the same.
_____________________________________________________________________________
3. Consider the following DoctorDetails table.
In the preceding table, you have to identify primary key, candidate key, and alternate key and give
the reason.
Candidate Key :DoctorID,ShiftID
Alternate Key : DoctorDutyID
doctorID and shiftID are individually unique in every row.
Therefore the coloumns, doctorID and ShiftID ,are candidate keys for the primary key.
The Shift id coloumn may contain duplicate values as two doctors may be on the same shift.
DoctorID should be chosen as the primary key and shiftid as the alternate key.
_____________________________________________________________________________
4. Tom is working in an organization as a database administrator. Based on the hierarchy, the
organization has multiple departments and each department has multiple employees. The
following ER diagram represents the relationship between the departments and the employees.
In the preceding diagram, you have to identify entities, their attributes, and type of relationship
between the entities.
Attributes of department : DepID,DepName and DepId
Attributes of employee : EmpID, EmpName ,and DepID
Type of Relationship : Many to many
_____________________________________________________________________________
5. You have been hired by a regional hospital to create a database that will be used to store the
information of the patients. Implementing the database will make the data easily accessible. The
following ER diagram represents the relationship between the patient and the doctor.
In the preceding diagram, you have to identify the entities, their attributes, and the type of
relationship between the entities.
Attributes for Patient : patient name , date admitted , patient id
Attributes for doctor : Doc_ID , Specialization , Doc_Name
Relationship-Many to many
_____________________________________________________________________________
6. Consider the following scenario:
An author can write multiple books and a book can be written by more than one author. Identify
the relationship between the author entity and the book entity and represent it with an ER
Diagram
7. Lee Wong is the newly appointed database administrator at Standard bank. The management of
this bank wants to computerize the process of banking. According to the bank's policy, one
customer can have many accounts but one account cannot be shared by many customers. The
following ER diagram represents the relationship between a customer and the accounts owned by
a particular customer.
In the preceding diagram, you have to identify entities, their attributes, and type of relationship
between the entities.
Attributes for Customer : CUSTOMER NAME , SSN NUMBER , CUSTOMER ADDRESS
Attributes for Account : ACCOUNT NUMBER , BALANCE
Relationship : Many to One
_____________________________________________________________________________
8. Consider the following scenario:
In a college, there are multiple departments. Each department has various faculty members. Only
one of these faculty members holds the position of the Head of the Department. Identify the
relationship between the faculty and the department and represent it with an ER Diagram.
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,CardType,CardNumber,ExpYear from [Sales].[CreditCard]
_____________________________________________________________________________
3. Display the customer ID and the account number of all the customers who live in the TerritoryID
(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]
select UnitPrice from [Sales].[SalesOrderDetail] where UnitPrice>2000
_____________________________________________________________________________
5. Display the sales order details of the product named 'Cable Lock' for which the ProductId is 843.
(Use the AdventureWorks database)
select * from [Sales].[SalesOrderDetail]
select SalesOrderDetailID 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].[SalesOrderDetail]
select ModifiedDate from [Sales].[SalesOrderDetail] where ModifiedDate=2004-06-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,SalesLastYear 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]
select SalesOrderID,SalesOrderDetailID,UnitPrice from [Sales].[SalesOrderDetail] where UnitPrice>2000 AND UnitPrice<2100
_____________________________________________________________________________
9. Display the sales territory details of Canada, France, and Germany. (Use the AdventureWorks
database)
select * from [Sales].[SalesTerritory]
select SalesYTD from [Sales].[SalesTerritory] where CountryRegionCode='CA' or CountryRegionCode='DE' or CountryRegionCode='FR'
_____________________________________________________________________________
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]
select SalesOrderID from [Sales].[SalesOrderHeader] where TaxAmt>=10000
_____________________________________________________________________________
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].[SalesPerson]
select SalesPersonID,TerritoryID from [Sales].[SalesPerson] 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]
select CardType,ExpMonth,ExpYear 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 table
select OrderDetails from Table Where receipt_date = '2004-07-12'
_____________________________________________________________________________
14. select * from table
select OrderDetails from Table Where receipt_date = '2004-07-12'
select * from Table
select OrderPlaced,TotalCost Where OrderPlaced='2001-07-01' AND TotalCost>=10000
_____________________________________________________________________________
15. Display the details of the orders that have been placed by customers online. (Use the
AdventureWorks database)
select * from Table
select Order where Order='online'
_____________________________________________________________________________
16. Display a report of all the orders in the following format. (Use the AdventureWorks database)
select * from [dbo].[FactInternetSales]
select SalesOrderNumber,OrderQuantity,UnitPrice,TotalProductCost from [dbo].[FactInternetSales]
_____________________________________________________________________________
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 [dbo].[FactInternetSales]
select OrderDateKey,TaxAmt from [dbo].[FactInternetSales] where UnitPrice<2000 ORDER BY 'OrderDateKey' 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 [dbo].[FactInternetSales]
select ProductKey,DueDateKey,SalesAmount from [dbo].[FactInternetSales] ORDER BY 'SalesAmount' DESC
_____________________________________________________________________________
19. Display the details of all the currencies that have the word 'Dollar' in their name. (Use the
AdventureWorks database)
select * from [dbo].[DimCurrency] where CurrencyName 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[dbo].[FactInternetSales]
select OrderdateKey,TotalProductCost from [dbo].[FactInternetSales] ORDER BY TotalProductCost ASC
1. Display all territories whose names begin with 'N'. (Use the AdventureWorks database)
select * from [Sales].[SalesTerritory]
select Name from [Sales].[SalesTerritory] where Name like 'N%'
_____________________________________________________________________________
2. Display the details of those stores that have Bike in their name. (Use the AdventureWorks
database)
select * from [Sales].[Store]
select Name from [Sales].[Store] where Name like '%bike%'
_____________________________________________________________________________
3. 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 * from [Sales].[SalesPerson]
select BusinessEntityID,TerritoryID,SalesQuota from [Sales].[SalesPerson] where SalesQuota IS NOT NULL
_____________________________________________________________________________
4. Display the top three sales persons based on the bonus. (Use the AdventureWorks database)
select * from [Sales].[SalesPerson]
select Top 3 * from [Sales].[SalesPerson]
_____________________________________________________________________________
5. Display the different types of credit cards used for purchasing products. (Use the AdventureWorks
database)
select * From [Sales].[CreditCard]
select Distinct CardType from [Sales].[CreditCard]
_____________________________________________________________________________
6. 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 * from [HumanResources].[Employee]
select top(10-5) BusinessEntityID,LoginID,JobTitle from [HumanResources].[Employee]
_____________________________________________________________________________
7. 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.
Though the table structures are large, it is necessary to improve the performance of this query by
modifying the table structures. Identify how to increase the performance of queries.
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.
_____________________________________________________________________________
9. Consider the following Product table.
The preceding table is not normalized. How can this table be converted into the first 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?
_____________________________________________________________________________
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?
_____________________________________________________________________________
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)
_____________________________________________________________________________
1. Write a query to display the sales person ID of all the sales persons and the name of the territory
to which they belong.
SELECT SalesPersonID,Name FROM Sales.SalesPerson
JOIN Sales.SalesTerritory
ON SalesPerson.TerritoryID=SalesTerritory.TerritoryID
_____________________________________________________________________________
2. Write a query to display the sales person ID, territory ID, and territory name of all the sales
persons in the following format
SELECT 'Person ID'=SalesPersonID,
'Territory ID'=s.TerritoryID,Name FROM Sales.SalesPerson s
JOIN Sales.SalesTerritory st
on s.Territory ID=st.Territory ID
_____________________________________________________________________________
3. Write a query to display the sales order ID, the product ID, and the order date for all the products
in the following format.
SELECT 'Order ID'=h.SalesOrderID,'Product ID'=d.ProductID,'Order Date'=OrderDate FROM Sales.SalesOrderHeader h
join Sales.SalesOrderDetail d
ON h.SalesOrderID=d.SalesOrderID
_____________________________________________________________________________
4. Write a query to display the sales order ID, territory name, month, and year of all the sales orders
in the following format.
SELECT SalesOrderID,'Territory Name'=Name,
Month=Datename(mm,OrderDate),
Year=Datename(yy,OrderDate)
FROM Slaes.SalesOrderHeader s
JOIN Sales.SalesTerritory t
ON s.TerritoryID=t.TerritoryID
_____________________________________________________________________________
5. Write a query to display the order number, territory name, order date, and the quarter in which
each order was placed in the following format.
SELECT SalesOrderID,'Territory Name'=Name,
OrderDate,Quarter=datepart(qq,OrderDate)
FROM Sales.SalesOrderHeader s
JOIN Sales.SalesTerritory t
ON s.TerritoryID=t.TerritoryID
_____________________________________________________________________________
6.Write a query to display the total amount due for all the sales orders rounded off to a whole
number. In addition, display the sales order ID and the type of credit card through which the
payment was made.
SELECT SalesOrderID,CardType,'Total Due'=Round(TotalDue,0) FROM Sales.SalesOrderHeader s
JOIN Sales.CreditCard c
ON s.CreditCardID=c.CreditCardID
_____________________________________________________________________________
7.Write a query to display all the country region codes along with their corresponding territory IDs.
SELECT c.CountryRegionCode,TerritoryID
FROM Sales.SalesTerritory s
RIGHT OUTER JOIN Sales.CountryRegionCurrency c
ON s.CountryRegionCode=c.CountryRegionCode
_____________________________________________________________________________
8.Write a query to display the total amount due for all the orders in the AdventureWorks database in
the following format:
SELECT 'Order VAlue'='The Total Amount Due For The Sales Order ID: '+ Convert(Varchar(10),SalesOrderID)+'is$'+Convert(Varchar(10),TotalDue) FROM Sales.SalesOrderHeader
_____________________________________________________________________________
9.Write a query to display the order date along with the sales order ID and the territory name. The
order date should be displayed in the dd/mm/yyyy format.
SELECT SalesOrderID,Name,Convert(Char(10),OrderDate,103 ) as 'Order Date' FROM Sales.SalesOrderHeader s
JOIN Sales.SalesTerritory t
ON t.TerritoryID=s.TerritoryID
_____________________________________________________________________________
10. Write a query to display the sales order ID and the territory name of the orders where the month of
the order is May and the year is 2004.
Select SalesOrderID,Name FROM Sales.SalesOrderHeader s
JOIN Sales.SalesTerritory t
ON s.TerritoryID=t.TerritoryID
WHERE DATENAME(mm,OrderDate)='May' AND DATEPART(yy,OrderDate)=2004
_____________________________________________________________________________
11.Write a query to display the contact ID of the customers that have the 'Vista' credit card.
Select ContactID,CardType FROM Sales.ContactCreditCard
JOIN Sales.CreditCard
ON ContactCreditCard.CreditCardID=CreditCard.CreditCardID
WHERE CardType='Vista'
_____________________________________________________________________________
12.Create a report containing the sales order ID of those orders where the total value is greater than
the average of the total value of all the orders.
Select SalesOrderID,TotalDue FROM Sales.SalesOrderHeader WHERE TotalDue>(Select Avg(TotalDue)FROM SalesOrderHeader)
_____________________________________________________________________________
13.Write a query to display the sales order IDs of the orders received from the Northeast territory.
(Use the AdventureWorks database)
Select SalesOrderID,FROM Sales.SalesOrderHeader WHERE TerritoryID=(Select TerritoryID FROM Sales.SalesTerritory WHERE Name='NorthEast')
_____________________________________________________________________________
14.Write a query to display the sales order IDs of the orders that have been paid through a
SuperiorCard. (Use the AdventureWorks database)
Select SalesOrderID,FROM Sales.SalesOrderHeader WHERE CreditCardID in (Select CreditCardID FROM Sales.CreditCard WHERE CardType='SuperiorCard')
_____________________________________________________________________________
15. Write a query to display the sales order ID, the order detail ID, and the total value of those orders
where the total value is greater than the maximum of the total value of order ID 43662. (Use the
AdventureWorks database)
Select SalesOrderID,SalesOrderDetailID,LineTotal FROM Sales.SalesOrderDetail WHERE LineTotal> ALL(SELECT LineTotal FROM Sales.SalesOrderDetail WHERE SalesOrderID=43662)
_____________________________________________________________________________
16.Write a query to display the details of those orders for which no discount was offered. (Use the
AdventureWorks database)
Select *FROM Sales.SalesOrderDetail WHERE SpecialOfferID=(Select SpecialOfferID FROM SalesSpecialOffer WHERE Type='No Discount')
_____________________________________________________________________________
17.Write a query to display the sales order IDs and the credit card IDs of those cards which are
expiring in the year 2007. (Use the AdventureWorks database)
Select SalesOrderID,CreditCardID FROM Sales.SalesOrderHeader WHERE CreditCardID in (Select CreditCardID FROM Sales.CreditCard WHERE ExpYear=2007)
_____________________________________________________________________________
18. Write a query to display the average rate of the Australian Dollar, where the CurrencyRateDate is
July 1, 2004. (Use the AdventureWorks database)
Select AverageRate FROM Sales.CurrencyRate WHERE CurrencyRateDate='2004-07-01' AND ToCurrencyCode=(Select CurrencyCode FROM Sales.Currency WHERE Name='Australian Dollar')
1. Write a query to display EmployeeID of those employees who belong to the department, Tool
Design. (Use the AdventureWorks database)
select BusinessEntityID as 'EmployeeID',JobTiTle from [HumanResources].[Employee] where JobTitle='Tool Designer'
_____________________________________________________________________________
2. Write a query to display the credit card number of Catherine Abel. (Use the AdventureWorks
database)
Select CardNumber FROM Sales.CreditCard WHERE CreditCardID=(Select CreditCardID FROM Sales.ContactCreditCard WHERE ContactID=(Select ContactID FROM Person.Contact WHERE
FirstName='Catherine' AND LastName='Abel'))
_____________________________________________________________________________
3. Write a query to display the first name and the last name of the customers along with their credit
card identification number. (Use the AdventureWorks database)
Select FirstName,LastName,B.CreditCardID
FROM Person.Contact A CROSS APPLY (Select * FROM Sales.ContactCreditCard B WHERE B.ContactID=A.ContactID)B
_____________________________________________________________________________
4. Write a query to display the sales order IDs and the order detail IDs along with the total value of
those orders that have a total value greater than the average of the total value for the order ID.
(Use the AdventureWorks database)
Select SalesOrderDetailID,SalesOrderID,LineTotal
FROM Sales.SalesOrderDetail s1
WHERE s1.LineTotal>(Select AVG(s2.LineTotal)
FROM Sales.SalesOrderDetail s2 WHERE s1.SalesOrderID=s2.SalesOrderID)
_____________________________________________________________________________
5. Write a query to display the sales person ID and the total number of sales orders processed by
each sales person. In addition, display the commision earned by each sales person. Commision is
given to each sales person at the rate of $ 10 per order. Exclude the orders where sales person ID
is null. The details of the sales person who has got the maximum commision should be displayed
first in the result set. (Use the AdventureWorks database)
WITH Sales_CTE (SalesPersonID,TotalSalesOrder) AS
(
Select SalesPersonID, COUNT(SalesOrderID)
FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID
)
Select SalesPersonID,TotalSalesOrder,
Commission=TotalSalesOrder*10
FROM Sales_CTE Order By TotalSalesOrder desc
_____________________________________________________________________________
6. Write a query to display CreditCardID, CardNumber, and ExpYear of the credit cards that do not
expire in the year 2005 and 2007. (Use the AdventureWorks database)
select CreditCardID,CardType,ExpYear from [Sales].[CreditCard] where ExpYear!=2005 and ExpYear!=2007
_____________________________________________________________________________
7. Create a table named Recipient in the NarrowFabrics database to store the details of the
recipients to whom the orders are dispatched. The following table provides the structure of the
Recipient table.
USE PictureLibrary
CREATE TABLE OfficeLocation
(
Office_ID int NOT NULL,
Office_Manager varchar(30) NOT NULL,
Office_Location geography NOT NULL
)
_____________________________________________________________________________
13. Write a query to make EmpDetail_FG as the default filegroup for the EmpDetail database.
ALTER DATABASE EmpDetail
MODIFY FILEGROUP EmpDetail_FG DEFAULT
GO
1.Display SalesOrderID, OrderQty, and UnitPrice from the SalesOrderDetail table where a similar unit price needs to be marked with an identical value. (Use the AdventureWorks database)
DENSE_RANK() OVER (ORDER BY UnitPrice ASC) AS
RANK FROM Sales.SalesOrderDetail
_____________________________________________________________________________
2.Write a query to retrieve the details of the product locations where cost rate is greater than 12. In addition, the locations need to be grouped into three groups, and then ranked based on the cost rate in descending order. (Use the AdventureWorks database)
_____________________________________________________________________________
3.Consider the following table structures.
Refer the preceding table structures for the following requirements:
1. Create the Category table in the NarrowFabrics database. Enforce the following data integrity rules while creating the table:
--The CategoryId should be the primary key.
--The Category attribute should be unique but not the primary key.
--The Description attribute can allow storage of NULL values.
2. Create the ProductBrand table in the NarrowFabrics database. Enforce the following data integrity rules while creating the table:
--The BrandId should be the primary key.
--The BrandName should be unique but not the primary key.
3. Create the NewProduct table with the following data integrity rules in the NarrowFabrics database:
--The ProductId should be the primary key.
--The Qoh of the product should be between 0 and 200.
--The Photo and ProductImgPath attributes can allow storage of NULL values.
--The ProductName and ProductDescription attributes should not allow NULL values.
--The values of the CategoryId attribute should be present in the Category table.
4. Modify the NewProduct table to enforce the following data integrity rule in the NarrowFabrics database:
--The values entered in the BrandId attribute should be present in the ProductBrand table.
CREATE TABLE Category
(
CategoryID CHAR(3) CONSTRAINT pkCategoryID PRIMARY KEY,
Category CHAR(20) CONSTRAINT unqCategory UNIQUE,
Description VARCHAR(100) NULL
)
2.
CREATE TABLE ProductBrand
(
BrandID CHAR(3) CONSTRAINT pkBrandID PRIMARY KEY,
BrandName CHAR(20) CONSTRAINT unqBrandName UNIQUE
)
3.
CREATE TABLE NewProduct
(
ProductID CHAR(6) CONSTRAINT pkProductID PRIMARY KEY,
ProductName VARCHAR(20) NOT NULL,
ProductDescription VARCHAR(250) NOT NULL,
CategoryID CHAR(3) CONSTRAINT fkCategoryID
FOREIGN KEY REFERENCES Category(CategoryID),
ProductRate MONEY,
BrandID CHAR(3),
Photo IMAGE NULL,
Qoh SMALLINT CONSTRAINT chkqoh CHECK (QOH BETWEEN 0 AND 200),
ProductImgPath VARCHAR(50) NULL
)
4.
ALTER TABLE NewProduct
ADD CONSTRAINT fkBrandID
FOREIGN KEY (BrandID)
REFERENCES ProductBrand(BrandID)
_____________________________________________________________________________
4.Delete the Recipient table from the NarrowFabrics database.
DROP TABLE Recipient
_____________________________________________________________________________
5.The Recipient table and the Country table in the NarrowFabrics database do not have the same data type for the CountryId attribute. The following tables show the sample structure of the two tables.
Alter the Recipient or Country table so that they have the same data type for the CountryId attribute.
ANS:
USE NarrowFabrics
ALTER TABLE Country
ALTER COLUMN CountryID CHAR(2)
_____________________________________________________________________________
6.Write a query to return the current year price and the previous year price of a product with the Product ID, 715, over subsequent years. (Use the AdventureWorks database)
ANS:
SELECT ProductID, YEAR(StartDate)
AS SalesYear, ListPrice AS CurrentPrice,
LAG(ListPrice, 1,0) OVER (ORDER BY YEAR(Stardate))
AS PreviousPrice
FROM Production.ProductListPriceHistory
WHERE ProductID = 715
_____________________________________________________________________________
7.The following statement was used to remove the Category table: DELETE TABLE Category
The preceding statement displays an error and aborts. Identify the error and rectify it. (Use the NarrowFabrics database)
ANS:
USE NarrowFabrics
GO
DROP TABLE NewProduct
DROP TABLE Category
_____________________________________________________________________________
8.Create a table named LeaveDetail in the AdventureWorks database. The following table shows the structure of the LeaveDetail table.
CREATE TABLE LeaveDetail
(
Name VARCHAR(50) NOT NULL,
Reason VARCHAR(100) NOT NULL,
Number_of_days INT CONSTRAINT chknoday
CHECK (Number_of_days < 3)
)
_____________________________________________________________________________
9.Write the SQL statement to create a table named PictureDetails in the PictureLibrary database. The following table shows the structure of the PictureDetails table.
(
EventPicID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
EventName VARCHAR(30) NOT NULL,
PIC VARBINARY(MAX) NOT NULL
)
_____________________________________________________________________________
10.The check constraint is applied on the Number_of_days column of the LeaveDetail table. This check constraint ensures that the value inserted for the Number_of_days column is less than three. However, the leave of four days has been approved for John based on some valid reasons. The HR Manager wants to update the leave details for John but fails to assign the value, 4, in the Number_of_days column. As a database developer, what will you do to perform the preceding task? (Use the AdventureWorks database)
ALTER TABLE LeaveDetail
NOCHECK CONSTRAINT chknoday
INSERT INTO LeaveDetail
VALUES ('Tom','Fever'4)
ALTER TABLE LeaveDetail
CHECK CONSTRAINT chknoday
_____________________________________________________________________________
11.Write a query to display the structure of the Department table. (Use the AdventureWorks database)
_____________________________________________________________________________
12.Write a query to display the details of all the salespersons by using synonyms. (Use the AdventureWorks database)
CREATE SYNONYM SpersonDetail
FOR Adventureworks.Sales.Salesperson
/*Replace the existing query with the new query*/
SELECT * FROM SpersonDetail
_____________________________________________________________________________
13.Write a query to create a rule on the GroupName column of the Department table to accept only three values: Research and Development, Sales and Marketing, and Manufacturing. (Use the AdventureWorks database)
GO
CREATE RULE groupType
AS @gType IN ('Research and Development',
'Sales and Marketing', 'Manufacturing')
GO
sp_bindrule 'groupType', 'HumanResources.Department.GroupName'
_____________________________________________________________________________
14.Write a statement to create a user-defined data type named quality. This data type should be varchar(100) and should not contain NULL values.
FROM VARCHAR(100) NOT NULL;
GO
_____________________________________________________________________________
15.Insert the data shown in the following table into the ProductBrand table of the NarrowFabrics database.
INSERT INTO ProductBrand VALUES ('B02', 'Nike')
INSERT INTO ProductBrand VALUES ('B03', 'Reebok')
_____________________________________________________________________________
16.AdventureWorks, Inc. has set up a new store. Insert the data into the database as shown in the following table.
/*EXECUTE ABOVE LINE BY PRESSING F5. REPLACE THE EXISTING QUERY WITH THE NEW QUERY*/
INSERT INTO Sales.Store VALUES(29484, 'Car store', 285,
'<StoreSurvey XMLns="http://schemas.microsoft.com/sqlserver/
2004/07/adventure-works/StoreSurvey">
<AnnualSales>35000</AnnualSales>
<AnnualRevenue>35000</AnnualRevenue>
<BankName>InternationalBank</BankName>
<BusinessType>BM</BusinessType>
<YearOpened>1980</YearOpened>
<specialty>Road</specialty>
<SquareFeet>7500</SquareFeet>
<Brands>AW</Brands>
<Internet>T1</Internet>
<NumberEmployees>7</NumberEmployees>
</StoreSurvey>', DEFAULT, DEFAULT)
_____________________________________________________________________________
17.The address of a vendor, Comfort Road Bicycles, has changed. You need to update the data, as shown in the following table, in the AdventureWorks database.
SET AddressID =
(SELECT AddressID FROM Person.Address WHERE
AddressLine1 = '4151 Olivera'
AND City = 'Atlanta')
FROM Purchasing.VendorAddress VA, Purchasing.Vendor V
WHERE VA.VendorID = V.VendorID
AND V.Name = 'Comfort Road Bicycles'
1.Write an SQL statement to insert a record into the PictureDetails table, which is created in the PictureLibrary database. The following table shows the data to be inserted into the PictureDetails table.
(
EventPicID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
EventName VARCHAR(30) NOT NULL,
PIC VARBINARY(MAX) NOT NULL
)
_____________________________________________________________________________
2.Delete all the records from the ProductBrand table in the NarrowFabrics database. Ensure that you do not delete the table.
_____________________________________________________________________________
3.Write an SQL statement to insert a record into the OfficeLocation table, which is created in the PictureLibrary database. The following table shows the data to be inserted into the OfficeLocation table.
GO
INSERT INTO OfficeLocation(Office_ID,
Office_Manager,Office_Location)
VALUES (1001, 'MAX',
GEOGRAPHY::PARSE('POINT(-83.0086 39.95954)'))
_____________________________________________________________________________
4.The production of a bicycle at AdventureWorks involves a number of phases. In each phase, the bicycle is moved to a different work center. The details of all the work centers are stored in the Production.ProductModel table. Bicycles of different types pass through different work centers, depending on the components that need to be fitted. The management wants a list of all the types of bicycles that go through work center 10. How will you generate this list?
XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
ProductModelManuInstructions' AS pd)
SELECT ProductModelID
FROM Production.ProductModel WHERE
Instructions.exist('/pd:root/pd:Location[@LocationID=10]')=1
_____________________________________________________________________________
5.The users of AdventureWorks need to publish the details of all the customers and their address on the organization's website. To perform this task, you need to retrieve the data in the XML format.
AccountNumber, CustomerType, AddressLine1, City,
StateProvinceID, PostalCode
FROM Sales.Customer C JOIN Sales.CustomerAddress CA
ON C.CustomerID = CA.CustomerID
JOIN Person.Address A
ON CA.AddressID = A.AddressID
FOR XML PATH('Customer')
_____________________________________________________________________________
6.The EmployeeDetails table contains the records of the employees. Write a query to delete the records of those employees who are designated as clerk. In addition, ensure that all the deleted rows are displayed after the execution of the query. (Use the AdventureWorks database)
GO
DELETE EmployeeDetails OUTPUT deleted.*
WHERE Designation='Clerk'
_____________________________________________________________________________
7.Delete all the records from the Employee table in the AdventureWorks database where the department ID is 5.
WHERE DepartmentID = 5
_____________________________________________________________________________
8.The management of AdventureWorks requires a list containing the skills of all the candidates who have applied for a vacancy. The details of all the candidates are stored in the XML format in the HumanResources.JobCandidate table. How will you display the list?
Resume.value('declare namespace
ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/ns:Resume/ns:Name/ns:Name.First)[1]','nvarchar(20)') AS [First Name],
Resume.value('declare namespace
ns="http://schemas.microsft.com/sqlserver/2004/07/adventure-works/Resume";
(/ns:Resume/ns:name/ns:Name.Last)[1]','nvarchar(20)') AS [Last Name],
Resume.value('declare namespace
ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/ns:Resume/ns:Skills)[1]','nvarchar(max)') AS [Skills]
FROM HumanResources.JobCandidate
_____________________________________________________________________________
9.The details of the two employees designated as clerk have been removed from the EmployeeDetails table. Write the SQL statement to reflect these changes made in the EmployeeDetails_Backup table. (Use the AdventureWorks database)
USING EmployeeDetails AS SOURCE
ON (TARGET.EmployeeID = SOURCE.EmployeeID)
WHEN MATCHED AND
TARGET.Designation <> SOURCE.Designation
THEN UPDATE SET
TARGET.Designation = SOURCE.Designation
WHEN NOT MATCHED THEN
INSERT VALUES (SOURCE.EmployeeID,
SOURCE.EmpName,SOURCE.Designation,
SOURCE.Salary,SOURCE.DeptNo)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
_____________________________________________________________________________
10.There is a change in the production process of the bicycle with the product model ID 7. Due to this change, the bicycle will not be going to work center 10. You need to update this change in the database. How will you perform this task? (Use the AdventureWorks database)
XMLNAMESPACES
(
'http://schemas.microsoft.com/sqlserver/2004/07
/adventure-works/ProductModelManuInstructions'AS pd
)
UPDATE Production.ProductModel SET
Instructions.modify('delete(/pd:root/pd:Location)[1]')
WHERE ProductModelID = 7
_____________________________________________________________________________
11.Write a query to copy the records of sales with unit price greater than $ 2024.994 in a new table named Sales_od. (Use the AdventureWorks database)
WHERE UnitPrice > 2024.994
_____________________________________________________________________________
12. Insert a record with the following address in the Address table of the AdventureWorks database:
2125 Yan Street, Bothell-79, Postal Code-98011
PostalCode) VALUES('2125 Yan Street','Bothell',79,'98011')
_____________________________________________________________________________
13. You need to create a table named Student_Details to store the details of students in the database. The structure is shown in the following table.
'<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="StudentName" type="string"/>
<element name="Address" type="string"/>
<element name="ContactNumber" type="int"/>
</schema>'
CREATE TABLE Student_details
(
StudentID INT,
StudentInfo XML(StudentInfo)
)
_____________________________________________________________________________
1. The Store table is often queried. The queries are based on the CustomerID attribute and take a
long time to execute. Optimize the execution of the queries. In addition, ensure that the
CustomerID attribute does not contain duplicate values. (Use the AdventureWorks database)
ON Sales.Store(CustomerID)
_____________________________________________________________________________
2. The SalesOrderDetail and SalesOrderHeader tables store the details of the sales orders. You
have created a backup of the SalesOrderDetail and SalesOrderHeader tables in the
SalesOrderDetail_Backup and SalesOrderHeader_Backup tables. To generate a report displaying
the sales order ID and the total amount of all the products purchased against an order, you are
using the following query:
SELECT sd.SalesOrderID, SUM(LineTotal) AS [Total Amount]
FROM Sales.SalesOrderDetail_Backup sd JOIN Sales.SalesOrderHeader_Backup sh
ON sd.SalesOrderID = sh.SalesOrderID
GROUP BY sd.SalesOrderID
The table contains a large amount of data. Suggest a solution to optimize the execution of this
query. (Use the AdventureWorks database)
ON Sales.SalesOrderHeader (SalesOrderID, CustomerID)
_____________________________________________________________________________
3. The SalesOrderDetail table is often queried. The queries are based on the SalesOrderDetailID
and SalesOrderID attributes. The execution of the queries takes a long time. Optimize the
execution of the queries. In addition, check the performance of the query before and after
optimizing the execution of the queries. (Use the AdventureWorks database)
ON Sales.SalesOrderDetail(SalesOrderId, SalesOrderDetailId)
_____________________________________________________________________________
4. A view has been defined as shown in the following statement:
CREATE VIEW vwSalesOrderDetail
AS
SELECT oh.SalesOrderID, TerritoryID, TotalDue, OrderQty, ProductID
FROM Sales.SalesOrderHeader oh JOIN Sales.SalesOrderDetail od
ON oh.SalesOrderID = od.SalesOrderID
The following UPDATE statement gives an error when you update the OrderQty and TerritoryID
attributes:
UPDATE vwSalesOrderDetail
SET OrderQty = 2, TerritoryID = 4
FROM vwSalesOrderDetail
WHERE SalesOrderID = 43659
Identify the problem and provide the solution. (Use the AdventureWorks database)
SET OrderQty = 2
FROM vwSalesOrderDetail
WHERE SalesOrderID = 43659
UPDATE vwSalesOrderDetail
SET TerritoryID = 4
FROM vwSalesOrderDetail
WHERE SalesOrderID = 43659
_____________________________________________________________________________
5. Display the details of all the credit cards that are of type SuperiorCard. The CreditCard table
contains a large amount of data. Therefore, the query takes a long time to retrieve the details of
the credit card. You need to optimize the execution of the query so that the result set does not
take too much time to be retrieved. (Use the AdventureWorks database)
CREATE FULLTEXT INDEX ON Sales.CreditCard(CardType)
KEY INDEX AK_CreditCard_CardNumber
SELECT * FROM Sales.CreditCard
WHERE CONTAINS (CardType, '"SuperiorCard"')
_____________________________________________________________________________
6. The Store table contains the details of all the stores. The HR Manager of AdventureWorks, Inc.
frequently queries the Store table based on the names of the stores. He wants to create the
following reports:
A report containing the details of all the stores that contain the word 'bike' in their names.
A report displaying the names of all the stores containing the phrase 'Bike Store'.
Write the query so that the result set is retrieved quickly. (Use the AdventureWorks database)
CREATE FULLTEXT INDEX ON Sales.Store(Name)
KEY INDEX PK_Store_CustomerID
SELECT * FROM Sales.Store
WHERE FREETEXT (Name, 'Bike')
SELECT * FROM Sales.Store
WHERE CONTAINS (Name, '"Bike Store"')
_____________________________________________________________________________
7. The manager of the production department wants to analyze the products that contain the exact
word 'road' in their description. Write a query so that the result set does not take a long time to
execute. (Use the AdventureWorks database)
WHERE CONTAINS (Description, 'road')
_____________________________________________________________________________
8. Display the details of all the currencies that contain the words New and Dollar in their names.
These words can be included in any order. In addition, you need to make sure that the query does
not take too much time to execute. (Use the AdventureWorks database)
KEY INDEX AK_Currency_Name
SELECT * FROM Sales.Currency
WHERE FreeText (Name, '"New" And "Dollar"')
_____________________________________________________________________________
9. The Store table is often queried. The queries are based on the Demographics attribute and take a
long time to execute. Optimize the execution of these queries. (Use the AdventureWorks
database)
ON Sales.Store (Demographics)
USING XML INDEX PXML_Store_Demographics
FOR PATH
_____________________________________________________________________________
10. You need to create a report displaying the details of all the products that contain the word 'top'
near the word 'line' in their description. Write a query to retrieve the desired output. Write the query
such that it does not take a long time to execute. (Use the AdventureWorks database)
FROM Production.ProductDescription
WHERE CONTAINS (Description, ' "top" near "line"')
_____________________________________________________________________________
11. Display the details of all the stores having the word 'bike' in their name. In addition, ensure that the
report contains the details of those stores that have the sales person ID as 277. You need to write
a query so that the result set does not take a long time to be retrieved. (Use the AdventureWorks
database)
SalesPersonID = 227 AND CONTAINS (Name, 'Bike')
_____________________________________________________________________________
12. The DepartmentHistory table of employees is often queried. The queries are based on the
EmployeeID attribute and take a long time to execute. Optimize the execution of these queries.
(Use the AdventureWorks database)
ON HumanResources.EmployeeDepartmentHistory(EmployeeID)
_____________________________________________________________________________
13. Create a view to retrieve the employee IDs along with the corresponding Group Name. (Use the
AdventureWorks database)
AS
SELECT E.EmployeeId, D.GroupName
FROM HumanResources.Department D
INNER JOIN
HumanResources.EmployeeDepartmentHistory E
ON D.DepartmentID = E.DepartmentID
_____________________________________________________________________________
14. Create a view to retrieve SalesOrderId and Price of the Product along with the corresponding
Product Name. (Use the AdventureWorks database)
AS
SELECT P.Name, S.SalesOrderID, S.UnitPrice
FROM Production.Product P INNER JOIN
Sales.SalesOrderDetail S
ON P.ProductID = S.ProductID
_____________________________________________________________________________
15. Create a view to display SalesOrderId, CustomerId, OrderQty, UnitPrice, SubTotal, and TotalDue
of the orders placed. (Use the AdventureWorks database)
SELECT H.CustomerID, H.SubTotal, H.TotalDue,
FROM Sales.SalesOrderDetail AS H
JOIN Sales.SalesOrderDetail AS D ON
H.SalesOrderID = D.SalesOrderID
1.Create a batch that finds the average pay rate of the employees and then lists the details of the employees who have a pay rate less than the average pay rate. (Use the AdventureWorks database)
SELECT @avg_rate = AVG(rate)
FROM HumanResources.EmployeePayHistory
SELECT * FROM HumanResources.EmployeePayHistory
WHERE Rate < @avg_rate
GO
_____________________________________________________________________________
2.Create a function that returns the shipment date of a particular order. (Use the AdventureWorks database)
RETURNS DATE
AS
BEGIN
DECLARE @ShipDate Datetime
SELECT @ShipDate = ShipDate
FROM Sales.SalesOrderHeader WHERE SalesOrderID = @SalesOrderID
IF (@ShipDate IS NULL)
SET @ShipDate = 0
RETURN @ShipDate
END
SELECT SalesOrderID, Sales.CalShipDate(SalesOrderID)
AS ShippingDate
FROM Sales.SalesOrderHeader
_____________________________________________________________________________
3.Create a stored procedure that accepts the name of a product and display its ID, number, and availability. (Use the AdventureWorks database)
AS
BEGIN
PRINT 'Product details'
SELECT ProductID, ProductNumber, MakeFlag AS Availability
FROM Production.Product
WHERE Name = @name
END
EXECUTE ProductList 'Keyed Washer'
_____________________________________________________________________________
4.Create a function that returns the credit card number for a particular order. (Use the AdventureWorks database)
RETURNS NVARCHAR(25)
AS
BEGIN
DECLARE @ret NVARCHAR(25)
SELECT @ret = CardNumber
FROM Sales.SalesOrderHeader S JOIN Sales.CreditCard C
ON S.CreditCardID = C.CreditCardID
WHERE SalesOrderID = @SalesOrderID
IF (@ret IS NULL)
SET @ret = 0
RETURN @ret
END
_____________________________________________________________________________
5.Create a function that returns a table containing the ID and the name of the customers who are categorized as individual customers (CustomerType = 'I'). The function should take one parameter. The parameter value can be either Shortname or Longname. If the parameter value is Shortname, only the last name of the customer will be retrieved. If the parameter value is Longname, then the full name will be retrieved. (Use the AdventureWorks database)
RETURN @tbl_Individual Table
(CustomerID INT PRIMARY KEY,Name NVARCHAR(100))
AS BEGIN IF (@format = 'LONGNAME')
INSERT @tbl_Individual
SELECT Cu.CustomerID,FirstName+''+LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
ORDER BY LastName,FirstName
ELSE IF (@format = 'SHORTNAME')
INSERT @tbl_Individual
SELECT Cu.CustomerID,LastName FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
ORDER BY LastName
RETURN
END
_____________________________________________________________________________
6.Create a user-defined function that accepts the account number of a customer and returns the customer's name from the Depositor table. Further, ensure that after creating the function, user is not able to alter or drop the Depositor table. (Use the AdventureWorks database)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN (
SELECT Customer_name, Acc_num
FROM dbo.Depositer
WHERE Acc_num = @AccNum
)
SELECT * FROM fx_Disp_AccDet(101)
_____________________________________________________________________________
7.Create a batch to check the availability of the product, Chaining Bolts, in the stock. If the product is available, display a message, 'The Stock is available'. If the product is not available, display a message, 'The Stock is not available'. (Use the AdventureWorks database)
SELECT @MakeFlag = MakeFlag FROM Production.Product
WHERE Name = 'Chaining Bolts'
IF @MakeFlag = 1
PRINT 'The Stock is available'
ELSE
PRINT 'The Stock is not available'
_____________________________________________________________________________
8.Create a stored procedure that accepts two numbers, num1 and num2, and displays the result after dividing these two numbers. In addition, num1 should always be greater than num2. If num1 is less than num2, generate a user-defined error message, 'You have entered your numbers in the wrong way'.
AS
BEGIN
DECLARE @Div INT
IF @Num1 < @Num2
RAISERROR ('You have entered your numbers
in the wrong way' ,16,1)
ELSE
SET @Div=@Num1/@Num2
PRINT @Div
END
_____________________________________________________________________________
9.Create a stored procedure that returns the standard cost of a given product. (Use the AdventureWorks database)
@StandardCost MONEY OUTPUT
AS
BEGIN
IF EXISTS (SELECT * FROM Production.ProductCostHistory
WHERE ProductID = @ProductId)
BEGIN
SELECT @StandardCost = StandardCost
FROM Production.ProductCostHistory
RETURN 0
END
ELSE
RETURN 1
END
_____________________________________________________________________________
1.The management of AdventureWorks Incorporation wants that whenever the pay rate of an employee is modified, its effect on the monthly salary of that employee should be displayed. John, the Database Developer at AdventureWorks, has been asked to resolve this problem. Help John to find an appropriate solution.
ON HumanResources.EmployeePayHistory FOR UPDATE
AS
BEGIN
DECLARE @rate AS MONEY
DECLARE @frq AS INT
SELECT @rate = Rate,
@frq = PayFrequency FROM INSERTED
SELECT @rate * @frq * 30 AS 'Monthly salary'
END
UPDATE HumanResources.EmployeePayHistory
SET Rate = Rate + 5
WHERE EmployeeID = 160
_____________________________________________________________________________
2.The management of AdventureWorks Incorporation has decided that no user should be able to change the prices of products. In addition, the management wants that all the attempts to change the price should be saved in a temporary table, Temp. John, the Database Developer, has been asked to make significant changes in the database to implement this policy. What should John do to achieve the same?
(
ProductID INT,
AttChangeCost MONEY,
AttTime DATETIME
)
CREATE TRIGGER updTrigger ON
[Production].[ProductCostHistory] INSTEAD OF UPDATE
AS
BEGIN
DECLARE @PID AS INT
DECLARE @COST AS MONEY
SELECT @PID = ProductID,
@COST = StandardCost FROM DELETED
INSERT INTO TEMP VALUES (@PID, @COST, GETDATE())
SELECT 'Sorry you can not change the price of a Product'
END
UPDATE [Production].[ProductCostHistory]
SET StandardCost = 55
WHERE ProductID = 707
_____________________________________________________________________________
3.Create a trigger to ensure that the average of the values in the Rate column of the EmployeePayHistory table should not be more than 20 when the value of the rate is increased. (Use the AdventureWorks database)
ON HumanResources.EmployeePayHistory
FOR UPDATE
AS
IF UPDATE (Rate)
BEGIN
DECLARE @AvgRate FLOAT
SELECT @AvgRate = AVG(Rate)
FROM HumanResources.EmployeePayHistory
IF (@AvgRate > 20)
BEGIN
PRINT 'The average value of
rate cannot be more than 20'
ROLLBACK TRANSACTION
END
END
_____________________________________________________________________________
4.Create a trigger on the Product table to ensure that if the value for the SafetyStockLevel column becomes less than or equal to the specified ReorderPoint column for a product because of an update operation, a message informing that the reorder level has been attained should be displayed. (Use the AdventureWorks database)
ON Production.Product
FOR UPDATE
AS
DECLARE @PID VARCHAR
SELECT @PID = ProductID FROM INSERTED
IF ( (SELECT SafetyStockLevel FROM INSERTED)
<= (SELECT ReorderPoint FROM INSERTED))
BEGIN
PRINT ' The stock of ' + @PID +
' is under the reorder point'
END
_____________________________________________________________________________
5.Create a trigger named Emp_Update on the EmployeeDetail table. This trigger should restrict a user from performing any kind of DML operation on the table before 9 A.M and after 5 P.M. (Use the AdventureWorks database)
ON EmployeeDetails AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @Time INT
SET @Time = DATENAME(HH, GETDATE())
IF @Time NOT BETWEEN 9 AND 17
BEGIN
ROLLBACK
PRINT ('The operation cannot be performed
before 9 A.M and after 5 P.M')
END
END
_____________________________________________________________________________
6.The management of AdventureWorks wants that whenever the account number of a user is modified, the old account number, as well as the new account number, should be instantaneously displayed to the user. How can you accomplish the desired task?
ON Purchasing.vendor
FOR UPDATE AS
BEGIN
DELCARE @oldvalue NVARCHAR(15)
DECLARE @newvalue NVARCHAR(15)
SELECT @oldvalue = AccountNumber FROM DELETED
SELECT @newvalue = AccountNumber FROM INSERTED
PRINT 'the old account number is' +@oldvalue
PRINT 'the new account number is' +@newvalue
END
_____________________________________________________________________________
7.Create a trigger named Emp_Update_Trg that will restrict the updation of salary of an employee if the new salary is less than the previous salary in the EmployeeDetails table. (Use the AdventureWorks database)
ON EmployeeDetails AFTER UPDATE
AS
BEGIN
DECLARE @OldSal MONEY
DECLARE @NewSal MONEY
SELECT @OldSal = Salary FROM DELETED
SELECT @NewSal = Salary FROM INSERTED
IF @OldSal > @NewSal
BEGIN
ROLLBACK
PRINT ('New salary cannot be less than the old salary')
END
END
_____________________________________________________________________________
8.Create a trigger named Emp_Delete_Trg on the EmployeeDetails table. This trigger should restrict the deletion of records from the table if the designation of an employee is Manager. (Use the AdventureWorks database)
ON EmployeeDetails
AFTER DELETE
AS
BEGIN
DECLARE @JOB VARCHAR(50)
SELECT @JOB = Designation FROM DELETED
IF @JOB = 'Manager'
BEGIN
ROLLBACK
PRINT ('CANNOT DELETE MANAGER FROM THE TABLE')
END
END
_____________________________________________________________________________
9.Create a trigger named Emp_Nested_Trg on the EmployeeDetails table for an insert operation. This trigger should ensure that a new record being inserted into the EmployeeDetails table has a matching record in the DeptDetails table. Otherwise, the insert operation is rolled back. (Use the AdventureWorks database)
ON EmployeeDetails AFTER INSERT
AS
BEGIN
DECLARE @DptNo INT
SELECT @DptNo = DeptNo FROM INSERTED
IF NOT EXISTS (SELECT * FROM DeptDetails
WHERE DeptNo = @DptNo)
PRINT 'The specified DeptNo does not exist.'
ROLLBACK
END
_____________________________________________________________________________
10.Consider the following statement:
CREATE VIEW Emp_Dept
AS
SELECT E.EmployeeID, E.EmpName, E.Designation, E.Salary, D.DeptNo, D.DeptName
FROM EmployeeDetails E
INNER JOIN DeptDetails D ON E.DeptNo=D.DeptNo
A view named Emp_Dept has been created on the base tables, EmployeeDetails and DeptDetails, by using the preceding statement. John is the Database Developer with AdventureWorks Incorporation. He wants that the users should be able to insert data in the underlying base tables by using the view. He can implement the desired functionality if the insert operation affects a single table. However, the same cannot be done if multiple tables are involved. He wants to be able to insert data in both the tables with the help of a single insert statement. Help John in resolving the problem.
ON Emp_Dept
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO EmployeeDetails(EmployeeID, EmpName,
Designation, Salary, DeptNo)
SELECT EmployeeID, EmpName, Designation, Salary,
DeptNo FROM INSERTED
INSERT INTO DeptDetails(DeptNo, DeptName)
SELECT DeptNo, DeptName FROM INSERTED END
_____________________________________________________________________________
11.John is a Database Developer. He is not able to delete the unwanted records from the base tables by using the view, Emp_Dept. Help John in resolving the problem.
ON Emp_Dept
INSTEAD OF DELETE
AS
BEGIN
DECLARE @DeptNo INT
DECLARE @Count INT
SELECT @DeptNo = DeptNo FROM DELETED
SELECT @Count = COUNT(*) FROM EmployeeDetails
WHERE DeptNo = @DeptNo
DELETE FROM EmployeeDetails WHERE DeptNo = @DeptNo
IF @Count = 1
DELETE FROM DeptDetails WHERE DeptNo = @DeptNo
END
1. You are a database administrator at AdventureWorks, Inc. You have been notified by the users
that the database works very slowly during peak business hours. You have decided to monitor the
T-SQL statements to identify the statements that are taking time to execute. How will you monitor the server
connect to the server dialog box is displayed
click at New Trace
connect to the sql server
the trace properties window is displayed
type the Name of the trace in the Trace name in the text box
select the save to file: check box
clear the existing connection check box
click at run
and then click at YES button
_____________________________________________________________________________
2.You are a database administrator at AdventureWorks, Inc. As a part of regular maintenance, you need to check the following details:
Current sessions
File space usage
Current transactions
Current connections
How will you perform this task?
SELECT * FROM sys.dm_db_file_space_usage
SELECT * FROM sys.dm_tran_current_transaction
SELECT * FROM sys.dm_exec_connections
_____________________________________________________________________________
3.You are a database administrator at AdventureWorks, Inc. The following statements are executed to check the state of I/O operations:
Select * sys.dm_db_backup_tapes
Select * from sys.dm_db_pending_io_requests
Select * from sys.dm_db_cluster_shared_drives
Select * from sys.dm_db_virtual_files_stats
However, the preceding statements generated an error. Identify the error and provide the solution.
SELECT * FROM sys.dm_io_pending_io_requests
SELECT * FROM sys.dm_io_cluster_shared_drives
SELECT * FROM sys.dm_io_virtual_file_stats
(DB_ID (N'AdventureWorks2012'), NULL);
_____________________________________________________________________________
4.You are a database administrator at AdventureWorks, Inc. You want to check the information about the operating system resources that are specific to SQL Server. How will you do this?
SELECT * FROM sys.dm_os_memory_pools
SELECT * FROM sys.dm_os_child_instances
SELECT * FROM sys.dm_os_sys_info
SELECT * FROM sys.dm_os_loaded_modules
SELECT * FROM sys.dm_os_tasks
SELECT * FROM sys.dm_os_memory_clerks
SELECT * FROM sys.dm_os_workers
_____________________________________________________________________________
5.You are a database administrator at AdventureWorks, Inc. You want to get the information regarding the amount of disk activity generated for the Employee table. How will you perform this task?
ANS.
SET STATISTICS IO ON
SELECT * FROM HumanResources.Employee
SET STATISTICS IO OFF
_____________________________________________________________________________
6. You are a database administrator at AdventureWorks, Inc. and want to generate an effective execution plan on the Employee table. For this, create a statistics on the Employee table. In addition, show the list of all the statistics maintained on the Employee table?
ON HumanResources.Employee
(EmployeeID,ContactId,BirthDate,MaritalStatus)
sp_helpstats 'HumanResources.Employee', 'ALL'
_____________________________________________________________________________
7.An employee having contact ID as 2 got married and her last name has been changed to Smith. You have to make the required changes in the Contact table and track these changes by using the CDC method. (Use the AdventureWorks database)
UPDATE Person.Contact
SET Lastname = 'Smith'
WHERE ContactID = 2
SELECT * FROM cdc.Person_Contact_CT
_____________________________________________________________________________
8.An employee having contact ID as 4 has changed his title from (Sr.) to (Mr.). You need to update its record into Contact table and track these changes by using the track changing method. (Use the AdventureWorks database)
ANS.
ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING
UPDATE Person.Contact
SET Title = 'Mr.'
WHERE ContactID = 4
SELECT * FROM CHANGETABLE
(CHANGES Person.Contact, 0) AS CT
_____________________________________________________________________________
sonugiri.blogspot.com
SQL Lab@Home 1
1. Design an ER diagram for the following situation:
An organization has two types of employees, salaried and wage earning. Both the types of
employees have some common properties, such as employee code, employee name, and
employee address. However, the salaried employees have other additional properties: basic,
allowance, and House Rent Allowance (HRA). The wage earning employees have distinct
properties that are daily wage and overtime.
Ans
_____________________________________________________________________________
2. Shopping Spree is a leading departmental store in Shanghai. The store has a number of regular
customers who purchase bulk items. The store also conducts regular feedback sessions to
analyze customer satisfaction levels. Chen, the Customer Analyst of Shopping Spree, has to make
the ER diagram to represent the preceding situation, and then to map the ER diagram to the
corresponding tables. Help Chen to do the same.
Ans
3. Consider the following DoctorDetails table.
In the preceding table, you have to identify primary key, candidate key, and alternate key and give
the reason.
Ans
Primary Key :DoctorIDCandidate Key :DoctorID,ShiftID
Alternate Key : DoctorDutyID
doctorID and shiftID are individually unique in every row.
Therefore the coloumns, doctorID and ShiftID ,are candidate keys for the primary key.
The Shift id coloumn may contain duplicate values as two doctors may be on the same shift.
DoctorID should be chosen as the primary key and shiftid as the alternate key.
_____________________________________________________________________________
4. Tom is working in an organization as a database administrator. Based on the hierarchy, the
organization has multiple departments and each department has multiple employees. The
following ER diagram represents the relationship between the departments and the employees.
In the preceding diagram, you have to identify entities, their attributes, and type of relationship
between the entities.
Ans
Entities: Department and EmployeeAttributes of department : DepID,DepName and DepId
Attributes of employee : EmpID, EmpName ,and DepID
Type of Relationship : Many to many
_____________________________________________________________________________
5. You have been hired by a regional hospital to create a database that will be used to store the
information of the patients. Implementing the database will make the data easily accessible. The
following ER diagram represents the relationship between the patient and the doctor.
In the preceding diagram, you have to identify the entities, their attributes, and the type of
relationship between the entities.
Ans
Entities:Patient and DoctorAttributes for Patient : patient name , date admitted , patient id
Attributes for doctor : Doc_ID , Specialization , Doc_Name
Relationship-Many to many
_____________________________________________________________________________
6. Consider the following scenario:
An author can write multiple books and a book can be written by more than one author. Identify
the relationship between the author entity and the book entity and represent it with an ER
Diagram
Ans
_____________________________________________________________________________
7. Lee Wong is the newly appointed database administrator at Standard bank. The management of
this bank wants to computerize the process of banking. According to the bank's policy, one
customer can have many accounts but one account cannot be shared by many customers. The
following ER diagram represents the relationship between a customer and the accounts owned by
a particular customer.
In the preceding diagram, you have to identify entities, their attributes, and type of relationship
between the entities.
Ans
Entities: Customer , AccountAttributes for Customer : CUSTOMER NAME , SSN NUMBER , CUSTOMER ADDRESS
Attributes for Account : ACCOUNT NUMBER , BALANCE
Relationship : Many to One
_____________________________________________________________________________
8. Consider the following scenario:
In a college, there are multiple departments. Each department has various faculty members. Only
one of these faculty members holds the position of the Head of the Department. Identify the
relationship between the faculty and the department and represent it with an ER Diagram.
Ans
_____________________________________________________________________________
SQL Lab@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,CardType,CardNumber,ExpYear from [Sales].[CreditCard]
_____________________________________________________________________________
3. Display the customer ID and the account number of all the customers who live in the TerritoryID
(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]
select UnitPrice from [Sales].[SalesOrderDetail] where UnitPrice>2000
_____________________________________________________________________________
5. Display the sales order details of the product named 'Cable Lock' for which the ProductId is 843.
(Use the AdventureWorks database)
select * from [Sales].[SalesOrderDetail]
select SalesOrderDetailID 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].[SalesOrderDetail]
select ModifiedDate from [Sales].[SalesOrderDetail] where ModifiedDate=2004-06-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,SalesLastYear 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]
select SalesOrderID,SalesOrderDetailID,UnitPrice from [Sales].[SalesOrderDetail] where UnitPrice>2000 AND UnitPrice<2100
_____________________________________________________________________________
9. Display the sales territory details of Canada, France, and Germany. (Use the AdventureWorks
database)
select * from [Sales].[SalesTerritory]
select SalesYTD from [Sales].[SalesTerritory] where CountryRegionCode='CA' or CountryRegionCode='DE' or CountryRegionCode='FR'
_____________________________________________________________________________
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]
select SalesOrderID from [Sales].[SalesOrderHeader] where TaxAmt>=10000
_____________________________________________________________________________
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].[SalesPerson]
select SalesPersonID,TerritoryID from [Sales].[SalesPerson] 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]
select CardType,ExpMonth,ExpYear 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 table
select OrderDetails from Table Where receipt_date = '2004-07-12'
_____________________________________________________________________________
14. select * from table
select OrderDetails from Table Where receipt_date = '2004-07-12'
select * from Table
select OrderPlaced,TotalCost Where OrderPlaced='2001-07-01' AND TotalCost>=10000
_____________________________________________________________________________
15. Display the details of the orders that have been placed by customers online. (Use the
AdventureWorks database)
select * from Table
select Order where Order='online'
_____________________________________________________________________________
16. Display a report of all the orders in the following format. (Use the AdventureWorks database)
select * from [dbo].[FactInternetSales]
select SalesOrderNumber,OrderQuantity,UnitPrice,TotalProductCost from [dbo].[FactInternetSales]
_____________________________________________________________________________
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 [dbo].[FactInternetSales]
select OrderDateKey,TaxAmt from [dbo].[FactInternetSales] where UnitPrice<2000 ORDER BY 'OrderDateKey' 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 [dbo].[FactInternetSales]
select ProductKey,DueDateKey,SalesAmount from [dbo].[FactInternetSales] ORDER BY 'SalesAmount' DESC
_____________________________________________________________________________
19. Display the details of all the currencies that have the word 'Dollar' in their name. (Use the
AdventureWorks database)
select * from [dbo].[DimCurrency] where CurrencyName 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[dbo].[FactInternetSales]
select OrderdateKey,TotalProductCost from [dbo].[FactInternetSales] ORDER BY TotalProductCost ASC
_____________________________________________________________________________
SQL Lab@Home 3
1. Display all territories whose names begin with 'N'. (Use the AdventureWorks database)
select * from [Sales].[SalesTerritory]
select Name from [Sales].[SalesTerritory] where Name like 'N%'
_____________________________________________________________________________
2. Display the details of those stores that have Bike in their name. (Use the AdventureWorks
database)
select * from [Sales].[Store]
select Name from [Sales].[Store] where Name like '%bike%'
_____________________________________________________________________________
3. 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 BusinessEntityID,TerritoryID,SalesQuota from [Sales].[SalesPerson] where SalesQuota IS NOT NULL
_____________________________________________________________________________
4. Display the top three sales persons based on the bonus. (Use the AdventureWorks database)
select * from [Sales].[SalesPerson]
select Top 3 * from [Sales].[SalesPerson]
_____________________________________________________________________________
5. Display the different types of credit cards used for purchasing products. (Use the AdventureWorks
database)
select * From [Sales].[CreditCard]
select Distinct CardType from [Sales].[CreditCard]
_____________________________________________________________________________
6. 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 * from [HumanResources].[Employee]
select top(10-5) BusinessEntityID,LoginID,JobTitle from [HumanResources].[Employee]
_____________________________________________________________________________
7. 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.
Though the table structures are large, it is necessary to improve the performance of this query by
modifying the table structures. Identify how to increase the performance of queries.
_____________________________________________________________________________
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.
_____________________________________________________________________________
9. Consider the following Product table.
The preceding table is not normalized. How can this table be converted into the first normal form?
Ans
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?
Ans
_____________________________________________________________________________
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?
Ans
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)
_____________________________________________________________________________
SQL Lab@Home 5
1. Write a query to display the sales person ID of all the sales persons and the name of the territory
to which they belong.
SELECT SalesPersonID,Name FROM Sales.SalesPerson
JOIN Sales.SalesTerritory
ON SalesPerson.TerritoryID=SalesTerritory.TerritoryID
_____________________________________________________________________________
2. Write a query to display the sales person ID, territory ID, and territory name of all the sales
persons in the following format
SELECT 'Person ID'=SalesPersonID,
'Territory ID'=s.TerritoryID,Name FROM Sales.SalesPerson s
JOIN Sales.SalesTerritory st
on s.Territory ID=st.Territory ID
_____________________________________________________________________________
3. Write a query to display the sales order ID, the product ID, and the order date for all the products
in the following format.
SELECT 'Order ID'=h.SalesOrderID,'Product ID'=d.ProductID,'Order Date'=OrderDate FROM Sales.SalesOrderHeader h
join Sales.SalesOrderDetail d
ON h.SalesOrderID=d.SalesOrderID
_____________________________________________________________________________
4. Write a query to display the sales order ID, territory name, month, and year of all the sales orders
in the following format.
SELECT SalesOrderID,'Territory Name'=Name,
Month=Datename(mm,OrderDate),
Year=Datename(yy,OrderDate)
FROM Slaes.SalesOrderHeader s
JOIN Sales.SalesTerritory t
ON s.TerritoryID=t.TerritoryID
_____________________________________________________________________________
5. Write a query to display the order number, territory name, order date, and the quarter in which
each order was placed in the following format.
SELECT SalesOrderID,'Territory Name'=Name,
OrderDate,Quarter=datepart(qq,OrderDate)
FROM Sales.SalesOrderHeader s
JOIN Sales.SalesTerritory t
ON s.TerritoryID=t.TerritoryID
_____________________________________________________________________________
6.Write a query to display the total amount due for all the sales orders rounded off to a whole
number. In addition, display the sales order ID and the type of credit card through which the
payment was made.
SELECT SalesOrderID,CardType,'Total Due'=Round(TotalDue,0) FROM Sales.SalesOrderHeader s
JOIN Sales.CreditCard c
ON s.CreditCardID=c.CreditCardID
_____________________________________________________________________________
7.Write a query to display all the country region codes along with their corresponding territory IDs.
SELECT c.CountryRegionCode,TerritoryID
FROM Sales.SalesTerritory s
RIGHT OUTER JOIN Sales.CountryRegionCurrency c
ON s.CountryRegionCode=c.CountryRegionCode
_____________________________________________________________________________
8.Write a query to display the total amount due for all the orders in the AdventureWorks database in
the following format:
SELECT 'Order VAlue'='The Total Amount Due For The Sales Order ID: '+ Convert(Varchar(10),SalesOrderID)+'is$'+Convert(Varchar(10),TotalDue) FROM Sales.SalesOrderHeader
_____________________________________________________________________________
9.Write a query to display the order date along with the sales order ID and the territory name. The
order date should be displayed in the dd/mm/yyyy format.
SELECT SalesOrderID,Name,Convert(Char(10),OrderDate,103 ) as 'Order Date' FROM Sales.SalesOrderHeader s
JOIN Sales.SalesTerritory t
ON t.TerritoryID=s.TerritoryID
_____________________________________________________________________________
10. Write a query to display the sales order ID and the territory name of the orders where the month of
the order is May and the year is 2004.
Select SalesOrderID,Name FROM Sales.SalesOrderHeader s
JOIN Sales.SalesTerritory t
ON s.TerritoryID=t.TerritoryID
WHERE DATENAME(mm,OrderDate)='May' AND DATEPART(yy,OrderDate)=2004
_____________________________________________________________________________
11.Write a query to display the contact ID of the customers that have the 'Vista' credit card.
Select ContactID,CardType FROM Sales.ContactCreditCard
JOIN Sales.CreditCard
ON ContactCreditCard.CreditCardID=CreditCard.CreditCardID
WHERE CardType='Vista'
_____________________________________________________________________________
12.Create a report containing the sales order ID of those orders where the total value is greater than
the average of the total value of all the orders.
Select SalesOrderID,TotalDue FROM Sales.SalesOrderHeader WHERE TotalDue>(Select Avg(TotalDue)FROM SalesOrderHeader)
_____________________________________________________________________________
13.Write a query to display the sales order IDs of the orders received from the Northeast territory.
(Use the AdventureWorks database)
Select SalesOrderID,FROM Sales.SalesOrderHeader WHERE TerritoryID=(Select TerritoryID FROM Sales.SalesTerritory WHERE Name='NorthEast')
_____________________________________________________________________________
14.Write a query to display the sales order IDs of the orders that have been paid through a
SuperiorCard. (Use the AdventureWorks database)
Select SalesOrderID,FROM Sales.SalesOrderHeader WHERE CreditCardID in (Select CreditCardID FROM Sales.CreditCard WHERE CardType='SuperiorCard')
_____________________________________________________________________________
15. Write a query to display the sales order ID, the order detail ID, and the total value of those orders
where the total value is greater than the maximum of the total value of order ID 43662. (Use the
AdventureWorks database)
Select SalesOrderID,SalesOrderDetailID,LineTotal FROM Sales.SalesOrderDetail WHERE LineTotal> ALL(SELECT LineTotal FROM Sales.SalesOrderDetail WHERE SalesOrderID=43662)
_____________________________________________________________________________
16.Write a query to display the details of those orders for which no discount was offered. (Use the
AdventureWorks database)
Select *FROM Sales.SalesOrderDetail WHERE SpecialOfferID=(Select SpecialOfferID FROM SalesSpecialOffer WHERE Type='No Discount')
_____________________________________________________________________________
17.Write a query to display the sales order IDs and the credit card IDs of those cards which are
expiring in the year 2007. (Use the AdventureWorks database)
Select SalesOrderID,CreditCardID FROM Sales.SalesOrderHeader WHERE CreditCardID in (Select CreditCardID FROM Sales.CreditCard WHERE ExpYear=2007)
_____________________________________________________________________________
18. Write a query to display the average rate of the Australian Dollar, where the CurrencyRateDate is
July 1, 2004. (Use the AdventureWorks database)
Select AverageRate FROM Sales.CurrencyRate WHERE CurrencyRateDate='2004-07-01' AND ToCurrencyCode=(Select CurrencyCode FROM Sales.Currency WHERE Name='Australian Dollar')
_____________________________________________________________________________
SQL Lab@Home 6
1. Write a query to display EmployeeID of those employees who belong to the department, Tool
Design. (Use the AdventureWorks database)
select BusinessEntityID as 'EmployeeID',JobTiTle from [HumanResources].[Employee] where JobTitle='Tool Designer'
_____________________________________________________________________________
2. Write a query to display the credit card number of Catherine Abel. (Use the AdventureWorks
database)
Select CardNumber FROM Sales.CreditCard WHERE CreditCardID=(Select CreditCardID FROM Sales.ContactCreditCard WHERE ContactID=(Select ContactID FROM Person.Contact WHERE
FirstName='Catherine' AND LastName='Abel'))
_____________________________________________________________________________
3. Write a query to display the first name and the last name of the customers along with their credit
card identification number. (Use the AdventureWorks database)
Select FirstName,LastName,B.CreditCardID
FROM Person.Contact A CROSS APPLY (Select * FROM Sales.ContactCreditCard B WHERE B.ContactID=A.ContactID)B
_____________________________________________________________________________
4. Write a query to display the sales order IDs and the order detail IDs along with the total value of
those orders that have a total value greater than the average of the total value for the order ID.
(Use the AdventureWorks database)
Select SalesOrderDetailID,SalesOrderID,LineTotal
FROM Sales.SalesOrderDetail s1
WHERE s1.LineTotal>(Select AVG(s2.LineTotal)
FROM Sales.SalesOrderDetail s2 WHERE s1.SalesOrderID=s2.SalesOrderID)
_____________________________________________________________________________
5. Write a query to display the sales person ID and the total number of sales orders processed by
each sales person. In addition, display the commision earned by each sales person. Commision is
given to each sales person at the rate of $ 10 per order. Exclude the orders where sales person ID
is null. The details of the sales person who has got the maximum commision should be displayed
first in the result set. (Use the AdventureWorks database)
WITH Sales_CTE (SalesPersonID,TotalSalesOrder) AS
(
Select SalesPersonID, COUNT(SalesOrderID)
FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID
)
Select SalesPersonID,TotalSalesOrder,
Commission=TotalSalesOrder*10
FROM Sales_CTE Order By TotalSalesOrder desc
_____________________________________________________________________________
6. Write a query to display CreditCardID, CardNumber, and ExpYear of the credit cards that do not
expire in the year 2005 and 2007. (Use the AdventureWorks database)
select CreditCardID,CardType,ExpYear from [Sales].[CreditCard] where ExpYear!=2005 and ExpYear!=2007
_____________________________________________________________________________
7. Create a table named Recipient in the NarrowFabrics database to store the details of the
recipients to whom the orders are dispatched. The following table provides the structure of the
Recipient table.
CREATE TABLE Recipient(
OrderNumber char(6),
FirstName varchar(20),
LastName varchar(20),
Address varchar(50),
City char(15),
State char(15),
CountryCode char(3),
ZipCode char(10),
Phone char(15))
NOTE: SQLSERVER---DATABASE--ADVENTURE WORKS--RIGHT CLICK ON TABLE AND CLICK AT NEW TABLE
_____________________________________________________________________________
8. NarrowFabrics, Inc. is a leading cloth manufacturing organization that manufactures and delivers
clothes to the distributors. Being a database developer at NarrowFabrics, you need to create a
database that will store the various database objects, such as tables, views, and indexes, used by
the organization. Write the SQL statements that you can use to create a database named
NarrowFabrics.
CREATE DATABASE NarrowFabrics
note: right click on the sql database. new database .
_____________________________________________________________________________
9. LiveChain, Inc. is a leading media company that organizes events and provides photography
services. After each event, the company stores the pictures taken in the event and delivers the
pictures to the client on request. As a database developer, you have been assigned the task to
create a database named PictureLibrary that the company can use to store the pictures.
CREATE DATABASE PictureLibrary
ON
PRIMARY ( NAME=PictureLibrary,
FILENAME='C:\DATA\PictureLibrary.mdf'),
FILEGROUP FileStreamGroup
CONTAINS FILESTREAM(NAME=VideoLibrary_Data,
FILENAME='C:\DATA\PictureLibrary_Data')
LOG ON (NAME=Log1,
FILENAME='C:\DATA\PictureLibrary.ldf')
_____________________________________________________________________________
10. Create a table named Country in the NarrowFabrics database to store the country details. The
following table provides the structure of the Country table.
OrderNumber char(6),
FirstName varchar(20),
LastName varchar(20),
Address varchar(50),
City char(15),
State char(15),
CountryCode char(3),
ZipCode char(10),
Phone char(15))
NOTE: SQLSERVER---DATABASE--ADVENTURE WORKS--RIGHT CLICK ON TABLE AND CLICK AT NEW TABLE
_____________________________________________________________________________
8. NarrowFabrics, Inc. is a leading cloth manufacturing organization that manufactures and delivers
clothes to the distributors. Being a database developer at NarrowFabrics, you need to create a
database that will store the various database objects, such as tables, views, and indexes, used by
the organization. Write the SQL statements that you can use to create a database named
NarrowFabrics.
CREATE DATABASE NarrowFabrics
note: right click on the sql database. new database .
_____________________________________________________________________________
9. LiveChain, Inc. is a leading media company that organizes events and provides photography
services. After each event, the company stores the pictures taken in the event and delivers the
pictures to the client on request. As a database developer, you have been assigned the task to
create a database named PictureLibrary that the company can use to store the pictures.
CREATE DATABASE PictureLibrary
ON
PRIMARY ( NAME=PictureLibrary,
FILENAME='C:\DATA\PictureLibrary.mdf'),
FILEGROUP FileStreamGroup
CONTAINS FILESTREAM(NAME=VideoLibrary_Data,
FILENAME='C:\DATA\PictureLibrary_Data')
LOG ON (NAME=Log1,
FILENAME='C:\DATA\PictureLibrary.ldf')
_____________________________________________________________________________
10. Create a table named Country in the NarrowFabrics database to store the country details. The
following table provides the structure of the Country table.
CREATE TABLE Country(
CountryID varchar(2),
Country char(25),
)
_____________________________________________________________________________
11. John is working with Client Network Ltd as the Database Administrator. He wants to create a
database to store the details of all the employees working in the organization. He needs to ensure
that while saving, the data is placed in multiple file groups so that he is able to effectively manage
the backup and restore operations. What should John do to perform this task?
CREATE DATABASE EmpDetail
ON PRIMARY
( NAME='EmpDetail_Primary',
FILENAME='D:\Data\EmpDetail_Prm.mdf',
SIZE=5MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
FILEGROUP EmpDetail_FG
( NAME='EmpDetail_Dat1',
FILENAME='D:\Data\EmpDetail_1.mdf',
SIZE=5MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
LOG ON
( NAME='EmpDetail_log',
FILENAME='D:\Data\EmpDetail.ldf',
SIZE=5MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
GO
_____________________________________________________________________________
12. You need to store the details of the offices in the central database, PictureLibrary. Write the SQL
statement to create the OfficeLocation table in the PictureLibrary database. The following table
shows the structure of the OfficeLocation table.
CountryID varchar(2),
Country char(25),
)
_____________________________________________________________________________
11. John is working with Client Network Ltd as the Database Administrator. He wants to create a
database to store the details of all the employees working in the organization. He needs to ensure
that while saving, the data is placed in multiple file groups so that he is able to effectively manage
the backup and restore operations. What should John do to perform this task?
CREATE DATABASE EmpDetail
ON PRIMARY
( NAME='EmpDetail_Primary',
FILENAME='D:\Data\EmpDetail_Prm.mdf',
SIZE=5MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
FILEGROUP EmpDetail_FG
( NAME='EmpDetail_Dat1',
FILENAME='D:\Data\EmpDetail_1.mdf',
SIZE=5MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
LOG ON
( NAME='EmpDetail_log',
FILENAME='D:\Data\EmpDetail.ldf',
SIZE=5MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
GO
_____________________________________________________________________________
12. You need to store the details of the offices in the central database, PictureLibrary. Write the SQL
statement to create the OfficeLocation table in the PictureLibrary database. The following table
shows the structure of the OfficeLocation table.
USE PictureLibrary
CREATE TABLE OfficeLocation
(
Office_ID int NOT NULL,
Office_Manager varchar(30) NOT NULL,
Office_Location geography NOT NULL
)
_____________________________________________________________________________
13. Write a query to make EmpDetail_FG as the default filegroup for the EmpDetail database.
ALTER DATABASE EmpDetail
MODIFY FILEGROUP EmpDetail_FG DEFAULT
GO
_____________________________________________________________________________
SQL Lab@Home 7
1.Display SalesOrderID, OrderQty, and UnitPrice from the SalesOrderDetail table where a similar unit price needs to be marked with an identical value. (Use the AdventureWorks database)
Ans
SELECT SalesOrderID, OrderQty, UnitPrice,DENSE_RANK() OVER (ORDER BY UnitPrice ASC) AS
RANK FROM Sales.SalesOrderDetail
_____________________________________________________________________________
2.Write a query to retrieve the details of the product locations where cost rate is greater than 12. In addition, the locations need to be grouped into three groups, and then ranked based on the cost rate in descending order. (Use the AdventureWorks database)
Ans
SELECT LocationID, Name, CostRate, Availability, NTILE(3) OVER (ORDER BY CostRate DESC) AS RANKFROM Production.LocationWHERE CostRate >= 12_____________________________________________________________________________
3.Consider the following table structures.
Refer the preceding table structures for the following requirements:
1. Create the Category table in the NarrowFabrics database. Enforce the following data integrity rules while creating the table:
--The CategoryId should be the primary key.
--The Category attribute should be unique but not the primary key.
--The Description attribute can allow storage of NULL values.
2. Create the ProductBrand table in the NarrowFabrics database. Enforce the following data integrity rules while creating the table:
--The BrandId should be the primary key.
--The BrandName should be unique but not the primary key.
3. Create the NewProduct table with the following data integrity rules in the NarrowFabrics database:
--The ProductId should be the primary key.
--The Qoh of the product should be between 0 and 200.
--The Photo and ProductImgPath attributes can allow storage of NULL values.
--The ProductName and ProductDescription attributes should not allow NULL values.
--The values of the CategoryId attribute should be present in the Category table.
4. Modify the NewProduct table to enforce the following data integrity rule in the NarrowFabrics database:
--The values entered in the BrandId attribute should be present in the ProductBrand table.
Ans
1.CREATE TABLE Category
(
CategoryID CHAR(3) CONSTRAINT pkCategoryID PRIMARY KEY,
Category CHAR(20) CONSTRAINT unqCategory UNIQUE,
Description VARCHAR(100) NULL
)
2.
CREATE TABLE ProductBrand
(
BrandID CHAR(3) CONSTRAINT pkBrandID PRIMARY KEY,
BrandName CHAR(20) CONSTRAINT unqBrandName UNIQUE
)
3.
CREATE TABLE NewProduct
(
ProductID CHAR(6) CONSTRAINT pkProductID PRIMARY KEY,
ProductName VARCHAR(20) NOT NULL,
ProductDescription VARCHAR(250) NOT NULL,
CategoryID CHAR(3) CONSTRAINT fkCategoryID
FOREIGN KEY REFERENCES Category(CategoryID),
ProductRate MONEY,
BrandID CHAR(3),
Photo IMAGE NULL,
Qoh SMALLINT CONSTRAINT chkqoh CHECK (QOH BETWEEN 0 AND 200),
ProductImgPath VARCHAR(50) NULL
)
4.
ALTER TABLE NewProduct
ADD CONSTRAINT fkBrandID
FOREIGN KEY (BrandID)
REFERENCES ProductBrand(BrandID)
_____________________________________________________________________________
4.Delete the Recipient table from the NarrowFabrics database.
Ans:
USE NarrowFabricsDROP TABLE Recipient
_____________________________________________________________________________
5.The Recipient table and the Country table in the NarrowFabrics database do not have the same data type for the CountryId attribute. The following tables show the sample structure of the two tables.
Alter the Recipient or Country table so that they have the same data type for the CountryId attribute.
ANS:
USE NarrowFabrics
ALTER TABLE Country
ALTER COLUMN CountryID CHAR(2)
_____________________________________________________________________________
6.Write a query to return the current year price and the previous year price of a product with the Product ID, 715, over subsequent years. (Use the AdventureWorks database)
ANS:
SELECT ProductID, YEAR(StartDate)
AS SalesYear, ListPrice AS CurrentPrice,
LAG(ListPrice, 1,0) OVER (ORDER BY YEAR(Stardate))
AS PreviousPrice
FROM Production.ProductListPriceHistory
WHERE ProductID = 715
_____________________________________________________________________________
7.The following statement was used to remove the Category table: DELETE TABLE Category
The preceding statement displays an error and aborts. Identify the error and rectify it. (Use the NarrowFabrics database)
ANS:
USE NarrowFabrics
GO
DROP TABLE NewProduct
DROP TABLE Category
_____________________________________________________________________________
8.Create a table named LeaveDetail in the AdventureWorks database. The following table shows the structure of the LeaveDetail table.
Ans
USE AdventureWorksCREATE TABLE LeaveDetail
(
Name VARCHAR(50) NOT NULL,
Reason VARCHAR(100) NOT NULL,
Number_of_days INT CONSTRAINT chknoday
CHECK (Number_of_days < 3)
)
_____________________________________________________________________________
9.Write the SQL statement to create a table named PictureDetails in the PictureLibrary database. The following table shows the structure of the PictureDetails table.
Ans
CREATE TABLE PictureDetails(
EventPicID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
EventName VARCHAR(30) NOT NULL,
PIC VARBINARY(MAX) NOT NULL
)
_____________________________________________________________________________
10.The check constraint is applied on the Number_of_days column of the LeaveDetail table. This check constraint ensures that the value inserted for the Number_of_days column is less than three. However, the leave of four days has been approved for John based on some valid reasons. The HR Manager wants to update the leave details for John but fails to assign the value, 4, in the Number_of_days column. As a database developer, what will you do to perform the preceding task? (Use the AdventureWorks database)
Ans
USE AdventureWorksALTER TABLE LeaveDetail
NOCHECK CONSTRAINT chknoday
INSERT INTO LeaveDetail
VALUES ('Tom','Fever'4)
ALTER TABLE LeaveDetail
CHECK CONSTRAINT chknoday
_____________________________________________________________________________
11.Write a query to display the structure of the Department table. (Use the AdventureWorks database)
Ans
sp_help 'humanResources.department'_____________________________________________________________________________
12.Write a query to display the details of all the salespersons by using synonyms. (Use the AdventureWorks database)
Ans
USE AdventureWorksCREATE SYNONYM SpersonDetail
FOR Adventureworks.Sales.Salesperson
/*Replace the existing query with the new query*/
SELECT * FROM SpersonDetail
_____________________________________________________________________________
13.Write a query to create a rule on the GroupName column of the Department table to accept only three values: Research and Development, Sales and Marketing, and Manufacturing. (Use the AdventureWorks database)
Ans
USE AdventureWorksGO
CREATE RULE groupType
AS @gType IN ('Research and Development',
'Sales and Marketing', 'Manufacturing')
GO
sp_bindrule 'groupType', 'HumanResources.Department.GroupName'
_____________________________________________________________________________
14.Write a statement to create a user-defined data type named quality. This data type should be varchar(100) and should not contain NULL values.
Ans
CREATE TYPE QualityFROM VARCHAR(100) NOT NULL;
GO
_____________________________________________________________________________
15.Insert the data shown in the following table into the ProductBrand table of the NarrowFabrics database.
Ans
INSERT INTO ProductBrand VALUES ('B01', 'Lee')INSERT INTO ProductBrand VALUES ('B02', 'Nike')
INSERT INTO ProductBrand VALUES ('B03', 'Reebok')
_____________________________________________________________________________
16.AdventureWorks, Inc. has set up a new store. Insert the data into the database as shown in the following table.
Ans
INSERT INTO Sales.Customer VALUES (7,'S',DEFAULT, DEFAULT)/*EXECUTE ABOVE LINE BY PRESSING F5. REPLACE THE EXISTING QUERY WITH THE NEW QUERY*/
INSERT INTO Sales.Store VALUES(29484, 'Car store', 285,
'<StoreSurvey XMLns="http://schemas.microsoft.com/sqlserver/
2004/07/adventure-works/StoreSurvey">
<AnnualSales>35000</AnnualSales>
<AnnualRevenue>35000</AnnualRevenue>
<BankName>InternationalBank</BankName>
<BusinessType>BM</BusinessType>
<YearOpened>1980</YearOpened>
<specialty>Road</specialty>
<SquareFeet>7500</SquareFeet>
<Brands>AW</Brands>
<Internet>T1</Internet>
<NumberEmployees>7</NumberEmployees>
</StoreSurvey>', DEFAULT, DEFAULT)
_____________________________________________________________________________
17.The address of a vendor, Comfort Road Bicycles, has changed. You need to update the data, as shown in the following table, in the AdventureWorks database.
Ans
UPDATE Purchasing.VendorAddressSET AddressID =
(SELECT AddressID FROM Person.Address WHERE
AddressLine1 = '4151 Olivera'
AND City = 'Atlanta')
FROM Purchasing.VendorAddress VA, Purchasing.Vendor V
WHERE VA.VendorID = V.VendorID
AND V.Name = 'Comfort Road Bicycles'
_____________________________________________________________________________
SQL Lab@Home 8
1.Write an SQL statement to insert a record into the PictureDetails table, which is created in the PictureLibrary database. The following table shows the data to be inserted into the PictureDetails table.
Ans
CREATE TABLE PictureDetails(
EventPicID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
EventName VARCHAR(30) NOT NULL,
PIC VARBINARY(MAX) NOT NULL
)
_____________________________________________________________________________
2.Delete all the records from the ProductBrand table in the NarrowFabrics database. Ensure that you do not delete the table.
Ans
TRUNCATE TABLE ProductBranda_____________________________________________________________________________
3.Write an SQL statement to insert a record into the OfficeLocation table, which is created in the PictureLibrary database. The following table shows the data to be inserted into the OfficeLocation table.
Ans
USE PictureLibraryGO
INSERT INTO OfficeLocation(Office_ID,
Office_Manager,Office_Location)
VALUES (1001, 'MAX',
GEOGRAPHY::PARSE('POINT(-83.0086 39.95954)'))
_____________________________________________________________________________
4.The production of a bicycle at AdventureWorks involves a number of phases. In each phase, the bicycle is moved to a different work center. The details of all the work centers are stored in the Production.ProductModel table. Bicycles of different types pass through different work centers, depending on the components that need to be fitted. The management wants a list of all the types of bicycles that go through work center 10. How will you generate this list?
Ans
WITHXMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/
ProductModelManuInstructions' AS pd)
SELECT ProductModelID
FROM Production.ProductModel WHERE
Instructions.exist('/pd:root/pd:Location[@LocationID=10]')=1
_____________________________________________________________________________
5.The users of AdventureWorks need to publish the details of all the customers and their address on the organization's website. To perform this task, you need to retrieve the data in the XML format.
Ans.
SELECT C.CustomerID, TerritoryID,AccountNumber, CustomerType, AddressLine1, City,
StateProvinceID, PostalCode
FROM Sales.Customer C JOIN Sales.CustomerAddress CA
ON C.CustomerID = CA.CustomerID
JOIN Person.Address A
ON CA.AddressID = A.AddressID
FOR XML PATH('Customer')
_____________________________________________________________________________
6.The EmployeeDetails table contains the records of the employees. Write a query to delete the records of those employees who are designated as clerk. In addition, ensure that all the deleted rows are displayed after the execution of the query. (Use the AdventureWorks database)
Ans
USE AdventureWorksGO
DELETE EmployeeDetails OUTPUT deleted.*
WHERE Designation='Clerk'
_____________________________________________________________________________
7.Delete all the records from the Employee table in the AdventureWorks database where the department ID is 5.
Ans
DELETE FROM HumanResources.EmployeeDepartmentHistoryWHERE DepartmentID = 5
_____________________________________________________________________________
8.The management of AdventureWorks requires a list containing the skills of all the candidates who have applied for a vacancy. The details of all the candidates are stored in the XML format in the HumanResources.JobCandidate table. How will you display the list?
Ans
SELECT JobCandidateID,Resume.value('declare namespace
ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/ns:Resume/ns:Name/ns:Name.First)[1]','nvarchar(20)') AS [First Name],
Resume.value('declare namespace
ns="http://schemas.microsft.com/sqlserver/2004/07/adventure-works/Resume";
(/ns:Resume/ns:name/ns:Name.Last)[1]','nvarchar(20)') AS [Last Name],
Resume.value('declare namespace
ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/ns:Resume/ns:Skills)[1]','nvarchar(max)') AS [Skills]
FROM HumanResources.JobCandidate
_____________________________________________________________________________
9.The details of the two employees designated as clerk have been removed from the EmployeeDetails table. Write the SQL statement to reflect these changes made in the EmployeeDetails_Backup table. (Use the AdventureWorks database)
ANS
MERGE EmployeeDetails_Backup AS TARGETUSING EmployeeDetails AS SOURCE
ON (TARGET.EmployeeID = SOURCE.EmployeeID)
WHEN MATCHED AND
TARGET.Designation <> SOURCE.Designation
THEN UPDATE SET
TARGET.Designation = SOURCE.Designation
WHEN NOT MATCHED THEN
INSERT VALUES (SOURCE.EmployeeID,
SOURCE.EmpName,SOURCE.Designation,
SOURCE.Salary,SOURCE.DeptNo)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
_____________________________________________________________________________
10.There is a change in the production process of the bicycle with the product model ID 7. Due to this change, the bicycle will not be going to work center 10. You need to update this change in the database. How will you perform this task? (Use the AdventureWorks database)
Ans
WITHXMLNAMESPACES
(
'http://schemas.microsoft.com/sqlserver/2004/07
/adventure-works/ProductModelManuInstructions'AS pd
)
UPDATE Production.ProductModel SET
Instructions.modify('delete(/pd:root/pd:Location)[1]')
WHERE ProductModelID = 7
_____________________________________________________________________________
11.Write a query to copy the records of sales with unit price greater than $ 2024.994 in a new table named Sales_od. (Use the AdventureWorks database)
Ans
SELECT * INTO Sales_od FROM Sales.SalesOrderDetailWHERE UnitPrice > 2024.994
_____________________________________________________________________________
12. Insert a record with the following address in the Address table of the AdventureWorks database:
2125 Yan Street, Bothell-79, Postal Code-98011
Ans
INSERT Person.Address(AddressLine1,City,StateProvinceID,PostalCode) VALUES('2125 Yan Street','Bothell',79,'98011')
_____________________________________________________________________________
13. You need to create a table named Student_Details to store the details of students in the database. The structure is shown in the following table.
Ans
CREATE XML SCHEMA COLLECTION StudentInfo AS'<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="StudentName" type="string"/>
<element name="Address" type="string"/>
<element name="ContactNumber" type="int"/>
</schema>'
CREATE TABLE Student_details
(
StudentID INT,
StudentInfo XML(StudentInfo)
)
_____________________________________________________________________________
SQL Lab@Home 9
1. The Store table is often queried. The queries are based on the CustomerID attribute and take a
long time to execute. Optimize the execution of the queries. In addition, ensure that the
CustomerID attribute does not contain duplicate values. (Use the AdventureWorks database)
Ans
CREATE UNIQUE NONCLUSTERED INDEX Idx_CustomerIDON Sales.Store(CustomerID)
_____________________________________________________________________________
2. The SalesOrderDetail and SalesOrderHeader tables store the details of the sales orders. You
have created a backup of the SalesOrderDetail and SalesOrderHeader tables in the
SalesOrderDetail_Backup and SalesOrderHeader_Backup tables. To generate a report displaying
the sales order ID and the total amount of all the products purchased against an order, you are
using the following query:
SELECT sd.SalesOrderID, SUM(LineTotal) AS [Total Amount]
FROM Sales.SalesOrderDetail_Backup sd JOIN Sales.SalesOrderHeader_Backup sh
ON sd.SalesOrderID = sh.SalesOrderID
GROUP BY sd.SalesOrderID
The table contains a large amount of data. Suggest a solution to optimize the execution of this
query. (Use the AdventureWorks database)
Ans
CREATE INDEX IDX_OrderHeader_SalesOrderID_CustomerIDON Sales.SalesOrderHeader (SalesOrderID, CustomerID)
_____________________________________________________________________________
3. The SalesOrderDetail table is often queried. The queries are based on the SalesOrderDetailID
and SalesOrderID attributes. The execution of the queries takes a long time. Optimize the
execution of the queries. In addition, check the performance of the query before and after
optimizing the execution of the queries. (Use the AdventureWorks database)
Ans
CREATE NONCLUSTERED INDEX Idx_SalesOrderId_SalesOrderDetailIdON Sales.SalesOrderDetail(SalesOrderId, SalesOrderDetailId)
_____________________________________________________________________________
4. A view has been defined as shown in the following statement:
CREATE VIEW vwSalesOrderDetail
AS
SELECT oh.SalesOrderID, TerritoryID, TotalDue, OrderQty, ProductID
FROM Sales.SalesOrderHeader oh JOIN Sales.SalesOrderDetail od
ON oh.SalesOrderID = od.SalesOrderID
The following UPDATE statement gives an error when you update the OrderQty and TerritoryID
attributes:
UPDATE vwSalesOrderDetail
SET OrderQty = 2, TerritoryID = 4
FROM vwSalesOrderDetail
WHERE SalesOrderID = 43659
Identify the problem and provide the solution. (Use the AdventureWorks database)
Ans
UPDATE vwSalesOrderDetailSET OrderQty = 2
FROM vwSalesOrderDetail
WHERE SalesOrderID = 43659
UPDATE vwSalesOrderDetail
SET TerritoryID = 4
FROM vwSalesOrderDetail
WHERE SalesOrderID = 43659
_____________________________________________________________________________
5. Display the details of all the credit cards that are of type SuperiorCard. The CreditCard table
contains a large amount of data. Therefore, the query takes a long time to retrieve the details of
the credit card. You need to optimize the execution of the query so that the result set does not
take too much time to be retrieved. (Use the AdventureWorks database)
Ans
CREATE FULLTEXT CATALOG CAT4 AS DEFAULTCREATE FULLTEXT INDEX ON Sales.CreditCard(CardType)
KEY INDEX AK_CreditCard_CardNumber
SELECT * FROM Sales.CreditCard
WHERE CONTAINS (CardType, '"SuperiorCard"')
_____________________________________________________________________________
6. The Store table contains the details of all the stores. The HR Manager of AdventureWorks, Inc.
frequently queries the Store table based on the names of the stores. He wants to create the
following reports:
A report containing the details of all the stores that contain the word 'bike' in their names.
A report displaying the names of all the stores containing the phrase 'Bike Store'.
Write the query so that the result set is retrieved quickly. (Use the AdventureWorks database)
Ans
CREATE FULLTEXT CATALOG CAT3 AS DEFAULTCREATE FULLTEXT INDEX ON Sales.Store(Name)
KEY INDEX PK_Store_CustomerID
SELECT * FROM Sales.Store
WHERE FREETEXT (Name, 'Bike')
SELECT * FROM Sales.Store
WHERE CONTAINS (Name, '"Bike Store"')
_____________________________________________________________________________
7. The manager of the production department wants to analyze the products that contain the exact
word 'road' in their description. Write a query so that the result set does not take a long time to
execute. (Use the AdventureWorks database)
Ans
SELECT * FROM Production.ProductDescriptionWHERE CONTAINS (Description, 'road')
_____________________________________________________________________________
8. Display the details of all the currencies that contain the words New and Dollar in their names.
These words can be included in any order. In addition, you need to make sure that the query does
not take too much time to execute. (Use the AdventureWorks database)
Ans
CREATE FULLTEXT INDEX ON Sales.Currency(Name)KEY INDEX AK_Currency_Name
SELECT * FROM Sales.Currency
WHERE FreeText (Name, '"New" And "Dollar"')
_____________________________________________________________________________
9. The Store table is often queried. The queries are based on the Demographics attribute and take a
long time to execute. Optimize the execution of these queries. (Use the AdventureWorks
database)
Ans
CREATE XML INDEX Sdx_Sales_Demographics_PATHON Sales.Store (Demographics)
USING XML INDEX PXML_Store_Demographics
FOR PATH
_____________________________________________________________________________
10. You need to create a report displaying the details of all the products that contain the word 'top'
near the word 'line' in their description. Write a query to retrieve the desired output. Write the query
such that it does not take a long time to execute. (Use the AdventureWorks database)
Ans
SELECT Description, ProductDescriptionIDFROM Production.ProductDescription
WHERE CONTAINS (Description, ' "top" near "line"')
_____________________________________________________________________________
11. Display the details of all the stores having the word 'bike' in their name. In addition, ensure that the
report contains the details of those stores that have the sales person ID as 277. You need to write
a query so that the result set does not take a long time to be retrieved. (Use the AdventureWorks
database)
Ans
SELECT * FROM Sales.Store WHERESalesPersonID = 227 AND CONTAINS (Name, 'Bike')
_____________________________________________________________________________
12. The DepartmentHistory table of employees is often queried. The queries are based on the
EmployeeID attribute and take a long time to execute. Optimize the execution of these queries.
(Use the AdventureWorks database)
Ans
CREATE NONCLUSTERED INDEX Idx_EmployeeIdON HumanResources.EmployeeDepartmentHistory(EmployeeID)
_____________________________________________________________________________
13. Create a view to retrieve the employee IDs along with the corresponding Group Name. (Use the
AdventureWorks database)
Ans
CREATE VIEW vsSalesAS
SELECT E.EmployeeId, D.GroupName
FROM HumanResources.Department D
INNER JOIN
HumanResources.EmployeeDepartmentHistory E
ON D.DepartmentID = E.DepartmentID
_____________________________________________________________________________
14. Create a view to retrieve SalesOrderId and Price of the Product along with the corresponding
Product Name. (Use the AdventureWorks database)
Ans
CREATE VIEW vsSalesAS
SELECT P.Name, S.SalesOrderID, S.UnitPrice
FROM Production.Product P INNER JOIN
Sales.SalesOrderDetail S
ON P.ProductID = S.ProductID
_____________________________________________________________________________
15. Create a view to display SalesOrderId, CustomerId, OrderQty, UnitPrice, SubTotal, and TotalDue
of the orders placed. (Use the AdventureWorks database)
Ans
CREATE VIEW vwSale ASSELECT H.CustomerID, H.SubTotal, H.TotalDue,
FROM Sales.SalesOrderDetail AS H
JOIN Sales.SalesOrderDetail AS D ON
H.SalesOrderID = D.SalesOrderID
_____________________________________________________________________________
SQL Lab@Home 10
1.Create a batch that finds the average pay rate of the employees and then lists the details of the employees who have a pay rate less than the average pay rate. (Use the AdventureWorks database)
Ans
DECLARE @avg_rate INTSELECT @avg_rate = AVG(rate)
FROM HumanResources.EmployeePayHistory
SELECT * FROM HumanResources.EmployeePayHistory
WHERE Rate < @avg_rate
GO
_____________________________________________________________________________
2.Create a function that returns the shipment date of a particular order. (Use the AdventureWorks database)
Ans
CREATE FUNCTION Sales.CalShipDate(@SalesOrderID INT)RETURNS DATE
AS
BEGIN
DECLARE @ShipDate Datetime
SELECT @ShipDate = ShipDate
FROM Sales.SalesOrderHeader WHERE SalesOrderID = @SalesOrderID
IF (@ShipDate IS NULL)
SET @ShipDate = 0
RETURN @ShipDate
END
SELECT SalesOrderID, Sales.CalShipDate(SalesOrderID)
AS ShippingDate
FROM Sales.SalesOrderHeader
_____________________________________________________________________________
3.Create a stored procedure that accepts the name of a product and display its ID, number, and availability. (Use the AdventureWorks database)
Ans
CREATE PROC ProductList @name VARCHAR(50)AS
BEGIN
PRINT 'Product details'
SELECT ProductID, ProductNumber, MakeFlag AS Availability
FROM Production.Product
WHERE Name = @name
END
EXECUTE ProductList 'Keyed Washer'
_____________________________________________________________________________
4.Create a function that returns the credit card number for a particular order. (Use the AdventureWorks database)
Ans
CREATE FUNCTION Sales.DisplayCardNumber(@SalesOrderID INT)RETURNS NVARCHAR(25)
AS
BEGIN
DECLARE @ret NVARCHAR(25)
SELECT @ret = CardNumber
FROM Sales.SalesOrderHeader S JOIN Sales.CreditCard C
ON S.CreditCardID = C.CreditCardID
WHERE SalesOrderID = @SalesOrderID
IF (@ret IS NULL)
SET @ret = 0
RETURN @ret
END
_____________________________________________________________________________
5.Create a function that returns a table containing the ID and the name of the customers who are categorized as individual customers (CustomerType = 'I'). The function should take one parameter. The parameter value can be either Shortname or Longname. If the parameter value is Shortname, only the last name of the customer will be retrieved. If the parameter value is Longname, then the full name will be retrieved. (Use the AdventureWorks database)
Ans
CREATE FUNCTION Sales.IndividualDetails(@format NVARCHAR(9))RETURN @tbl_Individual Table
(CustomerID INT PRIMARY KEY,Name NVARCHAR(100))
AS BEGIN IF (@format = 'LONGNAME')
INSERT @tbl_Individual
SELECT Cu.CustomerID,FirstName+''+LastName
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
ORDER BY LastName,FirstName
ELSE IF (@format = 'SHORTNAME')
INSERT @tbl_Individual
SELECT Cu.CustomerID,LastName FROM Person.Contact AS C
JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
ORDER BY LastName
RETURN
END
_____________________________________________________________________________
6.Create a user-defined function that accepts the account number of a customer and returns the customer's name from the Depositor table. Further, ensure that after creating the function, user is not able to alter or drop the Depositor table. (Use the AdventureWorks database)
Ans
CREATE FUNCTION fx_Disp_AccDet ( @AccNum INT)RETURNS TABLE WITH SCHEMABINDING
AS
RETURN (
SELECT Customer_name, Acc_num
FROM dbo.Depositer
WHERE Acc_num = @AccNum
)
SELECT * FROM fx_Disp_AccDet(101)
_____________________________________________________________________________
7.Create a batch to check the availability of the product, Chaining Bolts, in the stock. If the product is available, display a message, 'The Stock is available'. If the product is not available, display a message, 'The Stock is not available'. (Use the AdventureWorks database)
Ans
DECLARE @MakeFlag BITSELECT @MakeFlag = MakeFlag FROM Production.Product
WHERE Name = 'Chaining Bolts'
IF @MakeFlag = 1
PRINT 'The Stock is available'
ELSE
PRINT 'The Stock is not available'
_____________________________________________________________________________
8.Create a stored procedure that accepts two numbers, num1 and num2, and displays the result after dividing these two numbers. In addition, num1 should always be greater than num2. If num1 is less than num2, generate a user-defined error message, 'You have entered your numbers in the wrong way'.
Ans
CREATE PROCEDURE vx_DIV @Num1 INT, @Num2 INTAS
BEGIN
DECLARE @Div INT
IF @Num1 < @Num2
RAISERROR ('You have entered your numbers
in the wrong way' ,16,1)
ELSE
SET @Div=@Num1/@Num2
PRINT @Div
END
_____________________________________________________________________________
9.Create a stored procedure that returns the standard cost of a given product. (Use the AdventureWorks database)
Ans
CREATE PROCEDURE prcGetCostDetail2 @ProductId INT,@StandardCost MONEY OUTPUT
AS
BEGIN
IF EXISTS (SELECT * FROM Production.ProductCostHistory
WHERE ProductID = @ProductId)
BEGIN
SELECT @StandardCost = StandardCost
FROM Production.ProductCostHistory
RETURN 0
END
ELSE
RETURN 1
END
_____________________________________________________________________________
SQL Lab@Home 11
1.The management of AdventureWorks Incorporation wants that whenever the pay rate of an employee is modified, its effect on the monthly salary of that employee should be displayed. John, the Database Developer at AdventureWorks, has been asked to resolve this problem. Help John to find an appropriate solution.
Ans
CREATE TRIGGER updTriggerON HumanResources.EmployeePayHistory FOR UPDATE
AS
BEGIN
DECLARE @rate AS MONEY
DECLARE @frq AS INT
SELECT @rate = Rate,
@frq = PayFrequency FROM INSERTED
SELECT @rate * @frq * 30 AS 'Monthly salary'
END
UPDATE HumanResources.EmployeePayHistory
SET Rate = Rate + 5
WHERE EmployeeID = 160
_____________________________________________________________________________
2.The management of AdventureWorks Incorporation has decided that no user should be able to change the prices of products. In addition, the management wants that all the attempts to change the price should be saved in a temporary table, Temp. John, the Database Developer, has been asked to make significant changes in the database to implement this policy. What should John do to achieve the same?
Ans
CREATE TABLE Temp(
ProductID INT,
AttChangeCost MONEY,
AttTime DATETIME
)
CREATE TRIGGER updTrigger ON
[Production].[ProductCostHistory] INSTEAD OF UPDATE
AS
BEGIN
DECLARE @PID AS INT
DECLARE @COST AS MONEY
SELECT @PID = ProductID,
@COST = StandardCost FROM DELETED
INSERT INTO TEMP VALUES (@PID, @COST, GETDATE())
SELECT 'Sorry you can not change the price of a Product'
END
UPDATE [Production].[ProductCostHistory]
SET StandardCost = 55
WHERE ProductID = 707
_____________________________________________________________________________
3.Create a trigger to ensure that the average of the values in the Rate column of the EmployeePayHistory table should not be more than 20 when the value of the rate is increased. (Use the AdventureWorks database)
Ans
CREATE TRIGGER UpdatetriggerEPayHistoryON HumanResources.EmployeePayHistory
FOR UPDATE
AS
IF UPDATE (Rate)
BEGIN
DECLARE @AvgRate FLOAT
SELECT @AvgRate = AVG(Rate)
FROM HumanResources.EmployeePayHistory
IF (@AvgRate > 20)
BEGIN
PRINT 'The average value of
rate cannot be more than 20'
ROLLBACK TRANSACTION
END
END
_____________________________________________________________________________
4.Create a trigger on the Product table to ensure that if the value for the SafetyStockLevel column becomes less than or equal to the specified ReorderPoint column for a product because of an update operation, a message informing that the reorder level has been attained should be displayed. (Use the AdventureWorks database)
Ans
CREATE TRIGGER OrderStockON Production.Product
FOR UPDATE
AS
DECLARE @PID VARCHAR
SELECT @PID = ProductID FROM INSERTED
IF ( (SELECT SafetyStockLevel FROM INSERTED)
<= (SELECT ReorderPoint FROM INSERTED))
BEGIN
PRINT ' The stock of ' + @PID +
' is under the reorder point'
END
_____________________________________________________________________________
5.Create a trigger named Emp_Update on the EmployeeDetail table. This trigger should restrict a user from performing any kind of DML operation on the table before 9 A.M and after 5 P.M. (Use the AdventureWorks database)
Ans
CREATE TRIGGER Emp_UpdateON EmployeeDetails AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @Time INT
SET @Time = DATENAME(HH, GETDATE())
IF @Time NOT BETWEEN 9 AND 17
BEGIN
ROLLBACK
PRINT ('The operation cannot be performed
before 9 A.M and after 5 P.M')
END
END
_____________________________________________________________________________
6.The management of AdventureWorks wants that whenever the account number of a user is modified, the old account number, as well as the new account number, should be instantaneously displayed to the user. How can you accomplish the desired task?
Ans
CREATE TRIGGER VendoracccountnumON Purchasing.vendor
FOR UPDATE AS
BEGIN
DELCARE @oldvalue NVARCHAR(15)
DECLARE @newvalue NVARCHAR(15)
SELECT @oldvalue = AccountNumber FROM DELETED
SELECT @newvalue = AccountNumber FROM INSERTED
PRINT 'the old account number is' +@oldvalue
PRINT 'the new account number is' +@newvalue
END
_____________________________________________________________________________
7.Create a trigger named Emp_Update_Trg that will restrict the updation of salary of an employee if the new salary is less than the previous salary in the EmployeeDetails table. (Use the AdventureWorks database)
Ans
CREATE TRIGGER Emp_Update_TrgON EmployeeDetails AFTER UPDATE
AS
BEGIN
DECLARE @OldSal MONEY
DECLARE @NewSal MONEY
SELECT @OldSal = Salary FROM DELETED
SELECT @NewSal = Salary FROM INSERTED
IF @OldSal > @NewSal
BEGIN
ROLLBACK
PRINT ('New salary cannot be less than the old salary')
END
END
_____________________________________________________________________________
8.Create a trigger named Emp_Delete_Trg on the EmployeeDetails table. This trigger should restrict the deletion of records from the table if the designation of an employee is Manager. (Use the AdventureWorks database)
Ans
CREATE TRIGGER Emp_Delete_TrgON EmployeeDetails
AFTER DELETE
AS
BEGIN
DECLARE @JOB VARCHAR(50)
SELECT @JOB = Designation FROM DELETED
IF @JOB = 'Manager'
BEGIN
ROLLBACK
PRINT ('CANNOT DELETE MANAGER FROM THE TABLE')
END
END
_____________________________________________________________________________
9.Create a trigger named Emp_Nested_Trg on the EmployeeDetails table for an insert operation. This trigger should ensure that a new record being inserted into the EmployeeDetails table has a matching record in the DeptDetails table. Otherwise, the insert operation is rolled back. (Use the AdventureWorks database)
Ans
CREATE TRIGGER Emp_Nested_TrgON EmployeeDetails AFTER INSERT
AS
BEGIN
DECLARE @DptNo INT
SELECT @DptNo = DeptNo FROM INSERTED
IF NOT EXISTS (SELECT * FROM DeptDetails
WHERE DeptNo = @DptNo)
PRINT 'The specified DeptNo does not exist.'
ROLLBACK
END
_____________________________________________________________________________
10.Consider the following statement:
CREATE VIEW Emp_Dept
AS
SELECT E.EmployeeID, E.EmpName, E.Designation, E.Salary, D.DeptNo, D.DeptName
FROM EmployeeDetails E
INNER JOIN DeptDetails D ON E.DeptNo=D.DeptNo
A view named Emp_Dept has been created on the base tables, EmployeeDetails and DeptDetails, by using the preceding statement. John is the Database Developer with AdventureWorks Incorporation. He wants that the users should be able to insert data in the underlying base tables by using the view. He can implement the desired functionality if the insert operation affects a single table. However, the same cannot be done if multiple tables are involved. He wants to be able to insert data in both the tables with the help of a single insert statement. Help John in resolving the problem.
Ans
CREATE TRIGGER View_Insert_TrgON Emp_Dept
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO EmployeeDetails(EmployeeID, EmpName,
Designation, Salary, DeptNo)
SELECT EmployeeID, EmpName, Designation, Salary,
DeptNo FROM INSERTED
INSERT INTO DeptDetails(DeptNo, DeptName)
SELECT DeptNo, DeptName FROM INSERTED END
_____________________________________________________________________________
11.John is a Database Developer. He is not able to delete the unwanted records from the base tables by using the view, Emp_Dept. Help John in resolving the problem.
Ans
CREATE TRIGGER View_Delete_TrgON Emp_Dept
INSTEAD OF DELETE
AS
BEGIN
DECLARE @DeptNo INT
DECLARE @Count INT
SELECT @DeptNo = DeptNo FROM DELETED
SELECT @Count = COUNT(*) FROM EmployeeDetails
WHERE DeptNo = @DeptNo
DELETE FROM EmployeeDetails WHERE DeptNo = @DeptNo
IF @Count = 1
DELETE FROM DeptDetails WHERE DeptNo = @DeptNo
END
_____________________________________________________________________________
SQL Lab@Home 12
1. You are a database administrator at AdventureWorks, Inc. You have been notified by the users
that the database works very slowly during peak business hours. You have decided to monitor the
T-SQL statements to identify the statements that are taking time to execute. How will you monitor the server
Ans
open sql server profilerconnect to the server dialog box is displayed
click at New Trace
connect to the sql server
the trace properties window is displayed
type the Name of the trace in the Trace name in the text box
select the save to file: check box
clear the existing connection check box
click at run
and then click at YES button
_____________________________________________________________________________
2.You are a database administrator at AdventureWorks, Inc. As a part of regular maintenance, you need to check the following details:
Current sessions
File space usage
Current transactions
Current connections
How will you perform this task?
Ans
SELECT * FROM sys.dm_exec_sessionsSELECT * FROM sys.dm_db_file_space_usage
SELECT * FROM sys.dm_tran_current_transaction
SELECT * FROM sys.dm_exec_connections
_____________________________________________________________________________
3.You are a database administrator at AdventureWorks, Inc. The following statements are executed to check the state of I/O operations:
Select * sys.dm_db_backup_tapes
Select * from sys.dm_db_pending_io_requests
Select * from sys.dm_db_cluster_shared_drives
Select * from sys.dm_db_virtual_files_stats
However, the preceding statements generated an error. Identify the error and provide the solution.
Ans
SELECT * FROM sys.dm_io_backup_tapesSELECT * FROM sys.dm_io_pending_io_requests
SELECT * FROM sys.dm_io_cluster_shared_drives
SELECT * FROM sys.dm_io_virtual_file_stats
(DB_ID (N'AdventureWorks2012'), NULL);
_____________________________________________________________________________
4.You are a database administrator at AdventureWorks, Inc. You want to check the information about the operating system resources that are specific to SQL Server. How will you do this?
Ans
SELECT * FROM sys.dm_os_buffer_descriptorsSELECT * FROM sys.dm_os_memory_pools
SELECT * FROM sys.dm_os_child_instances
SELECT * FROM sys.dm_os_sys_info
SELECT * FROM sys.dm_os_loaded_modules
SELECT * FROM sys.dm_os_tasks
SELECT * FROM sys.dm_os_memory_clerks
SELECT * FROM sys.dm_os_workers
_____________________________________________________________________________
5.You are a database administrator at AdventureWorks, Inc. You want to get the information regarding the amount of disk activity generated for the Employee table. How will you perform this task?
ANS.
SET STATISTICS IO ON
SELECT * FROM HumanResources.Employee
SET STATISTICS IO OFF
_____________________________________________________________________________
6. You are a database administrator at AdventureWorks, Inc. and want to generate an effective execution plan on the Employee table. For this, create a statistics on the Employee table. In addition, show the list of all the statistics maintained on the Employee table?
Ans
CREATE STATISTICS EmployeeInfoON HumanResources.Employee
(EmployeeID,ContactId,BirthDate,MaritalStatus)
sp_helpstats 'HumanResources.Employee', 'ALL'
_____________________________________________________________________________
7.An employee having contact ID as 2 got married and her last name has been changed to Smith. You have to make the required changes in the Contact table and track these changes by using the CDC method. (Use the AdventureWorks database)
Ans
UPDATE Person.Contact
SET Lastname = 'Smith'
WHERE ContactID = 2
SELECT * FROM cdc.Person_Contact_CT
_____________________________________________________________________________
8.An employee having contact ID as 4 has changed his title from (Sr.) to (Mr.). You need to update its record into Contact table and track these changes by using the track changing method. (Use the AdventureWorks database)
ANS.
ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING
UPDATE Person.Contact
SET Title = 'Mr.'
WHERE ContactID = 4
SELECT * FROM CHANGETABLE
(CHANGES Person.Contact, 0) AS CT
_____________________________________________________________________________
thankyou
ReplyDeleteAnt Man Evolution 2018
ReplyDeleteManjaro Linux Review
15 August 2018 Speech
Sabse Upar