book

Oracle SQL

by Gavin JT Powell, Carol McCullough-Dieter

September 2004

Intermediate to advanced

688 pages

13h 57m

English

Digital Press

Read now

Unlock full access

Cover Page
Title Page
Copyright Page
Contents at a Glance
Foreword
Preface
Acknowledgements
Chapter 1: Introduction to Oracle SQL
1.1 A Little History1.1.1 The Evolution of Database Modeling1.1.2 The History of Relational Databases1.1.3 The Evolution of Oracle Database1.2 The Basics of Relational Data Modeling1.2.1 Normalization1.2.1.1 Referential Integrity1.2.2 Denormalization1.2.3 Different Forms of the Relational Data Model1.3 Structured Query Language (SQL)1.3.1 The Humble Origins of SQL1.3.2 What Is Oracle SQL?1.3.2.1 ANSI Standards and Oracle1.4 Software Useful for Reading this Book1.5 Syntax Conventions Used in This Book1.6 SQL Tools1.6.1 SQL*Plus in Command-Line Mode1.6.2 SQL*Plus in Windows Mode1.6.3 SQL*Plus Worksheet1.6.4 iSQL*Plus1.7 The MUSIC Schema1.7.1 The MUSIC Schema Sales Data Warehouse1.8 Endnotes
Chapter 2: New Features of Oracle SQL
2.1 New Features in Oracle Database 10g2.1.1 Oracle SQL Improvements in Oracle Database 10g2.1.2 PL/SQL Improvements in Oracle Database 10g2.1.2.1 Java Improvements in Oracle Database 10g2.1.3 XML Improvements in Oracle Database 10g2.1.4 Some Utility Improvements in Oracle Database 10g2.1.5 Database Object Improvements in Oracle 10g2.2 New Features in Oracle Database 9i2.2.1 Oracle SQL Improvements in Oracle Database 9i2.2.2 New PL/SQL Features in Oracle Database 9i
Chapter 3: Oracle Database Architecture
3.1 The Basic Concepts3.2 The Oracle Instance3.3 Oracle Database Physical Architecture3.3.1 Datafiles, Tablespaces, and Objects3.3.2 Controlfiles, Logging, and Archiving3.3.3 Rollback and Undo3.3.4 Temporary Sort Space3.4 Database Startup and Shutdown3.5 Enhancing the Physical Architecture3.5.1 Oracle Managed Files3.5.2 Partitioning3.5.3 Replication3.5.4 Standby Databases3.5.5 Clustering and Oracle RAC

Chapter 4: The SELECT Statement
4.1 The Basic SELECT Statement4.1.1 Uses of the SELECT Statement4.1.2 Syntax Conventions4.1.3 Some Simple Example SELECT Statements4.2 Types of SELECT Queries4.2.1 Simple Query4.2.2 Filtered Query4.2.3 Sorted Query4.2.4 Grouping or Aggregated Query4.2.5 Join Query4.2.6 Subquery4.2.7 Table or View Creation Query4.2.8 Hierarchical Query4.2.9 Composite Queries4.3 Other Aspects of the SELECT Statement4.3.1 The DUAL Table4.3.2 Using Functions4.3.3 Arithmetic Operations4.3.4 Using DISTINCT4.3.5 Null Values4.3.6 Using Pseudocolumns
Chapter 5: Filtering Rows
5.1 WHERE Clause Syntax5.1.1 Some Simple WHERE Clause Examples5.2 WHERE Clause Expression Conditions5.3 Logical Operators in the WHERE Clause5.4 Top-N Queries5.5 Endnotes
Chapter 6: Sorting Rows
6.1 ORDER BY Clause Syntax6.1.1 Some Simple ORDER BY Clause Examples6.2 Sorting and Null Values6.3 Sorting Methods6.3.1 Sorting by Position6.3.2 Sorting by Expression6.4 Endnotes
Chapter 7: Operators, Conditions, and Pseudocolumns
7.1 Precedence7.2 Operators7.2.1 Arithmetic Operators7.2.2 Logical Operators7.2.3 The Concatenation Operator7.2.4 Hierarchical Query Operators7.2.5 Set Operators7.2.6 Multiset Operators7.2.7 User-Defined Operators7.3 Conditions7.4 Pseudocolumns
Chapter 8: Using SQL*Plus
8.1 Environmental Settings8.2 Using Scripts and Variables8.3 Formatting Query Output in SQL*Plus8.3.1 Column Formatting and Headings8.3.1.1 Formatting Dates8.3.2 Lines, Pages, and Breaks8.4 Using iSQL*Plus8.4.1 Embedding Scripts in HTML8.4.2 iSQL*Plus versus SQL*Plus8.4.3 Troubleshooting iSQL*Plus8.4.4 Customizing iSQL*Plus Display8.5 Endnotes
Chapter 9: Single-Row Functions
9.1 Types of Functions9.2 Single-Row Functions9.2.1 String Functions9.2.2 Number Functions9.2.2.1 Binary Floating-Point Number Functions9.2.3 Date Functions9.2.4 Datatype Conversion Functions9.2.4.1 Number Conversion Function Formats9.2.4.2 Date Conversion Function Formats9.2.5 Miscellaneous Functions9.3 Combining Functions9.4 Endnotes
Chapter 10: Joining Tables
10.1 Join Formats10.1.1 Oracle’s Proprietary Format10.1.2 ANSI Format10.2 Types of Joins10.3 Examining Different Types of Joins10.3.1 Cross-Join or Cartesian Product10.3.2 Natural or Inner Join10.3.2.1 The USING clause10.3.2.2 The ON clause10.3.3 Outer Join10.3.3.1 Left Outer Join10.3.3.2 Right Outer Join10.3.3.3 Full Outer Join10.3.4 Self-Join10.3.4.1 Grouping Self-Join10.3.4.2 Hierarchical (Fishhook) Self-Join10.3.5 Equi-Joins, Anti-Joins, and Range Joins10.3.6 Mutable and Complex Joins10.4 Endnotes
Chapter 11: Grouping and Summarizing Data
11.1 GROUP BY Clause Syntax11.2 Types of Group Functions11.2.1 Aggregate Functions11.2.1.1 Simple Summary Functions11.2.1.2 Statistical Function Calculators11.2.1.3 Statistical Distribution Functions11.2.1.4 Ranking Functions11.2.1.5 Grouping Functions11.2.2 Enhancing Grouping Functions for Analysis11.2.2.1 The OVER Clause11.3 Special Grouping Function Behavior11.3.1 Group Functions and Null Values11.3.2 Selecting DISTINCT or ALL in Group Functions11.4 Using the GROUP BY Clause11.4.1 Grouping Rows11.4.2 Filtering Grouped Results with the Having Clause11.4.3 Extending the GROUP BY Clause Further11.4.3.1 The ROLLUP Clause11.4.3.2 The CUBE Clause11.4.3.3 The GROUPING SETS Clause11.5 The SPREADSHEET (MODEL) Clause11.5.1 SPREADSHEET (MODEL) Clause Syntax11.5.2 SPREADSHEET (MODEL) Clause Functions11.5.3 Using the SPREADSHEET (MODEL) Clause11.6 Endnotes
Chapter 12: Subqueries
12.1 Types of Subqueries12.2 Where Can Subqueries Be Used?12.3 Comparison Conditions and Subqueries12.4 Demonstrating Subqueries12.4.1 Single-Row Subqueries12.4.2 Multiple-Row Subqueries12.4.3 Multiple-Column Subqueries12.4.4 Regular versus Correlated Subqueries12.4.5 Nested Subqueries12.4.6 Inline Views12.4.7 Other Uses for Subqueries12.5 Endnotes
Chapter 13: Unusual Query Types
13.1 Composite Queries13.1.1 Set Operators13.1.2 Using Composite Queries13.2 Hierarchical Queries13.2.1 Hierarchical Query Operators13.2.2 Hierarchical Query Pseudocolumns13.2.3 Using Hierarchical Queries13.3 Flashback and Versions Queries13.3.1 Flashback Query Syntax13.3.2 Versions Query Pseudocolumns13.3.3 Using Flashback Queries13.4 Parallel Queries13.5 Endnotes
Chapter 14: Expressions
14.1 Types of Expressions14.2 Regular Expressions14.2.1 Regular Expression Functions14.2.2 Regular Expression Patterns14.2.3 Using Regular Expressions14.3 Oracle Expression Filter14.4 Endnotes
Chapter 15: Data Manipulation Language (DML)
15.1 What Is DML?15.2 Transaction Control15.2.1 Locks15.2.2 The SET TRANSACTION Command15.2.3 The SAVEPOINT Command15.3 Adding Data (INSERT)15.3.1 Inserting One Row15.3.2 Inserting with a Subquery15.3.3 The Multiple-Table INSERT Command15.4 Changing Data (UPDATE)15.4.1 Updating One Row15.4.2 Updating Many Rows15.5 Deleting Data (DELETE)15.5.1 Deleting One Row15.5.2 Deleting Many Rows15.5.3 Deleting All Rows15.6 Merging New and Old Data (MERGE)15.6.1 How To Use MERGE
Chapter 16: Datatypes and Collections
16.1 Simple Datatypes16.2 Complex and Object Datatypes16.2.1 Binary Object Datatypes16.2.2 Reference Pointer Datatypes16.2.2.1 Using the REF Datatype16.2.2.2 Using the BFILE Datatype16.2.3 User-Defined Datatypes16.2.4 Object Collection Datatypes16.2.4.1 Using VARRAY Collections16.2.4.2 Using Nested Table Collections16.2.5 Object Collection Functions16.2.6 Metadata Views16.3 Special Datatypes
Chapter 17: XML in Oracle
17.1 What Is XML?17.1.1 What Is XSL?17.2 Using XML in Oracle17.2.1 Creating XML Documents17.2.1.1 The XMLType Datatype17.2.1.2 Generating XML from Tables17.2.1.2.1 The SQL/XML Standard17.2.1.2.2 The SYS_XMLGEN Function17.2.2 XML and the Database17.2.2.1 New XML Documents17.2.2.2 Retrieving from XML Documents17.2.2.3 Changing and Removing XML Document Content17.3 Metadata Views17.4 Endnotes
Chapter 18: Tables
18.1 What Is a Table?18.1.1 Types of Tables18.1.2 Methods of Creating Tables18.1.2.1 Scripted Method18.1.2.2 CREATE TABLE ... AS Subquery18.1.2.3 Tools18.2 CREATE TABLE Syntax18.3 Creating Different Table Types18.3.1 Creating Relational Tables18.3.2 Creating Object Tables18.3.3 Creating Temporary Tables18.3.4 Creating Index-Organized Tables (IOTs)18.3.5 Creating External Tables18.3.6 Creating Partitioned Tables18.3.6.1 What Are the Types of Partitions?18.3.6.1.1 Partition Indexing18.3.6.2 CREATE TABLE Partition Syntax18.3.6.2.1 CREATE TABLE Range Partition Syntax18.3.6.2.2 CREATE TABLE List Partition Syntax18.3.6.2.3 CREATE TABLE Hash Partition Syntax18.3.6.2.4 CREATE TABLE Range-Hash Partition Syntax18.3.6.2.5 CREATE TABLE Range-List Partition Syntax18.4 Changing Table Structure18.4.1 Adding, Modifying, and Removing Columns18.4.2 Rebuilding a Table18.4.3 Renaming a Table18.5 Dropping a Table18.5.1 Truncating Instead of Dropping Tables18.6 Adding Comments to Tables18.6.1 Adding Comments to Schema Objects18.6.2 Scripting and SQL Code Comments18.7 The Recycle Bin18.8 Metadata Views18.9 Endnotes
Chapter 19: Views
19.1 What Is a View?19.2 Types and Uses of Views19.3 CREATE VIEW Syntax19.3.1 Creating Simple Views19.3.2 Creating Constraint Views19.3.3 Creating Complex Views19.3.3.1 Views with Joins19.3.3.2 Inline Subquery Views19.4 Changing and Dropping Views19.5 Working with Views19.5.1 Querying a View19.5.2 Views and DML Commands19.5.2.1 DML and Views with Joins19.6 Metadata Views19.7 Data Dictionary Views (Metadata)19.8 Endnotes
Chapter 20: Constraints
20.1 What Are Constraints?20.1.1 Types and Uses of Constraints20.2 Managing Constraints20.2.1 CREATE TABLE Syntax20.2.1.1 Primary Key and Unique Constraints20.2.1.2 Foreign Key Constraints20.2.1.2.1 Out-of-Line Primary and Foreign Keys20.2.1.3 Check Constraints20.2.1.4 REF Constraints20.2.2 CREATE VIEW Syntax20.3 Adding, Modifying, and Dropping Constraints20.3.1 ALTER TABLE Syntax20.3.2 ALTER VIEW Syntax20.3.3 Working with Constraints and ALTER TABLE20.3.3.1 Adding a Constraint to an Existing Table20.3.3.2 Modifying Constraints on Existing Tables20.3.3.3 Constraint States20.3.4 Renaming a Constraint20.3.5 Dropping Constraints20.3.5.1 Dropping Constraints with CASCADE20.4 Metadata Views
Chapter 21: Indexes and Clusters
21.1 Indexes21.1.1 What Is an Index?21.1.2 Types of Indexes21.1.2.1 Index Attributes21.1.3 Creating Indexes21.1.4 Changing and Dropping Indexes21.1.5 More Indexing Refinements21.2 Clusters21.2.1 What is a Cluster?21.2.2 Types of Clusters21.2.3 Creating Clusters21.3 Metadata Views21.4 Endnotes
Chapter 22: Sequences and Synonyms
22.1 Sequences22.1.1 Creating Sequences22.1.2 Changing and Dropping Sequences22.1.3 Using Sequences22.1.3.1 Using the CURRVAL and NEXTVAL Pseudocolumns22.1.3.2 Using Sequences in an INSERT Statement22.1.3.3 Other Uses of Sequences22.2 Synonyms22.2.1 Creating Public Synonyms22.2.2 Creating Private Synonyms22.2.3 Using Synonyms22.3 Metadata Views22.4 Endnotes
Chapter 23: Security
23.1 Users23.1.1 Users Provided by Oracle23.1.2 Creating Users23.1.3 Modifying User Passwords23.1.4 Dropping Users23.2 Privileges23.2.1 Granting Privileges23.2.2 Revoking Privileges23.2.2.1 Revoked System Privileges DO NOT Cascade23.2.2.2 Revoked Object Privileges DO Cascade23.3 Grouping Privileges Using Roles23.3.1 Creating and Altering Roles23.3.2 Granting and Revoking Privileges on Roles23.3.3 Setting User Roles23.3.4 Dropping Roles23.4 Metadata Views
Chapter 24: Basic PL/SQL
24.1 What is PL/SQL?24.2 Why Is PL/SQL a Programming Language?24.2.1 Blocks and Exception Trapping24.2.2 Procedures, Functions, Triggers, and Packages24.2.2.1 Using Named Procedures24.2.2.2 Using Functions24.2.2.3 Using Triggers24.2.2.4 Using Packages24.3 Variables and Datatypes in PL/SQL24.4 Retrieving Data in PL/SQL24.4.1 Explicit Cursors24.4.2 Implicit Cursors24.4.2.1 The Internal SQL Implicit Cursor24.4.2.2 Single-Row SELECT Implicit Cursor24.4.2.3 Cursor FOR Loop Implicit Cursor24.5 Changing Data in PL/SQL24.6 Dynamic SQL24.6.1 Building Cursors Dynamically24.7 Control Structures24.7.1 Selection24.7.1.1 The IF Statement24.7.1.2 The CASE Statement24.7.1.2.1 CASE Statement Search Condition24.7.1.2.2 CASE Statement Selector and Expression24.7.2 Iteration or Repetition24.7.2.1 The FOR Loop24.7.2.2 The WHILE Loop24.7.2.3 The LOOP...END LOOP Construct24.7.2.4 The FORALL Command24.7.3 Sequence Controls24.7.3.1 The GOTO Statement24.7.3.2 The NULL Command24.8 Objects and Methods24.9 Oracle-Provided Packages24.10 Metadata Views
Appendix A: Schema Scripting
A.1 MUSICMASTER.SQLA.2 CREATEUSER.SQLA.3 SCHEMAOLTP.SQLA.4 SEQUENCES.SQLA.5 INSTRUMENT.SQLA.6 ARTIST.SQLA.7 GENRE.SQLA.8 MUSISCD.SQLA.9 SONGANDTRACK.SQLA.10 GUESTARTIST.SQLA.11 STUDIOTIME.SQLA.12 UPDATEDATA.SQLA.13 CHECKDATA.SQLA.14 SCHEMADW.SQLA.15 DIMENSIONS.SQLA.16 FACTS.SQL
Appendix B: Utility Scripts
B.1 TablesB.2 ConstraintsB.3 Indexes
Appendix C: Sources of Information
C.1 Other titles by the authors
Index

Content preview from Oracle SQL

23

Security

In this chapter:

How do you create a user?
How do you change a user, and what can be changed?
What are privileges?
How are privileges classified?
How do roles group privileges?
When are roles and privileges used?

This chapter covers general Oracle Database 10g access privilege and security issues. You will find out how to share your table data with others using privileges and roles. You will also learn the DBA tasks of creating new users and giving them authority to perform various kinds of work within the database. Creating and managing users and privileges are often DBA tasks. As a result, many DBA-type options are omitted from this chapter. On the other hand, simple security and access skills are very useful for Oracle SQL programmers, ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781555583231

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Oracle SQL

by Gavin JT Powell, Carol McCullough-Dieter

23

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.