Reg No: 22BCE10897
Name: Pranjali Verma
1. Write an SQL query to fetch "FIRST_NAME" from Worker table using the alias name
as “WORKER NAME”
SELECT FIRST_NAME AS "WORKER NAME"
FROM Worker;
2. Write an SQL query to fetch "FIRST_NAME" from Worker table in upper case.
SELECT UPPER(FIRST_NAME)
FROM Worker;
3. Write an SQL query to fetch unique values of DEPARTMENT from Worker table.
SELECT DISTINCT DEPARTMENT
FROM Worker;
4. Write an SQL query to print the first three characters of FIRST_NAME from Worker
table.
SELECT SUBSTRING(FIRST_NAME, 1, 3)
FROM Worker;
Reg No: 22BCE10897
Name: Pranjali Verma
5. Write an SQL query to find the position of the alphabet ('a') in the first name
column 'Amitabh' from Worker table.
SELECT INSTR(FIRST_NAME, 'a')
FROM Worker
WHERE FIRST_NAME = 'Amitabh';
6. Write an SQL query to print the FIRST_NAME from Worker table after removing
white spaces from the right side.
SELECT RTRIM(FIRST_NAME)
FROM Worker;
7. Write an SQL query to print the DEPARTMENT from Worker table after removing
white spaces from the left side.
SELECT LTRIM(DEPARTMENT)
FROM Worker;
Reg No: 22BCE10897
Name: Pranjali Verma
8. Write an SQL query that fetches the unique values of DEPARTMENT from Worker
table and prints its length.
SELECT DISTINCT DEPARTMENT, LENGTH(DEPARTMENT)
FROM Worker;
9. Write an SQL query to print the FIRST_NAME from Worker table after replacing 'a'
with 'A'.
SELECT REPLACE(FIRST_NAME, 'a', 'A')
FROM Worker;
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.
SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS COMPLETE_NAME
FROM Worker;
Reg No: 22BCE10897
Name: Pranjali Verma
11. Write an SQL query to print all Worker details from the Worker table order by
FIRST_NAME Ascending.
SELECT *
FROM Worker
ORDER BY FIRST_NAME ASC;
12. Write an SQL query to print all Worker details from the Worker table order by
FIRST_NAME Ascending and DEPARTMENT Descending.
SELECT *
FROM Worker
ORDER BY FIRST_NAME ASC, DEPARTMENT DESC;
13. Write an SQL query to print details for Workers with the first name as "Vipul" and
"Satish" from Worker table.
SELECT *
FROM Worker
WHERE FIRST_NAME IN ('Vipul', 'Satish');
Reg No: 22BCE10897
Name: Pranjali Verma
14. Write an SQL query to print details of workers excluding first names, "Vipul" and
"Satish" from Worker table.
SELECT *
FROM Worker
WHERE FIRST_NAME NOT IN ('Vipul', 'Satish');
15. Write an SQL query to print details of Workers with DEPARTMENT name as
"Admin".
SELECT *
FROM Worker
WHERE DEPARTMENT = 'Admin';
16. Write an SQL query to print details of the Workers whose FIRST_NAME contains 'a'.
SELECT *
FROM Worker
WHERE FIRST_NAME LIKE '%a%';
Reg No: 22BCE10897
Name: Pranjali Verma
17. Write an SQL query to print details of the Workers whose FIRST_NAME ends with
'a'.
SELECT *
FROM Worker
WHERE FIRST_NAME LIKE '%a';
18. Write an SQL query to print details of the Workers whose FIRST_NAME ends with
'h' and contains six alphabets.
SELECT *
FROM Worker
WHERE FIRST_NAME LIKE '_____h';
19. Write an SQL query to print details of the Workers whose SALARY lies between
100000 and 500000.
SELECT *
FROM Worker
WHERE SALARY BETWEEN 100000 AND 500000;
Reg No: 22BCE10897
Name: Pranjali Verma
20. Write an SQL query to print details of the Workers who have joined in Feb'2014.
SELECT *
FROM Worker
WHERE YEAR(JOINING_DATE) = 2014 AND MONTH(JOINING_DATE) = 2;
21. Write an SQL query to fetch the count of employees working in the department
'Admin'.
SELECT COUNT(*)
FROM Worker
WHERE DEPARTMENT = 'Admin';
22. Write an SQL query to fetch worker names with salaries >= 50000 and <= 100000.
SELECT FIRST_NAME, LAST_NAME
FROM Worker
WHERE SALARY >= 50000 AND SALARY <= 100000;
Reg No: 22BCE10897
Name: Pranjali Verma
23. Write an SQL query to fetch the no. of workers for each department in the
descending order.
SELECT DEPARTMENT, COUNT(WORKER_ID) AS NumberOfWorkers
FROM Worker
GROUP BY DEPARTMENT
ORDER BY NumberOfWorkers DESC;
24. Write an SQL query to print details of the Workers who are also Managers.
SELECT *
FROM Worker
WHERE TITLE = 'Manager';
25. Write an SQL query to fetch duplicate records having matching data in some fields
of a table.
SELECT FIRST_NAME, LAST_NAME, COUNT(*)
FROM Worker
GROUP BY FIRST_NAME, LAST_NAME
HAVING COUNT(*) > 1;
26. Write an SQL query to fetch duplicate records having matching data in some fields of a
table.
Reg No: 22BCE10897
Name: Pranjali Verma
SELECT FIRST_NAME, LAST_NAME, COUNT(*)
FROM Worker
GROUP BY FIRST_NAME, LAST_NAME
HAVING COUNT(*) > 1;
Output:
27. Write an SQL query to show only odd rows from a table.
SELECT *
FROM (
SELECT
@row_number:=@row_number + 1 AS row_num,
w.*
FROM
(SELECT @row_number:=0) AS init,
Worker w
) AS subquery
WHERE row_num MOD 2 = 1;
28. Write an SQL query to show only even rows from a table.
SELECT *
FROM (
SELECT
Reg No: 22BCE10897
Name: Pranjali Verma
@row_number:=@row_number + 1 AS row_num,
w.*
FROM
(SELECT @row_number:=0) AS init,
Worker w
) AS subquery
WHERE row_num MOD 2 = 0;
29. Write an SQL query to clone a new table from another table.
CREATE TABLE NewTable AS
SELECT * FROM OriginalTable;
30. Write an SQL query to fetch intersecting records of two tables.
SELECT Worker.WORKER_ID, Worker.FIRST_NAME, Worker.LAST_NAME
FROM Worker
INNER JOIN WorkerSubset
ON Worker.WORKER_ID = WorkerSubset.WORKER_ID
AND Worker.FIRST_NAME = WorkerSubset.FIRST_NAME
AND Worker.LAST_NAME = WorkerSubset.LAST_NAME;
Reg No: 22BCE10897
Name: Pranjali Verma
31.Write an SQL query to show records from one table that another table does not have.
CREATE TABLE WorkerSubset2 AS
SELECT WORKER_ID, FIRST_NAME, LAST_NAME
FROM Worker
WHERE WORKER_ID IN ('001', '002', '003'); -- Selecting only some workers
SELECT *
FROM Worker w
WHERE NOT EXISTS (
SELECT 1
FROM WorkerSubset2 ws
WHERE ws.WORKER_ID = w.WORKER_ID
AND ws.FIRST_NAME = w.FIRST_NAME
AND ws.LAST_NAME = w.LAST_NAME
);
32.Write an SQL query to show the current date and time.
SELECT CURRENT_TIMESTAMP;
Reg No: 22BCE10897
Name: Pranjali Verma
33.Write an SQL query to show the top n (say 10) records of a table.
SELECT * FROM Worker LIMIT 10;
34.Write an SQL query to determine the nth (say n=5) highest salary from a table.
SELECT DISTINCT SALARY
FROM Worker
ORDER BY SALARY DESC
LIMIT 1 OFFSET 4;
35.Write an SQL query to determine the 5th highest salary without using TOP or limit
method.
SELECT SALARY FROM Worker w1
WHERE 4 = (
SELECT COUNT(DISTINCT SALARY) FROM Worker w2 WHERE w2.SALARY > w1.SALARY
);
36.Write an SQL query to fetch the list of employees with the same salary.
Reg No: 22BCE10897
Name: Pranjali Verma
SELECT FIRST_NAME, SALARY
FROM Worker
WHERE SALARY IN (
SELECT SALARY FROM Worker GROUP BY SALARY HAVING COUNT(*) > 1
);
37.Write an SQL query to show the second highest salary from a table.
SELECT MAX(SALARY) FROM Worker
WHERE SALARY < (SELECT MAX(SALARY) FROM Worker);
38.Write an SQL query to show one row twice in results from a table.
SELECT * FROM Worker WHERE WORKER_ID = '001'
UNION ALL
SELECT * FROM Worker WHERE WORKER_ID = '001';
39.Write an SQL query to fetch intersecting records of two tables.
SELECT * FROM Worker
INTERSECT
SELECT * FROM WorkerSubset3;
Reg No: 22BCE10897
Name: Pranjali Verma
40.Write an SQL query to fetch the first 50% records from a table.
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY WORKER_ID) AS rn
FROM Worker
) AS t
WHERE rn <= (SELECT FLOOR(COUNT(*) / 2) FROM Worker)
ORDER BY WORKER_ID;
41.Write an SQL query to fetch the departments that have less than five people in it.
SELECT DEPARTMENT, COUNT(*) AS count_people
FROM Worker
GROUP BY DEPARTMENT
HAVING COUNT(*) < 5;
42.Write an SQL query to show all departments along with the number of people in there.
SELECT DEPARTMENT, COUNT(*) AS count_people
Reg No: 22BCE10897
Name: Pranjali Verma
FROM Worker
GROUP BY DEPARTMENT;
43.Write an SQL query to show the last record from a table.
SELECT * FROM Worker
ORDER BY JOINING_DATE DESC
LIMIT 1;
44.Write an SQL query to fetch the first row of a table.
SELECT * FROM Worker
ORDER BY JOINING_DATE ASC
LIMIT 1;
45.Write an SQL query to fetch the last five records from a table.
SELECT * FROM Worker
ORDER BY JOINING_DATE DESC
LIMIT 5;
Reg No: 22BCE10897
Name: Pranjali Verma
46.Write an SQL query to print the name of employees having the highest salary in each
department.
SELECT FIRST_NAME, DEPARTMENT, SALARY
FROM Worker w1
WHERE SALARY = (
SELECT MAX(SALARY) FROM Worker w2 WHERE w2.DEPARTMENT = w1.DEPARTMENT
);
47.Write an SQL query to fetch three max salaries from a table.
SELECT DISTINCT SALARY
FROM Worker
ORDER BY SALARY DESC
LIMIT 3;
48.Write an SQL query to fetch three min salaries from a table.
SELECT DISTINCT SALARY
FROM Worker
Reg No: 22BCE10897
Name: Pranjali Verma
ORDER BY SALARY ASC
LIMIT 3;
49.Write an SQL query to fetch nth max salaries from a table.
SELECT DISTINCT SALARY FROM Worker w1
WHERE 4 = (
SELECT COUNT(DISTINCT SALARY) FROM Worker w2 WHERE w2.SALARY > w1.SALARY
);
50.Write an SQL query to fetch departments along with the total salaries paid for each of
them.
SELECT DEPARTMENT, SUM(SALARY) AS total_salary
FROM Worker
GROUP BY DEPARTMENT;
51.Write an SQL query to fetch the names of workers who earn the highest salary
SELECT FIRST_NAME, SALARY
FROM Worker
WHERE SALARY = (SELECT MAX(SALARY) FROM Worker);
