SQL/PL SQL
SQL -Structured Query Language is a domain-specific language used in programming and designed for managing data held in a relational database management system(RDBMS), or for stream processing in a relational data stream management system (RDSMS)
PL/SQL –Procedural Training/Structured Source Language that includes procedural language components like loops and conditions that permit declaration of constants and variables, procedures and functions and might handle runtime errors. One will produce PL/SQL units like procedures, functions, packages, sorts and triggers that are keep within the information for the use by applications that are utilized by any of the Oracle Database informatic interfaces
Introduction to SQL & SQL*Plus
Review of Basic SQL and history of SQL:
History of SQL
Evolution of SQL
SQL constructs:
- Select
- Project
- Join
Complex Boolean logic in SQL
ROWID restrictions
Using SQL*Plus
Creating basic reports
Altering SQL execution plans
Joining Oracle tables
Sort-merge joins
Nested Loop joins
Hash joins
STAR joins
Bitmap joins
Exercise – Change table join techniques & evaluate performance
Equi-join
Outer join
Advanced SQL operators
- Between operator
- IN and NOT In operators
- Sub-queries
- EXISTS clause
- Using wildcards in queries (LIKE operator)
SQL Tuning
DML and SQL Tuning
Writing and optimizing INSERT statements
Writing and optimizing DELETE statements
Writing and optimizing UPDATE statements
Aggregation IN sql
Aggregation in SQL
- Count(*)
- Sum
- Avg
- Min and max
- Using the group by clause
PL/SQL Section
Basics of PL/SQL
- PL/SQL architecture
- PL/SQL and SQL*Plus
- PL/SQL Basics
- Introduction to PL/SQL
- PL/SQL as a 4th generation language
- Compiling vs. Interpreting
- Declare statement
- Exercise: Write “hello” world PL/SQL program
Using PL/SQL Variables
PL/SQL Constants
PL/SQL Datatypes
Error messages – user errors and show errors
PL/SQL wrapper utility
Error checking – exception handling
- Defining exceptions
- Using the when others clause
- Ensuring complete error checking
- Passing error messages to calling routine
Cursors in PL/SQL
- Cursor basics
- Using a cursor for a multi-row SQL query
Dynamic SQL in PL/SQL
- Introduction to the dbms_sql package
- Creating a dynamic SQL statement
Entity/relation modeling
Types of data relationships
Data normalization
One-to-many relationships
One-to-many relationships
Many-to-many relationships
Recursive many-to-many relationships
Subqueries
- Using IN, NOT IN, EXISTS and NOT EXISTS
- Subqueries
- Correlated subquery
- Non-correlated subqueries
SQL access methods
- Review of Basic joining methods
- Merge join
- Hash Join
- Nested Loop join
- Advanced SQL operators
- Between operator
SQL Tuning and full-table scans
Basics of file I/O
Sequential reads vs. scattered reads
When full scans are best
RAM caching in the SGA
Automating table caching
Solid State Disks
Tracking full-scans over time with AWR
Exercise – Query v$sql
PL/SQL structures
- Simple blocks
- Control structures
- PL/SQL records
- Recognizing the Basic PL/SQL Block and Its Sections
- Describing the Significance of Variables in PL/SQL
- Distinguishing Between PL/SQL and Non-PL/SQL Variables
- Declaring Variables and Constants
- Executing a PL/SQL Block
Boolean logic in PL/SQL
- Identifying the Uses and Types of Control Structures
- Constructing an IF Statement
- Constructing and Identifying Different Loop Statements
- Controlling Block Flow Using Nested Loops and Labels
- Using Logic Tables
- If-then-else structure
- Testing for numbers characters and Booleans
Iteration in PL/SQL
- For loop
- While loop
PL/SQL tables
- Defining PL/SQL tables
- Reasons to use PL/SQL tables
- Populating a PL/SQL table
- Retrieving from a PL/SQL table
Nested blocks in PL/SQL
- Creating nested blocks
- Understanding scope in nested blocks
Triggers in PL/SQL
- Triggers and database events
- Defining a trigger
- Timing a trigger
- Enabling and disabling a trigger
Stored procedures, functions and packages
- Basics of stored procedures
- Basics of functions
- Basics of packages
- Defining stored procedures & functions
- Function and stored procedures prototypes
- Passing arguments to functions and stored procedures
- Recompiling functions and stored procedures
- Package forward declaration
- Package dependency
- Package overloading
- Listing package information