DATABASE MANAGEMENT SYSTEMS LAB
(Common to CSE, CSE-AI, CSE-AI&ML, CSE-DS)
II [Link] - II Semester L T P C
Course Code: 2305L2206 0 0 3 1.5
Course Outcomes:
At the end of the Course, Student will be able to:
CO1 Develop an orderly design procedure to model the given problem
CO2 Demonstrate the logical and orderly design procedure to meet problem
statement
CO3 Develop documentation to communicate Algorithm, Pseudo code,
Program, schematics, diagrams, or tables
Mapping of Course Outcomes with Program Outcomes:
Program Outcomes (POs) Program Specific
Course Outcomes (PSOs)
Outcomes
PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO 12 PSO1 PSO2 PSO3
CO1 3 3 3 3 3 2 2 2 2 2 3 3
CO2 3 3 2 2 2 2 3 3 3 3 3
CO3 3 3 3 3 2 2 2 3 2 2 2 3 2
Experiments covering the topics:
DDL, DML, DCL commands
Queries, nested queries, built-in functions,
PL/SQL programming- control structures
Procedures, Functions, Cursors, Triggers,
Database connectivity- ODBC/JDBC
Sample Experiments:
1. Creation, altering and droping of tables and inserting rows into a table (use
constraints while creating tables) examples using SELECT command.
2. Queries (along with sub Queries) using ANY, ALL, IN, EXISTS,
NOTEXISTS, UNION, INTERSET, Constraints. Example:- Select the roll
number and name of the student who secured fourth rank in the class.
3. Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN),
GROUP BY, HAVING and Creation and dropping of Views.
4. Queries using Conversion functions (to_char, to_number and to_date),
string functions (Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap,
length, substr and instr), date functions (Sysdate, next_day, add_months,
last_day, months_between, least, greatest, trunc, round, to_char, to_date)
34
2
5.
i. Create a simple PL/SQL program which includes declaration section,
executable section and exception –Handling section (Ex. Student
marks can be selected from the table and printed for those who
secured first class and an exception can be raised if no records were
found)
ii. Insert data into student table and use COMMIT, ROLLBACK and
SAVEPOINT in PL/SQL block.
6. Develop a program that includes the features NESTED IF, CASE and CASE
expression. The program can be extended using the NULLIF and
COALESCE functions.
7. Program development using WHILE LOOPS, numeric FOR LOOPS, nested
loops using ERROR Handling, BUILT –IN Exceptions, USE defined
Exceptions, RAISE- APPLICATION ERROR.
8. Programs development using creation of procedures, passing
parameters IN and OUT of PROCEDURES.
9. Program development using creation of stored functions, invoke functions
in SQLStatements and write complex functions.
10. Develop programs using features parameters in a CURSOR, FOR
UPDATE CURSOR, WHERE CURRENT of clause and CURSOR variables.
11. Develop Programs using BEFORE and AFTER Triggers, Row and
Statement Triggers and INSTEAD OF Triggers
12. Create a table and perform the search operation on table using indexing
and non-indexing techniques.
13. Write a Java program that connects to a database using JDBC
14. Write a Java program to connect to a database using JDBC and insert values
into it
15. Write a Java program to connect to a database using JDBC and delete values
from it
Text Books/Suggested Reading:
1. Oracle: The Complete Reference by Oracle Press
2. Nilesh Shah, "Database Systems Using Oracle”, PHI, 2007
3. Rick F Vander Lans, “Introduction to SQL”, Fourth Edition, Pearson
Education, 2007
34
3