This course is designed to deliver the fundamentals of SQL and PL/SQL along with the benefits of the programming languages using Oracle Database technology. In this course participants learn the concepts of relational databases. Additionally the participants are provided with the essential SQL skills that allow them to write queries against single and multiple tables, manipulate data in tables, and create database objects. The usage of single row functions to customize output, how to invoke conversion functions and conditional expressions are covered. The use of group functions to report aggregated data is also included. Additionally students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications. Participants also learn to create anonymous PL/SQL blocks, stored procedures and functions. Participants get to learn about declaring identifiers and trapping exceptions. Demonstrations and hands-on practice reinforce the fundamental concepts.
Overview of Oracle Database 11g and related products
Overview of relational database management concepts and terminologies
Introduction to SQL and its development environments
The HR schema and the tables used in this course
Oracle Database documentation and additional resources
II. Retrieve Data Using the SQL SELECT Statement
List the capabilities of SQL SELECT statements.
Generate a report of data from the output of a basic SELECT statement
Use arithmetic expressions and NULL values in the SELECT statement
Invoke Column aliases
Concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
Display the table structure using the DESCRIBE command
III. Restricted and Sorted Data
Write queries with a WHERE clause to limit the output retrieved
Describe the comparison operators and logical operators
Describe the rules of precedence for comparison and logical operators
Usage of character string literals in the WHERE clause
Write queries with an ORDER BY clause
Sort output in descending and ascending order
IV. Usage of Single-Row Functions to Customize Output
List the differences between single row and multiple row functions
Manipulate strings using character functions
Manipulate numbers with the ROUND, TRUNC and MOD functions
Perform arithmetic with date data
Manipulate dates with the date functions
V. Conversion Functions and Conditional Expressions
Describe implicit and explicit data type conversion
Describe the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
Nesting multiple functions
Apply the NVL, NULLIF, and COALESCE functions to data
Usage of conditional IF THEN ELSE logic in a SELECT statement
VI. Aggregated Data Using the Group Functions
Usage of the aggregation functions in SELECT statements to produce meaningful reports
Describe the AVG, SUM, MIN, and MAX function
How to handle Null Values in a group function?
Divide the data in groups by using the GROUP BY clause
Exclude groups of date by using the HAVING clause
VII. Display Data from Multiple Tables
Write SELECT statements to access data from more than one table
Join Tables Using SQL: 1999 Syntax
View data that does not meet a join condition by using outer joins
Join a table by using a self-join.
Create Cross Joins
VIII. Usage of Subqueries to Solve Queries
Use a Subquery to Solve a Problem
Group Functions in a Subquery
Use the ANY and ALL Operator in Multiple-Row Subqueries
Use the EXISTS Operator
IX. SET Operators
Describe the SET operators
Use a SET operator to combine multiple queries into a single query
Describe the UNION, UNION ALL, INTERSECT, and MINUS Operators
Use the ORDER BY Clause in Set Operations
X. Data Manipulation
Add New Rows to a Table
Change Data in a Table
Use DELETE and TRUNCATE Statements
How to save and discard changes with the COMMIT and ROLLBACK statements?
Implement Read Consistency
Describe the FOR UPDATE Clause
XI. DDL Statements to Create and Manage Tables
Categorize Database Objects
Describe the data types
Create a table using a subquery
How to alter a table?
How to drop a table?
XII. Other Schema Objects
Create, modify, and retrieve data from a view
Perform Data manipulation language (DML) operations on a view
How to drop a view?
Create, use, and modify a sequence
Create and drop indexes
Create and drop synonyms
XIII. Introduction to PL/SQL
List the benefits of PL/SQL Subprograms
Overview of the Types of PL/SQL blocks
Create a Simple Anonymous Block
Generate the Output from a PL/SQL Block
XIV. PL/SQL Identifiers
List the different Types of Identifiers in a PL/SQL subprogram
Usage of the Declarative Section to Define Identifiers
Use of variables to store data
Scalar Data Types
Sequences in PL/SQL Expressions
XV. Write Executable Statements
Basic PL/SQL Block Syntax Guidelines
How to comment code?
SQL Functions in PL/SQL
Data Type Conversion
Operators in PL/SQL
XVI. Interaction with the Oracle Server
SELECT Statements in PL/SQL to retrieve data
Data Manipulation in the Server Using PL/SQL
The SQL Cursor concept
Learn to use SQL Cursor Attributes to Obtain Feedback on DML
How to save and discard transactions?
XVII. Control Structures
Conditional processing Using IF Statements
Conditional processing Using CASE Statements
Simple Loop Statement
While Loop Statement
For Loop Statement
The Continue Statement
XVIII. Usage of Composite Data Types
The %ROWTYPE Attribute
Insert and Update with PL/SQL Records
Associative Arrays (INDEX BY Tables)
INDEX BY Table Methods
INDEX BY Table of Records
XIX. Explicit Cursors
Understand Explicit Cursors
Declare the Cursor
How to open the Cursor?
Fetching data from the Cursor
How to close the Cursor?
Cursor FOR loop
Explicit Cursor Attributes
FOR UPDATE Clause and WHERE CURRENT Clause
XX. Exception Handling
What are exceptions?
Handle Exceptions with PL/SQL
Trap Predefined Oracle Server Errors
Trap Non-Predefined Oracle Server Errors
Trap User-Defined Exceptions
XXI. Stored Procedures and Functions
What are Stored Procedures and Functions?
Differentiate between anonymous blocks and subprograms
Create a Simple Procedure
Create a Simple Procedure with IN parameter
Create a Simple Function
Execute a Simple Procedure
Execute a Simple Function
This course isn't currently on the calendar. Want to schedule it?
Need to request this course on a Different Day?
LANTEC is very responsive to local client needs and unique or custom class demands. If you require a delivery date option for a class title on our schedule, please submit your specific request for immediate consideration. We can often accommodate countless additional courses NOT available on our public schedules, please inquire for personal assistance.