DATE: 26.09.
2020
EX No: 03
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
CREATE:
INSERT:
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
CREATE:
INSERT:
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
CREATE:
INSERT:
1. Write an SQL query to fetch “FIRST_NAME” from Worker table using the alias name as <WORKER_NAME>.
QUERY: Select FIRST_NAME AS WORKER_NAME from Worker;
OUTPUT OF THE QUERY:
2. Write an SQL query to fetch “FIRST_NAME” from Worker table in upper case.
QUERY: Select upper(FIRST_NAME)from Worker;
OUTPUT OF THE QUERY:
3. Write an SQL query to fetch unique values of DEPARTMENT from Worker table.
QUERY: Select distinct DEPARTMENT from Worker;
OUTPUT OF THE QUERY:
4. Write an SQL query to print the first three characters of FIRST_NAME from Worker table.
QUERY: Select substring(FIRST_NAME,1,3)from Worker;
OUTPUT OF THE QUERY:
5. Write an SQL query to find the position of the alphabet (‘a’) in the first name column ‘Amitabh’ from Worker table.
QUERY: Select INSTR(FIRST_NAME, BINARY ’a’)from Worker where FIRST_NAME=’Amitabh’;
OUTPUT OF THE QUERY:
6. Write an SQL query to print the FIRST_NAME from Worker table after removing white spaces from the right side.
QUERY: Select RTRIM(FIRST_NAME)from Worker;
OUTPUT OF THE QUERY:
7. Write an SQL query that fetches the unique values of DEPARTMENT from Worker table and prints its length.
QUERY: Select distinct length(DEPARTMENT)from Worker;
OUTPUT OF THE QUERY:
8. Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending.
QUERY: Select*from Worker order by FIRST_NAME asc;
OUTPUT OF THE QUERY:
9. Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending and
DEPARTMENT Descending.
QUERY: Select*from Worker order by FIRST_NAME asc, DEPARTMENT desc;
OUTPUT OF THE QUERY:
10. Write an SQL query to print details of Workers with DEPARTMENT name as “Admin”.
QUERY: Select*from Worker where DEPARTMENT like ‘Admin%’;
OUTPUT OF THE QUERY:
11. Write an SQL query to print details of the Workers whose FIRST_NAME contains ‘a’.
QUERY: Select*from Worker where FIRST_NAME like ‘%a%’;
OUTPUT OF THE QUERY:
12. Write an SQL query to print details of the Workers whose SALARY lies between 100000 and 500000.
QUERY: Select*from Worker where SALARY between 100000 and 500000;
OUTPUT OF THE QUERY:
13. Write an SQL query to print details of the Workers who have joined in Feb’2014.
QUERY: Select*from Worker where year(JOINING_DATE)=2014 and month(JOINING_DATE)=2;
OUTPUT OF THE QUERY:
14. Write an SQL query to fetch the count of employees working in the department ‘Admin’.
QUERY: SELECT COUNT (*)FROM worker DEPARTMENT=’Admin’;
OUTPUT OF THE QUERY:
15. Write an SQL query to fetch worker names with salaries >= 50000 and <= 100000.
QUERY: SELECT CONCAT (FIRST_NAME,’ ‘, LAST_NAME) As Worker_Name, Salary
FROM worker WHERE WORKER_ID IN(SELECT WORKER_ID FROM worker WHERE Salary BETWEEN 50000
AND 100000);
OUTPUT OF THE QUERY:
16. Write an SQL query to fetch the no. of workers for each department in the descending order.
QUERY: SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers
FROM worker
GROUP BY DEPARTMENT
ORDER BY No_Of_Workers DESC;
OUTPUT OF THE QUERY:
17. Write an SQL query to print details of the Workers who are also Managers.
QUERY:SELECT DISTINCT W.FIRST_NAME,T.WORKER_TITLE
FROM Worker W
INNER JOIN Title T
ON W.WORKER_ID=T.WORKER_REF_ID
AND T.WORKER_TITLE in (‘Manager’);
OUTPUT OF THE QUERY:
18. Write an SQL query to clone worker table to a new table worker clone.
QUERY: CREATE TABLE WorkerClone LIKE Worker;
OUTPUT OF THE QUERY:
19. Select the record present in Worker and not in title table.
QUERY: Select WORKER_REF_ID MINUS FROM Title;
OUTPUT OF THE QUERY:
20. Write an SQL query to determine the nth (say n=5) highest salary from a table.
QUERY: SELECT*FROM Worker ORDER BY ’Salary’ DESC limit 4,1;
OUTPUT OF THE QUERY:
21. Write an SQL query to fetch the list of employees with the same salary.
QUERY: Select distinct W.WORKER_ID, W.Salary from Worker W, Worker W1 where W.salary and
W.WORKER_ID !=W1.WORKER_ID;
OUTPUT OF THE QUERY:
RESULT: Thus, the given SQL queries are executed successful.
