NIIT SQL LAB @ HOME ALL ANS

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 :DoctorID

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. 

ANS:

Entities: Department and Employee

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. 

ANS:

Entities:Patient and Doctor

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

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 , Account

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.

ANS:

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




                                     

SQL LAB @ HOME 2

1.   Display the details of all the customers. (Use the AdventureWorks database)ans:

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 4.
(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 * 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: