Select lastname || ‘ is a ’ job_id as “employee details” from employees;
Select lastname from employees where lastname like ‘_0%’
Select employee_id , lastname, job_id from employees
where job_id like ‘%SAL%’ESCAPE’\_’;
Character functions:
Case manupilation
Lower
Upper
Initcap
Character manipulation
Concat (a,b)
Substr (a,m,n) //same as mid function
Length (a)
İnstr (a,b,m,n) //instr(‘Hello World’ , ‘w’)
Lpad (salary, 10, *) //(*****24000)
Rpad
Trim
Replace (text, search, replace)
Number functions:
Round //rounds value to the specified decimal
Round (45.926, 2) -> 45.93
Trunc //truncates to the specified decimal
Trunc (45.926,2) -> 45.92
Mod //return the remainder of the division
Dates:
Date + number = date
Date – number =date
Date – date = number of days
Select sysdate from dual; -- Default format DD-MON-RR
Months_between (date1, date2)
Add_months (date,n)
Next_day (date, ‘friday’) -- find the next specified day of the week
Last_day (date) -- the date of the last day of the month
Round (date)
Trunc (date)
EX:
Round (sysdate, ‘MONTH’)
Round (sysdate, ‘YEAR’)
Trunc (sysdate, ‘MONTH’)
Trunc (sysdate, ‘YEAR’)
Select employee_id , concat(fisrt_name, last_name) name, length (lastname) ,
instr(lastname, ‘a’) “contains ‘a’ ?” from employees where substr(lastname,-1,1) = ‘n’;
to_char (number | date, [fmt])
to_number (char, [fmt])
to_date (char, [fmt])
alter session set nls_date_format = ‘DD-MM-YYY HH24:MI:SS’
alter session set time_zone = ‘-8:00’
select sessiontimezone, dbtimezone, current_timestamp, current_date, sysdate from dual;
select extract (month from hire_date) from employees;
select to_timestamp(‘2000-12-01 11:00:00’ , ‘YYYY-MM-DD HH:MI:SS’)
Select employee_id , to_char (hire_date, ‘MM/YY’) from employees
where last_name = ‘higgins’;
// (YYYY,YEAR, MM, MONTH, MON, DAY, DD, DY, J)
(HH24, MI, SS)
select to_char (hire_Date, ‘fmDD Month YYYY‘);
select to_char (hire_date, ‘fmDdspth “of” month YYYY fmHH:MI:SS AM’)
select last_name, hire_date from employees
where hire_Date = TO_DATE(‘May 24, 1999’, ‘fxMonth DD, YYYY’)
select to_char (salary, ‘$99,999.00’)
9 – represents a number
0 – forces a zero to be displayed
$ - forces a floating ddler sign
. – prints a decimal point
, - prints a thousand indicator
nvl (expr1, expr2)
nvl2 (expr1, expr2, expr3)
nullif (expr1, expr2)
coalesce (expr1, expr2, ... , exprn)
case expr when comparison1 then expr1
when comparison2 then expr2
else expr3
end; alias
decode (col | expr, search1, result1,
serach2, result2,
default) alias;
decode (job_id , ‘IT_PROG’ , 1.10*SAL
‘SL_CLECK’, 1.15*SAL
SAL) “REVISED_SAL”;
Joins:
Select table1.column, table2.column from table1 , table2
where table1.column(+)=table2.column;
select table1.column, table2.column from table1
[CROSS join table2]
[NATURAL join table2]
[JOIN table2 USING column_name]
[JOIN table2 ON tbl1.column=tbl2.column]
[LEFT | RIGHT | FULL OUTER join table2 ON tbl1.column ) tbl2.column]
SQLPLUS
define employee_id = 200;
set verify on
set echo on
show echo
set heading off | on
show heading
show _variable_name
set arraysize 20
set feedback on | off
set ttitle on | off | text
set btitle on | off | text
set break on | off | text | column
column lastname justify left format $99,990.00
column lastname clear
col colname format a30
break on job_id
noprint
break [on report element]
clear
script file
print
null text
column colname option
Ex:
set feedback off
ttitle ‘employee | report’ --top title [text | on | off]
btitle ‘confidential’ --bottom title
break on job_id
column job_id heading ‘job / category’
column lat_name heading ‘employee’
column salary heading ‘salary’ format $99,999.99
REM ** insert select statement
Select job_id , last_name salary from employees where salary < 15000
Order by job_id, last_name;
/
REM ** clear all formatting commands
Set feedback on
Column job_id clear
Column last_name clear
Column salary clear
Clear break
Merge into:
Merge into table_name table_alias
Using (table) alias
On (join condition)
When matched then update set col1 = val1 , col2 = val2
When not matched then insert (col_list) values (val_list)
EX:
Merge into copy_emp c using employee e
on (c.employee_id = e.employee_id)
When matched then
update set c.firstname = e.firstname
...
when not matched then
insert values (e.emplyee_id ...)
Commit , rollback , savepoint:
Update ..
Savepoint update_done
İnsert
Rollback to update_done
Create table dept80 as select * from employees where department_id = 80;
Alter table table_name add (column_name datatype)
Alter table set unused column col_name
Alter table table_name modify (col_name datatype)
Alter table table_name drop (col_name)
Rename dept to dept_detail;
Truncate table detail_dept;
Select row_num as rank;
anargodjaev.wordpress.com 