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