Introduction to SQL Programming Basics (Oracle Edition) (TTSQL002)

1. 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

2. 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
• 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?

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

4. 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

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

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

7. 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

8. ANSI 99 Joins

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

9. 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

10. 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
• Lab: Subqueries

11. Regular Expressions

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

12. Analytics

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

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

This hands-on course focuses on ‘learning by doing’, combining expert lecture, practical demonstrations and group discussions with plenty of machine-based real-world programming labs and exercises. Student machines are required.