TTSQL005: Advanced SQL Programming

To view the class schedule you need to become an Affiliate

• Largest “Guaranteed To Run” public technical training schedules available
• Easy to become an Affiliate – no charge or fee

Become an EPIC Affiliate

already an Affiliate?  Login

About this Course

This Advanced SQL Programming course is designed to strengthen your expertise in managing and analyzing relational databases. You will begin with a focus on relational database concepts, including the Entity-Relationship Model and key modeling conventions. Understanding these fundamentals will prepare you for applying more complex techniques in real-world scenarios, using practical examples from the tables provided throughout the course.

From there, the course will explore essential SQL functions for grouping and aggregating data, such as the MIN and MAX functions. You’ll learn how to use the GROUP BY and HAVING clauses effectively, work with nested functions, and handle null values in your queries. The course also covers various join types, allowing you to retrieve data from multiple tables, manage ambiguous column names, and create efficient queries with inner, outer, and cross joins.

In the final sections, the focus shifts to more advanced SQL topics like subqueries and set operators, as well as data manipulation and table management techniques. You’ll gain hands-on experience executing subqueries, using set operators like UNION and MINUS, and managing data through DML and DDL statements. By the end, you will be fully prepared to implement advanced SQL solutions, manage database objects, and perform complex data manipulation tasks with confidence.

NOTE: This course uses Oracle SQL Developer in the labs, however the tooling is just the means used to teach SQL. This is not a SQL Developer focused course, and prior SQL Developer knowledge is not required. The course focuses on skills and concepts that can be applied to other databases and platforms. If you need a specific alternate database, please inquire for details and options.

Audience Profile

This intermediate-to-advanced course is ideal for SQL developers, database professionals, data analysts, and IT professionals who work with databases and seek to advance their skills in complex data manipulation, analysis, and management.

At Course Completion

Working in a hands-on environment led by an expert instructor, attendees will explore:

Understand the relational database model and apply entity-relationship modeling conventions
Utilize group functions to aggregate data, handle null values, and nest functions
Implement advanced queries with joins, including natural, outer, and cross joins
Execute subqueries, including single-row, multiple-row, and multiple-column subqueries
Use set operators like UNION, INTERSECT, and MINUS to combine queries
Perform DML operations like inserting, updating, and deleting rows, with real-world applications
Understand DDL statements for creating and managing database schema objects
Implement and enforce constraints such as NOT NULL, PRIMARY KEY, and FOREIGN KEY

Outline

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 skill level, interests and participation.

1. Introduction

Relational Database Concept
Entity Relationship Model
Relationship Modeling Conventions
Tables used in this course

2. Group Functions

Types of Group Functions
Using the MIN and MAX Functions
Group Functions and Null Values
Using the GROUP BY Clause
Illegal Queries Using Group
Using the HAVING Clause
Nesting Group Functions

3. Displaying Data from Multiple Tables Using Joins

Types of Joins
Retrieving Records with Natural Joins
Retrieving Records with the USING Clause
Qualifying Ambiguous Column Names
Creating Joins with the ON Clause
Retrieving Records with Nonequijoins
LEFT/RIGHT OUTER JOIN
Creating Cross Joins

4. Using Subqueries to Solve Queries

Subquery Syntax
Types of Subqueries
Executing Single-Row Subqueries
HAVING Clause with Subqueries
No Rows Returned by the Inner Query
Using the ALL Operator in Multiple-Row Subqueries
Multiple-Column Subquery
Null Values in a Subquery

5. Using Set Operators

Set Operator Rules
UNION Operator
Using the INTERSECT Operator
Using the MINUS Operator
Matching SELECT Statements
Using the ORDER BY Clause in Set

6. Managing Tables Using DML Statements

HR Application Scenario
Data Manipulation Language
Inserting New Rows
Inserting Specific Date and Time Values
UPDATE Statement Syntax
Updating Two Columns with a Subquery
Deleting Rows from a Table
TRUNCATE Statement
Committing Data
Implementing Read Consistency

7. Introduction to Data Definition Language

Database objects
Naming rules
Data types
CREATE TABLE statement
Overview of constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK constraints
Creating a table using a subquery
ALTER TABLE statement
DROP TABLE statement

Prerequisites

To ensure a smooth learning experience and maximize the benefits of attending this course, you should have the following prerequisite skills:

Basic SQL Programming experience is required. This course is not intended for beginners.

Take Before: Students should have incoming practical skills aligned with those in the course(s) below, or should have attended the following course(s) as a pre-requisite:

TTSQL002 Introduction to SQL Programming Basics
TTSQL003 Introduction to Writing SQL Queries