SEMESTER-V

RDBMS AND ORACLE – PRACTICAl

1. SQL:

(i) Creation of DataBase

(ii) Various forms of selectstatement

(iii) Multiple Queries (Subquery)

(iv) Updateoperations

(v) Alter the DataBase

(vi) Number function, data functions, Aggregatefunction

(vii) Joining theTable.

RDBMS
AND
ORACLE
SQL
 1. CREATION OF DATABASE
AIM:
To create the database for student with specific name and set the primary key.

Algorithm:
1. Start the Program.
2. Create the table with below field names.
(i) S_No, Primary key
(ii) Name
(iii) Mark
3. Insert into table student.
4. Select the Values for table and display the result.

CODING:

SQL> Create table student (sno number (5) primary key, name varchar (20), mark number (3));

Table created.
SQL>insert into student values (&sno,’&name,’&mark);
Enter value for sno:001
Enter value for name: Ragu
Enter value for mark:75
Old l: insert into student (&sno,’&name’, &mark)
New l: insert into student values (001,’Ragu’, 75)
1 rowcreated
SQL>insert into student values (&sno,’&name,’&mark);
Enter value for sno:002
Enter value for name: Pream
Enter value for mark:85
Old l: insert into student values (&sno,’&name’, &mark)
New l: insert into student values (002,’Pream’, 85)
1 row created
SQL> insert into student values (&sno,’&name,’&mark);
Enter values for sno: 003
Enter values for name: Prasana
Enter value for mark: 95
Old l: insert into student values (&sno,’&name’, &mark)
New l: insert into student values (3,’Prasana’, 95)
1 row created
 OUTPUT:

SQL> select * from student;

SNO NAME MARK
----------------------------

001 Ragu 75
002 Pream 85
003 Prasana 95

RESULT

Thus the above program has been executed successfull

2. VARIOUS FORMS OF SELECTSTATEMENT
Aim:

To create the table & select values using various forms of select Statement.
Algorithm:
1. Start the process.
2. Create the table with table names student and field names sno, name, department, mark.
3. Insert values into the table student using insert query.
4. Select all value from table student where is created.
5. Select all value from table student where department BBA (CA).
6. Select all value from table student where order by mark.
7. Stop the process.

CODING:
SQL>Create table student (sno number (5), name varchar (20), department varchar (5), mark
number (3));
Table created.
SQL>insert into student values (&sno,’&name,’&depatment’&mark);
Enter value for sno: 1
Enter value for name: Rahim
Enter value for department: bca
Enter value for mark: 78
Old l: insert into student (&sno,’&name’,’&department’, &mark)
New l: insert into student values (1,’Rahim’,’bca’78)
1 row created.
SQL> insert into student values (&sno,’&name,’&depatment’&mark);
Enter value for sno: 2
Enter value for name: Ajay
Enter value for department: bba
Enter value for mark: 67
Old l: insert into student values (&sno,’&name’,’&department’, &mark)
New l: insert into student values (2,’Ajay’,’bba’, 67)
1 row created.
SQL> insert into student values (&sno,’&name,’&depatment’&mark);
Enter values for sno: 3
Enter values for name: Kumar
Enter value for department: maths
 Enter value for mark: 56
Old l: insert into student values (&sno,’&name’,’&department’, &mark)
New l: insert into student values (3,’Kumar’, maths’, 56)
1 row created.

SQL> insert into student values (&sno,’&name,’&depatment’&mark);

Enter values for sno: 4
Enter values for name: Aruna
Enter value for department: bba
Enter value for mark: 45
Old l: insert into student values (&sno,’&name’,’&department’&mark)
New l: insert into student values (4,’aruna’, Tamil, 45)
1 row created.
OUTPUT:

SQL >select name, mark from student;

NAME MARK
---------------
Rahim 78
Ajay 67
Kumar 56
Aruna 45

SQL>select * from student where mark>50;

SNONAME DEPAR MARK
---------------------------
1 Rahim bca 78
2 Ajay bba 67
3 Kumar maths 56

SQL>select * from student where mark>50 and

mark<60; SNO NAME DEPAR MARK
---------------------------
3 KUMAR MATHS 56
 SQL>select * from student where mark>50 or department=’bca’;
SNO NAME DEPAR MARK
------------------------------
1 Rahim bca 78
2 Ajay bba 67
3 Kumar maths 56

SQL>select * from student where department in (‘bca, bba’);

SNO NAME DEPAR MARK
- - - - - - - - - - - - - - - - - - - - - - - - - --
1 Rahim bca 78
2 Ajay bba 67

SQL>select * from student where mark between 50 and 70;

SNO NAME DEPAR MARK
-----------------------------
2 Ajay bba 67
3 Kumar maths 56
SQL>select * from student order by mark;
SNO NAME DEPAR MARK
-------------------------------
4 Aruna Tamil 45
3 Kumar maths 56
2 Ajay bba 67
1 Rahim bca 78

RESULT:

Thus the above program has been executed successfully.

3. MULTIPLE QUERIES (SUBQUERIES)
Aim:
To create and insert value to the table and select values using queries.
Algorithm:
1. Select the process.
2. Create the table with table name student and field name sno name using create table query.
3. Insert values to the table student using create table query.
4. Create table name with table name, result & field names sno, name, mark using create query.
5. Insert value to the table result using insert query.
6. Stop the process.

CODING:
SQL>create table student (sno number (5), name varchar (10));
Table created.

SQL>insert into student values (&sno,’&name’);

Enter value for sno: 1
Enter value for name: Bala
Old 1: insert into student values (&sno,’&name’)
New 1: insert into student values (1,’Bala’)
1 row created.

SQL> insert into student values (&sno,’&name’);

Enter value for sno: 2
Enter value for name: Abilash
Old 1: insert into student values (&sno,’&name’)
New 1: insert into student values (2,’abilash’)
1 row created.

SQL> insert into student values (&sno,’&name’);

Enter value for sno: 3
Enter value for name: Syed
Old 1: insert into student values (&sno,’&name’)
New 1: insert into student values (3,’syed’)
1 row created.

SQL> insert into student values (&sno,’&name’);

Enter value for sno: 4
Enter value for name: Angel
Old 1: insert into student values (&sno,’&name’)
New 1: insert into student values (4,’angel’)
1 row created.

SQL> select *from student;

 SNO NAME

1 Bala
2 Abilash
3 Syed
4 Angel
SQL> create table result (sno number (5), mark varchar (3));

SQL> create table result (sno number (5), mark varchar (3));

OUTPUT:
Table created.

SQL> insert into result values (&sno, &mark);

Enter value for sno: 1
Enter value for mark: 99
Old 1: insert into result values (&sno,’&mark)
New 1: insert into result values (1, 99)
1 row created.

SQL> insert into result values (&sno, &mark);

Enter value for sno: 2
Enter value for mark: 98
Old 1: insert into result values (&sno,’&mark)
New 1: insert into result values (2, 98)
1 row created.

SQL> insert into result values (&sno, &mark);

Enter value for sno: 3
Enter value for mark: 97
Old 1: insert into result values (&sno,’&mark)
New 1: insert into result values (3, 97)
1 row created.

SQL> insert into result values (&sno, &mark);

Enter value for sno: 4
Enter value for mark: 96
Old 1: insert into result values (&sno,’&mark)
New 1: insert into result values (4, 96)
1 row created.

SQL> select *from result;

SNO MARK
1 99
2 98
3 97
4 96
 SQL>select name from student where sno in (select sno from result where mark>70);

NAME

Bala
Abilash
RESULT:

Thus the above program has been executed successfully.

1.UPDATEOPERATIONS
Aim:
To update existing values in the table using update query.
Algorithm:
1. Select the process.
2. Create the table with table name student and field name sno name using create table query.
3. Insert values to the table student using create table query.
4. Update value name where sno, using update query.
5. Select all the values from table student using select query.
6. Stop the process.

CODING:
SQL> create table student (sno number (5) Primary key, name varchar (20), mark number
(3));
Table created.

SQL>insert into student values (&sno,’&name’, &mark);

Enter value for sno:001
Enter value for name: Prasana
Enter value for mark:78
Old 1: insert into student values (&sno,’&name’, &mark)
new1: insert into student values (001,’prasana’, 78)
1 row created.

SQL> insert into student values (&sno,’&name’, &mark);

Enter value for sno: 002
Enter value for name: Suriya
Enter value for mask: 89
Old 1: insert into student values (&sno,’&name’, &mark)
New 1: insert into student values (002,’suriya’,89)
1 row created.
SQL> insert into student values (&sno,’&name’, &mark);
Enter value for sno:003
Enter value for name: Sriram
Enter value for mark:56
Old 1: insert into student values (&sno,’&name’, &mark)
New 1: insert into student values (003,’sriram’,56)
 1 row created.

OUTPUT:

SQL> select * from student;

SNO NAME MARK
--------------------------------------------------------
001 Prasana 78
002 Suriya 89
003 Sriram 56

SQL>update student set name=’Murugan’where sno=3;

1 row updated.

SQL>select * from student;

SNO NAME MARK
----------------------------------------------------------
001 Prasana 78
002 Suriya 89
003 Murugan 56
RESULT:

Thus the above program has been executed successfully.

1.ALTER THEDATABASE
Aim:

To alter existing values in the table using modify and names alter the query.

Algorithm:
1. Select the process.
2. Create a student the process table with field link Reg no, Name, mark1.
3. Insert with value into student table with field link Reg no, Name, mark1, total, average.
4. Alter the table student by adding the new field grade.
5. Using update operation insert the values for the field.
6. Stop the process.

CODING:

SQL>create table student (sno number (5) primary key, name varchar (20), mark number
(3));
Table created.

SQL>alter table student add department varchar(10);

Table altered.
 OUTPUT:
SQL> desc student;

NAME Null? Type

----------------------------------------------------------------------------
SNO NOT NULL NUMBER (5)
NAME VARCHAR2 (10)
MARK NUMBER (3)
DEPARTMENT VARCHAR2 (10)

SQL>alter table student modify department varchar (20);

Table altered.

SQL>desc student;

Name Null? Type

-----------------------------------------------------------------------------
SNO NOT NULL NUMBER (5)
NAME VARCHAR2 (20)
MARK NUMBER (3)
DEPARTMENT VARCHAR2 (20)

SQL>alter table student rename to stud;

Table altered.

SQL> desc stud;

Name Null? Type

-----------------------------------------------------------------------------
SNO NOT NULL NUMBER (5)
NAME VARCHAR2 (20)
MARK NUMBER (3)
DEPARTMENT VARCHAR2 (20)
 6.1 AGGREGATE FUNCTIONS
Aim:

To create number function data function aggregate function queries.

Algorithm:

1. Select the process.

2. To create table with table name result from aggregate function with field name sno and marks.

3. Using aggregate function link min, mak, mark, sum, avg, and count using select queries.

4. Using data function link and month between day last day using select from dual queries.

5. Using number function link and mod power round and sort , sin, cos, tan using select from dual query.

6. Stop the process.

CODING:

SQL>create table result (sno number (5), mark varchar (3));

Table created.
SQL>insert into result values (&sno, &mark);
Enter value for sno: 01
Enter value for mark: 89
Old 1: insert into result values (&sno, &mark)
New 1: insert into result values (01, 89)
1 row created.
SQL>insert into result values (&sno, &mark);
Enter value for sno: 02
Enter value for mark: 78
Old 1: insert into result values (&sno, &mark)
New 1: insert into result values (02, 78)
1 row created.
SQL>insert into result values (&sno, &mark);
Enter value for sno: 03
Enter value for mark: 67
Old 1: insert into result values (&sno, &mark)
New 1: insert into result values (03, 67)
1 row created.
SQL>insert into result values (&sno, &mark);
Enter value for sno: 04
Enter value for mark: 56
Old 1: insert into values (&sno, &mark)
New 1: insert into result values (04, 56)
1 row created.

SQL>select*from result;

OUTPUT:

SNO MARK
--------------------------

01 89
02 78
03 67
04 56

SQL> select avg (mark) from result;

AVG (MARK)
72.5

SQL>select count (*) from result;

COUNT (*)
4

SQL>select sum (mark) from result;

SUM (MARK)
290

SQL>select max (mark) from result;

MAX
89

SQL>select min (mark) from result;

 MIN
56

RESULT:
Thus the above program has been executed successfully.

6.2. DATEFUNCTION

SQL> select add_months (’17-jun-85’, 2) form dual;

ADD_MONTH
------------------
17-AUG-85
SQL> select months_between (’17-jun-85’,’17-Jun-86’) from dual;
MONTIIS_BETWEEN(’17-JUN-85’,’17-JUN-86’)
---------------------------------------------------------------
-12
SQL> select next_day (’28-Agu-13’,’monday’) from dual’
NEXT_DAY (
-------------------
02-SEP-13
SQL>select last_day (’01-Aug—13’) from dual;
LAST_DAY (
----------------
31-AUG-13

SQL>select sys date from dual;

SYSDATE
--------------
29-AUG-13
SQL> select systimestamp from dual;
SYSTIMESTAMP
-----------------------------------------------------------------
- 29-AUG-13 06.59.22.690000 AM +05:30
SQL>select to_char (sysdate,’day’) from dual;
TO_CHAR(SYSDATE,’DAY’)
---------------------------------------
8 Tuesday

SQL> select to_char (sysdate,’day, date, month, yyyy’) from dual;

TO_CHAR (SYSDATE,’DAY, DATE, MONTH, YYYY’)
---------------------------------------------------------------------------
Tuesday, 8th October, 2019

SQL>select sysdate+2 form dual;

SYSDATE+2
-----------------
10-OCT-19

SQL> select to_char (to_date (’08-October-2019’),’day’) from dual;

TO_CHART
_____
Tuesday
6.3. NUMBERFUNCTION

SQL> select abs (-5) from dual;

ABS (-5)
--------------
5
SQL> select ceil (3.45) from dual;
CEIL (3.45)
---------------
4
SQL> select floor (7.55) from dual;
FLOOR (7.55)
------------------
7
SQL> select mod (28, 3) from dual;
MOD (28, 3)
---------------
1
SQL> select power (2, 2) from dual;
POWER (2, 2)
-----------------
4
SQL> select round (4) from dual;
ROUND (4)
-----------------
4
SQL> select sqrt (49) from dual;
SQRT(49)
----------------
7
SQL> select sin (10) from dual;
SIN (10)
--------------
-.54402111

SQL> select cos (2) from dual;

COS (2)
---------------
-.41614684

SQL> select tan (2) from dual;

TAN (2)
---------------
-2.1850399
7. JOINTING THET
AIM:
To create the tables program different join operations on table.
Algorithm:

1. Select the process.

2.Create the table department with below field name.

(i) Dept_ID

(ii) Dept_NAME

3. Create the table employee with below find between field names.

(i) Employee_ID

(ii) Employee _NAME

4. Insert the value into table department and employee.

5. Display the result from the table employee and department using the joins link Left Join, Right Join &
Full Join.

6. Stop the process.

CODING
SQL> create table student (sno number (3), name varchar (10), department varchar (10));
Table Created.
SQL> insert into student values (&sno, ‘&name’, ‘&department’);
Enter value for sno: 1
Enter value for name: Prakash
Enter value for department: bca
Old 1: insert into student values (&sno, ‘&name’, ‘&department’)
New 1: insert into student values (1, ‘Prakash’, ‘bca’)
1 row created.
SQL> insert into student values (&sno, ‘&name’, ‘&department’);
Enter value for sno: 2
Enter value for name: Belin
Enter value for department: bba
Old 1: insert into student values (&sno, ‘&name’, ‘&department’)
New 1: insert into student values (2, ‘Belin’, ‘bba’)
1 row created.
SQL> insert into student values (&sno, ‘&name’, ‘&department’);
Enter value for sno: 3
Enter value for name: Ravi
Enter valuve for department: maths
Old 1: insert into student values (&sno, ‘&name’,‘&department’)
New 1: insert into student values (3, ‘ravi’, ‘maths’)
1 row created.
SQL> select * from student;
SNO NAME DEPARTMENT
……………………………………………….
1 Prakash bca
2 Belin bba
3 Ravi maths
TABLE-2
SQL> create table fees (bill_id number (5), sno number (5), particular varchar (20));
Table Created.
SQL> insert into fees values (&bills_id, &sno, ‘&particular’);
Enter value for bill_id: 1
Enter value for sno: 2
Enter value for particular: semester
Old 1: insert into fees values (&bill_id, &sno,‘&particular’)
New 1: insert into fees values (1, 2, ‘semester’)
1 row created.
SQL> insert into fees values (&bills_id, &sno, ‘&particular’);
Enter value for bill_id: 2
Enter value for sno: 5
Enter value for particular: record
Old 1: insert into fees values (&bill_id, &sno, ‘&particular’)
New 1: insert into fees values (2, 5, ‘record’)
1 row created.
SQL> insert into fees values (&bills_id, &sno, ‘&particular’);
Enter value for bill_id: 3
Enter value for sno: 10
Enter value for particular: function
Old 1: insert into fees values (&bill_id, &sno, ‘&particular’)
New 1: insert into fees values (3, 10, ‘function’)
1 row created.
OUTPUT:

SQL> Select * from fees;

BILL_ID SNO PARTICULAR
------------------------------
1 2 semester
2 5 record
3 10 function
SQL>select fees.bill_id, student.name from fees inner join student on
fees.sno=student.sno; BILL_ID NAME
………………………
1 Prakash

SQL>select student.name, fees.bill_id from student left join fees on

student.sno=fees.sno; NAME BILL_ID
………………………
….. Prakash 1
Bel
in
Ra
vi

SQL> select student.name, fees.bill_id from student right join fees on

student.sno=fees.sno; NAME BILL_ID
………………………
….. Prakash 1
2
3

SQL> select student.name, fees.bill_idfrom student full outer join fees

on student.sno=fees.sno;
NAME BILL_ID
………………………
….. Prakash 1
Belin 2
Ravi 3
RESULT:
Thus the above program has been executed successfully.