MySQL_Test.txt

CREATE DATABASE ORG123;
SHOW DATABASES;
USE ORG123;

show tables from ORG123;
CREATE TABLE Worker (
	WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	FIRST_NAME CHAR(25),
	LAST_NAME CHAR(25),
	SALARY INT(15),
	JOINING_DATE DATETIME,
	DEPARTMENT CHAR(25)
);

INSERT INTO Worker 
	(WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
		(001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR'),
		(002, 'Niharika', 'Verma', 80000, '14-06-11 09.00.00', 'Admin'),
		(003, 'Vishal', 'Singhal', 300000, '14-02-20 09.00.00', 'HR'),
		(004, 'Amitabh', 'Singh', 500000, '14-02-20 09.00.00', 'Admin'),
		(005, 'Vivek', 'Bhati', 500000, '14-06-11 09.00.00', 'Admin'),
		(006, 'Vipul', 'Diwan', 200000, '14-06-11 09.00.00', 'Account'),
		(007, 'Satish', 'Kumar', 75000, '14-01-20 09.00.00', 'Account'),
		(008, 'Geetika', 'Chauhan', 90000, '14-04-11 09.00.00', 'Admin');

CREATE TABLE Bonus (
	WORKER_REF_ID INT,
	BONUS_AMOUNT INT(10),
	BONUS_DATE DATETIME,
	FOREIGN KEY (WORKER_REF_ID)
		REFERENCES Worker(WORKER_ID)
        ON DELETE CASCADE
);

INSERT INTO Bonus 
	(WORKER_REF_ID, BONUS_AMOUNT, BONUS_DATE) VALUES
		(001, 5000, '16-02-20'),
		(002, 3000, '16-06-11'),
		(003, 4000, '16-02-20'),
		(001, 4500, '16-02-20'),
		(002, 3500, '16-06-11');
CREATE TABLE Title (
	WORKER_REF_ID INT,
	WORKER_TITLE CHAR(25),
	AFFECTED_FROM DATETIME,
	FOREIGN KEY (WORKER_REF_ID)
		REFERENCES Worker(WORKER_ID)
        ON DELETE CASCADE
);

INSERT INTO Title 
	(WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES
 (001, 'Manager', '2016-02-20 00:00:00'),
 (002, 'Executive', '2016-06-11 00:00:00'),
 (008, 'Executive', '2016-06-11 00:00:00'),
 (005, 'Manager', '2016-06-11 00:00:00'),
 (004, 'Asst. Manager', '2016-06-11 00:00:00'),
 (007, 'Executive', '2016-06-11 00:00:00'),
 (006, 'Lead', '2016-06-11 00:00:00'),
 (003, 'Lead', '2016-06-11 00:00:00');
 
 select * from bonus;
 select * from title;
 select * from worker;
 
#1. Write an SQL query to fetch unique values of DEPARTMENT from 
select distinct DEPARTMENT 
from Worker;

#2. 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;

#3. Write an SQL query to print details of the Workers whose FIRST_NAME contains ‘a’
select * 
from Worker 
where FIRST_NAME LIKE '%a%';

#4. 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';

#5. 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;

#6. Write an SQL query to print details of the Workers who have joined in Feb’2014.
select *  
from Worker 
where JOINING_DATE BETWEEN '2014-02-01' AND '2014-02-28';

#7. Write an SQL query to fetch the count of employees working in the department ‘Admin’
select COUNT(*) AS employee_count 
from Worker 
where DEPARTMENT = 'Admin';

#8. Write an SQL query to fetch worker names with salaries >= 50000 and <= 100000.
select FIRST_NAME, LAST_NAME 
from Worker 
where SALARY BETWEEN 50000 AND 100000;

#9. Write an SQL query to fetch the no. of workers for each department in the descending order
select DEPARTMENT, COUNT(*) AS number_of_worker 
from Worker 
group by DEPARTMENT 
order by number_of_worker DESC;

#10. Write an SQL query to print details of the Workers who are also Managers
select * 
from Worker 
where WORKER_ID IN (
    select WORKER_REF_ID 
    from Title 
    where WORKER_TITLE = 'Manager'
);

#11. Write an SQL query to determine the 2nd lowest salary without using TOP or limit method.
select MIN(SALARY) AS SecondLowestSalary 
from Worker 
where SALARY > (select MIN(SALARY) from Worker);


#12. Write an SQL query to fetch the list of employees with the same salary
select FIRST_NAME, LAST_NAME, SALARY 
from Worker 
where SALARY IN (
    select SALARY 
    from Worker 
    group by SALARY 
    having COUNT(*) > 1
);

#13. Write an SQL query to show the second highest salary from a table
select MAX(SALARY) AS SecondHighestSalary
from Worker
where SALARY < (select MAX(SALARY) from Worker);

#14. Write an SQL query to show one row twice in results from a table.
select * from Worker 
UNION ALL 
select * from Worker;

#15. Write an SQL query to fetch the first 50% records from a table.
select * 
from Worker
where WORKER_ID <= (
    select COUNT(*) / 2 
    from Worker
);


#16. Write an SQL query to fetch the departments that have less than three people in it.
select DEPARTMENT
from Worker
group by DEPARTMENT
having COUNT(*) < 3;

#17. Write an SQL query to show all departments along with the number of people in there.
select DEPARTMENT, COUNT(*) AS Department_count
from Worker
group by DEPARTMENT;

#18. Write an SQL query to fetch the last five records from a table
select * 
from Worker 
order by WORKER_ID DESC 
limit 5;

#19. Write an SQL query to print the name of employees having the highest salary in each department
select w.FIRST_NAME, w.DEPARTMENT, w.SALARY
from Worker w
where SALARY = (
    select MAX(SALARY) 
    from Worker 
    where DEPARTMENT = w.DEPARTMENT
);

#20. Write an SQL query to fetch three max salaries from a table
select distinct SALARY 
from Worker 
order by SALARY DESC 
limit 3;

#21. Write an SQL query to print the name of employees having the lowest salary in account and admin department
select FIRST_NAME, DEPARTMENT, SALARY
from Worker w
where DEPARTMENT IN ('Account', 'Admin')
  and SALARY = (
    select MIN(SALARY)
    from Worker
    where DEPARTMENT = w.DEPARTMENT
  );