Initial Setup
Code to create and populate the database and tables:
-- Create database and use it
CREATE DATABASE ORG;
SHOW DATABASES;
USE ORG;
-- Create Worker table
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 data into Worker table
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 Bonus table
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 data into Bonus table
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 Title table
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 data into Title table
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');
-- Just to confirm Title table data
SELECT * FROM Title;
Queries and Answers
Q-1:
❓ Question: Write an SQL query to fetch FIRST_NAME from Worker table using the alias name as WORKER_NAME.
💻 Code:
SELECT FIRST_NAME AS WORKER_NAME FROM Worker;
Q-2:
❓ Question: Write an SQL query to fetch FIRST_NAME from Worker table in upper case.
💻 Code:
SELECT UPPER(FIRST_NAME) FROM Worker;
Q-3:
❓ Question: Write an SQL query to fetch unique values of DEPARTMENT from Worker table.
💻 Code:
SELECT DISTINCT DEPARTMENT FROM Worker;
Q-4:
❓ Question: Write an SQL query to print the first three characters of FIRST_NAME from Worker table.
💻 Code:
SELECT SUBSTRING(FIRST_NAME, 1, 3) FROM Worker;
Q-5:
❓ Question: Write an SQL query to find the position of the alphabet (‘b’) in the first name column Amitabh from Worker table.
💻 Code:
SELECT INSTR(FIRST_NAME, 'B') FROM Worker WHERE FIRST_NAME = 'Amitabh';
Q-6:
❓ Question: Write an SQL query to print the FIRST_NAME from Worker table after removing white spaces from the right side.
💻 Code:
SELECT RTRIM(FIRST_NAME) FROM Worker;
Q-7:
❓ Question: Write an SQL query to print the DEPARTMENT from Worker table after removing white spaces from the left side.
💻 Code:
SELECT LTRIM(FIRST_NAME) FROM Worker;
Q-8:
❓ Question: Write an SQL query that fetches the unique values of DEPARTMENT from Worker table and prints its length.
💻 Code:
SELECT DISTINCT DEPARTMENT, LENGTH(DEPARTMENT) FROM Worker;
Q-9:
❓ Question: Write an SQL query to print the FIRST_NAME from Worker table after replacing ‘a’ with ‘A’.
💻 Code:
SELECT REPLACE(FIRST_NAME, 'a', 'A') FROM Worker;
Q-10:
❓ Question: Write an SQL query to print the FIRST_NAME and LAST_NAME from Worker table into a single column COMPLETE_NAME separated by a space.
💻 Code:
SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS COMPLETE_NAME FROM Worker;
Q-11:
❓ Question: Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending.
💻 Code:
SELECT * FROM Worker ORDER BY FIRST_NAME;
Q-12:
❓ Question: Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending and DEPARTMENT Descending.
💻 Code:
SELECT * FROM Worker ORDER BY FIRST_NAME, DEPARTMENT DESC;
Q-13:
❓ Question: Write an SQL query to print details for Workers with the first name as “Vipul” and “Satish”.
💻 Code:
SELECT * FROM Worker WHERE FIRST_NAME IN ('Vipul', 'Satish');
Q-14:
❓ Question: Write an SQL query to print details of Workers excluding first names “Vipul” and “Satish”.
💻 Code:
SELECT * FROM Worker WHERE FIRST_NAME NOT IN ('Vipul', 'Satish');
Q-15:
❓ Question: Write an SQL query to print details of Workers with DEPARTMENT name as “Admin*”.
💻 Code:
SELECT * FROM Worker WHERE DEPARTMENT LIKE 'Admin%';
Q-16:
❓ Question: Write an SQL query to print details of the Workers whose FIRST_NAME contains ‘a’.
💻 Code:
SELECT * FROM Worker WHERE FIRST_NAME LIKE '%a%';
Q-17:
❓ Question: Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘a’.
💻 Code:
SELECT * FROM Worker WHERE FIRST_NAME LIKE '%a';
Q-18:
❓ Question: Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘h’ and contains six alphabets.
💻 Code:
SELECT * FROM Worker WHERE FIRST_NAME LIKE '_____h';
Q-19:
❓ Question: Write an SQL query to print details of the Workers whose SALARY lies between 100000 and 500000.
💻 Code:
SELECT * FROM Worker WHERE SALARY BETWEEN 100000 AND 500000;
Q-20:
❓ Question: Write an SQL query to print details of the Workers who have joined in Feb’2014.
💻 Code:
SELECT * FROM Worker
WHERE YEAR(JOINING_DATE) = 2014 AND MONTH(JOINING_DATE) = 2;
Q-21:
❓ Question: Write an SQL query to fetch the count of employees working in the department ‘Admin’.
💻 Code:
SELECT DEPARTMENT, COUNT(*) FROM Worker WHERE DEPARTMENT = 'Admin';
Q-22:
❓ Question: Write an SQL query to fetch worker full names with salaries between 50000 and 100000.
💻 Code:
SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME)
FROM Worker
WHERE SALARY BETWEEN 50000 AND 100000;
Q-23:
❓ Question: Write an SQL query to fetch the number of workers for each department in the descending order.
💻 Code:
SELECT DEPARTMENT, COUNT(WORKER_ID) AS no_of_worker
FROM Worker
GROUP BY DEPARTMENT
ORDER BY no_of_worker DESC;
Q-24:
❓ Question: Write an SQL query to print details of the Workers who are also Managers.
💻 Code:
SELECT w.* FROM Worker AS w
INNER JOIN Title AS t ON w.WORKER_ID = t.WORKER_REF_ID
WHERE t.WORKER_TITLE = 'Manager';
Q-25:
❓ Question: Write an SQL query to fetch number (more than 1) of same titles in the ORG of different types.
💻 Code:
SELECT WORKER_TITLE, COUNT(*) AS count
FROM Title
GROUP BY WORKER_TITLE
HAVING COUNT(*) > 1;
Q-26:
❓ Question: Write an SQL query to show only odd rows from a table (based on WORKER_ID).
💻 Code:
SELECT * FROM Worker WHERE MOD(WORKER_ID, 2) <> 0;
Q-27:
❓ Question: Write an SQL query to show only even rows from a table (based on WORKER_ID).
💻 Code:
SELECT * FROM Worker WHERE MOD(WORKER_ID, 2) = 0;
Q-28:
❓ Question: Write an SQL query to clone a new table from another table.
💻 Code:
CREATE TABLE worker_clone LIKE Worker;
INSERT INTO worker_clone SELECT * FROM Worker;
SELECT * FROM worker_clone;
Q-29:
❓ Question: Write an SQL query to fetch intersecting records of two tables (Worker and worker_clone).
💻 Code:
SELECT Worker.* FROM Worker
INNER JOIN worker_clone USING(WORKER_ID);
Q-30:
❓ Question: Write an SQL query to show records from one table that another table does not have (Minus operation).
💻 Code:
SELECT Worker.*
FROM Worker
LEFT JOIN worker_clone USING(WORKER_ID)
WHERE worker_clone.WORKER_ID IS NULL;
Q-31:
❓ Question: Write an SQL query to show the current date and time.
💻 Code:
SELECT CURDATE();
SELECT NOW();
Q-32:
❓ Question: Write an SQL query to show the top n (say 5) records of a table order by descending salary.
💻 Code:
SELECT * FROM Worker ORDER BY SALARY DESC LIMIT 5;
Q-33:
❓ Question: Write an SQL query to determine the 5th highest salary from a table using LIMIT.
💻 Code:
SELECT * FROM Worker ORDER BY SALARY DESC LIMIT 4,1;
Q-34:
❓ Question: Write an SQL query to determine the 5th highest salary without using LIMIT keyword.
💻 Code:
SELECT SALARY FROM Worker w1
WHERE 4 = (
SELECT COUNT(DISTINCT (w2.SALARY))
FROM Worker w2
WHERE w2.SALARY >= w1.SALARY
);
Q-35:
❓ Question: Write an SQL query to fetch the list of employees with the same salary.
💻 Code:
SELECT w1.*
FROM Worker w1, Worker w2
WHERE w1.SALARY = w2.SALARY
AND w1.WORKER_ID != w2.WORKER_ID;
Q-36:
❓ Question: Write an SQL query to show the second highest salary from a table using sub-query.
💻 Code:
SELECT MAX(SALARY)
FROM Worker
WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM Worker);
Q-37:
❓ Question: Write an SQL query to show one row twice in results from a table.
💻 Code:
SELECT * FROM Worker
UNION ALL
SELECT * FROM Worker
ORDER BY WORKER_ID;
Q-38:
❓ Question: Write an SQL query to list WORKER_ID who does not get bonus.
💻 Code:
SELECT WORKER_ID
FROM Worker
WHERE WORKER_ID NOT IN (SELECT WORKER_REF_ID FROM Bonus);
Q-39:
❓ Question: Write an SQL query to fetch the first 50% records from a table.
💻 Code:
SELECT * FROM Worker
WHERE WORKER_ID <= (SELECT COUNT(WORKER_ID)/2 FROM Worker);
Q-40:
❓ Question: Write an SQL query to fetch the departments that have less than 4 people in it.
💻 Code:
SELECT DEPARTMENT, COUNT(DEPARTMENT) AS depCount
FROM Worker
GROUP BY DEPARTMENT
HAVING depCount < 4;
Q-41:
❓ Question: Write an SQL query to show all departments along with the number of people in them.
💻 Code:
SELECT DEPARTMENT, COUNT(DEPARTMENT) AS depCount
FROM Worker
GROUP BY DEPARTMENT;
Q-42:
❓ Question: Write an SQL query to show the last record from a table.
💻 Code:
SELECT * FROM Worker
WHERE WORKER_ID = (SELECT MAX(WORKER_ID) FROM Worker);
Q-43:
❓ Question: Write an SQL query to fetch the first row of a table.
💻 Code:
SELECT * FROM Worker
WHERE WORKER_ID = (SELECT MIN(WORKER_ID) FROM Worker);
Q-44:
❓ Question: Write an SQL query to fetch the last five records from a table.
💻 Code:
(SELECT * FROM Worker ORDER BY WORKER_ID DESC LIMIT 5) ORDER BY WORKER_ID;
Q-45:
❓ Question: Write an SQL query to print the name of employees having the highest salary in each department.
💻 Code:
SELECT w.DEPARTMENT, w.FIRST_NAME, w.SALARY
FROM (SELECT MAX(SALARY) AS maxsal, DEPARTMENT FROM Worker GROUP BY DEPARTMENT) temp
INNER JOIN Worker w ON temp.DEPARTMENT = w.DEPARTMENT AND temp.maxsal = w.SALARY;
Q-46:
❓ Question: Write an SQL query to fetch three max salaries from a table using a co-related subquery.
💻 Code:
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;
(Alternatively, using LIMIT:)
SELECT DISTINCT SALARY FROM Worker ORDER BY SALARY DESC LIMIT 3;
Q-47:
❓ Question: Write an SQL query to fetch three min salaries from a table using a co-related subquery.
💻 Code:
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;
Q-48:
❓ Question: Write an SQL query to fetch nth max salaries from a table.
💻 Code:
(Here n is a placeholder. Replace n with the desired rank, e.g. n=3 for 3rd max salary.)
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:
❓ Question: Write an SQL query to fetch departments along with the total salaries paid for each of them.
💻 Code:
SELECT DEPARTMENT, SUM(SALARY) AS depSal
FROM Worker
GROUP BY DEPARTMENT
ORDER BY depSal DESC;
Q-50:
❓ Question: Write an SQL query to fetch the names of workers who earn the highest salary.
💻 Code:
SELECT FIRST_NAME, SALARY
FROM Worker
WHERE SALARY = (SELECT MAX(SALARY) FROM Worker);