Introduction to SQL Programming Basics|System Source

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. Data professionals like analysts, developers and architects are tasked with creating, optimizing, managing and analyzing data from databases – with little room for error. 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. A full presentation of the basics of relational databases and their use are also covered.

This “skills-centric” course is about 50% hands-on lab and 50% lecture, designed to train attendees core SQL programming and database skills, coupling the most current, effective techniques with the soundest industry practices. Throughout the course students will be led through a series of progressively advanced topics, where each topic consists of lecture, group discussion, comprehensive hands-on lab exercises, and lab review.

Our engaging instructors and mentors are highly experienced practitioners who bring years of current "on-the-job" experience into every classroom. Working within in an engaging, hands-on learning environment, guided by our expert team, attendees will explore:

Basic RDBMS Principles
The SQL Language and Tools
Using SQL Developer
SQL Query Basics
WHERE and ORDER BY
Functions
ANSI 92 JOINS
ANSI 99 Joins
GROUP BY and HAVING
Subqueries
Regular Expressions
Analytics

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

Basic RDBMS Principles

Relational design principles
Accessing data through a structured query language
Entity relationship diagrams
Data Domains
Null values
Indexes
Views
Denormalization
Data Model Review

The SQL Language and Tools

Using SQL*Plus
Why Use SQL*Plus When Other Tools Are Available?
Starting SQL*Plus
EZConnect
SQL Commands
PL/SQL Commands
SQL*Plus Commands
The COLUMN Command

The HEADING Clause
The FORMAT Clause
The NOPRINT Clause
The NULL Clause
The CLEAR Clause

Predefined define variables
LOGIN.SQL
Command history
Copy and paste in SQL*Plus
Entering SQL commands
Entering PL/SQL commands
Entering SQL*Plus commands
Default output from SQL*Plus
Entering Queries
What about PL/SQL?

Using SQL Developer

Choosing a SQL Developer version
Configuring connections

Creating A Basic Connection
Creating A TNS Connection
Connecting

Configuring preferences
Using SQL Developer

The Columns Tab
The Data Tab
The Constraints Tab
The Grants Tab
The Statistics Tab
Other Tabs
Queries In SQL Developer
Query Builder
Accessing Objects Owned By Other Users
The Actions Pulldown Menu

Differences between SQL Developer and SQL*Plus

Reporting Commands Missing In SQL Developer
General Commands Missing In SQL Developer

Data Dictionary report
User Defined reports
Using scripts in SQL Developer

SQL Query Basics

Understanding the data dictionary
Exporting Key Data Dictionary Information
The Dictionary View
Components of a SELECT Statement
- The SELECT Clause
- The FROM Clause
- The WHERE Clause
- The GROUP BY Clause
- The HAVING Clause
- The ORDER BY Clause
- The START WITH And CONNECT BY Clauses
- The FOR UPDATE Clause
- Set Operators
Column Aliases
Fully Qualifying Tables and Columns
Table Aliases
Using DISTINCT and ALL in SELECT statements

WHERE and ORDER BY

WHERE clause basics
Comparison operators
Literals and Constants in SQL
Simple pattern matching
Logical operations
The DUAL table
Arithmetic operations
Expressions in SQL
Character operators
Pseudo columns
Order by clause basics
Ordering Nulls
Accent and case sensitive sorts
Sampling data
WHERE and ORDER BY in SQL Developer
All, Any, Some

Functions

The basics of Oracle functions
Number functions
Character functions
Date functions
Conversion functions
Other functions
Large object functions
Error functions
The RR format mode;
Leveraging your knowledge

ANSI 92 JOINS

Basics of ANSI 92 Joins
Using Query Builder with multiple tables
Table Aliases
Outer joins
Outer Joins In Query Builder
Set operators
Self-referential joins
Non-Equijoins

ANSI 99 Joins

Changes with ANSI99
CROSS Join
NATURAL Join
JOIN USING
JOIN ON
LEFT / RIGHT OUTER JOIN
FULL OUTER JOIN

GROUP BY and HAVING

Introduction to GROUP functions Limiting Rows
Including NULL
Using DISTINCT With Group Functions
GROUP function requirements
The HAVING clause
Other GROUP function rules
Using Query Builder with GROUP clauses
ROLLUP and CUBE
The Grouping function
Grouping Sets

Subqueries

Why use subqueries?
WHERE clause subqueries
FROM clause subqueries
HAVING clause subqueries
CORRELATED subqueries
SCALAR subqueries
DML and subqueries
EXISTS subqueries
Hierarchical queries
TOP N AND BOTTOM N queries
Creating subqueries using Query Builder

Regular Expressions

Available Regular Expressions
Regular Expression Operators
Character Classes
Pattern matching options
REGEX_LIKE
REGEXP_SUBSTR
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_COUNT

Analytics

The WITH clause
Reporting aggregate functions
Analytical functions
User-Defined bucket histograms
The MODEL clause
PIVOT and UNPIVOT
Temporal validity

More Analytics

RANKING functions
RANK
DENSE_RANK
CUME_DIST
PERCENT_RANK
ROW_NUMBER
Windowing aggregate functions
RATIO_TO_REPORT
LAG / LEAD
Linear Regression functions
Inverse Percentile functions
Hypothetical ranking functions
Pattern Matching

Basic computer skills are required. A basic knowledge of databases is desired but not required.

This in an introductory-level course geared for end users, data scientists, business analysts, application developers and database administrators new to SQL.

Take After: Our core training courses provide students with a solid foundation for continued learning based on role, goals, or their areas of specialty. Our learning paths offer a wide variety of follow-on courses such as:

Database or tooling topics (SQL Server, Oracle, OBIEE, TOAD, etc)
Database security topics
Data Analytics / Big Data training – Spark, Hadoop, etc.

Related Courses:

SQL Training Suite
SQL Programming Basics
Writing SQL Queries
Next Level SQL | Advanced SQL Programming
SQL Tuning

Introduction to SQL Programming Basics

Course Overview

Scheduled Classes

What You'll Learn

Outline

Prerequisites

Who Should Attend

Next Step Courses