Oracle 기초 : 실전(6.5) Preview - Subqueries (update: 2020/06/18)

---

Preview: Introduction of Subqueries

Subquery에 대한 본 수업을 듣기 전에, 가볍게 훑어보기 좋은 자료가 있어 백업하기로 한다.
https://www.slideshare.net/salmanmemon77964/subqueries-oracle-database

---

Objective

After completing this lesson, you should be able to do the following:

• Describe the types of problem that Subqueries can solve
• Define subqueries
• List the types of Subqueries
• Write single-row and multiple-row subqueries

---

Using a Subquery to Solve the Problem

• Who has a salary greater than Abel's?
  For Solve this problem, you can split the Query to two parts:
  1. Main Query: Which employees have salaries greater than Abel's Salary
  2. Subquery: What is Abel's salary?

---

Syntax of Subquery:

select Alias-of-Subquery
from tableName
where expression-Operator ( select Alias-of-Subquery
from tableName);

• The subquery(inner Query) executes once before main query.
• The result of the subquery is used by main query (outer query).

---

Using a Subquery

select last_name
from employee
where salary > ( select salary 
		 from employee 
                 where last_name = 'Abel');

---

Guidelines for Using Subqueries

• Enclose subqueries in parentheses.
• Place subqueries on the right side of the comparison condition
• The ORDER BY clause in the subquery is not need unless you are performing Top-N analysis.
• Use single-row operators with single-row subqueries and use multiple-row operators with multiple-row subqueries.

---

Types of Subqueries

---

Single-row Subqueries

• Return only one row
• Use single-row conparison operators:
  

---

e.g. Executing Single-Row Subqueries

select last_name, job_id, salary
from employees
where job_id = (select job_id
		from employees
                where employee_id = 141)
and salary >   (select job_id
		from employees
                where employee_id = 143);

---

e.g. Using Aggregate Functions in a Subquery

select last_name, job_id, salary
from employees
where salary = (select min(salary)
		from employees);

---

The Having Clause with Subqueries

• The Oracle server executes subqueries first.
• The Oracle server returns results into the Having clause of the main query.

select department_id, MIN(salary)
from employee
group by department_id
having min(salary) > (select min(salary)
		      from employee
                      where department_id = 50);

---

Multiple-Row Subqueries

• Return more than one row
• Use multiple-row comparison operators
  

---

Using the Multiple-Row Operators with Subqueries

--Using the Any Operator in Multiple-Row Subqueries
select employee_id, last_name, job_id, salary
from employees
there salary < any (select salary 
		    from employees 
                    where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';

--Using the All Operator in Multiple-Row Subqueries 
select employee_id, last_name, job_id, salary
from employees
where salary < all (select salary 
		    from employees 
            	    where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';

---

Summary

In this lesson, you should have learned how to:

• Identify when a subquery can help solve a question
• Write subqueries when a query is based on unknown values

---