Course Details
You will receive a hands-on introduction to SQL Basics and SQL*Plus and learn about relational databases and how to access them through SQL and SQL*Plus.
Course Prerequisites
An understanding of relational database design concepts
Course Agenda
1. Introduction to the Oracle Database
- Why Use an Entity Relationship Model?
- Physical Data Storage
- Relationships between Tables
- Domains and Data Integrity Constraints
- Data Model Review
2. SQL Basics and SQL*Plus
- Understanding the Difference Between SQL, SQL*Plus, and PL/SQL
- Invoking and Leaving SQL*Plus
- Entering and Executing Commands
- The SQL Buffer, SQL*Plus Default Output
- LOGIN.SQL / GLOGIN.SQL
- The SELECT Command and Mandatory Clauses
3. Using SQL Developer
- Understanding SQL Developer
- SQL Developer Setup
- Advanced Functionality
4. The WHERE and ORDER BY Clauses
- WHERE, ORDER BY Clauses
- Using the Data Dictionary
- Hierarchical Queries
- Pattern Matching
5. Single Row Functions
- Arithmetic Operators
- Operator Precedence
- Single Row vs. Group Functions
- Date and Numeric Format Models
- Data Conversion / Date Manipulation / Time Zones
- Control Statements
- IF THEN ELSE Logic
- Regular Expressions
6. Joining Data from Multiple Tables in Queries
- ANSI SQL 92 and SQL99 Joins
- The JOIN Condition / The Cartesian Product
- Outer JOINs
7. Group Functions and the GROUP BY Clause
- Group Functions
- Distinct Operator in Group Functions
- GROUP BY, WITH Clause, ROLLUP, CUBE
- Grouping Sets
8. Using Subqueries
- Single-Row, Multi-Row, Multi-Column Subqueries
- Subqueries in a WHERE / FROM / HAVING Clauses
- Subqueries In the SELECT Clause – Scalar Subqueries
- Correlated Subqueries / Hierarchical Queries
- EXISTS / NOT EXISTS / WITH / Recursive WITH
9. SQL Set Queries
- Multiple Select Statement Rules
- Union vs. Union All of Select Statements
- INTERSECTion of Two Select Statements
- MINUS one select from another
10. Multi-Table Inserts
- Unconditional All Insert
- Conditional Insert with ELSE
- Conditional ALL
- Conditional FIRST
- Pivoting Insert
11. Analytical Functions
- Single Row Analytic Functions
- Aggregating Analytic Functions
- PIVOT / UNPIVOT
- The MODEL Clause
12. SQL*Plus Reporting
- SQL*Plus Report Writing Commands
- COLUMN Command / Titles, System Variables
- Master-Detail Report with TITLE and COLUMN Commands
- Control Breaks, Computing Aggregate Amounts
- Using SET Variables in SQL*Plus, SQL to Generate SQL
13. Data Manipulation Language (DML) Commands
- INSERT, UPDATE, DELETE, and MERGE Commands
- Use of Subqueries
- Transaction Control Commands / Read Consistency / Locking
- The MERGE Command
- Flashback Queries
14. Data Definition Language (DDL) Commands
- DDL Commands, Object Naming, Dropping Objects
- TRUNCATE, COMMENT, RENAME Commands
- Creating Tables / Integrity Constraints
- CREATE TABLE, ALTER TABLE Commands
- Datatypes, Types of Declarative Constraints
- Default Values
- Creating, Deferrable, Dropping, Disabling/Enabling Constraints / Displaying Constraint Information
- Handling Exceptions
- Views, Synonyms, Sequences, Indexes, Virtual Columns
- Temporary Tables, External Tables