TTSQL002: Introduction to SQL Programming Basics
About this Course
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.
NOTE: This course provides a complete, hands-on introduction to SQL including the use of both SQL Developer and SQL*Plus in some cases, however Oracle tooling is not the focus of the course, and other tooling or database options may be available. Please advise if you want to use another tool in class and we can coordinate alternative options with you in advance of the delivery.
Audience Profile
Basic Computer Literacy: Familiarity with operating systems, file management, and general computer navigation to ensure a smooth transition into learning SQL tools and environments.
Understanding of Fundamental Data Concepts: A grasp of basic data concepts like tables, records, and fields would aid in understanding relational databases and how SQL operates within them.
Basic technical background: Although specific prior scripting experience isn’t required to attend, we recommend that you have a background in IT or other technical topics or skills
At Course Completion
Working in a hands-on learning environment, led by our expert instructor, you'll explore:
Basic RDBMS Principles: Learn the art of relational design, entity relationship diagrams, data domains, and more.
The SQL Language and Tools: Get acquainted with SQL*Plus, EZConnect, and other vital SQL and PL/SQL commands.
Using SQL Developer: Configure connections, understand different tabs, and become proficient in Query Builder.
Essential Query Mechanics: Grasp SQL query basics, functions, ANSI 92 Joins, ANSI 99 Joins, and delve into subqueries.
Advanced Analytics: Explore regular expressions, analytics, ranking functions, pattern matching, and more. Throughout the course, hands-on labs will enable participants to apply the learned concepts directly, simulating real-world projects. Whether designing intricate data structures or performing nuanced data analyses, these exercises provide a practical understanding of how SQL can be applied on the job.
Data Analysis Essentials: Discover how to extract, analyze, and interpret data, deriving insights that can guide organizational strategies.
Database Management: Learn to construct and manage sophisticated database systems, ensuring efficiency and reliability.
Versatile Tool Utilization: Master tools like SQL Developer and SQL*Plus, harnessing their full capabilities to optimize your database operations.
Need different skills or topics? If your team requires different topics or tools, additional skills or custom approach, this course may be further adjusted to accommodate. We offer additional SQL, database, data analytics and other related courses which may be blended with this course for a track that best suits your development objectives.
Outline
Please note that this list of topics is based on our standard course offering, evolved from typical industry uses and trends. We will work with you to tune this course and level of coverage to target the skills you need most. Course agenda, topics and labs are subject to adjust during live delivery in response to student skill level, interests and participation.
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
Prerequisites
Basic Computer Literacy: Familiarity with operating systems, file management, and general computer navigation to ensure a smooth transition into learning SQL tools and environments.
Understanding of Fundamental Data Concepts: A grasp of basic data concepts like tables, records, and fields would aid in understanding relational databases and how SQL operates within them.
Basic technical background: Although specific prior scripting experience isn’t required to attend, we recommend that you have a background in IT or other technical topics or skills