DBMS with Oracle


Structured Query Language (SQL)

Chapter 1: Introduction To SQL

1.                Introduction Database

2.                Understanding DBMS vs RDBMS

3.                Gone through SQL Standards

4.                Sub languages of SQL

5.                Difference between 10g vs 11g vs 12c

6.                Installation of 12c

7.                About SQL*Plus and use of developer tool

8.                Datatypes in Oracle

9.                Operators in Oracle

10.             Understanding Schema design and objects

Chapter 2: Data Retrieval Techniques

1.                How to use select statement in different ways to retrieve records?

2.                Working with Column alias

3.                Working with Table alias

4.                Data filtering and sorting with in single table

5.                Clauses and its types in oracle

                 Where clause

                 Having clause

                 From clause

                 Group by clause

                 Order by clause

                 Using clause

                 Constraint clause

                 For update clause

Chapter 3: Working With DDL Commands

1.                Table creation using CREATE statement

2.                Creating table from another table

3.                Dropping a table using DROP command

4.                Altering the column of a table

5.                Modifying the column datatype in a table

6.                Renaming the column of a table

7.                Renaming an entire table

8.                Using truncate command

9.                Difference between Delete and Truncate command

Chapter 4: Working With DML Commands

1.                How to copy data from one table to another table?

2.                How to copy the structure alone from a table?

3.                Different types of inserting row to an existing table

4.                Updating any value of with in a record using UPDATE command

5.                Deleting a particular record from a table

6.                Using merge & insert all command [Implementation of Project 1 ]

Chapter 5: Integrity Constraints

1.                How to declare column level constraints?

2.                How to declare row level constraints ?

3.                How to add constraints to an existing table?

4.                Types of integrity constraints

                 Not null

                 Unique key

                 Primary key

                 Referential integrity

                 Check integrity

5.                How to enable and disable constraints?

6.                How to get information about constraints?

Chapter 6: Built In Functions

1.                Understanding Single row functions

2.                How to use single row functions using dummy table?

3.                Types of single row functions

                 String functions

                 Date functions

                 Mathematical functions

                 Conversion functions

                 Special functions

                 Analytical functions

4.                Working with multi row functions [Implementation of Project 2]

Chapter 7: Data Aggregation

1.                Working with aggregate function






2.                Working with group by clause

3.                Working with having clause

4.                Difference between WHERE and HAVING clause

Chapter 8: Importance Of JOIN

1.                Understanding joins and its uses

2.                Types of joins

                 Equi join

                 Non – equi join

                 Self join

                 Outer join

                 Left & Right outer join

                 Full outer join

                 Cross join [Implementation of Project 3]

Chapter 9: Set Operators And Pseudo Columns:

                 How to use set operators in a single table content?

                 Working with set operator types


                 UNION ALL



                 Working with pseudo columns using the following



Chapter 10: Sub Queries

1.                Importance of sub queries

2.                Using different types of sub queries

                 Single row sub queries

                 Multi row sub queries

                 Nested queries

                 Multi column sub queries

                 Correlated sub queries

3.                Using inline views and scalar queries [ Implementation of Project 4]

Chapter 11: Database Transaction And Security

1.                Working with data query language using TCL

2.                Working with data control language commands

3.                Use of commit and rollback

4.                Use of savepoint and set transaction

5.                How to give system privileges to an user?

6.                How to invoke and revoke object privileges?

7.                How to create users and roles?

Chapter 12: Design Of Schema Objects

1.                Creating and working with Views

2.                Working with Synonyms

3.                Creating Index and clusters

4.                Working with in materialized view

5.                Understanding sequences and its types [Implementation of Project 5]

                                           PL/SQL (Procedural Language Extension to SQL)

Chapter 1: Introduction To PL/SQL

1.                Informal introduction to PL/SQL

2.                Advantages of PL/SQL

3.                Datatypes in PL/SQL

4.                Program structure of PL/SQL

5.                Embedding SQL statements

6.                Using conditional statements and loops

Chapter 2: Creating And Using Cursors

1.                What is cursor?

2.                How to create cursor?

3.                Using cursors in PL/SQL

4.                How to create explicit cursor?

5.                Creation of for loop cursor

6.                What are cursor parameters?

7.                How to use for update clause?

8.                What is ref cursors?

9.                How to use implicit cursors?

Chapter 3: Understanding Exception Handling

1.                What is an Exception?

2.                Describing Exception types

3.                Handling system defined exceptions

4.                Handling user defined exceptions?

5.                Sql code vs Sql errm

6.                Pragma exception_init

Chapter 4: Creation Of Stored Procedures

                 Creating procedures in Pl/SQL

                 Working with procedure parameters

                 IN parameter

                 OUT parameter

                 INOUT parameter

                 How to create procedures with cursors

                 How procedures return records?

                 What is Pragma autonomous transaction?

Chapter 5: Creating & Using Functions

1.                Importance of function

2.                How to create functions?

3.                Difference between procedures and functions

4.                How to use inline functions?

Chapter 6: Creating & Using Packages

1.                What is a Package?

2.                Reasons to use packages

3.                What is package specification?

4.                What is package body?

5.                How to instantiate package?

6.                How to initialize instantiated package?

7.                What are all the package state?

Chapter 7: Triggers In PL/SQL

1.                How to create triggers?

2.                Benefits of trigger

3.                How to trigger a trigger?

4.                Using DML trigger & DDL trigger

5.                How to audit database using triggers?

6.                What are database level trigger?

Chapter 8: Collections In PL/SQL

1.                What is collection?

2.                How to use arrays?

3.                Using nested tables

4.                How to use index by value?

5.                Listing types of collection methods.

6.                General overview and discussion about DBA Concepts