Scribd
Upload
53 views20 pages

RDBMS Practical Notes for B.Sc CS

Here are some query commands demonstrated: 1. Basic SELECT: SQL> select * from student1; 2. SELECT with WHERE: SQL> select * from student1 where class='IIcs'; 3. ORDER BY: SQL> select * from student1 order by rno; 4. DISTINCT: SQL> select distinct class from student1; 5. BETWEEN: SQL> select * from student1 where rno between 1 and 2; 6. LIKE: SQL> select * from student1 where sname like 'r%'; 7. IN: SQL> select * from student1 where rno in (1,2);

Uploaded by

chocopraveen04
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
53 views20 pages

RDBMS Practical Notes for B.Sc CS

Here are some query commands demonstrated: 1. Basic SELECT: SQL> select * from student1; 2. SELECT with WHERE: SQL> select * from student1 where class='IIcs'; 3. ORDER BY: SQL> select * from student1 order by rno; 4. DISTINCT: SQL> select distinct class from student1; 5. BETWEEN: SQL> select * from student1 where rno between 1 and 2; 6. LIKE: SQL> select * from student1 where sname like 'r%'; 7. IN: SQL> select * from student1 where rno in (1,2);

Uploaded by

chocopraveen04
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SRI VIDYA MANDIR COLLEGE OF

ARTS & SCIENCE


(Co-Educational Institution – Affiliated to Periyar University)

SALEM-636010

DEPARTMENT OF COMPUTER SCIENCE

[Link]- COMPUTER SCIENCE

Relational Database Management System


2022
SRI VIDYA MANDIR COLLEGE OF
ARTS & SCIENCE
(Co-Educational Institution – Affiliated to Periyar
University)SALEM-636010

Certify that this is the Bonafide record of Practical Work done by

………………………………………………………………………….…………………………..

With University Register Number …………………………………………………….. of

[Link]( C.S) duringthe year-2022.

[Link] [Link],
Practical In-charges Head of the Department

Submitted for Practical:

Relational Database Management System


Examination held on : Paper Code:

Signature of the Examiners

Station : SALEM 1.

Date : 2.
LAB EXERCISE

1
Data Definition of Base Tables
1

5
2 DDL with Primary Key Constraints

3 9
DDL with constraints and verification by insert commands

4 Perform various data manipulation commands 12

5 Demonstrate the query commands 16

Write a pl/sql code block that will accept an account number from
6 the user and debit an amount of rs.2000 from the account if the 23

account has a minimum balance of the 500 after the amount is


debited.

7 Write a pl/sql code block to calculate the area of the circle for a 27

value of radius varying from 3 to 7

8 Write a pl/sql block of code for reversing a number(example:1234 30

as 4321)

Create a transparent audit system for a table client master


9 31
(client_no,name,adress,bal_due)

1
[Link]: 1 USE DDL OF BASE TABLE

PL/SQL Code:

[Link]
SQL> create table student(rno varchar2(20),sname varchar2(20),class varchar2(20)
address varchar2(20));
SQL> desc student;

Name Null? Type


----------------------------------------- -------- -----------------
RNO VARCHAR2(20)
SNAME VARCHAR2(20)
CLASS VARCHAR2(20)
ADDRESS VARCHAR2(20)

2. ALTER
SQL> ALTER TABLE student ADD phno number(10);
Table altered.
SQL> desc student;

Name Null? Type


----------------------------------------- -------- ----------------------------
RNO VARCHAR2(20)
SNAME VARCHAR2(20)
CLASS VARCHAR2(20)
ADDRESS VARCHAR2(20)
PHNO NUMBER(10)
SQL> insert into student values('01','raju','IICS','salem',6369690449);
1 row created.

2
SQL> select * from student;
RNO SNAME CLASS ADDRESS PHNO
------------------------------------------------------------------------------------------
01 raju IICS salem 6369690449

3. TRUNCATE
SQL> TRUNCATE TABLE student;
Table truncated.
SQL> select * from student;
no rows selected

[Link]
SQL> DROP TABLE student;
Table dropped.
SQL> desc student;

ERROR:
ORA-04043: object student does not exist

3
[Link]: 2.
DDL WITH PRIMARY KEY CONSTRAINTS.

PL/SQL Code:

SQL> create table student(rno int not null,sname varchar2(20),class varchar2(20),address


varchar2(20),primary key(rno));
Table created.
SQL> desc student;

Name Null? Type


----------------------------------------- -------- ----------------------------
RNO NOT NULL NUMBER(38)
SNAME VARCHAR2(20)
CLASS VARCHAR2(20)
ADDRESS VARCHAR2(20)
SQL> insert into student values(001,'raju','IIcs');
1 row created.
SQL> insert into student values(002,'ram','IIcs');
1 row created.
SQL> insert into student values(003,'ravi','IIcs');
1 row created.
SQL> select * from student
RNO SNAME SCLASS
-------------------- -------------------- --------------------
1 raju IIcs
2 ram IIcs
3 ravi IIcs

4
SQL> create table student(rno int not null,sname varchar2(20),class varchar2(20),address
varchar2(20),primary key(rno),CONSTRAINT regno PRIMARY KEY(class));
*
ERROR at line 1:
ORA-02260: table can have only one primary key

5
[Link]: 3.
USE DDL WITH CONSTRAINTS AND VERIFICATION BY INSERT
COMMAND

PL/SQL Code:

SQL> create table Persons ( ID int NOT NULL,LastName varchar(20) NOT


NULL,FirstName varchar(20),Age int,CHECK (Age>=18));
Table created.
SQL> insert into persons values(1,'g','ravi',23);
1 row created.
SQL> insert into persons values(1,'g','ram',22);
1 row created.
SQL> insert into persons values(2,'k','ram',22);
1 row created.
SQL> insert into persons values(3,'c','thiru',16);
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.SYS_C004041) violated
SQL> select * from persons;
ID LASTNAME FIRSTNAME AGE
-------------------------------------------------------------------------------- ---------------------------
1 g ravi 23
2 k ram 22

6
Ex. No: 4.
DATA MANIPULATION OF BASE TABLES AND VIEWS.

PL/SQL Code:

SQL> create table student1(rno int not null,sname varchar2(20),class varchar2(20),address


varchar2(20));
Table created.
[Link]

SQL> insert into student1 values(1,'ram','IIcs','salem');


1 row created.
SQL> insert into student1 values(2,'ravi','IIcs','omalur');
1 row created.
SQL> insert into student1 values(3,'raj','IIcs','erode');
1 row created.
SQL> select * from student1;
RNO SNAME CLASS ADDRESS
---------- -------------------- -------------------- ---------------------------------------
1 ram IIcs salem
2 ravi IIcs omalur
3 raj IIcs erode

[Link]

SQL> update student1 SET sname='sathish' where rno=1;


1 row updated.
SQL> select * from student1;
RNO SNAME CLASS ADDRESS
---------- -------------------- -------------------- ------------------------
7
1 sathish IIcs salem
2 ravi IIcs omalur
3 raj IIcs erode

[Link]

SQL> delete from student1 WHERE sname='sathish';


1 row deleted.
SQL> select * from student1;
RNO SNAME CLASS ADDRESS
---------- -------------------- -------------------- --------------------
2 ravi IIcs omalur
3 raj IIcs erode

8
Ex. No: 5.
DEMONSTRATE THE QUERY COMMANDS.

PL/SQL Code:

SQL> create table student1(rno int not null,sname varchar2(20),class varchar2(20),address


varchar2(20));
Table created.
SQL> insert into student1 values(1,'ram','IIcs','salem');
1 row created.
SQL> insert into student1 values(2,'ravi','IIcs','omlur');
1 row created.
SQL> select * from student1;
RNO SNAME CLASS ADDRESS
---------- -------------------- -------------------- --------------------
1 ram IIcs salem
2 ravi IIcs omalur
[Link]

SQL> SELECT COUNT(address)FROM student1;


COUNT(ADDRESS)
--------------
2
[Link]

SQL> SELECT address AS 'location' FROM student1;


RNO SNAME CLASS LOCATION
---------- -------------------- -------------------- --------------------
1 ram IIcs salem
2 ravi IIcs omalur
9
[Link]

SQL> SELECT * FROM student1 WHERE rno = 2 AND ADDRESS = 'omalur';


RNO SNAME CLASS ADDRESS
---------- -------------------- -------------------- --------------------
2 ravi IIcs omalur

3. INNERJOIN

SQL> CREATE TABLE Persons1(ID number(20),LastName varchar2(20),FirstName


varchar2(20),address varchar2(20));
Table created.
SQL> insert into persons1 values(1,'g','ajith','salem');
1 row created
SQL> insert into persons1 values(2,'k','ajay','attur');
1 row created.
SQL> select * from persons1;

ID LASTNAME FIRSTNAME ADDRESS


---------- -------------------- -------------------- ----------------------------------
1 g ajith salem
2 k ajay attur

SQL> select * from student1;

RNO SNAME CLASS ADDRESS


---------- -------------------- -------------------- ------------------------------
2 ravi IIcs omlur
3 raj IIcs erode

10
SQL> SELECT ID, LASTNAME, FIRSTNAME FROM persons1 INNER JOIN
student1 ON [Link] = [Link];

ID LASTNAME FIRSTNAME
---------- -------------------- --------------------
2 k ajay
[Link]

SQL> INSERT INTO student1 values(1,'sathish','IIcs','attur');


1 row created.
SQL> INSERT INTO student1 values(4,'prabhu','IIBCA','namakkal');
1 row created.
SQL> INSERT INTO student1 values(5,'tamil','IIBCA','rasipuram');
1 row created.
SQL> INSERT INTO student1 values(6,'krishna','IBCA','mettur');
1 row created.
SQL> select * from student1;

RNO SNAME CLASS ADDRESS


---------- -------------------- -------------------- --------------------
1 sathish IIcs attur
2 ravi IIcs omlur
3 raj IIcs erode
4 prabhu IIBCA namakkal
5 tamil IIBCA rasipuram
6 krishna IBCA mettur

SQL> SELECT * FROM student1 WHERE rno BETWEEN 1 AND 4;

` RNO SNAME CLASS ADDRESS


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

11
1 sathish IIcs attur
2 ravi IIcs omlur
3 raj IIcs erode
4 prabhu IIBCA namakkal

12
[Link]: 6.
Write a PL/SQL code block that will accept an account number from the user and
debit an amount of Rs. 2000 from the account if the account has a minimum balance of
500after the amount is debited. The Process is to find on the Accounts table.

PL/SQL Code:

(i) Create tables according to the following definition.

SQL> create table Accounts3(Account_id varchar(5), Name varchar(20), Bal number(7,2));


Table created.

ACCOU NAME BAL


----- --------- ----------- ----------
AC001 Anuj 5000
AC002 Robert 10000
AC003 Mita 5000
AC004 Sunita 15000
AC005 Melba 10000

SQL> set server output on;


SQL> start d:\[Link];
Enter value for acct_no: 'AC003'
old 7: acct_no:=&acct_no;
new 7: acct_no:='AC003';
PL/SQL procedure successfully completed.

ACCOU NAME BAL


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

13
AC001 Anuj 5000
AC002 Robert 10000
AC003 Mita 3000
AC004 Sunita 15000
AC005 Melba 10000

14
[Link]: 7.
Write a PL/SQL code block to calculate the area of the circle for a value of radius
varying from 3 to 7. Store the radius and the corresponding values of calculated area in a
table Areas. Areas – radius, area.

PL/SQL Code:

(i) Create tables according to the following definition.

SQL> CREATE TABLE areas (Radius number (5), area number (14,2));
Table created.
SQL> SET SERVEROUTPUT ON;
SQL> start d:\[Link];

RADIUS AREA
---------- ----------
3 28.26
4 50.24
5 78.5
6 113.04
7 153.86

PL/SQL procedure successfully completed.

15
[Link]: 8.
WRITE A PL/SQL BLOCK OF CODE FOR REVERSING ANUMBER.
(EXAMPLE: 1234 AS 4321).
PL/SQL Code:
SQL> set serveroutput on;
SQL> start d:\[Link];
The Given Number is102345
The Inverted Number is543201
PL/SQL procedure successfully completed.

16
[Link]: 9.
Create a transparent audit system for a table Client_master (client_no, name,
address, Bal_due). The system must keep track of the records that are being deleted or
updated. The functionality being when a record is deleted or modified the original record
details and the date of operation are stored in the audit client (client_no, name,
bal_due,operation,user id, opdate) table, then the delete or update is allowed to go
through.

PL/SQL Code:

(i) Create tables according to the following definition.


SQL> desc auditclient;
Name Null? Type
----------------------------------------- -------- ----------------------------
CLIENT_NO VARCHAR2 (6)
NAME VARCHAR2 (20)
BAL_DUE NUMBER (10, 2)
OPERATION VARCHAR2 (8)
USERID VARCHAR2 (20)
ODATE DATE
SQL> desc client_master;
Name Null? Type
----------------------------------------- -------- ----------------------------
CLIENT_NO VARCHAR2 (6)
NAME VARCHAR2 (20)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (30)
STATE VARCHAR2 (15)
PINCODE NUMBER (6)
BAL_DUE NUMBER (10,

17
CLIENT NAME ADDRESS CITY STATE PINCODE BAL_DUE
------------------------------ --------------- ---------- ----------------------------------------------
1 rahul salem salem tamilnadu 645221 1000

2 ravi salem salem tamilnadu 434565 4000

3 siva attur Salem tamilnadu 234567 2000

SQL> update client_master set bal_due=3000 where client_no=1;


1 row updated.

CLIENT NAME DUE OPERATIO USERID ODATE


------ -------------------- ---------- -------- -------------------- ---------
1 rahul 2000 update SYSTEM 02-DEC-18
1 rahul 3000 delete SYSTEM 02-DEC-18
2 ravi 4000 delete SYSTEM 02-DEC-18

18

You might also like