Introduction to SQL Programming Basics

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