SQL LAB @ HOME 6 ~ NIIT POST

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.

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.

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