Oracle 19C - (SQL & PL/SQL)

Download as pdf or txt
Download as pdf or txt
You are on page 1of 8

6/26/2021 DURGA SOFTWARE SOLUTIONS

Courses
SCJP SCWCD Design patterns EJB CORE JAVA AJAX Adv. Ja
Offered:

ORACLE 19c Course Details


Subscribe and Access : 5200+ FREE Videos
and 21+ Subjects Like CRT, SoftSkills, JAVA,
Hadoop, Microsoft .NET, Testing Tools etc..
Batch Date: June 21st @ 8:00PM

Faculty: Mr. Dinesh

Duration : 45 Days

Venue :
DURGA SOFTWARE SOLUTIONS at Maitrivanam
Plot No : 202, IInd Floor ,
HUDA Maitrivanam,
Ameerpet, Hyderabad-500038.

Ph.No: +91 - 9246212143, 80 96 96 96 96

Syllabus:

ORACLE 19c - (SQL & PL/SQL)


1. Fundamentals of Database

• What is Data Base and Why is Data Base?


• DBMS Concepts

http://durgasoft.com/Oracle19C-Dinesh-Online.asp 1/8
6/26/2021 DURGA SOFTWARE SOLUTIONS

• RDBMS Concepts
• Oracle versions
• What is oracle 12c?
• What is MULTITENANT DATABASE?
• Features in Oracle 12c
• Advantages of Oracle 12c
• What is DATA MODEL for any OLTP RDBMS?

2. STRUCURE QUERY LANGUAGE( SQL ) AND CLIENT TOOLS

• What is SQL?
• Role of SQL in RDBMS
• Sub-languages in SQL
• CLIENT INTERFACE TOOS
• SQL * PLUS
• SQL DEVELOPER
• PLSQL DEVELOPER
• TOAD
• Oracle data types

3. SQL LANGUAGES

Data Definition Language (DDL) Commands


• CREATE
• ALTER
• TRUNCATE
• DROP
• RENAME

Data Manipulation Languages(DML) Commands


• INSERT
• UPDATE
• DELETE

Data Query Language / Retrieval Language


• SELECT

Transaction Control Languages(TCL) Commands


• ROLLBACK
• COMMIT
• SAVEPOINT
http://durgasoft.com/Oracle19C-Dinesh-Online.asp 2/8
6/26/2021 DURGA SOFTWARE SOLUTIONS

Data Control Languages(DCL) Commands


• GRANT
• REVOKE

4. DATA INTEGRITY CONSTRAINTS

• KEY Constraints
• DOMAIN Constraints
• REFERENTIAL INTEGRITY Constraints

5. OPERATORS

• Arithmetic Operators
• Relational Operators
• Relation Negation Operators
• Logical Operators
• Set Operators (Union, Union All, Intersect, Minus)

6. JOINS

• Purpose Of JOINS

• Types of JOINS

Cross join
Equi join
Inner Join
Self Join
Outer joins
left Outer join
Right Outer join
Full Outer join

7. SUB - QUERIES

• Single row sub-queries


• Multi-row sub-queries
• Correlated Sub-Queries
• Use of EXISTS / NOT EXISTS /ANY /ALL

8. SQL OBJECTS

http://durgasoft.com/Oracle19C-Dinesh-Online.asp 3/8
6/26/2021 DURGA SOFTWARE SOLUTIONS

8.1. VIEWS

• What is a view?
• Importance of View.
• Types of Views
• Simple views

Simple views with WITH CHECK OPTION


Simple views with WITH READ ONLY option

• Composite views
• Inline views
• Materialized views

Read Only Materialized view


Updatable Materialized view
Complex Materialized view

• Force views

8.2. INDEXES

• What is a INDEX
• Necessity of Index
• Types of Indexes

Simple Index
Composite Index
Bitmap Index
Function based index

8.3. CLUSTERS

• Use of cluster
• Creation of cluster
• Advantages of clusters

8.4. SEQUENCES

• Creating sequence
• Uses of sequences

http://durgasoft.com/Oracle19C-Dinesh-Online.asp 4/8
6/26/2021 DURGA SOFTWARE SOLUTIONS

Inserting sequence values


Updating column with sequence values
Altering sequence
Using sequence value as DEFAULT value
[ from oracle 12c ]

8.5. SYNONYMS

• Use of Synonym
• Types of Synonyms(PUBLIC,PRIVATE)
• How and when to create synonyms?

9. PARTITIONS

• What is partition?
• Types of partitions

Range partition
List partition
Hash partition

10. SQL FUNCTIONS

Categories of Functions
• Group or Aggregate Functions
• Scalar or Single Row Functions

Types of Functions
• Numerical Functions
• String Functions
• Conversion Functions
• Date Functions
• Analytical functions

11. PL/SQL

• Introduction to PL/SQL
• What is a Program block?
• Advantages of PL/SQL.
• Architecture of PL/SQL Engine
• Features, Structure of Program Block
http://durgasoft.com/Oracle19C-Dinesh-Online.asp 5/8
6/26/2021 DURGA SOFTWARE SOLUTIONS

• Data types, Executable Stmts.


• What are the blocks in the program structure?
• DECLARE Block
• BEGIN Block
• EXCEPTION Block
• END

11.1. TYPES OF PROGRAMS

• Static Programs
• Dynamic Programs
• Worked Examples and Assignments

11.2. Type Compatibility keywords

• Declaring variables Dynamically --%TYPE


• Declaring Table Type Record Variables--%ROWTYPE

11.3. SUB PROGRAMS

Stored Procedures
• What is a procedure?
• How to create it?
• What are the advantages of procedures?
• Types of stored procedures
• Static Procedures (without Arguments)
• Dynamic Procedures (with Arguments)
• Arguments or Parameters Mode
• IN OUT INOUT

CONTOL STRUCTURES
CONDITIONAL STATEMENTS
• Simple IF
• IF—THEN—ELSE
• COMPOUND IF
• ELSIF Construct
• Worked Examples and Assignments

LOOPS
• Simple Loop

http://durgasoft.com/Oracle19C-Dinesh-Online.asp 6/8
6/26/2021 DURGA SOFTWARE SOLUTIONS

• While Loop
• For Loop

CURSORS
• Types: Implicit and Explicit cursors
• Cursor Variables
• FOR loop cursor
• Parameterized cursor
• REF_CURSOR

What is BULK COLLECT?


What is FORALL?
What is BULKBIND?

EXCEPTION HANDLING AND THEIR TYPES


• Predefined Exceptions
• User defined Exceptions
• Pragma_ Exception_init
• RISE_APPLICATION_ERROR

PL/SQL RECORDS AND TABLES


• Composite Data Types
• PL/SQL Records & Tables
• Nested Records
• Using Record in a Table
• And Examples

FUNCTIONS
• What is a Function?
• When we have to create a function?
• How to call it?
• With and without arguments

PL/SQL Collections
• VARRAYS
• Associative Arrays
• Nested tables/ PLSQL TABLES

TRIGGERS
• What is a Trigger Program?
• When we need a Trigger?
http://durgasoft.com/Oracle19C-Dinesh-Online.asp 7/8
6/26/2021 DURGA SOFTWARE SOLUTIONS

• Parts of a Trigger
• Types of Triggers
• Database Triggers
• Row Level Triggers with Ex.
• Statement Level Triggers
• Instead-of Triggers - Views with Ex.

PACKAGES
• What is a PACKAGE?
• Structure of package
• Package Spec and Body
• Accessing procedures and functions from a package

Supporting
Interview Tips
Interview Questions
Resume Preparation tips

Durgasoft
11,331 likes

Like Page Share

http://durgasoft.com/Oracle19C-Dinesh-Online.asp 8/8

You might also like