Oracle SQL: the Essential Reference

Oracle SQL: the Essential Reference

by David C. Kreines
Released September 2000
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781565926974

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

SQL (Structured Query Language) is the heart of a relational database management system. It's the language used to query the database, to create new tables in the database, to update and delete database fields, and to set privileges in the database. Oracle SQL: The Essential Reference is for everyone who needs to access an Oracle database using SQL--developers, DBAs, designers, and managers. SQL is based on research dating back to the late 1960s, but its first commercial release was in the RDBMS announced by the fledgling Oracle Corporation in 1979. Since that time, every other database vendor has adopted SQL, and ANSI and the ISO have made it a standard. Although vendors diverge in their extensions to SQL, the core language is standard across vendor boundaries. Despite SQL's long history and relative simplicity, few developers and database administrators are truly masters of SQL. The constant stream of vendor enhancements, the hard-won experience in tuning SQL for best performance, and the requirements of particular operational environments mean that there is always more to learn about SQL. Oracle SQL: The Essential Reference delivers all the information needed to keep ahead of the learning curve on standard SQL and Oracle's extensions to it. This single, concise reference volume will hold its own against a stack of Oracle manuals and even yield insights and examples not available in those manuals. There are chapters on basic SQL elements (naming requirements, column types, pseudo-types, data conversion rules, operators); Data Definition Language (DDL) and Data Manipulation Language (DML); common language elements (constraints, storage clause, predicates); SQL functions; PL/SQL (including procedures, functions, and packages); SQL*Plus, and Oracle SQL optimization and tuning. The book covers Oracle 8i, release 8.1.6.

Table of contents

  1. Copyright
  2. Dedication
  3. Foreword
    1. SQL: A Venerable History and a Vital Future
    2. Programming and Data Access Languages
    3. The Origins of SQL
    4. The SQL Language
    5. The Commercial Development of SQL through the 1980s
    6. The Evolution of SQL: the 1990s and Beyond
    7. Standardization of the SQL Language
    8. SQL: A Success Story
  4. Preface
    1. Audience for This Book
    2. Versions of Oracle
    3. Contents of This Book
    4. Conventions Used in This Book
    5. Comments and Questions
    6. Acknowledgments
  5. Elements of SQL
    1. Lexical Conventions
    2. Naming in SQL
    3. Schema Objects
    4. Datatypes
    5. Data Conversion
    6. Relational Operators
    7. Structure of a SQL Statement
    8. SQL Statements
  6. Data Definition Statements
    1. SQL DDL Statements by Task
    2. SQL Statement Syntax
      1. ALTER RESOURCE COST
      2. ALTER SESSION
      3. ALTER SYSTEM
      4. ANALYZE
      5. ASSOCIATE STATISTICS
      6. AUDIT (Schema Objects)
      7. AUDIT (SQL Statements)
      8. CALL
      9. COMMENT
      10. CREATE/ALTER/DROP CLUSTER
      11. CREATE CONTROLFILE
      12. CREATE/ALTER DATABASE
      13. CREATE/DROP DATABASE LINK
      14. CREATE/DROP DIMENSION
      15. CREATE/DROP DIRECTORY
      16. CREATE/ALTER/DROP FUNCTION
      17. CREATE/ALTER/DROP INDEX
      18. CREATE/ALTER/DROP MATERIALIZED VIEW
      19. CREATE/ALTER/DROP MATERIALIZED VIEW LOG
      20. CREATE/ALTER/DROP PACKAGE
      21. CREATE/ALTER/DROP PROCEDURE
      22. CREATE/ALTER/DROP PROFILE
      23. CREATE/ALTER/DROP ROLE
      24. CREATE/ALTER/DROP ROLLBACK SEGMENT
      25. CREATE SCHEMA
      26. CREATE/ALTER/DROP SEQUENCE
      27. CREATE/ALTER/DROP SNAPSHOT
      28. CREATE/ALTER/DROP SNAPSHOT LOG
      29. CREATE/DROP SYNONYM
      30. CREATE/ALTER/DROP TABLE
      31. CREATE/ALTER/DROP TABLESPACE
      32. CREATE TEMPORARY TABLESPACE
      33. CREATE/ALTER/DROP TRIGGER
      34. CREATE/ALTER/DROP USER
      35. CREATE/ALTER/DROP VIEW
      36. DISASSOCIATE STATISTICS
      37. EXPLAIN PLAN
      38. GRANT (Object Privileges)
      39. GRANT (System Privilege or Role)
      40. NOAUDIT (Schema Objects)
      41. NOAUDIT (SQL Statements)
      42. RENAME
      43. REVOKE (Object Privileges)
      44. REVOKE (System Privilege or Role)
  7. Data Manipulation and Control Statements
    1. SQL DML and Control Statements by Task
    2. SQL Statement Syntax
      1. ALTER SESSION
      2. ANALYZE
      3. DELETE
      4. EXPLAIN PLAN
      5. INSERT
      6. SAVEPOINT
      7. SELECT
      8. SET CONSTRAINT
      9. SET ROLE
      10. SET TRANSACTION
      11. TRUNCATE
      12. UPDATE
  8. Common SQL Elements
    1. Autoextend_Clause
    2. Column_Constraint_Clause
    3. Constraint_State_Clause
    4. LOB_Storage_Clause
    5. Partition_Clause
    6. Physical_Attributes_Clause
    7. Storage_Clause
    8. Table_Constraint_Clause
  9. SQL Functions
    1. Aggregate Functions
    2. Numeric Functions
      1. ABS
      2. ACOS
      3. ASIN
      4. ATAN
      5. ATAN2
      6. CEIL
      7. COS
      8. COSH
      9. EXP
      10. FLOOR
      11. GREATEST
      12. LEAST
      13. LN
      14. LOG
      15. MOD
      16. POWER
      17. ROUND
      18. SIGN
      19. SIN
      20. SQRT
      21. TAN
      22. TANH
      23. TRUNC
    3. Character Functions
      1. ASCII
      2. CHR
      3. CONCAT
      4. GREATEST
      5. INITCAP
      6. INSTR
      7. INSTRB
      8. LEAST
      9. LENGTH
      10. LENGTHB
      11. LOWER
      12. LPAD
      13. LTRIM
      14. NLS_INITCAP
      15. NLS_LOWER
      16. NLS_UPPER
      17. NLSSORT
      18. REPLACE
      19. RPAD
      20. RTRIM
      21. SOUNDEX
      22. SUBSTR
      23. SUBSTRB
      24. TRANSLATE
      25. TRIM
      26. UPPER
    4. Date Functions
      1. ADD_MONTHS
      2. GREATEST
      3. LAST_DAY
      4. LEAST
      5. MONTHS_BETWEEN
      6. NEW_TIME
      7. NEXT_DAY
      8. ROUND
      9. SYSDATE
      10. TRUNC
    5. Conversion Functions
      1. CHARTOROWID
      2. CONVERT
      3. HEXTORAW
      4. RAWTOHEX
      5. ROWIDTOCHAR
      6. TO_CHAR (Converting Dates to Character Strings)
      7. TO_CHAR (Converting Numbers to Character Strings)
      8. TO_DATE
      9. TO_LOB
      10. TO_MULTI_BYTE
      11. TO_NUMBER
      12. TO_SINGLE_BYTE
      13. TRANSLATE USING
    6. Other Functions
      1. BFILENAME
      2. DECODE
      3. DUMP
      4. EMPTY_BLOB
      5. EMPTY_CLOB
      6. NLS_CHARSET_DECL_LEN
      7. NLS_CHARSET_ID
      8. NLS_CHARSET_NAME
      9. NVL
      10. SYS_CONTEXT
      11. SYS_GUID
      12. UID
      13. USER
      14. USERENV
      15. VSIZE
  10. SQL*Plus
    1. Command-Line Syntax
    2. SQL*Plus Editing Commands
    3. Formatting SQL*Plus Output
    4. Miscellaneous SQL*Plus Commands
      1. @
      2. @@
      3. ARCHIVE LOG
      4. CONNECT
      5. COPY
      6. DESCRIBE
      7. DISCONNECT
      8. EDIT
      9. EXECUTE
      10. EXIT
      11. HELP
      12. HOST
      13. PAUSE
      14. RECOVER
      15. REMARK
      16. SET
      17. SHOW
      18. SHUTDOWN
      19. SPOOL
      20. START
      21. STARTUP
      22. STORE
      23. TIMING
      24. WHENEVER OSERROR
      25. WHENEVER SQLERROR
    5. SQL*Plus Variables and Related Commands
      1. &
      2. &&
      3. ACCEPT
      4. DEFINE
      5. PRINT
      6. UNDEFINE
      7. VARIABLE
    6. SQL*Plus System Variables
      1. APPINFO
      2. ARRAYSIZE
      3. AUTOCOMMIT
      4. AUTOPRINT
      5. AUTORECOVERY
      6. AUTOTRACE
      7. BLOCKTERMINATOR
      8. CLOSECURSOR
      9. CMDSEP
      10. COLSEP
      11. COMPATIBILITY
      12. CONCAT
      13. COPYCOMMIT
      14. COPYTYPECHECK
      15. DEFINE
      16. ECHO
      17. EDITFILE
      18. EMBEDDED
      19. ESCAPE
      20. FEEDBACK
      21. FLAGGER
      22. FLUSH
      23. HEADING
      24. HEADSEP
      25. INSTANCE
      26. LINESIZE
      27. LOBOFFSET
      28. LONG
      29. LONGCHUNKSIZE
      30. MARKUP HTML
      31. NEWPAGE
      32. NULL
      33. NUMFORMAT
      34. NUMWIDTH
      35. PAGESIZE
      36. PAUSE
      37. RECSEP
      38. RECSEPCHAR
      39. SERVEROUTPUT (Oracle7 Syntax)
      40. SERVEROUTPUT (Oracle8 Syntax)
      41. SHIFT
      42. SHOWMODE
      43. SQLBLANKLINES
      44. SQLCASE
      45. SQLCONTINUE
      46. SQLNUMBER
      47. SQLPREFIX
      48. SQLPROMPT
      49. SQLTERMINATOR
      50. SUFFIX
      51. TAB
      52. TERMOUT
      53. TIME
      54. TIMING
      55. TRIMOUT
      56. TRIMSPOOL
      57. UNDERLINE
      58. VERIFY
      59. WRAP
  11. PL/SQL
    1. The Structure of PL/SQL
    2. Block Header
    3. Declaration Section
    4. Execution Section
    5. Exception Section
    6. Procedures and Packages
    7. Triggers
      1. CREATE TRIGGER
      2. ALTER TRIGGER
      3. DROP TRIGGER
  12. SQL Statement Tuning
    1. Using EXPLAIN PLAN
    2. Using Oracle's SQL Trace Facility
    3. SQL*Plus Tuning Aids
    4. Improving Query Performance
  13. SQL Resources
    1. Books
    2. Other Publications
    3. Organizations
    4. Web Sites
  14. Colophon
  15. Index

Product information

  • Title: Oracle SQL: the Essential Reference
  • Author(s): David C. Kreines
  • Release date: September 2000
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781565926974