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.
Answer:- The Fields are : Emp_Id, Name, Salary,
Designation.
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?
Answer:- Emp_id
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.
Answer:- Combination of Emp_id,Designation.
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.
Answer :- Primary key is the unique identifier for each row in a
table. The filed designated as primary key cannot be NULL.
Example In the Employee table, the Emp_id serves as a foreign
key. It establishes a relationship by linking to a primary key in
another table. For instance the Emp_id in the Orders table
references the Emp_id in the Employee Table.
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”.
Answer :- Select PName From Produce Where Category =
“DOMESTIC”
b) To display product name and the price whose quantity is less than 50.
Answer:- Select PName, Price From Product Where
Quantity < 50
c) To display price and quantity of LAPTOP.
Answer :- Select Price,Quantity From Product Where PName =
“Laptop”.
d) To display name and quantity of those products whose price is above 5000.
Answer :- Select Name,Quantity From Product Where Price >
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?
Answer:- Fields = 8, Records = 6
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.
Answer:- Select E_Name, Salary From Employee Where Salary <= 16000
ii) Display details of employees who are getting commission of Rs. 300 or greater.
Answer:- Select * From Employee Where Commission >= 300
iii) Display all the details of the employee profile ‘CLERK’.
Answer:- Select * From Employee Where 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?
Answer :- There are 8 fields and 6 records in the table
EmployeeSalary.
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.
Answer :- SELECT E_Name,Salary From Employee Where
Salary >= 22000.
ii) Display details of employees those are not getting Commission.
Answer :- SELECT * From Employee Whose Commission
is null
iii) Display all the details of the employee profile ‘Salesman’.
Answer :- SELECT * From Employee Where 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?
Answer:- There are 6 fields and 6 records in the table
EmployeeSalary
ii) Write the SQL queries for the following:-
a) List all data of table EmployeeSalary.
Answer:- Select * From EmployeeSalary
b) Display all the records whose salary is less than 25000.
Answer:- Select * From EmployeeSalary Where Salary
< 25000
c) Display the FirstName and Job Title of employees having Dept ‘Finance’.
Answer:- Select FirstName, JobTitle From EmployeeSalary Where
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?
ANSWER:- Fields = 6
Records: = 7
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.
Answer :- Select * From SummaryCamp Where DOB >=
“02/02/09” and DOB <= “06/03/10”
b) Display all the records having Gender F.
Answer:- Select * From SummaryCamp Where Gender = “F”
c) Display all the records according to the First Name.
Answer:- Select * From SummaryCamp Order by FirstName
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?
Answer:- Fields = 6
Records=5
b) Write the SQL queries to :-
i) Display the details of all the items in ascending order of item name (i.e. IName)
Answer:- Select * From Items Order By IName
ii) Display IName and Price of all those items whose price is in the range of 10000
and 22000.
Answer:- Select IName, Price From Items Where price >= 10000
and price <= 22000
iii) Display all the information of the items having price less than 10000.
Answer:- Select * From Items Where price < 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.
Answer:- Fields = 6, Records = 5
ii) Write the SQL Queries to :
a) Display NAME, FEE, GENDER, JOINYEAR about the APPLICANTS, who have joined
before 2010.
Answer:- Select Name,Fee,Gender,JoinYear From Applicants
where joinyear < 2010
b) Display the NAMES of all APPLICANTS in ascending order of their JOINYEAR.
Answer:- Select Name From Applicants order by Joinyear
c) Display the details of APPLICANTS of female.
Answer:- Select * From Applicants where Gender=”F”
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?
Answer:- Fields = 6, Records=4
ii) Write SQL Queries for the following:-
a) Display all Products whose Qty is between 100 and 400.
Answer:- Select * From Product Where Qty >= 100 and Qty
<= 400
b) Display S_Name, P_Name, Cost for all the Products whose Qty is less than 300.
Answer:- Select S_Name,P_Name, Cost From Product Where Qty
< 300
c) Display all the records alphabetically by S_Name.
Answer:- Select * From Product Order 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?
Answer:- Fields = 3, Records=5
ii) Write SQL Commands for the following:-
a) Display Id and SName of all the shops located in Nehru Place.
Answer:- Select ID,SName From Shoppe where area = “Nehru
Place”.
b) Display the details alphabetically by SName.
Answer:- Select * From Shoppe Order By SName
c) Display SName of shops whose Area is CP.
Answer:- Select SName From Shoppe Where Area = “CP”
