Single Row Subquery in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Single Row Subquery in Oracle with Examples

In this article, I am going to discuss Single Row Subquery in Oracle with Examples. Please read our previous article where we discussed Subqueries in Oracle with Examples.

Oracle Single Row Subquery

When a Subquery returns a single value is called a Single Row Subquery. In Single Row Subquery we can use operators such as = , < , > , <= , >=,!=. Let us see a few examples for a better understanding of the Single Row Subquery in Oracle.

Example to understand Single Row Subquery in Oracle

We are going to use the following Employee table to understand Single Row Subquery in Oracle with Examples.

Please use the below SQL Script to create and populate the Employee table with the required sample data.

CREATE TABLE EMPLOYEE
(	
        EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2)
);

INSERT INTO EMPLOYEE VALUES (7369, 'SMITH', 'CLERK',      7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMPLOYEE VALUES (7521, 'WARD', 'SALESMAN',    7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMPLOYEE VALUES (7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7654, 'MARTIN', 'SALESMAN',  7698, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMPLOYEE VALUES (7698, 'BLAKE',  'MANAGER',   7839, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMPLOYEE VALUES (7782, 'CLARK',  'MANAGER',   7839, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMPLOYEE VALUES (7788, 'SCOTT',  'ANALYST',   7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMPLOYEE VALUES (7844, 'TURNER', 'SALESMAN',  7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMPLOYEE VALUES (7876, 'ADAMS',  'CLERK',     7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7900, 'JAMES',  'CLERK',     7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMPLOYEE VALUES (7902, 'FORD',   'ANALYST',   7566, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMPLOYEE VALUES (7934, 'MILLER', 'CLERK',     7782, TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

Example1:

Who is getting the first high salary from the Employee table?
Solution: SUBQUERY = OUTER + INNER

Step1: Inner Query. The following is going to be our inner query
SELECT MAX(SAL) FROM EMPLOYEE;

Step2: Outer query. The following is going to be our outer query
SELECT * FROM EMPLOYEE WHERE SAL= (INNER QUERY);

Step3: subquery= outer + inner. The following is our subquery which includes the outer and inner query.
SELECT * FROM EMPLOYEE WHERE SAL = (SELECT MAX(SAL) FROM EMPLOYEE);

When you execute the above subquery, you will get the following output which shows the first highest salary employee details from the Employee table.

Example2:

Whose employee job is the same as the job of ‘SMITH’?
Solution:

SELECT * FROM EMPLOYEE WHERE JOB=(SELECT JOB FROM EMPLOYEE WHERE ENAME='SMITH');

When you execute the above subquery, you will get the following output.

Example3:

Whose salary is more than the max salary of the job is “SALESMAN”?
Solution:

SELECT * FROM EMPLOYEE WHERE SAL>(SELECT MAX(SAL) FROM EMPLOYEE WHERE JOB='SALESMAN');

When you execute the above subquery, you will get the following output.

Example4:

Whose employee job is same as the job of “BLAKE” and who are earning Salary more than “BLAKE” salary?
Solution:

SELECT * FROM EMPLOYEE WHERE JOB=(SELECT JOB FROM EMPLOYEE WHERE ENAME='BLAKE') AND SAL>(SELECT SAL FROM EMPLOYEE WHERE ENAME='BLAKE');

When you execute the above subquery, you will get the following output.

Example5:

Display senior employee?
Solution:
SELECT * FROM EMPLOYEE WHERE HIREDATE=(SELECT MIN(HIREDATE) FROM EMPLOYEE);
When you execute the above subquery, you will get the following output.

Example6:

Find the second-highest Salary from the Employee table?
Solution:
SELECT MAX(SAL) FROM EMPLOYEE WHERE SAL<(SELECT MAX(SAL) FROM EMPLOYEE);
When you execute the above subquery, you will get the following output.

Example7:

Display employee details who are getting the Second highest Salary in the Employee table?
Solution:
SELECT * FROM EMPLOYEE WHERE SAL=(SELECT MAX(SAL) FROM EMPLOYEE WHERE SAL< (SELECT MAX(SAL) FROM EMPLOYEE));
When you execute the above subquery, you will get the following output.

Example8:

Display employee details who are getting 3rd highest Salary in Employee table?
Solution:

SELECT * FROM EMPLOYEE WHERE SAL=
(SELECT MAX(SAL) FROM EMPLOYEE WHERE SAL<
(SELECT MAX(SAL) FROM EMPLOYEE WHERE SAL<
(SELECT MAX(SAL) FROM EMPLOYEE)));

When you execute the above subquery, you will get the following output.

Example9:

Sum of salary of jobs if the sum of salary of jobs are more than the sum of salary of the job is ‘clerk’?
Solution:

SELECT JOB, SUM(SAL) FROM EMPLOYEE GROUP BY JOB HAVING SUM(SAL)>(SELECT SUM(SAL) FROM EMPLOYEE WHERE JOB='CLERK');

When you execute the above subquery, you will get the following output.

Subquery with “UPDATE”:

To update employee salary with max. salary of Employee table whose empno is 7900?
Solution:
UPDATE EMPLOYEE SET SAL=(SELECT MAX(SAL) FROM EMPLOYEE) WHERE EMPNO=7900;
When you execute the above subquery, you will get the following output.

Subquery with “DELETE”:

Delete employee details from the Employee table whose job is the same as the job of ‘SCOTT’?
Solution:

DELETE FROM EMPLOYEE WHERE JOB=(SELECT JOB FROM EMPLOYEE WHERE ENAME='SCOTT');

When you execute the above subquery, you will get the following output.

In the next article, I am going to discuss Multiple Row Subquery in Oracle with examples. Here, in this article, I try to explain Single Row Subquery in Oracle with Examples. I hope you enjoy this Oracle Single Row Subquery article.