DBMS Lab Exercise 1

SQL> create table stud(rollno number(7) primary key,sname varchar(25),mark1 number(3),mark2 number(3),mark3 number(3)); 

Table created. 

SQL> create table dept(depid number(2)primary key,depname varchar(5)); 

Table created. 

SQL> create table staff(staffid number(5),staffname varchar(25),designation varchar(10)); 

Table created. 

SQL> desc student;

Name                                      Null?    Type

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

ROLLNO                                    NOT NULL NUMBER(7)

NAME                                               VARCHAR2(25)

MARK1                                              NUMBER(3)

MARK2                                              NUMBER(3)

MARK3                                              NUMBER(3) 

SQL> desc dept;

Name                                      Null?    Type

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

DEPID                                     NOT NULL NUMBER(2)

DEPNAME                                            VARCHAR2(5) 

SQL> desc staff;

Name                                      Null?    Type

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

STAFFID                                            NUMBER(5)

STAFFNAME                                          VARCHAR2(25)

DESIGNATION                                        VARCHAR2(10) 

SQL> alter table stud add(depname varchar(5)); 

Table altered. 

Download the original attachment

SQL> insert into stud values(&rollno,'&sname',&mark1,&mark2,&mark3,'&depname');

Enter value for rollno: 678

Enter value for sname: Ram

Enter value for mark1: 98

Enter value for mark2: 86

Enter value for mark3: 83

Enter value for depname: cse

old   1: insert into stud values(&rollno,'&sname',&mark1,&mark2,&mark3,'&depname')

new   1: insert into stud values(678,'Ram',98,86,83,'cse') 

1 row created. 

SQL> insert into stud values(&rollno,'&sname',&mark1,&mark2,&mark3,'&depname');

Enter value for rollno: 596

Enter value for sname: Sunil

Enter value for mark1: 85

Enter value for mark2: 84

Enter value for mark3: 74

Enter value for depname: eie

old   1: insert into stud values(&rollno,'&sname',&mark1,&mark2,&mark3,'&depname')

new   1: insert into stud values(596,'Sunil',85,84,74,'eie') 

1 row created. 

SQL>  insert into stud values(&rollno,'&sname',&mark1,&mark2,&mark3,'&depname');

Enter value for rollno: 857

Enter value for sname: Uday

Enter value for mark1: 78

Enter value for mark2: 72

Enter value for mark3: 76

Enter value for depname: eee

old   1:  insert into stud values(&rollno,'&sname',&mark1,&mark2,&mark3,'&depname')

new   1:  insert into stud values(857,'Uday',78,72,76,'eee') 

1 row created. 

SQL> insert into stud values(&rollno,'&sname',&mark1,&mark2,&mark3,'&depname');

Enter value for rollno: 756

Enter value for sname: Raju

Enter value for mark1: 82

Enter value for mark2: 69

Enter value for mark3: 87

Enter value for depname: it

old   1: insert into stud values(&rollno,'&sname',&mark1,&mark2,&mark3,'&depname')

new   1: insert into stud values(756,'Raju',82,69,87,'it') 

1 row created. 

SQL>  insert into stud values(&rollno,'&sname',&mark1,&mark2,&mark3,'&depname');

Enter value for rollno: 574

Enter value for sname: Joe

Enter value for mark1: 85

Enter value for mark2: 63

Enter value for mark3: 97

Enter value for depname: cse

old   1:  insert into stud values(&rollno,'&sname',&mark1,&mark2,&mark3,'&depname')

new   1:  insert into stud values(574,'Joe',85,63,97,'cse') 

1 row created. 

SQL>  insert into stud values(&rollno,'&sname',&mark1,&mark2,&mark3,'&depname');

Enter value for rollno: 875

Enter value for sname: Geetha

Enter value for mark1: 82

Enter value for mark2: 92

Enter value for mark3: 76

Enter value for depname: eee

old   1:  insert into stud values(&rollno,'&sname',&mark1,&mark2,&mark3,'&depname')

new   1:  insert into stud values(875,'Geetha',82,92,76,'eee') 

1 row created. 

SQL> alter table stud add(total number(3),percentage number(5,2)); 

Table altered. 

SQL> insert into dept values(&depid,'&depname');

Enter value for depid: 1

Enter value for depname: cse

old   1: insert into dept values(&depid,'&depname')

new   1: insert into dept values(1,'cse') 

1 row created. 

SQL> insert into dept values(&depid,'&depname');

Enter value for depid: 2

Enter value for depname: it

old   1: insert into dept values(&depid,'&depname')

new   1: insert into dept values(2,'it') 

1 row created. 

SQL> insert into dept values(&depid,'&depname');

Enter value for depid: 3

Enter value for depname: eie

old   1: insert into dept values(&depid,'&depname')

new   1: insert into dept values(3,'eie') 

1 row created. 

SQL> insert into dept values(&depid,'&depname');

Enter value for depid: 4

Enter value for depname: eee

old   1: insert into dept values(&depid,'&depname')

new   1: insert into dept values(4,'eee') 

1 row created. 

SQL> insert into dept values(&depid,'&depname');

Enter value for depid: 5

Enter value for depname: ece

old   1: insert into dept values(&depid,'&depname')

new   1: insert into dept values(5,'ece') 

1 row created. 

SQL> insert into dept values(&depid,'&depname');

Enter value for depid: 6

Enter value for depname: bt

old   1: insert into dept values(&depid,'&depname')

new   1: insert into dept values(6,'bt') 

1 row created. 

SQL>  insert into dept values(&depid,'&depname');

Enter value for depid: 7

Enter value for depname: civil

old   1:  insert into dept values(&depid,'&depname')

new   1:  insert into dept values(7,'civil') 

1 row created. 

SQL> update stud set total=mark1+mark2+mark3; 

6 rows updated. 

SQL> update stud set percentage=total/3; 

6 rows updated. 

SQL> desc stud;

Name                                      Null?    Type

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

ROLLNO                                    NOT NULL NUMBER(7)

SNAME                                              VARCHAR2(25)

MARK1                                              NUMBER(3)

MARK2                                              NUMBER(3)

MARK3                                              NUMBER(3)

DEPNAME                                            VARCHAR2(5)

TOTAL                                              NUMBER(3)

PERCENTAGE                                         NUMBER(5,2) 

SQL> alter table staff add primary key(staffid); 

Table altered. 

SQL> alter table staff add depid number(2); 

Table altered. 

SQL> alter table staff add constraint dpfk foreign key(depid) references dept(depid); 

Table altered.  

SQL> insert into staff values(&staffid,'&sname','&designation',&depid);

Enter value for staffid: 101

Enter value for sname: Angel Mary

Enter value for designation: Prof.

Enter value for depid: 1

old   1: insert into staff values(&staffid,'&sname','&designation',&depid)

new   1: insert into staff values(101,'Angel Mary','Prof.',1) 

1 row created. 

SQL> insert into staff values(&staffid,'&sname','&designation',&depid);

Enter value for staffid: 105

Enter value for sname: Lavanya

Enter value for designation: Asst Prof

Enter value for depid: 3

old   1: insert into staff values(&staffid,'&sname','&designation',&depid)

new   1: insert into staff values(105,'Lavanya','Asst Prof',3) 

1 row created. 

SQL>  insert into staff values(&staffid,'&sname','&designation',&depid);

Enter value for staffid: 103

Enter value for sname: Vani

Enter value for designation: Lecturer

Enter value for depid: 4

old   1:  insert into staff values(&staffid,'&sname','&designation',&depid)

new   1:  insert into staff values(103,'Vani','Lecturer',4) 

1 row created. 

SQL> insert into staff values(&staffid,'&sname','&designation',&depid);

Enter value for staffid: 106

Enter value for sname: Sita

Enter value for designation: Prof

Enter value for depid: 6

old   1: insert into staff values(&staffid,'&sname','&designation',&depid)

new   1: insert into staff values(106,'Sita','Prof',6) 

1 row created.  

SQL> insert into staff values(&staffid,'&sname','&designation',&depid);

Enter value for staffid: 110

Enter value for sname: Rani

Enter value for designation: Asst Prof

Enter value for depid: 1

old   1: insert into staff values(&staffid,'&sname','&designation',&depid)

new   1: insert into staff values(110,'Rani','Asst Prof',1) 

1 row created. 

SQL> insert into staff values(&staffid,'&sname','&designation',&depid);

Enter value for staffid: 108

Enter value for sname: Raj

Enter value for designation: Asst Prof

Enter value for depid: 2

old   1: insert into staff values(&staffid,'&sname','&designation',&depid)

new   1: insert into staff values(108,'Raj','Asst Prof',2) 

1 row created. 

SQL> alter table stud add(grade varchar(30)); 

Table altered. 

SQL> update stud set grade=case when percentage>75 then 'First Class with Distinction' when percenta 

  2   

SQL> select * from stud; 

    ROLLNO SNAME                          MARK1      MARK2      MARK3 DEPNA

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

     TOTAL PERCENTAGE GRADE

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

       678 Ram                               98         86         83 cse

       267         89 

       596 Sunil                             85         84         74 eie

       243         81 

       857 Uday                              78         72         76 eee

       226      75.33
 

    ROLLNO SNAME                          MARK1      MARK2      MARK3 DEPNA

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

     TOTAL PERCENTAGE GRADE

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

       756 Raju                              82         69         87 it

       238      79.33 

       574 Joe                               85         63         97 cse

       245      81.67 

       875 Geetha                            82         92         76 eee

       250      83.33
  

6 rows selected. 

SQL> update stud set grade=case when percentage>75.0 then 'first class with distinction' when percen

tage>60.0 and percentage<75.0 then 'first class' else 'second class' end; 

6 rows updated. 

SQL> select sname from stud; 

SNAME

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

Ram

Sunil

Uday

Raju

Joe

Geetha

6 rows selected. 

SQL> select rollno from stud; 

    ROLLNO

----------

       678

       596

       857

       756

       574

       875

6 rows selected. 

SQL> select * from stud; 

    ROLLNO SNAME                          MARK1      MARK2      MARK3 DEPNA

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

     TOTAL PERCENTAGE GRADE

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

       678 Ram                               98         86         83 cse

       267         89 first class with distinction 

       596 Sunil                             85         84         74 eie

       243         81 first class with distinction 

       857 Uday                              78         72         76 eee

       226      75.33 first class with distinction
 

    ROLLNO SNAME                          MARK1      MARK2      MARK3 DEPNA

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

     TOTAL PERCENTAGE GRADE

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

       756 Raju                              82         69         87 it

       238      79.33 first class with distinction 

       574 Joe                               85         63         97 cse

       245      81.67 first class with distinction 

       875 Geetha                            82         92         76 eee

       250      83.33 first class with distinction
 

6 rows selected. 

SQL> select * from staff where designation='Asst Prof' and depid=1; 

   STAFFID STAFFNAME                 DESIGNATIO      DEPID

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

       110 Rani                      Asst Prof           1 

SQL> select count(depid) from dept; 

COUNT(DEPID)

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

           7 

SQL> select min(total) from stud; 

MIN(TOTAL)

----------

       226 

SQL> select avg(percentage), max(percentage) from stud; 

AVG(PERCENTAGE) MAX(PERCENTAGE)

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

     82.5228571              89 

SQL> select count(rollno) from stud where grade='first class with distinction'; 

COUNT(ROLLNO)

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

            6 

SQL> desc stud;

Name                                      Null?    Type

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

ROLLNO                                    NOT NULL NUMBER(7)

SNAME                                              VARCHAR2(25)

MARK1                                              NUMBER(3)

MARK2                                              NUMBER(3)

MARK3                                              NUMBER(3)

DEPNAME                                            VARCHAR2(5)

TOTAL                                              NUMBER(3)

PERCENTAGE                                         NUMBER(5,2)

GRADE                                              VARCHAR2(30) 

SQL> select * from stud; 

    ROLLNO SNAME                          MARK1      MARK2      MARK3 DEPNA

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

     TOTAL PERCENTAGE GRADE

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

       678 Ram                               98         86         83 cse

       267         89 first class with distinction 

       596 Sunil                             85         84         74 eie

       243         81 first class with distinction 

       857 Uday                              78         72         76 eee

       226      75.33 first class with distinction
 

    ROLLNO SNAME                          MARK1      MARK2      MARK3 DEPNA

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

     TOTAL PERCENTAGE GRADE

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

       756 Raju                              82         69         87 it

       238      79.33 first class with distinction 

      574 Joe                               85         63         97 cse

       245      81.67 first class with distinction 

       875 Geetha                            82         92         76 eee

       250      83.33 first class with distinction
  

6 rows selected. 

SQL> select sname from stud where sname like '%a'; 

SNAME

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

Geetha

SQL> select rollno,sname from stud order by total desc; 

    ROLLNO SNAME

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

       678 Ram

       875 Geetha

       574 Joe

       596 Sunil

       756 Raju

       857 Uday 

6 rows selected. 

SQL> select * from dept; 

     DEPID DEPNA

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

         1 cse

         2 it

         3 eie

         4 eee

         5 ece

         6 bt

         7 civil 

7 rows selected. 

SQL> select count(rollno),depname from stud group by depname; 

COUNT(ROLLNO) DEPNA

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

            3 cse

            2 eee

            1 eie

            1 it 

SQL> select distinct depname from stud; 

DEPNA

-----

cse

eee

eie

it 

SQL> select * from staff where designation='Lecturer'; 

   STAFFID STAFFNAME                 DESIGNATIO      DEPID

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

       103 Vani                      Lecturer            4 

SQL> select * from stud where sname like '%e'; 

    ROLLNO SNAME                          MARK1      MARK2      MARK3 DEPNA

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

     TOTAL PERCENTAGE GRADE

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

       574 Joe                               85         63         97 cse

       245     81.67 first class with distinction
 

SQL> delete from stud where sname='Raju'; 

1 row deleted. 

SQL> delete from staff where depid=6; 

1 row deleted.