SQL, or Structured Query Language, serves as the backbone of modern data management and manipulation. Understanding its principles allows technical professionals to create, maintain, and interrogate databases, giving them the power to make data-driven decisions that can shape the success of their organizations. From e-commerce platforms to complex inventory systems, SQL is the key to managing big data and transforming it into actionable insights.
Geared for experienced technical professionals, Introduction to SQL Programming is a three-day, hands-on course that will provide you with a solid understanding of SQL programming, providing you with the tools, techniques, and insights required to excel in database management and analysis.
Throughout the course, you'll learn how to handle data challenges with creativity and precision, learning how to extract, analyze, and interpret large datasets. This includes developing abilities to craft, interpret, and optimize SQL queries, analyze data, and implement effective database solutions, leading to informed, data-driven decision-making. You'll explore relational design principles, entity relationship diagrams, and the creation of efficient data models to represent complex relationships within data. The course also covers how to construct, interpret, and optimize SQL queries, including the use of functions, joins, subqueries, and advanced analytical techniques.
Through extensive lab work, you'll gain the foundational knowledge and practical skills required to tackle various data challenges with confidence and creativity. You'll exit the course equipped with the SQL knowledge and skills needed to craft, interpret, and optimize SQL queries, analyze data, and implement effective database solutions, leading to informed, data-driven decision-making within your organization.
Working in a hands-on learning environment, led by our expert instructor, you'll explore:
Audience
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 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
