Oracle18c SQL (Three Days)
Course: OR18SQL3
Duration: 3 Days
Level: I
On our website at:
http://www.verhoef-training.com/courses/OR18SQL3.html
Course Summary
This course provides a complete, hands-on introduction to writing queries in Oracle SQL including the use of both SQL Developer and SQL*Plus. This coverage is appropriate for users of Oracle11g and higher. A full presentation of the basics of relational databases and their use are also covered.
Topics Covered In This Course
Course Content
C****************************************************
CHAPTER 1 -- BASIC RDBMS PRINCIPLES
C****************************************************
RELATIONAL DESIGN PRINCIPLES
ACCESSING DATA THROUGH A STRUCTURED QUERY LANGUAGE
ENTITY RELATIONSHIP DIAGRAMS
DATA DOMAINS
NULL VALUES
INDEXES
VIEWS
DENORMALIZATION
DATA MODEL REVIEW
LAB 1: BASIC RDBMS PRINCIPLES
LAB 1 SOLUTIONS: BASIC RDBMS PRINCIPLES
C*********************************************************
CHAPTER 2 -- THE SQL LANGUAGE AND TOOLS
C*********************************************************
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
WE HAVE HISTORY!
PERFORMANCE SETTINGS
CSV OPTION FOR MARKUP
FEEDBACK ONLY
SUPPORT FOR LONG IDENTIFIERS
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?
LAB 2: SQL LANGUAGE AND TOOLS
LAB 2 SOLUTIONS: SQL LANGUAGE AND TOOLS
C***********************************************
CHAPTER 3 -- USING SQL DEVELOPER
C***********************************************
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
- Saving Typing
- Query Builder
- Modifying Your Query With The Drawer Pullout
- 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
SPOOLING IN SQL DEVELOPER
DATA DICTIONARY REPORTS
USER DEFINED REPORTS
USING SCRIPTS IN SQL DEVELOPER
INTRODUCING SQLCL: THE DEATH OF SQL*PLUS?
- Installation
- Editing / Autocomplete
- SQLFORMAT ANSICONSOLE
- NEW COMMANDS
- ALIAS
- APEX
- BRIDGE
- CD
- CTAS
- DDL
- FIND
- FORMAT
- HISTORY
- INFORMATION
- LOAD
- REPEAT
- TNSPING
- WHICH
- MISCELLANEOUS
LAB 3: USING SQL DEVELOPER
LAB 3 SOLUTIONS: USING SQL DEVELOPER
C*****************************************
CHAPTER 4 -- SQL QUERY BASICS
C*****************************************
UNDERSTANDING THE DATA DICTIONARY
- Exporting Key Data Dictionary Information
- An Alternative Approach To A Quick ERD
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
A NEAT TRICK
USING DISTINCT AND ALL IN SELECT STATEMENTS
LAB 4: SQL QUERY BASICS
LAB 4 SOLUTIONS: SQL QUERY BASICS
C**********************************************
CHAPTER 5 -- WHERE AND ORDER BY
C**********************************************
WHERE CLAUSE BASICS
COMPARISON OPERATORS
LITERALS AND CONSTANTS IN SQL
SIMPLE PATTERN MATCHING
LOGICAL OPERATORS
THE DUAL TABLE
ARITHMETIC OPERATORS
EXPRESSIONS IN SQL
CHARACTER OPERATORS
PSEUDO COLUMNS
ORDER BY CLAUSE BASICS
ORDERING NULLS
ACCENT AND CASE INSENSITIVE SORTS
DATA BOUND COLLATION
CASE-INSENSITIVE DATABASE
SAMPLING DATA
WHERE AND ORDER BY IN SQL DEVELOPER
ALL, ANY, SOME
LAB 5: WHERE AND ORDER BY
LAB 5 SOLUTIONS: WHERE AND ORDER BY
C*******************************
CHAPTER 6 -- FUNCTIONS
C*******************************
THE BASICS OF ORACLE FUNCTIONS
NUMBER FUNCTIONS
CHARACTER FUNCTIONS
DATE FUNCTIONS
CONVERSION FUNCTIONS
OTHER FUNCTIONS
LARGE OBJECT FUNCTIONS
ERROR FUNCTIONS
THE RR FORMAT MODEL
LEVERAGING YOUR KNOWLEDGE
LAB 6: FUNCTIONS
LAB 6 SOLUTIONS: FUNCTIONS
C**********************************
CHAPTER 7 -- ANSI 92 JOINS
C**********************************
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
LAB 7: ANSI 92 JOINS
LAB 7: SOLUTIONS: ANSI 92 JOINS
C***********************************
CHAPTER 8 -- ANSI 99 JOINS
C***********************************
CHANGES WITH ANSI99
CROSS JOIN
NATURAL JOIN
JOIN USING
JOIN ON
LEFT / RIGHT OUTER JOIN
FULL OUTER JOIN
LAB 8: ANSI 99 JOINS
LAB 8 SOLUTIONS: ANSI 99 JOINS
C**********************************************
CHAPTER 9 -- GROUP BY AND HAVING
C***********************************************
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
LAB 9: GROUP BY AND HAVING
LAB 9 SOLUTIONS: GROUP BY AND HAVING
C**********************************
CHAPTER 10-- SUBQUERIES
C**********************************
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
LAB 10: SUBQUERIES
LAB 10 SOLUTIONS: SUBQUERIES
C************************************************
CHAPTER 11-- REGULAR EXPRESSIONS
C************************************************
AVAILABLE REGULAR EXPRESSION FUNCTIONS
REGULAR EXPRESSION OPERATORS
CHARACTER CLASSES
PATTERN MATCHING OPTIONS
REGEX_LIKE
REGEXP_SUBSTR
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_COUNT
LAB 11: REGULAR EXPRESSIONS
LAB 11 SOLUTIONS: REGULAR EXPRESSIONS
C********************************
CHAPTER 12 -- ANALYTICS
C********************************
THE WITH CLAUSE
REPORTING AGGREGATE FUNCTIONS
ANALYTICAL FUNCTIONS
USER-DEFINED BUCKET HISTOGRAMS
THE MODEL CLAUSE
PIVOT AND UNPIVOT
TEMPORAL VALIDITY
LAB 12: ANALYTICS
LAB 12 SOLUTIONS: ANALYTICS
C**********************************
CHAPTER 13 -- ANALYTICS II
C**********************************
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
What You Can Expect
To cover a complete, hands-on introduction to SQL including the use of both SQL Developer and SQL*Plus. presentation of the basics of relational databases and their use. This includes anyone needing to interface with an Oracle database or those needing a general understanding of Oracle database functionality.
Who Should Take This Course
This course is appropriate for anyone needing to write queries in 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.
Recommended Prerequisites
Basic computer skills are needed. A basic knowledge of databases is desired but not required.
Training Style
Lecture and Hands on Labs
Related Courses
Code |
Course Title |
Duration |
Level |
|
OR18DBA3 |
Oracle 18c DBA I (Three Days) |
3 Days |
I |
Details |
OR18PLS3 |
Oracle 18c Release 2 PL/SQL (Three Days) |
3 Days |
I |
Details |
OR18PLS5 |
Oracle 18c PL/SQL (Five Days) |
5 Days |
I |
Details |
Every student attending a Verhoef Training class will receive
a certificate good for $100 toward their next public class taken
within a year.
You can also buy "Verhoef Vouchers" to get a discounted rate for a
single student in any of our public or web-based classes.
Contact your account manager or our sales office for details.