Reg No: 22BCE10897

Name: Pranjali Verma

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;

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;

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

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.

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);