Tuesday, November 9, 2010

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;

Tuesday, November 2, 2010

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;