DBMS Lab Manual
Sree ChaitanayaEngineering College
Department of Computer Science Engineering
Database Management Systems
Lab Manual [CS 05157]
II B Tech – I Semester [Branch:CSE]
INSTRUCTORS:Mr. Rishi Sayal, HOD
Mr. Saidulu, Asst. Prof.
Document NO:GNEC/CSE/LAB MANUAL/DBMS / Date of Issue: / Compiled by / Authorized by
Date of revision / Verified by
PROGRAMMER:jaipal
INDEX
S.No / Contents / Page. no1 / Lab Objective / 3
2 / Introduction About Lab / 4
3 / Standard Operating Procedure – SOP / 6
3 / Guidelines to Students / 7
4 / Description about SQL, PL/SQL statements and D2K. / 8
5 / List of Lab Exercises
4.1 Syllabus Programs (JNTU)
4.2 Additional Programs / 9
6 / Background Theory / 14
7 / Solutions for Programs / 20
8 / PL-Sql Programs / 50
9 / Additional Viva Question / 88
10 / References / 94
LAB OBJECTIVE
Upon successful completion of this Lab the student will be able to:
Creating database objects
Modifying database objects
Manipulating the data
Retrieving the data from the database server
Performing database operations in a procedural manner using pl/sql
Performing database operations (create, update, modify, retrieve, etc.,) using front-end tools like D2K.
Design and Develop applications like banking, reservation system, etc.,
INTRODUCTION ABOUT LAB
There are 66 systems ( Compaq Presario ) installed in this Lab. Their configurations are as follows :
Processor:AMD Athelon ™ 1.67 GHz
RAM:256 MB
Hard Disk:40 GB
Mouse:Optical Mouse
Network Interface card:Present
Software
All systems are configured in DUAL BOOT mode i.e., Students can boot from Windows XP or Linux as per their lab requirement. This is very useful for students because they are familiar with different Operating Systems so that they can execute their programs in different programming environments.
Each student has a separate login for database access
Oracle 9i client version is installed in all systems. On the server, account for each student has been created.
This is very useful because students can save their work(scenarios’, pl/sql programs, data related projects,etc) in their own accounts. Each student work is safe and secure from other students.
Latest Technologies like DOT NET and J2EE are installed in some systems. Before submitting their final project, they can start doing mini project from 2nd year onwards.
MASM (Macro Assembler) is installed in all the systems
Students can execute their assembly language programs using MASM. MASM is very useful students because when they execute their programs they can see contents of Processor Registers and how each instruction is being executed in the CPU.
Rational Rose Software is installed in some systems
Using this software, students can depict UML diagrams of their projects.
Softwares installed : C, C++, JDK1.5, MASM, OFFICE-XP, J2EE and DOT NET, Rational Rose.
Systems are provided for students in the 1:1 ratio.
Systems are assigned numbers and same system is allotted for students when they do the lab.
STANDARD OPERATING PROCEDURE – SOP
a) Explanation on today’s experiment by the concerned faculty using OHP/PPT covering the following aspects: 25 mins.
1)Name of the experiment/Aim
2)Software/Hardware required
3)Algorithm
4)Test Data
1)Valid data sets
2)Limiting value sets
3)Invalid data sets
b) Writing of source program by the students25 min.
c) Compiling and execution of the program 100 mins.
Writing of the experiment in the Observation Book:
The students will write the today’s experiment in the Observation book as per the following format:
a)Name of the experiment/Aim
b)Software/Hardware required
c)Algorithm
d)Source Program
e)Test Data
- Valid data sets
- Limiting value sets
- Invalid data sets
f)Results for different data sets
g)Viva-Voc Questions and Answers
h)Errors observed (if any) during compilation/execution
i)Signature of the Faculty
Guidelines to Students
Equipment in the lab for the use of student community. Students need to maintain a proper decorum in the computer lab. Students must use the equipment with care. Any damage is caused is punishable.
Students are required to carry their observation / programs book with completed exercises while entering the lab.
Students are supposed to occupy the machines allotted to them and are not supposed to talk or make noise in the lab. The allocation is put up on the lab notice board.
Lab can be used in free time / lunch hours by the students who need to use the systems should take prior permission from the lab in-charge.
Lab records need to be submitted on or before date of submission.
Students are not supposed to use floppy disks
How to Write and executesql, pl/sql commands/programs:
1). Open your oracle application by the following navigation
Start->all programs->oracle orahome.->application
development->sql.
2). You will be asked for user name, pass word and host string
You have to enter user name, pass word and host string as given
by the administrator. It will be different from one user to another user.
3). Upon successful login you will get SQL prompt (SQL>).
In two ways you can write your programs:
a). directly at SQL prompt
b). or in sql editor.
If you type your programs at sql prompt then screen will look like follow:
SQL> SELECT ename,empno,
2sal from
3emp;
where 2 and 3 are the line numbers and rest is the command/program……
to execute above program/command you have to press ‘/’ then enter.
Here editing the program is somewhat difficult; if you want to edit the previous command then you have to open sql editor (by default it displays the sql buffer contents). By giving ‘ed’ at sql prompt.(this is what I mentioned as a second method to type/enter the program).
in the sql editor you can do all the formatting/editing/file operations directly by selecting menu options provided by it.
To execute the program which saved; do the following
SQL> @ programname.sql
Or
SQL> Run programname.sql
Then press ‘\’ key and enter.
This how we can write, edit and execute the sql command and programs.
Always you have to save your programs in your own logins.
List of Lab Exercises
Syllabus Programs (JNTU)
S.No / Name of the program1 / Database Schema for a customer-sale scenario
Customer(Cust id : integer, cust_name: string)
Item(item_id: integer, item_name: string, price: integer)
Sale(bill_no: integer, bill_data: date, cust_id: integer, item_id: integer, qty_sold: integer)
For the above schema, perform the following—
a)Create the tables with the appropriate integrity constraints
b)Insert around 10 records in each of the tables
c)List all the bills for the current date with the customer names and item numbers
d)List the total Bill details with the quantity sold, price of the item and the final amount
e)List the details of the customer who have bought a product which has a price>200
f)Give a count of how many products have been bought by each customer
g)Give a list of products bought by a customer having cust_id as 5
h)List the item details which are sold as of today
i)Create a view which lists out the bill_no, bill_date, cust_id, item_id, price, qty_sold, amount
Create a view which lists the daily sales date wise for the last one week
2 / Database Schema for a Student Library scenario
Student(Stud_no : integer,Stud_name: string)
Membership(Mem_no: integer,Stud_no: integer)
Book(book_no: integer, book_name:string, author: string)
Iss_rec(iss_no:integer, iss_date: date, Mem_no: integer, book_no: integer)
For the above schema, perform the following—
a)Create the tables with the appropriate integrity constraints
b)Insert around 10 records in each of the tables
c)List all the student names with their membership numbers
d)List all the issues for the current date with student and Book names
e)List the details of students who borrowed book whose author is CJDATE
f)Give a count of how many books have been bought by each student
g)Give a list of books taken by student with stud_no as 5
h)List the book details which are issued as of today
i)Create a view which lists out the iss_no, iss _date, stud_name, book name
j)Create a view which lists the daily issues-date wise for the last one week
3 / Database Schema for a Employee-pay scenario
employee(emp_id : integer, emp_name: string)
department(dept_id: integer,dept_name:string)
paydetails(emp_id : integer, dept_id: integer, basic: integer, deductions: integer, additions: integer, DOJ: date)
payroll(emp_id : integer, pay_date: date)
For the above schema, perform the following—
a)Create the tables with the appropriate integrity constraints
b)Insert around 10 records in each of the tables
c)List the employee details department wise
d)List all the employee names who joined after particular date
e)List the details of employees whose basic salary is between 10,000 and 20,000
f)Give a count of how many employees are working in each department
g)Give a names of the employees whose netsalary>10,000
h)List the details for an employee_id=5
i)Create a view which lists out the emp_name, department, basic, dedeuctions, netsalary
j)Create a view which lists the emp_name and his netsalary
4 / Database Schema for a Video Library scenario
Customer(cust_no: integer,cust_name: string)
Membership(Mem_no: integer, cust_no: integer)
Cassette(cass_no:integer, cass_name:string, Language: String)
Iss_rec(iss_no: integer, iss_date: date, mem_no: integer, cass_no: integer)
For the above schema, perform the following—
a)Create the tables with the appropriate integrity constraints
b)Insert around 10 records in each of the tables
c)List all the customer names with their membership numbers
d)List all the issues for the current date with the customer names and cassette names
e)List the details of the customer who has borrowed the cassette whose title is “ The Legend”
f)Give a count of how many cassettes have been borrowed by each customer
g)Give a list of book which has been taken by the student with mem_no as 5
h)List the cassettes issues for today
i)Create a view which lists outs the iss_no, iss_date, cust_name, cass_name
j)Create a view which lists issues-date wise for the last one week
5 / Database Schema for a student-Lab scenario
Student(stud_no: integer, stud_name: string, class: string)
Class(class: string,descrip: string)
Lab(mach_no: integer, Lab_no: integer, description: String)
Allotment(Stud_no: Integer, mach_no: integer, dayof week: string)
For the above schema, perform the following—
a)Create the tables with the appropriate integrity constraints
b)Insert around 10 records in each of the tables
c)List all the machine allotments with the student names, lab and machine numbers
d)List the total number of lab allotments day wise
e)Give a count of how many machines have been allocated to the ‘CSIT’ class
f)Give a machine allotment etails of the stud_no 5 with his personal and class details
g)Count for how many machines have been allocatedin Lab_no 1 for the day of the week as “Monday”
h)How many students class wise have allocated machines in the labs
i)Create a view which lists out the stud_no, stud_name, mach_no, lab_no, dayofweek
j)Create a view which lists the machine allotment details for “Thursday”.
6 / Write a program to find largest number from the given three numbers.
7 / Simple programs using loop, while and for iterative control statement.
8 / Write a program to check whether the given number is Armstrong or not
9 / Write a program to generate all prime numbers below 100.
10 / Write a program to demonstrate the GOTO statement.
11 / Write a program to demonstrate %type and %rowtype attributes
12 / Write a program to demonstrate predefined exceptions
13 / Write a program to demonstrate user defined exceptions
14 / Create a cursor, which displays all employee numbers and names from the EMP table.
15 / Create a cursor, which update the salaries of all employees as per the given data.
16 / Create a cursor, which displays names of employees having salary > 50000.
17 / Create a procedure to find reverse of a given number
18 / Create a procedure to update the salaries of all employees as per the given data
19 / Create a procedure to demonstrate IN, OUT and INOUT parameters
20 / Create a function to check whether given string is palindrome or not.
21 / Create a function to find sum of salaries of all employees working in depart number 10.
22 / Create a trigger before/after update on employee table for each row/statement.
23 / Create a trigger before/after delete on employee table for each row/statement.
24 / Create a trigger before/after insert on employee table for each row/statement.
25 / Create a Form to display employee details using SQL
26 / Create a Report to generate all employee annual salaries….
Additional Programs
S. No / Name of the Program1 / Create a form using Forms 6i to display Employee table data.
2 / Create a Master/details relationship form which perform Add New, Search, Delete, Save and Update on the records
3 / Generate a report to calculate employee’s salaries department wise from employee table.
4 / Create a Report to generate the details of employee table including sum and average salaries department wise.
Background Theory
Oracle workgroup or server is the largest selling RDBMS product.it is estimated that the combined sales of both these oracle database product account for aroud 80% of the RDBMSsystems sold worldwide.
These products are constantly undergoing change and evolving.The natural language of this RDBMS product is ANSI SQL,PL/SQL a superset of ANSI SQL.oracle 8i and 9i also under stand SQLJ.
Oracle corp has also incorporated a full-fledged java virtual machine into its database engine.since both executable share the same memory space the JVM can communicate With the database engine with ease and has direct access to oracle tables and their data.
SQL is structure query language.SQL contains different data types those are
- char(size)
- varchar2(size)
- date
- number(p,s)
- long
- raw/long raw
Different types of commands in SQL:
A).DDL commands:-To create a database objects
B).DML commands:-To manipulate data of a database objects
C).DQL command: -To retrieve the data from a database.
D).DCL/DTL commands:-To control the data of a database…
DDL commands:
1. The Create Table Command:- it defines each column of the table uniquely. Each column has minimum of three attributes, a name , data type and size.
Syntax:
Create table <table name>(<col1> <datatype>(<size>),<col2> <datatype<size>));
Ex:
create table emp(empno number(4) primary key, ename char(10));
2. Modifying the structure of tables.
a)add new columns
Syntax:
Alter table <tablename> add(<new col<datatype(size),<new col>datatype(size));
Ex:
alter table emp add(sal number(7,2));
3.Dropping a column from a table.
Syntax:
Alter table <tablename> drop column <col>;
Ex:
alter table emp drop column sal;
4. Modifying existing columns.
Syntax:
Alter table <tablename> modify(<col<newdatatype>(<newsize>));
Ex:
alter table emp modify(ename varchar2(15));
5. Renaming the tables
Syntax:
Rename <oldtable> to <new table>;
Ex:
rename emp to emp1;
6.truncating the tables.
Syntax:
Truncate table <tablename>;
Ex:
trunc table emp1;
7. Destroying tables.
Syntax:
Drop table <tablename>;
Ex:
drop table emp;
DML commands:
8.Inserting Data into Tables:- once a table is created the most natural thing to do is load this table with data to be manipulated later.
Syntax:
insert into <tablename> (<col1>,<col2>) values(<exp>,<exp>);
9.Delete operations.
a)remove all rows
Syntax:
delete from <tablename>;
b)removal of a specified row/s
Syntax:
delete from <tablename> where <condition>;
10. Updating the contents of a table.
a)updating all rows
Syntax:
Update <tablename> set <col>=<exp>,<col>=<exp>;
b)updating seleted records.
Syntax:
Update <tablename> set <col>=<exp>,<col>=<exp>
where <condition>;
11. Types of data constrains.
a)not null constraint at column level.
Syntax:
<col<datatype>(size)not null
b) unique constraint
Syntax:
Unique constraint at column level.
<col<datatype>(size)unique;
c)unique constraint at table level:
Syntax:
Create table tablename(col=format,col=format,unique(<col1>,<col2>);
d)primary key constraint at column level
Syntax:
<col<datatype>(size)primary key;
e) primary key constraint at table level.
Syntax:
Create table tablename(col=format,col=format
primary key(col1>,<col2>);
f)foreign key constraint at column level.
Syntax:
<col<datatype>(size>) references <tablename>[<col>];
g) foreign key constraint at table level
Syntax:
foreign key(<col>[,<col>])references<tablename>[(<col>,<col>)
h)check constraint
check constraint constraint at column level.
Syntax: <col<datatype>(size) check(<logical expression>)
i) check constraint constraint at table level.
Syntax:check(<logical expression>)
DQL Commands:
12.Viewing data in the tables:- once data has been inserted into a table, the next most logical operation would be to view what has been inserted.
a)all rows and all columns
Syntax:
Select <col> to <col n> from tablename;
Select * from tablename;
13.Filtering table data:- while viewing data from a table, it is rare that all the data from table will be required each time. Hence, sql must give us a method of filtering out data that is not required data.
a)Selected columns and all rows:
Syntax:
select <col1>,<col2> from <tablename>;
b)selected rows and all columns:
Syntax:
select * from <tablename> where <condition>;
c)selected columns and selected rows
Syntax:
select <col1>,<col2> from <tablename> where<condition>;
14. Sorting data in a table.
Syntax:
Select * from <tablename> order by <col1>,<col2> <[sortorder]>;