| 1 |
Exercise – 1 </br> Table DDL & DML with Constraints & SELECT: </br> • Creation, altering and dropping of tables and inserting rows into a table (use constraints while creating tables) examples using SELECT command. |
Creating Tables |
| 2 |
Exercise – 2 </br> Advanced Queries with Subqueries, Operators & Constraints: </br> • 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. |
Queries |
| 3 |
Exercise – 3 </br> Aggregate Functions, Grouping & Views: </br> • Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY, HAVING and Creation and dropping of Views. |
Queries |
| 4 |
Exercise – 4 </br> Conversion & String Functions, Date & Time Manipulation: </br> • 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). |
Queries using conversion functions |
| 5 |
Exercise – 5 </br> PL/SQL Program: Student Marks Retrieval with Exception Handling: </br> • Creation of 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). |
PL/SQL Programs |
| 6 |
Exercise – 6 </br> Data Manipulation with COMMIT, ROLLBACK & SAVEPOINT in PL/SQL: </br> • Insert data into student table and use COMMIT, ROLLBACK and SAVEPOINT in PL/SQL block. |
Commit and Rollback |
| 7 |
Exercise – 7 </br> Conditional Logic: Nested IF, CASE & NULLIF/COALESCE: </br> • Develop a program that includes the features NESTED IF, CASE and CASE expression. The program can be extended using the NULLIF and COALESCE functions. |
Programs using Case |
| 8 |
Exercise – 8 </br> Looping: WHILE, Numeric FOR & Error Handling: </br> • Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops using ERROR Handling, BUILT –IN Exceptions, USE defined Exceptions, RAISE- APPLICATION ERROR. |
Programs using Loops |
| 9 |
Exercise – 9 </br> Procedures: Parameter Passing & Function Invocation: </br> • Programs development using creation of procedures, passing parameters IN and OUT of PROCEDURES. |
Procedures |
| 10 |
Exercise – 10 </br> Stored Functions: Complex Implementations & SQL Integration: </br> • Program development using creation of stored functions, invoke functions in SQL Statements and write complex functions. |
Stored Functions |
| 11 |
Exercise – 11 </br> Packages: Specification, Body, Objects & Cursor Interactions: </br> • Program development using creation of package specification, package bodies, private objects, package variables and cursors and calling stored packages. |
Packages |
| 12 |
Exercise – 12 </br> Cursors: Parameterized, FOR UPDATE & WHERE CURRENT: </br> • Develop programs using features parameters in a CURSOR, FOR UPDATE CURSOR,WHERE CURRENT of clause and CURSOR variables. |
Cursors |
| 13 |
Exercise – 13 </br> Triggers: BEFORE, AFTER, Row, Statement & INSTEAD OF: </br> • Develop Programs using BEFORE and AFTER Triggers, Row and Statement Triggers and INSTEAD OF Triggers. |
Triggers |
| 14 |
Exercise – 14 </br> For a given set of relation tables perform the following: </br> (a). Creating Views </br> (b). Dropping Views </br> (c). Selecting from a View |
Views |