I I

1
II i'il 111111 II
I I

Question l:
(a) Create a table called Empl~yee with the following structure.
Empno (Number), Ename (Varchar2(20)), Job (Varchar2(20) ), Sal (Number)
(i) Add a column DoJ (DATE) with the Employee table.
(ii) Insert any five records into the table.
(iii) Update the column details of job
(iv) Rename the column Sal as Emp_Salary of Employee table using alter
(v) Delete the employee whose empno is 101.
(b) Write a PL/SQL program to demonstrate Exceptioris.
2022.11.13 13:53
 ur · ~ie Ja~aDase 11g Express Edition Release 11. 2 . 0 . 2 . 0 - 64bit Production

SQL> create table employee1(empno numbe r ,ename varchar2(20) ,job var char2(20),sal number);
Table created.

' SQL> desc employee1;

Name
Ntdl? Type
-----------------------------------------
EMPNO ----------------------------
EN.NIE NUMBER
JOO VARCHAR2(2'0)
SAL VARCHAR2(20)
NUMBER
SQL> alter table employee1 add (doj date);
Table altered.

Null? Type

vivo V1~fJJ
Perfect Shot 2022.11.13 13:Li
<'
~ I.\ ~ ~ v,
w ., '~ ,0 ~
r-
\111
,ti-S
r-
a V a V
i E ~
:,
~- .....
:, I-'•
.,n .,n ~., .,n :,
ti) '-'' Ill
l'O ffl l'1) I'\) re,
~ ~
., c:u
~ rt r+
..,
ft) fl) 11)
r+
0.. .... Q. C. ....
I :, I I
....::::, ::,
rt rt rt
0 0 0
l'D 11) 11)
a a 3
"tJ "'O "'C
..... ..., ...,
0 0 0
'< '< '<
l'D l't) l't)
t1) l't) 11)
t- t- ~

<
Q) Q)
..., Q)
...,< ...,<
C C: C:
l'D l't) 11)
11'1 IJ'I IJ'I
;-..,.
""'
.... t-
'"' Ii-\
a, CS) CS)
w ~ ..,.:.
". ". "" .
Q)
..,.< 0-
a ::r
a n Q)
C ~
. '< <
Q)
". . .
n ". "" .
:r .., IJ'I
l'D
..,. 0 11)
0- .
~ 0
. ""
"~ ..,.rt (S)
.
n CS)
a, 11'1 (S)
(S) . (S)
u, (S)
N "~ (S)
~ (S)
(S) (S) "" .
u, N
(S)
".
"-l
N (S) I
~ (S) w
u• ". l>
2
N I
~ w I\.)
I ~
I\.)
'
m '-" t'-l
1\,:1
N t •
I\.) I
., N
~·
~ "~
... CD
N
I"-"
.._...."
...
 EMPNO ENAME JOB SAL DOJ
101 bhava 10000 00 22-JA N-22
102 vicky 10050 00 23-JA N-22
103 ammu 10052 00 24-JA N-22
SQL> upda te empl oyeel set job= 'driv er' where (enam e='vi cky') ;
1 row upda ted.

SQL> selec t*fro m emplo yee1;

EMPNO Ef\JAME SAL DOJ

---------- -------------------- -------------------- ---------- ----- ----
101 bhava 10000 00 22-JA N-22
102 vicky 10050 00 23-JA N-22
vivo V19
Perfect Shot
103 8fflfflU 10052 00 24-JA N-22 2022.11 .13 13:S1
 SQL> alter table employee1 rename column sal to emp_salary;
Table altered.

'. SQL> select*f ram employee1;

EMPNO ENAME EMP_SALARY DOJ

101 bhava 1000000 22-JAN-22

102 vicky 1005000 23-JAN-22
103 ammu 1005200 24-JAN-22

SQL> delete employee1 where empno=1;

0 rows deleted.
SQL> delete employee1 where empno=101;
1 row deleted.

SQL> select*from employee1;

EMPNO ENAME JOB EMP_SALARV DOJ

102 vicky driver 1005000 23-JAN-22

vivo V19 103 ammu cheif 1005200 24-JAN-22
·• Perfect Shot 2022.11.13 13:S1
 □ of2
Questio n :>:: -
-(a) Create departr nent
Deptno {Number), Dep
(i) Add column designa ti:.
(ti) ~err values into the t ,
(iii) List. the records of emp
(iv) Update the record where .,
(v) Delete any column data fro
11

(b) \Vrite PL/SQL queries to er

 --· ,,.~, . ,,, u, 1v11 11n • " ltlfl lll
\

SQL> crt 1te tib le dtp 1rt mt nt( de ptn

0 numbtr,dtptnamt v,r ch ,r( 20 ),l
oc ,ti 0n v1 rch 1r2 (20 ));
T1b le cre ■ ted.
SQL> desc department;
Name
-- -- -- -- -- -- ··· ·- ··· ··· ··· ··· ··· ··· ··· ··· Null? Type
DEPTNO -------· NUMBER
DEPTNAME
LOCATION VARCHAR2(20)
VARCHAR2(20)
SQL> al ter t abl e department add
(de sig na tio n va rch ar2 (20 ));
ab le alt ere d.
QL> desc department;
Name
Null? Type
OEPTNO ---------------------------- -------
OEPTNAME NUMBER
LOCATION VARCHAR2(20)
DESIGNATION VARCHAR2(20)
VARCHAR2(20)
QL> ins ert int o department value
s(101, 'ro bo tic s' ,'a va di ' ,'c he if'
);
row cre ate d.
QL> ins ert int o department value
s(102, 'ho stp ita l', 'an na nu r' ,'d oc
to r') ;
row cre ate d.
QL> ins ert int o department va lue
s(S ,'c ou rt' , 'ko ra ttu r', 'la wy er' );
row cre ate d.
QL> se lec t*from department;
DEPTNO DEPTNAME
--- -- - -- - - --- --- - -- - -- -- LOCATION DESIGNATION .
-- - - - - - - --- - - - ~-.- --- - - -·-
101 rob oti cs avad i
- :-~~~.f:. :-~~?t :..._::=.· .:. ___ -~-- -
102 host pi ta l . . ·. '· ' c' ~
';. t':~nn~anl.ir/ -.
. .5 , ~}?U rt
'I i J .;•J:· doctor. ' /-="'; ,:,:·:...:~.
· ,'.-".·r, ·l
~-- 1 1 • kol"attur. lawyer. <i ···c"t~. i.-:i .. _:.
n
Q_
 select deptno deptname oca ion

ERROR at line 1:
ORA.-00923: FROM keyword not found where expected

SQL> select deptno deptname from department group by deptno;

----------

vivo V19
Perfect Shot 2022.11.13 1'4:17
SQL> update department set deptno=103 where (location='korattur');
1 rm-.1 updated .

SQL> select*from department;

DEPTNO DEPTNAME LOCATION DESIGNATION

---------- -------------------- avadi
101 robotics
-------------------- --------------------
cheif
102 hostpital
163 court

0 rows deleted.

SQL> delete departlle

1 row deleted.

SQL> select-from,:deP.ar.tment;
-.:·,:;;
..,;;,.·
OEPTNO -DEs,NAME l!OCATION DESIGNAliION
:j!·~·-,
---------- ~-- -~--------
19l)jr.obotics avai:Ji ctieif.
102· liostP.ital!
;;_ I
annanur. <ioctor.
;,., .
 Ques tion No. 3
A). Create a table called Custo mer table

Name Type
Cust name V arcbar2(20)
Cust street Varcb ar2(20)
Cust city V arcba r2(20)

a. Insert recor ds into the table.

b. Add salary colum n to the table.
c. Alter the table column domai n.
d. Drop salary colum n of Lhe custo mer table.
e. Delete the rows o f customer table whose cust_city is ,,hyd" .
B ). Write PUS QL queri es to create Triggers.
 Tabl e c r eat ed .

SQL> desc customer;

Name.
Null? Type
- --- - - - -- - - ---------------------- --------
CUST_NAME
VARCHAR2(20)
CUST STREET
VARCHAR2(20)
cusr: cuv VARCHAR2(20)

SQL> inse rt into custoaer va lues( • vicky •. • avadi •. • chennai · ) ;

1 ro1111 created .

SQL> insert into custoaer values("aam". "pakkam". 'tiruvallur' );

1 row created .

SQL> inse rt into custa.!r values(• bhava •. · pettai' • 'salem' ) ;

1 row created .

SQL> · s ele ctSfraa custa.er;

CUST_NAME CUST_CITV .
~henn~i~£;.: :,.:.: . "',,.. _. _;,_, :, , .
viclcy avadi
paldcaa .tiruvallur.
pettai salem
~- .. ,r;;,_:.;f-~
SQL> alter table custaer add(sa,}-arY. niaiber);
\.-►.-.ii.~
Table a ltered. ., ..
SQL> selectS-froa . ... ) .. ,~ :~-

t QJST NAME STREET CUST_CITV. SALARY.

! --- - ----- ~....- .. --------------- --------------------
vivo J ,~clcy ~~~:::~lur.
Perfec~ pt_~ ,- · salena
 SQL> alte r tab le cus
tom er mo dify (sal ary
num ber( 6));
Tab le alte red .

SQL> alte r tab le cus

tom er dro p(s alar y num
alte r tab le cus tom er dro p(s ala ry num ber( 6});
ber( 6})
ERROR at lin e 1: "'
ORA -009 07: 11is sing
rig ht par ent hes is

SQL> alt er tab le cus

tom er mod ify( sala ry
num ber( 6));
Tab le alte red .

SQL > sele rtSf ro1 1 cus tom er;

CUST_NAHE CUST_STREET CUS T_C ITY

vick y ava di che nna i
annu pakkam tiru val lu r
· bha va pet tai sale m
SQL> alte r tab le cus tom er
drop column sala ry;
Tab le alte red .
SQL> sele rt•f rom custo■er;

CUST _NAME OJST_STREET

- --- ---------------- -------- ------ -- ----
vick y ava di
a• u pakkam
bha va pet tai

l : : ::::~
SQL> del ete custo11er whe re
cus t_c ity= 'sal em' ;;

cus<ooer: .
V1 9 C.UST NAME CUST _S:tREET
' ect Sh a~~..~-- ---- ---- 20 22 .11.
.- ---- _~~;;,i:~.~~~::~}~----- I
 Question No. 4
A) Create a table called branch table.

Name Type
Branch name V archar2(20)
Branch city V archar2(20)
asserts Number

a. Increase the size of data type for asserts to the branch.

b. Add and drop a column to the branch table.
c. Insert values to the table.
d. Update the branch name column
e. Delete any two columns from the table
B) Write PL/SQL code in Function for Factorial number and Prime number.
 SQL > c r ea-::e : aol e :ira n c h(br a nc h_ name varc har2 ( 20),branc h_city varchar2(20),asserts m1mber ) ;
- abl e c r e a .:ed.

SQL> desc bra nc h ;

l'Jame Null? Type
- - ---- - ---- -- - - -- - -- -- - -- - - -- - -- - --- -- -- -
BRANCH NAME
VARCHAR2(20)
BRANCH- CITY
VARCHAR2(20)
ASS ERTS NUMBER

SQL> alter table branch modify(asserts number(G));

Table altered.

SQL> desc branch;

Name Null? Type

BRANCH NAME VARCHAR2(20)

BRANCH- CITY VARCHAR2 ( 20)
ASSERTS NUMBER(G)

SQL> alter table add (pinc ode number(6));

alter table ;dd (pincode number(6))

ERROR at line 1:
ORA-00903: invalid table name

SQL> alter table branch add (pincode number(6));

Table altered.

SQL> desc branch;

Name Null? Type

BRANCH NAME VARCHAR2(20)

VARCHAR2(20)
V19 BRANCH=CITY
NUMBER(6)
feet Sho-e.ssERTS
PINCODE _NUM_B EI\(~_) .. ·•• .
 SQL> a:-:er- -::a::ile ~ranch drop(pi ncode );

SQt> cesc Dra.nch;

ffaae Null? Type

BRANCH NAME VARCHAR2( 20)

3AANCH-CITY VARCHAP.2( 20)
ASSERTS NUt-18ER( 6 )

SQL> insert into branch values(• indian•,•chennai",101.);

1 row crea---LE:d.

1 ro11 created.

SQL> if'iseM: iot.o bf-".,.nch v alU-05( • hr.lfc •, •madurai •, '.l.03);

1 l'OII created.

~ _err, ASSERTS

indi__a) ct.ennai
state -t:beni
hdfc aaaurai
vivo V1%lr!t.> update fw.ancb set brand)_n~nion where a~ser.ts=~01;
Perfect, S~9:1§,.. ~ set branch naae=union where asser,ts,:::~0!. 2022.11.13 16: 26
 SQL> upd ate branch set branch_name= 'union' 1~here assertse101;

1 r oH updated.

SQL> select- from branch;

BRANCH_NAME BRANCH_CITV ASSERTS

union chennai
state theni
hdfc madurai
SQL> delete branch column assert=101;
delete branch column assert=101

ERROR at line 1:
ORA-88933: SQL command not properly ended

SQL> delete branch assert=181;

delete branch assert=101
•
ERROR at line 1:
ORA-88933: SQL command not properly ended

SQL> delete branch assert 101;

delete branch assert 181
ERROR at: line 1: • ,;.;.iii/;/I}"'-" ~<F',
ORA-00933: SQL coiatand not proper,1~ ·ended
.,-ff'

SQL> delete branch whe~~ '., ai siJ{::101;

delete branch where . :!; .e~:=101
2022.11.13 16:
c!gshot ERROR at line 1: RT"::;:if ~valid ~identifier.
del ete from bra nch ~he re
asse rt=1 01
ERROR at line 1:
ORA - 089 04: "'ASSERT": inv
alid ide ntif ier

SQL> del ete from bran ch whe

re branch_ nam e='s tate ';
1 row del eted .

SQL> del ete fro■ bran ch where branch_name='h

dfc';
1 row del eted .

SQL> select&fro■ branch;

BRANCH_NAME BRANCH_CITV ASSERTS
--------------------
unio n chennai
SQL> •

2022 .11. 13 16:2 6

 SQL> set serveroutput on;
SQL> create or replace trigger display_salary_difference before insert or delete or update on emp1
2 for each rol-1
3 when(NEl-J .eno>8)
4 declare
5 salary_diff number;
6 begin
7 salary_diff:= :NEloJ.salary- :OLD. salary;
8 dbms_output.put_line('new salary:'! I :NEW.salary);
9 dbms_output.put_line('old salary:' I !:OLD.salary);
10 dbms_output.put_line('salary difference:'! lsalary_diff);
11 end;
12 /

Trigger created.
SQL> insert into empt values(t3.
2 insert into empt values(13.'bha'.1000);
insert into empt values(13.'bha".1000)
ERROR at line 2:
ORA-08936: lli.ssing expression

SQL> insert into empt values(t3. 'bha'.1.-000);:_.

new salary: 1099 _ .. '\:;/· :v.
old salary: . ,;/•·
salary difference:
·,
1 row created. . ~
-......-;.r·'

SQL> update eap1 set 's~lary=50 wtier.e eno=13;

new salary:58 _ ,: /;;""'
old salary: ____ ,. ·
salary difference:~950
- /!'JU·,

1 row ,: upd~ed.
ot .:.'"
SQL> set serveroutput on;
SQL > declare
23 begin cursor ce is select eno,ename,salary from emp1;
for x in ce
loop
6
7
dbms_0,u"t;p9t i:, i ~ ~t.
1
1

e nd loop; , ·;1;x~,er1~mel I" 'f fx.sal a ry);

8 end;
g I
11 bhava 10000
1 2 bhavani 10200
1 3 bha 50

PL/SQL proceduij~e · ccess

•- ..-~:.!--~-:-
, •f
SQL> set serveroutput on ,;
SQL> create or replace procedure sum(e IN number,b IN number) is c number;
2. begin
3 c: =a+b;
4
5 end; dbms_output. put_line('sum of two nos='I le);
6 /

Procedure created ..
SQL> declare
2 x nlaber;
3 y 11111mer;
4
5 x:=&x;
6 y:=&.y;
7 SUll(X,.y) ;
8 end;
g /
J:ntr-o.- value for x:
old 5: x:=&x;

successful!~ c~letecf.
SQl.f. ~
L> set se rve r out put on·
L> DECLAR E '
2 s_rollno students.rollno%type ·- &s_rollno;
3 s_name students.name%type;
4 s_address students.address%type·
5 e x_invalid_rollNo EXCEPTION; ,
6 BEGIN
7 IF s_rollno <= 0 THEN
8 RAISE ex_invalid_rollNo;
9 ELSE
10 select name~address INTO s_name~s_address FROM students
11 WHERE s rollno=rollno;
12 dbms_output.put_line(s_rollNo 11 • • 11 s_name 11 • • 11 s_address);
13 END IF;
14 EXCEPTION
15 WHEN ex invalid rollNo THEN
16 dbms_output.put:line( 'rollNc;> _.~u~t zero!•);
17 WHEN no data found THEN <}•, ,.
18 dbms output.put_linet•~~:,ri sucl, student!' •)';
19 WHEN-others THEN 1!:i:_
20 dbms_output. pu~~~_ine~ • Er.r.or. ! • ) ;
21 END; .. _··
22 / <.. :.-
Enter value fQ~ ~si 5ollno: 10f e := &s_r.ollno;
ld 2· s r.ollno students.rolino%tY.P.
~ew 2;
:S=~_o llno stu<ients. r.ollno%tyP.e . - 101;
101 bhava · av adi
... ~.i

,. -.-~ . successT.ull~ com~IetecJ.

PL/SQ~ ~r.oceaur.e
.:,);· ee2':'1 ~ os:17.
:1.
 I ~ ~• • I '-. •'

15 end loop ;
16 /

Warning : Function created with compilation errors.

SQL> set serveroutput on;
SQL> cr eate or replace function prime(n number)
2 r eturn number i s
3 r number;
4 c number := e;
5 begin
6 for i in 1 .. n loop
7 r : = aod(n,i ) ;
8 if (r=e) then
g C : = C+1;
10 end if;
11 end lOOP- '" >::·/c
12 retumiJc~-
~ I
13 endJ J!cime;
14 I .{;i",_.
<,<.:

vivo V19
Funcf:J;:; c
Perfect Shot 2022.11 .1Li 06:02
.. l r I mu t1set

SQL > Declare

2 n number(4) :=&n ;
3 c number(B) ;
4 begin
5 c :=prime(n) ;
6 if (c=2) then
7 DBMS_OUTPUT .PUT_LINE(nl I· is a prime number');
8 else
9 DBMS_OUTPUT.PUT_LINE(nll' is not a prime number');
10 end if;
end;
11
12
I
Enter value for n: 3
old 2 : n number( 4 ). : =&rf;
new 2: n n~~.!r.( 4 ) : = 3 ;
3 is a Prille '_L!H.imtier.

PL/SQL r.oc·'"