SELECT with Subqueries Questions

1.    Display the names and job titles of all employees with the same job as Jones.

SELECT ename, job FROM emp
WHERE job = (SELECT job FROM emp WHERE ename = 'JONES');

2.    Display the names and department name of all employees working in the same city as Jones.

SELECT ename, dname FROM emp INNER JOIN dept ON emp.deptno = DEPT.deptno
WHERE loc = (SELECT loc FROM emp INNER JOIN dept ON emp.deptno = DEPT.deptno
                     WHERE ename = 'JONES');
                “OR”
SELECT ename, dname FROM emp INNER JOIN dept USING (deptno)
WHERE loc = (SELECT loc FROM emp INNER JOIN dept USING (deptno)
                     WHERE ename = 'JONES');

3.    Display the name of the employee whose salary is the lowest.

SELECT ename FROM emp WHERE sal = (SELECT min(sal) FROM emp);
“OR”
SELECT ename FROM emp, (SELECT min(sal) as sal  FROM emp) minsal
WHERE emp.sal = minsal.sal

4.    Display the names of all employees except the lowest paid.

SELECT ename FROM emp WHERE sal> (SELECT min(sal) FROM emp);

5.    Display the names of all employees whose job title is the same as anyone in the sales dept.

SELECT ename FROM emp
WHERE job IN (SELECT DISTINCT job FROM emp INNER JOIN USING(deptno)
WHERE dname = 'SALES');

6.    Display the names of all employees who work in a department that employs an analyst.

SELECT ename FROM emp WHERE deptno IN (SELECT DISTINCT emp.deptno
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno WHERE job = 'ANALYST');

7.    Display the names of all employees with their job title, their current salary and their salary following a 10% pay rise for clerks and a 7% pay rise for all other employees.

SELECT ename, job, sal, 1.1 * sal AS "newsal" FROM emp WHERE job = 'CLERK'
UNION
SELECT ename, job, sal, 1.07 * sal AS "newsal" FROM emp WHERE job <> 'CLERK';

8.    Display the names of all employees with their salary and commission earned. Employees with a null commission _eld should have 0 in the commission column.

SELECT ename, sal, comm FROM emp WHERE comm IS NOT NULL
UNION
SELECT ename, sal, 0 FROM emp WHERE comm IS NULL;
                “OR”
SELECT ename, sal, NVL(comm,0) as comm FROM emp


9.    Display the names of ALL employees with the total they have earned (ie. salary plus commission).

SELECT ename, sal + comm AS "earnings" FROM emp WHERE comm IS NOT NULL
UNION
SELECT ename, sal FROM emp WHERE comm IS NULL;
“OR”
SELECT ename, NVL2(comm,sal + comm,sal) AS "earnings" FROM emp

10.    Repeat the display for the last question but this time display in descending order of earnings.

SELECT ename, sal + comm AS "earnings" FROM emp WHERE comm IS NOT NULL
UNION
SELECT ename, sal FROM emp WHERE comm IS NULL ORDER BY earnings DESC;
“OR”
SELECT ename, NVL2(comm,sal + comm,sal) AS "earnings" FROM emp ORDER BY earnings DESC;

I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: