SQL LAB @ HOME 3 ~ NIIT POST

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 * 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?

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: