Course Content   SQL

CHAPTER 1   BASIC RDBMS PRINCIPLES

CHAPTER OVERVIEW

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 SUMMARY

CHAPTER 2   THE SQL LANGUAGE AND TOOLS

CHAPTER OVERVIEW

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 SUMMARY

CHAPTER 3   USING SQL DEVELOPER

CHAPTER OVERVIEW

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 79

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

CHAPTER 4   SQL QUERY BASICS

CHAPTER OVERVIEW

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 SUMMARY

CHAPTER 5   DATA MANIPULATION

CHAPTER OVERVIEW

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 SUMMARY

CHAPTER 6   WHERE AND ORDER BY

CHAPTER OVERVIEW

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 SUMMARY

CHAPTER 7   FUNCTIONS

CHAPTER OVERVIEW

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 SUMMARY

CHAPTER 8   ANSI 92 JOINS

CHAPTER OVERVIEW

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 SUMMARY

CHAPTER 9   ANSI 99 JOINS

CHAPTER OVERVIEW

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 SUMMARY

CHAPTER 10   GROUP BY AND HAVING

CHAPTER OVERVIEW

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 SUMMARY

CHAPTER 11   SUBQUERIES

CHAPTER OVERVIEW

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 SUMMARY

Course Content   PL/SQL

CHAPTER 1   PL/SQL PROGRAM STRUCTURE

OVERVIEW OF THIS CHAPTER

PL/SQL VS. SQL

PL/SQL ENGINES AVAILABLE

ANONYMOUS PL/SQL BLOCK STRUCTURE

OBJECT NAMING RULES

VARIABLE DECLARATIONS

AVAILABLE DATATYPES

• Scalar Datatypes
• Using Extended Datatypes
• Object Types

EXECUTABLE STATEMENTS

EXPRESSIONS

BLOCK LABELING

VARIABLE SCOPING RULES

COMMENTS IN PROGRAMS AND SCRIPTS

BASIC CODING STANDARDS

LAB 1: PL/SQL PROGRAM STRUCTURE

LAB 1 SOLUTIONS: PL/SQL PROGRAM STRUCTURE

SUMMARY OF THIS CHAPTER

CHAPTER 2   PL/SQL FLOW CONTROL

OVERVIEW OF THIS CHAPTER

CONDITIONAL CONTROL

COMPARISON OPERATORS

LOGICAL OPERATORS

• Truth Tables

REPETITION CONTROL

• The Simple Loop
• WHILE Loop
• FOR Loop
• CONTINUE Statements In Loops
• Step Loops

THE GOTO STATEMENT

CASE EXPRESSIONS / STATEMENTS

BIND VARIABLES

SUBSTITUTION VARIABLES

LAB 2: PL/SQL FLOW CONTROL

LAB 2 SOLUTIONS: PL/SQL FLOW CONTROL

SUMMARY OF THIS CHAPTER

CHAPTER 3   SQL DEVELOPER AND PL/SQL

OVERVIEW OF THIS CHAPTER

SQL DEVELOPER AND PL/SQL

CREATING AND EXECUTING SCRIPTS

LAB 3: SQL DEVELOPER AND PL/SQL

LAB 3 SOLUTIONS: SQL DEVELOPER AND PL/SQL

SUMMARY OF THIS CHAPTER

CHAPTER 4   SELECT INTO

OVERVIEW OF THIS CHAPTER

SELECTING SINGLE ROWS OF DATA

ANCHORING VARIABLES TO DATATYPES

DML IN PL/SQL

RETURNING   INTO

SEQUENCES IN PL/SQL

TRANSACTION CONTROL IN PL/SQL

AUTONOMOUS TRANSACTIONS

LAB 4: SELECT INTO

LAB 4 SOLUTIONS: SELECT INTO

SUMMARY OF THIS CHAPTER

CHAPTER 5   THE PL/SQL CURSOR

OVERVIEW OF THIS CHAPTER

DECLARING EXPLICIT CURSORS

OPENING AND CLOSING EXPLICIT CURSORS

USING EXPLICIT CURSORS TO RETRIEVE VALUES

EXPLICIT CURSOR ATTRIBUTES

USING A LOOP WITH AN EXPLICIT CURSOR

USING %ROWTYPE WITH CURSORS

THE CURSOR FOR LOOP

DBMS_OUTPUT

LAB 5: THE PL/SQL CURSOR

LAB 5 SOLUTIONS: THE PL/SQL CURSOR

SUMMARY OF THIS CHAPTER

CHAPTER 6   OPTIMIZATION

OVERVIEW OF THIS CHAPTER

TIMING PL/SQL

FOR UPDATE / WHERE CURRENT OF

LAB 6: OPTIMIZATION

LAB 6 SOLUTIONS: OPTIMIZATION

SUMMARY OF THIS CHAPTER

CHAPTER 7   PL/SQL EXCEPTION HANDLING

OVERVIEW OF THIS CHAPTER

THE EXCEPTION SECTION

ORACLE NAMED EXCEPTIONS

PRAGMA EXCEPTION_INIT

USER DEFINED EXCEPTIONS

• The Scope Of User-Defined Exceptions

RAISING NAMED EXCEPTIONS

EXCEPTION PROPAGATION

RAISING AN EXCEPTION AGAIN

LIFE AFTER AN EXCEPTION

WHEN OTHERS

TAKING YOUR BALL AND GOING HOME

DBMS_ERRLOG

LAB 7: PL/SQL EXCEPTION HANDLING

LAB 7 SOLUTIONS: PL/SQL EXCEPTION HANDLING

SUMMARY OF THIS CHAPTER

CHAPTER 8   STORED PROCEDURES

OVERVIEW OF THIS CHAPTER

PROCEDURES

BENEFITS OF STORED PROCEDURES

• Database Security
• Performance
• Productivity
• Portability

PARAMETERS AND STORED PROCEDURES

• Parameter Notation

STORED OBJECT CREATION

• Syntax For Creating A Procedure

COMPILATION ERRORS

VIEWING COMPILED CODE

DROPPING A PROCEDURE

THE ALTER COMMAND AND STORED PROCEDURES

LAB 8: STORED PROCEDURES

LAB 8 SOLUTIONS: STORED PROCEDURES

SUMMARY OF THIS CHAPTER

CHAPTER 9   CREATING FUNCTIONS IN PL/SQL

OVERVIEW OF THIS CHAPTER

FUNCTIONS

PURITY LEVELS

USING WHITE LISTS

OPTIMIZATIONS

PARALLEL_ENABLE

DETERMINISTIC FUNCTIONS

PL/SQL RESULT CACHE

NOCOPY

DBMS_OUTPUT IN FUNCTIONS

USING THE WITH CLAUSE FOR FUNCTIONS

PRAGMA UDF

PRAGMA INLINE

USING SQL DEVELOPER WITH STORED PROCEDURES

DEBUGGING

LAB 9: FUNCTIONS

LAB 9 SOLUTIONS: FUNCTIONS

SUMMARY OF THIS CHAPTER

CHAPTER 10   PACKAGES

OVERVIEW OF THIS CHAPTER

CREATING PACKAGES

PACKAGE BENEFITS

• Security
• Persistent State
• I/O Efficiency

A SIMPLE PACKAGE

OVERLOADING

BODILESS PACKAGES

SOURCE CODE ENCRYPTION

CREATING PACKAGES FROM PROCEDURES AND FUNCTIONS

LAB 10: PACKAGES

LAB 10 SOLUTIONS: PACKAGES

SUMMARY OF THIS CHAPTER

CHAPTER 11 -- CREATING DML TRIGGERS

OVERVIEW OF THIS CHAPTER

DML TRIGGERS

DML TRIGGER STRUCTURE

CONDITIONAL TRIGGERING PREDICATES

TRIGGERS FOR BUSINESS RULES ENFORCEMENT

MUTATING AND CONSTRAINING TABLES

COMPOUND TRIGGERS

CONTROLLING FIRING ORDER

DDL FOR TRIGGERS

VIEWING TRIGGER SOURCE

INSTEAD OF TRIGGERS

LAB 11: DML TRIGGERS

LAB 11 SOLUTIONS: DML TRIGGERS

SUMMARY OF THIS CHAPTER