SubQueries

Single Row_SubQuery

--------------------

Single-Row Sub-Queries

Returns one value

SELECT <columns> FROM <table_name> WHERE <col_name> <OP> (SELECT STMNT);

> , >=, <, <=, =. <>

A single-row subquery in Oracle SQL is a subquery (a query nested inside another query) that returns only one row.

The main/outer query uses the inner query result as its conditions

If single row sub-query returns more than one row then you will get an error

ORA-01422: exact fetch returns more than requested number of rows)

It can be used in Select, Where, Having clauses

Single Row_SubQuery

--------------------

--1. Find who is getting highest salary in the employee table?

select max(sal) from emp;

select *from emp where sal=(select max(sal) from emp);

select max(salary) from employees;

select *from employees where salary=(select max(salary) from employees);

--2.Find second maximum salary emp?

select *from emp where sal=(

select max(sal) from emp where sal!=(select max(sal) from emp));

select *from emp where sal=(

select max(sal) from emp where sal<>(select max(sal) from emp));

--3.Find the employees who are getting salary more than the average salary?

select round(avg(salary)) from employees;

select * from employees where salary>(select round(avg(salary)) from employees);

--4.Find emp who is earning more than BLAKE?

select *from emp where sal>(select sal from emp where ename='BLAKE');

--5.Find emp who has maximum experience

select hiredate from emp;

select min(hiredate) from emp;

select *from emp where hiredate=(select min(hiredate) from emp);

--6.Find emp who has minimum experience

select hiredate from emp;

select max(hiredate) from emp;

select *from emp where hiredate=(select max(hiredate) from emp);

--7.Find the department which is having maximum number of emp working?
select count(*) from emp group by deptno;
select max(count(*)) from emp group by deptno;
select deptno,count(*)from emp group by deptno having count(*)=(select max(count(*)) from emp group by deptno);
select deptno,max(count(*))from emp group by deptno;--not a single-group group function

--8.In which year maximum number of employees joined?

select hiredate,count(*) from emp group by hiredate;

select extract(year from hiredate) year,count(*) emp_joined from emp group by hiredate having count(*)=(select max(count(*)) from emp group by hiredate);

Multi-Row Sub-Queries
---------------------
--. Find out the highest salaried employees for each department?
Two ways: 
Using Rank()
select deptno, sal,Rank() over(partition by deptno order by sal desc) Rank from emp;
select 
a.deptno,
a.sal
from
(select deptno, sal,Rank() over(partition by deptno order by sal desc) Rank from emp) a
where
a.Rank=1
group by a.deptno,a.sal;
Using Sub-Query
select max(sal) from emp;
select deptno,max(sal) from emp group by deptno;
select max(sal) from emp;
select max(sal) from emp group by deptno;
select deptno,max(sal) from emp group by deptno;
select *from emp where (deptno,sal) IN (select deptno,max(sal) from emp group by deptno);--must follow the order of the columns