Table of Contents

COURSE INTRODUCTION

ABOUT THIS TEXTBOOK

Objectives

Target Audience

Course Prerequisites

Suggested Next Courses

Certification Examination

Instructor-Led Training (ILT)/Live Virtual Training Schedule [kit]

Instructor-Led Training (ILT)/Live Virtual Training Schedule [custom module]

Learning Resources

Resource Files

ABOUT THE ELECTRONICS DATA MODEL

Understanding This Data Model

Business Narrative

The Tables

About The CUSTOMERS Table

About The PRODUCTS Table

About The SALES Table

About The TEAMS & MEMBERS Tables

TEAMS & MEMBERS Relationships

Informal Diagram

Logical Model

Physical Model

Sample Data

WORKSHOP SETUP GUIDE

ABOUT THE WORKSHOP SETUP

SECTION 1

ABOUT DATABASE TECHNOLOGIES & RELATIONAL DATABASES

SECTION OVERVIEW

ABOUT RELATIONAL DATABASES

Have I Already Used An RDBMS?

About SQL

A Bit More About The SQL Language

SQL Execution Illustrated

The Oracle RDBMS

RELATIONAL DATABASE TABLES

About The Table Structure

Relational Table Illustrated

ABOUT THE COLUMNS

About Column Data Types

Foreign Key & Primary Key Columns

ABOUT THE RELATIONSHIPS

Relationship Cardinality

1:N Relationships

M:N Relationships

Reflexive Relationships

1:1 Relationships

CHARACTERISTICS OF SQL

A Declarative Language

SQL Statement Categories

SQL Statements Summary

Using SQL

Introducing The SELECT Staement

SELECT Statement Structure

SELECT Statement Examples

Selecting All Columns

SQL Rules

NULL Values Within A Standard Column

NULL Values Within A Foreign Key

The Order Of Rows

SECTION REVIEW

SECTION 2

USING THE DATABASE INTERFACE

SECTION OVERVIEW

SELECTING THE APPROPRIATE TOOL

ABOUT DATABASE CONNECTIONS

User Authentication

Database Authentication Elements

Oracle Database Connections

ABOUT BIND VARIABLES

USING SQL DEVELOPER

Installation & Usage

Preparing The Execution Environment

Installing SQL Developer

Launch SQL Developer

Setting The JDK Path

Defining A Database Connection

Basic Connection Type

TNS Connection Type

Advanced Connection Type

Managing Connections

Using SQL Worksheet For SQL

Including Comments

Using the SQL Worksheet Tool Bar

Executing External Scripts

Using SQL Worksheet For PL/SQL

Using Bind Variables From SQL Developer

SQL*Plus Formatting Commands

USING SQL*PLUS

Launching The Tool

Entering Statements

Using CONNECT, DISCONNECT and EXIT

SQL Statement Example

About HOST or $

About SPOOL

Additional Spool Options

More About File Names

More About Script Files

About Profile .SQL Files

Sample LOGIN.SQL File

Additional SQL*Plus Commands

USING APPLICATION EXPRESS

Launching Application Express

Logon Screen Example

Entering SQL Statements Or PL/SQL Blocks

Using Bind Variables From Application Express

SECTION REVIEW

WORKSHOP SECTION

USING THE DATABASE INTERFACE

EXERCISES

What You Will Do Within This Workshop

Assumptions

SQL INTERFACE-1

SQL INTERFACE-2

SQL INTERFACE-3

SOLUTIONS

SQL INTERFACE-1

SQL INTERFACE-2

SQL INTERFACE-3

SECTION 3

ABOUT SQL & BUILDING A SELECT STATEMENT

SECTION OVERVIEW

Selection & Projection

SELECTING COLUMNS

The SELECT Clause

Listing Columns

Qualifying Column Names

Computational Column

Concatenated Column

DISTINCT / UNIQUE Option

USING ALIAS NAMES

Column Alias Names

Complex Alias Names

Table Alias Names

SECTION REVIEW

WORKSHOP SECTION

ABOUT SQL & BUILDING A SELECT STATEMENT

EXERCISES

What You Will Do In This Workshop

SELECT-1

SELECT-2

SELECT-3

SELECT-4

SELECT-5

SELECT-6

SELECT-7

SOLUTIONS

SELECT-1

SELECT-2

SELECT-3

SELECT-4

SELECT-5

SELECT-6

SELECT-7

SECTION 4

RESTRICTING DATA WITH THE WHERE CLAUSE

SECTION OVERVIEW

ABOUT LOGICAL OPERATORS

About The WHERE Clause

About The Logical Operators

About The Boolean Operators

EQUALITY OPERATOR

Equality Operator

Inequality Operator

Inequality Operator List

BOOLEAN OPERATORS

How The Operators Are Used

Boolean Operator Example

NULL & BETWEEN OPERATORS

IS [NOT] NULL Operator

[NOT] BETWEEN Operator

FINDING TEXT STRINGS

[NOT]LIKE Operator

The Wildcard Characters

LIKE Operator Example, Beginning String

LIKE Operator Example, Middle String

LIKE Operator Example, Single Character

REGEXP_LIKE()

What Are Regular Expressions?

Using The Function

Building The Regular Expression

IN OPERATOR

SECTION REVIEW

WORKSHOP SECTION

RESTRICTING DATA WITH THE WHERE CLAUSE

EXERCISES

What You Will Do In This Workshop

WHERE-1

WHERE-2

WHERE-3

WHERE-4

WHERE-5

WHERE-6

WHERE-7

SOLUTIONS

WHERE-1

WHERE-2

WHERE-3

WHERE-4

WHERE-5

WHERE-6

WHERE-7

SECTION 5

SORTING DATA WITH THE ORDER BY CLAUSE

SECTION OVERVIEW

ABOUT THE ORDER BY CLAUSE

Examples

Sorting By Un-selected Columns

MULTIPLE COLUMN SORTS

SPECIFYING THE SORT SEQUENCE

ABOUT NULL VALUES WITHIN SORTS

USING COLUMN ALIASES

Using The Ordinal Position

SECTION REVIEW

WORKSHOP SECTION

SORTING DATA WITH THE ORDER BY CLAUSE

EXERCISES

What You Will Do In This Workshop

ORDER-1

ORDER-2

ORDER-3

ORDER-4

ORDER-5

ORDER-6

SOLUTIONS

ORDER-1

ORDER-2

ORDER-3

ORDER-4

ORDER-5

ORDER-6

SECTION 6

PSEUDO COLUMNS & FUNCTIONS

SECTION OVERVIEW

USING ROWID

About RowID & The ROWID Pseudo Column

When Multiple Tables Are Accessed

Retrieving By ROWID

USING ROWNUM

About ROWNUM

Why Use ROWNUM?

USING THE FUNCTIONS

SYSDATE

USER & UID

USING THE DUAL TABLE

About The DUAL Table

Why Use DUAL?

SESSIONTIMEZONE FUNCTION

SECTION REVIEW

WORKSHOP SECTION

PSEUDO COLUMNS & FUNCTIONS

EXERCISES

What You Will Do In This Workshop

PSEUDO-1

PSEUDO-2

PSEUDO-3

PSEUDO-4

SOLUTIONS

PSEUDO-1

PSEUDO-2

PSEUDO-3

PSEUDO-4

SECTION 7

JOINING TABLES

SECTION OVERVIEW

About The ANSI/ISO SQL Standard

ABOUT JOINS

Why Joins Are Needed

Join Techniques

Discerning Between Different Techniques

INNER JOIN

Inner Join Scenario

Inner Join Solution

Another Inner Join Example

Understanding Inner Join Processing

How An Inner Join Is Processed

Inner Join Processing Demonstrated

Why An Inner Join May Not Be Helpful

Special Inner Join Situations

Removing Redundant Rows

Including Conditional Expressions

Multiple Inner Join Scenario

Multiple Inner Join Example

About The Cartesian Product

Cartesian Product Example

REFLEXIVE JOIN

Reflexive Join Illustrated

Reflexive Join Processing

Reflexive Join Processing Illustrated

Reflexive Join Example

NON-KEY JOIN

Non-Key Join Example

Combining Techniques

OUTER JOIN

Outer Join Solution

Outer Join Example

Isolating The Outer Join Rows

SECTION REVIEW

WORKSHOP SECTION

JOINING TABLES

EXERCISES

What You Will Do In This Workshop

Instructor Note

How To Approach This Workshop

JOIN-1

JOIN-2

JOIN-3

JOIN-4

JOIN-5

JOIN-6

JOIN-7

JOIN-8

JOIN-9

JOIN-10

SOLUTIONS

JOIN-1

JOIN-2

JOIN-3

JOIN-4

JOIN-5

JOIN-6

JOIN-7

JOIN-8

JOIN-9

JOIN-10

SECTION 8

USING THE SET OPERATORS

SECTION OVERVIEW

ABOUT THE SET OPERATORS

Example

What Are The Set Operators?

About Compatible Sets

SET OPERATOR EXAMPLES

UNION Example

Expanding The Column List

INTERSECT Example

MINUS Example

Subtracting From The Inverse Set

SECTION REVIEW

WORKSHOP SECTION

USING THE SET OPERATORS

EXERCISES

What You Will Do In This Workshop

SET-1

SET-2

SET-3

SET-4

SOLUTIONS

SET-1

SET-2

SET-3

SET-4

SECTION 9

SUMMARY FUNCTIONS

SECTION OVERVIEW

ABOUT SUMMARY FUNCTIONS

Summary Functions List

Examples

Why Do We Really Use Summary Functions?

SUMMARY FUNCTIONS WITH DISTINCT

SECTION REVIEW

WORKSHOP SECTION

SUMMARY FUNCTIONS

EXERCISES

What You Will Do In This Workshop

SUMMARY-1

SUMMARY-2

SUMMARY-3

SUMMARY-4

SOLUTIONS

SUMMARY-1

SUMMARY-2

SUMMARY-3

SUMMARY-4

SECTION 10

A USING SUB-QUERIES

SECTION OVERVIEW

FINDING DATA WITH SUB-QUERIES

Simple Sub-query Example

Sub-query Illustrated

Scalar Sub-query Expression

Inline View Sub-query

Summarizing Query Types

The Sub-query Operators

Sub-query Operator List

STANDARD SUB-QUERIES

Summary Functions Within Sub-queries

Finding The Extremes

Varying Query Results With Operators

CORRELATED SUB-QUERIES

What Is A Correlated Sub-query?

Non Correlated Sub query Example

Correlated Sub-query Example

The EXISTS Operator

About This Operator

EXISTS Example

NOT EXISTS Example

SECTION REVIEW

WORKSHOP SECTION

USING SUB-QUERIES

EXERCISES

What You Will Do In This Workshop

SUBQUERY-1

SUBQUERY-2

SUBQUERY-3

SUBQUERY-4

SUBQUERY-5

SUBQUERY-6

SUBQUERY-7

SUBQUERY-8

SUBQUERY-9

SUBQUERY10

SOLUTIONS

SUBQUERY-1

SUBQUERY-2

SUBQUERY-3

SUBQUERY-4

SUBQUERY-5

SUBQUERY-6

SUBQUERY-7

SUBQUERY-8

SUBQUERY-9

SUBQUERY-10

SECTION 11

AGGREGATING DATA WITHIN GROUPS

SECTION OVERVIEW

ABOUT SUMMARY GROUPS

SQL Without Summary Groups

SQL With Summary Groups

Grouping An Entire Table

Avoiding Errors

FINDING GROUPS WITHIN THE TABLES

Group By Team Example

Group By Gender Example

SELECTING DATA FROM THE BASE TABLES

Including Join Conditions

Adding Other Conditions

Top-N Queries

SELECTING GROUPS FROM THE RESULTS

About the HAVING Clause

Simple Example

HAVING Clause Illustrated

A Comprehensive Example

SECTION REVIEW

WORKSHOP SECTION

AGGREGATING DATA WITHIN GROUPS

EXERCISES

What You Will Do In This Workshop

GROUP-1

GROUP-2

GROUP-3

GROUP-4

GROUP-5

GROUP-6

GROUP-7

SOLUTIONS

GROUP-1

GROUP-2

GROUP-3

GROUP-4

GROUP-5

GROUP-6

GROUP-7

SECTION 12

BUILD SIMPLE SQL*PLUS REPORTS

SECTION OVERVIEW

FORMAT OUTPUT WITH COLUMN

FORMAT Clause

Alphabetic Characters

Numeric Characters

WORD_WRAPPED Example

TRUNC Example

HEADING Clause

NULL Option

DEFINING REPORT BREAKS WITH BREAK

BREAK Example

SKIP Option

PRODUCE SUBTOTALS WITH COMPUTE

COMPUTE Example

Computation Options

SECTION REVIEW

WORKSHOP SECTION

BUILD SIMPLE SQL*PLUS REPORTS

EXERCISES

What You Will Do In This Workshop

REPORT-1

REPORT-2

REPORT-3

SOLUTIONS

REPORT-1

REPORT-2

REPORT-3

SECTION 13

USE DATA DEFINITION LANGUAGE TO CREATE & MANAGE TABLES

SECTION OVERVIEW

CREATE TABLE STATEMENT

Object Name Rules

About Table Security

Create Using SELECT

Column Data Types

Selecting The Appropriate Column Data type

Character Data types

More About Unicode Types

Numeric Data types

Date & Time Data types

Specialized Data types

LOB Types

Legacy Data Types

Additional Column Options

NOT NULL

DEFAULT

Virtual Columns

ALTER TABLE STATEMENT

Drop Columns Examples

Add Columns Example

Renaming A Column

Modify A Column Data Type Example

Modify A Column Constraint Example

DROP TABLE STATEMENT

SUPPORTIVE STATEMENTS

DESCRIBE

RENAME

SECTION REVIEW

WORKSHOP SECTION

USE DATA DEFINITION LANGUAGE TO CREATE & MANAGE TABLES

EXERCISES

What You Will Do In This Workshop

DDL-1

DDL-2

DDL-3

DDL-4

SOLUTIONS

DDL-1

DDL-2

DDL-3

DDL-4

SECTION 14

USE DATA MANIPULATION LANGUAGE TO MANIPULATE DATA

SECTION OVERVIEW

How DML Statements Are Generally Used

ABOUT THE INSERT STATEMENT

Statement Rules

Implicit Column Names

Implicit Column Names Statement Rules

INSERT Using SELECT

ABOUT THE DELETE STATEMENT

Statement Structure

Statement Example

Statement Rules

ABOUT THE UPDATE STATEMENT

Statement Structure

Initial Example

Additional Examples

Specifying Column Values

ABOUT TRANSACTIONS

A Transaction Scenario

A Transaction Example

About The Transaction Control Statements

ROLLBACK

Pending Database Changes

Example Transaction With ROLLBACK

COMMIT

Example Transaction With COMMIT

About Implicit Commits

SAVEPOINT

Statement Structure

About The Transaction Scenario

Transaction Scenario Example

SET TRANSACTION

SET TRANSACTION READ ONLY Statement Rules

SET TRANSACTION READ ONLY Example

TRUNCATE TABLE

Statement Example

Difference From DELETE

Difference From DROP TABLE

COMPLEX TABLE REFERENCES

About Tables In A Production Environment

What Are Complex Table References?

More About Schemas

Schemas Illustrated

Referencing Schemas

Referencing Remote Databases

SECTION REVIEW

WORKSHOP SECTION

USE DATA MANIPULATION LANGUAGE TO MANIPULATE DATA

EXERCISES

What You Will Do In This Workshop

DML-1

DML-2

DML-3

DML-4

DML-5

DML-6

DML-7

SOLUTIONS

DML-1

DML-2

DML-3

DML-4

DML-5

DML-6

DML-7

APPENDIX A

REFERENCE INFORMATION

SQL STATEMENTS REFERENCE

SQL DDL Statements

SQL DML Statements

SQL Transaction Control Statements

SQL Operators

INDUSTRY REFERENCES