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;
Tuesday, November 9, 2010
Lesson 04
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
From identifies the table containing those columns.
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(;)
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;
Subscribe to:
Posts (Atom)