Loading presentation...

Present Remotely

Send the link below via email or IM


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.


Oracle SQL into #4

Grouping data

Rodion Mironov

on 31 January 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Oracle SQL into #4

Agenda: most-used aggregate functions;
GROUP BY clause;
HAVING clause;
ListAgg function. Introduction to Oracle SQL. Grouping data. Aggregate functions Aggregate functions work with set of rows: COUNT
FROM employees NULL in aggregate functions NULLs are ignored by aggregate function
count(*) is an exception SELECT
COUNT(*) -- all rows
,COUNT(1) -- all rows
,COUNT(manager_id) -- rows with manager_id is not null
,COUNT(commission_pct) -- rows with commission_pct is not null
FROM employees DISTINCT in aggregate functions SELECT
,COUNT(DISTINCT last_name)
FROM employees SELECT
FROM employees
GROUP BY job_id Grouping SELECT
FROM employees
GROUP BY job_id, department_id
ORDER BY department_id SELECT
,department_id || ' ' || job_id
FROM employees
GROUP BY job_id, department_id; SELECT AVG(MAX(salary))
FROM employees
GROUP BY department_id Nested aggregate functions:
with GROUP BY clause only
max. 2 levels SELECT SUM(AVG(MAX(salary)))
FROM employees
GROUP BY department_id SELECT AVG(MAX(salary))
FROM employees Practice 1.Calculate maximum, minimum and average salary for each department;

2.Select number of unique JOB_IDs in EMPLOYEES table;

3.List departments having more than 10 employees or summary salary > 30000;

4.Find 5 most-paid (in average) JOB_IDs;

5.Find average number of employees in department;

6.Show list of DEPARTMENT_IDs having at least one employee with salary > 8000. Show total salary for each such department.

7.For each department in EMPLOYEES table calculate:
a.Number of people with commission;
b.Number of people without commission.

8.For each JOB_ID show list of employees on this position with their DEPARTMENT_IDs and SALARIES (one row per JOB_ID). List of employees should be ordered by salary in descending order.

Aggregate functions:

GROUP BY clause: Useful links ListAgg new 11g R2 function for strings aggregation;
pre-ListAgg solutions were limited and/or complex (PL/SQL, self-written function, sys_connect_by_path);
non-standard syntax. LISTAGG(expression[, separator])
WITHIN GROUP (ORDER BY expression2) SELECT department_id,
LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name)
FROM employees
GROUP BY department_id http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions003.htm#i89203 http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2182483 SUM(expr) preudo code:
result := 0;
for each row {
if expr is not null {
result := result + expr;
} -- Aggregate functions and NULLs
avg(commission_pct) as AVG_NO_NULLS
,count(commission_pct) as COUNT_NO_NULLS
,avg(NVL(commission_pct,0)) as AVG_WITH_NULLS
,count(NVL(commission_pct,0)) as COUNT_WITH_NULLS
from employees; just ignores duplicated values Aggregate functions
with empty data set count returns 0;
other functions return NULL; SELECT
FROM (select *
from employees
where employee_id <0); With GROUP BY clause aggregate function operate on separate groups of rows and return one row per group. Several columns (or expression) can be used for grouping: For GROUP BY query you can SELECT: columns from GROUP BY clause;
expressions with columns from GROUP BY clause;
aggregate functions and expressions with aggregate functions;
constants; SELECT
FROM employees
GROUP BY job_id, department_id; GROUP BY is executed:
after WHERE clause;
before ORDER BY clause; ORDER BY can use aggregate functions SELECT
,count(*) as cnt
,sum(salary) as budget
FROM employees
GROUP BY department_id
ORDER BY cnt, sum(salary); WHERE can't reference aggregate functions.
Use HAVING clause instead. SELECT
,count(*) as cnt
,sum(salary) as budget
FROM employees
WHERE count(*) > 5
GROUP BY department_id; SELECT
,count(*) as cnt
,sum(salary) as budget
FROM employees
GROUP BY department_id
HAVING count(*) > 5;
Full transcript