A company’s success hinges on responsible, accurate database management. Organizations rely on highly available data to complete all sorts of tasks, from creating marketing reports and invoicing customers to setting financial goals. When databases aren’t built or maintained correctly, it’s easy to mishandle or lose valuable data. Our SQL Programming and Database Training Series provides students with the skills they require to develop, analyze and maintain data and in correctly structured, modern and secure databases. 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.

starstarstarstarstar

* Actual course outline may vary depending on offering center. Contact your sales representative for more information.

Learning Objectives

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, attendees will 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

1

  • Introduction: Quick Tools Review

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

  • 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
3

  • Restricting and Sorting Data

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

  • 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
5

  • 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
6

  • Using the Group Functions and Aggregated Data

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

  • SQL Joins and Join Types

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

  • Using Subqueries

  • Introduction to Subqueries
    Single Row Subqueries
    Multiple Row Subqueries
9

  • Using the SET Operators

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

  • Using Data Manipulation Language (DML) statements

  • Data Manipulation Language
    Database Transactions
    Insert
    Update
    Delete
    Merge
11

  • Using Data Definition Language (DDL)

  • Data Definition Language
    Create
    Alter
    Drop
12

  • 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
13

  • Creating Sequences, Synonyms, Indexes

  • Creating sequences
    Creating synonyms
    Creating indexes
    Index Types
14

  • Creating Views

  • Creating Views
    Altering Views
    Replacing Views
15

  • Managing Schema Objects

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

  • 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
17

  • Data Control Language (DCL)

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

  • Manipulating Data

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

Audience

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.

Language

English

Prerequisites

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

$1,795

Length: 3.0 days (24 hours)

Level:

Not Your Location? Change

Course Schedule:

Schedule select
22
Jan
Monday
10:00 AM ET -
6:00 PM ET
Filling Fast
Available
Schedule select
11
Mar
Monday
10:00 AM ET -
6:00 PM ET
Filling Fast
Available
Schedule select
22
Apr
Monday
10:00 AM ET -
6:00 PM ET
Filling Fast
Available
Loading...