Duration: 5 Days
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. Topics necessary to the development and administration of an Oracle 12c database are also covered.
What You Will Learn
- Relational database
terminology
- Structured
Query Language (SQL) basics
- Format reports using SQL*Plus commands
- Extract and organize information from the database
- Insert, update, and delete information in database tables
- Create and drop tables, views, synonyms, and indexes
Audience
Application designers, developers, and database administrators
Prerequistes
An understanding of relational database design concepts
Course Outline
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
Course Labs
Lab 1: Data
Model Review
Lab 2: SQL Basics
Lab 3: SQL
Developer
Lab 4: Basic Queries
Lab 5: Data
Definition
Lab 6: Data Manipulation
Lab 7:
Filtering and Sorting Data
Lab 8: Single Row Functions
Lab 9: ANSI 92 Joins
Lab 10: ANSI 99 Joins
Lab
11: Group Clauses and Functions
Lab 12: Subqueries
Lab 13: SQL SET Queries
Lab 14: Multi-Table Inserts
Lab 15: Analytical Functions
Lab 16: SQL Plus Reporting
Lab 17: Advanced DDL
Lab 18: Advanced DML
Lab
19: Database Security
Lab 20: Getting Data In And Out
