Introduction to SQL Programming Basics

Course Objectives

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.

• 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

Course Prerequisites

This is an introductory- level SQL course, appropriate for anyone needing to interface with an Oracle database or those needing a general understanding of Oracle database functionality. That would include end users, business analysts, application developers and database administrators. Basic computer skills are required.  A basic knowledge of databases is desired but not required.

Take After: Our core SQl and database 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:

• Intermediate or Advanced SQL programming topics
• Database or tooling topics (SQL Server, Oracle, OBIEE, TOAD, etc)
• Database security topics
• Data Analytics / Big Data training – Spark, Hadoop, etc.
• Please contact us for recommended next steps tailored to your longer-term education, project, role or development objectives.

Course Agenda

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