Single Row Subquery in Oracle

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.

Example to understand Single Row Subquery in Oracle

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.

Single Row Subquery in Oracle with Examples

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.

Single Row Subquery in Oracle with Examples

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.

Single Row Subquery in Oracle with Examples

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.

Single Row Subquery in Oracle with Examples

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.

Single Row Subquery in Oracle

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.

Single Row Subquery in Oracle

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.

Single Row Subquery in Oracle

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.

Single Row Subquery in Oracle

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.

Single Row Subquery

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.

Oracle Single Row Subquery

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.

Oracle Single Row Subquery

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.

Leave a Reply

Your email address will not be published.