1.
EXCUTE A SINGLE LINE QUERY & GROUP FUNCTIONS
create table gp(id number(5),name varchar(20),salary number(10),address varchar(20));
insert into gp values(1,'jeeva',30000,'mumbai');
insert into gp values(2,'deva',40000,'chennai');
select * from gp;
select sum(salary)from gp;
select min(salary)from gp;
select max(salary)from gp;
select avg(salary)from gp;
select count(id),address from gp group by address;
select name,sum(salary)from gp group by name;
2.EXCUTE DDL COMMANDS
create table std(regno number(5),name varchar(10),age number(10),address
varchar(10),pincode number(10),phone number(10));
desc std;
insert into std values(1,'sathish',18,'chennai', 3232, 5656464);
insert into std values(2,'nithish',18,'cheyyar', 32232, 5656421);
select*from std;
alter table std add mailid varchar(15);
table altered
alter table std rename column mailid to email;
truncate table std;
Table truncated.
drop table std;
Table dropped.
3.EXCUTE DML COMMANDS
create table emp(emp_id number not null, emp_name varchar(10),emp_age number
(10),emp_phone number(16));
insert into emp values (100,'prakash',10,123456789);
insert into emp values (99,'prashanth',18,123456781);
insert into emp values (98,'praveen',23,123456782);
insert into emp values (97,'pranav',25,123456783);
select*from emp;
update emp set emp_name='praveen' where emp_id=98;
1 row(s) updated…
delete*from emp where emp_age=10;
1 row(s) deleted.
4.EXECUTE DCL&TCL COMMANDS
create table sale_d(emp_id number(10),emp_name varchar(10),salary number (10),address
varchar(16));
insert into sale_d values(11,'prithvi',48000,'chennai');
insert into sale_d values(12,'sathish',58000,'chennai');
insert into sale_d values(13,'naveen',47000,'chennai');
select*from sale_d;
create user prithvi identified by attu;
User created.
grant connect,resource to prithvi;
Statement processed.
group all on sale_d to prithvi;
grant select on sale_d to prithvi;
Statement processed.
USER:PRITHVI
select * from system.sale_d;
revoke select on sale_d to prithvi;
Statement processed.
USER:PRITHVI
select * from system.sale_d;
ORA-00942: table or view does not exist
5.IMPLEMENT THE NESTED QUERIES
create table dp(c_name varchar(20),acc_no varchar2(20));
insert into dp values ('SATHISH',900);
insert into dp values ('PRITHVI',907);
insert into dp values ('NAVEEN',999);
select*from dp;
create table bw(c_name varchar(20),loan_no varchar2(20));
insert into bw values('SATHISH',800);
insert into bw values('NAVEEN',999);
INSERT INTO bw VALUES('PRITHVI',607);
INSERT INTO bw VALUES('BHUVANA',202);
INSERT INTO bw values ('RAMYA',775);
insert into bw values('MANOJ',608);
select*from bw;
select c_name from dp where c_name in(select c_name from bw);
select c_name from bw where c_name not in(select c_name from dp);
select c_name from bw where exists(select * from dp where c_name=bw.c_name);
create table branch1(branch_name varchar(20),assets number(20),city varchar(30));
insert into branch1 values('MYANMAR',1000000,'RANIPET');
insert into branch1 values('MYANMAR',1000000,'RANIPET');
insert into branch1 values ('SPAIN',600000,'arni');
insert into branch1 values ('SPAIN',700000,'arni');
insert into branch1 values('LONDON',450000,'arni');
select *from branch1;
select branch_name,city from branch1 where assets < any (select assets from branch1 where
city='RANIPET');
select branch_name,city from branch1 where assets >=all (select assets from branch1 where
city='RANIPET');
6.IMPLEMENT JOIN OPERATIONS IN SQL
create table p_d (p_id number(25),p_name varchar(25),brand varchar(25),price varchar(25));
insert into p_d values(101,'Camera','Canon','30000');
insert into p_d values(102,'Television','Sony','80000');
insert into p_d values(103,'Phone','Oneplus','70000');
select*from p_d;
create table p_o (p_id number(20),o_id number(25),c_name varchar(25),T_unit number(25));
insert into p_o values(101,14,'Balaji',23);
insert into p_o values(102,18,'Sathish',28);
insert into p_o values(103,16,'Prithvi',27);
select*from p_o;
select p.p_id,p.p_name,o.o_id,o.T_unit from p_o o,p_d p where o.p_id(+)=P.p_id;
select p.p_id,o.c_name,o.o_id,o.T_unit from p_o o,p_d p where p.p_id=o.p_id(+);
select p.p_id,p.p_name,o.o_id,o.T_unit from p_o o,p_d p where o.p_id=p.p_id;
7.CREATE VIEW FOR A PARTICULAR TABLE
create table consumer (c_id varchar(10),c_name varchar(20),age number (20),c_address
varchar(20));
insert into consumer values('c11','SATHISH',19,'LONDON');
insert into consumer values('c12','RAMYA',18,'LONDON');
insert into consumer values('c13','PRTHVIRAJAN',18,'BANGALORE');
insert into consumer values('c14','NAVEENSILK',14,'SILUKUVARUPATTI');
insert into consumer values('c15','MANOJ',14,'MANNARKUDI');
select*from consumer;
create table product(p_id varchar(20),p_cost int);
insert into product values('p11',202);
insert into product values('p12',657);
insert into product values('p13',543);
insert into product values('p14',675);
insert into product values('p15',974);
select *from product;
create view cus_view as select c_id,c_name,age,p_cost,p_id from consumer,product;
Table created….
select *from cus_view;
select *from cus_view where age<18;
9.WRITE PL/SQL PROCEDURE FOR AN APPLICATION USING EXCEPTION
HANDLING
declare
a int:=10;
b int:=0;
answer int;
begin
answer:=a/b;
dbms_output.put_line('the result after division is'||answer);
exception
when zero_divide then
dbms_output.put_line('divide by 0 please check the value again');
dbms_output.put_line('a value is'||a);
dbms_output.put_line('b value is'||b);
end;
OUTPUT:
divide by 0 please check the value again
a value is10
b value is0
Statement processed.
10.WRITE PL\SQL PROCEDURE FOR AN APPLICATION USING CURSOR
create table sale_d(emp_id number(10),emp_name varchar(10),salary number (10),address
varchar(16));
insert into sale_d values(11,'prithvi',48000,'chennai');
insert into sale_d values(12,'sathish',58000,'chennai');
insert into sale_d values(13,'naveen',47000,'chennai');
select*from sale_d;
declare
cursor s is select*from sale_d;
m sale_d%rowtype;
begin
open s;
loop
fetch s into m;
update sale_d set salary=m.salary+15000 where emp_id=m.emp_id;
exit when s%notfound;
end loop;
close s;
end;
Statement proceesed……..
select*from sale_d;
11.WRITE PL\SQL PROCEDURE FOR AN APPLICATION USING FUNCTION
create table emp2( c_id number(10),c_name varchar(10));
insert into emp2 values (111,'reo');
insert into emp2 values (112,'juice');
insert into emp2 values (113,'raj');
select*from emp2;
create or replace function search(n number)return varchar2 is temp varchar2(20);
begin
select c_name into temp from emp2 where c_id=n;
return temp;
end;
Function created.
declare
op varchar2(20);
ip number(10);
begin
ip:=:ip;
op:=search(ip);
dbms_output.put_line('address : '||op);
end;
\
12.WRITE PL/SQL PROCEDURE FOR AN APPLICATION USING PACKAGE
create table bank(id number(19),salary number(20));
insert into bank values(11,45555);
insert into bank values(12,777745);
insert into bank values(13,454645);
select *from bank;
create package cust2 as procedure find_salary(j_id bank.id%type);
end cust2;
package created…….
create or replace package body cust2 as procedure find_salary(j_id bank.id%type)is csal
bank.salary%type;
begin
select salary into csal from bank where id=j_id;
dbms_output.put_line('salary or given emp id:'||csal);
end find_salary;
end cust2;
Package Body created.
declare
code bank.id%type:=12;
begin
cust2.find_salary(code);
end;
Statement processed.
OUTPUT:
salary or given emp id:777745
THIRUVALLUVAR UNIVERSITY
INDO-AMERICAN COLLEGE
CHEYYAR- 604 407
DEPARTMENT OF COMPUTER APPLICATIONS
RDBMS LAB
2022 - 2023
Reg.No :
Name :
Class : II – B.C.A
Subject : RDBMS LAB
THIRUVALLUVAR UNIVERSITY
THIRUVALLUVAR UNIVERSITY
INDO-AMERICAN COLLEGE
CHEYYAR- 604 407
DEPARTMENT OF COMPUTER APPLICATIONS
Certified to be the bonafide record of practical work done by
____________________________With Register Number ________________ in
this department during the Academic year 2022-2023
Staff–in-charge Head of the department
Submitted by BCA degree practical examination held on ________________
Indo-American college, Cheyyar -604 407.
Examiners
Place : 1.
Date : 2.
INDEX
S.No Date Title Page No Sign
EXECUTE A SINGLE LINE QUERY AND
1
GROUP FUNCTIONS
2 EXECUTE DDL COMMANDS
3
EXECUTE DML COMMANDS
4 EXECUTE DCL AND TCL COMMANDS
5
IMPLEMENT THE NESTED QUERIES.
6
IMPLEMENT JOIN OPERATIONS IN SQL
7 CREATE VIEWS FOR A PARTICULAR
TABLE
IMPLEMENT LOCKS FOR A PARTICULAR
8
TABLE.
WRITE PL/SQL PROCEDURE FOR AN
9 APPLICATION USING EXCEPTION
HANDLING
WRITE PL/SQL PROCEDURE FOR AN
10
APPLICATION USING CURSORS.
WRITE A PL/SQL PROCEDURE FOR AN
11 APPLICATION USING FUNCTIONS
WRITE A PL/SQL PROCEDURE FOR AN
12
APPLICATION USING PACKAGE
8.IMPLEMENT LOCKS FOR A PARTICULAR TABLE
--------------------------------- ROW-LEVEL LOCK --------------------------------------------------
USER-1: DBASE1
select * from stock
USER-2: SYSTEM
select * from dbase1.stock
update dbase1.stock set currstock=90 where itemcode=1003;
1 row(s) updated.
USER-1: DBASE1
update stock set currstock=100 where itemcode=1003
Pending….
When User-1 tries to update the same row updated by User-2 Row-Level Lock will Occur.
To release the lock User-2 has to Commit/Rollback the Transaction.
USER-2: SYSTEM
COMMIT;
USER-1: DBASE1
update stock set currstock=100 where itemcode=1003
1 row(s) updated
Now the Row-Level Lock has released and the statement processed
User: DBASE1 USER: SYSTEM
select * from stock select * from dbase1.stock;
--------------------------------- TABLE LEVEL LOCK -----------------------------------------------
---
EXCLUSIVE LOCK
USER: DBASE1
lock table stock in exclusive mode
Table(s) Locked
USER: SYSTEM
Select * from dbase1.stock;
Note: We can view contents of the table which is locked by a User.
Update dbase1.stock set currstock=40 where itemcode=1004
SQL Statement Running .....................................
Note: We cannot modify the table as it is locked in Exclusive Mode by another User
(Dbase1)
USER: DBASE1
COMMIT
Statement processed.
USER: SYSTEM
Update dbase1.stock set currstock=40 where itemcode=1004
1 row(s) updated
Note: Once Commit command executed, Exclusive Lock is released and Modification is
allowed by another User.
select * from dbase1.stock;
--------------------------------SHARE LOCK---------------------------------------------
USER: DBASE1
lock table stock in share mode
statement processed
USER: SYSTEM
lock table dbase1.stock in share mode
statement processed
Note: Both users can lock the table in Share Mode with Read Option allowed.
USER: DBASE1
update stock set currstock=150 where itemcode=1003
SQL Statement Running .....................................
Note: We can’t modify the Table as Shared Lock is applied by both the users.We have to
commit/Rollback the Lock applied in both the users for any Modifications to be done.
USER: DBASE1
COMMIT
SQL Statement Running .....................................
USER: SYSTEM
COMMIT
Statement processed.
USER: DBASE1
COMMIT
Statement processed.
Note: Now the Shared Lock has been released by both users
USER: DBASE1 USER: SYSTEM
select * from stock select * from dbase1.stock
Note: The data in the table of both users are same now after the Shared Lock Processing
