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

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?

INTRODUCING SQLCL: THE DEATH OF SQL*PLUS?

• Installation
• Editing / Autocomplete
• SQLFORMAT ANSICONSOLE
• NEW COMMANDS
  • ALIAS
  • Search
  • APEX
  • BRIDGE
  • CD
  • CTAS
  • DDL
  • FIND
  • FORMAT
  • HISTORY
  • INFORMATION
  • LOAD
  • NET
  • OERR
  • REPEAT
  • REST
  • SODA
  • SSHTUNNEL
  • TNSPING
  • WHICH
  • MISCELLANEOUS

  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
  • 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

  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
  • 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

  CHAPTER 5 – 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

  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

  CHAPTER 6 – 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 6: FUNCTIONS

  LAB 6 SOLUTIONS: FUNCTIONS

  CHAPTER 7 – 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 7: ANSI 92 JOINS

  LAB 7: SOLUTIONS: ANSI 92 JOINS

  CHAPTER 8 – ANSI 99 JOINS

  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

  CHAPTER 9 – 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 9: GROUP BY AND HAVING

  LAB 9 SOLUTIONS: GROUP BY AND HAVING

  CHAPTER 10 – 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 10: SUBQUERIES

  LAB 10 SOLUTIONS: SUBQUERIES

  CHAPTER 11– 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 11: REGULAR EXPRESSIONS

  LAB 11 SOLUTIONS: REGULAR EXPRESSIONS

  CHAPTER 12 – ANALYTICS

  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

  CHAPTER 13 – 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

  CHAPTER 14 – 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 14: BASIC REPORTING

  LAB 14 SOLUTIONS: BASIC REPORTING

  CHAPTER 15 – SECURITY

  BASIC SECURITY

  • SYSTEM Privileges
  • OBJECT Privileges

  SCHEMA ONLY ACCOUNTS

  THE DATA DICTIONARY AND SECURITY

  USING ROLES FOR PRIVILEGE MANAGEMENT

  USING PROFILES

  • Kernel Limits
  • Password Limits
  • Creating And Using Profiles

  LAB 15: SECURITY

  LAB 15 SOLUTIONS: SECURITY

  CHAPTER 16– 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

  EXPORTING DATA SETS

  DATA VALIDATION

  A WORD ABOUT DATA PUMP

  LAB 16: DATA IMPORT AND EXPORT

  LAB 16 SOLUTIONS: DATA IMPORT AND EXPORT

  CHAPTER 17 – 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 17: DATA MANIPULATION

  LAB 17 SOLUTIONS: DATA MANIPULATION

  CHAPTER 18– 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 18: ADVANCED DATA MANIPULATION

  LAB 18 SOLUTIONS: ADVANCED DATA MANIPULATION

  CHAPTER 19– 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

  JSON SUPPORT

  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 19: INTRODUCTION TO DATA DEFINITION

  LAB 19 SOLUTIONS: INTRODUCTION TO DATA DEFINITION

  CHAPTER 20 – ADVANCED DATA DEFINITION

  DDL AND THE DATA DICTIONARY

  DISABLING CONSTRAINTS

  ENABLING CONSTRAINTS

  HANDLING CONSTRAINT EXCEPTIONS

  USING DEFERRABLE CONSTRAINTS

  SEQUENCES

  • Scalable Sequences

  EXTERNAL TABLES FOR DATA STORAGE

  • Why Are External Tables Useful
  • Privileges Needed
  • Syntax For Creating External Tables
  • Inline 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 20: ADVANCED DATA DEFINITION

  LAB 20 SOLUTIONS: ADVANCED DATA DEFINITION