/*
* A table can have maximum 254 columns
* table name - maximum 30 characters
* column name - maximum 30 characters
* constraint can be Primary key, not null, unique,
foreign key, check or user define
* Only one primary key per table is allowed
*/
create table dept_new
(
deptno number(2) constraint pk_dept_new primary key,
dname varchar2(10) constraint nn_dept_new_dname not null,
loc varchar2(20),
constraint u_dname_loc unique(dname,loc)
);
create table emp_new
(
empno number(4),
ename varchar2(10) constraint nn_emp_new_ename not null,
job char(10) constraint c_emp_new_job
check (job=upper(job) and job is not null),
mgr number(4) constraint fk_emp_new_mgr
REFERENCES emp_new(empno),
joindate date default sysdate,
sal number(8,2),
comm number(8,2),
deptno number(2) constraint nn_emp_new_deptno not null,
constraint fk_emp_new_deptno FOREIGN KEY (deptno)
REFERENCES dept_new(deptno) ON DELETE CASCADE,
constraint pk_emp_new primary key (empno),
constraint c_emp_new_sal_comm check ( sal+nvl(comm,0) <= 10000)
);
/*
Note
----
* Dept_new table should be created first before
the employee table because dept_new.deptno has
been used in employee table as referential
integrity constraint
* Integrity Constraint (can be specified at Column/Table level)
* NOT NULL - We must provide a value, null would not be
allowed. It can be specified at column level only.
* UNIQUE - Not allow duplicate members for that column.
BUT it will allow NULL values.
* PRIMARY KEY - Automatically enforces as UNIQUE and NOT NULL.
A table can have only ONE primary key
* FOREIGN KEY - (1) They can reference only primary key
(2) Automatic validation is done
(3) There can be multiple foreign key in a table
(4) A foreign key will not allow NULLS
* CHECK - (1) Check condition will fail the operation if
condition evaluates to false
(2) Check refer to columns of same table
* CONSTRAINT - Column Level
* Given along with column defination and
applies to that column only
* NOT NULL always comes in column level
Table Level
* Can apply to one or more columns
*/
-- To create a table using SELECT Statement
-- This will create the table & also insert the records of CLERK
Create table emp_new1 as
SELECT empno,ename,sal+500 Sal, deptno
FROM emp
Where job = 'CLERK';
-- To create a table using SELECT Statement
-- This will only create the table & not insert the records
Create table emp_new1 as
SELECT empno,ename,sal+500 Salary, deptno
FROM emp
Where 1 = 2;
Drop table emp_new;
Drop table dept_new
/*
Drop table cannot drop of the relation has been setup using
foreign key. But if we want to forcibly drop it we have to
use CASCADE CONSTRAINTS
*/
-- Renaming a Table
Rename emp_new to emp_new2;
------------------------------------------------------------------------------------------------
The SELECT statement is used to select data from a database.
The result is stored in a result table, called the result-set.
SELECT [ALL | DISTINCT]
{* | column_name, ...}
FROM table_name alias,
[WHERE expressions]
[GROUP BY expression4]
[HAVING expression5]
[UNION | INTERSECT | MINUS SELECT ...]
[ORDER BY expression |ASC | DESC];
-------------------------------------------------------------------------------------------------
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
/*
* We cannot drop a particular column
(except from Oracle-8i onwards)
* New Column added later receives NULL values
* Reducing width is allowed ONLY IF the column contained
NULL in all the existing rows.
*/
Alter table emp_new
ADD ( martial_status CHAR(1),
qualification VARCHAR2(2000)
);
-- To add NULL CONSTRAINT
Alter table emp_new
MODIFY martial_status CHAR(2) NOT NULL;
-- To check martial status value 'MA' or 'SI'
Alter table emp_new
ADD CHECK (martial_status IN ('MA','SI') );
Alter table emp_new
MODIFY ename varchar2(15);
-- Due to some reason we have to drop a constraint
/*
* We cannot MODIFY a constraint, we have to DELETE
and CREATE it again
ALTER TABLE <table_name>
DROP CONSTRAINT <constraint_name>
Constraint Name - It is unique, It is stored in data
dictionary. To see the name of it
we have to
SELECT * From USER_CONSTRAINTS
WHERE table_name = 'EMP_NEW';
* It is therefore advisable to give Constraint Name
*/
ALTER TABLE emp_new
DROP CONSTRAINT C_EMP_NEW_JOB;
-------------------------------------------------------------------------------------------------------------------------
The WHERE clause is used to extract only those records that fulfill a specified criterion.
/*
* To form condition(s) for selecting (filtering)
specific rows
* First clause that is applied in SELECT statements
WHERE cluse could contain
-------------------------
* Filter condition(s)
* Join condition(s)
* A sub query on the right hand side of the conditions
It should be written in parentheses
*/
-- Simple Filter Conditions
select ename,sal from emp
where job='CLERK';
-- Join Condition
select ename,sal,dname
from emp,dept
where emp.deptno = dept.deptno;
-- Sub Query
select * from emp
where deptno = (select deptno from dept
where loc = 'CHICAGO');
-- To see deptno 10 or 30
select ename,deptno
from emp
where deptno = 10 or deptno = 30;
select ename,deptno
from emp
where deptno in (10,30);
select ename,deptno
from emp
where deptno not between 10 and 30;
-- To see deptno 10 and salary >=3000
select *
from emp
where deptno = 10 and sal >=3000;
-- To see deptno 10 & salary >=3000 or hiredate >='01-JAN-93'
select *
from emp
where deptno = 10
and (sal >= 3000 or hiredate >= '01-JAN-1993');
-- To see all dept except 20
select *
from emp
where deptno <> 20;
select *
from emp
where NOT deptno = 20;
select *
from emp
where deptno NOT IN (20);
-- Find the employees who do not receive commission
-- or whose comm is less than 100
select *
from emp
where comm is null or comm < 100;
-------------------------------------------------------------------------------------------------------------
The ORDER BY keyword is used to sort the result-set by one or more columns.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.
/*
* It is the last clause specified and to be executed
* It arranges and sort rows before returning them to
the applications.
* It can go upto 16 key/expression for ordering
*/
-- Simple sorting
select * from emp
order by ename desc;
-- Sorting by hiredate and salary in descending for
-- job = 'CLERK'
select * from emp
where job = 'CLERK'
order by hiredate desc, sal desc;
-- POSITIONAL NOTATION
select ename,sal,sal*0.1+nvl(comm,0),deptno
from emp
order by 4 desc, 1 asc;
----------------------------------------------------------------------------------------------------------------
* It operates on a group of row & returns one value
* By default entire (or filtered) table forms a group
Own group can be specified by Group By clause
* If Select List contains an aggregate function,
then it can contain only any one of the following
* Any other aggregare function(s)
* constant values
* Only those columns that appears in GROUP BY clause
* We have SUM([Distinct] expN)
AVG([Distinct] expN)
MAX([Distinct] expN)
MIN([Distinct] expN)
STDEV([Distinct] expN)
COUNT(* | [Distinct] expN)
Select count(*), count(mgr),count( DISTINCT mgr)
From EMP;
COUNT(*) COUNT(MGR) COUNT(DISTINCTMGR)
--------- ---------- ------------------
14 13 6
Select min(hiredate), max(sal), sum(comm), avg(sal), avg(comm)
From EMP
where deptno in (20,30);
MIN(HIRED MAX(SAL) SUM(COMM) AVG(SAL) AVG(COMM)
--------- --------- --------- --------- ---------
17-DEC-80 3000 2200 1843.1818 550
Select deptno,min(hiredate),max(sal),
sum(comm),avg(sal),avg(comm)
From EMP
where deptno in (20,30)
group by deptno;
DEPTNO MIN(HIRED MAX(SAL) SUM(COMM) AVG(SAL) AVG(COMM)
--------- --------- --------- --------- --------- ---------
20 17-DEC-80 3000 2175
30 20-FEB-81 2850 2200 1566.6667 550
---------------------------------------------------------------------------------------------------------------------------------
/*
* Group is always dependent on common values in a field
* Grouping should not be done on columns which are unique or
primary key
SELCT grouping_column_list, aggregate_function(column)
FROM table_name
GROUP BY grouping_column_list
*/
/*
Only the following can appear in the SELECT list,
if the query contains a GROUP BY clause
1. Columns or Expressions that appear in GROUP BY clause
2. Aggregate functions (SUM, COUNT, ...)
3. Constant Expresions or literals (sysdate, 'Hello', 0 ,...)
*/
-- To get minimum salary for each category of job
select job,min(sal),max(sal),round(avg(sal),0),count(*)
from emp
group by job;
JOB MIN(SAL) MAX(SAL) ROUND(AVG(SAL),0) COUNT(*)
--------- --------- --------- ----------------- ---------
ANALYST 3000 3000 3000 2
CLERK -2500 1100 88 4
MANAGER 2450 2975 2758 3
PRESIDENT 5000 5000 5000 1
SALESMAN 1250 1600 1400 4
select deptno,min(sal),max(sal),round(avg(sal),0),count(*)
from emp
group by deptno;
DEPTNO MIN(SAL) MAX(SAL) ROUND(AVG(SAL),0) COUNT(*)
-------- --------- --------- ----------------- ---------
10 -2500 5000 1650 3
20 800 3000 2175 5
30 950 2850 1567 6
-- To give list of clerks in a dept which
-- should be 2 or more in one dept;
select deptno, count(*) "No of Clerks"
from emp
where job = 'CLERK'
group by deptno
having count(*) >= 2
order by 2;
DEPTNO No of Clerks
-------- ------------
10 1
30 1
20 2
-- After adding Having Clause
DEPTNO No of Clerks
--------- ------------
20 2
/*
Note
----
GROUP BY cluse should be used with columns which
can be grouped and HAVING clause should be used
for functions which can be applied on that group.
WHERE clause acts on a single row while
HAVING acts on groups of rows
*/
--------------------------------------------------------------------------------------------------------
/*
* To extract data or information from two or more tables
* Condition(s) for joining is specified in WHERE clause
* There are 4 types of Joins
EQUI Join, NON EQUI Join, OUTER Join, SELF Join
* If no join conditions is specified when more than one table is used
the result will be CARTESIAN PRODUCT
* If N tables are joined, to avoid CARTESIAN PRODUCT atleat (N-1)
joining conditions must be there in Where Clause. If N = 3 then
2 joining conditions must be there in the where clause
*/
-- EQUI JOIN (Natural Join)
-- Two tables are joined on the basis of equality conditions
select ename,job,sal,dept.deptno,dname
from emp,dept
where emp.deptno = dept.deptno;
-- NON EQUI JOIN
-- Tow tables are joined on the basis of non-equality conditions
-- (Except equal everything is used)
select emp.deptno,ename,dept.deptno,dname,loc
from emp,dept
where emp.deptno > dept.deptno;
select empno, ename, e.deptno, dname, sal, 'Grade:'||grade "Grade"
from emp e, dept d, salgrade s
where e.deptno = d.deptno and
e.sal between s.losal and s.hisal;
-- OUTER JOIN
/*
* In Outer Join we preserve rows of one table even if no matching
rows are found in other table. To create outer join we have to
put (+) after that table column, which will provide dummy rows
for the purpose of preserving the rows of the dominant table.
* Two-sided Outer Join are not allowed
*/
-- List of all employee name along with names of their departments
-- even if they don't have any employee in it
select empno,ename,dname
from dept,emp
where emp.deptno(+) = dept.deptno;
-- SELF JOIN
/*
* Same table can be used as two different tables, say A & B
both can be joined as joining the two different table.
Here table alias name A & B are compulsory
*/
-- List all the name of employee and their manager
-- (Note that in a single record only name of employee exists
-- and WHERE clause is applicable only for single record
-- access at a time) so assuming that EMP table is at two
-- places, say A & B, get employee name from A & corresponding
-- manager name from B using SELF JOIN Method
select A.empno "Empno", A.ename "Employee", a.job ,
B.empno "Mgr Empno",B.ename "Manager", b.job "Mgr-Desig"
from emp A, emp B
where a.mgr = b.empno(+);
select A.empno "Empno", A.ename "Employee",
count(B.mgr) "No of Subordinates"
from emp A, emp B
where a.empno = b.mgr
group by A.empno,A.ename
order by 2;
------------------------------------------------------------------------------------------------------------------
/*
* A SELECT statement nested with another SELECT statement
* A sub-query can appear only on Right Hand Side of any
condition in WHERE / HAVING clause
* There are two kinds of sub query (Normal & Co-related)
* We cannot use ORDER BY clause in a sub-query
*/
-- NORMAL SUB QUERY
-- It will give dept 40
-- As there are no employee for this dept.
--
select * from dept
where deptno NOT IN (Select distinct deptno
from emp
where deptno is not null);
-- This will give all the employee from dept 20
-- And their Salary is greater or equal to SALES
select * from emp
where deptno = 20
and sal >= (select max(sal) from emp
where deptno = (select deptno from dept
where dname='SALES'));
-- List of all the employee who earns
-- Maximum Salary in their department
select * from emp
where (deptno,sal) IN (select deptno, max(sal)
from emp
group by deptno)
order by deptno,ename;
-- CORELATED SUB QUERY
/*
Sub Query done from the same table i.e. table of the outer and
inner query is same. The sub-query executes once for each row
of parent query
It is called Corelated Sub-Query beacuse it refers to some
column(s) of parent query in any conditions
*/
-- It gives all those employee who earns above average
-- salary in their department
Select * from emp outer
Where sal > (select avg(sal) from emp
where deptno = outer.deptno);
-- Top 5 Salaries of the organization
Select * from emp a
Where 5 >= (select count(distinct sal) from emp
where sal >= a.sal)
order by sal desc;
-- List of all employees who has somebody reporting to them
select * from Emp a
Where exists (select 'Dummy' from emp
where mgr = a.empno);
-- * will be displayed if the employee gets highest salary
-- in the respective department
column eno format a6 heading Empno
Select decode(sal,maxsal,'* ',' ')||to_char(empno) eno,
ename,sal,emp.deptno,dname,avgsal,sal-avgsal,
decode(sign(sal-avgsal),1,'>',-1,'<','=') sstat
From emp,
(select deptno,max(sal) maxsal, avg(sal) avgsal
from emp
group by deptno) d,
dept
Where emp.deptno = d.deptno
and dept.deptno = emp.deptno
order by emp.deptno,ename;
-- It will give Dept 1st Highest & 2nd Highest Salary
select dept.deptno,dname,max_sal "Highest Sal",max(sal) "2nd Highest Sal"
from dept, emp, (select deptno,max(sal) max_sal from emp group by deptno) ms
where dept.deptno=emp.deptno(+)
and dept.deptno=ms.deptno(+)
and nvl(sal,0) < nvl(max_sal,1)
group by dept.deptno,dname,max_sal;
----------------------------------------------------------------------------------------------------------------------
DML
Insert into dept_new
values (10, 'EDP','MUMBAI');
Insert into dept_new
values (20, '&Dept_name','&Loc');
commit;
create table emp_new2
as select * from emp where 1=2;
insert into emp_new2
select * from emp where deptno = 20;
commit;
update emp
set ename = 'JOHNY'
where ename = 'JOHN';
update emp_new2
set sal = (select max(sal) from emp)
where job = 'MANAGER'
and deptno = (SELECT deptno from dept
where dname = 'SALES');
Delete from emp_new2; -- All the rows will be deleted
---------------------------------------------------------------------------------------------------------------
Set Opertors
/*
create table n1 ( name varchar2(20) );
create table n2 ( name varchar2(20) );
insert into n1 VALUES('SACHIN');
insert into n1 VALUES('SAURAV');
insert into n1 VALUES('DRAVID');
insert into n1 VALUES('LAKSHMAN');
insert into n1 VALUES('ZAHEER');
insert into n2 VALUES('SACHIN');
insert into n2 VALUES('SAURAV');
insert into n2 VALUES('KAPIL');
insert into n2 VALUES('SRIKANT');
insert into n2 VALUES('GAVASKAR');
insert into n2 VALUES('AMARNATH');
insert into n2 VALUES('BINNY');
*/
-- SET OPERATORS (UNION, INTERSECT, MINUS)
/*
Rules for SET Operations
* Number of columns should be same
* Data type must be the same
* Column names are always picked up from the first
select statement
* Order By can appear only after last SELECT Statement
*/
-- UNION
/*
NAME (N1) (5 ROWS) NAME (N2) (7 ROWS)
------------------ ------------------
SACHIN SACHIN
SAURAV SAURAV
DRAVID KAPIL
LAKSHMAN SRIKANT
ZAHEER GAVASKAR
AMARNATH
BINNY
*/
-- This will combine two table columns but repeated values
-- will be shown only once
-- It will give 10 rows
SELECT NAME FROM N1
UNION
SELECT NAME FROM N2;
-- This will combine two table columns
-- but repeated values will be ALSO be shown
-- It will give 12 rows
SELECT NAME FROM N1
UNION ALL
SELECT NAME FROM N2;
-- INTERSECT (Set of common members in both tables)
/*
NAME (N1) (5 ROWS) NAME (N2) (7 ROWS)
------------------ ------------------
SACHIN SACHIN
SAURAV SAURAV
DRAVID KAPIL
LAKSHMAN SRIKANT
ZAHEER GAVASKAR
AMARNATH
BINNY
*/
-- Only repeated name will be shown
-- It will give 2 rows
SELECT NAME FROM N1
INTERSECT
SELECT NAME FROM N2;
-- MINUS
-- It is used for subtraction and result remaining members
-- from the BASE TABLE
/*
NAME (N1) (5 ROWS) NAME (N2) (7 ROWS)
------------------ ------------------
SACHIN SACHIN
SAURAV SAURAV
DRAVID KAPIL
LAKSHMAN SRIKANT
ZAHEER GAVASKAR
AMARNATH
BINNY
*/
-- It will give names of N1 which is not existing in N2
-- It will give 3 rows (DRAVID,LAKSHMAN,ZAHEER)
SELECT NAME FROM N1
MINUS
SELECT NAME FROM N2;
-------------------------------------------------------------------------------------------------------------------
OPERATORS
=========
Arithmetic Character Relational Logical
---------- --------- ---------- --------------------------------
+ - * / || < > c1 AND c2
Concatenation = != c1 OR c2
<> NOT condition
<= >= ... [NOT] IN .... | SUBQUERY
e1 [NOT] BETWEEN e2 AND e3
exp IS [NOT] NULL
char_expr1 [NOT] LIKE char_expr2
[NOT] EXISTS <SUBQUERY>
LIKE OPERATOR (Wild Card)
==============================
% Zero or more occurances
of any char
_ (Underscore) One occurances
of any char
Like Example
------------
Suppose you want to find out'%' OR '_' which is enclosed in the name field. You will have to give '% \% %' OR '% \_ %'. Here \ indicate that anything after this should be ignored.
Select * from emp where ename like '%\%%' ESCAPE '\';.
Other Operators
---------------
OUTER JOIN Operator
Set Operator [UNION,INTERSCECT,MINUS]
------------------------------------------------------------------------------------------------
PseudoColumns
/*
* ROWNUM
* ROWID
* LEVEL
* CURRVAL
* NEXTVAL
*/
ROWNUM
------
Select ROWNUM, empno,enmae,job from emp
Where Rownum <=5;
Note : It will give 5 rows.
ROWID
-----
It is unique address of a row in a database.
Please refer Data_Types for more details.
LEVEL
-----
It is a TREE like structure (Hierarchy).
List SMITH and his managers i.e. List of all employees
who are managers of SMITH.
Select * from emp
Connect By empno= PRIOR mgr
Start with ename = 'SMITH';
Graphical Presentation
----------------------
column en format a30 heading Emp-Name
select lpad(' ',(level-1)*3)||ename en,empno,job,mgr,level
from emp
--where level <= 3
connect by prior empno=mgr
start with job='PRESIDENT';
CURRVAL & NEXTVAL
-----------------
We have already discussed in Sequence. Please refer to it.
----------------------------------------------------------------------------------------------------------------------
/*
* Indexed are used for faster access / retrieval
* Indexes are automatically updated in case of DML Operations
* QUERY OPTIMIZER decides on the basis of the
query (and other informations) whether to use any index or not
and which index to use.
The command to create index is
create index i_ename on emp(ename);
Here I_ename is Index name, emp is table name
and ename is table column name upto 16 columns
*/
-- Query Optimizer will use the index
select * from emp
where empno = 7980;
-- Query Optimizer will NOT use the index
select * from emp
where empno <> 7980;
-----------------------------------------------------------------------------------------------------------------------
/*
* View is a Stored query table based on one or more tables
or views
* View can be used as if it is an actual table
* View does not contain data
* Whenever the view is accessed the query is evaluated.
Moreover, any changes made in data through view are
automatically updated in concerned table. Thus it is
dynamic
* View cannot be modified, it has to be dropped
* The purpose of View is for security and it can be used
to project specific columns
* View definition cannot have an ORDER BY clause
*/
Create or replace View empview as
Select * from emp where sal >= 4000;
Create or replace View V1 as
Select empno,ename,job DESIG,sal SALARY,
sal+nvl(comm,0) Total,deptno
From emp;
Note : All expressions must have an alias name.
DML OPERATIONS in Views
-----------------------
* Insert into empview values(1279,'Arjun',....,20)
Here a row would be inserted into emp
* Insert into empview values(1280,'Abhay',....,35)
Here a row would be inserted into emp with deptno 35.
Deptno 35 is a wrong value but since no check is available
it would allow to insert in it.
To overcome the above problem you have to give
"WITH CHECK OPTION"
Create or replace View empview
as Select * from emp where sal >= 4000
WITH CHECK OPTION;
* DML Operations are restricted in views if view defination
contain any one of the following
- Derieved Columns
- Joins
- Aggregate Functions (sum,max,min,...)
- DISTINCT keyword - Select Distinct Job from emp;
* We can have a view within a view
To see the views on your machine
It has 3 views in Data Dictionary
(1) USER_VIEW - All views created by a user
(2) ALL_VIEW - All Views created by a user &
to which you have rights
(3) DBA_VIEW - It is available only to DBA
To see the contents of a view, say
DESCRIVE view;
To delete a view
DROP VIEW <view_name>
Create FORCE view as select empno,ename,deptno
from emp;
Note : If you give FORCE it does only syntax checking. It
will allow you to create a view even if table is not
there. But while selecting the table should be there.
Otherwise it will give error.
-----------------------------------------------------------------------------------------
* Synonyms are objects for referring objects with another name
* Existing object can be name of a table or a view or sequence
* Public Synonyms can be referred by all users
who have necessary permissions on the base object.
* It is useful in hiding ownership and location details of an
object.
* You cannot ALTER a SYNONYM. Synonyms don't have any permissions.
*/
create synonym emp1 for emp_new;
drop synonym emp1;
-----------------------------------------------------------------------------------------------
CREATE SEQUENCE seq_name
[ INCREMENT BY n1 ] [ START WITH n2 ]
[ MAXVALUE n3 ] [ MINVALUE n4 ]
[ CYCLE | NOCYCLE ]
Default values
--------------
MINVALUE = 1
MAXVALUE = 32 Digit
INCREMENT = 1
START WITH = Same as MINVALUE 1
NOCYCLE
* Sequence is an object to generate sequential numbers.
* Cycle indicated that once the extreme is reached, start
the cycle again.
create sequence seqempno
Start With 101
Increment By 1
Maxvalue 1101;
Alter Sequence seqempno
Increment By 2
Maxvalue 200;
Drop Sequence seqempno;
Note
----
* To make the sequence start from a new number drop the sequence
and create it again
* ALTER SEQUENCE has no effect on numbers already generated
* Sequence cannot be ROLLBACK
Sequence refers to 2 Psuedocolumn
---------------------------------
<sequence name>.CURRVAL
<sequence name>.NEXTVAL
Examples
--------
insert into emp values(<sequence name>.NEXTVAL, 'Ajay',...);
Select <sequence name>.CURRVAL from dual;
Select <sequence name>.NEXTVAL from dual;
---------------------------------------------------------------------------------------------------------------------
-- Greatest(e1,e2,e3, ....)
-- It returns greatest of all.
-- It should be of similar data types
select greatest(500*0.1,300),
greatest(500*0.1, 500, 25*17-1.5*20)
from dual;
GREATEST(500*0.1,300) GREATEST(500*0.1,500,25*17-1.5*20)
--------------------- ----------------------------------
300 500
cl scr;
-- Least(e1,e2,e3, ....)
-- It returns Least of all.
-- It should be of similar data types
select least(500*0.1,300),
least('ABC','Abc','abc','aBC')
from dual;
LEAST(500*0.1,300) LEA
------------------ ---
50 ABC
cl scr;
-- NVl(e1,e2)
-- Both the argument e1 & e2 should be of same data type
-- If e1 evaluates to NULL then it would subsitute to e2
select empno,ename,sal,comm,sal+nvl(comm,0)
from emp where deptno=30;
EMPNO ENAME SAL COMM SAL+NVL(COMM,0)
--------- ---------- --------- --------- ---------------
7698 BLAKE 2850 2850
7654 MARTIN 1250 1400 2650
7499 ALLEN 1600 300 1900
7844 TURNER 1500 0 1500
7900 JAMES 950 950
7521 WARD 1250 500 1750
cl scr;
-- DECODE(expr, v1, e1, v2, e2, [else default])
-- It will evaluate expr and try to compare with e1
-- If it matches then v1 will be displayed
-- Otherwise it will proceed to e2 or default value if there
--
select decode(10, 1, 'One', 2, 'Two', 'xxx'),
decode(10, 1, 'One', 10, 'Ten', 'xxx')
from dual;
DEC DEC
--- ---
xxx Ten
cl scr;
-- User
-- Displays the Username
--
select user from dual;
USER
------------------------------
SCOTT
-- Lists Name, Empno, Salary, Equal/Below/Above (1500)
-- for all those employees whose earning (sal+comm) is atleast 800
select ename,
empno,
sal,
decode(sign(sal-1500), 0, 'Equal', -1, 'Below', 'Above') "Status"
from emp
where nvl(sal+comm,sal) >= 800;
ENAME EMPNO SAL Statu
---------- --------- --------- -----
KING 7839 5000 Above
BLAKE 7698 2850 Above
CLARK 7782 2450 Above
JONES 7566 2975 Above
MARTIN 7654 1250 Below
ALLEN 7499 1600 Above
TURNER 7844 1500 Equal
JAMES 7900 950 Below
WARD 7521 1250 Below
FORD 7902 3000 Above
SMITH 7369 800 Below
SCOTT 7788 3000 Above
ADAMS 7876 1100 Below
-------------------------------------------------------------------------------------------------------------
Numeric Function
-- ABS(n)
-- Returns a positive number. It ignores sign
Select ABS(-5.5), ABS(5.5) from dual;
ABS(-5.5) ABS(5.5)
--------- ---------
5.5 5.5
-- MOD(m,n)
-- Gives remainder after a decimal for a number
-- Mod(5,23) Integer divisible not possible
Select MOD(23,5), MOD(5,23) from dual;
MOD(23,5) MOD(5,23)
--------- ---------
3 5
-- POWER(m,n)
-- Gives Power of a number
Select Power(2,3) from dual;
POWER(2,3)
----------
8
-- SIGN(n)
-- If n is POSITIVE it returns 1
-- If n is NEGATIVE it returns -1
-- If n is EQUAL TO 0 it returns 0
Select sign(22), sign(-22.5), sign(0) from dual;
SIGN(22) SIGN(-22.5) SIGN(0)
--------- ----------- ---------
1 -1 0
-- SQRT(n)
-- Gives the Square Root for a number
Select SQRT(16), SQRT(25) from dual;
SQRT(16) SQRT(25)
--------- ---------
4 5
-- TRUNC(m [,n])
-- Removes Decimal without Rounding off
Select TRUNC(2672.673),
TRUNC(2672.673,1),
TRUNC(2672.673,-2)
from dual;
TRUNC(2672.673) TRUNC(2672.673,1) TRUNC(2672.673,-2)
--------------- ----------------- ------------------
2672 2672.6 2600
-- ROUND(m [,n])
-- Returns the number to rounded off to n2 places
-- If n is not specified then it is 0
Select ROUND(2672.673),
ROUND(2672.673,1),
ROUND(2672.673,2),
ROUND(2672.673,-2)
from dual;
ROUND(2672.673) ROUND(2672.673,1) ROUND(2672.673,2) ROUND(2672.673,-2)
--------------- ----------------- ----------------- ------------------
2673 2672.7 2672.67 2700
-- CEIL(n)
-- Returns the greatest integer greater or equal to n
Select CEIL(23.21), CEIL(27.82), CEIL(-27.82) from dual;
CEIL(23.21) CEIL(27.82) CEIL(-27.82)
----------- ----------- ------------
24 28 -27
-- FLOOR(n)
-- Returns the LARGEST integer lesser or equal to n
Select floor(23.21), floor(27.82), floor(-27.82) from dual;
FLOOR(23.21) FLOOR(27.82) FLOOR(-27.82)
------------ ------------ -------------
23 27 -28
--------------------------------------------------------------------------------------------------
Date function
cl scr;
/*
DATE FORMATS
============
* it stores century and seconds (Date & Time)
* It supports lots of format
* Default format is 'DD-MON-YY' for accepting and displaying dates.
DD Day of Month (1-31)
D Day of Week (1-7)
DDD Day of Year (365)
YYYY Century of Year (2001)
YYY Last 3 digit (001)
YY Last 2 digit (01)
Y Last 1 digit (1)
RETURNS NUMBER
==============
CC Century (21)
Q Quarter (3)
W Week of the month (4)
WW Week of the year (35)
RETURNS CHARACTER
=================
MON First 3 alphabet character of month (AUG)
MONTH Entire word (AUGUST)
DY Spell Day of Week (First 3 alphabets) (WED)
DAY Complete spelling of the day (WEDNESDAY)
YEAR Spelled out the year
HH/HH12 12 Hrs Format
HH24 24 Hrs Format
MI Minutes
SS Seconds
SSSSS Seconds since Midnight
SUFFIX
======
th Adds suffix th/nd/st/rd to a number format
e.g. : 5th, 2nd, 23rd, 31st
sp Spelled out number format
e.g. : five, two, three, thirty-one
spth or thsp Spelled out with suffix
e.g. : fifth, second, twenty-third, thirty-first
PREFIX
======
fm Removes trailing spaces from Day/Month formats
*/
select sysdate,to_char(sysdate, 'dd/mm/yyyy')
from dual;
SYSDATE TO_CHAR(SY
--------- ----------
07-SEP-01 07/09/2001
select sysdate,to_char(sysdate, 'DDD-D-DD-MM/CC-Q-W-WW-YYY')
from dual;
SYSDATE TO_CHAR(SYSDATE,'DDD-D-DD
--------- -------------------------
07-SEP-01 250-6-07-09/21-3-1-36-001
select sysdate,to_char(sysdate, 'Mon, dd, YYYY')
from dual;
SYSDATE TO_CHAR(SYSDA
--------- -------------
07-SEP-01 Sep, 07, 2001
select sysdate,to_char(sysdate, 'DY, MON, DD, HH12:MI:SSAM')
from dual;
SYSDATE TO_CHAR(SYSDATE,'DY,MON,
--------- ------------------------
07-SEP-01 FRI, SEP, 07, 10:35:34AM
select sysdate,to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS')
from dual;
SYSDATE TO_CHAR(SYSDATE,'DD-
--------- --------------------
07-SEP-01 07-Sep-2001 10:35:50
select To_char(sysdate, 'ddth "of" Month, YYYY')
from dual;
TO_CHAR(SYSDATE,'DDTH"O
-----------------------
07th of July , 2001
select To_char(sysdate, 'fmDdthsp "Of" Month, YYYY')
from dual;
TO_CHAR(SYSDATE,'FMDDTHSP"OF"MONT
---------------------------------
Seventh Of July, 2001
/*
DATE ARITHMETIC
===============
D + n => Date n days hence
D - n => Date n days before
D - D => Number of days between 2 Dates
D + D => ERROR (Not Valid)
D + n/24 => Date n hours hence
D - n/24/60 => Date n minutes before
(D-D) *24*60*60 => Number of seconds between 2 Date×
*/
select sysdate,sysdate+3,sysdate-3 from dual;
SYSDATE SYSDATE+3 SYSDATE-3
--------- --------- ---------
07-SEP-01 10-SEP-01 04-SEP-01
select sysdate, sysdate-to_date('10-AUG-2001') from dual;
SYSDATE SYSDATE-TO_DATE('10-AUG-2001')
--------- ------------------------------
07-SEP-01 28.442766
select sysdate, sysdate-to_date('10-08-2001','DD-MM-YYYY') from dual;
SYSDATE SYSDATE-TO_DATE('10-08-2001','DD-MM-YYYY')
--------- ------------------------------------------
07-SEP-01 28.442917
/* DATE FUNCTIONS
==============
*/
cl scr;
-- ADD_Months(d1,n)
-- To add n months from the Date d1
--
Select sysdate, add_months(sysdate, 3), add_months(sysdate, -3)
from dual;
SYSDATE ADD_MONTH ADD_MONTH
--------- --------- ---------
07-SEP-01 07-DEC-01 07-JUN-01
cl scr;
-- Months_Between(d1,d2)
-- Returns the months passed between 2 dates
--
Select trunc(months_between(sysdate, to_date('01-APR-1972')),0)
from dual;
TRUNC(MONTHS_BETWEEN(SYSDATE,TO_DATE('01-APR-1972')),0)
-------------------------------------------------------
353
cl scr;
-- LAST_DAY(d)
-- Gives the last day of month for the specified date
--
Select sysdate, last_day(sysdate)
from dual;
SYSDATE LAST_DAY(
--------- ---------
07-SEP-01 30-SEP-01
cl scr;
-- NEXT_DAY(d,c)
-- To find out the next day occurance from the specified date
--
Select sysdate, next_day(sysdate,'MON')
from dual;
SYSDATE NEXT_DAY(
--------- ---------
07-SEP-01 10-SEP-01
cl scr;
-- Round of mm Gives First Day of Next Month
-- Trunc of mm Gives First Day of Current Month
--
Select sysdate, round(sysdate,'mm'), trunc(sysdate,'mm')
from dual;
SYSDATE ROUND(SYS TRUNC(SYS
--------- --------- ---------
07-SEP-01 01-SEP-01 01-SEP-01
cl scr;
-- Round of Q Gives First Day of Next Quarter
-- Trunc of Q Gives First Day of Current Quarter
--
Select sysdate, round(sysdate,'Q'), trunc(sysdate,'Q')
from dual;
SYSDATE ROUND(SYS TRUNC(SYS
--------- --------- ---------
07-SEP-01 01-OCT-01 01-JUL-01
cl scr;
-- Round of YY Gives First Day of Next Year
-- Trunc of YY Gives First Day of Current Year
--
Select sysdate, round(sysdate,'YY'), trunc(sysdate,'YY')
from dual;
SYSDATE ROUND(SYS TRUNC(SYS
--------- --------- ---------
07-SEP-01 01-JAN-02 01-JAN-01
cl scr;
-- Round of DD Gives Next Day if time has crossed 12
-- Otherwise same day as sysdate
-- Trunc of DD Gives Same day as Sysdate
--
Select to_char(sysdate,'dd-mm-yyyy hh:mi:ss'),
round(sysdate,'DD'), trunc(sysdate,'DD')
from dual;
-- Before 12 noon
TO_CHAR(SYSDATE,'DD-MM ROUND(SYS TRUNC(SYS
---------------------- --------- ---------
07-09-2001 11:00:13 am 07-SEP-01 07-SEP-01
-- After 12 noon
TO_CHAR(SYSDATE,'DD-MM ROUND(SYS TRUNC(SYS
---------------------- --------- ---------
07-09-2001 13:00:38 pm 08-SEP-01 07-SEP-01
-----------------------------------------------------------------------------------------------------------
Char Function.
-- CHR(n)
-- It accepts a number and returns a character
Select chr(65), chr(101), chr(132) from dual;
-- Output
C C C
- - -
A e „
cl scr;
-- LOWER(c)
-- It changes string to lower case
Select Lower('ICICI LTD.') from dual;
-- Output
LOWER('ICI
----------
icici ltd.
cl scr;
-- UPPER(c)
-- It changes string to upper case
Select Upper('icici Ltd.') from dual;
-- Output
UPPER('ICI
----------
ICICI LTD.
cl scr;
-- INITCAP(c)
-- It changes string to Title case
Select initcap('SAchin TEndulKAr') from dual;
-- Output
INITCAP('SACHINT
----------------
Sachin Tendulkar
cl scr;
-- ASCII(c)
-- It returns the ASCII Value of the first
-- character of the string
Select ascii('Amitabh'), ascii('amitabh') from dual;
-- Output
ASCII('AMITABH') ASCII('AMITABH')
---------------- ----------------
65 97
cl scr;
-- LTRIM(c1 [,c2])
-- It is used to remove LEADING blank spaces &
-- also removes the character from begining of string
Select Ltrim(' Mumbai'),
Ltrim('Mumbai', 'M'),
Ltrim('Mumbai', 'm') from dual;
-- Output
LTRIM( LTRIM LTRIM(
------ ----- ------
Mumbai umbai Mumbai
cl scr;
-- RTRIM(c1 [,c2])
-- It is used to remove TRAILING blank spaces &
-- also removes the character from end of string
Select Rtrim('MUMBAI '),
Rtrim('MUMBAI','I'),
Rtrim('MUMBAI','i') from dual;
-- Output
RTRIM( RTRIM RTRIM(
------ ----- ------
MUMBAI MUMBA MUMBAI
cl scr;
-- LPAD(c1,n [,c2])
-- Add string c2 before c1 as many times as to make
-- the length of string c1 n characters
Select lpad('MUMBAI',10),
lpad('MUMBAI',10, '*') from dual;
-- Output
LPAD('MUMB LPAD('MUMB
---------- ----------
MUMBAI ****MUMBAI
cl scr;
-- RPAD(c1,n [,c2])
-- Add string c2 after c1 as many times as to make
-- the length of string c1 n characters
Select rpad('MUMBAI',10),
rpad('MUMBAI',10, '*') from dual;
-- Output
RPAD('MUMB RPAD('MUMB
---------- ----------
MUMBAI MUMBAI****
cl scr;
-- SUBSTR(c1,m [,n])
-- It is used to remove character from abywahere in
-- the string, m indicates start and n is optional
-- If specified it will remove only that much character
-- otherwise all
Select substr('MUMBAI',3),
substr('MUMBAI',3,2),
substr('MUMBAI',-3),
substr('MUMBAI',-3,2) from dual;
-- Output
SUBS SU SUB SU
---- -- --- --
MBAI MB BAI BA
cl scr;
-- Soundex(c)
-- It returns an unique number if the words are similar in sound
Select soundex('LEELA'), soundex('LILA'),
soundex('MEELA')
from dual;
-- Output
SOUN SOUN SOUN
---- ---- ----
L400 L400 M400
cl scr;
-- Replace(c1,c2 [,c3])
-- Within c1 finds c2 replaces it with c3, c3 is
-- optional argument. If not specified it will
-- find c2 and remove it
Select replace('TURNER','ER','AN'),
replace('RAMESH','ER','AN')
from dual;
-- Output
REPLAC REPLAC
------ ------
TURNAN RAMESH
cl scr;
-- Translate(c1,from, to)
-- It finds one to one and replaces it.
-- If it doesn't find replacement then it removes it
Select translate('TURNER','ER','AN'),
translate('RAMESH','ER','AN')
from dual;
-- Output
TRANSL TRANSL
------ ------
TUNNAN NAMASH
cl scr;
-- Instr(c1,c2 [,m [,n]] )
-- It searches c2 in c1 Returns 0 if not found
-- If c2 is found in c1 Returns the position of it
-- If m specified then it starts searching from there
-- If n specified then it check for that occurence
Prompt ' '
Prompt 'CORPORATE WORLD'
Prompt ' '
Select Instr('CORPORATE WORLD','OR') "0",
Instr('CORPORATE WORLD','OR',5) "5",
Instr('CORPORATE WORLD','OR',3,2) "3,2"
from dual;
-- Output
0 5 3,2
--------- --------- ---------
2 5 12
cl scr;
-- LENGTH(c)
-- It returns the length of the string
Select length('Sachin ') from dual;
-- Output
LENGTH('SACHIN')
----------------
8
cl scr;
-- To_CHAR(n [,format string])
-- 9 Digit
-- , Comma
-- . Dit
-- - Sign
-- B Blank if Zero
-- PR Negative in <>
Select to_char(2500, '99,99,999.99'),
to_char(-2500, '99,99,999.99PR'),
to_char(10, 'B99,99,999.99PR')
from dual;
-- Output
TO_CHAR(2500, TO_CHAR(-2500,' TO_CHAR(0,'$B99
------------- --------------- ---------------
2,500.00 <2,500.00>
cl scr;
-- To_NUMBER(c )
Select to_number('2500') from dual;
--------------------------------------------------------------------------------------
No comments:
Post a Comment