Maxtrain.com - info@maxtrain.com - 513-322-8888 - 866-595-6863
NewTTSQL002
Introduction to SQL Programming Basics
Description
Introduction to SQL Programming Basics Course
Introduction to SQL Programming Basics Course Objectives
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 dive 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.
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.
Audience
- 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
Introduction to SQL Programming Basics Outline
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
$1995.00
|
3 Days Course |