Lab Work
Introduction to Database Management System
Prepare Sample Data To Practice SQL Skill.
Sample Table – Worker
WORKER_ID FIRST_NAME LAST_NAME SALARY JOINING_DATE DEPARTMENT
001 Monika Arora 100000 2014-02-20 09:00:00 HR
002 Niharika Verma 80000 2014-06-11 09:00:00 Admin
003 Vishal Singhal 300000 2014-02-20 09:00:00 HR
004 Amitabh Singh 500000 2014-02-20 09:00:00 Admin
005 Vivek Bhati 500000 2014-06-11 09:00:00 Admin
006 Vipul Diwan 200000 2014-06-11 09:00:00 Account
007 Satish Kumar 75000 2014-01-20 09:00:00 Account
008 Geetika Chauhan 90000 2014-04-11 09:00:00 Admin
Sample Table – Bonus
WORKER_REF_ID BONUS_DATE BONUS_AMOUNT
1 2016-02-20 00:00:00 5000
2 2016-06-11 00:00:00 3000
3 2016-02-20 00:00:00 4000
1 2016-02-20 00:00:00 4500
2 2016-06-11 00:00:00 3500
Sample Table – Title
WORKER_REF_ID WORKER_TITLE AFFECTED_FROM
1 Manager 2016-02-20 00:00:00
2 Executive 2016-06-11 00:00:00
8 Executive 2016-06-11 00:00:00
5 Manager 2016-06-11 00:00:00
4 Asst. Manager 2016-06-11 00:00:00
7 Executive 2016-06-11 00:00:00
6 Lead 2016-06-11 00:00:00
3 Lead 2016-06-11 00:00:00
PREPARED BY PUNIT KUMAR CHAUBEY BIET LUCKNOW
To prepare the sample data, you can run the following queries in your database query
executor or on the SQL command line.
Q-1. Write an SQL query to fetch “FIRST_NAME” from Worker table
using the alias name as <WORKER_NAME>.
Q-2. Write an SQL query to fetch “FIRST_NAME” from Worker table in
upper case.
Q-3. Write an SQL query to fetch unique values of DEPARTMENT from
Worker table.
Q-4. Write an SQL query to print the first three characters
of FIRST_NAME from Worker table.
Q-5. Write an SQL query to find the position of the alphabet (‘a’) in the
first name column ‘Amitabh’ from Worker table.
Q-6. Write an SQL query to print the FIRST_NAME from Worker table
after removing white spaces from the right side.
Q-7. Write an SQL query to print the DEPARTMENT from Worker table
after removing white spaces from the left side.
Q-8. Write an SQL query that fetches the unique values of
DEPARTMENT from Worker table and prints its length.
Q-9. Write an SQL query to print the FIRST_NAME from Worker table
after replacing ‘a’ with ‘A’.
Q-10. Write an SQL query to print the FIRST_NAME and LAST_NAME
from Worker table into a single column COMPLETE_NAME. A space
char should separate them.
Q-11. Write an SQL query to print all Worker details from the Worker
table order by FIRST_NAME Ascending.
PREPARED BY PUNIT KUMAR CHAUBEY BIET LUCKNOW
Q-12. Write an SQL query to print all Worker details from the Worker
table order by FIRST_NAME Ascending and DEPARTMENT
Descending.
Q-13. Write an SQL query to print details for Workers with the first
name as “Vipul” and “Satish” from Worker table.
Q-14. Write an SQL query to print details of workers excluding first
names, “Vipul” and “Satish” from Worker table.
Q-15. Write an SQL query to print details of Workers with
DEPARTMENT name as “Admin”.
Q-16. Write an SQL query to print details of the Workers whose
FIRST_NAME contains ‘a’.
Q-17. Write an SQL query to print details of the Workers whose
FIRST_NAME ends with ‘a’.
Q-18. Write an SQL query to print details of the Workers whose
FIRST_NAME ends with ‘h’ and contains six alphabets.
Q-19. Write an SQL query to print details of the Workers whose
SALARY lies between 100000 and 500000.
Q-20. Write an SQL query to print details of the Workers who have
joined in Feb’2014.
Ans.
Q-21. Write an SQL query to fetch the count of employees working in
the department ‘Admin’.
Q-22. Write an SQL query to fetch worker names with salaries >= 50000
and <= 100000.
Q-23. Write an SQL query to fetch the no. of workers for each
department in the descending order.
Q-24. Write an SQL query to print details of the Workers who are also
Managers.
Q-25. Write an SQL query to fetch duplicate records having matching
data in some fields of a table.
Q-26. Write an SQL query to show only odd rows from a table.
Q-27. Write an SQL query to show only even rows from a table.
Q-28. Write an SQL query to clone a new table from another table.
Q-29. Write an SQL query to fetch intersecting records of two tables.
Q-30. Write an SQL query to show records from one table that another
table does not have.
Q-31. Write an SQL query to show the current date and time.
PREPARED BY PUNIT KUMAR CHAUBEY BIET LUCKNOW
Q-32. Write an SQL query to show the top n (say 10) records of a table.
Q-33. Write an SQL query to determine the nth (say n=5) highest salary
from a table.
Q-34. Write an SQL query to determine the 5th highest salary without
using TOP or limit method.
Q-35. Write an SQL query to fetch the list of employees with the same
salary.
Q-36. Write an SQL query to show the second highest salary from a
table.
Q-37. Write an SQL query to show one row twice in results from a
table.
Q-38. Write an SQL query to fetch intersecting records of two tables.
Q-39. Write an SQL query to fetch the first 50% records from a table.
Q-40. Write an SQL query to fetch the departments that have less than
five people in it.
Q-41. Write an SQL query to show all departments along with the
number of people in there.
Q-42. Write an SQL query to show the last record from a table.
Q-43. Write an SQL query to fetch the first row of a table.
Q-44. Write an SQL query to fetch the last five records from a table.
Q-45. Write an SQL query to print the name of employees having the
highest salary in each department.
Q-46. Write an SQL query to fetch three max salaries from a table.
Q-47. Write an SQL query to fetch three min salaries from a table.
Q-48. Write an SQL query to fetch nth max salaries from a table.
Q-49. Write an SQL query to fetch departments along with the total
salaries paid for each of them.
Q-50. Write an SQL query to fetch the names of workers who earn the
highest salary.
PREPARED BY PUNIT KUMAR CHAUBEY BIET LUCKNOW
