Oracle SQL

About Oracle SQL

If you’re interested in complexity in Oracle in particular, you’d have to go for the numerous ways storage can be configured using TABLESPACE, how to use PARTITIONs on base tables and secondary indexes, when to use the various types of different index algorithms (B-Tree, R-Tree, Hash, etc), CLUSTERing, when to use IOTs versus not, etc.   Knowing enough about Oracle to do this sort of thing correctly definitely qualifies as an “advanced” skill, typically acquired by working on numerous successful Oracle deployments.

What are the objectives for Oracle SQL Course?

  • Identify the company’s strategic objectives.
  • Workforce and cultural analysis.
  • Strategic group mapping(including diagram) and Nine cell matrix – its analysis and recommendations.

What are the prerequisites for Oracle SQL Course?

Processor, Memory, and Operating System Requirements

SQL is a database computer language for the retrieval and management of data in relational database.

 

Certificate for Oracle SQL

Oracle Certified Associate (OCA),

Oracle Certified Professional (OCP),

Oracle Certified Master (OCM).

Oracle Specialist (OCS) and Expert-level (OCE)

 

Future scope for Oracle SQL

As a key component for every Oracle Database user, Oracle intends to continue enhancing Oracle SQL Developer. Oracle SQL Developer will continue to be included with versions of the Oracle Database as a standard component and as a separate download on the Oracle Technology Network (OTN).

We’ve recently released a command line interface, SQLcl, and you can expect continued and regular enhancements and updates.

 

Oracle SQL 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, Displaying Constraint Information
  • Disabling/Enabling Constraints
  • Handling Exceptions
  • Views, Synonyms, Sequences, Indexes, Virtual Columns
  • Temporary Tables, External Tables