Structured Query Language
DDL - Data Definition Language
------------------------------
Create
alter
drop
rename
truncate
DML- Data Manipulation Language
-------------------------------
Insert
Update
Delete
Select
TCL - Transaction Control Language
----------------------------------
Commit
Rollback
savepoint
DCL - Data Control language
---------------------------
grant
revoke
How to Create tables:-
Syntax
create table table_name (col1 datatype, col2 datatype, ......, coln datatype);
; -> is called Terminatable symbol or execute sysmbol
Eg
SQL> create table emp (empid number, empname varchar2(30), age number, doj date, salary number(7,2));
Table created.
How to insert values in to tables
SQL> insert into emp values (1001,'Rajesh',28,'21-JUN-2010',38000.00);
1 row created.
Interative mode insert:-
SQL> insert into emp values (&empid , '&empname', &age,'&doj',&salary);
Enter value for empid: 1002
Enter value for empname: Jameel
Enter value for age: 29
Enter value for doj: 20-JAN-2003
Enter value for salary: 35000.00
old 1: insert into emp values (&empid , '&empname', &age,'&doj',&salary)
new 1: insert into emp values (1002 , 'Jameel', 29,'20-JAN-2003',35000.00)
1 row created.
SQL> /
Enter value for empid: 1003
Enter value for empname: Madhan
Enter value for age: 28
Enter value for doj: 30-NOV-2008
Enter value for salary: 25000
old 1: insert into emp values (&empid , '&empname', &age,'&doj',&salary)
new 1: insert into emp values (1003 , 'Madhan', 28,'30-NOV-2008',25000)
1 row created.
/ -> This sysmbol is used to execute the lastly executed statement.
To insert null values or to insert only specific column then
SQL> insert into emp values (1001,NULL,28,'21-JUN-2010',38000.00);
1 row created.
SQL> insert into emp (empid, empname) values (1002,'Kareem');
1 row created.
Selecting from Table:-
SQL> select * from emp;
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1001 Rajesh 28 21-JUN-10 38000
1002 Jameel 29 20-JAN-03 35000
1003 Madhan 28 30-NOV-08 25000
1001 28 21-JUN-10 38000
1002 Kareem
SQL> select * from emp where age > 28;
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1002 Jameel 29 20-JAN-03 35000
SQL> select * from emp where age >= 28;
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1001 Rajesh 28 21-JUN-10 38000
1002 Jameel 29 20-JAN-03 35000
1003 Madhan 28 30-NOV-08 25000
1001 28 21-JUN-10 38000
SQL> select * from emp where salary < 30000;
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1003 Madhan 28 30-NOV-08 25000
Relational operators-
<, >, <=, >=, <>, =
Eg:-
SQL> select empname from emp where salary > 30000;
EMPNAME
------------------------------
Rajesh
Jameel
Logical Operators
and, or, not
SQL> select empname from emp where salary > 30000 and age = 28;
EMPNAME
------------------------------
Rajesh
SQL> select empname from emp where salary > 30000 or age > 28;
EMPNAME
------------------------------
Jameel
Character operator
between, in, not in , like , not like
Eg:-
SQL> select * from emp where salary between 30000 and 35000;
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1002 Jameel 29 20-JAN-03 35000
SQL> select * from emp where empid in (1000,1001,1002);
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1002 Jameel 29 20-JAN-03 35000
SQL> select * from emp where empid not in (1000,1001,1002);
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1003 Madhan 28 30-NOV-08 25000
SQL> select * from emp where empname like '%a%';
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1002 Jameel 29 20-JAN-03 35000
1003 Madhan 28 30-NOV-08 25000
Aliase:-
SQL> select empname as name from emp;
NAME
------------------------------
Manzoor
Jameel
Madhan
Concatination:- (||)
SQL> select empname || ' is getting monthly salary of Rs.' || salary as Salary_Details from emp;
SALARY_DETAILS
--------------------------------------------
Jameel is getting monthly salary of Rs.35000
Madhan is getting monthly salary of Rs.25000
View Table structure:-
desc table_name or describe table_name
The symbol != and ^= Represents not equal to <>
like
% - > denotes many character
_ - > denotes one character
eg
like 'SA_'
SAB
SAJ
like 'SA_E'
SAVE
SAME
Sorting
order by - asc (Ascending) or desc (desending) - Default is asc
SQL> select empname, salary from emp order by salary;
EMPNAME SALARY
------------------------------ ----------
Madhan 25000
Jameel 35000
Rajesh 38000
3 rows selected.
SQL> select empname, salary from emp order by salary desc;
EMPNAME SALARY
------------------------------ ----------
Rajesh 38000
Jameel 35000
Madhan 25000
SQL> select * from emp order by salary, doj;
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1003 Madhan 28 30-NOV-08 25000
1002 Jameel 29 20-JAN-03 35000
1001 Rajesh 28 21-JUN-10 38000
Single Row Functions:-
1) Character Functions.
2) Number Functions.
3) Date Functions.
4) Conversion Functions.
Conversion Funcations(Convert a value from one data type to another)
NVL, NVL2, NULLIF, COALESCE, CASE, DECODE
NVL(exp1,exp2)
- Converts nullto actual value, i.e if the exp1 is null then it will return the
value specified in exp2.
SQL> select empid, nvl(empname,'Name Not Known') from emp;
EMPID NVL(EMPNAME,'NAMENOTKNOWN')
---------- ------------------------------
1001 Rajesh
1002 Jameel
1003 Madhan
1004 Name Not Known
NVL2(exp1,exp2,exp3)
- if exp1 is not null then it will return exp2 , if the exp1 is null then it will
return exp3.
SQL> select empid, nvl2(empname,empname,'Name not known') from emp;
EMPID NVL2(EMPNAME,EMPNAME,'NAMENOTK
---------- ------------------------------
1001 Rajesh
1002 Jameel
1003 Madhan
1004 Name not known
NULLIF(exp1,exp2)
- retunrs null if both exp1 and exp2 are same or it will returns the exp1 if they are not same.
Eg.
NULLIF (length(first_name),length(last_name))
COALESCE(exp1, exp2,exp3,.....expn)
- Retunrs the first non null expression in the expressions lists.
Eg.
COALESCE(commpct,salary,10)
if commpact is null not then it will return commpact elseif
commpct is null and salary is not null then it will return salary elseif
commpct and salary are null then it will return 10.
CASE
SQL> select empid, empname, salary,
case
when salary > 36000 then 'A Grade'
when salary > 34000 and salary < 36000 then 'B Grade'
else 'Other Grade'
end case
from emp
/
EMPID EMPNAME SALARY CASE
----- ------------------------------ ---------- -----------
1001 Rajesh 38000 A Grade
1002 Jameel 35000 B Grade
1003 Madhan 25000 Other Grade
SQL> select empid, empname, salary,
case
when salary between 36000 and 40000 then 'A Grade'
when salary between 34000 and 35999 then 'B Grade'
else 'Other Grade'
end case
from emp
/
EMPID EMPNAME SALARY CASE
----- ------------------------------ ---------- -----------
1001 Rajesh 38000 A Grade
1002 Jameel 35000 B Grade
DECODE(col1,search1,result1,search2,result2.... default);
Eg:
SQL> select empid,empname, salary,
decode(salary,38000,'A Grade',35000,'B Grade','Other Grade') from emp;
EMPID EMPNAME SALARY DECODE(SALA
---------- ------------------------------ ---------- -----------
1001 Rajesh 38000 A Grade
1002 Jameel 35000 B Grade
1003 Madhan 25000 Other Grade
Character Functions:-
lower, upper, initcap, concat, substr, length, instr, lpad, rpad, trim,replace
Eg:-
lower,upper and initcap
SQL> select lower(empname) ,upper(empname), initcap(empname) from emp;
LOWER(EMPNAME) UPPER(EMPNAME) INITCAP(EMPNAME)
------------------------------ ------------------------------ -------------------
rajesh RAJESH Rajesh
jameel JAMEEL Jameel
madhan MADHAN Madhan
Concat
SQL> select concat(empname,concat('is getting salary of Rs.',salary)) from emp;
CONCAT(EMPNAME,CONCAT('ISGETTINGSALARYOFRS.',SALARY))
-------------------------------------------------------------------------------
Rajeshis getting salary of Rs.38000
Jameelis getting salary of Rs.35000
Madhanis getting salary of Rs.25000
substr,length,instr
SQL> select substr(empname,1,3) , length(empname), instr(empname,'a') from emp;
SUB LENGTH(EMPNAME) INSTR(EMPNAME,'A')
--- --------------- ------------------
Raj 6 2
Jam 6 2
Mad 6 2
lpad,rpad,trim
SQL> select lpad(salary,10,'*'), rpad(salary,10,'*'), trim('H' from 'Hellow') from emp;
LPAD(SALAR RPAD(SALAR TRIM(
---------- ---------- -----
*****38000 38000***** ellow
*****35000 35000***** ellow
*****25000 25000***** ellow
replace
SQL> select replace(empname,'a','B') from emp;
REPLACE(EMPNAME,'A','B')
------------------------------
RBjesh
JBmeel
MBdhBn
Number Functions:-
round, trunc,mod
SQL> select round(45.46) from dual;
ROUND(45.46)
------------
45
SQL> select round(45.56) from dual;
ROUND(45.56)
------------
46
SQL> select trunc(45.96) from dual;
TRUNC(45.96)
------------
45
SQL> select mod(100,3) from dual;
MOD(100,3)
----------
1
1 row selected.
SQL> select mod(100,2) from dual;
MOD(100,2)
----------
0
Dual - Dual is a dummy table which is used to view the results from functinons and calculations.
Date Functions:-
Arithmetic with dates:-
SQL> select sysdate, sysdate+1, sysdate-1 from dual;
SYSDATE SYSDATE+1 SYSDATE-1
------------------ ------------------ ------------------
12-DEC 10 12:12:30 13-DEC 10 12:12:30 11-DEC 10 12:12:30
SQL> select sysdate,sysdate+10/24 , sysdate-10/24 from dual;
SYSDATE SYSDATE+10/24 SYSDATE-10/24
------------------ ------------------ ------------------
12-DEC 10 12:13:03 12-DEC 10 22:13:03 12-DEC 10 02:13:03
SQL> select doj, empname || 'is Working for ' ||
trunc(sysdate-doj) || ' Days ' ||
trunc((sysdate-doj)/7) || ' Weeks' ||
trunc((sysdate-doj)/30 ) || ' Months'||
trunc((sysdate-doj)/365) || ' Years'
from emp
21-JUN-10 Rajeshis Working for 174 Days 24 Weeks 5 Months 0Years
20-JAN-03 Jameelis Working for 2883Days 411 Weeks 96 Months 7Years
30-NOV-08 Madhanis Working for 742 Days 106 Weeks 24 Months 2Years
months_between -> No. of months between 2 dates
add_months -> Add calendar months to date
next_day -> next day of the specified date
last_day -> last day of the month
round -> round date
trunc -> truncate date
Data type conversions:-
to_char, to_number, to_date
Store Date Format
select
to_char(sysdate,'SCC') "Cetury",
to_char(sysdate,'yyyy') "YYYY",
to_char(sysdate,'yyy') "YYY",
to_char(sysdate,'yy') "YY",
to_char(sysdate,'y') "Y",
to_char(sysdate,'y,yyy') "Y,YYY",
to_char(sysdate,'syear') "Spelled year",
to_char(sysdate,'year') "Spelled Year",
to_char(sysdate,'BC') "Cen",
to_char(sysdate,'AD') "Cen",
to_char(sysdate, 'Q') "Quarter",
to_char(sysdate,'MM') "Month",
to_char(sysdate,'Month') "Month",
to_char(sysdate,'RM') "Roman",
to_char(sysdate,'WW') "Week",
to_char(sysdate,'W') "Week Day",
to_char(sysdate,'DDD') "Day 3Char",
to_char(sysdate,'DD') "Date",
to_char(sysdate,'D') "Day",
to_char(sysdate,'DAY') "Day" ,
to_char(sysdate, 'DY') "Day",
to_char(sysdate, 'J') "Julian Day",
to_char(sysdate,'AM PM hh hh12 hh24 mi ss ssss') "Time"
from dual
Cet ----- 21
YYYY ----- 2010
YYY ----- 010
YY ----- 10
Y ----- 0
Y,YYY ----- 2,010
Spelled year ----- twenty ten
Spelled Year ----- twenty ten
Ce ----- AD
Ce ----- AD
Q ----- 4
Mo ----- 12
Month ----- December
Roma ----- XII
We ----- 50
W ----- 2
Day ----- 346
Da ----- 12
D ----- 1
Day ----- SUNDAY
Day ----- SUN
Julian Day ----- 2455543
Time ----- PM PM 12 12 12 52 17 1717
SQL> select to_char(sysdate,'DDTHSP') from dual;
TO_CHAR(SYSDAT
--------------
TWELFTH
SQL> select to_char(sysdate,'DDSP') from dual;
TO_CHAR(SYSD
------------
TWELVE
SQL> select to_char(sysdate,'DDTH') from dual;
TO_C
----
12TH
Joins:-
Equijoin, Non Equijoin, Outer Join, Self Join
Equijoin ( Also called simple join or inner join)
-------------------------------------------------
EG:-
SQL> select e.empname , e.age, d.name from emp e, department d where e.dept = d.dept;
EMPNAME AGE NAME
------------------------------ ---------- ------------------------------
Rajesh 28 Purchase
Jameel 29 Sales
Madhan 28 IT
Kannan 29 Infra
SQL> select e.empname , e.age, d.name from emp e, department d where e.dept = d.dept and e.empname = 'Rajesh';
EMPNAME AGE NAME
------------------------------ ---------- ------------------------------
Rajesh 28 Purchase
Join more the 2 tables:-
For n number of table joins we need n-1 joins
Eg:-
select e.last_name, d.deptname, l.city from emp e, department d, city l where
e.deptid = d.deptid and d.location = l.locationid;
Non-Equijoin
EG:-
SQL> select empname, salary from emp;
EMPNAME SALARY
------------------------------ ----------
Rajesh 38000
Jameel 35000
Madhan 25000
Kannan 40000
SQL> select * from salgrade;
MIN MAX G
---------- ---------- -
50000 1000000 A
25000 49599 B
10000 24999 C
Eg
SQL> select e.empname, e.salary , s.grade from emp e, salgrade s where
e.salary between s.min and s.max;
EMPNAME SALARY G
------------------------------ ---------- -
Madhan 25000 B
Jameel 35000 B
Rajesh 38000 B
Kannan 40000 B
Outer Join:-
You use the outer join too see all the rows that do not meet join condition.
The outer join operator is the (+) sign.
EG:-
SQL> select empname, dept from emp;
EMPNAME DEPT
------------------------------ ----------
Rajesh 1
Jameel 2
Madhan 3
Kannan 4
Raja
Ramiz
SQL> select dept,name from department;
DEPT NAME
---------- ------------------------------
1 Purchase
2 Sales
3 IT
4 Infra
5 Software
6 Hardware
In the below statement the Hardware and software department are exists in the department table but there is no any employees pertaining to those department,
this can be accomplished by using the outer join.
SQL> select e.empname, d.name from emp e, department d where e.dept(+) = d.dept;
EMPNAME NAME
------------------------------ ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Hardware
Software
Also to list all the employees with and without department query as below
SQL> select e.empname, d.name from emp e, department d where e.dept = d.dept(+);
EMPNAME NAME
------------------------------ ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Ramiz
Raja
Self join :-
Joining a table to itself.
SQL> select * from emp;
EMPID EMPNAME AGE DOJ SALARY DEPT MANAGER_ID
------ ---------- ---------- --------- ---------- ---------- ----------
1001 Rajesh 28 21-JUN-10 38000 1 1002
1002 Jameel 29 20-JAN-03 35000 2 1003
1003 Madhan 28 30-NOV-08 25000 3 1004
1004 Kannan 29 03-MAY-06 40000 4 1005
Now we want to list the empname and his manager name, see that each employ is having a manger id and that manger is also an employ. So we can use the self join to accomplish this task.
SQL> select e.empname || ' is report to ' || m.empname from emp e, emp m where e.manager_id = m.empid;
E.EMPNAME||'ISREPORTTO'||M.EMPNAME
--------------------------------------------------------------------------
Rajesh is report to Jameel
Jameel is report to Madhan
Madhan is report to Kannan
Cross join
1) It is the cross product of 2 tables.
2) Same as Cartesian product between 2 tables.
select last_name, department_name from emp cross join departments
Natural Join
It was not possible to do a join without explicitly specifying the columns in the corresponding tables in prior release of the oracle server. In oracle 9i it is possible to let the join be completed automatically based on columns in 2 tables which have matching data type names, using the keywords NATURAL JOIN .
Note. The natural join can only be happen on columns having same name and data-type in both the tales. if columns name are same but data-type is different it will throw an error.
Eg:-
SQL> select name, department_name , location_id, city from departments natural join location;
SQL> select name, department_name, location_id, city from departments natural join location where department_id in (20,25,30);
Using Clause:-
If several columns have the same name and data-types then do no match the natural join clause , it can be modified with the using clause to specify the columns that should be used for an equijoin notes, use the using clause to match only one columns where more than one columns are matched. Do not use a table name or alias name in the referenced column.
Eg:-
Select l.city , d.department_name from location l join department d using (location_id) where location_id = 1400;
select e.empid, e.name , d.location_id from emp e join department d using (dept_id);
On clause:-
1) To specify arbitary conditions or specify colunms to join, the on clause random is used.
2) Separates the join condition from other search conditions.
Eg:-
select e.empid,e.lastname,e.dept_id, d.deptid, d.location from emp e join department d on (e.department_id = d.department_id);
self join using on
select e.lastname, m.lastname from emp e join emp m on (e.manager_id = m.employee_id)
Joining more than 2 tables using on:-
select e.emp_id, d.deptname, l.city from emp e join department d on (e.employee_id = d.department_id) join location l on (d.location_id = l.location_id);
Left outer join:-
SQL> select e.empname, d.name from emp e left outer join department d on ( e.dept = d.dept);
EMPNAME NAME
---------- ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Ramiz
Raja
Right outer join:-
SQL> select e.empname, d.name from emp e right outer join department d on ( e.dept = d.dept);
EMPNAME NAME
---------- ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Hardware
Software
Full outer join
SQL> select e.empname, d.name from emp e full outer join department d on ( e.dept = d.dept);
EMPNAME NAME
---------- ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Ramiz
Raja
Hardware
Software
Group by Functions:-
avg, count, max, min, sum, stddev, variance.
Note:- You cannot use the where caluse to restirct group, you can use the having clause to restrict group.
Eg:-
select dept_id, avg(salary) from emp having avg(salary) > 8000 group by dept_id;
(or)
select dept_id, avg(salary) from emp group by dept_id having avg(salary) > 8000;
Merge Statement
Syntax:-
Merge into table_name as table_alias
using (table | view | subquery) as alias
on (join condition)
when matched then
update set
col1 = col1_val, col2 = col2_val
when not matched then
insert (columns_list) values
(columns_value);
Eg:-
SQL> select * from copyof_emp;
EMPID EMPNAME AGE DOJ SALARY DEPT MANAGER_ID
---------- ---------- ---------- --------- ---------- ---------- ----------
1001 Rajesh 28 21-JUN-10 38000 1 1002
1002 Jameel 29 20-JAN-03 35000 2 1003
SQL> select * from emp;
EMPID EMPNAME AGE DOJ SALARY DEPT MANAGER_ID
---------- ---------- ---------- --------- ---------- ---------- ----------
1001 Rajesh 28 21-JUN-10 38000 1 1002
1002 Jameel 29 20-JAN-03 35000 2 1003
1003 Madhan 28 30-NOV-08 25000 3 1004
1004 Kannan 29 03-MAY-06 40000 4 1005
Merge into copyof_emp c
using emp e
on (c.empid = e.empid)
when matched then
update set
c.empname = e.empname,c.age=e.age,c.doj=e.doj,c.salary=e.salary,c.dept=e.dept,c.manager_id=e.manager_id
when not matched then
insert values
(e.empid,e.empname,e.age,e.doj,e.salary,e.dept,e.manager_id);
SQL> select * from copyof_emp;
EMPID EMPNAME AGE DOJ SALARY DEPT MANAGER_ID
---------- ---------- ---------- --------- ---------- ---------- ----------
1001 Rajesh 28 21-JUN-10 38000 1 1002
1002 Jameel 29 20-JAN-03 35000 2 1003
1003 Madhan 28 30-NOV-08 25000 3 1004
1004 Kannan 29 03-MAY-06 40000 4 1005
Query For Date Difference
select
EXTRACT(DAY FROM numtodsinterval(3.22,'DAY')) || ' days '
|| EXTRACT(HOUR FROM numtodsinterval(3.22,'DAY')) || ' hours '
|| EXTRACT(MINUTE FROM numtodsinterval(3.22,'DAY')) || ' minutes '
|| EXTRACT(SECOND FROM numtodsinterval(3.22,'DAY')) || ' seconds' from dual
/
Check the below for more functions....
http://www.java2s.com/Tutorial/Oracle/0200__SQL-Data-Types/Catalog0200__SQL-Data-Types.htm
DDL - Data Definition Language
------------------------------
Create
alter
drop
rename
truncate
DML- Data Manipulation Language
-------------------------------
Insert
Update
Delete
Select
TCL - Transaction Control Language
----------------------------------
Commit
Rollback
savepoint
DCL - Data Control language
---------------------------
grant
revoke
How to Create tables:-
Syntax
create table table_name (col1 datatype, col2 datatype, ......, coln datatype);
; -> is called Terminatable symbol or execute sysmbol
Eg
SQL> create table emp (empid number, empname varchar2(30), age number, doj date, salary number(7,2));
Table created.
How to insert values in to tables
SQL> insert into emp values (1001,'Rajesh',28,'21-JUN-2010',38000.00);
1 row created.
Interative mode insert:-
SQL> insert into emp values (&empid , '&empname', &age,'&doj',&salary);
Enter value for empid: 1002
Enter value for empname: Jameel
Enter value for age: 29
Enter value for doj: 20-JAN-2003
Enter value for salary: 35000.00
old 1: insert into emp values (&empid , '&empname', &age,'&doj',&salary)
new 1: insert into emp values (1002 , 'Jameel', 29,'20-JAN-2003',35000.00)
1 row created.
SQL> /
Enter value for empid: 1003
Enter value for empname: Madhan
Enter value for age: 28
Enter value for doj: 30-NOV-2008
Enter value for salary: 25000
old 1: insert into emp values (&empid , '&empname', &age,'&doj',&salary)
new 1: insert into emp values (1003 , 'Madhan', 28,'30-NOV-2008',25000)
1 row created.
/ -> This sysmbol is used to execute the lastly executed statement.
To insert null values or to insert only specific column then
SQL> insert into emp values (1001,NULL,28,'21-JUN-2010',38000.00);
1 row created.
SQL> insert into emp (empid, empname) values (1002,'Kareem');
1 row created.
Selecting from Table:-
SQL> select * from emp;
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1001 Rajesh 28 21-JUN-10 38000
1002 Jameel 29 20-JAN-03 35000
1003 Madhan 28 30-NOV-08 25000
1001 28 21-JUN-10 38000
1002 Kareem
SQL> select * from emp where age > 28;
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1002 Jameel 29 20-JAN-03 35000
SQL> select * from emp where age >= 28;
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1001 Rajesh 28 21-JUN-10 38000
1002 Jameel 29 20-JAN-03 35000
1003 Madhan 28 30-NOV-08 25000
1001 28 21-JUN-10 38000
SQL> select * from emp where salary < 30000;
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1003 Madhan 28 30-NOV-08 25000
Relational operators-
<, >, <=, >=, <>, =
Eg:-
SQL> select empname from emp where salary > 30000;
EMPNAME
------------------------------
Rajesh
Jameel
Logical Operators
and, or, not
SQL> select empname from emp where salary > 30000 and age = 28;
EMPNAME
------------------------------
Rajesh
SQL> select empname from emp where salary > 30000 or age > 28;
EMPNAME
------------------------------
Jameel
Character operator
between, in, not in , like , not like
Eg:-
SQL> select * from emp where salary between 30000 and 35000;
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1002 Jameel 29 20-JAN-03 35000
SQL> select * from emp where empid in (1000,1001,1002);
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1002 Jameel 29 20-JAN-03 35000
SQL> select * from emp where empid not in (1000,1001,1002);
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1003 Madhan 28 30-NOV-08 25000
SQL> select * from emp where empname like '%a%';
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1002 Jameel 29 20-JAN-03 35000
1003 Madhan 28 30-NOV-08 25000
Aliase:-
SQL> select empname as name from emp;
NAME
------------------------------
Manzoor
Jameel
Madhan
Concatination:- (||)
SQL> select empname || ' is getting monthly salary of Rs.' || salary as Salary_Details from emp;
SALARY_DETAILS
--------------------------------------------
Jameel is getting monthly salary of Rs.35000
Madhan is getting monthly salary of Rs.25000
View Table structure:-
desc table_name or describe table_name
The symbol != and ^= Represents not equal to <>
like
% - > denotes many character
_ - > denotes one character
eg
like 'SA_'
SAB
SAJ
like 'SA_E'
SAVE
SAME
Sorting
order by - asc (Ascending) or desc (desending) - Default is asc
SQL> select empname, salary from emp order by salary;
EMPNAME SALARY
------------------------------ ----------
Madhan 25000
Jameel 35000
Rajesh 38000
3 rows selected.
SQL> select empname, salary from emp order by salary desc;
EMPNAME SALARY
------------------------------ ----------
Rajesh 38000
Jameel 35000
Madhan 25000
SQL> select * from emp order by salary, doj;
EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1003 Madhan 28 30-NOV-08 25000
1002 Jameel 29 20-JAN-03 35000
1001 Rajesh 28 21-JUN-10 38000
Single Row Functions:-
1) Character Functions.
2) Number Functions.
3) Date Functions.
4) Conversion Functions.
Conversion Funcations(Convert a value from one data type to another)
NVL, NVL2, NULLIF, COALESCE, CASE, DECODE
NVL(exp1,exp2)
- Converts nullto actual value, i.e if the exp1 is null then it will return the
value specified in exp2.
SQL> select empid, nvl(empname,'Name Not Known') from emp;
EMPID NVL(EMPNAME,'NAMENOTKNOWN')
---------- ------------------------------
1001 Rajesh
1002 Jameel
1003 Madhan
1004 Name Not Known
NVL2(exp1,exp2,exp3)
- if exp1 is not null then it will return exp2 , if the exp1 is null then it will
return exp3.
SQL> select empid, nvl2(empname,empname,'Name not known') from emp;
EMPID NVL2(EMPNAME,EMPNAME,'NAMENOTK
---------- ------------------------------
1001 Rajesh
1002 Jameel
1003 Madhan
1004 Name not known
NULLIF(exp1,exp2)
- retunrs null if both exp1 and exp2 are same or it will returns the exp1 if they are not same.
Eg.
NULLIF (length(first_name),length(last_name))
COALESCE(exp1, exp2,exp3,.....expn)
- Retunrs the first non null expression in the expressions lists.
Eg.
COALESCE(commpct,salary,10)
if commpact is null not then it will return commpact elseif
commpct is null and salary is not null then it will return salary elseif
commpct and salary are null then it will return 10.
CASE
SQL> select empid, empname, salary,
case
when salary > 36000 then 'A Grade'
when salary > 34000 and salary < 36000 then 'B Grade'
else 'Other Grade'
end case
from emp
/
EMPID EMPNAME SALARY CASE
----- ------------------------------ ---------- -----------
1001 Rajesh 38000 A Grade
1002 Jameel 35000 B Grade
1003 Madhan 25000 Other Grade
SQL> select empid, empname, salary,
case
when salary between 36000 and 40000 then 'A Grade'
when salary between 34000 and 35999 then 'B Grade'
else 'Other Grade'
end case
from emp
/
EMPID EMPNAME SALARY CASE
----- ------------------------------ ---------- -----------
1001 Rajesh 38000 A Grade
1002 Jameel 35000 B Grade
DECODE(col1,search1,result1,search2,result2.... default);
Eg:
SQL> select empid,empname, salary,
decode(salary,38000,'A Grade',35000,'B Grade','Other Grade') from emp;
EMPID EMPNAME SALARY DECODE(SALA
---------- ------------------------------ ---------- -----------
1001 Rajesh 38000 A Grade
1002 Jameel 35000 B Grade
1003 Madhan 25000 Other Grade
Character Functions:-
lower, upper, initcap, concat, substr, length, instr, lpad, rpad, trim,replace
Eg:-
lower,upper and initcap
SQL> select lower(empname) ,upper(empname), initcap(empname) from emp;
LOWER(EMPNAME) UPPER(EMPNAME) INITCAP(EMPNAME)
------------------------------ ------------------------------ -------------------
rajesh RAJESH Rajesh
jameel JAMEEL Jameel
madhan MADHAN Madhan
Concat
SQL> select concat(empname,concat('is getting salary of Rs.',salary)) from emp;
CONCAT(EMPNAME,CONCAT('ISGETTINGSALARYOFRS.',SALARY))
-------------------------------------------------------------------------------
Rajeshis getting salary of Rs.38000
Jameelis getting salary of Rs.35000
Madhanis getting salary of Rs.25000
substr,length,instr
SQL> select substr(empname,1,3) , length(empname), instr(empname,'a') from emp;
SUB LENGTH(EMPNAME) INSTR(EMPNAME,'A')
--- --------------- ------------------
Raj 6 2
Jam 6 2
Mad 6 2
lpad,rpad,trim
SQL> select lpad(salary,10,'*'), rpad(salary,10,'*'), trim('H' from 'Hellow') from emp;
LPAD(SALAR RPAD(SALAR TRIM(
---------- ---------- -----
*****38000 38000***** ellow
*****35000 35000***** ellow
*****25000 25000***** ellow
replace
SQL> select replace(empname,'a','B') from emp;
REPLACE(EMPNAME,'A','B')
------------------------------
RBjesh
JBmeel
MBdhBn
Number Functions:-
round, trunc,mod
SQL> select round(45.46) from dual;
ROUND(45.46)
------------
45
SQL> select round(45.56) from dual;
ROUND(45.56)
------------
46
SQL> select trunc(45.96) from dual;
TRUNC(45.96)
------------
45
SQL> select mod(100,3) from dual;
MOD(100,3)
----------
1
1 row selected.
SQL> select mod(100,2) from dual;
MOD(100,2)
----------
0
Dual - Dual is a dummy table which is used to view the results from functinons and calculations.
Date Functions:-
Arithmetic with dates:-
SQL> select sysdate, sysdate+1, sysdate-1 from dual;
SYSDATE SYSDATE+1 SYSDATE-1
------------------ ------------------ ------------------
12-DEC 10 12:12:30 13-DEC 10 12:12:30 11-DEC 10 12:12:30
SQL> select sysdate,sysdate+10/24 , sysdate-10/24 from dual;
SYSDATE SYSDATE+10/24 SYSDATE-10/24
------------------ ------------------ ------------------
12-DEC 10 12:13:03 12-DEC 10 22:13:03 12-DEC 10 02:13:03
SQL> select doj, empname || 'is Working for ' ||
trunc(sysdate-doj) || ' Days ' ||
trunc((sysdate-doj)/7) || ' Weeks' ||
trunc((sysdate-doj)/30 ) || ' Months'||
trunc((sysdate-doj)/365) || ' Years'
from emp
21-JUN-10 Rajeshis Working for 174 Days 24 Weeks 5 Months 0Years
20-JAN-03 Jameelis Working for 2883Days 411 Weeks 96 Months 7Years
30-NOV-08 Madhanis Working for 742 Days 106 Weeks 24 Months 2Years
months_between -> No. of months between 2 dates
add_months -> Add calendar months to date
next_day -> next day of the specified date
last_day -> last day of the month
round -> round date
trunc -> truncate date
Data type conversions:-
to_char, to_number, to_date
Store Date Format
select
to_char(sysdate,'SCC') "Cetury",
to_char(sysdate,'yyyy') "YYYY",
to_char(sysdate,'yyy') "YYY",
to_char(sysdate,'yy') "YY",
to_char(sysdate,'y') "Y",
to_char(sysdate,'y,yyy') "Y,YYY",
to_char(sysdate,'syear') "Spelled year",
to_char(sysdate,'year') "Spelled Year",
to_char(sysdate,'BC') "Cen",
to_char(sysdate,'AD') "Cen",
to_char(sysdate, 'Q') "Quarter",
to_char(sysdate,'MM') "Month",
to_char(sysdate,'Month') "Month",
to_char(sysdate,'RM') "Roman",
to_char(sysdate,'WW') "Week",
to_char(sysdate,'W') "Week Day",
to_char(sysdate,'DDD') "Day 3Char",
to_char(sysdate,'DD') "Date",
to_char(sysdate,'D') "Day",
to_char(sysdate,'DAY') "Day" ,
to_char(sysdate, 'DY') "Day",
to_char(sysdate, 'J') "Julian Day",
to_char(sysdate,'AM PM hh hh12 hh24 mi ss ssss') "Time"
from dual
Cet ----- 21
YYYY ----- 2010
YYY ----- 010
YY ----- 10
Y ----- 0
Y,YYY ----- 2,010
Spelled year ----- twenty ten
Spelled Year ----- twenty ten
Ce ----- AD
Ce ----- AD
Q ----- 4
Mo ----- 12
Month ----- December
Roma ----- XII
We ----- 50
W ----- 2
Day ----- 346
Da ----- 12
D ----- 1
Day ----- SUNDAY
Day ----- SUN
Julian Day ----- 2455543
Time ----- PM PM 12 12 12 52 17 1717
SQL> select to_char(sysdate,'DDTHSP') from dual;
TO_CHAR(SYSDAT
--------------
TWELFTH
SQL> select to_char(sysdate,'DDSP') from dual;
TO_CHAR(SYSD
------------
TWELVE
SQL> select to_char(sysdate,'DDTH') from dual;
TO_C
----
12TH
Joins:-
Equijoin, Non Equijoin, Outer Join, Self Join
Equijoin ( Also called simple join or inner join)
-------------------------------------------------
EG:-
SQL> select e.empname , e.age, d.name from emp e, department d where e.dept = d.dept;
EMPNAME AGE NAME
------------------------------ ---------- ------------------------------
Rajesh 28 Purchase
Jameel 29 Sales
Madhan 28 IT
Kannan 29 Infra
SQL> select e.empname , e.age, d.name from emp e, department d where e.dept = d.dept and e.empname = 'Rajesh';
EMPNAME AGE NAME
------------------------------ ---------- ------------------------------
Rajesh 28 Purchase
Join more the 2 tables:-
For n number of table joins we need n-1 joins
Eg:-
select e.last_name, d.deptname, l.city from emp e, department d, city l where
e.deptid = d.deptid and d.location = l.locationid;
Non-Equijoin
EG:-
SQL> select empname, salary from emp;
EMPNAME SALARY
------------------------------ ----------
Rajesh 38000
Jameel 35000
Madhan 25000
Kannan 40000
SQL> select * from salgrade;
MIN MAX G
---------- ---------- -
50000 1000000 A
25000 49599 B
10000 24999 C
Eg
SQL> select e.empname, e.salary , s.grade from emp e, salgrade s where
e.salary between s.min and s.max;
EMPNAME SALARY G
------------------------------ ---------- -
Madhan 25000 B
Jameel 35000 B
Rajesh 38000 B
Kannan 40000 B
Outer Join:-
You use the outer join too see all the rows that do not meet join condition.
The outer join operator is the (+) sign.
EG:-
SQL> select empname, dept from emp;
EMPNAME DEPT
------------------------------ ----------
Rajesh 1
Jameel 2
Madhan 3
Kannan 4
Raja
Ramiz
SQL> select dept,name from department;
DEPT NAME
---------- ------------------------------
1 Purchase
2 Sales
3 IT
4 Infra
5 Software
6 Hardware
In the below statement the Hardware and software department are exists in the department table but there is no any employees pertaining to those department,
this can be accomplished by using the outer join.
SQL> select e.empname, d.name from emp e, department d where e.dept(+) = d.dept;
EMPNAME NAME
------------------------------ ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Hardware
Software
Also to list all the employees with and without department query as below
SQL> select e.empname, d.name from emp e, department d where e.dept = d.dept(+);
EMPNAME NAME
------------------------------ ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Ramiz
Raja
Self join :-
Joining a table to itself.
SQL> select * from emp;
EMPID EMPNAME AGE DOJ SALARY DEPT MANAGER_ID
------ ---------- ---------- --------- ---------- ---------- ----------
1001 Rajesh 28 21-JUN-10 38000 1 1002
1002 Jameel 29 20-JAN-03 35000 2 1003
1003 Madhan 28 30-NOV-08 25000 3 1004
1004 Kannan 29 03-MAY-06 40000 4 1005
Now we want to list the empname and his manager name, see that each employ is having a manger id and that manger is also an employ. So we can use the self join to accomplish this task.
SQL> select e.empname || ' is report to ' || m.empname from emp e, emp m where e.manager_id = m.empid;
E.EMPNAME||'ISREPORTTO'||M.EMPNAME
--------------------------------------------------------------------------
Rajesh is report to Jameel
Jameel is report to Madhan
Madhan is report to Kannan
Cross join
1) It is the cross product of 2 tables.
2) Same as Cartesian product between 2 tables.
select last_name, department_name from emp cross join departments
Natural Join
It was not possible to do a join without explicitly specifying the columns in the corresponding tables in prior release of the oracle server. In oracle 9i it is possible to let the join be completed automatically based on columns in 2 tables which have matching data type names, using the keywords NATURAL JOIN .
Note. The natural join can only be happen on columns having same name and data-type in both the tales. if columns name are same but data-type is different it will throw an error.
Eg:-
SQL> select name, department_name , location_id, city from departments natural join location;
SQL> select name, department_name, location_id, city from departments natural join location where department_id in (20,25,30);
Using Clause:-
If several columns have the same name and data-types then do no match the natural join clause , it can be modified with the using clause to specify the columns that should be used for an equijoin notes, use the using clause to match only one columns where more than one columns are matched. Do not use a table name or alias name in the referenced column.
Eg:-
Select l.city , d.department_name from location l join department d using (location_id) where location_id = 1400;
select e.empid, e.name , d.location_id from emp e join department d using (dept_id);
On clause:-
1) To specify arbitary conditions or specify colunms to join, the on clause random is used.
2) Separates the join condition from other search conditions.
Eg:-
select e.empid,e.lastname,e.dept_id, d.deptid, d.location from emp e join department d on (e.department_id = d.department_id);
self join using on
select e.lastname, m.lastname from emp e join emp m on (e.manager_id = m.employee_id)
Joining more than 2 tables using on:-
select e.emp_id, d.deptname, l.city from emp e join department d on (e.employee_id = d.department_id) join location l on (d.location_id = l.location_id);
Left outer join:-
SQL> select e.empname, d.name from emp e left outer join department d on ( e.dept = d.dept);
EMPNAME NAME
---------- ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Ramiz
Raja
Right outer join:-
SQL> select e.empname, d.name from emp e right outer join department d on ( e.dept = d.dept);
EMPNAME NAME
---------- ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Hardware
Software
Full outer join
SQL> select e.empname, d.name from emp e full outer join department d on ( e.dept = d.dept);
EMPNAME NAME
---------- ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Ramiz
Raja
Hardware
Software
Group by Functions:-
avg, count, max, min, sum, stddev, variance.
Note:- You cannot use the where caluse to restirct group, you can use the having clause to restrict group.
Eg:-
select dept_id, avg(salary) from emp having avg(salary) > 8000 group by dept_id;
(or)
select dept_id, avg(salary) from emp group by dept_id having avg(salary) > 8000;
Merge Statement
Syntax:-
Merge into table_name as table_alias
using (table | view | subquery) as alias
on (join condition)
when matched then
update set
col1 = col1_val, col2 = col2_val
when not matched then
insert (columns_list) values
(columns_value);
Eg:-
SQL> select * from copyof_emp;
EMPID EMPNAME AGE DOJ SALARY DEPT MANAGER_ID
---------- ---------- ---------- --------- ---------- ---------- ----------
1001 Rajesh 28 21-JUN-10 38000 1 1002
1002 Jameel 29 20-JAN-03 35000 2 1003
SQL> select * from emp;
EMPID EMPNAME AGE DOJ SALARY DEPT MANAGER_ID
---------- ---------- ---------- --------- ---------- ---------- ----------
1001 Rajesh 28 21-JUN-10 38000 1 1002
1002 Jameel 29 20-JAN-03 35000 2 1003
1003 Madhan 28 30-NOV-08 25000 3 1004
1004 Kannan 29 03-MAY-06 40000 4 1005
Merge into copyof_emp c
using emp e
on (c.empid = e.empid)
when matched then
update set
c.empname = e.empname,c.age=e.age,c.doj=e.doj,c.salary=e.salary,c.dept=e.dept,c.manager_id=e.manager_id
when not matched then
insert values
(e.empid,e.empname,e.age,e.doj,e.salary,e.dept,e.manager_id);
SQL> select * from copyof_emp;
EMPID EMPNAME AGE DOJ SALARY DEPT MANAGER_ID
---------- ---------- ---------- --------- ---------- ---------- ----------
1001 Rajesh 28 21-JUN-10 38000 1 1002
1002 Jameel 29 20-JAN-03 35000 2 1003
1003 Madhan 28 30-NOV-08 25000 3 1004
1004 Kannan 29 03-MAY-06 40000 4 1005
Query For Date Difference
select
EXTRACT(DAY FROM numtodsinterval(3.22,'DAY')) || ' days '
|| EXTRACT(HOUR FROM numtodsinterval(3.22,'DAY')) || ' hours '
|| EXTRACT(MINUTE FROM numtodsinterval(3.22,'DAY')) || ' minutes '
|| EXTRACT(SECOND FROM numtodsinterval(3.22,'DAY')) || ' seconds' from dual
/
Check the below for more functions....
http://www.java2s.com/Tutorial/Oracle/0200__SQL-Data-Types/Catalog0200__SQL-Data-Types.htm
No comments:
Post a Comment