SQL one table 30 questions

SQL is your bread and butter. Let’s take a table and try to answer few questions ranging from very basic to intermediate level.

Emp Table – Employees at Olivers’ Restro Pub

Dept Table

a. Show all the data from emp table
SELECT * FROM emp;

b. Show all the data from Dept table
SELECT * FROM dept;

c. Display distinct jobs from Dept table
SELECT DISTINCT(job) FROM dept;

d. Number of employees
SELECT COUNT(*) FROM emp;

e. List the employee in the ascending order of salary
SELECT * from emp ORDER BY Sal;

f. Show the employee information of the Managers
SELECT * from emp WHERE EmpNo in (SELECT MGR FROM emp);

g. List of employees who were hired before 2018.
SELECT * FROM emp WHERE HireDate < ’01-Jan-2018′;

h. List the detail of employees along with the annual salary, order it on the annual salary
SELECT *, sal*12 as Annual_Income
FROM emp
ORDER BY Annual_Income;

i. Display number of months of experience of all the Managers
SELECT *, months_between(sysdate,HireDate) as Exp
FROM emp
WHERE EmpNo IN (SELECT MGR FROM emp);

j. Display the name of the employees with Commission(Comm) less than Salary (Sal)
SELECT EName FROM emp WHERE Comm < Sal;

k. Display the name of the employee with Daily income more than 200
SELECT EName FROM emp WHERE (sal/30)>200;

l. Show information of all the Waiters
SELECT * FROM emp WHERE Job = ‘Waiter’;

m. Show all the employee who joined on 01-Aug-2018, 4-Aug-2018, 29-Oct-2018 in descending order of Hire Date
SELECT * FROM emp WHERE HireDate IN (’01-Aug-2018′,’04-Aug-2018′,’29-OCt-2018′)
ORDER BY HireDate DESC;

n. List the employees who joined in 2018
SELECT * FROM emp WHERE HireDate BETWEEN (’01-Jan-2018′) AND (’31-Dec-2018′);

o. Employees with Annual Salary between 600000 and 1000000
SELECT * FROM emp WHERE Sal*12 BETWEEN 600000 AND 1000000;

p. List the employees with name starting with N and containing 5 alphabets
SELECT * FROM emp WHERE EName LIKE ‘N____’;

Or

SELECT * FROM emp WHERE EName LIKE ‘N%’ AND len(EName) = 5;

q. List the employee with the third alphabet in their name as K
SELECT * FROM emp WHERE Upper(EName) LIKE ‘__K%’;

r. Show the name of the employees who joined in August month of any year.
SELECT * FROM emp WHERE to_char(HireDate,’mon’)=’Aug’;

s. Show the employee details of those who were hired in the 90s
SELECT * FROM emp WHERE to_char(HireDate, ‘yy’) LIKE ‘9_’;

t. Show the employee who were not hired in the month of October.
SELECT * FROM emp WHERE to_char(HireDate,’MON’) NOT IN ‘(‘Oct’);

u. List the total information of the employees along with DName and Location of people working under ‘Accounts’
SELECT *
FROM emp e
INNER JOIN dept d ON (e.DeptNo = d.DeptNo)
WHERE d.DName = ‘Account’

v. List all the employees with more than 10 years of experience as of now
SELECT *
FROM emp
WHERE TIMESTAMPDIFF(MONTH, HireDate, sysdate)

w. List the detail of all the employees whose salary is less than that of Aman

SELECT *
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE EName = ‘Aman’);

x. Show the name of those employees who are senior to their own Manager.
SELECT * FROM emp w, emp m
WHERE w.MGR = m.EmpNo and w.HireDate < m.HireDate

Or

SELECT * FROM emp w, emp m
WHERE w.EmpNo = m.MGR and w.HireDate < m.HireDate

y. Show the employees who are senior to Aman
SELECT * FROM emp WHERE HireDate < (SELECT HireDate FROM emp WHERE EName = ‘Aman’)

z. Show the employees who are senior to Aman and are working in Delhi or Bangalore

SELECT * FROM emp e, dept d WHERE
UPPER(d.loc) IN (‘DELHI,’BANGALORE’) AND e.DeptNo = d.DeptNo
AND e.HireDate < (SELECT e.HireDate FROM emp e WHERE EName = ‘Aman’);

za. Show the employees with the same job as Aman or Amit.
SELECT * FROM emp
WHERE job in (SELECT job from emp WHERE EName IN (‘Aman’,’Amit’);

zb. Find the highest salary of any employee
SELECT MAX(Sal)
FROM emp;

zc. Find the detail of the employee with the minimum pay
SELECT * FROM emp WHERE Salary = (SELECT MIN(Salary) FROM emp);

zd. Show the detail of the recently hired employee working in Delhi

I am tired, comment the answer below

Keep Practicing.

XtraMous