[Link].
– Computer Science & Engineering JNTUA R23 Regulations
II Year [Link]. CSE – II Semester
L T P C
0 0 3 1.5
(23A05402P) DATABASE MANAGEMENT SYSTEMS LAB
Course Objectives:This Course will enable students to
Populate and query a database using SQL DDL/DML Commands
Declare and enforce integrity constraints on a database
Writing Queries using advanced concepts of SQL
Programming PL/SQL including procedures, functions, cursors and triggers.
Course Outcomes:After completion of the course, students will be able to
• Utilizing Data Definition Language (DDL), Data Manipulation Language (DML),
and Data Control Language (DCL) commands effectively within a database
environment (L3)
• Constructing and execute queries to manipulate and retrieve data from databases.
(L3)
• Develop application programs using PL/SQL. (L3)
• Analyze requirements and design custom Procedures, Functions, Cursors, and
Triggers, leveraging their capabilities to automate tasks and optimize database
functionality (L4)
• Establish database connectivity through JDBC (Java Database Connectivity) (L3)
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)
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.
[Link]. – Computer Science & Engineering JNTUA R23 Regulations
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 SQL
Statements 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
