LOB data type(Large objects datatype):

CLOB: Character large objects ->4GB-data base memory

BLOB:Binary large objects->4GB- data base memory

Bfile:4GB->memory allocation in O.S it takes memory form(O.S)

Table:

Which is nothing but logical representation of rows and columns.

table name naming conventions for object we can follow below conventions:

1. Name should not more than 30 characters.

2. It should start with Alphabet.
3. It should be a complete word,no gap between words.
4. Allowable special characters are #,_$.
5. Numbers also we can use 0to9.
6. We should not use reserved words(keywords).
7. Object name should not already exists in the database in user schema.

1. Employ-> entity

2. Employ->attributes - empno,ename,job,hiredate,sal,commission,deptno.

3. next datatypes :

empno: number data type:number(4)

ename:char(20)

Job:varchar2(15)

sal:Number(7,2)

hiredate:date

comm:Number(7,2)

Deptno:number(2)

Creation of Table:

Create table <table name>

(
<column name1><data type1>,

<column name2><data type2>,

<column name3><data type3>,

-------------------------------------

-----------------------------------

<column name><data type n>

Maximum column is->1000

ex: Create table gkce students

(id number(5),

branch varchar2(10),

section char(1) );

SQL>

SQL> desc emp_info;

Create table emp(empno number(4),ename char(50),job varchar(15),sal number(7,2),hired

date(date),comm number(7,2),dept number(2));

table created:

SQL>desc emp;

Username-scott

password-tiger

-> GKCE students table:

Create table gkce students

( student id number(5),student name varchar2(10),student branch varchar2(5),student section char(1));

Gkce students

studentid
name

branch

-> Create table company:

( cname varchar2(10),

city varchar2(10));

company

cname city

Tcs Chennai

cts hyderabad

->Create table sbibank:

(Account no number(5),

branch name varchar2(10),

Account type varchar2(10),

interest rate number(3,2),

);

SBI BANK

Account no 11088676896
Branch name Sullurpet
Interest rate 1.25%
Account type savings

-> Create table airtel_bill:

( mobile no number(11),

Account type varchar2(10),

Month bill number(5,2));

Airtel

Mobile no 9000001234
Account type Postpaid
Month bill RS.350.27

SQL
STRUCTURE QUERY LANGUAGE
SQL :- it is classified into 5 types they are
1)DDL-DATA DEFINATION LANGUAGE
2)DML-DATA MANIPULATION LANGUAGE
3)DCL-DATA CONTROL LANGUAGE
4)TCL-TRANSACTION CONTROL LANGUAGE
5)DQL/DRL

1) DDL-DATA DEFINATION LANGUAGE:-Creating structure and editing structure.

These are five types
1-Create: used to create table
syntax create table stud(k varchar2(20),
n number(10) ,
d date,
a number(4,1),
g char,
phn char(12));
2-Alter:
classified into six types
a)ADD- alter table stud add(emailed varchar2(20),branch varchar2(10));
b)DROP-alter table stud drop (branch);
c)RENAME-alter table stud rename column emailed to Email;
d)MODIFY-alter table stud modify(branch varchar2(20),k number(20));
e)ENABLE
f)DISABLE
3-Drop
used to drop table .syntax-drop table stud;
4-Truncate
syntax-truncate table stud;
5-Rename
used to rename table. syntax-rename stud to student;
9i-255 columns,10g-1000columns
DDL commands are AUTO COMMITIVE

DML-DATA MANIPULATION LANGUAGE:-

These are six types
1-Select
2-Insert
3-update
4-Delete
5-Merge
6-Explain plan
2-Insert:-
insert into emp values(1234,’vijay’,2500,’developer’,’10-JAN-1985’,10);
*all fields must be present
*if we miss anything error will display.
insert into emp values(1234,’vijay’,2500,’developer’,’10-JAN-1985’,null);
*null is a keyword so we can’t give in ‘Quotetions’.
insert into emp (empno,ename ,deptno)values(12345,’RAM’10);
*by value method we are giving every field value.
By Address Method Insertion:-
insert into
emp(empno.ename,sal,job,hiredate,deptno)values(&empno,&ename,&salary,&job,&date of
joining,&deptno);
*this is prompt no need to give column name it is only for displaying purpose.
‘/’one record is completed
next record
insert complete

UPDATE:-
empnoenamesaldeptno
1234 VIJAY 2000 10
update emp set ename=’VIJAYRAM’ where empno=1234;
update emp set ename=’VIJAYRAM’,sal=5000 where empno=1234;
update emp set sal=(5000+sal)where deptno=10;
update emp set sal=(5000+sal),deptno=null where deptno=10;
DELETE:-
Delete from emp ;
emp records will delete
delete from emp where ename-‘VIAJY’;
truncate from emp; //auto commitable permanently deleted.
delete from emp; //not utocommitable(temporarly deleted).

1-Select:-
select *from emp;
*=>all records
select empno,ename from emp;
Projections, selections are possible in select statement .
select *from emp where deptno=10;
select *from emp where deptno=10 and sal>5000;
Quries:
write a query to display managers information ?
select *from emp where job=’MANAGER’;
waq to dtde who is getting salary more than 5000 and working as ANALYST ?
select *from emp where sal>5000 and job=’ANALYST’;
waq to dtde who is working in 10th department and totalsal<3500 ?
select *from emp where (sal+comm)>3500 and deptno=10 andcomm>500;
who is not getting comm ?
select *from emp where comm is null;
waq to dtde whose deptno=10 join before 1-JAN-1984 and working as MANAGER who is
getting some comm and MANAGER=7839 ?
select *from emp where deptno=10 and job=’MANAGER’ and hiredate<’01-JAN-1984’ and
comm is notnull and manager=7839;
waq to dtde whose sal between 3000 and 5000?
select *from emp where sal>3000 and sal<5000;
select *from emp where sal between 3000 and 5000;
waq to dtde whose sal not lies between 7000 and 1000?
select *from emp where sal not between 7000 and 10000;
waq to dtdewhos salary is 3000 or 4000or 5000 or 6000?
select *from emp where sal=3000 or sal=4000 or sal=5000 or sal=6000;
wqa to dtde who is working 10 th department as a manager or deptno20 as a analyst?
select *from emp where (deptno=10 and
job=’MANAGER’ )or(job=’ANALYST’anddeptno=20);
waq to dtde who is working under 7866?
select * from emp where mgr=7866;
waq to dtde who is working under some manager?
select *from emp where mgr is not null;

PATTERN SEARCH

“%&_”are used for pattern search.

“_”only one character represented.
“%”represents more than one character.

LIKE:-
select *from emp where enamelike’v%’;
start with ‘v’ and end with’m’ .
select *from emp where ename like ’v%’and like ‘%M’;
select *from emp where enamelike’v%m’;
select *from emp where enamelike’v%’;

4 letter employee name:

select *from emp where ename=’____’;
select *from emp where ename=’_a__’;

Atleast one ‘A’ containg word:

select *from emp where ename=’%A%’;
words having letter ‘A’ and does not having letter ‘B’
select *from emp where enamelike’%A%’ and ename not like’%B%’;

COMMIT:
update emp set sal=5000 where deptno=10;
commit;
* it saves the record into database permanently.

ROLLBACK:
update emp set sal=5000 where deptno=10;
rollback;
* if any database is performed except DDL command it retrives the previous stored data if
we use rollback.
*rollback should write before commit.

SAVE POINT:
insert 1;
insert 2;
.
.
.
.
.
insert 10;
save point A;
insert 30;
.

SQL FUNCTIONS
single row function Multirow function

1. Character Functions " "

2. Number Functions " "
3. Date Functions " "
4. Conversion Functions " "

single row function :

Each row gives output and takes input then it is said to be single row functions.

Multirow function:

Multirow functions group of lines takes input and produce output is said to be Multirow functions.

Dual Table;

Dual table is a oracle provided table which will be used for normal calculation purpose.

Desc dual;
desc dual

NAME NULL TYPE

DUMMY VARCHAR2

Select * from dual;

D
X
1 row selected

Column name Dummy varchar2(1) and value is 'X' which consists of single row& Single column.

Select 25*25 from dual;

o/p->125

1 row selected

Select 25*5 from emp;

25*5

125

125 Depends on no:of records or no:of rows 125 will be printed.

'

'

125

14 rows selected

CHARACTER FUNCTIONS
Upper(argument)->Always return character datatype.

Upper function takes one argument and converted into Upper case

Ex:

select upper('vijay') from dual;

o/p-VIJAY
select upper(ename),upper(job) from emp;

all ename values & job values are converted into upper case

Lower(argument)->Always return character datatype.

EX:

select lower('VIJAY') from dual;

o/p-vijay

INITCAP(argument)-> Always return character datatype.

select initcap('vijay') from dual;

o/p->Vijay.

First letter capital and remaining letters are small.

-> select * from emp where upper(job)=upper('manager');

ASCII: American standard code for information interchange

ASCII('A') -> it return ascii value of 'A'.

select ascii('A') from dual;

o/p-> 65.

CHR : it gives ascii value and print corresponding character

select chr(65) from dual;

LENGTH:

select length('vijay') from dual;

o/p->5.

CONCAT:
concatenate two strings vijay ram

select concat('vijay','ram') from dual;

o/p->vijayram

select concat(concat('vijay','ram'),'k') from dual;

o/p->vijayramk

||->concatenate operator pipe symbol.

select ('vijay'||'Ram'||'B.Tech') from dual;

o/p->vijayramkb.tech

_____________________________________________________________________________________
select empno||'is getting salary'||sal||'and also commission||comm from emp;

7389 is getting salary from 800 and also commission.

___________________________________________________________________________________

LTRIM: Cutting string ,left cutting the string

select ltrim('vijayaram','vij') from dual;

o/p->ayaram

select ltrim ('Btech','B') from dual;

o/p->tech

select ltrim ('Btech','Bte') from dual;

o/p-> ch.

select ltrim ('vijay') from dual;

o/p->vijay.

RTRIM: Cutting string ,right cutting the string.

select rtrim('vijayaram','m') from dual;

o/p->vijayayara.

select rtrim('murali','i') from dual;

o/p-> mural.

TRIM;

It trim spaces of the string.

select trim (' vijayaram ') from dual;

It eliminates spaces of right as well as left.

select rtrim(ltrim('vijayaramv','v') from dual;

o/p->ijayaram.

select trim('v' from 'vijayaramv') from dual;

it removes v from both left&right side.

trim as(ltrim and rtrim):-

select trim(leading 'v' from 'vijayaramv');

select trim(trailing 'v' from 'vijayaramv');

LPAD:

select lpad(string,totallength,padding string) from dual;

select lpad('vijay',15,'*') from dual;

o/p-> **********vijay-> total length 15

vijay-5 characters.

remaining 10 characters * to left side.

-> select rpad('vijay',15,'*') from dual;

o/p-> vijay**********

-> select rpad(lpad('vijay',10,'*'),15,'*') from dual;

o/p-> *****vijay*****

REPLACE:

select replace('vijay','vij','ram') from dual;

o/p-> ramay (string by string it can replace)

TRANSLATE:

select translate ('vijayaramk','abcd','ram') from dual;

o/p-> vij1y1r1mk

here character by character it replaces.

Replace (vs) Translate

It is string by string It is character by character.

select translate('vijay','i','1834') from dual;

o/p-> v1jay

_____________________________________________________________________________________

select ltrim('vijay','yajiv') from dual;

o/p-> null

____________________________________________________________________________________-

REVERSE:

Select reverse('vijay') from dual;

o/p-> yajiv

* reverse function is not available in PL/SQL.

INSTRING:

instr(char1,char2,(m),(n));

m- position

n- occurance

select instr('vijayaram','a',1,2) from dual;

o/p-> m,n are optional

It always return number.

1. Select instr('vijayaram','a',1,1) from dual;

o/p-> 4

2. Select instr('vijayaram','a',5,1) from dual;

o/p-> 6

3. Select instr('vijayaram','a',5,2) from dual;

o/p-> 8

4. Select instr('vijayaram','a',6,2) from dual;

o/p-> 8

5. Select instr('vijayaram','a',7,2) from dual;

o/p-> 0

6. Select instr('vijayaram','a',7,1) from dual;

o/p-> 8

7. Select instr('vijayaram','a',-4,2) from dual;

o/p-> 8

8. Select instr('vijayaram','a',-4) from dual;

o/p-> 6

* select instr('vijayaram','ya',1,1) from dual;

o/p-> 5

* select instr('vijayaram','ay',1,1) from dual;

o/p-> 4

SUBSTRING:

substr(chr1,(m),(n))

m, n are optionals

n-> always +ve in substring

n-> always +ve in instring

chr1 M N o/p

vijayaram 1 8 vijayara
5 3 yar

7 - ram

-5 - yaram

SINGLE ROW NUMBER FUNCTION

ABS(n):-
select -8,124,abs(-8),abs(124) from dual;
-8,124,8,124
CEIL(n):-
select ceil(75)from dual;
if fraction is there it produce upper value.
FLOOR(n):-
select floor(7.5) from dual; o/p= 7
select florr(7.5) from dual; o/p=7
trunc(m,[n])
round(m,[n])
TRUNC(m,[n]):-
trunk(m,[n])
n number of digits after the decimal point.
it removes fractional parts.
select trunk(785.27333,2)from dual;
o/p =785.27
M N TRUNC O/P ROUND O/P

1.932 1 1.9 1.9

1.999 1 1.9 2.0
1.98732 2 1.98 1.99
1.98732 4 1.9873 1.9873
798.22 -2 700 800
798.22 -1 790 800
SIGN:-
select sign(-8432)from dual; o/p= -1
select sign(8432)from dual; o/p=1

POWER:-
select power(2,3)from dual; o/p=8
select power(2,-3)from dual; o/p=0.125
select power(2,2)from dual; o/p=4
MOD(m,n):-
select mod(3,2)from dual; o/p=1
select mod(8,3)from dual; o/p=2
select mod(1,8)from dual; o/p=1
select mod(2,8)from dual; o/p=2
SQRT( ):-
select sqrt(25)from dual; o/p=5
EXP(n):-
select exp(2)from dual; o/p=7.3890561
LN( ):-
select ln(10)from dual; o/p=2.30258509
LOG(m,n):-
select log(10,100)from dual;
log 100=log
10 10 102 =2log1010 =2

LEAST: (NUMBER FUNCTION):-

this will be give the last number

select least (1,2,3),least(-1,-2,-3) from dual;

LEAST LEAST

1 -3

LEAST: (STRING FUNCTION):-

This will give the least string

select least('a','b','c'),least('satish','srinu','saketh') from dual;

LEAST LEAST

a saketh

VSIZE:-

how many bytes are required to store the value

vsize(empno);

select vsize(empno),vsize(ename) from emp;

vsize and length are almost similar

vsize for number and character

length for only character

DECODE:-

Decode(expression,value1,return1,value2,return2,...........return n)

expression=value1 then return 1

otherwise expression=value2 then return 2

'

'

'

expression = values then return n

otherwise return

select decode(1000,100,'hundered',500,'fivehundered',1000,'thousand','not satisfied') from dual;

select gender,decode(gender,'m','male','f','female','not specified') description from emp;

we don't use expressions here i.e.., <,>,!,=

Gender,decode(gender, ,'male','f','female',

male and female are same data type

i.e., two numbers or two characters

one character, one number not possible.

CASE:-

It will work like decode

case expression when value1 then return 1

when value2 then return 2

'

'

else return

end;

select case 1000 when 100 then 'Hundered'

when 200 then 'two hundered'

when 1000 then 'thousand'

else 'not known'

end description from dual;

select case when condition then statement or return

when condition then return

'

'

'

select case when sal>500 then 'more than 500'

when sal<2000 then 'less than 2000'

else 'middle value'

and sal description from emp;

here expression are allocated like <,>

select case sal when 5000 then 'five thousand'

when 2000 then 'two thousand'

when 3000 then 'three thousand'

else 'not known'

end;

(it is like decode, it is like if else statement)

_______________________________________________________________________________

decode-> we can't use decode in plsql

case-> we can use in plsql

_____________________________________________________________________________________
CONVERSION FUNCTIONS
TO_CHAR -> date in to character

TO_DATE -> character in to date

TO_NUMBER -> o/p will be number

Insert into emp (empno,ename,hiredate) values (1234,'vijay',to_date('01-JAN-2011 08:45:55AM','DD-

MM-YYYY HH:MI:SS AM');

TO_CHAR FUNCTIONS:-

1.select to_char(to_date('01-JAN-2011','DD-MON-YYYY'),'MONTH') from dual;

o/p-> JANUARY

2. select to_char(hiredate,'MONTH') from emp;

o/p-> DECEMBER

FEBRUARY

'

'

3. select to_char(hiredate,'MON') from emp;

o/p-> DEC,FEB,.......

4. select to_char(hiredate,'mon') from emp;

o/p-> dec,feb.........

5. select to_char(hiredate,'D') from emp;

o/p-> sunday-1,monday-2,tusday-3,wednesday-4,thursday-5,friday-6,saturday-6 (day of the week)

6. select to_char(hiredate,'DD') from emp;

o/p-> day of the month.

7. select to_char(hiredate,'DDD') from emp;

o/p-> day of the year 352 day

8. select to_char(hiredate,'YYYY') from emp;

o/p-> 1985,1981.....
9. select to_char(hiredate,'year') from emp;

o/p-> nineteen eighty

10. select to_char(hiredate,'Q') from emp;

o/p-> it prints quarter of the month

jan,feb,mar=1

apr,may,jun=2

jul,aug,sept=3

oct,nov,dec=4

10. select to_char(hiredate,'w') from emp;

o/p-> week of the month

11. select to_char(hiredate,'ww') from emp;

o/p-> week of the year

12. select to_char(hiredate,'DAY') from emp;

o/p-> WEDNESDAY,FRIDAY

13. select to_char(hiredate,'J') from emp;

o/p-> judian calender

1-JAN-4712BC

__________________________________________________________________________________-

select extract(year from sysdate) from dual;

o/p-> 2011

select extract(month from sysdate) from dual;

o/p-> 8

select extract(day from sysdate) from dual;

o/p-> 23

select extract(year from '01-JAN-2011') from dual;

o/p-> error

select extract(year from to_date('01-JAN-2011','DD-MON-YYYY')) from dual;

o/p-> 2011

_____________________________________________________________________________________

QUERIES:

1. WAQ to DTDE who joined in the january month ?

select * from emp where trim(to_char(hiredate,'MON'))='JAN';

select * from emp where hiredate like '%JAN'

2. WAQ to DTDE who joined in the first quator ?

select * from emp where (to_cahr(hiredate,'Q'))=1;

3. WAQ to DTDE who joined in the first week of any month ?

select * from emp where (to_char(hiredate,'W'))=1;

4. WAQ to DTDE who joined in the monday of any month ?

select * from emp where trim(to_char(hiredate,'DAY')='MONDAY';

* If we are not using trim it does not produce output because highest length is WEDNESDAY 10
characters but monday is 6 characters.

4 spaces are there to remove this spaces we use trim function.

_____________________________________________________________________________________

* Here we can convert number in to character ?

select to_char(9999,'9,999') from dual;

o/p-> 9,999

select to_char(10000,'99,999') from dual;

o/p-> 10,000

select to_char(sal,'$00,000') from emp;

o/p-> $00,800,$01,600,$01,250........

select to_char(sal,'$99,999') from emp;

o/p-> $800,$1,600,$1,250............

___________________________________________________________

select 'Rs.'||to_char(sal,'$99,999') from emp;

o/p-> Rs.800,Rs. 1,600,Rs.1,250..........

select to_number('9,999',9999) from dual;

o/p-> 9999

__________________________________________________________

* WAQ to DTDE who joined in the month of 'MAY' ?

select * from emp trim(to_char(hiredate,'MONTH'))='MAY';

_-> trim because to eliminate spaces because in MONTH section we have SEPTEMBER in MAY six spaces
are there so by using trim it eliminate spaces.

__________________________________________________________________________

MULTIROW FUNCTIONS
1) SUM:-
select sum(sal)from emp;

2) AVG:-
select avg(sal)from emp;

3) MIN:-

Select min(sal)from emp;

4) MAX:-
select max(sal)from emp;

5)COUNT:-
select count(*)from emp;

Any group function wouldn’t conside null value if null is there it will eliminate.

Sal
5000
7000
3000
2000
0
select max(sal)from emp; o/p is 7000.
select count(sal)from emp; o/p is 5 because it not count null value.
select count(*) from emp; o/p is 6 because it count null value.

DATA DICTIONARY VIEW

DBA-DBA related information.
ALL-ALL user related information.

User _tables:-how many tables created , when created ,memory no.of records ,.user ,owner,
related information.
Tab – less information.
user_tab_columns.
desc – sql*plus.
* all_tables,all_tab_columns,user_table,user_tab_columns,all_user.
matter table

Dict Dictionary
in this table data dictionary views information is stored.
Basic structure of select statement
select col1,col2,----------coln
from table 1,table 2,table 3---------table n
where <condition> filter conditions by using these we can filter like 10th dept,we can select
one dept
group<condition>
having class
having<condition>
order by<clause>
*group by class is there no need of having class.
*if having class is there means defneatly above having group by is there.
Order by class:-
1)Ascending order
2)Descending order
select *from emp where deptno=10 order by sal desc;
order by having more than one column.
select *from emp where deptno=10 order by sal desc,emp,asc;
empno ename sal deptno job
3 v 2000 10 developer
1 I 3000 10 analyst
2 j 5000 10 analyst
5 j 5000 10 ______
6 k ____ 10 _______
7 r _____ 10 _______
*)select *from emp where deptno=10 order by sal desc nulls last;
*)select *from emp where deptno=10 order by sal desc nulls first;
-)it prints all null values abpve because null is the high value is to remove null value last then we
nulls last.
select deptno,empno as empno,sal,job from emporder by emp_no desc;
we can use alias names in order by class.
we can’t use alias names in where,group,having .
order by class we can use alias names because it execute last so alias names already assigned
so we can use ‘ALIAS NAMES’in order by class.

GROUP BY CLASS
Select max(sal)from emp;
select max(sal)from emp groupby deptno;

deptno no.same one group in which max salaray

select max(sal) as max sal from emp group by deptno;
max values:5000,3000
*we don’t know for which department max sal is 5000 and 3000
select deptno,max(sal) from emp groupby deptno;
deptno=single row function.
max(sal)=multirow function.
so far every deptno one corresponding sal is there so we have to use deptno,max(sal) because
deptno in group by class.
10 5000
20 7000
30 3000
40 2000 deptno=normal column,row function.
max(sal)=group function.
here for every deptno we have corresponding max(sal) in so deptno,max(sal)will work.
10 5000
20 3000
30 4000
40 6000
select empno,max(sal) from emp groupby empno;
it will print as usal printing.
select deptno,job,max(sal) from emp groupby deptno,job;
select deptno,max(sal),min(sal),avg(sal),count(*)from emp group by deptno;
in where class we can’t use group by class.

waq to dtde in which dept no.of employees greater than 3

select deptno,max(sal) maxsal,min(sal) minsal,avg(sal) avgsal,count(*)from emp groupby deptno
having count(*)>=3;
select empno,ename,sal,sal*12 annsal from emp order by sal desc;
ORDER BY CLASS CONSIDER NULL VALUES AS BIG SO IT PLACE LAST:
empno ename sal
1 a 2000
2 b 5000
3 c -
4 d 7000
select empno,ename,sal,sal*12 annsal from emp orderby sal;
1 a 2000
2 b 5000
4 d 7000
3 c -
select empno,ename,sal,sal*12 annsal from emp orderby sal desc;
3 c -
1 a 2000
2 b 5000
4 d 7000
select empno,sal,sal*12 annsal from emp order by sal desc nullslast;
select empno,sal,sal*12 annsal from emp order by sal desc nullsfirst;
ename
vijay ***ay
ram ***
kalisetty ***osetty
select replace(ename,substr(ename,1,3),’***’)from emp;

select to_char(hiredate,’DAY’)as DAY,to_char(hiredate,’MM’)as MONTH,

to_char(hiredate,’YYYY’)as year, to_char(hiredate,’HH’)as HOUR,to_char(hiredate,’MI’)as
MINUTES,to_char(hiredate,’SS’)as seconds from emp;
DAYMONTHYEARHOURMINUTE SECONDS
WEDNESDAY 12 1980 12 00 00

select empno,ename,sal,decode(round(sal/1000),1,’*’,2,’**’,3,’***’,4,’****’,5,’*****’,’enter
number other than zero’)as’******’ from emp;
empno ename sal ******
7369 SMITH 800 *
1000 **
1250 *
2850 ***
2450 **
3000 ***
5000 ******
1500 **
1100 *
950 *

3000

Table paper:

Pno Pname Pdate email

1 Eenadu 01-JAN-70 [email protected]
2 Jyothi 11-JUN-87 [email protected]
3 Sakshi 23-JUL-09 [email protected]
4 Surya 30-MAY-00 [email protected]
5 Vartha 29-APR-88 [email protected]
6 Visalandhra 21-AUG-90 [email protected]
7 Bhumi 12-DEC-96 [email protected]
select pno,pname,pdate,replace(email,' ','') as email from paper;

_____________________________________________________________________________________

Select concat (substr('VIJAY,RAM',(instr('VIJAY,RAM',',',1,1))+1,9)

concat(',',substr('vijay,ram','(instr('VIJAY,RAM',','1,1))-1)) from dual;

_______________________________________________________________

Conn sys as sysdba

enter

password=manager

show user=sys

Alter user scott account unlock;

password expired= tiger

enter newpassword=tiger

_____________________________________________________________________________________

SQL*PLUS SETTINGS:

SQL*plus:-

set linesize 200:-in oneline 200 characters

set pagesize 100:-in one page 100 lines

clscr:- no semicolon

set sql prompt-> set prompt vijay>

SQL>ed login.sql>window open note pad

SQL>@login

ORACLE RDBMS
DATA:- Some information if just like characters.
Information:

Number Alphanumeric(Alphabets) Special characters

Data is systamatic manner then it is said to be information

Management system:-

Set of instructions to process data in database

we need to use pice of code to keep data in database.

RDBMS

(4G)SQL (3G)SQL

To eliminate the drawbacks of SQL we go for PL/SQL

application SAP/.NET/JAVA front end D.B

editor

(SQL engine)

CUI-> command user interface

GUI-> graphical user interface

SQL engine interpeter command gives in editor and manipulates the data.

CUI

GUI Interface are

ISQL * +

+OAD

SQL Navigator
PL/SQL developer

adhoc instructions which is in editor

built in instructions which is in application.

TABLE:-

logical representation of rows & columns

SQL*plus -> end with semicolon

DCL:-

Grant all on emp to vijay;

all means-> update+insert+delete+select

only owner can drop the table other user doesnot possible to drop that one.

Revoke all on emp from vijay;

Grant all on emp to vijay with grant option;

scott->vijay->Ram->emp

vijay can give permission to other user because here we are mention grant option .

Grant all on emp to vijay with grant option.

Data Dictionary views:-

We want to see data dictionary view to see what permissions are given to other users.

1.select sal,empno,ename from emp;

2.WAQ to DTDE who 's job is Manager?

select * from emp where job='MANAGER' or job='manager';

3. WAQ to DTDE who is working in deptno=10 and getting sal>5000

select * from emp where deptno=10 and sal>5000

4. WAQ to DTDE who's employee number in the given list 7856,7866,1234,9999?

select * from emp where empno in(7856,7866,1234,9999);

5. WAQ to DTDE who is getting some comm and working as manager and sal<5000?

select * from emp where comm is not null and comm <>0 and job='MANAGER' and sal<5000;

6. WAQ to DTDE who joined before 1-JAN-1983?

select * from emp where hiredate<'1-JAN-83';

7. WAQ to DTDE for the employees who's name consists exactly 4 characters?

select * from emp where ename = '____';

USAGE OF ESCAPE:

Select * from emp where ename like '=%%' escape '=';

if ename like '%mur%i' if we want search the '%' symbol in ename then we use escape if we write like .

Create table emp12(ename varchar2(10),empno number);

insert into emp12 values('%murali',1);

ename empno

mur%ali 1234

m%ural%i 1245

%rali 3678

murali 7658

ravi 1237

select * from emp12 where ename like'%%';

ename empno

mur%ali 1234
m%ural%i 1245

%rali 3678

If we want to print ename who's name starts with '%' then this query will not for that we use escape

select * from emp12 where ename like '=%%' escape '=';

ename empno

%rali 3678

insert into emp12 values ('murali%',2);

select * from emp12 where ename like '%=%' escape '=';

ename empno

murali% 2

select * from emp12 where ename like '%%=' escape '=' ;

error: missing or illegal character following the escape character.

_________________________________________________________________________

insert ename like vijay's

insert into emp(ename,empno) values('vijay''s',1);

name as like vijay's

___________________________________________________

AUXULIARY COLUMN:

empno,ename,sal,annsal,experience,deptno
Auxuliary columns which does not exist in real emp table.

Select empno,ename,sal,sal*12,deptno from emp;

sal*12 annsal

DUAL TABLE:

Dual table is a dummy table provided by the oracle to do the manipulations.

It is a single row& single column table.

column name-> dummy

value-> X

select * from dual;

D
X
1 row selected.

_________________________________________________________________________________

WAQ to add ***** before the ename 5 stars after the ename of the employee emp table:-

select rpad(lpad(ename,length(ename)+5,'*'),length(ename)+10,'*') from emp;

___________________________________________________________________________________

SQL * PLUS settings:

sql> set line 200

sql> set sql prompt vijay>

VIJAY> set pagesize 100

VIJAY> set feedback 5 (default value is 3)

Feedback is used for how many rows are selected while executing select statement.

VIJAY> show all

it display all the environment settings

VIJAY> cl scr -> clear screen

cl scr;

VIJAY> L

it display last statement which is execute

VIJAY> select * from emp;

VIJAY> L

l * select * from emp

VIJAY> R

it again execute the last query which is already executed.

VIJAY> select 8 from emp;

Generally in a keyboard 8 and * in a same key if we type instead of '*'

VIJAY>select 8 from emp;

VIJAY>c/8/*

here

c-> change

8-> old character

*-> new character

VIJAY>select

2 * from

3 emp

4 where

5 deptno=10; app where deptno=10

app where deptno=10

VIJAY> 1 set time on

set time on
* select set pause on

>2 btitle 'reportedset

forpause
supermarket'
on
2 *** from ttitle 'vijay's report'
btitle 'reported for supermarket'
>3 set feedback 3

3 * emp set echo on

ttitle 'vijay's report'

set feedback 3

set echo on
>4

4 * where

>5

5 * deptno=10;

____________________________________________________________________________

SPOOL option:-

VIJAY> spool G:\m\murali\kkk.sql

VIJAY> select * from emp;

VIJAY> spool off

_____________________________________________________________________________

VIJAY> spool G:\m\murali\kkk.sql append

VIJAY> select * from emp where deptno=10;

VIJAY> spool off

______________________________________________________________________________

VIJAY> spool G:\m\murali\kkk.sql replace

VIJAY> select * from dept;

VIJAY> spool off

_________________________________________________________________________________

VIJAY> ed

editor is open

select * from emp; -> no need of semo colon

alt + F4 -> save close run;

VIJAY> /

which executes previous statement

VIJAY> ed login.sql

login.sql file - if we write anything then it executes at every time whenever we login in oracle.
ed login.sql -> set line 200

set pagesize 100

set sqlprompt vijay>

cl scr

Alt + F4

VIJAY> @ login .sql

VIJAY> show all

_____________________________________________________________________________

DATA DICTIONARY VIEWS:

This is also table but created by oracle itself .

Tab:

Table name cluster created_date

select * from tab;

select * from all_tables;-> any user table information

select * from user_tables; -> scott/tiger only scott user related

select * from DBA_tables ; -> All information about table master table details.

_________________________________________________________________________________

select * from user_tab_columns;

dict table-> table name all tables

comments

__________________________________________________________________________________