Lesson 05

Reporting Aggregated Data Using the Group Functions

?# What are group functions?

Group functions operate on sets of rows to give one result per group.

!# Types of Group Functions

* AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE

!# Group Functions: Syntax

Select group_function(column), ...
From table
[Where condition]
[Order By column];

You can use AVG and SUM for numeric data.

Select AVG(salary), MAX(salary), MIN(salary), SUM(salary)
From employees
Where job_id LIKE '%REP%';

You can use MIND and MAX for numeric, character, and date data types

Select MIN(hire_date), MAX(hire_date)
From employees;

COUNT(*) returns the number of rows in a tabel

Select COUNT(*)
From employees
Where department_id = 50;

COUNT(DISTINCT expr) return the number of distinct non-null values of exp.
To display the number of distinct department values in the EMPLOYEES tabel:

Select COUNT(DISTINCT department_id)
From employees;

Group functions ignore null values in the column:

Select AVG(commission_pct)
From employees;

The NVL functions forces group functions to include null values:

Select AVG(NVL(commission_pct, 0 ))
From employees;

Creating Groups of Data:
GROUP BY Clause Syntax

Select column, group_function(column)
From table
[Where condition]
[GROUP BY group_by_expression]
[Order By column];

You can divide rows in a table into smaller groups by using the GROUP BY clause.

All columns in the Select list that are not in group functions must be in the GROUP BY clause.

Select department_id, AVG(salary)
From employees
GROUP BY department_id;

The GROUP BY column does not have to be in the Select list.

Select AVG(salary)
From employees
GROUP BY department_id;

Using the Group by Clause on Multiple Columns

Select department_id, job_id, SUM(salary)
From employees
Where department_id > 40
Group By department_id, job_id
Order By department_id;

Lesson 04

select to_char( salary, '$99,999,00') Salary
from employees
where last_name = 'Ernst';

select last_name, to_char( hire_date, 'DD-Mon-YYYY')
from employees
where hire_date < to_date('01-Jan-90','DD-Mon-RR');

select last_name,
  upper(concat(substr(last_name, 1, 8), '_US'))
from employees
where department_id = 60;

select last_name, salary, NVL(commission_pct, 0 ),
  (salary*12) + (salary*12*NVL(commission_pct, 0 )) AN_SAL
from employees;

select last_name, salary, commission_pct,
  NVL2(commission_pct, 'SAL+COMM', 'SAL') income 
from employees where department_id IN (50 , 80);

select first_name, length(first_name) "expr1",
  last_name, length (last_name) "expr2",
  NULLIF(length(first_name), length(last_name)) result
from employees;

select last_name, employee_id,
  coalesce(to_char(commission_pct), to_char(manager_id),
    'No commission and no manager')
from employees;

select last_name, job_id, salary,
  case job_id when 'IT_PROG' then 1.10* salary
              when 'ST_CLERK' then 1.15*salary
              when 'SA_REP' then 1.20*salary
  else        salary end "revised_salary"
from employees;

select last_name, job_id, salary,
  DECODE(job_id, 'IT_PROG',  1.10*salary,
                  'ST_CLERK', 1.15*salary,
                  'SA_REP',   1.20*salary,
        salary) REVISED_SALARY
from   employees;

select last_name, salary,
  DECODE (TRUNC(salary/2000, 0),
                          0, 0.00,
                          1, 0.09,
                          2, 0.20,
                          3, 0.30,
                          4, 0.40,
                          5, 0.42,
                          6, 0.44,
                          0.45) TAX_RATE
from   employees
where  department_id = 80;

Lesson 03

select employee_id, last_name, department_id
from employees
where LOWER(last_name) = 'higgins';

select employee_id, concat ( first_name, last_name) Name,
      job_id, Length (last_name),
      instr (last_name, 'a') "Contains 'a'?"
from employees
where substr(job_id, 4 ) = 'REP';

select round (45.923,2), round(45.923,0),round(45.923,-1)
from dual;

select trunc(45.923,2),trunc(45.923),trunc(45.923,-1)
from dual;

select last_name, salary, mod(salary, 5000)
from employees
where job_id = 'SA_REP';

select last_name, hire_date
from employees
where hire_date < '01-FEB-88';

select sysdate
from dual;

select last_name, (sysdate-hire_date)/7 as Weeks
from employees
where department_id = 90;

Lesson 02

select employee_id, last_name, job_id, department_id
from employees
where department_id = 90;

select last_name, job_id, department_id
from employees
where last_name = 'Whalen';

select last_name
from employees
where hire_date = '17-feb-96';

select last_name, salary
from employees
where salary = 3000;

select last_name, salary
from employees
where salary between 2500 and 3500;

select employee_id, last_name, salary, manager_id
from employees
where manager_id in (100,101,201);

select first_name
from employees
where first_name like 'S%';

select last_name
from employees
where last_name like '_o%';

select last_name, manager_id
from employees
where manager_id is null;

select employee_id, last_name, job_id, salary
from employees
where salary >= 1000
and job_id like '%MAN%';

select employee_id, last_name, job_id, salary
from employees
where salary >= 1000
or job_id like '%MAN%';

select last_name, job_id
from employees
where job_id
not in ('IT_PROG','ST_CLERK','SA_REP');

select last_name, job_id, salary
from employees
where job_id = 'SA_ReP'
or job_id = 'AD_PRES'
and salary > 15000;

select last_name, job_id, department_id, hire_date
from employees
order by hire_date;

select last_name, job_id, department_id, hire_date
from employees
order by hire_date desc;

select last_name, job_id, department_id, hire_date
from employees
order by 3;

select last_name, department_id, salary
from employees
order by department_id, salary desc;

select employee_id, last_name, salary, department_id
from employees
where employee_id = &employee_num;

select last_name, department_id, salary *12
from employees
where job_id = '&job_title';

define employee_numb = 200

select employee_id, last_name, salary, department_id
from employees
where employee_id = &employee_numb

undefine employee_numb;

Lesson 01

Retrieving Data Using the SQL SELECT Statement

Basic SELECT Statement

Select *| {[DISTINCT] column|expression [alias],....}
From table;

Select identifies the columns to be displayed.
From identifies the table containing those columns.

Select *
From departments;

Writing SQL Statements

# SQL statements are not case-sensitive.
# SQL statements can be entered on one or more lines.
# Keywords cannot be abbreviated or split acros lines.
# Clauses are usually placed on separete lines.
# Indents are used to enhance readability.
# In SQL Developer, SQL statements can optionally be terminated by a semicolon(;).
# Semicolons are required when you execute multiple SQL statements.
# In SQL*Plus, you are required to end each SQL statement with a semicolon(;)

select department_id
from departments;

select last_name, salary, salary + 300
from employees;

select last_name, salary, 12*salary+100
from employees;

select last_name, salary, 12*(salary+100)
from employees;

select last_name, job_id, salary, commission_pct
from employees;

select last_name, 12*salary*commission_pct
from employees;

select last_name AS name, commission_pct com
from employees;

select last_name "name", salary*12 "annual salary"
from employees;

select last_name||job_id as "employees"
from employees;

select last_name || ' is a '|| job_id as "employee details"
from employees;

select department_name || q'[ Department's Manager ID:]' || manager_id as "department and manager"
from departments;

select department_id
from employees;

select distinct department_id
from employees;

describe employees;

SELECT first_name, last_name, job_id, salary AS yearly
FROM   employees;