DBMS Lab Programs

1)    Draw ER diagrams for train services in a railway station?

 

2)    Draw ER diagram for Hospital Administration (Hospital Management System)

 

3)    Create table for student that consist of student htno, student name, father name, dob, phone no, street and answer the following queries.

a)      Add new field city.

b)      Modify the width of field student name.

c)      Insert the records into the table.

d)     Retrieve all the records from the student table.

e)      Retrieve fields htno, sname, phno, from table.

f)       Retrieve all student information who live in city kurnool.

g)      Modify the phone number of particular student.

h)      Modify the city of any student.

i)        Retrieve all the records in descending order.

j)        Delete all the student information who lives in city hyderabad.

Creating table for student:

SQL> create table student (HTNO NUMBER primary key, sname varchar2(20),fname varchar2(20),dob date, phone number(10),street varchar2(10));

Table created.

a)      Adding new field city:

SQL>alter table student add(city varchar2(9));

Table altered

b)     Modify the width of field student name:

SQL>alter table student modify(sname varchar2(25));

Table altered

c)      Insert the records into the table:

SQL>insert into student values(&htno,’&sname’,’&fname’,’&dob’,&phone,’&street’,’&city’);

Enter value for htno: 1125

Enter value for sname: rajesh kumar

Enter value for fname: kiran rao

Enter value for dob: 10-jun-1995

Enter value for phone: 9595456456

Enter value for street: ramnag

Enter value for city: kurnool

old   1: insert into student values(&htno,'&sname','&fname','&dob',&phone,'&street','&city')

new   1: insert into student values(1125,'rajesh kumar','kiran rao','10-jun-1995',9595456456, 'ramnag','kurnool')

1 row created.

Do  above  step  repeatedly  for  5  students.

d)     Retrieve all the records from the student table.

SQL> select   *   from   tudent;

      HTNO     SNAME                       FNAME                DOB               PHONE            STREET           CITY

      1125      rajesh kumar              kiran rao            10-JUN-95     9595456456   ramnag          kurnool

      1126      kalyan                         rakesh                 15-JUL-96     8855454546   rajnag      Hyderabad

e)      Retrieve fields htno, sname, phno, from table.

SQL> select   htno,sname,phone from student;

      HTNO        SNAME                          PHONE

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

      1125         rajesh kumar              9595456456

      1126         kalyan                          8855454546

f)    Retrieve all student information who live in city kurnool.

 SQL> select * from student where city=’kurnool’;

You can write your output from observation.

g)      Modify the phone number of particular student.

SQL>update student3 set phone=4564564564  where htno=1125;

You can write your output from observation.

h)     Modify the city of any student.

SQL>update student3 set  city=Kurnool  where  htno=1126;

You can write your output from observation.

i)        Retrieve all the records in descending order.

SQL> select * from student order by sname desc;

You can write your output from observation.

       SNO SNAME

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

         3 suresh

         2 ravi

SQL> select * from student order by sname asc;

You can write your output from observation.

       SNO SNAME

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

         2 ravi

         3 suresh

j)       Delete all the student information who lives in city hyderabad.

SQL>delete  from  student3  where city = ’hyderabad’;

You can write your output from observation.

4)    create  table for employee which consist of   ENO, ENAME, BASIC, DA, HRA, GROSS?

And answer the following queries:

a)      Insert  the information for  ENO, ENAME, BASIC salary.

b)     Calculate DA which is 5% of basic salary.

c)      Calculate  HRA  which is 11% of  basic salary.

d)     Calculate gross salary  gs = DA + HRA + BASIC.

e)      Display employee table.

f)       Display the employee names whose salary is greater than 10,000.

g)      Delete the employee information for the given eno.

Creating table:

SQL> create   table  employee(eno  number(10)  primary key, ename  varchar2(30),  basic  number(10), da  number(10), hra  number(10),  gs  number(10));

 Output:

Table Created.

a) Insert  the information for  ENO, ENAME, BASIC salary.

SQL> insert   into   employee(eno,ename,basic) values(&eno,'&ename',&basic);

Enter value for eno: 1101

Enter value for ename: ravi

Enter value for basic: 5000

old   1: insert into employee(eno,ename,basic) values(&eno,'&ename',&basic)

new   1: insert into employee(eno,ename,basic) values(1011,'kiran',5000)

1 row created.

Like this enter 5 employees details (from your Lab observation)

SQL> select  *  from  employee;

       ENO  ENAME                           BASIC         DA        HRA         GS

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

      1101    ravi                                    5000

      1102    rajesh                                8000

      1103    pavan kumar                   11000

b) Calculate DA which is 5% of basic salary:

   SQL>update  employee   set   da = basic * 5/100;

            3   rows   updated.

c)     Calculate  HRA  which is 11% of  basic salary.

    SQL>update  employee   set   hra = basic * 11/100;

 3   rows   updated.

d)     Calculate gross salary  gs = DA + HRA + BASIC.

     SQL>update  employee   set   gs = DA + HRA + BASIC;

               3   rows   updated.

e) Display employee table.

SQL> select  *  from  employee;

       ENO    ENAME                        BASIC         DA        HRA         GS

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

      1101     ravi                                 5000           250        550          5800

      1102     rajesh                              8000           400        880          9280

      1103     pavan kumar                  11000          550       1210        12760

f) Display the employee names whose salary is greater than 10,000:

SQL>select   ename   from  employee   where   gs>10000;

ENAME

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

pavan kumar

5)    Create table for account details of customers in different banks and create a view for customers acno, balance, branchname where bname = HSBC bank?

(Note: You can write your outputs from your observation)

Creating   account   table:

SQL>create table   account(acno   number, balance  number,  bname  varchar2(20), primary  key(acno),  check(balance >=1000));

Insert  records  into  account  table:

SQL>insert   into  account  values(&acno, &bal, ‘&bname’);

Display table:

SQL>select   *  from  account;

Creating  view  for  HSBC  bank:

SQL> create  view  hsbc_accounts(h_acno, h_bal,h_bname) As (select acno, bal, bname   from   account   where   bname = ‘HSBC’);

Retrieving   all  information from view:

SQL>select   *   from   hsbc_accoutns;

Retriew all information  where  balance is  greater than  40000:

SQL>select   *   from   hsbc_accoutns  where  h_bal>40000;

Retriew all information  where  balance between  30000 and 50000:

SQL>select   *   from   hsbc_accoutns  where  h_bal  between  30000 and 50000 ;

6)    Write a view to extract details from two or more tables?

Ans: First of all we need to create two table (or if tables are already existed, then we can apply view on them).

Creating two tables: 1) Dept  table   2) Emp table.

SQL> Create table dept(deptno number(3),dname varchar2(15),location varchar2(15), constraint pk_dept primary key(deptno));

Table created.

SQL> desc dept;

 Name                      Null?                              Type

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

 DEPTNO               NOT NULL        NUMBER(3)

 DNAME                                           VARCHAR2(15)

 LOCATION                                     VARCHAR2(15)

SQL> Create table emp(empno number(4),ename varchar2(25),job varchar2(15), hiredate date,sal number(6,2),deptno number(3), constraint pk_emp primary key (empno), constraint fk_deptno foreign key(deptno) references dept(deptno));

Table created.

SQL> desc emp;

 Name                             Null?                          Type

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

 EMPNO               NOT NULL             NUMBER(4)

 ENAME                                               VARCHAR2(25)

 JOB                                                      VARCHAR2(15)

 HIREDATE                                         DATE

 SAL                                                     NUMBER(6,2)

 DEPTNO                                             NUMBER(3)

Insert values into DEPT table:

SQL> insert   into   dept  VALUES (100,'ACCOUNTS','KURNOOL');

1 row created.

SQL> insert into dept VALUES (110,'RESEARCH','BANGALORE');

1 row created.

SQL> insert into dept VALUES (120,'SALES','HYDERABAD');

1 row created.

SQL> insert into dept VALUES (130,'OPERATIONS','KADAPA');

1 row created.

Insert values into EMP table:

SQL> INSERT   INTO   EMP   VALUES(7801,'RAVI','MANAGER','01-JAN-2016',5000,100);

1 row created.

SQL> INSERT INTO EMP VALUES(7802,'KIRAN','MANAGER','01-JAN-2017',6000,110);

1 row created.

SQL> INSERT INTO EMP VALUES(7803,'RAJ KUMAR','MANAGER','01-FEB-2016',8000,120);

1 row created.

Like this enter multiple employees details (see your Lab observation)

SQL> SELECT   *   FROM   DEPT;

    DEPTNO      DNAME           LOCATION

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

       100        ACCOUNTS           KURNOOL

       110        RESEARCH           BANGALORE

       120        SALES                    HYDERABAD

       130        OPERATIONS       KADAPA

SQL> SELECT  *  FROM   EMP;

     EMPNO     ENAME                     JOB               HIREDATE         SAL     DEPTNO

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

      7839         RAVI                      MANAGER         01-JAN-16        5000        100

      7849         KIRAN                   MANAGER         01-JAN-17        6000        110

      7566         RAJ KUMAR         MANAGER         01-FEB-16        8000        120

      7588         JOHN                      MANAGER         01-FEB-16        8000        130

      7901         RAJESH                 ANALYST           01-MAR-16      2000        100

      7902         KRISHNA              ANALYST           01-MAR-17      2100        100

      7903        RAMA RAO           CLERK                01-MAR-16       2100        110

      7904        KALYAN RAO      CLERK                 01-MAR-16       2100        120

      7905        UDAY                     SALES MAN       01-MAR-16       2100        120

      7906        BHASKER             SALES MAN        01-MAR-17       2100        120

      7907       SOMESH                 CLERK                01-MAR-16       2100        130

      7908       TEJA                        CLERK                01-MAR-16       2300        130

12  rows  selected.

Creating VIEW on two tables (EMP, DEPT)

SQL> CREATE     VIEW    EMP_DEP      AS     SELECT   A.ENAME,   B.DNAME,  A.JOB,  A.SAL,   A.EMPNO,   A.HIREDATE,      B.LOCATION      FROM     EMP   A, DEPT  B   WHERE A.DEPTNO = B.DEPTNO      ORDER     BY     A.ENAME;

View created.

SQL> SELECT   *   FROM EMP_DEP;

Write the output from your lab observation.

PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is oracle corporation's procedural extension language for sql and the oracle relational database.

PL/SQL is one of three key programming languages embedded in the oracle database, along with sql

It is one of the important tool

it is programming language of sql which contains both the power of procedure oriented approach and  database

PL/SQL supports variables, conditions, loops and exceptions.

variable is a identifier which is used to store some value.

pl/sql program units (essentially code containers) can be compiled into the oracle database. Once the program units have been stored into the database, they become available for execution at a later time

some of the main features of PL/SQL is

1.      speed

2.      performance

3.      security

4.      existence

5.      administration

pl/sql block structure:

declare

--------- declaration block

begin

--------- statement block

exception

---------exception block

end

datatype:

it represents the type of data is to store in a variable

1.      number

2.      char

3.      varchar

4.      long

5.      date

6.      lob(large object binary):stores text files

7.      clob(character large object binary): usually have very high size limits, on the order of 2gb or more

8.      blob(binary large object): is a collection of binary data stored as a single entity in a database management system. blobs are typically images,audio or other multimedia objects.

Operators:

Assignment :    ‘:=’

Comparison:     ‘=’

dbms_output.put_line ( ):  it is a function used to print some message or the value of the variable on the monitor

| |      used to concatenate the message and the value of the variable

Set serveroutput on:   It is a statement used to on/off the server output to display the message

if you take ‘off’ then the program will be compiled but no output is displayed

ed filename is used to type the program in notepad

@filename  will execute the program.

1.     write a PL/SQL program to print the message.

SQL>set serveroutput on;

SQL>Begin

dbms_output.put_line('programming in PL/SQL');

end;

/

Output:

Programming in PL/SQL

PL/SQL procedure sucessfully completed

2.     Write a PL/SQL program for the addition of two numbers?

SQL>set serveroutput on;

SQL> Declare

               a  number;

               b  number;

               c  number;

           begin

               a:=20;

               b:=30;

               c:=a+b;

           dbms_output.put_line('sum of values c = '||c);

           end;

           /

Output:

sum of value c =  50

PL/SQL procedure sucessfully completed

3.     Write a PL/SQL program to find greatest of 3 numbers?

SQL>set serveroutput on;

SQL> Declare

                   a  number:=&a;

                   b  number:=&b;

                   c  number:=&c;

           Begin

                   if(a>b and a>c) then

                          dbms_output.put_line ('a is greater');

                  else

                          if(b>a and b>c) then

                                  dbms_output.put_line ('b is greater');

                          else

                                 dbms_output.put_line ('c is greater');

                          end  if;

                 end if;

           End;

/

Output:

Enter value for a: 5

old   2: a number:=&a;

new   2: a number:=5;

Enter value for b: 10

old   3: b number:=&b;

new   3: b number:=10;

Enter value for c: 4

old   4: c number:=&c;

new   4: c number:=4;

b is greater

PL/SQL procedure successfully completed.

4)    Write a PL/SQL to print the 10 numbers using if then exit?

                       sql>set serveroutput on;

                       sql> Declare

                                   i number;

                              Begin

                                   i:=1;

                                loop

                                   if(i>=11) then

                                   exit;

                                   end   if;

                                   dbms_output.put_line(i);

                                   i:=i+1;

                               end   loop;

                                   end;

                                   /

         Output:

1

2

3

4

5

6

7

8

9

10

                         PL/SQL procedure sucessfully completed

5)    Write a PL/SQL program to find factorial of given number using PROCEDURE?

SQL> CREATE    OR    REPLACE    PROCEDURE      FACT1( n     NUMBER)

     IS

         f    NUMBER:=1;

     BEGIN

         FOR   i   IN   1 . . n

              LOOP

                 f := f  * i;

         END   LOOP;

       dbms_output.put_line( 'Factorial for given no is : ' | | f );

    END;

    /

Procedure created.

SQL> SET   SERVEROUTPUT   ON;

To see output do following steps:

SQL> Begin

           fact1(4);

           End;

           /

Factorial for given no is : 24

PL/SQL procedure successfully completed.

6)    Write a PL/SQL program to create FUNCTION?

(Here we are applying this funtion on EMP table, EMP table which contains description as below. So we must create EMP table with EMPID, EMPNAME, SAL. Then insert few employees details. Then after create FUNCTION and execute it)

SQL> Create table EMP(empid varchar2(4), empname  varchar2(30), sal  number(10));

Table created.

SQL> insert into EMP values (E101, ‘Karthikeyan’, 6250);

1 row created.

SQL> insert into EMP values (E102, ‘Anand’, 2500);

1 row created.

SQL> insert into EMP values (E103, ‘Raja’, 1900);

1 row created.