SQL LAB at Home 3

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

select * from Production.Location
where Name Like '%Bike%'

---------------------------------------------------------------------------------------------------------------------------------------

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

select * from Production.Location
where Name Like 'N%'

---------------------------------------------------------------------------------------------------------------------------------------

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 'sales Person Id'=salesPersonID,'Territory ID'=TerritoryID,'Sales Quota'=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
order by bonus desc

------------------------------------------------------------------------------------------------------------------------------------

5. 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 EmployeeID,LoginID,Title from HumanResources.Employee
order by EmployeeID
OFFSET 5 Rows
fetch next 10 Rows only

------------------------------------------------------------------------------------------------------------------------------------

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

                                                                                                             

-------------------------------------------------------------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------------------------------------------------------------

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?

================================================================================================

GNIITSolution 

                               GNIIT/NIIT Solution: -Sonugiri0032@gmail.com