Ucer Lab Manual DBMS

Download as pdf or txt
Download as pdf or txt
You are on page 1of 10

DBMS LAB

KCS - 551
DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING
AND
INFORMATION TECHNOLOGY

United College of Engineering and Research, Naini,


Allahabad
(Uttar Pradesh Technical University, Lucknow)

D-2, Industrial Area, Naini, Allahabad-211010


(0532-2101633, 2687669)

Website: www.united.ac.in

LAB CODE: KCS-551 Page 1


SYLLABUS FOR DBMS LAB (KCS-551)
1. Installing oracle/ MYSQL

2. Creating Entity-Relationship Diagram using case tools.

3. Writing SQL statements Using ORACLE /MYSQL:

a)Writing basic SQL SELECT statements.

b) Restricting and sorting data.

c)Displaying data from multiple tables.

d)Aggregating data using group function.

e)Manipulating data.

e)Creating and managing tables.

4. Normalization

5. Creating cursor

6. Creating procedure and functions

7. Creating packages and triggers

8. Design and implementation of payroll processing system

9. Design and implementation of Library Information System

10. Design and implementation of Student Information System

11. Automatic Backup of Files and Recovery of Files \

12. Mini project (Design & Development of Data and Application ) for following :

a) Inventory Control System.

b) Material Requirement Processing.

c) Hospital Management System.

d) Railway Reservation System.

e) Personal Information System.

f) Web Based User Identification System.

g) Timetable Management System. h) Hotel Management System

LAB CODE: KCS-551 Page 2


Objective: To attain expertise in lab equipment handling and
understanding the basic devices, their properties Characteristics in detail.
Along with their practical usage in the circuit.

1. Installing oracle/MySQL.

2. Creating Entity-Relationship Diagram using case tools..

3. Writing SQL statements Using ORACLE /MYSQL:

a. Writing basic SQL SELECT statements.


b. Restricting and sorting data.
c. Displaying data from multiple tables.
d. Aggregating data using group function.
e. Manipulating data.
f. Creating and managing tables.

4. Normalization in ORACLE.

5. Creating cursor in oracle.

6. Creating procedure and functions in oracle.

7. Creating packages and triggers in oracle.

LAB CODE: KCS-551 Page 3


STUDY AND EVALUATION SCHEME OF KCS-551
LABORATORY

Total No. Of Periods : 2 Periods per week

INTERNAL EVALUATION:
CLASS TEST : 10 MARKS

TEACHER’S ASSESMENT : 10 MARKS

TOTAL INTERNAL MARKS: 20 MARKS

END SEMESTER EXAM : 30 MARKS

TOTAL MARKS : 50 MARKS


CREDIT : 1

LAB CODE: KCS-551 Page 4


PROGRAM NO-1

OBJECTIVE- Create database. Database have tables Programmers, Studies, software


Schema of relations are as follows:
Table Name: Programmers

Name Null Data Type Description


NAME NOT NULL VARCHAR2(8) Name
DOB NOT NULL DATE Date of Birth
DOJ NOT NULL DATE Date of Joining
SEX NOT NULL VARCHAR2(1) Male or Female
PROF1 VARCHAR2(8) Language 1
PROF2 VARCHAR2(8) Language 2
SALARY NOT NULL NUMBER(4) Salary

Table Name : Studies

Name Null Data Type Description


NAME NOT NULL VARCHAR2(8) Name
SPLACE NOT NULL VARCHAR2(9) Studies Place
COURSE NOT NULL VARCHAR2(5) Course Studies
CCOST NOT NULL NUMBER(5) Course Cost

Table Name : Software

Name Null Data Type Description


NAME NOT NULL VARCHAR2(8) Name
TITLE NOT NULL VARCHAR2(20) Developed Project
Name
DEV_IN NOT NULL VARCHAR2(8) Language Developed
SCOST NUMBER(7,2) Software Cost
DCOST NUMBER(5) Development Cost
SOLD NUMBER(3) No. Of Software Sold

LAB CODE: KCS-551 Page 5


PROGRAM NO-2

OBJECTIVE- Insert the data in all tables of database as given.


Data in Table : Programmers

NAME DOB DOJ SEX PROF1 PROF2 SALARY


Anand 21-Apr-66 21-Apr-92 M Pascal Basic 3200
Altaf 02-Jul-64 13-Nov-90 M Clipper Cobol 2800
Jagadesh 06-Oct-70 04-Oct-94 M Oracle Java 4100
Juliana 31-Jan-68 21-Apr-90 F Cobol Dbase 3000
Kamala 30-Oct-68 02-Jan-92 F C Dbase 2900
Mary 24-Jun-70 01-Feb-91 F C++ Oracle 4500
Nelson 11-Sep-65 11-Oct-89 M Cobol Dbase 2500
Partick 19-Nov-65 21-Apr-90 M Pascal Clipper 2800
Qadir 31-Aug-65 21-Apr-93 M Assembly C 3000
Ramesh 03-May-67 28-Feb-91 M Pascal Dbase 3200
Rebecca 01-Jan-67 01-Dec-90 F Basic Cobol 2500
Remitha 19-Apr-70 20-Apr-93 F C Assembly 3600
Revathi 02-Dec-69 02-Jan-92 F Pascal Basic 3700
Vijaya 14-Dec-65 02-May-92 F Foxpro C 3500

Data in Table : Studies

NAME SPACE COURSE CCOST


Anand SDBT Pgdca 4500
Altaf SSIL Dca 7200
Jagadesh SSIL Dca 3500
Juliana BITS Dca 22000
Kamala SAKTHIDBTECH Dcp 5000
Mary SDBT Pgdca 4500
Nelson SAKTHIDBTECH Dap 6200
Partick SAKTHIDBTECH Dcap 5200
Qadir APPLE Hdcp 14000
Ramesh SDBT Pgdca 4500
Rebecca SSS Dca&p 11000
Remitha MICROSOFT Dcs 6000
Revathi SDBT Dap 5000
Vijaya MICROSOFT Dca 48000

LAB CODE: KCS-551 Page 6


Data in Table : Software

Name Title Dev_in Scost Dcost Sold


ANAND PARASHUTES BASIC 399.95 6000 43
ANAND VIDEO THINKING PACK PASCAL 7500.00 16000 9
JAGADESH SERIAL LINK UTILITY JAVA 800.00 7500 10
JAGADESH SHARES MANAGEMENT ORACLE 3000.00 12000 14
JULIANA INVENTORY CONTROL COBOL 3000.00 3500 0
KAMALA PARCEL PACKAGE DBASE 9000.00 20000 7
MARY FINANCIAL ACC S/W ORACLE 18000.00 85000 4
MARY CODE GENERATOR C 4500.00 20000 23
MARY READ ME C++ 300.00 1200 84
PARTICK GRAPHIC EDITOR PASCAL 750.00 5000 11
QADIR BOMBS AWAY ASSEMBLY 499.95 530 114
QADIR VACCINES C 1900.00 3400 21
RAMESH HOTEL MANAGEMENT DBASE 12000.00 35000 4
RAMESH DEAD LEE PASCAL 99.95 4500 73
REMITTA PC UTILITIES C 725.00 5000 51
REMITTA TSR HELP PACKAGE ASSEMBLY 2500.00 900 6
REVATHI HOSPITAL MANAGEMENT PASCAL 1100.00 75000 2
REVATHI QUIZ MASTER BASIC 3200.00 2100 15
VIJAYA ISK EDITOR C 900.00 700 6

LAB CODE: KCS-551 Page 7


PROGRAM NO-3

OBJECTIVE- Write SQL statement for the following queries.


Queries – I
a. Find out the SIMPLE COST AVERAGE FOR package developed in PASCAL.
b. Display the NAMES and AGES of all the programmers.
c. Display the NAMES of those who have done the DAP course.
d. When the HIGHEST number of copies sold by a package.
e. Display the NAMES and DATE OF BIRTH of all programmers born in JANUARY
f. Display the LOWEST course fee.
g. How many programmers have done the PGDCA course.
h. How much revenue has been earned through sale of packages developed in C.
i. Display the details of the SOFTWARE developed by RAMESH.
j. How many programmers studies at SDBT.
k. Display the details of PACKAGES whose sales CROSSED the 20000 mark.
l. Find out the NUMBER OF COPIES, which should be sold in order to recover the
DEVELOPMENT COST of each package.
m. Display the detail of packages for WHICH development cost has been recorded.
n. What is the price of the costliest software developed in BASIC? How many packages were
developed in DBASE.
o. How many programmers studies in SAKTHIDBTECH.
p. How many programmers paid 5000 to 10000 for their COURSE. What is the AVERAGE
course fee?
q. Display the DETAILS of programmers knowing C.
r. How many programmers know either COBOL or PASCAL.
s. How many programmers DON’T know PASCAL & C.
t. How old is the OLDEST male programmer.
u. What is the AVERAGE age of female programmers.
v. CALCULATE the experience in years for each programmers and display along with the
names, in DESCENDING order.
w. How many female programmers are there.
x. What are the languages known by the male programmers.
y. What is the Average salary?
z. How many people draw 2000 to 4000.
aa. Display the details of those WHO DON’T know CLIPPER, COBOL or PASCAL.
bb. How many FEMALE programmers knowing C are above 24 years of age.
cc. Who are the programmers who will be celebrating their Birthdays within a WEEK?
dd. Display the details of those with LESS than a year EXPERIENCE.

LAB CODE: KCS-551 Page 8


ee. Display the details of those who will be COMPLETING 2 years of service this YEAR.
ff. CALCULATE the amount TO BE recovered for those package WHOSE development cost
has not yet been recovered.
gg. List the packages, which have not been sold so far.
hh. Find out the COST of the software developed by MARY.
ii. Display the institute names FROM the Studies table WITH OUT DUPLICATES.
jj. How many different courses are mentioned in the studies table.
kk. Display the names of the programmers whose names contain 2 OCCURENCES of the
letter ’A’.
ll. Display the names of the programmers WHOSE names contain UPTO 5 characters.
mm. How many female programmers knowing COBOL have more than 2 years Experience.
nn. What is the LENGTH of the shortest name in the programmer table?
oo. What is the AVERAGE development cost of a package developed in COBOL?
pp. Display the name, sex, DOB (DD/MM/YY format), DOJ (DD/MM/YY format) for all the
programmers WITHOUT using conversion function.
qq. What is the amount paid in salaries of the males programmers WHO DON’T know
COBOL?
rr. Who are the programmers WHO WERE BORN on the LAST DAY of the MONTH.
ss. Display the title, SCOST, DCOST and DIFFERENCE between SCOST and DCOST in
DESCENDING or of DIFFERENCE.
tt. Display the names of the packages WHOSE names contain MORE THAN 1 word.
uu. Display the name, job of THOSE MONTH of BIRTH and MONTH of JOINING are the
SAME.

LAB CODE: KCS-551 Page 9


PROGRAM NO-4

OBJECTIVE- Write SQL statement for the following queries.


1. Display THE NUMBER OF packages developed IN EACH language.
2. Display THE NUMBER OF packages developed by EACH person.
3. Display THE NUMBER OF male and female programmers.
4. Display THE COSTLIEST package and HIGHEST SEELING.
5. Display THE NUMBER Of people BORN in EACH YEAR.
6. Display THE NUMBER OF people JOINED in EACH YEAR.
7. Display THE NUMBER OF people BORN in EACH YEAR.
8. Display THE NUMBER OF people JOINED in EACH MONTH.
9. Displays the language wise COUNT of prof1.
10. Displays the language wise COUNT of prof2.
11. Display THE NUMBER OF people in EACH salary group.
12. Display THE NUMBER OF people WHO studies in EACH institute.
13. Display THE NUMBER OF people WHO studies in EACH course.
14. Display the TOTAL development COST of the packages developed in EACH language.
15. Display the selling cost of the packages developed in EACH language.
16. Display the cost of the package developed EACH programmer.
17. Display the sales values of the packages developed by EACH programmer.
18. Display THE NUMBER of packages sold by EACH programmer.
19. Display the sales COST of the packages developed by EACH programmer language wise.
20. Display EACH programmer’s name, costliest package & CHEAPEST packages
developed by him/her.
21. Display EACH language name with AVERAGE development cost, AVERAGE selling
cost and AVERAGE price per copy.
22. Display EACH institute name with number of courses, AVERAGE cost per course.
23. Display EACH institute name with NUMBER of students.
24. Display the names of male and female programmers.
25. Display the programmer’s name and their packages.
26. Display the NUMBER of packages in EACH language EXCEPT C & C++.
27. Display the NUMBER of PACKAGES in EACH language for WHICH development
COST is LESS than 1000.
28. Display the AVERAGE DIFFERENCE BETWEEN SCOST and DCOST for EACH
language.
29. Display the TOTAL SCOST and DCOST and amount to be recovered for EACH
programmer for those WHOSE DCOST has NOT YET BEEN recovered.
30. Display HIGHEST, LOWEST and AVERAGE salaries for THOSE earning MORE than
2000.

LAB CODE: KCS-551 Page 10

You might also like