Introduction to Writing SQL Queries

Please note that this list of topics is based on our standard course offering, evolved from typical industry uses and trends. We’ll work with you to tune this course and level of coverage to target the skills you need most. Topics, agenda and labs are subject to change, and may adjust during live delivery based on audience needs, participation and skill-level.

Introduction: Quick Tools Review

• Introduction to SQL and its development environments
• Using SQL*PLUS
• Using SQL Developer

Using the SQL SELECT Statement

• Capabilities of the SELECT statement
• Arithmetic expressions and NULL values in the SELECT statement
• Column aliases
• Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
• Use of the DESCRIBE command

Restricting and Sorting Data

• Limiting the Rows
• Rules of precedence for operators in an expression
• Substitution Variables
• Using the DEFINE and VERIFY command

Single-Row Functions

• Describe the differences between single row and multiple row functions
• Manipulate strings with character function in the SELECT and WHERE clauses
• Manipulate numbers with the ROUND, TRUNC and MOD functions
• Perform arithmetic with date data
• Manipulate dates with the date functions

Conversion Functions and Expressions

• Describe implicit and explicit data type conversion
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nest multiple functions
• Apply the NVL, NULLIF, and COALESCE functions to data
• Decode/Case Statements

Using the Group Functions and Aggregated Data

• Group Functions
• Creating Groups of Data
• Having Clause
• Cube/Rollup Clause

SQL Joins and Join Types

• Introduction to JOINS
• Types of Joins
• Natural join
• Self-join
• Non equijoins
• OUTER join

Using Subqueries

• Introduction to Subqueries
• Single Row Subqueries
• Multiple Row Subqueries

Using the SET Operators

• Set Operators
• UNION and UNION ALL operator
• INTERSECT operator
• MINUS operator
• Matching the SELECT statements

Using Data Manipulation Language (DML) statements

• Data Manipulation Language
• Database Transactions
• Insert
• Update
• Delete
• Merge

Using Data Definition Language (DDL)

• Data Definition Language
• Create
• Alter
• Drop

Data Dictionary Views

• Introduction to Data Dictionary
• Describe the Data Dictionary Structure
• Using the Data Dictionary views
• Querying the Data Dictionary Views
• Dynamic Performance Views

Creating Sequences, Synonyms, Indexes

• Creating sequences
• Creating synonyms
• Creating indexes
• Index Types

Creating Views

• Creating Views
• Altering Views
• Replacing Views

Managing Schema Objects

• Managing constraints
• Creating and using temporary tables
• Creating and using external tables

Retrieving Data Using Subqueries

• Retrieving Data by Using a Subquery as Source
• Working with Multiple-Column subqueries
• Correlated Subqueries
• Non-Correlated Subqueries
• Using Subqueries to Manipulate Data
• Using the Check Option
• Subqueries in Updates and Deletes
• In-line Views

Data Control Language (DCL)

• System privileges
• Creating a role
• Object privileges
• Revoking object privileges

Manipulating Data

• Overview of the Explicit Default Feature
• Using multitable INSERTs
• Using the MERGE statement
• Tracking Changes in Data

Please note that this list of topics is based on our standard course offering, evolved from typical industry uses and trends. We’ll work with you to tune this course and level of coverage to target the skills you need most. Topics, agenda and labs are subject to change, and may adjust during live delivery based on audience needs, participation and skill-level.

Introduction: Quick Tools Review

• Introduction to SQL and its development environments
• Using SQL*PLUS
• Using SQL Developer

Using the SQL SELECT Statement

• Capabilities of the SELECT statement
• Arithmetic expressions and NULL values in the SELECT statement
• Column aliases
• Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
• Use of the DESCRIBE command

Restricting and Sorting Data

• Limiting the Rows
• Rules of precedence for operators in an expression
• Substitution Variables
• Using the DEFINE and VERIFY command

Single-Row Functions

• Describe the differences between single row and multiple row functions
• Manipulate strings with character function in the SELECT and WHERE clauses
• Manipulate numbers with the ROUND, TRUNC and MOD functions
• Perform arithmetic with date data
• Manipulate dates with the date functions

Conversion Functions and Expressions

• Describe implicit and explicit data type conversion
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nest multiple functions
• Apply the NVL, NULLIF, and COALESCE functions to data
• Decode/Case Statements

Using the Group Functions and Aggregated Data

• Group Functions
• Creating Groups of Data
• Having Clause
• Cube/Rollup Clause

SQL Joins and Join Types

• Introduction to JOINS
• Types of Joins
• Natural join
• Self-join
• Non equijoins
• OUTER join

Using Subqueries

• Introduction to Subqueries
• Single Row Subqueries
• Multiple Row Subqueries

Using the SET Operators

• Set Operators
• UNION and UNION ALL operator
• INTERSECT operator
• MINUS operator
• Matching the SELECT statements

Using Data Manipulation Language (DML) statements

• Data Manipulation Language
• Database Transactions
• Insert
• Update
• Delete
• Merge

Using Data Definition Language (DDL)

• Data Definition Language
• Create
• Alter
• Drop

Data Dictionary Views

• Introduction to Data Dictionary
• Describe the Data Dictionary Structure
• Using the Data Dictionary views
• Querying the Data Dictionary Views
• Dynamic Performance Views

Creating Sequences, Synonyms, Indexes

• Creating sequences
• Creating synonyms
• Creating indexes
• Index Types

Creating Views

• Creating Views
• Altering Views
• Replacing Views

Managing Schema Objects

• Managing constraints
• Creating and using temporary tables
• Creating and using external tables

Retrieving Data Using Subqueries

• Retrieving Data by Using a Subquery as Source
• Working with Multiple-Column subqueries
• Correlated Subqueries
• Non-Correlated Subqueries
• Using Subqueries to Manipulate Data
• Using the Check Option
• Subqueries in Updates and Deletes
• In-line Views

Data Control Language (DCL)

• System privileges
• Creating a role
• Object privileges
• Revoking object privileges

Manipulating Data

• Overview of the Explicit Default Feature
• Using multitable INSERTs
• Using the MERGE statement
• Tracking Changes in Data