In his 5 day instructor led class students will have a hands-on introduction to SQL including the use of both SQL Developer and SQL*Plus. This coverage is appropriate for both users of Oracle12c and Oracle11g. A full presentation of the basics of relational databases and their use are also covered.
Target Audience
This course is 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.
Prerequisites
Basic computer skills are needed. A basic knowledge of databases is desired but not required.
Course Content
CHAPTER 1 – 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
LAB 1: BASIC RDBMS PRINCIPLES
LAB 1 SOLUTIONS: BASIC RDBMS PRINCIPLES
CHAPTER 2 – 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?
LAB 2: SQL LANGUAGE AND TOOLS
LAB 2 SOLUTIONS: SQL LANGUAGE AND TOOLS
CHAPTER 3 – 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 REPORTS
USER DEFINED REPORTS
USING SCRIPTS IN SQL DEVELOPER
LAB 3: USING SQL DEVELOPER
LAB 3 SOLUTIONS: USING SQL DEVELOPER
CHAPTER 4 – 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
LAB 4: SQL QUERY BASICS
LAB 4 SOLUTIONS: SQL QUERY BASICS
CHAPTER 5 – DATA MANIPULATION
THE DATA MANIPULATION LANGUAGE
· The INSERT Command
· The UPDATE Command
· The DELETE Command
· Using The DEFAULT Keyword With Updates And Inserts
USING SQL DEVELOPER FOR DML
THE TRANSACTION CONTROL LANGUAGE (TCL)
IMPLICIT TCL
LAB 5: DATA MANIPULATION
LAB 5 SOLUTIONS: DATA MANIPULATION
CHAPTER 6 — WHERE AND ORDER BY
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
SAMPLING DATA
WHERE AND ORDER BY IN SQL DEVELOPER
ALL, ANY, SOME
LAB 6: WHERE AND ORDER BY
LAB 6 SOLUTIONS: WHERE AND ORDER BY
CHAPTER 7 – FUNCTIONS
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 7: FUNCTIONS
LAB 7 SOLUTIONS: FUNCTIONS
CHAPTER 8 – 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
LAB 8: ANSI 92 JOINS
LAB 8 SOLUTIONS: ANSI 92 JOINS
CHAPTER 9 – ANSI 99 JOINS
CHANGES WITH ANSI99
CROSS JOIN
NATURAL JOIN
JOIN USING
JOIN ON
LEFT / RIGHT OUTER JOIN
FULL OUTER JOIN
LAB 9: ANSI 99 JOINS
LAB 9 SOLUTIONS: ANSI 99 JOINS
CHAPTER 10 – 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
LAB 10: GROUP BY AND HAVING
LAB 10 SOLUTIONS: GROUP BY AND HAVING
CHAPTER 11 – 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
LAB 11: SUBQUERIES
LAB 11 SOLUTIONS: SUBQUERIES
CHAPTER 12 – BASIC REPORTING
BASIC REPORTING
· The COLUMN Command
· Setting Column Width
PRINT | NOPRINT
TTITLE | BTITLE
REPHEADER / REPFOOTER
NEW_VALUE / OLD_VALUE
THE COMPUTE COMMAND
COMMENTS IN SCRIPT FILES
SUBSTITUTION VARIABLES
· Named Substitution Variables
· Numbered Substitution Variables
· Dealing With Multiple References
· Using The DEFINE Command
· The ACCEPT And PROMPT Commands
RUNNING SCRIPTS UNATTENDED
LAB 12: BASIC REPORTING
LAB 12 SOLUTIONS: BASIC REPORTING
CHAPTER 13 – DATA IMPORT AND EXPORT
USING SQL*LOADER WITH FIELD DELIMITED DATA
USING SQL*LOADER WITH COMMA DELIMITED DATA
DATA LOADING USING SQL DEVELOPER
EXPORTING ORACLE DATA INTO EXCEL
DOING AN ODBC QUERY
A WORD ABOUT DATA PUMP
LAB 13: DATA IMPORT AND EXPORT
LAB 13 SOLUTIONS: DATA IMPORT AND EXPORT
CHAPTER 14 – SECURITY
BASIC SECURITY
· SYSTEM Privileges
· OBJECT Privileges
THE DATA DICTIONARY AND SECURITY
USING ROLES FOR PRIVILEGE MANAGEMENT
USING PROFILES
· Kernel Limits
· Password Limits
· Creating And Using Profiles
LAB 14: SECURITY
LAB 14 SOLUTIONS: SECURITY
CHAPTER 15 – ADVANCED DATA MANIPULATION
THE MERGE COMMAND
MULTIPLE COLUMN SUBQUERY UPDATES AND DELETES
DML AGAINST VIEWS
TRANSACTIONS AND READ CONSISTENCY
DML LOCKS
FLASHBACK TECHNOLOGIES
INSERTING LARGE OBJECTS
CHANGED DATA TRACKING
· Flashback Versions Query
· Log Miner
· Change Data Capture
· Flashback Data Archive
LAB 15: ADVANCED DATA MANIPULATION
LAB 15 SOLUTIONS: ADVANCED DATA MANIPULATION
CHAPTER 16 – INTRODUCTION TO DATA DEFINITION
INTRODUCTION TO DDL COMMANDS
KEY OBJECTS
OBJECT NAMING RULES
THE DATA DICTIONARY
AVAILABLE DATATYPES
· Using Extended Datatypes
THE CREATE TABLE STATEMENT
NAMING CONSTRAINTS
INTEGRITY CONSTRAINTS
· Primary Keys
· Foreign Keys
· NOT NULL Constraints
· UNIQUE Constraints
· CHECK Constraints
· DEFAULT Values
IDENTITY Columns
CONSTRAINTS AND CREATE TABLE… AS SELECT
CONSTRAINT LIMITATIONS
CREATING TABLES IN SQL DEVELOPER
OTHER DDL ACTIONS IN SQL DEVELOPER
THE ALTER TABLE COMMAND
DROPPING OBJECTS
RENAMING OBJECTS
THE TRUNCATE COMMAND
THE COMMENT COMMAND
CREATING SIMPLE VIEWS
LAB 16: INTRODUCTION TO DATA DEFINITION
LAB 16 SOLUTIONS: INTRODUCTION TO DATA DEFINITION
CHAPTER 17 – ADVANCED DATA DEFINITION
DDL AND THE DATA DICTIONARY
DISABLING CONSTRAINTS
ENABLING CONSTRAINTS
HANDLING CONSTRAINT EXCEPTIONS
USING DEFERRABLE CONSTRAINTS
SEQUENCES
EXTERNAL TABLES FOR DATA STORAGE
· Why Are External Tables Useful
· Privileges Needed
· Syntax For Creating External Tables
EXTERNAL TABLES AND THE ORACLE_DATAPUMP DRIVER
INDEXES
· Guidelines
· Index Creation Syntax
· Rebuilding Indexes
· Function Based Indexes
COMMENTS
SYNONYMS
· CREATE SYNONYM Syntax
COMPLEX VIEWS
· Syntax For Views
VIRTUAL COLUMNS
COMPRESSED TABLES
INVISIBLE INDEXES
ONLINE DDL ENHANCEMENTS
INVISIBLE COLUMNS
CREATING MULTIPLE INDEXES ON COLUMNS
LAB 17: ADVANCED DATA DEFINITION
LAB 17 SOLUTIONS: ADVANCED DATA DEFINITION
CHAPTER 18 – REGULAR EXPRESSIONS
AVAILABLE REGULAR EXPRESSION FUNCTIONS
REGULAR EXPRESSION OPERATORS
CHARACTER CLASSES
PATTERN MATCHING OPTIONS
REGEX_LIKE
REGEXP_SUBSTR
REGEXP INSTR
REGEXP_REPLACE
REGEXP_COUNT
LAB 18: REGULAR EXPRESSIONS
LAB 18 SOLUTIONS: REGULAR EXPRESSIONS
CHAPTER 19 – ANALYTICS
THE WITH CLAUSE
REPORTING AGGREGATE FUNCTIONS
ANALYTICAL FUNCTIONS
USER-DEFINED BUCKET HISTOGRAMS
THE MODEL CLAUSE
PIVOT AND UNPIVOT
TEMPORAL VALIDITY
LAB 19: ANALYTICS
LAB 19 SOLUTIONS: ANALYTICS
CHAPTER 20 – ANALYTICS II
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
To Hire a proven Oracle 12c SQL Subject Matter Expert Consultant / Course Author and Instructor who teaches this class, call 800-798-3901 today!