SQL Practice Questions – II

1. Display the names of the employees who earn highest salary in their respective departments.
SELECT ENAME
FROM EMP E
WHERE SAL = (SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = E.DEPTNO);
2. Display the names of employees who earn the highest salary in their respective job groups.
SELECT ENAME
FROM EMP E
WHERE SAL IN (SELECT MAX(SAL)
FROM EMP
GROUP BY JOB
HAVING E.JOB = JOB);
or,
SELECT ENAME
FROM EMP E
WHERE SAL IN (SELECT MAX(SAL)
FROM EMP
WHERE JOB = E.JOB
GROUP BY JOB);
3. Display the employee names who are working in accounting dept.
SELECT ENAME
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE DNAME = ‘ACCOUNTING’);
4. Display the employees names who are working in Chicago.
SELECT ENAME
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘CHICAGO’);
5. Display the job groups having total salary greater than the maximum salary for managers.
SELECT JOB, SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING SUM(SAL) > (SELECT MAX(SAL)
FROM EMP
www.linkedin.com/in/anjanakuiri
 WHERE JOB='MANAGER');
6. Display the names of employees from department number 10 with a salary greater than that of
any employee working in other departments.
SELECT ENAME
FROM EMP
WHERE DEPTNO = 10
AND SAL > ANY(SELECT SAL
FROM EMP
WHERE DEPTNO <> 10);
7. Display the names of employees from department number 10 with salary greater than that of all
employees working in other departments.
SELECT ENAME
FROM EMP
WHERE DEPTNO = 10
AND SAL > ALL(SELECT SAL
FROM EMP
WHERE DEPTNO != 10);
8. Display the names of employees in Upper case.
SELECT UPPER(ENAME) EMP_NAME
FROM EMP;
9. Display the names of employees in lower case.
SELECT LOWER(ENAME) EMP_NAME
FROM EMP;
10. Display the name of employees in proper case.
SELECT INITCAP(ENAME) EMP_NAME
FROM EMP;
or,
SELECT CONCAT(UCASE(LEFT(ENAME, 1)), LCASE(SUBSTRING(ENAME, 2))) EMP_NAME
FROM EMP;
11. Find out the length of your name using appropriate function.
SELECT LENGTH(‘INDIA’)
FROM DUAL;
12. Display the length of all employees?
SELECT ENAME, LENGTH(ENAME) NAME_LENGTH
FROM EMP;

13. Display the name of the employee concatenate with EMP no.
SELECT ENAME || ' - ' || EMPNO EMP_DETAILS
FROM EMP;
or,
SELECT CONCAT(ENAME, ' - ', EMPNO) EMP_DETAILS
FROM EMP;
www.linkedin.com/in/anjanakuiri
 or,
SELECT CONCAT(ENAME,EMPNO) EMP_DETAILS
FROM EMP;
14. Use an appropriate function to extract 3 characters starting from the 2nd character from the
string ‘Oracle’. The expected output is ‘rac’.
SELECT SUBSTR(‘Oracle’, 2, 3) RESULT
FROM DUAL;
or,
SELECT SUBSTRING(‘Oracle’, 2, 3) RESULT
FROM DUAL;
15. Find the first occurrence of character a from the following string ‘computer maintenance
corporation’.
SELECT INSTR(‘computer maintenance corporation’, ‘a’,1,1) POSITION
FROM DUAL;
or,
SELECT INSTR('computer maintenance corporation', 'a') POSITION
FROM DUAL;
16. Use the TRANSLATE function to replace every occurrence of the letter 'A' with 'B' in the string
'Allen's'.
SELECT TRANSLATE('Allen''s', 'A', 'B') MODIFIED_TEXT
FROM DUAL;
17. Display the information from EMP table. Wherever job ‘manager’ is found it should be displayed
as boss (replace function).
SELECT EMPNO, ENAME, REPLACE(JOB, 'MANAGER', 'BOSS') JOB
FROM EMP;

18. Display empno, ename, deptno from EMP table. Instead of display department numbers display
the related department name.
SELECT e.empno, e.ename, d.dname
FROM emp e
INNER JOIN dept d
ON e.deptno = d.deptno;

19. Display your age in days.

SELECT ROUND(SYSDATE - TO_DATE('15-AUG-1947'))
FROM DUAL;

20. Display your age in months.

SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,'15-AUG-1947')) AGE_IN_MONTHS
FROM DUAL;

21. Display current date as 15th August friday nineteen forty seven.
SELECT TO_CHAR(SYSDATE,'DDTH MONTH DAY YEAR') FORMATTED_DATE
FROM DUAL;

www.linkedin.com/in/anjanakuiri
22. Display the following output for each row from EMP table as ‘scott has joined the company on
Wednesday 13th august nineteen ninety’.
SELECT ENAME||' has joined the company on '||TO_CHAR(HIREDATE,'DAY DDTH MONTH YEAR')
JOINING_DETAILS
FROM EMP;
23. Find the date of the nearest Saturday after the current day.
SELECT NEXT_DAY(SYSDATE, 'SATURDAY')
FROM DUAL;

24. Display current time.

SELECT TO_CHAR(SYSDATE,'HH:MI:SS') TIME
FROM DUAL;
25. Display the date three months before the current date.
SELECT ADD_MONTHS(SYSDATE, -3)
FROM DUAL;

26. Display the common jobs from department number 10 and 20.
SELECT JOB
FROM EMP
WHERE DEPTNO = 10
INTERSECT
SELECT JOB
FROM EMP
WHERE DEPTNO = 20;

27. Display the jobs found in department number 10 and 20 eliminate duplicate jobs.
SELECT DISTINCT(JOB)
FROM EMP
WHERE DEPTNO = 10
AND JOB IN(SELECT JOB
FROM EMP
WHERE DEPTNO = 20);
or,
SELECT JOB
FROM EMP
WHERE DEPTNO =10
INTERSECT
SELECT JOB
FROM EMP
WHERE DEPTNO = 20;

28. Display the jobs which are unique to dept no 10.

SELECT JOB
FROM EMP
WHERE DEPTNO = 10
MINUS
www.linkedin.com/in/anjanakuiri
 SELECT JOB
FROM EMP
WHERE DEPTNO != 10;
or,
SELECT JOB
FROM EMP
WHERE DEPTNO = 10
AND JOB NOT IN (SELECT JOB
FROM EMP
WHERE DEPTNO <> 10);

29. Display the details of those who do not have any person working under them.
SELECT *
FROM EMP
WHERE EMPNO NOT IN (SELECT MGR
FROM EMP
WHERE MGR IS NOT NULL);

30. Display those who are not managers and who are managers any one.
SELECT *
FROM EMP
WHERE EMPNO IN (SELECT MGR FROM EMP WHERE MGR IS NOT NULL)
UNION
SELECT *
FROM EMP
WHERE EMPNO NOT IN (SELECT MGR FROM EMP WHERE MGR IS NOT NULL);

31. Display those employees whose name contains not less than 4 chars.
SELECT *
FROM EMP
WHERE LENGTH(ENAME) >= 4;
32. Display those departments whose name start with ‘S’ while location name end with ‘O’.
SELECT *
FROM DEPT
WHERE DNAME LIKE 'S%'
AND LOC LIKE '%O';
33. Display those employees whose manager name is JONES.
SELECT *
FROM EMP
WHERE MGR = (SELECT EMPNO
FROM EMP
WHERE ENAME = 'JONES');
34. Display those employees whose salary is more than 3000 after giving 20% increment.
SELECT *
www.linkedin.com/in/anjanakuiri
 FROM EMP
WHERE SAL * 1.2 > 3000;

35. Display all employees with there dept name.

SELECT E.ENAME, D.DNAME
FROM EMP E
INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
36. Display ename who are working in sales dept.
SELECT ENAME
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE DNAME = 'SALES');
37. Display employee name, deptname, salary and comm. for those salary in between 2000 to 5000
while location is Chicago.
SELECT E.ENAME, D.DNAME, E.SAL, E.COMM
FROM EMP E
INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE D.LOC = 'CHICAGO'
AND E.SAL BETWEEN 2000 AND 5000;
38. Display those employees whose salary is greater than his manager salary.
SELECT *
FROM EMP E
WHERE SAL > (SELECT SAL
FROM EMP
WHERE EMPNO = E.MGR);
39. Display those employees who are working in the same dept where their manager is working.
SELECT *
FROM EMP E
WHERE DEPTNO = (SELECT DEPTNO
FROM EMP
WHERE EMPNO = E.MGR);
40. Display those employees who are not working under any manager.
SELECT *
FROM EMP
WHERE MGR IS NULL;

www.linkedin.com/in/anjanakuiri