PRACTICAL NO.

Question 1 : Create the following database :

 Colleges (cname, city, address, phone, afdate)

 Staffs (sid, sname, saddress, contacts)
 Staffjoins (sid, cname, dept, DOJ, post, salary)
 Teachings (sid, class, paperid, fsession, tsession)
 Subjects (paperid, subject, paperno, papername)

1. COLLEGES TABLE :

a) Create the above tables with the given specifications and constraints .

Command:

SQL>create table colleges(cname varchar2(40) primary key,city varchar2(20),address

varchar2(40),phone number(10),afdate date);

Table created.

SQL>desc colleges;

Name Null? Type

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

CNAME NOT NULL VARCHAR2(40)

CITY VARCHAR2(20)
ADDRESS VARCHAR2(40)
PHONE NUMBER(10)
AFDATE DATE

b) Insert about 5 rows as are appropriate to solve the following queries.

Command:
SQL> insert into colleges values('&cname','&city','&address',&phone,'&afdate');
Enter value for cname: Mahant Laxmidas College
Enter value for city: raipur
Enter value for address: ampara sector 3

1
Enter value for phone: 989356156
Enter value for afdate: 16-aug-1995
old 1: insert into colleges values('&cname','&city','&address',&phone,'&afdate')
new 1: insert into colleges values('Mahanat Laxmidas College','raipur','ampara
sector3',8546971537,'16-aug-1995')

1 row created.
SQL> /
Enter value for cname: durga college
Enter value for city: raipur
Enter value for address: ampara sector 4
Enter value for phone: 9835725781
Enter value for afdate: 12-aug-1992
old 1: insert into colleges values('&cname','&city','&address',&phone,'&afdate')
new 1: insert into colleges values('durga college','raipur','ampara sector 4',9835725781,'12-aug-
1992')

1 row created.
SQL> /
Enter value for cname: vivekanand mahavidyalay
Enter value for city: raipur
Enter value for address: modhapara raipur
Enter value for phone: 8737923008
Enter value for afdate: 09-may-1999
old 1: insert into colleges values('&cname','&city','&address',&phone,'&afdate')
new 1: insert into colleges values('vivekanand mahavidyalay','raipur','modhapara
raipur',8737923008,'09-may-1999')

1 row created.
SQL> /
Enter value for cname: raipur institute of tech.
Enter value for city: raipur
Enter value for address: tatibandh sector 9
Enter value for phone: 7886644289
Enter value for afdate: 15-jun-1997
old 1: insert into colleges values('&cname','&city','&address',&phone,'&afdate')
new 1: insert into colleges values('raipur institute of tech.','raipur','tatibandh sector
9',7886644289,'15-jun-1997')

1 row created.
SQL> /
2
Enter value for cname: Adarsh College
Enter value for city: raipur
Enter value for address: pragati nagar sector 39
Enter value for phone: 8546971537
Enter value for afdate: 18-sep-2008
old 1: insert into colleges values('&cname','&city','&address',&phone,'&afdate')
new 1: insert into colleges values('Adarsh College','raipur','pragati nagar sector
39',8546971537,'18-sep-2008')

1 row created.

SQL> select * from colleges;

CNAME CITY ADDRESS PHONE AFDATE

-------------------------------- -------------- ----------------------- - ------------------- --------------------
Mahant Laxmidas College raipur amapara sector 3 9893561526 16-AUG-95
durga college raipur amapara sector 4 9835725781 12-AUG-92
vivekanand mahavidyalay raipur modhapara raipur 8737923008 09-MAY-99
raipur institute of tech. raipur tatibandh sector 9 7886644289 15-JUN-97
Adarsh College raipur pragati nagar sector 39 8546971537 18-SEP-08

2. STAFFS TABLE :

a) Create the above tables with the given specifications and constraints .

Command:

SQL> create table staff(sid number(10) primary key,sname varchar2(20) not null, saddress
varchar(40)not null,contacts number(12) not null);

Table created.
SQL> desc staff;
Name Null? Type
------------------------------- --------------- -------------------
SID NOT NULL NUMBER(10)
SNAME NOT NULL VARCHAR2(20)
SADDRESS NOT NULL VARCHAR2(40)
CONTACTS NOT NULL NUMBER(12)

3
b) Insert about 5 rows as are appropriate to solve the following queries.

Command:

SQL> insert into staff values(&sid,'&sname','&saddress',&contacts);

Enter value for sid: 101
Enter value for sname: meera dhruw
Enter value for saddress: raipur
Enter value for contacts: 7869425452
old 1: insert into staff values(&sid,'&sname','&saddress',&contacts)
new 1: insert into staff values(101,'meera dhruw','raipur',7869425452)

1 row created.

SQL> /

Enter value for sid: 102

Enter value for sname: raja singh
Enter value for saddress: durg
Enter value for contacts: 9875436159
old 1: insert into staff values(&sid,'&sname','&saddress',&contacts)
new 1: insert into staff values(102,'raja singh','durg',9875436159)

1 row created.

SQL> /
Enter value for sid: 103
Enter value for sname: mohan sahu
Enter value for saddress: bilaspur
Enter value for contacts: 7584697341
old 1: insert into staff values(&sid,'&sname','&saddress',&contacts)
new 1: insert into staff values(103,'mohan sahu','bilaspur',7584697341)

1 row created.

SQL> /
Enter value for sid: 104
Enter value for sname: kamal sahu
Enter value for saddress: raipur
Enter value for contacts: 9836874581
old 1: insert into staff values(&sid,'&sname','&saddress',&contacts)
new 1: insert into staff values(104,'kamal sahu','raipur',9836874581)

1 row created.

SQL> /

4
Enter value for sid: 105
Enter value for sname: rohan hora
Enter value for saddress: raipur
Enter value for contacts: 7684513971
old 1: insert into staff values(&sid,'&sname','&saddress',&contacts)
new 1: insert into staff values(105,'rohan hora','raipur',7684513971)

1 row created.

SQL> /
Enter value for sid: 106
Enter value for sname: mahhira
Enter value for saddress: raipur
Enter value for contacts: 7845329612
old 1: insert into staff values(&sid,'&sname','&saddress',&contacts)
new 1: insert into staff values(106,'mahhira','raipur',7845329612)

1 row created.

SQL> select * from staff;

SID SNAME SADDRESS CONTACTS

-------- -------------------- ---------------------- ------------------
101 meera dhruw raipur 7869425452
102 raja singh durg 9875436159
103 mohan sahu bilaspur 7584697341
104 kamal sahu raipur 9836874581
105 rohan hora raipur 7684513971
106 mahhira raipur 7845329612
6 rows selected.

3. STAFFJOINS TABLE :

a) Create the above tables with the given specifications and constraints .

Command:

SQL> create table staffjoins(sid number(10) primary key,cname varchar2(20) not null,dept
varchar2(20) not null,doj date,post varchar2(20),salary number(9) not null);

Table created.

SQL> desc staffjoins;

5
Name Null? Type
---------------------- ------------------- --------------
SID NOT NULL NUMBER(10)
CNAME NOT NULL VARCHAR2(20)
DEPT NOT NULL VARCHAR2(20)
DOJ DATE
POST VARCHAR2(20)
SALARY NOT NULL NUMBER(9)

b) Insert about 5 rows as are appropriate to solve the following queries.

Command:

SQL> insert into staffjoins values(&sid,'&cname','&dept','&doj','&post',&salary);

Enter value for sid: 101
Enter value for cname: durga college
Enter value for dept: computer
Enter value for doj: 12-mar-2012
Enter value for post: professor
Enter value for salary: 18000
old 1: insert into staffjoins values(&sid,'&cname','&dept','&doj','&post',&salary)
new 1: insert into staffjoins values(101,'durga college','computer','12-mar-
2012','professor',18000)

1 row created.

SQL> /
Enter value for sid: 102
Enter value for cname: pragati college
Enter value for dept: management
Enter value for doj: 18-aug-2009
Enter value for post: professor
Enter value for salary: 21000
old 1: insert into staffjoins values(&sid,'&cname','&dept','&doj','&post',&salary)
new 1: insert into staffjoins values(102,'pragati college','management','18-aug-
2009','professor',21000)

1 row created.

6
SQL> /
Enter value for sid: 103
Enter value for cname: adarsh college
Enter value for dept: commerce
Enter value for doj: 23-apr-2016
Enter value for post: professor
Enter value for salary: 19000
old 1: insert into staffjoins values(&sid,'&cname','&dept','&doj','&post',&salary)
new 1: insert into staffjoins values(103,'adarsh college','commerce','23-apr-
2016','professor',19000)

1 row created.

SQL> /
Enter value for sid: 104
Enter value for cname: durga college
Enter value for dept: computer
Enter value for doj: 21-sep-2014
Enter value for post: professor
Enter value for salary: 20000
old 1: insert into staffjoins values(&sid,'&cname','&dept','&doj','&post',&salary)
new 1: insert into staffjoins values(104,'durga college','computer','21-sep-
2014','professor',20000)

1 row created.

SQL> /
Enter value for sid: 105
Enter value for cname: adarsh college
Enter value for dept: education
Enter value for doj: 15-may-2007
Enter value for post: professor
Enter value for salary: 25000
old 1: insert into staffjoins values(&sid,'&cname','&dept','&doj','&post',&salary)
new 1: insert into staffjoins values(105,'adarsh college','education','15-may-
2007','professor',25000)

1 row created.

7
SQL> select * from staffjoins;

SID CNAME DEPT DOJ POST SALARY

---------- -------------------- -------------------- --------------- ------------- -----------------
101 durga college computer 12-MAR-12 professor 18000
102 pragati college management 18-AUG-09 professor 21000
103 adarsh college commerce 23-APR-16 professor 19000
104 durga college computer 21-SEP-14 professor 20000
105 adarsh college education 15-MAY-07 professor 25000

4. TEACHINGS TABLE :

a) Create the above tables with the given specifications and constraints .

Command:

SQL> create table teachings(sid number(10) primary key,class varchar2(10) not null,paperid
number(10) unique,fsession date,tsession date);

Table created.

SQL> desc teachings;

Name Null? Type
--------------------------------- --------------- ----------------------------
SID NOT NULL NUMBER(10)
CLASS NOT NULL VARCHAR2(10)
PAPERID NUMBER(10)
FSESSION DATE
TSESSION DATE

b) Insert about 5 rows as are appropriate to solve the following queries.

Command:

SQL> insert into teachings values(&sid,'&class',&paperid,'&fsession','&tsession');

Enter value for sid: 101
Enter value for class: bca 1
Enter value for paperid: 1101
Enter value for fsession: 16-jun-2018
Enter value for tsession: 25-mar-2019

8
old 1: insert into teachings values(&sid,'&class',&paperid,'&fsession','&tsession')
new 1: insert into teachings values(101,'bca 1',1101,'16-jun-2018','25-mar-2019')

1 row created.

SQL> /
Enter value for sid: 102
Enter value for class: Management
Enter value for paperid: 1102
Enter value for fsession: 16-jun-2018
Enter value for tsession: 25-mar-2019
old 1: insert into teachings values(&sid,'&class',&paperid,'&fsession','&tsession')
new 1: insert into teachings values(102,'Management',1102,'16-jun-2018','25-mar-2019')

1 row created.

SQL> /
Enter value for sid: 103
Enter value for class: economics
Enter value for paperid: 1103
Enter value for fsession: 16-jul-2018
Enter value for tsession: 23-mar-2019
old 1: insert into teachings values(&sid,'&class',&paperid,'&fsession','&tsession')
new 1: insert into teachings values(103,'economics',1103,'16-jul-2018','23-mar-2019')

1 row created.

SQL> /
Enter value for sid: 104
Enter value for class: Maths
Enter value for paperid: 1104
Enter value for fsession: 16-jun-2018
Enter value for tsession: 25-mar-2019
old 1: insert into teachings values(&sid,'&class',&paperid,'&fsession','&tsession')
new 1: insert into teachings values(104,'Maths',1104,'16-jun-2018','25-mar-2019')

1 row created.

SQL> /
Enter value for sid: 105

9
Enter value for class: evs
Enter value for paperid: 1105
Enter value for fsession: 16-jun-2018
Enter value for tsession: 25-mar-2019
old 1: insert into teachings values(&sid,'&class',&paperid,'&fsession','&tsession')
new 1: insert into teachings values(105,'evs',1105,'16-jun-2018','25-mar-2019')

1 row created.

SQL> select * from teachings;

SID CLASS PAPERID FSESSION TSESSION

---------- -------------- ---------------- -------------------- -----------------
101 bca 1 1101 16-JUN-18 25-MAR-19
102 Management 1102 16-JUN-18 25-MAR-19
103 economics 1103 16-JUL-18 23-MAR-19
104 Maths 1104 16-JUN-18 25-MAR-19
105 evs 1105 16-JUN-18 25-MAR-19

5. SUBJECTS TABLE :

a) Create the above tables with the given specifications and constraints .

Command:

SQL> create table subjects(paperid number(10) primary key,subject varchar2(20) not

null,paperno number(10) not null,papername varchar2(20) not null);
Table created.

SQL> desc subjects;

Name Null? Type
---------------------------------- ----------- -------------
PAPERID NOT NULL NUMBER(10)
SUBJECT NOT NULL VARCHAR2(20)
PAPERNO NOT NULL NUMBER(10)
PAPERNAME NOT NULL VARCHAR2(20)
b) Insert about 5 rows as are appropriate to solve the following queries.

Command:

10
SQL> insert into subjects values(&paperid,'&subject',&paperno,'&papername');
Enter value for paperid: 1101
Enter value for subject: C language
Enter value for paperno: 506
Enter value for papername: c language
old 1: insert into subjects values(&paperid,'&subject',&paperno,'&papername')
new 1: insert into subjects values(1101,'C language',506,'c language')

1 row created.

SQL> /
Enter value for paperid: 1102
Enter value for subject: management
Enter value for paperno: 301
Enter value for papername: management
old 1: insert into subjects values(&paperid,'&subject',&paperno,'&papername')
new 1: insert into subjects values(1102,'management',301,'management')

1 row created.

SQL> /
Enter value for paperid: 1103
Enter value for subject: economics
Enter value for paperno: 402
Enter value for papername: economics
old 1: insert into subjects values(&paperid,'&subject',&paperno,'&papername')
new 1: insert into subjects values(1103,'economics',402,'economics')

1 row created.

SQL> /
Enter value for paperid: 1104
Enter value for subject: maths
Enter value for paperno: 502
Enter value for papername: discrete maths
old 1: insert into subjects values(&paperid,'&subject',&paperno,'&papername')
new 1: insert into subjects values(1104,'maths',502,'discrete maths')

1 row created.

11
SQL> /
Enter value for paperid: 1105
Enter value for subject: evs
Enter value for paperno: 801
Enter value for papername: evs
old 1: insert into subjects values(&paperid,'&subject',&paperno,'&papername')
new 1: insert into subjects values(1105,'evs',801,'evs')
1 row created.
SQL> select * from subjects;
PAPERID SUBJECT PAPERNO PAPERNAME
----------------- ----------------------- ----------------- ----------------------

1101 C language 506 c language

1102 management 301 management
1103 economics 402 economics
1104 maths 502 discrete maths
1105 evs 801 evs

c ) List the names of the teachers teaching computer subjects.

Command :

SQL> select staff.sname from staff,teachings,subjects where teachings.paperid=subjects.paperid

and subjects.subject='computer';

SNAME
--------------------
meera dhruw
raja singh
mohan sahu
kamal sahu
rohan hora
mahhira
meera dhruw
raja singh
mohan sahu
kamal sahu
rohan hora
mahhira
12 rows selected.

12
d ) List the names and cities of all staff working in your college.
Command :

SQL> select staff.sname,colleges.city from staff,colleges,staffjoins where staf

f.sid=staffjoins.sid and colleges.cname=staffjoins.cname and colleges.cname='ada
rsh college';

SNAME CITY
-------------------- ---------------
mohan sahu raipur
rohan hora Raipur

e ) List the names and cities of all staff working in your college who earn more than 15000

Command :

SQL> select staff.sname,colleges.city from staff,colleges,staffjoins where

staff.sid=staffjoins.sid and colleges.cname=staffjoins.cname and colleges.cname='ada
rsh college' and salary>15000;

SNAME CITY
-------------------- ---------------
mohan sahu raipur
rohan hora raipur

f ) Find the staffs whose names start with ‘M’ or ‘R’ and ends with ‘A’ or 7 characters
long.

Command :

SQL> select sname from staff where sname like 'm%a' or sname like 'r%a' or sname
like '_______';

SNAME
--------------------
rohan hora
mahhira

13
g ) Find the staffs whose date of joining is 2005.

Command :

SQL> select staff.sname from staff , staffjoins where staff.sid= staffjoins.sid and staffjoins.doj
between '1-jan-2005' and '31-dec-2005';

SNAME
--------------------
mohan sahu

h ) Modify the database so the staff N1 now works in C2 college.

Command :

SQL> update staffjoins set cname='Mahant Laxmidas' where sid=104;

1 row updated

i ) List the names of subjects, which T1 teaches in this session or all sessions.

Command :

SQL> select subject from teachings,subjects where subjects.paperid=teachings.paperid and

teachings.sid=102 and fsession='16-jun-2018' or fsession=tsession;

SUBJECT
--------------------
management

j ) Find the colleges who have most number of staffs.

Command:

SQL> select cname from staffjoins where sid = (select max(count(sid)) from staff
joins group by cname);

no rows selected

k ) Find the college that has the smallest payroll.

Command:

SQL> select cname from staffjoins where salary=(select min(salary) from staffjoins);

14
CNAME
--------------------
durga college

l ) List maximum,average,minimum,salary of each college.

Command :

SQL> select max(salary),min(salary),avg(salary) from staffjoins;

MAX(SALARY) MIN(SALARY) AVG(SALARY)

----------- ----------- -----------
25000 18000 20600

m ) Create a view having fields sname, cname , dept, DOJ and post.

Command :

SQL> create view v1 as select staff.sname,staffjoins.cname,staffjoins.dept,staff

joins.doj,staffjoins.post from staff,staffjoins;
SQL> select * from v1;

SNAME CNAME DEPT DOJ POST

------------------ ------------------ --------------- --------- --------
meera dhruw durga college computer 12-MAR-12 professor
raja singh durga college computer 12-MAR-12 professor
mohan sahu durga college computer 12-MAR-12 professor
kamal sahu durga college computer 12-MAR-12 professor
rohan hora durga college computer 12-MAR-12 professor
mahhira durga college computer 12-MAR-12 professor
meera dhruw pragati college management 8-AUG-09 professor
raja singh pragati college management 18-AUG-09 professor

mohan sahu pragati college management 18-AUG-09 professor

kamal sahu pragati college management 18-AUG-09 professor
rohan hora pragati college management 18-AUG-09 professor
mahhira pragati college management 18-AUG-09 professor
meera dhruw adarsh college commerce 23-APR-05 professor
raja singh adarsh college commerce 23-APR-05 professor
mohan sahu adarsh college commerce 23-APR-05 professor
kamal sahu adarsh college commerce 23-APR-05 professor
rohan hora adarsh college commerce 23-APR-05 professor
mahhira adarsh college commerce 23-APR-05 professor
15
meera dhruw Mahant Laxmidas computer 21-SEP-14 professor
raja singh Mahant Laxmidas computer 21-SEP-14 professor
mohan sahu Mahant Laxmidas computer 21-SEP-14 professor
kamal sahu Mahant Laxmidas computer 21-SEP-14 professor
rohan hora Mahant Laxmidas computer 21-SEP-14 professor
mahhira Mahant Laxmidas computer 21-SEP-14 professor
meera dhruw adarsh college education 15-MAY-07 professor
raja singh adarsh college education 15-MAY-07 professor
mohan sahu adarsh college education 15-MAY-07 professor
kamal sahu adarsh college education 15-MAY-07 professor
rohan hora adarsh college education 15-MAY-07 professor
mahhira adarsh college education 15-MAY-07 professor

30 rows selected.

16
 PRACTICAL NO. 2
Question 2 : Create the following database :
 Enrollment(enrollno,name,gender,DOB,address,phone)
 Admission(admno, enrollno,course,yearsem,date,cname)
 Colleges(cname,city ,address ,phone,afdate)
 FeeStructure(course,yearsem,fee)
 Payment (billno,admno,amount,pdate,purpose)

1. Enrollement Table
a ) Create the above tables with the given specifications and constraints .\

Command:

SQL> create table enrollment(enrollno number(5) primary key,name varchar(40) not

null,gender varchar(1) not null,dob date,address varchar(40) not null,phone number(10));

Table created.
SQL> desc enrollment;
Name Null? Type
--------------------------------- -------- ---------------------
ENROLLNO NOT NULL NUMBER(5)
NAME NOT NULL VARCHAR2(40)
GENDER NOT NULL VARCHAR2(1)
DOB DATE
ADDRESS NOT NULL VARCHAR2(40)
PHONE NUMBER(10)

b) Insert about 5 rows as are appropriate to solve the following queries.

Command:
SQL> insert into enrollment values(&enrollno,'&name','&gender','&dob','&address',&phone);
Enter value for enrollno: 101
Enter value for name: nizam
Enter value for gender: m
Enter value for dob: 13-aug-1999

17
Enter value for address: raipur
Enter value for phone: 8962804573
old 1: insert into enrollment values(&enrollno,'&name','&gender','&dob','&address',&phone)
new 1: insert into enrollment values(101,'nizam','m','13-aug-1999','raipur',8962804573)

1 row created.

SQL> /
Enter value for enrollno: 102
Enter value for name: shilpa
Enter value for gender: f
Enter value for dob: 04-feb-1999
Enter value for address: agra
Enter value for phone: 9990001234
old 1: insert into enrollment values(&enrollno,'&name','&gender','&dob','&address',&phone)
new 1: insert into enrollment values(102,'shilpa','f','04-feb-1999','agra',9990001234)

1 row created.

SQL> /
Enter value for enrollno: 103
Enter value for name: mohnish
Enter value for gender: m
Enter value for dob: 03-feb-2000
Enter value for address: adhara
Enter value for phone: 8982801234
old 1: insert into enrollment values(&enrollno,'&name','&gender','&dob','&address',&phone)
new 1: insert into enrollment values(103,'mohnish','m','03-feb-2000','adhara',8982801234)

1 row created.

SQL> /
Enter value for enrollno: 104
Enter value for name: minu
Enter value for gender: f
Enter value for dob: 05-mar-1999
Enter value for address: pagalpur
Enter value for phone: 9399123409
old 1: insert into enrollment values(&enrollno,'&name','&gender','&dob','&address',&phone)
new 1: insert into enrollment values(104,'minu','f','05-mar-1999','pagalpur',9399123409)

18
1 row created.

SQL> /
Enter value for enrollno: 105
Enter value for name: cheeku
Enter value for gender: m
Enter value for dob: 19-sep-1999
Enter value for address: garakhpur
Enter value for phone: 7999623999
old 1: insert into enrollment values(&enrollno,'&name','&gender','&dob','&address',&phone)
new 1: insert into enrollment values(105,'cheeku','m','19-sep-1999','garakhpur',7999623999)

1 row created.
SQL> select * from enrollment;

ENROLLNO NAME G DOB ADDRESS PHONE

------------------- ---------------- ------- -------- ------------------ ----------------
101 nizam m 13-AUG-99 raipur 8962804573
102 shilpa f 04-FEB-99 agra 9990001234
103 mohnish m 03-FEB-00 adhara 8982801234
104 minu f 05-MAR-99 pagalpur 9399123409
105 cheeku m 19-SEP-99 garakhpur 7999623999

2. Admisssion Table
a ) Create the above tables with the given specifications and constraints .\

Command:

SQL> create table admission(admno number(5) primary key,enrollno number(5) unique,course

varchar(40),yearsem number(1),aDate date,cname varchar(40));

Table created.

SQL> desc admission;

Name Null? Type
--------------------- --------------------- ----------------------------
ADMNO NOT NULL NUMBER(5)
ENROLLNO NUMBER(5)

19
COURSE VARCHAR2(40)
YEARSEM NUMBER(1)
ADATE DATE
CNAME VARCHAR2(40)

b) Insert about 5 rows as are appropriate to solve the following queries.

Command:
SQL> insert into admission values(&admno,&enrollno,'&course',&yearsem,'&adate','&cname');
Enter value for admno: 1201
Enter value for enrollno: 101
Enter value for course: bca
Enter value for yearsem: 6
Enter value for adate: 16-jun-2018
Enter value for cname: adarsh college
old 1: insert into admission values(&admno,&enrollno,'&course',&yearsem,'&adate','&cname')
new 1: insert into admission values(1201,101,'bca',6,'16-jun-2018','adarsh college')

1 row created.

SQL> /
Enter value for admno: 1202
Enter value for enrollno: 102
Enter value for course: mca
Enter value for yearsem: 6
Enter value for adate: 15-jun-2018
Enter value for cname: adarsh college
old 1: insert into admission values(&admno,&enrollno,'&course',&yearsem,'&adate','&cname')
new 1: insert into admission values(1202,102,'mca',6,'15-jun-2018','adarsh college')

1 row created.

SQL> /
Enter value for admno: 1203
Enter value for enrollno: 103
Enter value for course: pgdca
Enter value for yearsem: 2
Enter value for adate: 14-jun-2018
Enter value for cname: mahant college
old 1: insert into admission values(&admno,&enrollno,'&course',&yearsem,'&adate','&cname')

20
new 1: insert into admission values(1203,103,'pgdca',2,'14-jun-2018','mahant college')

1 row created.

SQL> /
Enter value for admno: 1204
Enter value for enrollno: 104
Enter value for course: bcom
Enter value for yearsem: 6
Enter value for adate: 21-jun-2018
Enter value for cname: pragati college
old 1: insert into admission values(&admno,&enrollno,'&course',&yearsem,'&adate','&cname')
new 1: insert into admission values(1204,104,'bcom',6,'21-jun-2018','pragati college')

1 row created.

SQL> /
Enter value for admno: 1205
Enter value for enrollno: 105
Enter value for course: mcon
Enter value for yearsem: 6
Enter value for adate: 1-jun-2018
Enter value for cname: durga college
old 1: insert into admission values(&admno,&enrollno,'&course',&yearsem,'&adate','&cname')
new 1: insert into admission values(1205,105,'mcon',6,'1-jun-2018','durga college')

1 row created.
3. Colleges Table
a ) Create the above tables with the given specifications and constraints .\

Command:

SQL>create table colleges(cname varchar2(40) primary key,city varchar2(20),address

varchar2(40),phone number(10),afdate date);

Table created.

SQL>desc colleges;

21
Name Null? Type

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

CNAME NOT NULL VARCHAR2(40)

CITY VARCHAR2(20)
ADDRESS VARCHAR2(40)
PHONE NUMBER(10)
AFDATE DATE

b) Insert about 5 rows as are appropriate to solve the following queries.

Command:
SQL> insert into colleges values('&cname','&city','&address',&phone,'&afdate');
Enter value for cname: Mahant Laxmidas College
Enter value for city: raipur
Enter value for address: ampara sector 3
Enter value for phone: 989356156
Enter value for afdate: 16-aug-1995
old 1: insert into colleges values('&cname','&city','&address',&phone,'&afdate')
new 1: insert into colleges values('Mahanat Laxmidas College','raipur','ampara
sector3',8546971537,'16-aug-1995')

1 row created.
SQL> /
Enter value for cname: durga college
Enter value for city: raipur
Enter value for address: ampara sector 4
Enter value for phone: 9835725781
Enter value for afdate: 12-aug-1992
old 1: insert into colleges values('&cname','&city','&address',&phone,'&afdate')
new 1: insert into colleges values('durga college','raipur','ampara sector 4',9835725781,'12-aug-
1992')

1 row created.
SQL> /
Enter value for cname: vivekanand mahavidyalay
Enter value for city: raipur
Enter value for address: modhapara raipur
Enter value for phone: 8737923008
Enter value for afdate: 09-may-1999

22
old 1: insert into colleges values('&cname','&city','&address',&phone,'&afdate')
new 1: insert into colleges values('vivekanand mahavidyalay','raipur','modhapara
raipur',8737923008,'09-may-1999')

1 row created.
SQL> /
Enter value for cname: raipur institute of tech.
Enter value for city: raipur
Enter value for address: tatibandh sector 9
Enter value for phone: 7886644289
Enter value for afdate: 15-jun-1997
old 1: insert into colleges values('&cname','&city','&address',&phone,'&afdate')
new 1: insert into colleges values('raipur institute of tech.','raipur','tatibandh sector
9',7886644289,'15-jun-1997')

1 row created.
SQL> /
Enter value for cname: Adarsh College
Enter value for city: raipur
Enter value for address: pragati nagar sector 39
Enter value for phone: 8546971537
Enter value for afdate: 18-sep-2008
old 1: insert into colleges values('&cname','&city','&address',&phone,'&afdate')
new 1: insert into colleges values('Adarsh College','raipur','pragati nagar sector
39',8546971537,'18-sep-2008')

1 row created.

SQL> select * from colleges;

CNAME CITY ADDRESS PHONE AFDATE

-------------------------------- -------------- ----------------------- - ------------------- --------------------
Mahant Laxmidas College raipur amapara sector 3 9893561526 16-AUG-95
durga college raipur amapara sector 4 9835725781 12-AUG-92
vivekanand mahavidyalay raipur modhapara raipur 8737923008 09-MAY-99
raipur institute of tech. raipur tatibandh sector 9 7886644289 15-JUN-97
Adarsh College raipur pragati nagar sector 39 8546971537 18-SEP-08

23
 4. FeeStructure Table
a ) Create the above tables with the given specifications and constraints .

Command:

SQL> create table feestructure(course varchar(40),yearsem number(1),fee number(6));

Table created.

SQL> desc feestructure;

Name Null? Type
------------------------------ --------------- ----------------------------
COURSE VARCHAR2(40)
YEARSEM NUMBER(1)
FEE NUMBER(6)

b) Insert about 5 rows as are appropriate to solve the following queries.

Command:
SQL> insert into feestructure values('&course',&yearsem,&fee);
Enter value for course: bca
Enter value for yearsem: 6
Enter value for fee: 26000
old 1: insert into feestructure values('&course',&yearsem,&fee)
new 1: insert into feestructure values('bca',6,26000)

1 row created.

SQL> /
Enter value for course: mca
Enter value for yearsem: 6
Enter value for fee: 45000
old 1: insert into feestructure values('&course',&yearsem,&fee)
new 1: insert into feestructure values('mca',6,45000)

1 row created.
SQL> /
Enter value for course: pgdca
Enter value for yearsem: 2

24
Enter value for fee: 17600
old 1: insert into feestructure values('&course',&yearsem,&fee)
new 1: insert into feestructure values('pgdca',2,17600)

1 row created.

SQL> /
Enter value for course: bcom
Enter value for yearsem: 6
Enter value for fee: 21500
old 1: insert into feestructure values('&course',&yearsem,&fee)
new 1: insert into feestructure values('bcom',6,21500)

1 row created.

SQL> /
Enter value for course: mcom
Enter value for yearsem: 6
Enter value for fee: 22000
old 1: insert into feestructure values('&course',&yearsem,&fee)
new 1: insert into feestructure values('mcom',6,22000)

1 row created.

5. Payment Table
a ) Create the above tables with the given specifications and constraints .\

Command:
SQL> create table payment(billno number(9) primary key,admno number(5) unique,amount
number(6),pdate date,purpose varchar(40));

Table created.

SQL> desc payment;

Name Null? Type

------------------------------ --------------- ----------------------------
BILLNO NOT NULL NUMBER(9)
ADMNO NUMBER(5)

25
AMOUNT NUMBER(6)
PDATE DATE
PURPOSE VARCHAR(40)

b) Insert about 5 rows as are appropriate to solve the following queries.

Command:
SQL> insert into payment values(&billno,&admno,&amount,'&pdate','&purpose');
Enter value for billno: 1
Enter value for admno: 1201
Enter value for amount: 25000
Enter value for pdate: 25-jun-2018
Enter value for purpose: tution fees
old 1: insert into payment values(&billno,&admno,&amount,'&pdate','&purpose')
new 1: insert into payment values(1,1201,25000,'25-jun-2018','tution fees')

1 row created.
SQL> /
Enter value for billno: 2
Enter value for admno: 1202
Enter value for amount: 2000
Enter value for pdate: 20-jul-2018
Enter value for purpose: caution money
old 1: insert into payment values(&billno,&admno,&amount,'&pdate','&purpose')
new 1: insert into payment values(2,1202,2000,'20-jul-2018','caution money')

1 row created.
SQL> /
Enter value for billno: 3
Enter value for admno: 1203
Enter value for amount: 16000
Enter value for pdate: 10-jul-2018
Enter value for purpose: tution fees
old 1: insert into payment values(&billno,&admno,&amount,'&pdate','&purpose')
new 1: insert into payment values(3,1203,16000,'10-jul-2018','tution fees')

1 row created.

SQL> /
Enter value for billno: 4

26
Enter value for admno: 1204
Enter value for amount: 4000
Enter value for pdate: 11-jul-2018
Enter value for purpose: sports fees
old 1: insert into payment values(&billno,&admno,&amount,'&pdate','&purpose')
new 1: insert into payment values(4,1204,4000,'11-jul-2018','sports fees')

1 row created.

SQL> /
Enter value for billno: 5
Enter value for admno: 1205
Enter value for amount: 2100
Enter value for pdate: 12-nov-2018
Enter value for purpose: exam fees
old 1: insert into payment values(&billno,&admno,&amount,'&pdate','&purpose')
new 1: insert into payment values(5,1205,2100,'12-nov-2018','exam fees')

1 row created.

SQL> select * from payment;

BILLNO ADMNO AMOUNT PDATE PURPOSE

---------- ---------- --------------- --------------------- --------------
1 1201 25000 25-JUN-18 tution fees
2 1202 2000 20-JUL-18 caution money
3 1203 16000 10-JUL-18 tution fees
4 1204 4000 11-JUL-18 sports fees
5 1205 2100 12-NOV-18 exam fees

c) Get full detail of all students who took admission this year.

Commands:

SQL> select * from enrollment,admission where enrollment.enrollno=admission.enro

llno and adate between '1-jan-2018' and '31-dec-2018';

ENROLLNO NAME G DOB ADDRESS PHONE ADMNO ENROLLNO COURSE YEARSEM ADATE CNAME
---------------- ------------ --- -------- ------------- ------------ ------------- -------------- ---------- ------------- ----------- ---------------
101 nizam m 13-AUG-99 raipur 896280457 1201 101 bca 6 16-JUN-18 adarsh college
102 shilpa f 04-FEB-99 agra 9990001234 1202 102 mca 6 15-JUN-18 adarsh college

27
103 mohnish m 03-FEB-00 dhara 8982801234 1203 103 pgdca 2 14-JUN-18 mahant college
104 minu f 05-MAR-99 pagalpur 9399123409 1204 104 bcom 6 21-JUN-18 pragati college
105 cheeku m 19-SEP-99 garakhpur 7999623999 1205 105 mcon 6 01-JUN-18 durga college

d) Get detail of students who took admission in Bhilai colleges.

Command:

SQL> select enrollment.enrollno, enrollment.name, enrollment.gender, enrollment.

dob, enrollment.address, enrollment.phone, admission.admno,admission.course from
enrollment,admission,colleges where enrollment.enrollno=admission.enrollno and
colleges.city='bhilai';

no rows selected

e ) Calculate the total amount of fees collected in this session by your college.

Command:

SQL> select sum(payment.amount) from admission,payment where admission.adate bet

ween '1-jan-18' and '31-dec-2018';

SUM(PAYMENT.AMOUNT)
-------------------
245500

f ) list the number of admission in your class in every year.

Command:

SQL> select count(admno) from admission where adate between '1-jan-2018' and '31-dec-2018'
and course='bca';

COUNT(ADMNO)
------------
1

28
 PRACTICAL NO. 3

Question 3 : Create the following database :

 Sujects(paperid,subject,paper,papername)
 Test(paperid, date,time,max,min)
 Score (rollno,paperid,maks,attendence)
 Students (admno,rollno,class,yearsem)

1. Subjects Table :

a) Create the above tables with the given specifications and constraints .

Command:
SQL> create table subjects(paperid number(10) primary key,subject varchar2(20) not
null,paperno number(10) not null,papername varchar2(20) not null);
Table created.
SQL> desc subjects;
Name Null? Type
------------------------------- ------------- -------------
PAPERID NOT NULL NUMBER(10)
SUBJECT NOT NULL VARCHAR2(20)
PAPERNO NOT NULL NUMBER(10)
PAPERNAME NOT NULL VARCHAR2(20)

b) Insert about 5 rows as are appropriate to solve the following queries.

Command:
SQL> insert into subjects values(&paperid,'&subject',&paperno,'&papername');
Enter value for paperid: 1101
Enter value for subject: C language
Enter value for paperno: 506
Enter value for papername: c language
old 1: insert into subjects values(&paperid,'&subject',&paperno,'&papername')
new 1: insert into subjects values(1101,'C language',506,'c language')

1 row created.

SQL> /

29
Enter value for paperid: 1102
Enter value for subject: management
Enter value for paperno: 301
Enter value for papername: management
old 1: insert into subjects values(&paperid,'&subject',&paperno,'&papername')
new 1: insert into subjects values(1102,'management',301,'management')

1 row created.

SQL> /
Enter value for paperid: 1103
Enter value for subject: economics
Enter value for paperno: 402
Enter value for papername: economics
old 1: insert into subjects values(&paperid,'&subject',&paperno,'&papername')
new 1: insert into subjects values(1103,'economics',402,'economics')

1 row created.

SQL> /
Enter value for paperid: 1104
Enter value for subject: maths
Enter value for paperno: 502
Enter value for papername: discrete maths
old 1: insert into subjects values(&paperid,'&subject',&paperno,'&papername')
new 1: insert into subjects values(1104,'maths',502,'discrete maths')

1 row created.

SQL> /
Enter value for paperid: 1105
Enter value for subject: evs
Enter value for paperno: 801
Enter value for papername: evs
old 1: insert into subjects values(&paperid,'&subject',&paperno,'&papername')
new 1: insert into subjects values(1105,'evs',801,'evs')

1 row created.

30
 2. Test Table :

a) Create the above tables with the given specifications and constraints .

Command:
SQL> create table test(paperid number(10) primary key,tdate date,time varchar2(10) not
null,max number(5) not null,min number(5) not null);

Table created.

SQL> desc test;

Name Null? Type
----------------------------- ---------- ----------------------------
PAPERID NOT NULL NUMBER(10)
TDATE DATE
TIME NOT NULL VARCHAR2(10)
MAX NOT NULL NUMBER(5)
MIN NOT NULL NUMBER(5)

b) Insert about 5 rows as are appropriate to solve the following queries.

Command:
SQL> insert into test values(&paperid,'&tdate','&time',&max,&min);
Enter value for paperid: 1101
Enter value for tdate: 20-aug-2018
Enter value for time: 8:30
Enter value for max: 50
Enter value for min: 17
old 1: insert into test values(&paperid,'&tdate','&time',&max,&min)
new 1: insert into test values(1101,'20-aug-2018','8:30',50,17)

1 row created.

SQL> /
Enter value for paperid: 1102
Enter value for tdate: 21-aug-2018
Enter value for time: 9:30
Enter value for max: 100
Enter value for min: 34
old 1: insert into test values(&paperid,'&tdate','&time',&max,&min)

31
new 1: insert into test values(1102,'21-aug-2018','9:30',100,34)

1 row created.

SQL> /
Enter value for paperid: 1103
Enter value for tdate: 22-aug-2018
Enter value for time: 11:50
Enter value for max: 100
Enter value for min: 34
old 1: insert into test values(&paperid,'&tdate','&time',&max,&min)
new 1: insert into test values(1103,'22-aug-2018','11:50',100,34)

1 row created.

SQL> /
Enter value for paperid: 1104
Enter value for tdate: 23-aug-2018
Enter value for time: 1:30
Enter value for max: 50
Enter value for min: 17
old 1: insert into test values(&paperid,'&tdate','&time',&max,&min)
new 1: insert into test values(1104,'23-aug-2018','1:30',50,17)

1 row created.

SQL> /
Enter value for paperid: 1105
Enter value for tdate: 25-aug-2018
Enter value for time: 8:30
Enter value for max: 75
Enter value for min: 25
old 1: insert into test values(&paperid,'&tdate','&time',&max,&min)
new 1: insert into test values(1105,'25-aug-2018','8:30',75,25)

1 row created.

32
 3. Score Table :

a) Create the above tables with the given specifications and constraints .

Command:
SQL> create table score(rollno number(10) primary key,paperid number(10) unique,
marks number(5) not null,attendance varchar2(7));

Table created.

SQL> desc score;

Name Null? Type
----------------------------- --------------- ----------------------------
ROLLNO NOT NULL NUMBER(10)
PAPERID NUMBER(10)
MARKS NOT NULL NUMBER(5)
ATTENDANCE VARCHAR2(7)

b) Insert about 5 rows as are appropriate to solve the following queries.

Command:
SQL> insert into score values(&rollno,&paperid,&marks,'&attendance');
Enter value for rollno: 501
Enter value for paperid: 1101
Enter value for marks: 34
Enter value for attendance: p
old 1: insert into score values(&rollno,&paperid,&marks,'&attendance')
new 1: insert into score values(501,1101,34,'p')

1 row created.
SQL> /
Enter value for rollno: 502
Enter value for paperid: 1102
Enter value for marks: 66
Enter value for attendance: p
old 1: insert into score values(&rollno,&paperid,&marks,'&attendance')
new 1: insert into score values(502,1102,66,'p')

1 row created.

33
SQL> /
Enter value for rollno: 503
Enter value for paperid: 1103
Enter value for marks: 75
Enter value for attendance: p
old 1: insert into score values(&rollno,&paperid,&marks,'&attendance')
new 1: insert into score values(503,1103,75,'p')

1 row created.

SQL> /
Enter value for rollno: 504
Enter value for paperid: 1104
Enter value for marks: 0
Enter value for attendance: a
old 1: insert into score values(&rollno,&paperid,&marks,'&attendance')
new 1: insert into score values(504,1104,0,'a')

1 row created.

SQL> /
Enter value for rollno: 505
Enter value for paperid: 1105
Enter value for marks: 52
Enter value for attendance: p
old 1: insert into score values(&rollno,&paperid,&marks,'&attendance')
new 1: insert into score values(505,1105,52,'p')

1 row created.

4. Students Table :

a) Create the above tables with the given specifications and constraints .

Command:
SQL> create table students(admno number(10) primary key,rollno number(10) unique
,class varchar2(10) not null,yearsem number(2) not null);

Table created.

34
SQL> desc students;
Name Null? Type
------------------------- ------------ ----------------------------
ADMNO NOT NULL NUMBER(10)
ROLLNO NUMBER(10)
CLASS NOT NULL VARCHAR2(10)
YEARSEM NOT NULL NUMBER(2)

b) Insert about 5 rows as are appropriate to solve the following queries.

Command:
SQL> insert into students values(&admno,&rollno,'&class',&yearsem);
Enter value for admno: 201
Enter value for rollno: 501
Enter value for class: bca 1
Enter value for yearsem: 6
old 1: insert into students values(&admno,&rollno,'&class',&yearsem)
new 1: insert into students values(201,501,'bca 1',6)

1 row created.

SQL> /
Enter value for admno: 202
Enter value for rollno: 502
Enter value for class: bba 1
Enter value for yearsem: 6
old 1: insert into students values(&admno,&rollno,'&class',&yearsem)
new 1: insert into students values(202,502,'bba 1',6)

1 row created.

SQL> /
Enter value for admno: 203
Enter value for rollno: 503
Enter value for class: bcom 1
Enter value for yearsem: 6
old 1: insert into students values(&admno,&rollno,'&class',&yearsem)
new 1: insert into students values(203,503,'bcom 1',6)

1 row created.

35
SQL> /
Enter value for admno: 204
Enter value for rollno: 504
Enter value for class: bca 2
Enter value for yearsem: 6
old 1: insert into students values(&admno,&rollno,'&class',&yearsem)
new 1: insert into students values(204,504,'bca 2',6)

1 row created.

SQL> /
Enter value for admno: 205
Enter value for rollno: 505
Enter value for class: pgdca
Enter value for yearsem: 2
old 1: insert into students values(&admno,&rollno,'&class',&yearsem)
new 1: insert into students values(205,505,'pgdca',2)

1 row created.

c) List the students who were present in a paper of a subject.

Commands:

SQL> select students.admno,students.rollno,students.class,students.yearsem from

score,students where score.rollno=students.rollno and score.attendance='p';

ADMNO ROLLNO CLASS YEARSEM

---------- ---------- ---------- ----------
201 501 bca 1 6
202 502 bba 1 6
203 503 bcom 1 6
205 505 pgdca 2

d) List all roll numbers who have passed in first division.

Commands:

SQL> select score.rollno from score,test where score.paperid=test.paperid and

score.marks*(100/test.max) >59.99;

36
ROLLNO
----------
501
502
503
505

e) List the highest score,average and minimum score in BCA in your college.

Commands:

SQL> select max(score.marks),avg(score.marks),min(score.marks) from score,students where

score.rollno=students.rollno and students.class='bca 2';

MAX(SCORE.MARKS) AVG(SCORE.MARKS) MIN(SCORE.MARKS)

---------------- ---------------- ----------------
56 28 0

37