DBMS Practical File PDF

Preview

Summary

DBMS Practicals...

Description

1 table created Name CNAME CITY ADDRESS PHONE AFDATE

Null? NOT NULL

Type VARCHAR2(20) VARCHAR2(10) VARCHAR2(20) NUMBER(10) DATE

1 table created Name SID SNAME SADDRESS CONTACTS

Null? NOT NULL

Type NUMBER(2) VARCHAR2(20) VARCHAR2(20) NUMBER(10)

1 table created Name SID CNAME DEPT DOJ POST SALARY

1.

Null?

Type NUMBER(2) VARCHAR2(20) VARCHAR2(30) DATE VARCHAR(20) NUMBER(7,2)

Using the following database: Colleges (cname, city, address, phone, afdate) Staffs (sid, sname, saddress, contacts) StaffJoins (sid, cname, dept, DOJ, post, salary) 1

Teachings (sid, class, paperid, fsession, tsession) Subjects (paperid, subject, paperno, papername) Write the following statements for the following:i. Create the above tables with the given specifications and constraints. CREATE TABLE Colleges ( cname varchar2(20) city varchar2(10), address varchar2(20), phone number(10), afdate date ); DESC Colleges;

primary key,

CREATE TABLE Staffs ( sid number(2) primary key, sname varchar2(20), saddress varchar2(20), contacts number(10) ); DESC Staffs; CREATE TABLE StaffJoins ( sid number(2) references Staffs, cname varchar2(20) references Colleges, dept varchar2(30), DOJ date, post varchar2(20), salary number(7,2) ); DESC StaffJoins;

1 table created Name

Null?

SID

2

Type NUMBER(2)

CLASS PAPERID FSESSION TSESSION

VARCHAR2(10) VARCHAR2(10) DATE DATE

1 table created Name PAPERID SUBJECT PAPERNO PAPERNAME

CNAME Disha college Durga college Daga college Central college Chanakya college Palloti college Harishankar college Mats college Degree Girls college RIT

Null? NOT NULL

CITY Raipur Raipur Bhilai Raipur Bhilai Raipur Raipur Raipur Bhilai Raipur

ADDRESS Ram Nagar, Kota K.K Road Civil Lines Fafadih G.E Road Pandri Shankar Nagar Pandri G.E Road G.E Road

Type VARCHAR2(10) VARCHAR2(40) VARCHAR2(10) VARCHAR2(20)

PHONE 4205553 4202253 4658457 9654821565 6548325 9853254185 2565486 4596658 6583245 4598544

10 rows inserted CREATE TABLE Teachings ( sid number(2) references Staffs, class varchar2(10), paperid varchar2(10), fsession date, tsession date ); DESC Teachings; 3

AFDATE 30-JUN-00 30-JAN-75 30-AUG-95 15-MAY-04 02-JUL-03 03-SEP-90 04-OCT-65 05-NOV-04 06-DEC-92 07-FEB-96

CREATE TABLE Subjects ( paperid varchar2(10) subject varchar2(40), paperno varchar2(10), papername varchar2(20) ); DESC Subjects;

primary key,

ii. Insert about 10 rows as appropriate to solve the following

queries. INSERT INTO Colleges VALUES ( ‘&cname’, ‘&city’, ‘&address’, ‘&phone’, ‘&afdate’ );

SID 01 02 03 04 05 06 07 08 09 10

SNAME Mandeep Suyash Bhawana Bhoj Vishal Manish Neha Jaya Vandana Rajesh

SADDRESS Raipur Raipur Raipur Raigarh Rajnandgaon Abhanpur Raipur Bhilai Bhilai Baloda Bazaar

10 rows inserted 4

CONTACTS 9301619104 9893180320 2564813 9986548521 9301515654 9564125368 9301256487 2664856 5487446 9301654854

SID 01 02

CNAME Disha college Disha college

DEPT Computer Science Computer Science

DOJ 01-JUL-00 01-JUL-02

03

Disha college

Computer Science

01-JUL-03

04

Daga college

Mathematics

01-AUG-03

05

Durga college

Mathematics

01-SEP-04

06 07 08

Palloti college Disha college RIT

Maths, Electronics Mathematics Electronics

01-OCT-05 01-NOV-05 10-NOV-05

09

Mats college

Maths, Electronics

01-DEC-05

10

Chanakya college

Electronics

07-DEC-06

POST HOD Professor Assistant Professor Professor Assistant Professor Professor Professor Professor Assistant Professor Assistant Professor

SALARY 100000 .00 50000.00 25000.00 50000.00 25000.00 50000.00 50000.00 50000.00 25000.00 25000.00

10 rows inserted SID 01 02 03 04 05 06 07 08 09 10

CLASS BCA-1 BCA-2 BCA-3 BE-1 BE-2 BE-3 BE-4 BSC-1 BSC-2 BSC-3

PAPERID BCA-101 BCA-201 BCA-301 BE-101 BE-201 BE-301 BE-401 BSC-101 BSC-201 BSC-301

FSESSION 01-JUL-00 01-JUL-02 01-JUL-03 01-AUG-03 01-SEP-04 01-OCT-05 01-NOV-05 10-NOV-05 01-DEC-05 07-DEC-06

INSERT INTO Staffs VALUES ( ‘&sid’, ‘&sname’, ‘&saddress’, ‘&contacts’, );

5

TSESSION 01-MAR-01 01-MAR-03 01-MAR-04 03-MAR-04 01-MAR-05 01-MAR-06 01-MAR-06 01-MAR-06 01-MAR-07 01-DEC-07

INSERT INTO StaffJoins VALUES ( ‘&sid’, ‘&cname’, ‘&dept’, ‘&DOJ’, ‘&post’ ‘&salary’ );

INSERT INTO Teachings VALUES ( ‘&sid’, ‘&class’, ‘&paperid’, ‘&fsession’, ‘&tsession’ ); PAPERID BCA-101 BCA-201 BCA-301 BE-101 BE-201 BE-301 BE-401 BSC-101 BSC-201

SUBJECT COMPUTER COMPUTER COMPUTER MATHS MATHS MATHS MATHS ELECTRONICS MATHS

PAPERNO 01 03 02 01 02 03 04 08 07

6

PAPERNAME VISUAL BASIC DBMS COM-DCOM CALCULUS DISCRETE MATHS NUMARICAL ANALYSIS CALCULUS FUDA. OF ELECTRONICS CALCULUS

BSC-301

PHYSICS

06

PHYSICS

SNAME Mandeep Suyash Bhawana

SNAME Mandeep Suyash Bhawana Neha

SADDRESS Raipur Raipur Raipur Raipur

7

SNAME Mandeep Suyash Bhawana Neha

SADDRESS Raipur Raipur Raipur Raipur

INSERT INTO Subjects VALUES ( ‘&paperid’, ‘&subject’, ‘&paperno’, ‘&papername’ );

iii. List the names of teachers teaching computer subjects. SELECT Staffs.sname FROM Staffs, Teachings, Subjects WHERE Subjects.paperid=Teachings.paperid AND Teachings.sid=Staffs.sid AND Subjects.subject IN ( 'VISUAL BASIC' , 'DBMS' , 'COMDCOM' ) ;

iv. List the names and cities of all staff working in your college.

SELECT Staffs.sname, Staffs.saddress FROM Colleges, Staffs, StaffJoins WHERE StaffJoins.sid = Staffs.sid AND Colleges.cname='Disha college' AND Colleges.cname=StaffJoins.cname;

8

v. List the names and cities of all staff working in your college who earn more than 15,000. SELECT Staffs.sname, Staffs.saddress FROM Staffs, StaffJoins WHERE Staffs.sid=StaffJoins.sid AND StaffJoins.cname='Disha college' AND StaffJoins.salary>15000; SID 01 03 09

SID 06 07 08 09

SNAME Mandeep Bhawana Vandana

SNAME Manish Neha Jaya Vandana

SADDRESS Raipur Raipur Bhilai

SADDRESS Abhanpur Raipur Bhilai Bhilai

CONTACTS 9301619104 2564813 5487446

CONTACTS 9564125368 9301256487 2664856 5487446

1 row updated

SUBJECT

9

DOJ 01-OCT-05 01-NOV-05 10-NOV-05 01-DEC-05

VISUAL BASIC

CLASS BCA-2 BCA-3 BE-1 BE-2 BE-3 BSC-1 BSC-2 BSC-3 BE-4

vi. Find the staffs whose names start with ‘M’ or ‘R’ and ends

with ‘A’ and/or 7 characters long. SELECT * FROM Staffs WHERE (sname LIKE 'M%A' OR sname LIKE 'R%A' AND sname LIKE '???????') OR (sname LIKE '???????’);

vii.

Find the staffs whose date of joining is 2005. SELECT Staffs.*, StaffJoins.DOJ FROM Staffs, StaffJoins WHERE StaffJoins.sid = Staffs.sid AND (StaffJoins.DOJ BETWEEN '01-jan-05' AND '31-dec-05') ;

10

viii. Modify the database so that staff N1 now works in C2

College. UPDATE StaffJoins SET cname = 'Durga college' WHERE sid = 101; ix. List the names of subjects, which T1 teaches in this session

or all sessions. SELECT Subjects.papername FROM Subjects, Teachings, Staffs WHERE Staffs.sid = Teachings.sid AND Teachings.paperid = Subjects.paperid AND Staffs.sname = 'Mandeep';

x. Find the classes that T1 do not teach at present session. SELECT class FROM Teachings WHERE class (SELECT class FROM Teachings WHERE sid = 101);

CNAME Disha college

SNAME Mandeep

11

CNAME Disha college Daga college Palloti college RIT

CNAME Durga college Mats college Chanakya college

CNAME Disha college Daga college Palloti college RIT

xi. Find the colleges who have most number of staffs. SELECT StaffJoins.cname FROM StaffJoins GROUP BY cname HAVING COUNT(sid)=(SELECT MAX(COUNT(sid)) FROM StaffJoins GROUP BY cname);

12

Find the staffs that earn a higher salary who earn greater than average salary of their college.

xii.

SELECT Staffs.sname FROM Staffs, StaffJoins WHERE Staffs.sid = StaffJoins.sid AND StaffJoins.cname = 'Disha college' AND salary>(SELECT AVG(salary) FROM StaffJoins WHERE StaffJoins.cname='Disha college'); xiii. Find the colleges whose average salary is more than

average salary of C2. SELECT DISTINCT(cname) FROM StaffJoins WHERE AVG(salary)>(SELECT AVG(salary) FROM StaffJoins WHERE cname=’Durga College’);

xiv.

Find the colleges that have the smallest payroll. SELECT cname FROM StaffJoins WHERE salary=(SELECT MIN(SUM(salary)) FROM StaffJoins GROUP BY cname);

xv. Find the colleges where the total salary is greater than the

average salary of all colleges. SELECT StaffJoins.cname FROM StaffJoins WHERE SUM(salary)>(SELECT AVG(salary) FROM StaffJoins);

CNAME Chanakya college Daga college Disha college Durga college Mats college Palloti college RIT

MAX(SALARY) 25000 50000 100000 25000 25000 50000 50000

AVG(SALARY) 25000 50000 56250 25000 25000 50000 50000

13

MIN(SALARY) 25000 50000 25000 25000 25000 50000 50000

No Rows Selected

SID SNAME SADDRESS CONTACTS CNAME 01 Mandeep Raipur 9301619104 Disha college 02 Suyash Raipur 9893180320 Disha college 03 Bhawana Raipur

2564813

04 Bhoj

Raigarh

986548521

05 Vishal

Rajnandgaon 9301515654

06 Manish 07 Neha 08 Jaya

Abhanpur Raipur Bhilai

09 Vandana Bhilai 10 Rajesh

9564125368 9301256487 2664856 5487446

Baloda Bazaar9301654854

DEPT DOJ POST SALARY Computer Science 01-JUL-00 HOD 100000 Computer Science 01-JUL-02 Professor 50000 Assistant Disha college Computer Science 01-JUL-03 25000 Professor Daga college Mathematics 01-AUG-03 Professor 50000 Assistant Durga college Mathematics 01-SEP-04 25000 Professor Palloti college Maths, Electronics 01-OCT-05 Professor 50000 Disha college Mathematics 01-NOV-05 Professor 50000 RIT Electronics 10-NOV-05 Professor 50000 Assistant Mats college Maths, Electronics 01-DEC-05 25000 Professor Assistant Chanakya college Electronics 07-DEC-06 25000 Professor

SNAME Mandeep

14

Suyash Bhoj Manish Neha Jaya

xvi.

List maximum, average , minimum salary of each college. SELECT cname, MAX(salary), AVG(salary), MIN(salary) FROM StaffJoins GROUP BY cname;

xvii. List the names of the teachers, departments teaching in

more than one department. SELECT Staffs.sname, StaffJoins.dept FROM Staffs, StaffJoins WHERE Staffs.sid=StaffJoins.sid

xviii. Acquire details of staffs by name in a college or each

college. SELECT Staffs.*, StaffJoins.cname, StaffJoins.dept, StaffJoins.DOJ, StaffJoins.post, StaffJoins.salary FROM Staffs, StaffJoins WHERE Staffs.sid = StaffJoins.sid;

15

Find the names of staff that earn more than each staff of C2 college.

xix.

SELECT Staffs.sname FROM Staffs, StaffJoins WHERE Staffs.sid = StaffJoins.sid AND salary>(SELECT MAX(salary) FROM StaffJoins WHERE cname = 'Durga college'); SNAME Bhoj Vishal Manish Jaya Vandana Rajesh

SNAME Neha Bhawana Suyash Mandeep

Name CNAME SNAME DEPT DOJ POST

VView

Name CNAME

Null?

Type VARCHAR2(20) VARCHAR2(20) VARCHAR2(30) DATE NUMBER(7,2)

Created Null?

Type VARCHAR2(20)

16

SALARY

NUMBER(7,2)

View Created

xx.Find all staff that does not work in same cities as the colleges

they work. SELECT Staffs.sname FROM Staffs, StaffJoins WHERE Staffs.sid=StaffJoins.sid AND StaffJoins.cname='DISHA COLLEGE' ORDER BY StaffJoins.salary;

List names of employees in ascending order according to salary who are working in your college or all colleges.

xxi.

SELECT sname FROM Staffs, StaffJoins WHERE Staffs.sid=StaffJoins.sid AND StaffJoins.cname='Disha college' ORDER BY StaffJoins.salary; xxii. Create a view having field cname, sname, dept, DOJ and

post. CREATE VIEW StaffDetail1 AS SELECT StaffJoins.cname, Staffs.sname, StaffJoins.dept, StaffJoins.DOJ, StaffJoins.post FROM StaffJoins, Staffs WHERE StaffJoins.sid = Staffs.sid;

17

xxiii. Create a view consisting of cname, average salary and

total salary of staff in that college. CREATE VIEW StaffDetail2 AS SELECT StaffJoins.cname, AVG(salary) "salary" FROM StaffJoins GROUP BY cname;

Name ENROLLNO NAME GENDER DOB ADDRESS PHONE

Null? NOT NULL

Type VARCHAR2(8) VARCHAR2(10) VARCHAR2(1) DATE VARCHAR2(10) NUMBER(10)

18

Name ADMNO ENROLLNO COURSE YEARSEM DATE CNAME

Null? NOT NULL

Type NUMBER(4) VARCHAR2(8) VARCHAR2(5) VARCHAR2(10) DATE VARCHAR2(20)

xxiv. Select the colleges having highest and lowest average

salary using above views. SELECT cname FROM StaffDetail2 WHERE salary= MAX(salary) AND salary=MIN(salary) xxv. List the staff names of a department using above views. SELECT sname, dept FROM StaffDetail1 2.

i.

Create the following databases: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) Create the above tables with given specifications and constraints. 19

CREATE TABLE Enrollment ( enrollno varchar2(8) name varchar2(10), gender varchar2(1), DOB date, address varchar2(10), phone number(10) );DESC Enrollment;

primary key,

CREATE TABLE Admissions ( admno number(4) enrollno varchar2(8) course varchar2(5), yearsem varchar2(10), date date, cname varchar2(20) ); DESC Admissions; Name CNAME CITY ADDRESS PHONE AFDATE

Null? NOT NULL

Name COURSE YEARSEM FEES

primary key, references Enrollment,

references Colleges

Type VARCHAR2(20) VARCHAR2(10) VARCHAR2(20) NUMBER(10) DATE

Null?

Type

NOT NULL

VARCHAR2(5) VARCHAR2(10) NUMBER(5,2)

20

Name BILLNO ADMNO AMOUNT PDATE PURPOSE

ENROLLNO UU/20001 UU/20002 UU/20003 UU/20004 UU/20005 UU/20006 UU/20007 UU/20008 UU/20009 UU/20010

Null? NOT NULL

NAME Anshul Bhawna Chetna Dithi Elisha Farishta Gitika Hritik Ishita Janhavi

Type NUMBER(10) NUMBER(4) NUMBER(5,2) DATE VARCHAR2(10)

GENDER DOB M 01-JAN-93 F 02-FEB-93 F 03-MAR-93 F 04-APR-93 F 05-MAY-93 F 06-JUN-93 F 07-JUL-93 M 08-AUG-93 F 09-SEP-93 F 10-OCT-93

ADDRESS Raipur Bhilai Bilaspur Kawardha Chirmiri Ambikapur Raipur Ambikapur Kawardha Chirmiri

CREATE TABLE Colleges ( cname varchar2(20) city varchar2(10), address varchar2(20), phone number(10), afdate date ); DESC Collesges;

primary key,

21

PHONE 9893452616 9976543287 9876789543 9826123457 8827321546 7887656454 9898989898 7878787878 8899776655 9998979695

CREATE TABLE FeeStructure ( course varchar2(5), yearsem varchar2(10), fee number(5,2) ); DESC FeeStructure; CREATE TABLE Payment ( billno number(10) admno number(4) amount number(5,2), pdate date, purpose varchar2(10) ); DESC Paymwnt; ii.

primary key, references Admissions,

Insert about 10 rows as are appropriate to solve the following queries. INSERT INTO Enrollment VALUES ( ‘&enrollno’, ‘&name’, ‘&gender’, ‘&DOB’, ‘&address’, ‘&phone’ );

ADMNO 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010

ENROLLNO COURSE UU/20001 BCA-I UU/20002 BSC-I UU/20003 BCOM-I UU/20004 BBA-I UU/20005 PGDCA-I UU/20006 BCA-II UU/20007 BSC-II UU/20008 BCOM-II UU/20009 BBA-II UU/20010 PGDCA-II

YEARSEM DATE 2005-06 02-AUG-87 2005-06 04-AUG-88 2006-07 02-MAR-88 2006-07 02-AUG-89 2007-08 02-FEB-88 2007-08 20-NOV-87 2008-09 02-AUG-87 2008-09 04-AUG-88 2009-10 02-MAR-88 2009-10 02-AUG-89

22

CNAME Disha college Durga college Daga college Central college Chanakya college RIT Disha college Durga college Daga college Central college

CNAME Disha college Durga college Daga college Central college Chanakya college Palloti college Harishankar college Mats college Degree Girls college RIT

CITY Raipur Raipur Bhilai Raipur Bhilai Raipur Raipur Raipur Bhilai Raipur

ADDRESS Ram Nagar, Kota K.K Road Civil Lines Fafadih G.E Road Pandri Shankar Nagar Pandri G.E Road G.E Road

COURSE BCA-I BSC-I BCOM-I BBA-I PGDCA-I BCA-II BSC-II BCOM-II BBA-II PGDCA-II BCA-I

YEARSEM 2005-06 2005-06 2006-07 2006-07 2007-08 2007-08 2008-09 2008-09 2009-10 2009-10 2005-06

PHONE 4205553 4202253 4658457 9654821565 6548325 9853254185 2565486 4596658 6583245 4598544

AFDATE 30-JUN-00 30-JAN-75 30-AUG-95 15-MAY-04 02-JUL-03 03-SEP-90 04-OCT-65 05-NOV-04 06-DEC-92 07-FEB-96 FEE 31000 .00 30000.00 30000.00 21000.00 20000.00 20000.00 21000.00 20000.00 20000.00 25000.00 10000.00

INSERT INTO Admission VALUES ( ‘&admno’, ‘&enrollno’, ‘&course’, ‘&yearsem’, ‘&date’, ‘&cname’ );

INSERT INTO Colleges VALUES ( ‘&cname’, ‘&city’, 23

‘&address’, ‘&phone’, ‘&afdate’ );

INSERT INTO FeeStructure VALUES ( ‘&course’, ‘&yearsem’, ‘&fee’ );

BILLNO 1 2 3 4 5 6 7 8 9 10

ADMNO 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010

AMOUNT PDATE 31000.00 21-JUL-07 21000.00 21-JUN-07 21000.00 21-AUG-07 25000.00 21-SEP-07 10000.00 01-JUL-07 30000.00 23-OCT-07 20000.00 21-JUL-07 20000.00 21-JUN-07 25000.00 21-AUG-07 10000.00 21-SEP-07

24

PURPOSE

ADMNO 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010

ENROLLNO COURSE UU/20001 BCA-I UU/20002 BSC-I UU/20003 BCOM-I UU/20004 BBA-I UU/20005 PGDCA-I UU/20006 BCA-II UU/20007 BSC-II UU/20008 BCOM-II UU/20009 BBA-II UU/20010 PGDCA-II

YEARSEM DATE 2005-06 02-AUG-87 2005-06 04-AUG-88 2006-07 02-MAR-88 2006-07 02-AUG-89 2007-08 02-FEB-88 2007-08 20-NOV-87 2008-09 02-AUG-87 2008-09 04-AUG-88 2009-10 02-MAR-88 2009-10 02-AUG-89

CNAME Disha college Durga college Daga college Central college Chanakya college RIT Disha college Durga college Daga college Central college

ADMNO 1003 1005

ENROLLNO COURSE UU/20003 BCOM UU/20005 PGDCA

YEARSEM DATE I-year 02-MAR-88 I-semester 02-FEB-88

CNAME Daga college Chanakya college

TOTAL AMOUNT

25

52000 .00

INSERT INTO Payment VALUES ( ‘&billno’, ‘&admno’, ‘&amount’, ‘&pdate’, ‘&purpose’ );

iii.

Get full details of all students who took admission this year class wise. SELECT * FROM Admissions WHERE date BETWEEN '31-dec-10' AND '01-jan-12' ORDER BY course;

iv.

Get details of students who took admission in Bhilai colleges. SELECT * FROM Colleges, Admissions WHERE Colleges.cname=Admission.cname AND Colleges.city= ‘Bhilai’;