DDL COMMANDS CREATE COMMAND:
SQL> create table sub (name varchar(8),age number(5),mark1 number(4),mark2 number(4), 2 mark3 number(4)); Table created. DESC COMMAND : SQL> desc sub; Name Null? Type --------------------------------------------------------------------------------- -------- ------------------------------------------------------NAME VARCHAR2(8) AGE NUMBER(5) MARK1 NUMBER(4) MARK2 NUMBER(4) MARK3 NUMBER(4) ALTER COMMAND:
1)ADD COMMAND : SQL> alter table sub add(total number(6)); Table altered. SQL> desc sub; Name Null? Type --------------------------------------------------------------------------------- -------- ------------------------------------------------------NAME VARCHAR2(8) AGE NUMBER(5) MARK1 NUMBER(4) MARK2 NUMBER(4) MARK3 NUMBER(4) TOTAL NUMBER(6) RENAME COMMAND:
SQL> rename sub to subha; Table renamed. SQL> desc subha; Name --------------------------------------------------NAME AGE MARK1 MARK2
Null? Type --------------- -------- -----------VARCHAR2(8) NUMBER(5) NUMBER(4)
2)MODIFY COMMAND: SQL> alter table sub modify(mark3 number(6)); Table altered. SQL> desc sub; Name Null? Type --------------------------------------------------------------------------------- -------- ------------------------------------------------------NAME VARCHAR2(8) AGE NUMBER(5) MARK1 NUMBER(4) MARK2 NUMBER(4) MARK3 NUMBER(6) TOTAL NUMBER(4) MARK3 NUMBER(6) TOTAL NUMBER(6) TRUNCATE COMMAND:
SQL> truncate table subha; Table truncated. SQL> desc sub; ERROR: ORA-04043: object sub does not exist
DROP COMMAND: SQL> drop table subha;
Table dropped. SQL> desc subha; ERROR: ORA-04043: object subha does not exist
DQL COMMANDS
SQL> create table sub (name varchar(8),age number(5),mark1 number(4),mark2 number(4), 2 mark3 number(4)); Table created. SQL> insert into sub values('&name','&age','&mark1','&mark2','&mark3'); values('&name','&age','&mark1','&mark2','&mark3'); Enter value for name: sachin Enter value for age: 12 Enter value for mark1: 90 Enter value for mark2: 90 Enter value for mark3: 90 old 1: insert into sub values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into sub values('sachin','12','90','90','90') 1 row created. SQL> / Enter value for name: sachin Enter value for age: 22 Enter value for mark1: 90 Enter value for mark2: 89 Enter value for mark3: 78 old 1: insert into sub values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into sub values('sachin','22','90','89','78') 1 row created. SQL> / Enter value for name: ranjan Enter value for age: 21 Enter value for mark1: 89 Enter value for mark2: 78 Enter value for mark3: 80 old 1: insert into sub values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into sub values('ranjan','21','89','78','80') 1 row created.
SQL> / Enter value for name: gayathri Enter value for age: 12 Enter value for mark1: 78 Enter value for mark2: 89 Enter value for mark3: 90 old 1: insert into sub values('&name','&age','&mark1','&mark2','&mark3' values('&name','&age','&mark1','&mark2','&mark3')) new 1: insert into sub values('gayathri','12','78','89','90') 1 row created. SQL> / Enter value for name: rajini Enter value for age: 22 Enter value for mark1: 90 Enter value for mark2: 87 Enter value for mark3: 56 old 1: insert into sub values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into sub values('rajini','22','90','87','56') 1 row created.
SELECT COMMAND: TYPE 1: SQL> select * from sub;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- --------------------------------------------------------sachin 12 90 90 90 sachin 22 90 89 78 ranjan 21 89 78 80 gayathri 12 78 89 90 rajini 22 90 87 56 TYPE 2: SQL> select name,age from sub;
NAME -------- ---------sachin 12 sachin 22 ranjan 21 gayathri 12 rajini 22
AGE
TYPE 3: SQL> select distinct name from sub; NAME -------gayathri rajini ranjan sachin TYPE 4: SQL> select * from sub where mark2=90;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- --------------------------------------------------------sachin 12 90 90 90 SQL> delete from sub where name='sachin';
2 rows deleted. SQL> select * from sub;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- --------------------------------------------------------ranjan 21 89 78 80 gayathri 12 78 89 90 rajini 22 90 87 56 TYPE 5: SQL> select name from sub order by name;
NAME -------gayathri rajini ranjan TYPE 6: SQL> select name from sub order by name desc; NAME -------ranjan rajini gayathri
LOGICAL OPERATION SQL> select * from sub ;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- --------------------------------------------------------ranjan 21 89 78 80 gayathri 12 78 89 90 rajini 22 90 87 56 sachin 12 89 90 78 ram 21 90 67 78
AND OPERATION: SQL> select * from sub where name='rajini' and age=22;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- --------------------------------------------------------rajini 22 90 87 56 SQL> select * from from sub where name='rajini' and age=21;
no rows selected OR OPERATION: SQL> select * from sub where name='ranjan' or age=21;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- ------------------------------------------------------ranjan 21 89 78 80 ram 21 90 67 78
NOT OPERATION:
SQL> select * from from sub where not name='ram'; name='ram';
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- ------------------------------------------------------ranjan 21 89 78 80 gayathri 12 78 89 90 rajini 22 90 87 56 sachin 12 89 90 78
SET OPERATION
SQL> create table root (name varchar(8),age number(5),mark1 number(4),mark2 number(4), 2 mark3 number(5)); Table created. SQL> insert into root values('&name','&age','&mark1','&mark2','&mark3'); Enter value for name: sachin Enter value for age: 12 Enter value for mark1: 89 Enter value for mark2: 89 Enter value for mark3: 90 old 1: insert into root values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into root values('sachin','12','89','89','90' values('sachin','12','89','89','90')) 1 row created. SQL> / Enter value for name: kalai Enter value for age: 20 Enter value for mark1: 90 Enter value for mark2: 90 Enter value for mark3: 78 old 1: insert into root values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into root values('kalai','20','90','90','78') 1 row created. SQL> / Enter value for name: rani Enter value for age: 18 Enter value for mark1: 78 Enter value for mark2: 77 Enter value for mark3: 67 old 1: insert into root values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into root values('rani','18','78','77','67') 1 row created. SQL> / Enter value for name: kalam Enter value for age: 32 Enter value for mark1: 90 Enter value for mark2: 90 Enter value for mark3: 89 old 1: insert into root values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into root values('kalam','32','90','90','89') 1 row created. SQL> / Enter value for name: gokul
Enter value for age: 20 Enter value for mark1: 90 Enter value for mark2: 90 Enter value for mark3: 90 old 1: insert into root values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into root values('gokul','20','90','90','90') 1 row created. SQL> select * from root;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- ----------------------------------------------------------sachin 12 89 89 90 kalai 20 90 90 78 rani 18 78 77 67 kalam 32 90 90 89 gokul 20 90 90 90 SQL> select * from sub;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- --------------------------------------------------------ranjan 21 89 78 80 gayathri 12 78 89 90 rajini 22 90 87 56 sachin 12 89 90 78 ram 21 90 67 78
UNION COMMAND: SQL> select name,age from sub union select name,age from root; NAME AGE -------- ---------------gayathri 12 gokul 20 kalai 20 kalam 32 rajini 22 ram 21 rani 18 ranjan 21 sachin 12 9 rows selected.
UNION ALL COMMAND: SQL> select name,age from sub union all select name,age from root; NAME AGE -------- ---------------ranjan 21
gayathri rajini sachin ram sachin kalai rani kalam gokul
12 22 12 21 12 20 18 32 20
10 rows selected.
INTERSECT: SQL> select name from sub intersect select name from root; NAME -------sachin
MINUS: SQL> select name from sub minus select name from root; NAME -------gayathri rajini ram ranjan
SUB QUERIES SQL> create table emp(ename varchar(12),eno number(7),deptno number(2)); Table created. SQL> insert into emp values('&ename','&eno','&deptno'); Enter value for ename: navee Enter value for eno: 1001 Enter value for deptno: 10 old 1: insert into emp values('&ename','&eno','&deptno') new 1: insert into emp values('navee','1001','10') 1 row created. SQL> / Enter value for ename: arun Enter value for eno: 2001 Enter value for deptno: 20 old 1: insert into emp values('&ename','&eno','&deptno') new 1: insert into emp values('arun','2001','20') 1 row created. SQL> / Enter value for ename: puneeth Enter value for eno: 3001 Enter value for deptno: 30 old 1: insert into emp values('&ename','&eno','&deptno') new 1: insert into emp values('puneeth','3001','30') 1 row created. SQL> / Enter value for ename: gayathri Enter value for eno: 4001 Enter value for deptno: 40 old 1: insert into emp values('&ename','&eno','&deptno') new 1: insert into emp values('gayathri','4001','40') 1 row created. SQL> create table dep1(deptno dep1(deptno number(2),place number(2),place char(7)); Table created. SQL> / Enter value for deptno: 20 Enter value for place: chennai old 1: insert into dep1 values('&deptno','&place') new 1: insert into dep1 values('20','chennai') 1 row created.
SQL> / Enter value for deptno: 30 Enter value for place: mumbai old 1: insert into dep1 values('&deptno','&place') new 1: insert into dep1 values('30','mumbai') 1 row created. SQL> / Enter value for deptno: 40 Enter value for place: delhi old 1: insert into dep1 values('&deptno','&place') new 1: insert into dep1 values('40','delhi') 1 row created. SQL> select * from emp; ENAME ENO DEPTNO ------------ ---------- --------------------navee 1001 10 arun 2001 20 puneeth 3001 30 gayathri 4001 40 SQL> select * from dep1; DEPTNO PLACE ---------- -----------------20 chennai 30 mumbai 40 delhi
IN COMMAND: SQL> select ename,eno from emp where deptno in(select deptno from dep1); ENAME ENO ------------ ---------arun 2001 puneeth gayathri 4001
3001
NOT IN COMMAND: SQL> select ename,eno from emp where deptno 2 not in(select deptno from from dep1);
ENAME ENO ------------ ---------------navee 1001 SQL> select ename,eno from emp where deptno>all(select deptno deptno from dep1); no rows selected
SQL> select ename,eno from emp where deptno < all(select deptno from dep1); ENAME ENO ------------ ---------------navee 1001 SQL> select ename,eno from emp where deptno>= all(select deptno from dep1); ENAME ENO ------------ ----------------gayathri 4001 SQL> select ename,eno from emp where deptno <= all(select deptno from dep1); ENAME ENO ------------ ----------------navee 1001 arun 2001 SQL> select ename,eno from emp where deptno=all(select deptno deptno from dep1); no rows selected SQL> select ename,eno from emp where deptno<> all(select deptno from dep1); ENAME ENO ------------ ---------------navee 1001 SQL> select ename,eno from emp where deptno> any(select deptno from dep1); ENAME ENO ------------ ---------------- puneeth gayathri 4001
3001
SQL> select ename,eno from emp where deptno< any(select deptno from dep1); ENAME ENO ------------ ---------------navee 1001 arun 2001 puneeth 3001 SQL> select ename,eno from emp where deptno<= any(select deptno from dep1); ENAME ENO ------------ ---------------navee 1001 arun 2001 puneeth 3001 gayathri 4001 SQL> select ename,eno from emp where deptno>=any(select deptno from dep1);
ENAME ENO ------------ ----------------arun 2001 puneeth gayathri 4001
3001
SQL> select ename,eno from emp where deptno=any(select deptno from dep1); ENAME ENO ------------ -----------------arun 2001 puneeth gayathri 4001
3001
SQL> select ename,eno from emp where deptno<>any(select deptno from dep1); ENAME ENO ------------ ---------------navee 1001 arun 2001 puneeth gayathri 4001
3001
EXISTS COMMAND : SQL> select ename,eno from emp where exists(select deptno from dep); ENAME ENO ------------ ----------------navee 1001 arun 2001 puneeth gayathri 4001
3001
NOT EXISTS COMMAND: SQL> select ename,eno from emp where not exists(select deptno from dep); no rows selected
CONSTRAINTS
NOT NULL:
SQL> create table cool1(name char(15),reg number not null,age number,place char(12)); Table created. SQL> insert into cool1 values('&name',®,&age,'&place'); Enter value for name: ram Enter value for reg: 1001 Enter value for age: 21 Enter value for place: delhi old 1: insert into cool1 values('&name',®,&age,'&place') new 1: insert into cool1 values('ram',1001,21,'delhi') 1 row created. SQL> / Enter value for name: krish Enter value for reg: null Enter value for age: 21 Enter value for place: thane old 1: insert into cool1 values('&name',®,&age,'&place') new 1: insert into cool1 values('krish',null,21,'thane') insert into cool1 values('krish',null,21,'thane') * ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."COOL1"."REG")
SQL> select * from cool1;
NAME REG AGE PLACE --------------- ---------- ---------- -----------ram 1001 21 delhi
UNIQUE:
SQL> create table cool2(name char(14),reg number constraint gg unique,age number,place char(8)); Table created.
SQL> insert into cool2 values('&name',®,&age,'&place'); Enter value for name: praveen Enter value for reg: 1001 Enter value for age: 21 Enter value for place: dehradun old 1: insert into cool2 values('&name',®,&age,'&place') new 1: insert into cool2 values('praveen',1001,21,'d values('praveen',1001,21,'dehradun') ehradun')
1 row created. SQL> / Enter value for name: kannan Enter value for reg: 1001 Enter value for age: 21 Enter value for place: delhi old 1: insert into cool2 values('&name',®,&age,'&place') new 1: insert into cool2 values('kannan',1001,21 values('kannan',1001,21,'delhi') ,'delhi') insert into cool2 values('kannan',1001,21,'delhi') * ERROR at line 1: ORA-00001: unique constraint (SCOTT.GG) violated
SQL> select * from cool2;
NAME REG AGE PLACE -------------- ---------- ---------- ------- praveen 1001 21 dehradun SQL> insert into cool2 values('&name',®,&age,'&place'); Enter value for name: subha Enter value for reg: null Enter value for age: 21 Enter value for place: guwahati old 1: insert into cool2 values('&name',®,&age,'&place') new 1: insert into cool2 values('subha',null,21,'guwahati') 1 row created. SQL> / Enter value for name: kalki Enter value for reg: Enter value for age: 21 Enter value for place: hungary old 1: insert into cool2 values('&name',®,&age,'&place') new 1: insert into cool2 values('kalki',,21,'hungary') insert into cool2 values('kalki',,21,'hungary') * ERROR at line 1: ORA-00936: missing expression
SQL> select * from cool2;
NAME REG AGE PLACE -------------- ---------- ---------- ---------------- praveen 1001 21 dehradun subha 21 guwahati SQL> alter table cool2 disable constraint gg; Table altered.
SQL> insert into into cool2 values('&name',®,&age,'&place'); Enter value for name: ravish Enter value for reg: 1001 Enter value for age: 21 Enter value for place: hungary old 1: insert into cool2 values('&name',®,&age,'&place') new 1: insert into cool2 values('ravish',1001,21,'hunga values('ravish',1001,21,'hungary') ry') 1 row created. SQL> select * from cool2;
NAME REG AGE PLACE -------------- ---------- ---------- ------- praveen 1001 21 dehradun subha 21 guwahati ravish 1001 21 hungary CHECK:
SQL> create table cool3(name char(15),regno number,age number,place char(14),check 2 (place in('chennai','mumbai','delhi'))); Table created. SQL> insert into cool3 values('&name',®no,&age,'&place'); Enter value for name: gayathri Enter value for regno: 1002 Enter value for age: 21 Enter value for place: delhi old 1: insert into cool3 values('&name',®no,&age,'&place') new 1: insert into cool3 values('gayathri',1002,21,'delhi') 1 row created. SQL> / Enter value for name: rani Enter value for regno: 1003 Enter value for age: 22 Enter value for place: pune old 1: insert into cool3 values('&name',®no,&age,'&place') new 1: insert into cool3 values('rani',1003,22,'pune') insert into cool3 values('rani',1003,22,'pune') * ERROR at line 1: ORA-02290: check constraint (SCOTT.SYS_C004261) violated
SQL> select * from cool3;
NAME REGNO AGE PLACE --------------- ---------- ---------- -------------gayathri 1002 21 delhi
DEFAULT:
SQL> create table cool5(name char(14),reg char(14),reg number,age number primary key, 2 place char(13) char(13) default('kashmir')); Table created.
SQL> insert into cool5(name,reg,age) values ('jamuna',1006,23); 1 row created. SQL> select * from cool5;
NAME REG AGE PLACE -------------- ---------- ---------- ------------ jamuna 1006 23 kashmir
PRIMARY KEY: SQL> create table cool4(name char(14),reg number,age number primary key,place char(13));
Table created. SQL> insert into cool4 values('&name',®no,&age,'&place'); Enter value for name: krish Enter value for regno: 1004 Enter value for age: 12 Enter value for place: pune old 1: insert into cool4 values('&name',®no,&age,'&place') new 1: insert into cool4 values('krish',1004,12,'pun values('krish',1004,12,'pune') e') 1 row created. SQL> / Enter value for name: kannan Enter value for regno: 1003 Enter value for age: 12 Enter value for place: pondy old 1: insert into cool4 values('&name',®no,&age,'&place') new 1: insert into cool4 values('kannan',1003,12 values('kannan',1003,12,'pondy') ,'pondy') insert into cool4 c ool4 values('kannan',1003,12,'pondy') * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SYS_C004262) violated
SQL> / Enter value for name: ganesh Enter value for regno: 1005 Enter value for age: null Enter value for place: panipet
old 1: insert into cool4 values('&name',®no,&age,'&place') new 1: insert into cool4 values('ganesh',1005,nu values('ganesh',1005,null,'panipet') ll,'panipet') insert into cool4 values('ganesh',1005,null,'panipet') * ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."COOL4"."AGE") SQL> create table cool6(name char(14),reg number,age number,place char(13),primary key(name,age)); Table created. SQL> insert into cool6 values('&name',®no,&age,'&place'); Enter value for name: guna Enter value for regno: 1004 Enter value for age: 12 Enter value for place: pune old 1: insert into cool6 values('&name',®no,&age,'&place') new 1: insert into cool6 values('guna',1004,12,'pun values('guna',1004,12,'pune') e') 1 row created. SQL> / Enter value for name: guna Enter value for regno: 1006 Enter value for age: 21 Enter value for place: panipet old 1: insert into cool6 values('&name',®no,&age,'&place') new 1: insert into cool6 values('guna',1006,21,'panipe values('guna',1006,21,'panipet') t') 1 row created. SQL> / Enter value for name: guna Enter value for regno: 1007 Enter value for age: 12 Enter value for place: burgur old 1: insert into cool6 values('&name',®no,&age,'&place') new 1: insert into cool6 values('guna',1007,12,'burg values('guna',1007,12,'burgur') ur') insert into cool6 c ool6 values('guna',1007,12,'burgur') * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SYS_C004265) violated
SQL> select * from cool6;
NAME REG AGE PLACE -------------- ---------- ---------- ------------guna 1004 12 pune guna 1006 21 panipet
FOREIGN KEY: SQL> create table par_cool(name char(12),age number(4),des char(9) primary key); Table created.
SQL> create table ch_cool(name char(12),des char(9) references par_cool(des)); Table created. SQL> > insert into par_cool values('&name',&age,'&des'); SP2-0734: unknown command beginning "> insert i..." - rest of line ignored. SQL> insert into par_cool values('&name',&age,'&des'); Enter value for name: arun Enter value for age: 12 Enter value for des: MD old 1: insert into par_cool values('&name',&age,'&des') new 1: insert into par_cool values('arun',12,'MD') 1 row created. SQL> / Enter value for name: kumar Enter value for age: 13 Enter value for des: GM old 1: insert into par_cool values('&name',&age,'&des') new 1: insert into par_cool values('kumar',13,'GM') 1 row created. SQL> / Enter value for name: ram Enter value for age: 21 Enter value for des: old 1: insert into par_cool values('&name',&age,'&des') new 1: insert into par_cool values('ram',21,'') insert into par_cool values('ram',21,'') * ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."PAR_COOL"."DES")
SQL> / Enter value for name: ram Enter value for age: 21 Enter value for des: MD old 1: insert into par_cool values('&name',&age,'&des') new 1: insert into par_cool values('ram',21,'MD') insert into par_cool values('ram',21,'MD') * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SYS_C004286) violated SQL> insert into ch_cool values('&name','&des'); Enter value for name: arun Enter value for des: MD old 1: insert into ch_cool values('&name','&des') new 1: insert into ch_cool values('arun','MD') 1 row created. SQL> /
Enter value for name: ranjan Enter value for des: GM old 1: insert into ch_cool values('&name','&des') new 1: insert into ch_cool values('ranjan','GM') 1 row created. SQL> / Enter value for name: kannan Enter value for des: steno old 1: insert into ch_cool values('&name','&des') new 1: insert into ch_cool values('kannan','steno') insert into ch_cool values('kannan','steno') * ERROR at line 1: ORA-02291: integrity constraint (SCOTT.SYS_C004287) violated - parent key not found
SQL> select * from par_cool;
NAME AGE DES ------------ ---------- --------arun 12 MD kumar 13 GM SQL> select * from ch_cool;
NAME DES ------------ --------arun MD ranjan GM SQL> delete from par_cool where age=12; delete from par_cool where age=12 * ERROR at line 1: ORA-02292: integrity constraint (SCOTT.SYS_C004287) violated - child record found
SQL> delete from ch_cool where des='MD'; 1 row deleted.
SQL> select * from ch_cool; NAME DES ------------ --------ranjan GM SQL> select * from par_cool;
NAME AGE DES ------------ ---------- --------arun 12 MD kumar 13 GM SQL> delete from par_cool where des='MD';
1 row deleted. SQL> select * from par_cool;
NAME AGE DES ------------ ---------- --------kumar 13 GM
DML & DCL COMMANDS SQL> create table sub (name (name varchar(8),age number(5),mark1 number(5),mark1 number(4),mark2 number(4), 2 mark3 number(4)); Table created. INSERT COMMAND: TYPE 1: SQL> insert into sub values('&name','&age','&mark1','&mark2','&mark3'); values('&name','&age','&mark1','&mark2','&mark3'); Enter value for name: sachin Enter value for age: 12 Enter value for mark1: 78 Enter value for mark2: 78 Enter value for mark3: 67 old 1: insert into sub values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into sub values('sachin','12','78','78','67') 1 row created. SQL> / Enter value for name: ranjan Enter value for age: 22 Enter value for mark1: 89 Enter value for mark2: 78 Enter value for mark3: 65 old 1: insert into sub values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into sub values('ranjan','22','89','78','65') 1 row created. SQL> select * from sub;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- ------------------------sachin 12 78 78 67 ranjan 22 89 78 65
TYPE 2: SQL> insert into sub(name,age,mark1,mark2,mark3) values ('ram',20,90,90,98); 1 row created.
SQL> select * from sub;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- ---------------------------------------------------------
sachin ranjan ram
12 22 20
78 89 90
78 78 90
67 65 98
TYPE 3: SQL> insert into sub values ('gayathri',13,90,100,98); 1 row created. SQL> select * from sub;
NAME -------sachin ranjan ram gayathri
AGE MARK1 MARK2 MARK3 ---------- -----------------------------12 78 78 67 22 89 78 65 20 90 90 98 13 90 100 98
UPDATE COMMAND: SQL> update sub set age=13 where name='ranjan'; 1 row updated. SQL> select * from sub;
NAME -------sachin ranjan ram gayathri
AGE MARK1 MARK2 MARK3 ---------- ---------- -------------- -----------12 78 78 67 13 89 78 65 20 90 90 98 13 90 100 98
SQL> alter table sub add(total number(5)); Table altered. SQL> select * from sub;
NAME AGE MARK1 MARK2 MARK3 TOTAL -------- ---------- ---------- ---------- ---------- ------------------------------sachin 12 78 78 67 ranjan 13 89 78 65 ram 20 90 90 98 gayathri 13 90 100 98
SQL> update sub set total=mark1+mark2+mark3; 4 rows updated. SQL> select * from sub;
NAME AGE MARK1 MARK2 MARK3 TOTAL -------- ---------- ---------- ---------- ---------- ---------------------------------------------------------------
sachin ranjan ram gayathri
12 13 20 13
78 89 90 90
78 78 90 100
67 65 98 98
223 232 278 288
SQL> select avg(mark1) from sub; AVG(MARK1) ---------86.75 SQL> select count(name) from sub; COUNT(NAME) ----------4 SQL> select sum(mark2) from sub; SUM(MARK2) ---------346 DELETE COMMAND: SQL> delete from sub where name='ram'; 1 row deleted. SQL> select * from sub; NAME AGE MARK1 MARK2 MARK3 TOTAL -------- ---------- ---------- ---------- -----------------------------------------sachin 12 78 78 67 223 ranjan 13 89 78 65 232 gayathri 13 90 100 98 288 SQL> delete from sub; 3 rows deleted. SQL> select * from sub; no rows selected
JOINS SQL>create table emp1(ename char(10),eno number(3),mangerno number(3)); Table created. SQL> insert into emps1 values('&ename',&eno,&emangerno); Enter value for ename: naga Enter value for eno: 101 Enter value for emangerno: 121 old 1: insert into emps1 values('&ename',&eno,&emangerno) new 1: insert into emps1 values('naga',101,121) 1 row created. SQL> / Enter value for ename: navin Enter value for eno: 111 Enter value for emangerno: 121 old 1: insert into emps1 values('&ename',&eno,&emangerno) new 1: insert into emps1 values('navin',111,121) 1 row created. SQL> / Enter value for ename: ram Enter value for eno: 121 Enter value for emangerno: 131 old 1: insert into emps1 values('&ename',&eno,&emangerno) new 1: insert into emps1 values('ram',121,131) 1 row created. SQL> / Enter value for ename: raj Enter value for eno: 141 Enter value for emangerno: 131 old 1: insert into emps1 values('&ename',&eno,&emangerno) new 1: insert into emps1 values('raj',141,131) 1 row created. SQL> create table t able customers1(eno number(3),salary number(9,2),location char(10)); Table created.
SQL>insert into customers1 values(&eno,&salary,'&location'); Enter value for eno: 101
Enter value for salary: 10000 Enter value for location: chennai old 1: insert into customers1 values(&eno,&salary,'&location') new 1: insert into customers1 values(101,10000,'chennai') 1 row created. SQL> / Enter value for eno: 111 Enter value for salary: 10500 Enter value for location: madurai old 1: insert into customers1 values(&eno,&salary,'&location') new 1: insert into customers1 values(111,10500,'madurai') 1 row created. SQL> / Enter value for eno: 131 Enter value for salary: 8000 Enter value for location: pondy old 1: insert into customers1 values(&eno,&salary,'&location') new 1: insert into customers1 values(131,8000,'pondy') 1 row created. SQL> / Enter value for eno: 141 Enter value for salary: 5000 Enter value for location: avadi old 1: insert into customers1 values(&eno,&salary,'&location') new 1: insert into customers1 values(141,5000,'avadi') 1 row created. SQL> / Enter value for eno: 151 Enter value for salary: 9800 Enter value for location: loco old 1: insert into customers1 values(&eno,&salary,'&location') new 1: insert into customers1 values(151,9800,'loco') 1 row created.
SQL> select * from emps1; ENAME ---------naga navin
ENO ---------101 111
MANGERNO ---------121 121
ram raj
121 141
131 131
SELF JOINS: SQL> select emps1.ename,emps1.eno,emps1.mangerno from emps1 where emps1.eno=emps1.mangerno; no rows selected SQL> select * from customers1; ENO ---------------101 111 131 141 151
SALARY ---------------10000 10500 8000 5000 9800
LOCATION ---------------chennai madurai pondy avadi loco
EQUI JOINS: SQL>selectemps1.ename,emps1.eno,cus selectemps1.ename,emps1.eno,customers1.eno,customers1.salary tomers1.eno,customers1.salary from emps1,customers1 where emps1.eno=customers1.eno; ENAME ---------naga navin raj
ENO ---------101 111 141
ENO ---------101 111 141
SALARY ---------10000 10500 5000
NON EQUI JOINS: SQL> select emps1.ename,emps1.eno,cus emps1.ename,emps1.eno,customers1.salary,customers1.eno,cus tomers1.salary,customers1.eno,customers.location tomers.location from emps1,customers1.eno<>cus emps1,customers1.eno<>customers1.eno; tomers1.eno; ENAME ENO SALARY ENO LOCATION ---------- ---------- ---------- ---------- ---------navin 111 10000 101 CHENNAI ram 121 10000 101 CHENNAI raj 141 10000 101 CHENNAI naga 101 10500 111 PONDY ram 121 10500 111 PONDY raj 141 10500 111 PONDY naga 101 8000 131 AVADI navin 111 8000 131 AVADI ram 121 8000 131 AVADI raj 141 8000 131 AVADI naga 101 5000 141 LOCO navin 111 5000 141 LOCO ram 121 5000 141 LOCO naga 101 9800 151 CHOOLAI navin 111 9800 151 CHOOLAI ram 121 9800 151 CHOOLAI raj 141 9800 151 CHOOLAI
17 rows selected. LEFT OUTER JOINS: SQL>select emps1.ename,emps1.eno,custo emps1.ename,emps1.eno,customers1.salary,customers1.eno mers1.salary,customers1.eno from emps1,customers1 where emps1.eno(+)=customers1.eno; ENAME ---------naga navin raj
ENO ---------101 111 141
SALARY ---------10000 10500 8000 5000 9800
ENO ---------101 111 131 141 151
RIGHT OUTER JOINS:
SQL> select emps1.ename,emps1.eno,cus emps1.ename,emps1.eno,customers1.eno,customers1.salary tomers1.eno,customers1.salary from emps1,customers1 where emps1.eno=customers1.eno(+); ENAME ---------naga navin ram raj
ENO ENO SALARY ---------- ---------- ---------101 101 10000 111 111 10500 121 141 141 5000
RELATIONAL OPERATION SQL> select * from sub;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- --------------------------------------------------------ranjan 21 89 78 80 gayathri 12 78 89 90 rajini 22 90 87 56 sachin 12 89 90 78 ram 21 90 67 78 SQL> select * from sub where age<20;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- ------------------------------------------------------gayathri 12 78 89 90 sachin 12 89 90 78
SQL> select * from sub where age>20;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- ------------------------------------------------------ranjan 21 89 78 80 rajini 22 90 87 56 ram 21 90 67 78 SQL> select * from sub where age>=21;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- ------------------------------------------------------ranjan 21 89 78 80 rajini 22 90 87 56 ram 21 90 67 78 SQL> select * from sub where age<>22;
NAME AGE MARK1 MARK2 MARK3 -------- ---------- ---------- ---------- ------------------------------------------------------ranjan 21 89 78 80 gayathri 12 78 89 90 sachin 12 89 90 78 ram 21 90 67 78
SQL FUNCTION Select abs(-6) from dual; ABS(-6) --------------6 SQL> select cos(0) from dual; COS(0) --------------1 SQL>select cosh(3) from dual; Cosh(3) -------------10.067662 SQL>select exp(0) from dual; EXP(0) --------------1 SQL>select ceil(4.56) from dual; CEIL(4.56) -------------------5 SQL>select floor(4.5) from dual; FLOOR(4.5) ------------------4 SQL>select ln(9) from dual; LN(9) ---------------------------2.1972246 SQL>select power(6,3) from dual; POWER(6,3) ---------------------216 SQL>select sign(-5) from dual; SIGN(-5) --------------------1 SQL>select sin(0) from dual; SIN(0) -------------------0
SQL>select concat(‘get’,’ready’)from dual; CONCAT -----------------------
Get ready SQL>select initcap(‘my country’)from dual; INITCAP(‘MY COUNTRY’) ------------------------My country SQL>select lower (‘APPLE’)from dual; LOWER --------------------Apple SQL>select upper (‘apple’)from dual; UPPER -------------------APPLE SQL>select lpad(‘abc’,5,’**’) from dual; LPAD(‘ABC’,5,’**’) ---------------------**abc SQL>select rpad(‘abc’,5,’**’) from dual; RPAD(‘ABC’,5,’**’) --------------------------abc** SQL> select replace(‘aprc’,r,’e’) replace(‘aprc’,r,’e’) from dual; REPL ----------------------apec SQL> select substr(‘INKPEN’,4,6) from dual; dual; SUB --------------------PEN SQL> select select length(‘time’) from dual; LENGTH(‘TIME’) ------------------4
SQL> select greatest(‘aaa’,’xxx’) greatest(‘aaa’,’xxx’) from dual; GRE ---------------xxx SQL> select least(‘ddd’,’aaa’) least(‘ddd’,’aaa’) from dual; LEA
---------------aaa SQL> select sysdate from dual; SYSDATE ----------------16-sep-06 SQL> select last_day(‘8_nov_06’) last_day(‘8_nov_06’) from dual; LAST_DAY ---------------------30-NOV-06 SQL> select mod(5,2) from dual; dual; MOD(5,2) -----------------1 SQL> select round(543.877,3)from dual; ROUND(543.877,3) --------------------543.877 SQL> select add_months(‘8_nov_06’,4)from add_months(‘8_nov_06’,4)from dual; ADD_MONTHS -------------------08-MAR-06 SQL> select last_day(‘7-nov-06’)from dual; LAST_DAY -------------------30-NOV-06 SQL> select months_between(‘5-aug-06’,’3-dec-06’)from dual; MONTHS_BETWEEN(‘5-aug-06’,’3-dec-06’) -----------------------------15.93548
SQL> select next_day(‘7-dec-06,’’fri’)from dual; NEXT_DAY(‘7-dec-06,’’fri’) ----------------------14-DEC-06 SQL> select to_date(‘3-4-06’,’mm-dd-yy’) from dual; TO_DATE(‘3-4-06’,’mm-dd-yy’) ---------------------------04-mar-06 SQL>select * from aa;
NAME AGE MARK1 MARK2 -----------------------------------------------------------Mani 18 98 83 Ramesh 19 98 89
SQL>select avg(mark1) from aa; AVG(MARK1) -------------------98 SQL>select sum(mark2) from aa; SUM(MARK2) ------------------172 SQL>select count(name) from aa; COUNT(NAME) ---------------------2 SQL>select max(mark1) from aa; MAX(MARK1) -------------------98 SQL>select min(age) from aa; MIN(AGE) --------------------18
ARITHMETIC OPERATION
MAIN PROGRAM:
declare a number(10); b number(10); c number(10); d number(10); e number(10); f number(10); g number(10); begin a:=&a; b:=&b; c:=a+b; d:=a-b; e:=a*b; f:=a/b; g:=a%b; dbms_output.put_line('The addition of two numbers is '||c); dbms_output.put_line('The subraction of two numbers is '||d); dbms_output.put_line('The multiplication of two numbers is '||e); dbms_output.put_line('The division of two numbers is '||f); dbms_output.put_line('The division of two numbers is '||g); end; INPUT & OUTPUT:
Enter value for a: 6 old 10: a:=&a; new 10: a:=6; Enter value for b: 5 old 11: b:=&b; new 11: b:=5; The addition of two numbers is 11 The subraction of two numbers is 1 The multiplication of two numbers is 30 The division of two numbers is 1 The division of two numbers is 1 PL/SQL procedure successfully compl eted.
EBILL WITHOUT CURSOR
BEFORE EXECUTION
SQL> select * from ebill; CNAME CNO NOU ---------- ---------- ---------- ---------ram 101 100 0 ranjan 102 250 0 gayathri 203 23 0
BILL
PL/SQL BLOCK:
Declare Cn ebill.cname%type; No ebill.cno%type; Units ebill.nou%type; Cost ebill.bill%type; Begin No:=&customerno; Select cname,cno,nou,bill into cn,no,units,cost from ebill where cno=no; If(units<200)then Cost:=units*0.5; Elsif (units between 200 and 500) then Cost:=units*0.75; Else Cost:=units; End if; Update ebill set bill=cost where cno=no; End;
INPUT & OUTPUT:
Enter value for customerno: 101 old 7: No:=&customerno; new 7: No:=101; PL/SQL procedure successfully completed. AFTER EXECUTION:
SQL> select * from ebill; CNAME CNO NOU BILL ---------- ---------- ---------- ---------ram 101 100 50 ranjan 102 250 0 gayathri 203 23 0
FIBONACCI SERIES
PROGRAM: declare
a b c n
number(3); number(3); number(3); number(4);
begin
n:=&n; c:=0; a:=-1; b:=1; for i in 1..n loop c:=a+b; a:=b; b:=c; dbms_output.put_line(c); end loop; end;
INPUT & OUTPUT:
Enter value for n: 5 old 7: N:=&N; new 7: N:=5; 0 1 1 2 3 PL/SQL procedure successfully completed.
FUNCTIONS-BANK DETAILS select * from bank; ACCNO -----101 102 103
CUNAME BALANCE -------------------- -------------------------siva 6000 vinu 5000 san 6709
Main program: declare no number(10); ch char(10); am number(10); bb number(10); begin no:=&no; ch:='&ch'; am:=&am; if ch='w'then bb:=withdrl(no,am); update bank set balance=bb where accno=no; elsif ch='d'then bb:=got(no,am); update bank set balance=bb where accno=no; else dbms_output.put_line('no char found'); end if; end;
Withdrawal function: create or replace function withdrl(acno in number,amt in number) return number is bal number(10,2); begin select balance into bal from bank where accno=acno; if(amt
Deposit function: create or replace function got(acno in number,amt in number) return number is bal number(10,2); number(10,2); begin select balance into bal from bank where accno=acno; bal:=bal+amt; return(bal); end;
INPUT & OUTPUT: SQL> @z:\dbms\bank\withdrawl.sql 9 / Function created.
SQL> @z:\dbms\bank\deposit.sql 7 / Function created. SQL> @z:\dbms\bank\bank.sql 20 / Enter value for no: 101 old 7: no:=&no; new 7: no:=101; Enter value for ch: w old 8: ch:='&ch'; new 8: ch:='w'; Enter value for am: 500 old 9: am:=&am; new 9: am:=500; PL/SQL procedure successfully completed. SQL> select * from bank; ACCNO CUNAME BALANCE ---------- -------------------- -----------------------------101 siva 5500 102 vinu 5000 103 san 6709 SQL> @z:\dbms\bank\bank.sql 20 / Enter value for no: 102 old 7: no:=&no; new 7: no:=102; Enter value for ch: d old 8: ch:='&ch'; new 8: ch:='d'; Enter value for am: 1000 old 9: am:=&am; new 9: am:=1000; PL/SQL procedure successfully completed. SQL> select * from bank; ACCNO CUNAME BALANCE ---------- -------------------- -------------------------101 siva 5500 102 vinu 6000 103 san 6709
PROCEDURE-ADDITION OF TWO NUMBERS MAIN PROGRAM:
declare get number(5); f1 number; f2 number; begin f1:=&f1; f2:=&f2; aq(f1,f2,get); dbms_output.put_line('the value is:'||get); end; SUBPROGRAM:
create or replace procedure aq(no1 in number,no2 in number,result out number) is begin result:=no1+no2; end;
INPUT & OUTPUT:
SQL> @z:\dbms\add1.sql @z:\dbms\add1.sql 5 / Procedure created. SQL> @z:\dbms\add2.sql @z:\dbms\add2.sql 11 / Enter value for f1: 4 old 6: f1:=&f1; new 6: f1:=4; Enter value for f2: 6 old 7: f2:=&f2; new 7: f2:=6; the value is:10 PL/SQL procedure successfully completed.
REVERSAL OF A NUMBER
Program:
Declare
n number:=&n; c varchar2(20); s varchar2(20); l number; begin c:=to_char(n); l:=length(c); while(l>0) loop s:=s||substr(c,l,1); l:=l-1; end loop; dbms_output.put_line('reverse'||s); end;
INPUT & OUTPUT:
Enter value for n: 567 old 2: n number:=&n; new 2: n number:=567; reverse765
EBILL WITH CURSOR BEFORE EXECUTION: CNAME CNO NOU BILL -------------------------------------------Arun 100 100 Balu 200 200 Carl 300 300 PL/SQL BLOCK:
Declare Cn ebill.cname%type; No ebill.cno%type; Units ebill.nou%type; Cost ebill.bill%type; Cursor c is select * from ebill; T ebill.cno%type; Begin Open c; Loop Exit when c% not found; If(c%is open)then Fetch c into cn,no,units,cost; T:-no; If(units<200)then Cost:=units*0.5; Elseif units between 200 and 500 then Cost:=units*0.75; Else Cost:=units; Endif; Update ebill set bill=cost where cno=t; Endif; Endloop; Close c; End;
AFTER EXECUTION
SQL>select * from ebill; Cname cno nou bill ------------------------------------------Arun 100 100 50 Balu 200 200 150 Carl 300 300 225
FACTORIAL OF A NUMBER
MAIN PROGRAM:
DECLARE n number(10); s number(10); BEGIN n:=&n; s:=1; while(n>=1) loop s:=s*n; n:=n-1; end loop; DBMS_OUTPUT.PUT_LINE(s); END;
INPUT & OUTPUT:
SQL>Enter value for n: 4 old 5: n:=&n; new 5: n:=4; 24
FUNCTION-PHONE DETAILS
BEFORE EXECUTION SQL> select * from phone; NAME PHONENO CITY -------------------- ---------- -------------------aaa 246789 chennai ccc 346578 avadi eee 246789 salem ddd 4536789 ooty
STATE -------------tamilnadu tamilnadu tamilnadu tamilnadu
MAIN PROGRAM: declare na phone.name%type; pho phone.phoneno%type; phone.phoneno%type; ci phone.city%type; sta phone.state%type; phone.state%type; begin pho:=&phone_number; na:=ph(pho); select city,state into ci,sta from phone where name=na; dbms_output.put_line('phone number is:'||pho); dbms_output.put_line('n dbms_output.put_line('name ame is:'||na); dbms_output.put_line('c dbms_output.put_line('city ity is:'||ci); dbms_output.put_line('state is:'||sta); end;
FUNCTION: create or replace function ph(phnum in number)return varchar as uname varchar(20); na varchar(20); begin select name into uname from phone where phoneno=phnum; na:=uname; return na; end;
INPUT & OUTPUT: SQL> set serveroutput on SQL> @z:\dbms\fun1.sql @z:\dbms\fun1.sql 9 / Function created. SQL> @z:\dbms\fun2.sql @z:\dbms\fun2.sql 15 / Enter value for phone_number: phone_number: 4536789 old 7: pho:=&phone_number; pho:=&phone_ number; new 7: pho:=4536789; pho:=4536789; phone number is:4536789 name is:ddd city is:ooty state is:tamilnadu PL/SQL procedure successfully completed.
IMPLICIT CURSOR
SQL> select * from bank; CNAME ACCNO AMT ------------- ---------- ---------ram 1001 3000 ranjan 1002 3500 gayathri 2002 3900 MAIN PROGRAM:
Declare M bank.amt%type; E number(2):=0; Name bank.cname%type; Cursor c is select accno from bank; Begin For I in c Loop Update bank set amt=amt-100 where accno=i.accno; Exit when sql%notfound; Select cname,amt into name,m from bank where accno=i.accno; Dbms_output.put_line(‘name Dbms_output.put_line(‘name is:’||name); Dbms_output.put_line(‘changed Dbms_output.put_line(‘changed amount is:’||name); E:=e+to_char(sql%rowcount); End loop; Dbms_output.put_line(‘the total rows selected is:’||e); End; INPUT & OUTPUT: name is:ram changed amount is:2900 name is:ranjan changed amount is:3400 name is:gayathri changed amount is:3800 the total rows selected are:3
PL/SQL procedure successfully completed.
REVERSAL OF A STRING
PROGRAM: declare c char(10); s varchar2(20); varchar2(20); l number; begin c:='&c'; l:=length(c); while(l>0) loop s:=s||substr(c,l,1); l:=l-1; end loop; dbms_output.put_line('r dbms_output.put_line('reverse'||s); everse'||s); end;
INPUT & OUTPUT: Enter value for c: fat old 6: c:='&c'; new 6: c:='fat'; reverse taf PL/SQL procedure successfully completed.
TRIGGERS
SQL> CREATE TABLE T4 (a INTEGER, b CHAR(10)); Table created. SQL> CREATE TABLE T5 (c CHAR(10), d INTEGER); Table created. MAIN PROGRAM:
SQL> CREATE TRIGGER trig1 2 AFTER INSERT ON T4 3 REFERENCING NEW AS newRow 4 FOR EACH ROW 5 WHEN (newRow.a <= 10) 6 BEGIN 7 INSERT INTO T5 VALUES(:newRow.b, :newRow.a); 8 END trig1; 9 / Trigger created. INPUT & OUTPUT: SQL> insert into into t4 values(&a,'&b'); Enter value for a: 4 Enter value for b: gy old 1: insert into t4 values(&a,'&b') new 1: insert into t4 values(4,'gy')
1 row created. SQL> / Enter value for a: 67 Enter value for b: hj old 1: insert into t4 values(&a,'&b') new 1: insert into t4 values(67,'hj') 1 row created. SQL> / Enter value for a: 10 Enter value for b: jko old 1: insert into t4 values(&a,'&b') new 1: insert into t4 values(10,'jko') 1 row created.
SQL> select * from t4;
A ---------4 67 10
B ---------gy hj jko
SQL> select * from t5; C D ---------- ---------gy 4 jko
10
