SQL LAB @ HOME 7 ~ NIIT POST

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 NarrowFabrics

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) 

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

  

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

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

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

ANS:

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

ANS:

CREATE TYPE Quality
FROM 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.VendorAddress
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'