SQL is your bread and butter. Let’s take a table and try to answer
Emp Table – Employees at Olivers’ Restro Pub
| EmpNo | EName | Job | MGR | HireDate | Sal | Comm | DeptNo |
| 1234 | Amit | Waiter | 8382 | 19-Oct-18 | 50000 | 500 | 50 |
| 5678 | Ashish | Analyst | 8635 | 2-Nov-18 | 60000 | 200 | 51 |
Dept Table
| DeptNo | Dname | Loc |
| 50 | Service | Delhi |
| 51 | Account | Mumbai |
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
