Top 50 Interview Questions of sql

Use these command to create these tables and insertion operation
CREATE DATABASE ORG;
SHOW DATABASES;
USE ORG;

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');
        
SELECT * FROM WORKER;

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,'Assit. 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');


We have these tables
Worker Table



Bonus Table



Title Table


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

-- Q-2 Write an SQL Query to fetch first_name from worker table in upper case
select upper(first_name) from worker; 

-- Q-3 Write an SQL query to fetch unique values of department from worker table
select distinct department from worker;
select department from worker group by department;

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

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

-- Q-7 Write an sql query to print the First_name from worker table after removing white spaces from the left side
SELECT LTRIM(first_name) from worker;


-- Q-8 Write an SQL Query that fetches the unique value of departent from worker table and prints its length
SELECT DISTINCT(department), length(department) 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 into a single column complete
-- A space char should seperate the
select concat(first_name, ' ', last_name) as Name from worker;

-- Q-11 Write an sql query to print all workerdetails from 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 decending
select * from worker order by first_name, 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 for workers excluding first name as "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 like 'Admin%';

-- Q-16 Write an sql query to print details of 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 workers whose first name end with h and conatins 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 wokrers 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 employeses working in the department 'Admin'
select department,count(*) from worker where department='Admin';
select count(*) from worker group by department having department='Admin';

-- Q-22 Write an sql query to fetch worker full name with salaries>=50000 and <=100000
select concat(first_name, ' ', last_name) as Name from worker where salary>=50000 and salary<=100000;
select concat(first_name, ' ', last_name) as Name from worker where salary between 50000 and 100000;


-- Q-23 Write an sql query to fetch the no. of workers for each department in descending order.
select department,count(*) as Number_of_workers from worker group by department order by count(*) desc;

-- Q-24 Write an sql query to print details of workers who are also manager.
select worker.* from worker,title where title.worker_title='manager' and worker.worker_id=title.worker_ref_id;
select worker.* from worker inner join title on worker.worker_id=title.worker_ref_id and title.worker_title='manager';
select * from worker as w inner join title as t on w.worker_id=t.worker_ref_id and t.worker_title='manager';

-- Q-25 Write an sql query to fetch number (more than 1) of different titles in org.
select worker_title,count(*)as count from title group by worker_title having count(*)>1;

-- 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 query to show only even rows from table
select * from worker where MOD(worker_id,2)=0;

-- Q-28 Write an query to clone a new table from another table
create table worker_clone like worker;
insert into worker_clone select * from worker;


-- Q-29  Write an SQL Query to fetch intersecting records of two tables.
select * from worker inner join worker_clone using(worker_id);

-- Q-30 Write an SQL query to show reconrds from one table that another table does not have.
-- MINUS
select worker.* from worker left join worker_clone using(worker_id) where worker_clone.worker_id is NULL;

-- Q-31 Write an SQL query to show the current date and time
select curdate();
select now();

-- Q-32 Write an sql query to show the top n (say 5) records of a table order by descending salary
select * from worker order by salary desc LIMIT 5;

-- Q-33 Write an sql query to determine the nth (say n=5) highest salary from a table.
select * from worker order by salary desc LIMIT 4,1;

-- Q-34 Write an sql query to determine the 5th highest salary without using LIMIT keyword
select * from worker as w1 where 5=(select count(distinct(w2.salary)) from worker as w2 where w2.salary>=w1.salary);

-- Q-35 Write an sql query to fetch the details of employees with the same salary
select w1.* from worker as w1,worker as w2 where w1.salary=w2.salary and w1.worker_id!=w2.worker_id;

-- Q-36 Write an sql query to show the second highest salary from table
select max(salary) from worker where salary not in (select max(salary) from worker);


-- Q-37 Write an sql query to show one row twice in results from table
select * from worker
union all
select * from worker;

-- Q-38 Write an sql query to list worker_id who does not get bonus
select worker_id from worker where worker_id not in (select worker_ref_id from bonus);

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

-- Q-40 Write an sql query to fetch the departments that have less than 4 people
select department,count(department) from worker group by department having count(*)<4;

-- Q-41 Write an sql query to show all departments along with the number of people in there.
select department, count(*) as No_of_People from worker group by department;

-- Q-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);
select * from worker where worker_id=(select count(*) from worker);


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


-- Q-44 Write an sql query to fetch the last five records from a table
(select * from worker order by worker_id desc limit 5) order by worker_id;

-- Q-45 Write an sql query to write the name of employee having the highest salary in each department
select w.department, w.first_name, w.salary from 
(select department,max(salary) as maxsal from worker group by department) as temp
inner join worker w on temp.department = w.department and temp.maxsal=w.salary;

-- Q-46 Write an sql query to fetch three max salaries from a table using corelated subquery
select distinct(salary) from worker w1
where 3>=(select count(distinct(salary)) from worker w2 where w1.salary<= w2.salary) order by w1.salary desc;
-- By Limit
select distinct(salary) from worker order by salary desc limit 3;

-- Q.47 Write an sql query to fetch three min salaries from the table using corelated subquery
select distinct(salary) from worker w1
where 3>=(select count(distinct(salary)) from worker w2 where w1.salary>=w2.salary) order by w1.salary desc;

-- by Limit
(select distinct(salary) from worker order by salary limit 3)order by salary desc;

-- Q-48 Write an sql query to fetch nth maximum salary from a table
select distinct(salary) from worker w1
where n>=(select count(distinct(salary)) from worker w2 where w1.salary<= w2.salary) order by w1.salary desc;

-- Q-49 Write an SQL query to fetch departments with the total salaries paid for each of them
select department, sum(salary) as depsal from worker group by department;

-- Q-50 Write an SQL query to fetch the name of workers who earn the highest salary
select first_name,salary from worker where salary=(select max(salary) from worker);

create table pair(
A int,
B int);

insert into pair values(1,2),(2,4),(2,1),(3,2),(4,2),(5,6),(6,5),(7,8);

select * from pair;

-- remove reversed pairs
-- Method 1: Joins
select lt.* from pair lt Left join pair rt on lt.A=rt.B and lt.b=rt.A
where rt.A is null or lt.Ap2.A);