Database Management Systems Lab (R15)
LIST OF EXPERIMENTS
1. Practicesession: Students shouldbe allowed choosing appropriate DBMS software, installing
it, configuring it and starting working on it. Create sample tables, execute some queries,use
SQLPLUS features, use PL/SQLfeatures like cursors onsample database. Students should
be permitted to practice appropriate Userinterface creation tool and Report generation tool.
2. A college consists of number of employees‟ workingin different departments.In this
context; create two tables‟ employee and department. Employee consists of columns
empno, empname, basic, hra, da, deductions, gross, net, date-of-birth. The calculation of
hra, daareas pertherules of the college. Initiallyonly empno, empname, basic have valid
values. Other value sareto becomputed and updatedlater. Department contains department
deptname, and descriptioncolumns. Deptno is the primarykey indepartment table and
referential lintegrity constraint exists between employee and department tables. Perform the
following operations on the database:
Create tables department and employee with required constraints.
Initially only the fewcolumns (essential) are to be added. Addtheremaining
columns separately by using appropriate SQL command.
Basic column should not be null.
Addconstraint that basic should not be lessthan 5000.
Calculate hra, da, gross and net byusing PL/SQL program.
When ever salary is updated and its value becomes less than 5000 atriggerhas to
be raised preventing the operation.
The assertions are: hra should not belessthan 10% of basic and dash should not be
less than 50% of basic.
The percentage of hra and daare to be stored separately.
When the data becomes more than100%, a message has to be generated and with
user permission dahasto be merged with basic.
Empno should be unique and has to be generated automatically.
If the employee is going to retireina particular month, automatically a message has
to be generated.
The default value for date-of-bir this 1 jan, 1970.
When the employees called daily – wagersare to be added the constraint that salary
should be greater than or equal to 5000 should be dropped.
Display the information of the employees and departments with description of
Display the average salary of all the departments.
Display the average salary department wise.
Display the maximum salary of each department and also all departments put
Commit the changes when ever required and rollback if necessary.
Use substitution variables to insert values repeatedly.
Assume some of the employees shave given wrong information about date-ofbirth. Update the corresponding tables to change the value.
Find the employees whose salary is between 5000 and 10000 but not exactly 7500.
SRI SAI INSTITUTE OF TECHNOLOGY AND SCIENCE:RAYACHOTY Page 1