Oracle 9i Introduction to SQL Part III
Objectives I-2
Oracle9i I-3
Oracle9i Application Server I-5
Oracle9i Database I-6
Oracle9i: Object Relational Database Management System I-8
Oracle Internet Platform I-9
System Development Life Cycle I-10
Data Storage on Different Media I-12
Relational Database Concept I-13
Definition of a Relational Database I-14
Data Models I-15
Entity Relationship Model I-16
Entity Relationship Modeling Conventions I-17
Relating Multiple Tables I-19
Relational Database Terminology I-20
Relational Database Properties I-21
Communicating with a RDBMS Using SQL I-22
Relational Database Management System I-23
SQL Statements I-24
Tables Used in the Course I-25
Summary I-26
1 Writing Basic SQL SELECT Statements
Objectives 1-2
Capabilities of SQL SELECT Statements 1-3
Basic SELECT Statement 1-4
Contents
iii
Selecting All Columns 1-5
Selecting Specific Columns 1-6
Writing SQL Statements 1-7
Column Heading Defaults 1-8
Arithmetic Expressions 1-9
Using Arithmetic Operators 1-10
Operator Precedence 1-11
Using Parentheses 1-13
Defining a Null value 1-14
Null values in Arithmetic Expressions 1-15
Defining a Column Alias 1-16
Using Column Aliases 1-17
Concatenation Operator 1-18
Using the Concatenation Operator 1-19
Literal Character Strings 1-20
Using Literal Character Strings 1-21
Duplicate Rows 1-22
Eliminating Duplicate Rows 1-23
SQL and iSQL*Plus Interaction 1-24
SQL Statements versus iSQL*Plus Commands 1-25
Overview of iSQL*Plus 1-26
Logging In to iSQL*Plus 1-27
The iSQL*Plus Environment 1-28
Displaying Table Structure 1-29
Interacting with s cript Files 1-31
Summary 1-34
Practice 1 Overview 1-35
iv
2 Restricting and Sorting Data
Objectives 2-2
Limiting Rows Using a Selection 2-3
Limiting the Rows Selected 2-4
Using the WHERE Clause 2-5
Character Strings and Dates 2-6
Comparison Conditions 2-7
Using Comparison Conditions 2-8
Other Comparison Conditions 2-9
Using the BETWEEN Condition 2-10
Using the IN Condition 2-11
Using the LIKE Condition 2-12
Using the NULL Conditions 2-14
Logical Conditions 2-15
Using the AND Operator 2-16
Using the OR Operator 2-17
Using the NOT Operator 2-18
Rules of Precedence 2-19
ORDER BY Clause 2-22
Sorting in Descending Order 2-23
Sorting by Column Alias 2-24
Sorting by Multiple Columns 2-25
Summary 2-26
Practice 2 Overview 2-27
v
3 Single-Row Functions
Objectives 3-2
SQL Functions 3-3
Two Types of SQL Functions 3-4
Single-Row Functions 3-5
Character Functions 3-7
Case Manipulation Functions 3-9
Using Case Manipulation Functions 3-10
Character-Manipulation Functions 3-11
Using the Character-Manipulation Functions 3-12
Number Functions 3-13
Using the ROUND Function 3-14
Using the TRUNC Function 3-15
Using the MOD Function 3-16
Working with Dates 3-17
Arithmetic with Dates 3-19
Using Arithmetic Operators with Dates 3-20
Date Functions 3-21
Using Date Functions 3-22
Practice 3, Part 1 Overview 3-24
Conversion Functions 3-25
Implicit Data-Type Conversion 3-26
Explicit Data-Type Conversion 3-28
Using the TO_CHAR Function with Dates 3-31
Elements of the Date Format Model 3-32
Using the TO_CHAR Function with Dates 3-36
vi
vii
Using the TO_CHAR Function with Numbers 3-37
Using the TO_NUMBER and TO_DATE Functions 3-39
RR Date Format 3-40
Example of RR Date Format 3-41
Nesting Functions 3-42
General Functions 3-44
NVL Function 3-45
Using the NVL Function 3-46
Using the NVL2 Function 3-47
Using the NULLIF Function 3-48
Using the COALESCE Function 3-49
Conditional Expressions 3-51
The CASE Expression 3-52
Using the CASE Expression 3-53
The DECODE Function 3-54
Using the DECODE Function 3-55
Summary 3-57
Practice 3, Part 2 Overview 3-58
4 Displaying Data from Multiple Tables
Objectives 4-2
Obtaining Data from Multiple Tables 4-3
Cartesian Products 4-4
Generating a Cartesian Product 4-5
Types of Joins 4-6
Joining Tables Using Oracle Syntax 4-7
What Is an Equijoin? 4-8
Retrieving Records with Equijoins 4-9
Additional Search Conditions Using the AND Operator 4-10
Qualifying Ambiguous Column Names 4-11
Using Table Aliases 4-12
Joining More than Two Tables 4-13
Nonequijoins 4-14
Retrieving Records with Nonequijoins 4-15
Outer Joins 4-16
Outer Joins Syntax 4-17
Using Outer Joins 4-18
Self Joins 4-19
Joining a Table to Itself 4-20
Practice 4, Part 1 Overview 4-21
Joining Tables Using SQL: 1999 Syntax 4-22
Creating Cross Joins 4-23
Creating Natural Joins 4-24
Retrieving Records with Natural Joins 4-25
Creating Joins with the USING Clause 4-26
Retrieving Records with the USING Clause 4-27
Creating Joins with the ON Clause 4-28
Retrieving Records with the ON Clause 4-29
Creating Three-Way Joins with the ON Clause 4-30
INNER versus OUTER Joins 4-31
LEFT OUTER JOIN 4-32
RIGHT OUTER JOIN 4-33
viii
FULL OUTER JOIN 4-34
Additional Conditions 4-35
Summary 4-36
Practice 4, Part 2 Overview 4-37
5 Aggregating Data Using Group Functions
Objectives 5-2
What Are Group Functions? 5-3
Types of Group Functions 5-4
Group Functions Syntax 5-5
Using the AVG and SUM Functions 5-6
Using the MIN and MAX Functions 5-7
Using the COUNT Function 5-8
Using the DISTINCT Keyword 5-10
Group Functions and Null values 5-11
Using the NVL Function with Group Functions 5-12
Creating Groups of Data 5-13
Creating Groups of Data: GROUP BY Clause Syntax 5-14
Using the GROUP BY Clause 5-15
Grouping by More Than One Column 5-17
Using the GROUP BY Clause on Multiple Columns 5-18
Illegal Queries Using Group Functions 5-19
Excluding Group Results 5-21
Excluding Group Results: The HAVING Clause 5-22
Using the HAVING Clause 5-23
Nesting Group Functions 5-25
Summary 5-26
Practice 5 Overview 5-27
ix
6 Subqueries
Objectives 6-2
Using a Subquery to Solve a Problem 6-3
Subquery Syntax 6-4
Using a Subquery 6-5
Guidelines for Using Subqueries 6-6
Types of Subqueries 6-7
Single-Row Subqueries 6-8
Executing Single-Row Subqueries 6-9
Using Group Functions in a Subquery 6-10
The HAVING Clause with Subqueries 6-11
What Is Wrong with This Statement? 6-12
Will This Statement Return Rows? 6-13
Multiple-Row Subqueries 6-14
Using the ANY Operator in Multiple-Row Subqueries 6-15
Using the ALL Operator in Multiple-Row Subqueries 6-16
Null values in a Subquery 6-17
Summary 6-18
Practice 6 Overview 6-19
7 Producing Readable Output with iSQL*Plus
Objectives 7-2
Substitution Variables 7-3
Using the & Substitution Variable 7-5
Character and Date values with Substitution Variables 7-7
Specifying Column Names, Expressions, and Text 7-8
x
Defining Substitution Variables 7-10
DEFINE and UNDEFINE Commands 7-11
Using the DEFINE Command with & Substitution Variable 7-12
Using the VERIFY Command 7-14
Customizing the iSQL*Plus Environment 7-15
SET Command Variables 7-16
iSQL*Plus Format Commands 7-17
The COLUMN Command 7-18
Using the COLUMN Command 7-19
COLUMN Format Models 7-20
Using the BREAK Command 7-21
Using the TTITLE and BTITLE Commands 7-22
Creating a s cript File to Run a Report 7-23
Sample Report 7-25
Summary 7-26
Practice 7 Overview 7-27
8 Manipulating Data
Objectives 8-2
Data Manipulation Language 8-3
Adding a New Row to a Table 8-4
The INSERT Statement Syntax 8-5
Inserting New Rows 8-6
Inserting Rows with Null values 8-7
Inserting Special values 8-8
Inserting Specific Date values 8-9
xi
Creating a s cript 8-10
Copying Rows from Another Table 8-11
Changing Data in a Table 8-12
The UPDATE Statement Syntax 8-13
Updating Rows in a Table 8-14
Updating Two Columns with a Subquery 8-15
Updating Rows Based on Another Table 8-16
Updating Rows: Integrity Constraint Error 8-17
Removing a Row from a Table 8-18
The DELETE Statement 8-19
Deleting Rows from a Table 8-20
Deleting Rows Based on Another Table 8-21
Deleting Rows: Integrity Constraint Error 8-22
Using a Subquery in an INSERT Statement 8-23
Using the WITH CHECK OPTION Keyword on DML Statements 8-25
Overview of the Explict Default Feature 8-26
Using Explicit Default values 8-27
The MERGE Statement 8-28
MERGE Statement Syntax 8-29
Merging Rows 8-30
Database Transactions 8-32
Advantages of COMMIT and ROLLBACK Statements 8-34
Controlling Transactions 8-35
Rolling Back Changes to a Marker 8-36
Implicit Transaction Processing 8-37
State of the Data Before COMMIT or ROLLBACK 8-38
State of the Data After COMMIT 8-39
xii
Committing Data 8-40
State of the Data After ROLLBACK 8-41
Statement-Level Rollback 8-42
Read Consistency 8-43
Implementation of Read Consistency 8-44
Locking 8-45
Implicit Locking 8-46
Summary 8-47
Practice 8 Overview 8-48
9 Creating and Managing Tables
Objectives 9-2
Database Objects 9-3
Naming Rules 9-4
The CREATE TABLE Statement 9-5
Referencing Another User’s Tables 9-6
The DEFAULT Option 9-7
Creating Tables 9-8
Tables in the Oracle Database 9-9
Querying the Data Dictionary 9-10
Data Types 9-11
Datetime Data Types 9-13
TIMESTAMP WITH TIME ZONE Data Type 9-15
TIMESTAMP WITH LOCAL TIME Data Type 9-16
INTERVAL YEAR TO MONTH Data Type 9-17
Creating a Table by Using a Subquery Syntax 9 -18
xiii
Creating a Table by Using a Subquery 9-19
The ALTER TABLE Statement 9-20
Adding a Column 9-22
Modifying a Column 9-24
Dropping a Column 9-25
The SET UNUSED Option 9-26
Dropping a Table 9-27
Changing the Name of an Object 9-28
Truncating a Table 9-29
Adding Comments to a Table 9-30
Summary 9-31
Practice 9 Overview 9-32
10 Including Constraints
Objectives 10-2
What Are Constraints? 10-3
Constraint Guidelines 10-4
Defining Constraints 10-5
The NOT NULL Constraint 10-7
The UNIQUE Constraint 10-9
The PRIMARY KEY Constraint 10-11
The FOREIGN KEY Constraint 10-13
FOREIGN KEY Constraint Keywords 10-15
The CHECK Constraint 10-16
Adding a Constraint Syntax 10-17
Adding a Constraint 10-18
Dropping a Constraint 10-19
xiv
Disabling Constraints 10-20
Enabling Constraints 10-21
Cascading Constraints 10-22
Viewing Constraints 10-24
Viewing the Columns Associated with Constraints 10-25
Summary 10-26
Practice 10 Overview 10-27
11 Creating Views
Objectives 11-2
Database Objects 11-3
What Is a View? 11-4
Why Use Views? 11-5
Simple Views and Complex Views 11-6
Creating a View 11-7
Retrieving Data from a View 11-10
Querying a View 11-11
Modifying a View 11-12
Creating a Complex View 11-13
Rules for Performing DML Operations on a View 11-14
Using the WITH CHECK OPTION Clause 11-17
Denying DML Operations 11-18
Removing a View 11-20
Inline Views 11-21
Top-n Analysis 11-22
Performing Top-n Analysis 11-23
xv
Example of Top-n Analysis 11-24
Summary 11-25
Practice 11 Overview 11-26
12 Other Database Objects
Objectives 12-2
Database Objects 12-3
What Is a Sequence? 12-4
The CREATE SEQUENCE Statement Syntax 12-5
Creating a Sequence 12-6
Confirming Sequences 12-7
NEXTVAL and CURRVAL Pseudocolumns 12-8
Using a Sequence 12-10
Modifying a Sequence 12-12
Guidelines for Modifying a Sequence 12-13
Removing a Sequence 12-14
What Is an Index? 12-15
How Are Indexes Created? 12-16
Creating an Index 12-17
When to Create an Index 12-18
When Not to Create an Index 12-19
Confirming Indexes 12-20
Function-Based Indexes 12-21
Removing an Index 12-22
Synonyms 12-23
xvi
Creating and Removing Synonyms 12-24
Summary 12-25
Practice 12 Overview 12-26
13 Controlling User Access
Objectives 13-2
Controlling User Access 13-3
Privileges 13-4
System Privileges 13-5
Creating Users 13-6
User System Privileges 13-7
Granting System Privileges 13-8
What Is a Role? 13-9
Creating and Granting Privileges to a Role 13-10
Changing Your Password 13-11
Object Privileges 13-12
Granting Object Privileges 13-14
Using the WITH GRANT OPTION and PUBLIC Keywords 13-15
Confirming Privileges Granted 13-16
How to Revoke Object Privileges 13-17
Revoking Object Privileges 13-18
Database Links 13-19
Summary 13-21
Practice 13 Overview 13-22
14 SQL Workshop Workshop Overview
Workshop Overview 14-2
xvii
15 Using SET Operators
Objectives 15-2
The SET Operators 15-3
Tables Used in This Lesson 15-4
The UNION SET Operator 15-7
Using the UNION Operator 15-8
The UNION ALL Operator 15-10
Using the UNION ALL Operator 15-11
The INTERSECT Operator 15-12
Using the INTERSECT Operator 15-13
The MINUS Operator 15-14
SET Operator Guidelines 15-16
The Oracle Server and SET Operators 15-17
Matching the SELECT Statements 15-18
Controlling the Order of Rows 15-20
Summary 15-21
Practice 15 Overview 15-22
16 Oracle 9i Datetime Functions
Objectives 16-2
TIME ZONES 16-3
Oracle 9i Datetime Support 16-4
CURRENT_DATE 16-6
CURRENT_TIMESTAMP 16-7
LOCALTIMESTAMP 16-8
DBTIMEZONE and SESSIONTIMEZONE 16-9
xviii
EXTRACT 16-10
FROM_TZ 16-11
TO_TIMESTAMP and TO_TIMESTAMP_TZ 16-12
TO_YMINTERVAL 16-13
TZ_OFFSET 16-14
Summary 16-16
Practice 16 Overview 16-17
17 Enhancements to the GROUP BY Clause
Objectives 17-2
Review of Group Functions 17-3
Review of the GROUP BY Clause 17-4
Review of the HAVING Clause 17-5
GROUP BY with ROLLUP and CUBE Operators 17-6
ROLLUP Operator 17-7
ROLLUP Operator Example 17-8
CUBE Operator 17-9
CUBE Operator: Example 17-10
GROUPING Function 17-11
GROUPING Function: Example 17-12
GROUPING SETS 17-13
GROUPING SETS: Example 17-15
Composite Columns 17-17
Composite Columns: Example 17-19
Concatenated Groupings 17-21
xix
Concatenated Groupings Example 17-22
Summary 17-23
Practice 17 Overview 17-24
18 Advanced Subqueries
Objectives 18-2
What Is a Subquery? 18-3
Subqueries 18-4
Using a Subquery 18-5
Multiple-Column Subqueries 18-6
Column Comparisons 18-7
Pairwise Comparison Subquery 18-8
Nonpairwise Comparison Subquery 18-9
Using a Subquery in the FROM Clause 18-10
Scalar Subquery Expressions 18-11
Correlated Subqueries 18-14
Using Correlated Subqueries 18-16
Using the EXISTS Operator 18-18
Using the NOT EXISTS Operator 18-20
Correlated UPDATE 18-21
Correlated DELETE 18-24
The WITH Clause 18-26
WITH Clause: Example 18-27
Summary 18-29
Practice 18 Overview 18-31
xx
19 Hierarchical Retrieval
Objectives 19-2
Sample Data from the EMPLOYEES Table 19-3
Natural Tree Structure 19-4
Hierarchical Queries 19-5
Walking the Tree 19-6
Walking the Tree: From the Bottom Up 19-8
Walking the Tree: From the Top Down 19-9
Ranking Rows with the LEVEL Pseudocolumn 19-10
Formatting Hierarchical Reports Using LEVEL and LPAD 19-11
Pruning Branches 19-13
Summary 19-14
Practice 19 Overview 19-15
20 Oracle 9i Extensions to DML and DDL Statements
Objectives 20-2
Review of the INSERT Statement 20-3
Review of the UPDATE Statement 20-4
Overview of Multitable INSERT Statements 20-5
Types of Multitable INSERT Statements 20-7
Multitable INSERT Statements 20-8
Unconditional INSERT ALL 20-10
Conditional INSERT ALL 20-11
Conditional FIRST INSERT 20-13
Pivoting INSERT 20-15
External Tables 20-18
xxi
Creating an External Table 20-19
Example of Creating an External Table 20-20
Querying External Tables 20-23
CREATE INDEX with CREATE TABLE Statement 20-24
Summary 20-25
Practice 20 Overview 20-26
A Practice Solutions
B Table Des criptions and Data
C Using SQL* Plus
D Writing Advanced s cripts
E Oracle Architectural Components
Index
Additional Practices
Additional Practice Solutions
Table and Des criptions
xxii