Sunday 29 July 2012

SQL - Basics

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

No comments:

Post a Comment