Introduction to SQL Programming Basics (TTSQL002)

3 Days

18 CPD hours

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.

Working in a hands on learning environment led by our expert practitioner, 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
Subqueries
Regular Expressions
Analytics

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.

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

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

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

Additional course details:

Nexus Humans Introduction to SQL Programming Basics (TTSQL002) training program is a workshop that presents an invigorating mix of sessions, lessons, and masterclasses meticulously crafted to propel your learning expedition forward.