--1) worker table

create table worker(worker_id number not null primary key ,first_name varchar(30),
last_name varchar(30),salary number, joining_date date, department varchar(10));

insert into worker values(001, 'Monika', 'Arora', 100000, '14feb2021', 'HR');

insert into worker values(002, 'Niharika', 'Verma', 80000, '14feb2021', 'Admin');
insert into worker values(003,'vishal','singhal',300000,'16feb2021','hr');
insert into worker values(004, 'Amitabh', 'Singh', 500000, '14feb2021 ', 'Admin');
insert into worker values(005, 'Vivek', 'Bhati', 500000, '14june2020 ', 'Admin');
insert into worker values(006, 'Vipul', 'Diwan', 200000, '14june2020 ', 'Account');
insert into worker values(007, 'Satish', 'Kumar', 75000, '14jan2019 ', 'Account');
insert into worker values(008, 'Geetika', 'Chauhan', 90000, '14apr2019 ', 'Admin');

--2)Bonus table

create table bonus(worker_ref_id number,bonus_amount number,bonus_date date,

foreign key (worker_ref_id) references worker(worker_id) on delete cascade);

insert into bonus values(001, 5000, '16feb2020');

insert into bonus values(002,300,'16jun2021');
insert into bonus values(003,4000,'16feb2020');
insert into bonus values(001,4500,'16feb2020');
insert into bonus values(002,3500,'16jun2021');

--3)Title table

create table title (worker_ref_id number, worker_title varchar(10),affected_from

date, foreign key(worker_ref_id) references worker(worker_id) on delete cascade);

insert into title values(001, 'Manager', '20feb2020');

insert into title values(002, 'Executive', '11jun2021');
insert into title values(008, 'Executive', '11jun2021');
insert into title values(005, 'Manager', '11jun2021');
insert into title values(004, 'A_Manager', '11jun2021');
insert into title values(007, 'Executive', '11jun2021');
insert into title values(006, 'Lead', '11jun2021');
insert into title values(003, 'Lead', '11jun2021');

--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 substr(first_name,1,3) from worker;

--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(first_name) from worker;

--8)Write an SQL query that fetches the unique values of DEPARTMENT from Worker
table and prints its length.

select distinct length(department) from worker;

----------------------or-----------------------

select distinct department, length(department) as 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;

------------------OR-------------------------

select first_name|| last_name " complete_name" from worker;

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

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

--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 like '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%';

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

--20)Write an SQL query to print details of the Workers who have joined in
Feb�2021.

Select * from Worker where JOINING_DATE = '14feb2021';

-----------------------------Or-----------------------
Select * from Worker where to_char(JOINING_DATE,'mon-yy') = 'feb-21';
--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 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);

--23)Write an SQL query to fetch the no. of workers for each department in the
descending order.

SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers

FROM worker
GROUP BY DEPARTMENT
ORDER BY No_Of_Workers DESC;

--24)Write an SQL query to print details of the Workers who are also Managers.

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

--25) Write an SQL query to fetch duplicate records having matching data in some
fields of a table.

SELECT WORKER_TITLE, AFFECTED_FROM, COUNT(*)

FROM Title
GROUP BY WORKER_TITLE, AFFECTED_FROM
HAVING COUNT(*) > 1;

--26)Write an SQL query to show only odd rows from a table.

SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) <> 0;

--27)Write an SQL query to show only even rows from a table.

SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) = 0;

--28)Write an SQL query to clone a new table from another table.

create table
workerclone(WORKER_ID,FIRST_NAME,LAST_NAME,SALARY,JOINING_DATE,DEPARTMENT) as
(select WORKER_ID,FIRST_NAME,LAST_NAME,SALARY,JOINING_DATE,DEPARTMENT from
worker);

---------------------
--29)Write an SQL query to fetch intersecting records of two tables.

(SELECT * FROM Worker)

INTERSECT
(SELECT * FROM WorkerClone);

--30)Write an SQL query to show records from one table that another table does not
have.

select worker_id,first_name from worker minus select worker_ref_id,worker_title

from title;

--31)Write an SQL query to show the current date and time.

SELECT SYSDATE FROM DUAL;

--32)Write an SQL query to show the top n (say 10) records of a table.

SELECT * FROM (SELECT * FROM Worker ORDER BY Salary DESC)

WHERE ROWNUM <= 10;

--33)Write an SQL query to determine the nth (say n=5) highest salary from a table.

select * from(
select FIRST_NAME, salary, dense_rank()
over(order by salary desc)r from worker)
where r=&n;

--34) Write an SQL query to determine the 5th highest salary without using TOP or
limit method.

SELECT salary FROM (

SELECT salary, row_number() OVER (order by salary desc) AS rn FROM worker
)
WHERE rn = 5;

---------------------------------OR------------------------
SELECT Salary
FROM Worker W1
WHERE 4 = (
SELECT COUNT( DISTINCT ( W2.Salary ) )
FROM Worker W2
WHERE W2.Salary >= W1.Salary
);

--35) Write an SQL query to fetch the list of employees with the same salary.

Select distinct W.WORKER_ID, W.FIRST_NAME, W.Salary

from Worker W, Worker W1
where W.Salary = W1.Salary
and W.WORKER_ID != W1.WORKER_ID;
--36) Write an SQL query to show the second highest salary from a table.

Select max(Salary) from Worker

where Salary not in (Select max(Salary) from Worker);

--37. Write an SQL query to show one row twice in results from a table.

select FIRST_NAME, DEPARTMENT from worker W where W.DEPARTMENT='HR'

union all
select FIRST_NAME, DEPARTMENT from Worker W1 where W1.DEPARTMENT='HR';

--38). Write an SQL query to fetch intersecting records of two tables.

(SELECT * FROM Worker)

INTERSECT
(SELECT * FROM WorkerClone);

--39). Write an SQL query to fetch the first 50% records from a table.

SELECT *
FROM WORKER
WHERE WORKER_ID <= (SELECT count(WORKER_ID)/2 from Worker);

--40)Write an SQL query to fetch the departments that have less than five people in
it.

SELECT DEPARTMENT, COUNT(WORKER_ID) as

FROM Worker GROUP BY DEPARTMENT HAVING COUNT(WORKER_ID) < 8;

--41. Write an SQL query to show all departments along with the number of people in
there.

SELECT DEPARTMENT, COUNT(DEPARTMENT) as FROM Worker GROUP BY DEPARTMENT;

--42) Write an SQL query to show the last record from a table.

Select * from Worker where WORKER_ID = (SELECT max(WORKER_ID) from Worker);

--43)Write an SQL query to fetch the first row of a table.

Select * from Worker where WORKER_ID = (SELECT min(WORKER_ID) from Worker);

--44)Write an SQL query to fetch the last five records from a table.

select * from (select * from worker order by worker_id desc) where rownum<=4;

--45) Write an SQL query to print the name of employees having the highest salary
in each department

SELECT DEPARTMENT, MAX(Salary) FROM worker GROUP BY DEPARTMENT;

--46) Write an SQL query to fetch three max salaries from a table.

SELECT distinct Salary from worker a WHERE 3 >= (SELECT count(distinct Salary) from
worker b WHERE a.Salary <= b.Salary) order by a.Salary desc;

--47)Write an SQL query to fetch three min salaries from a table.

SELECT distinct Salary from worker a WHERE 3 >= (SELECT count(distinct Salary) from
worker b WHERE a.Salary >= b.Salary) order by a.Salary desc;

--48) Write an SQL query to fetch nth max salaries from a table.

--not working
SELECT distinct Salary from worker a WHERE n >= (SELECT count(distinct Salary) from
worker b WHERE a.Salary <= b.Salary) order by a.Salary desc;

--anater solution--working

select * from(
select FIRST_NAME, salary, dense_rank()
over(order by salary desc)r from worker)
where r=&n;

--49. Write an SQL query to fetch departments along with the total salaries paid
for each of them.
SELECT DEPARTMENT, sum(Salary) from worker group by DEPARTMENT;

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

-- functions in
rdbms*************************************************************************88

select avg(salary) from worker;

select min(salary) from worker;

select max(salary) from worker;

select count(*) from worker;

select count(salary) from worker;

select sum(salary) from worker;

SELECT SYSDATE FROM DUAL;

select power(5,2) from dual;