Sub-Queries in Oracle SQL
Sub-Queries in Oracle SQL
|
SNO |
Type
of Sub-Quries |
|
1 |
Single-Row Sub-Queries |
|
2 |
Multi-Row Sub-queries |
|
3 |
Co-Related Sub-Queries |
|
4 |
Inline Views |
|
5 |
Scalar Sub-Queries |
|
Single-Row
Sub-Queries |
|
Returns one value |
|
SELECT <columns> |
|
> , >=, <, <=, =. <> |
A single-row subquery in
Oracle SQL is a subquery (a query nested inside another query) that returns
only one row.
Ø
It can be used in Select, Where, and Having
clauses.
Ø
The main/outer query uses the inner query result
as its conditions.
Ø
If a single-row sub-query returns more than one
row, then you will get an error
Ø
ORA-01422: exact fetch returns more than the requested
number of rows.
FAQ
1. Find employees who are earning more than BLAKE?
The inner query finds BLAKE's salary. The outer query finds everyone earning more than that specific number.
SELECT ename, sal
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'BLAKE');
2. Find employees who are senior to KING?
"Seniority" is determined by having an earlier hiredate.
SELECT ename, hiredate
FROM emp
WHERE hiredate < (SELECT hiredate FROM emp WHERE ename = 'KING');
3. Display a list of Employee names working at the CHICAGO location?
The sub-query bridges two tables. We find the deptno for CHICAGO from the dept table and use it to filter the emp table.
SELECT ename
FROM emp
WHERE deptno = (SELECT deptno FROM dept WHERE loc = 'CHICAGO');
4. Display employees who are earning more than avg(sal)?
Calculate the average salary of the whole table first, then return employees earning above that number.
SELECT ename, sal
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);
5. Find the emp name who is getting the highest salary? or Find 1st maximum salary emp?
We cannot use WHERE sal = MAX(sal) directly. we must evaluate the MAX(sal) in a sub-query first.
SELECT ename, sal
FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp);
6. Find emp who has maximum experience?
Maximum experience means they joined the earliest. Therefore, we look for the minimum hiredate.
SELECT ename, hiredate
FROM emp
WHERE hiredate = (SELECT MIN(hiredate) FROM emp);
7. Find emp who has minimum experience?
Conversely, minimum experience means they joined most recently (the maximum hiredate).
SELECT ename, hiredate
FROM emp
WHERE hiredate = (SELECT MAX(hiredate) FROM emp);
8. Find second maximum salary emp?
We use a nested sub-query. The innermost query finds the highest salary. The middle query finds the highest salary less than the absolute highest (which is the 2nd highest). The outer query fetches the employee(s) making that amount.
SELECT ename, sal
FROM emp
WHERE sal = (
SELECT MAX(sal)
FROM emp
WHERE sal < (SELECT MAX(sal) FROM emp)
);
9. Find the department which is having maximum number of emp working?
We group by
deptnoand count employees, comparing that count to the maximum count of any department.
SELECT deptno, COUNT(*) as total_employees
FROM emp
GROUP BY deptno
HAVING COUNT(*) = (
SELECT MAX(COUNT(*))
FROM emp
GROUP BY deptno
);
10. In which year maximum number of employees joined?
Similar to the previous query, but we group by the extracted year of the hiredate instead of the department. (Standard SQL syntax shown below).
SELECT EXTRACT(YEAR FROM hiredate) AS join_year, COUNT(*) as total_joined
FROM emp
GROUP BY EXTRACT(YEAR FROM hiredate)
HAVING COUNT(*) = (
SELECT MAX(COUNT(*))
FROM emp
GROUP BY EXTRACT(YEAR FROM hiredate)
);
Gemini said
11. Increase the sal of employee having max experience by 10%?
Sub-queries work perfectly in
UPDATEstatements to identify exactly which rows to target.
UPDATE emp7 SET sal = sal * 1.10 WHERE hiredate = (SELECT MIN(hiredate) FROM emp7);
12. Swap employee salaries whose empno are 7369, 7499?
While not strictly a standard single-row subquery problem, you can solve this gracefully using a CASE statement with single-row sub-queries fetching the other person's salary.
UPDATE emp7 SET sal = CASE empno WHEN 7369 THEN (SELECT sal FROM emp7 WHERE empno = 7499) WHEN 7499 THEN (SELECT sal FROM emp7 WHERE empno = 7369) ELSE sal END WHERE empno IN (7369, 7499); select *from emp7;
13. How do you delete duplicates from a table?
If you are using Oracle (where this dataset originates), the fastest way uses a correlated subquery with the hidden ROWID pseudocolumn. It keeps the row with the lowest ROWID (the original) and deletes the rest.
DELETE FROM emp e1
WHERE rowid > (
SELECT MIN(rowid)
FROM emp e2
WHERE e1.empno = e2.empno
);
13. How do you delete duplicates from a table?
DELETE FROM emp7 e1
WHERE rowid > (
SELECT MIN(rowid)
FROM emp7 e2
WHERE e1.empno = e2.empno
);
If inner query returns more than one rows then it is calle Multi_Row Subquery.
Syntax:
SELECT COLUMNS
FROM TABNAME
WHERE COLNAME <OP> (SELECT STMT);
OP-Operator can be any of the following:
Exercises:
ename
from emp
where deptno in(select deptno from dept where loc in('NEW YORK','CHICAGO'));
select
ename
from emp
where deptno in(select deptno from dept where loc not in('NEW YORK','CHICAGO'));
Let us understand ANY and ALL operators logic
Comments
Post a Comment