Q.1.

Emp_Id Name Salary Designation

E01 Kajal 7800 Manager
E02 Rahul 5000 Sales Executive
E03 Tarun 5500 Cle

1.) You are tasked with designing a new employee database for your company. Based on the given
table structure. Identify the fields that would be necessary to store key information about each
employee.
2.) As a database administrator, you need to ensure each employee has a unique identifier.
Which field in the table would you choose as the primary key, and why?
3.) During a review of the employee database, you realize there could be another field apart from
the primary key that can uniquely identify a record.
Identify the field and justify why It could server as an alternate key.
4.) You are integrating the employee table with another table that stores department details.
Explain how you would use the primary key from the employee table and a foreign key in the
department table to establish a relationship between the two tables. Illustrate with an example.

Q.2.) Consider the table Product and write SQL queries for the following:-

PID PName Price Quantity Category

P01 Board 1000 2 Office
P02 Eyeliner 700 50 MakeUp
P04 Pencil 5 500 School
P05 Lamp 250 10 Domestic
P08 BedSheet 450 45 Domestic
P09 Laptop 75000 15 Office

a) To display names of all products whose category is “DOMESTIC”.

b) To display product name and the price whose quantity is less than 50.
c) To display price and quantity of LAPTOP.
d) To display name and quantity of those products whose price is above 5000.

Q.3) Consider the following table :Employee

Emp_no E_name Profile Manager Hire_date Salary Commission Dept_no

8369 SMIT Clerk 8902 1990-12-18 8000 NULL 20

8499 ANYA Salesman 8698 1991-02-20 16000 300.00 30

8521 SETH Salesman 8698 1991-04-02 12500 500.00 30

8566 MAHADEVAN Manager 8839 1991-02-22 29850 NULL 20

8654 MOMIN Salesman 8698 1991-09-28 12500 1400.00 30

8698 BINA Manager 8839 1991-05-01 28500 NULL 30

a) How many fields and records are there in the table Employee?
b) Write the SQL queries for the following:
i) Display E_name and salary of those employees whose Salary is less than or equal to
16000.
ii) Display details of employees who are getting commission of Rs. 300 or greater.
iii) Display all the details of the employee profile ‘CLERK’.

Q.4) Consider the following table Employee:-

Emp_no E_name Profile Manager Hire_date Salary Commission Dept_no

8369 SMIT Clerk 8902 1990-12-18 8000 NULL 20

8499 ANYA Salesman 8698 1991-02-20 16000 300.00 30

8521 SETH Salesman 8698 1991-04-02 12500 500.00 30

8566 MAHADEVAN Manager 8839 1991-02-22 29850 NULL 20

8654 MOMIN Salesman 8698 1991-09-28 12500 1400.00 30

8698 BINA Manager 8839 1991-05-01 28500 NULL 30

a) How many fields and records are there in the table Employee?
b) Write the SQL queries for the following:-
i) Display E_Name and Salary of these Employees whose Salary is greater than or equal to
22000.
ii) Display details of employees those are not getting Commission.
iii) Display all the details of the employee profile ‘Salesman’.

Q.5.) Table:- EmployeeSalary

Last Name First Name Dept. Payroll Number Salary Job Title
Shen James Finance A621 19500 Payroll Clerk
Gupta Shruti Finance M502 35000 Accountants
Bedi Reeta HR M421 18500 Secetary
Walker Tia Sales W815 24000 Sales Representative
Shafia Ahmed Factory H219 39000 Factory Manager
Mittal Chavi Purchasing M134 2000 Purchasing Card

i) How many fields and records are there in the table EmployeeSalary?
ii) Write the SQL queries for the following:-
a) List all data of table EmployeeSalary.
b) Display all the records whose salary is less than 25000.
c) Display the FirstName and Job Title of employees having Dept ‘Finance’.
Q.6) Table : SummerCamp

ChildID FirstName LastName DOB Gender Group

109 Vedansh Gupta 30/06/10 M 2A
214 Mack Tyagi 09/12/08 M 1B
115 Aditi` Thakur 13/04/09 F 1A
108 Vikrant Chauhan 02/02/09 M 2A
141 Swati Sainit 06/05/10 F 2B
233 Vishaka Tyagi 01/08/10 F 3A
274 Madhur Gupta 06/03/10 M 3B

i) How many fields and records are there in the table SummaryCamp?
ii) Write SQL queries for the following:-
a) Display all the records of table SummaryCamp whose DOB is between 02/02/09 and
06/03/10.
b) Display all the records having Gender F.
c) Display all the records according to the First Name.

Q.7) Table :- Items

Code IName Qty. Price Company TCode

1001 Digital Pad 121 120 11000 XENITA T01
1006 LED SCREEN 70 38000 Santora T02
40
1004 Car GPS 50 2150 Geoknow T01
System
1003 Digital Camera 160 8000 Digiclick T02
12X
1005 Pen Drive 32 600 1200 Storehome T03
Gb

a) How many fields and records are there in the table ITEMS?
b) Write the SQL queries to :-
i) Display the details of all the items in ascending order of item name (i.e. IName)
ii) Display IName and Price of all those items whose price is in the range of 10000
and 22000.
iii) Display all the information of the items having price less than 10000.
Q.8) TABLE : APPLICANTS

No. Name FEE Gender C_ID Join Year

1012 Amardeep 30000 M A01 2012
1102 Avisha 25000 F A02 2009
1103 Ekant 30000 M A02 2011
1049 Arun 30000 M A03 2009
1025 Amber 40000 M A02 2011

i) How many records and fields are there in the table APPLICANTS.
ii) Write the SQL Queries to :
a) Display NAME, FEE, GENDER, JOINYEAR about the APPLICANTS, who have joined
before 2010.
b) Display the NAMES of all APPLICANTS in ascending order of their JOINYEAR.
c) Display the details of APPLICANTS of female.

Q.9) TABLE:PRODUCT

S_No. P_Name S_Name `QTY Cost City

S1 Biscuit PriyaGold 120 12.00 Delhi
S2 Bread Britania 200 25.00 Mumbai
S3 Chocolate Cadbury 350 40.00 Mumbai
S4 Sauce Kissan 400 45.00 Chennai

i) How many fields and records are there in PRODUCT table?

ii) Write SQL Queries for the following:-
a) Display all Products whose Qty is between 100 and 400.
b) Display S_Name, P_Name, Cost for all the Products whose Qty is less than 300.
c) Display all the records alphabetically by S_Name.
Q.10) TALBE:SHOPPE

ID SName Area
S001 ABC Computeronics CP
S002 All Infotech Media GK II
S003 Tech Shoppe CP
S004 Geeks Tecno Soft Nehru Place
S005 Hitech Tech Store Nehru Place

i) How many fields and records are there in SHOPPE table?

ii) Write SQL Commands for the following:-

a) Display Id and SName of all the shops located in Nehru Place.

b) Display the details alphabetically by SName.
c) Display SName of shops whose Area is CP.