Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Oracle SQL intro #6

Subqueries
by

Rodion Mironov

on 4 February 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Oracle SQL intro #6

Introduction to
Oracle SQL. Subqueries. Agenda what is subquery;
scalar subqueries, single-row conditions;
multi-row subqueries and conditions;
correlated subqueries;
inline views;
subquery factoring. Subquery - just a query embedded into another (parent) query. Who is working longer
than company president? How long company president works? select
e.last_name
,e.first_name
,e.hire_date
from employees e
where e.hire_date < (select hire_date
from employees e2
where e2.job_id = 'AD_PRES') Example shown is SCALAR subquery - it returns one value (i.e. one column and not more than one row).
Such subqueries:
can be used in all clauses of SQL statement but GROUP BY;
can be used in CASE and DECODE expressions;
are used with single-row comparison operators (=, <>, <, >, <=, >=). select e.last_name, e.first_name,
round(e.salary/(select sum(e2.salary)
from employees e2)*100, 2)
as total_salary_percent
from employees e
order by total_salary_percent desc select d.department_name, count(*) as staff
from employees e
inner join departments d on e.department_id = d.department_id
group by d.department_name
having count(*) > (select count(*)
from employees)*0.1 select e.last_name
from employees e
where e.salary > (select e2.salary
from employees e2
where e2.last_name = 'Ivanov') select e.last_name
from employees e
where e.salary > (select e2.salary
from employees e2
where e2.last_name = 'King') ORA-01427:single-row
subquery returns more than
one row Scalar subquery
returning no rows equals
to NULL. Subqueries can be used in SELECT, FROM, WHERE, HAVING and ORDER BY clauses I can use subquery returning one row
and more than 1 column:
in WHERE or HAVING clauses;
with "=" and "<>" comparison operators. select
e.last_name, e.first_name
,e.department_id, e.job_id
from employees e
where (e.department_id, e.job_id) =
(select e2.department_id, e2.job_id
from employees e2
where e2.last_name = 'Atkinson') I can use subquery returing more than one row
with multiple-row comparison operators:
[NOT] IN
[NOT] EXISTS
ALL
ANY <expr> [NOT] IN <subquery> :
TRUE if <expr> is [not] present in records returned by <subquery>;
beware of NULLs with NOT IN. select d.department_name
from departments d
where d.location_id in
(select l.location_id
from locations l
where l.country_id = 'UK') select d.department_name
from departments d
where d.department_id
not in (select distinct e.department_id
from employees e) NOT IN is always false
(unknown, if fact) if subquery
returns at least one
NULL value. <expr> <operator> ALL/ANY <subquery>:
TRUE if <expr> <operator> is true for ALL/ANY records returned by subquery select e.last_name, e.first_name, e.job_id
from employees e
where e.salary > ALL
(select e2.salary
from employees e2
where e2.job_id = 'IT_PROG') [NOT] EXISTS <subquery>:
TRUE if <subquery> returns [NO] rows select c.country_name
from countries c
where not exists
(select 1 from
locations l
where l.country_id = c.country_id) And, of course, I can use multiple-row and multiple-column subqueries. select e.last_name, e.first_name
from employees e
where (e.employee_id, e.department_id)
in (select jh.employee_id
,jh.department_id
from job_history jh) Correlated subqueries:
subqueries referensing column(s) from parent statement;
are executed ones per row. select
e.last_name
,e.first_name
,d.department_name
,(select count(*)
from employees e2
where e2.department_id = e.department_id)
as num_colleagues
from employees e
inner join departments d on e.department_id = d.department_id select e.last_name, e.first_name
from employees e
where e.salary >= ALL
(select e2.salary
from employees e2
where e2.job_id = e.job_id) Inline views:
subqueries in FROM clause;
are used like "table" in parent statement;
can not be correlated. select
e.last_name
,e.first_name
,e.salary
,dep_stats.min_dep_salary
,dep_stats.avg_dep_salary
,dep_stats.max_dep_salary
from employees e left outer join
(select
e2.department_id
,min(e2.salary) as min_dep_salary
,round(avg(e2.salary),2) as avg_dep_salary
,max(e2.salary) as max_dep_salary
from employees e2
group by e2.department_id) dep_stats on e.department_id = dep_stats.department_id
where e.salary >= dep_stats.avg_dep_salary*0.7 You must use column aliases in inline view for futher reference. Subquery factoring aka WITH clause
useful if you need to use the same inline view twice or more;
useful for "test" queries; with dep_budget as
(select d.department_name, sum(e.salary) as budget
from departments d
left outer join employees e on d.department_id = e.department_id
group by d.department_name
)
select db1.*
from dep_budget db1
where db1.budget > (select avg(budget) from dep_budget db2) Note: WITH clause is often a problem for query optimizer. At least for now. So don't use it just for conveniece. with
a as (select 1 as a_id from dual)
,ab as (select null as a_id, 2 as b_id from dual)
select a.a_id, ab.a_id, ab.b_id
from a
left outer join ab on a.a_id = ab.a_id Practice 1.List all employees having the same manager as Sarah Bell;

2.Print full names of employees who have namesakes in company. Order list by first names. Note: namesake - person with the same name.

3.Find all employees who had at least two other positions before;

4.List all departments having no employees currently. Do it in two different ways.

5.For each employee find his full name, salary and salary rank in his department.
Order list by DEPARTMENT_ID and salary rank.

What will this query return?
select 1 from dual
where (1,1) not in (select 2, null from dual) Subqueries and alternatives IN vs JOIN IN vs EXISTS NOT IN vs NOT EXISTS select jh.employee_id
from job_history jh
where jh.employee_id in
(select e.employee_id
from employees e) select jh.employee_id
from job_history jh
inner join employees e
on jh.employee_id = e.employee_id VS select l.location_id
from locations l
where l.location_id in
(select d.location_id
from departments d) select l.location_id
from locations l
inner join departments d
on l.location_id = d.location_id VS select distinct
e.department_id
,e.job_id
from employees e
where (e.department_id, e.job_id) in
(select jh.department_id, jh.job_id
from job_history jh) select distinct e.department_id, e.job_id
from employees e
where exists
(select null
from job_history jh
where jh.department_id = e.department_id
and jh.job_id = e.job_id) VS select e.Last_Name
from employees e
where not exists
(select null
from employees e1
where e1.manager_id = e.employee_id) select e.Last_Name
from employees e
where e.employee_id not in
(select e1.manager_id
from employees e1) VS Summary:
IN is equivalent to EXISTS.
there was some difference between IN and EXISTS execution (not results) in earlier version.
NOT IN and NOT EXISTS treat NULLS differently http://docs.oracle.com/cd/B10501_01/server.920/a96533/opt_ops.htm#1656 http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:442029737684 http://docs.oracle.com/cd/E11882_01/server.112/e26088/queries007.htm#i2067858 Using Subqueries


AskTom EXISTS vs IN


AskTom NOT EXISTS vs NOT IN


9i optimizer operations Useful links http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:953229842074
Full transcript