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

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

select * from Worker;

-- Q-1. Write an SQL query to fetch “FIRST_NAME” from Worker table using the alias name as <WORKER_NAME>.
-- select FIRST_NAME from Worker as WORKER_NAME;

-- Q-2. Write an SQL query to fetch “FIRST_NAME” from Worker table in upper case.
-- select upper(FIRST_NAME) from Worker as WORKER_NAME;



-- Q-3. Write an SQL query to fetch unique values of DEPARTMENT from Worker table.
-- select distinct(DEPARTMENT) from Worker;

-- Q-4. Write an SQL query to print the first three characters of  FIRST_NAME from Worker table.
-- select substring(FIRST_NAME,1,3) as "name" from Worker;

-- Q-5. Write an SQL query to find the position of the alphabet (‘b’) in the first name column ‘Amitabh’ from Worker table.
-- select instr(FIRST_NAME,'b') as "pos" from Worker where FIRST_NAME='Amitabh';
-- Q-6. Write an SQL query to print the FIRST_NAME from Worker table after removing white spaces from the right side.

-- Q-7. Write an SQL query to print the DEPARTMENT from Worker table after removing white spaces from the left side.

-- Q-8. Write an SQL query that fetches the unique values of DEPARTMENT from Worker table and prints its length.
-- select distinct(DEPARTMENT),length(DEPARTMENT) as "length" from Worker;
-- Q-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;
-- Q-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;

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

-- Q-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 and DEPARTMENT desc;

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

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

-- Q-15. Write an SQL query to print details of Workers with DEPARTMENT name as “Admin*”.
-- select * from Worker where DEPARTMENT='Admin';

-- Q-16. Write an SQL query to print details of the Workers whose FIRST_NAME contains ‘a’.
-- select * from Worker where FIRST_NAME like '%a%';

-- Q-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';
-- Q-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';
-- Q-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;
-- Q-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)=02 ;

-- Q-21. Write an SQL query to fetch the count of employees working in the department ‘Admin’.
-- select count(department) as "employees" from Worker where department='Admin';

-- Q-22. Write an SQL query to fetch worker full names with salaries >= 50000 and <= 100000.
select concat(FIRST_NAME,' 'LAST_NAME) as "full names" from Worker where salaries>=50000 and <=100000;

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


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

-- Q-25. Write an SQL query to fetch number (more than 1) of same titles in the ORG of different types.

-- Q-26. Write an SQL query to show only odd rows from a table.
-- select * from worker where MOD (WORKER_ID, 2) != 0; 

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

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


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

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

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


-- Q-32. Write an SQL query to show the top n (say 5) records of a table order by descending salary.

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

-- Q-34. Write an SQL query to determine the 5th highest salary without using LIMIT keyword.

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

-- Q-36. Write an SQL query to show the second highest salary from a table using sub-query.


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


-- Q-38. Write an SQL query to list worker_id who does not get bonus.

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

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