Introduction to Writing SQL Queries|System Source

SQL is the cornerstone of all relational database operations. In this hands-on course, you learn to exploit the full potential of the SELECT statement to write robust queries using the best query method for your application, test your queries, and avoid common errors and pitfalls. It also teaches alternative solutions to given problems, enabling you to choose the most efficient solution in each situation.

The course describes the ANSI/ISO SQL standard, but also identifies deviations from the standard in the two most widely used database products, Oracle and Microsoft SQL Server.


06/01/24 - TTV - Virtual-Instructor Led - Virtual-Instructor Led	(click to enroll)
07/22/24 - TTV - Virtual-Instructor Led - Virtual-Instructor Led	(click to enroll)
09/09/24 - TTV - Virtual-Instructor Led - Virtual-Instructor Led	(click to enroll)
10/28/24 - TTV - Virtual-Instructor Led - Virtual-Instructor Led	(click to enroll)
12/09/24 - TTV - Virtual-Instructor Led - Virtual-Instructor Led	(click to enroll)

This course combines expert lecture, real-world demonstrations and group discussions with machine-based practical labs and exercises. Working in a hands-on learning environment led by our expert practitioner, you’ll learn to:

Maximize the potential of SQL to build powerful, complex and robust SQL queries
Query multiple tables with inner joins, outer joins and self joins
Construct recursive common table expressions
Summarize data using aggregation and grouping
Execute analytic functions to calculate ranks
Build simple and correlated subqueries
Thoroughly test SQL queries to avoid common errors
Select the most efficient solution to complex SQL problems

Viewing outline for:

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

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

In order to benefit from the hands-on labs, attendees should have prior experience in scripting or programming languages.

This is an introductory- level course appropriate for those who are developing applications using relational databases, or who are using SQL to extract and analyze data from databases and need to use the full power of SQL queries.

We offer courses that serve as an excellent follow on to this course, with one option listed below. Our team can work with you to help you select the best next steps based on your role or learning goals.

Advanced SQL Programming

Explore Advanced Querying Techniques, Manipulating Table Data with DML, Stored Procedures, Triggers & More

Introduction to Writing SQL Queries

Course Overview

Scheduled Classes

What You'll Learn

Outline

Prerequisites

Who Should Attend

Next Step Courses