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 #7

Set operations
by

Rodion Mironov

on 5 February 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Oracle SQL intro #7

Introduction to Oracle SQL.
Set operation. Agenda UNION and UNION ALL;
MINUS;
INTERSECT;
ORDER BY with set operations Set operation:
mathematical set operations with data sets;
number and types of columns should be identical for all sets (query blocks); query block 1
{UNION | UNION ALL | MINUS | INTERSECT}
query block 2
....
query block N Even those initially present in one data source.
Other set operations (but UNION ALL) do the same. select d.department_name
from employees e
inner join departments d on e.department_id = d.department_id
group by d.department_name
having count(*) > 10
union
select d.department_name
from departments d
inner join locations l on d.location_id = l.location_id
where l.country_id = 'UK' select *
from (
select job_id, start_date, end_date
from job_history
where employee_id = 101
union all
select job_id,hire_date, null
from employees
where employee_id = 101)
order by start_date UNION would return the same result for such query, but UNION ALL is better as it does not waste resources doing deduplication Column names from the first query are used for the result.
Column names from second query are ignored. MINUS - returns records present in first data and not present in second. With deduplication. INTERSECT - returns records present in both data sets. With deduplication. select employee_id, job_id
from employees
intersect
select employee_id, job_id
from job_history What will this query return ? select null from dual
union
select null from dual Order of precedence:
all set operations have equal order of precedence and are executed from left to right;
this can be changed with parentheses. ORDER BY with set operations:
order by can appear only in the very last query block;
order by can reffer to columns names/aliaces from the first query block only. Practice select 2 from dual
minus
select 1 from dual
union all
select 2 from dual select 2 from dual
minus
(select 1 from dual
union all
select 2 from dual) select 2 as f1 from dual
union all
select 1 as f2 from dual
order by f2 select 2 as f1 from dual
union all
select 1 as f2 from dual
order by f1 select department_name
from departments
minus
select d.department_name
from locations l
inner join departments d on l.location_id = d.location_id
where l.country_id = 'US' 1.List full names of people who worked or is currently working as "Programmer". Specify “Currently” or “In the past” in a separate column.

2.Find the most popular month for hiring people in our company. Take into account currently working people and history.

3.Find all departments that are either located in Canada or have not less than 10 people.

4.For each employee print full name and number of different positions (jobs) he worked. Take into account current positions and JOB_HISTORY.

5.List names of departments that have employees currently and hadn't fired employees in the past. UNION - unites two data sets and deletes duplicates.
UNION ALL - unites two data sets, does not delete duplicates. select 2 as f1 from dual
order by f1
union all
select 1 as f2 from dual
Full transcript