Course Code- Database Management Systems Lab
Lecture – Tutorial- 0-0-3 30
Internal Marks:
Practical::
Credits: 1.5 External Marks: 70
Prerequisites: Computer Programming 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: BTL
LEVEL
Upon successful completion of the course, the student will be able to:
CO1 Apply database management techniques to solve problems L2
CO2 Conduct experiments by using modern tools like MYSQL, Oracle L3
CO3 Develop an effective report based on various constructs implemented. L3
CO4 Apply technical knowledge for a given problem and express with an effective L3
oral communication
CO5 Analyze outputs of queries for a given problem L4
Contribution of Course Outcomes towards the achievement of Program Outcomes (1 – Low, 2- Medium, 3 – High)
PO PO PO PO PO PO PO PO PO PO PO PO PS PS PS
1 2 3 4 5 6 7 8 9 10 11 12 O1 O 2 O3
CO1
3 2 - - - - - - - - - 2 2 - -
CO2 3 - 3 - 2 - - - - - - - 2 - 2
CO3 3 2 2 - 3 - - - - 2 - 3 2 3 -
CO4 3 3 3 2 3 - - - - 3 - - 2 3 2
CO5 - - - - - - - - - - - 3 - 2 -
Syllabus
EXP Mapped
CONTENTS
CO
Creation, altering and dropping of tables and inserting rows into a table (use CO1,CO2,CO
I constraints while creating tables). 3,CO4,CO5
II Queries using i)DML Commands. INSERT, UPDATE and DELETE CO1,CO2,CO
ii)DCL Commands: COMMIT , ROLLBACK and SAVEPOINT. 3,CO4,CO5
III Queries using i)SELECT statement ii) SELECT statement with where CO1,CO2,CO
clause(Comparison Operators, AND, OR, NOT, IN, BETWEEN,LIKE) iii) 3,CO4,CO5
ORDER BY clause(sort by column name) iv) LIMIT clause
IV Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), CO1,CO2,CO
GROUP BY, HAVING and Creation and dropping of Views. 3,CO4,CO5
V Queries (along with sub Queries) using ANY, ALL, IN, EXISTS, CO1,CO2,CO
NOTEXISTS, UNION, INTERSET, Constraints. Example:- Select the roll 3,CO4,CO5
number and name of the student who secured fourth rank in the class.
VI Queries using Conversion functions (to_char, to_number and to_date),CO1,CO2,CO
string functions (Concatenation, lpad, rpad, ltrim, rtrim, lower, upper,3,CO4,CO5
initcap, length, substr and instr), date functions (Sysdate, next_day,
add_months, last_day, months_between, least, greatest, trunc, round,
to_char, to_date)
Queries (along with sub Queries) using ANY, ALL, IN, EXISTS, CO1,CO2,CO
VII NOTEXISTS, UNION, INTERSECT. 3,CO4,CO5
VIII Queries using Inner join, outer join using USING and NATURAL CO1,CO2,CO
Keywords. 3,CO4,CO5
IX Create a simple PL/SQL program which includes declaration section, CO1,CO2,CO
executable section and exception –Handling section (Ex. Student marks can 3,CO4,CO5
be selected from the table and printed for those who secured first class and
an exception can be raised if no records were found)
Insert data into student table and use COMMIT, ROLLBACK and
SAVEPOINT in PL/SQL block.
X Develop a program that includes the features NESTED IF, CASE and CO1,CO2,CO
CASE expression. The program can be extended using the NULLIF and 3,CO4,CO5
COALESCE functions.
XI Program development using WHILE LOOPS, numeric FOR LOOPS, nested CO1,CO2,CO
loops using ERROR Handling, BUILT –IN Exceptions, USE defined 3,CO4,CO5
Exceptions, RAISE- APPLICATION ERROR.
Programs development using creation of procedures, passing parameters IN CO1,CO2,CO
XII and OUT of PROCEDURES. 3,CO4,CO5
Program development using creation of stored functions, invoke functions CO1,CO2,
XIII in SQL Statements and write complex functions. CO3,CO4,
CO5
XIV Develop programs using features parameters in a CURSOR, FOR UPDATE CO1,CO2,CO
CURSOR, WHERE CURRENT of clause and CURSOR variables. 3,CO4,CO5
Develop Programs using BEFORE and AFTER Triggers, Row and CO1,CO2,
XV Statement Triggers and INSTEAD OF Triggers. CO3,CO4,
CO5
CO1,CO2,CO
XVI Create a table and perform the search operation on table using indexing 3,CO4,CO5
and non-indexing techniques
Learning Resources
Text Books
1. Murach‟s MySQL by JOEL MURACH, Shroff Publishers & Distributors [Link], June
2012.
2. The Complete Reference MYSQL,VikramVaswani, 2017, McGrawHill Education.
3. Oracle: The Complete Reference by Oracle Press
4. Nilesh Shah, "Database Systems Using Oracle”, PHI, 2007
5. Rick F Vander Lans, “Introduction to SQL”, Fourth Edition, Pearson Education, 2007