Beginning Oracle SQL: for Oracle Database 12c, Third Edition

Beginning Oracle SQL: for Oracle Database 12c, Third Edition

by Lex De Haan, Tim Gorman, Inger Jørgensen, Melanie Caffrey
Released March 2014
Publisher(s): Apress
ISBN: 9781430265566

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.

Start your free trial

Book description

Beginning Oracle SQL is your introduction to the interactive query tools and specific dialect of SQL used with Oracle Database. These tools include SQL*Plus and SQL Developer. SQL*Plus is the one tool any Oracle developer or database administrator can always count on, and it is widely used in creating scripts to automate routine tasks. SQL Developer is a powerful, graphical environment for developing and debugging queries.

Oracle's is possibly the most valuable dialect of SQL from a career standpoint. Oracle's database engine is widely used in corporate environments worldwide. It is also found in many government applications. Oracle SQL implements many features not found in competing products. No developer or DBA working with Oracle can afford to be without knowledge of these features and how they work, because of the performance and expressiveness they bring to the table.

Written in an easygoing and example-based style, Beginning Oracle SQL is the book that will get you started down the path to successfully writing SQL statements and getting results from Oracle Database.

  • Takes an example-based approach, with clear and authoritative explanations

  • Introduces both SQL and the query tools used to execute SQL statements

  • Shows how to create tables, populate them with data, and then query that data to generate business results

    • What you'll learn

  • Create database tables and define their relationships.

  • Add data to your tables. Then change and delete that data.

  • Write database queries that generate accurate results.

  • Avoid common traps and pitfalls in writing SQL queries, especially from nulls.

  • Reap the performance and expressiveness of analytic and window functions.

  • Make use of Oracle Database's support for object types.

  • Write recursive queries to query hierarchical data.

    • Who this book is for

    Beginning Oracle SQL is aimed at developers and database administrators who must write SQL statements to execute against an Oracle database. No prior knowledge of SQL is assumed.

    Table of contents

    1. Title Page
    2. Contents at a Glance
    3. Contents
    4. About the Authors
    5. About the Technical Reviewer
    6. Acknowledgments
    7. Introduction
    8. CHAPTER 1: Relational Database Systems and Oracle
      1. 1.1 Information Needs and Information Systems
      2. 1.2 Database Design
      3. 1.3 Database Management Systems
      4. 1.4 Relational Database Management Systems
      5. 1.5 Relational Data Structures
      6. 1.6 Relational Operators
      7. 1.7 How Relational Is My DBMS?
      8. 1.8 The Oracle Software Environment
      9. 1.9 Case Tables
    9. CHAPTER 2: Introduction to SQL and SQL Developer
      1. 2.1 Overview of SQL
      2. 2.2 Basic SQL Concepts and Terminology
      3. 2.3 Introduction to SQL Developer
    10. CHAPTER 3: Data Definition, Part I
      1. 3.1 Schemas and Users
      2. 3.2 Table Creation
      3. 3.3 Datatypes
      4. 3.4 Commands for Creating the Case Tables
      5. 3.5 The Data Dictionary
    11. CHAPTER 4: Retrieval: The Basics
      1. 4.1 Overview of the SELECT Command
      2. 4.2 The SELECT Clause
      3. 4.3 The WHERE Clause
      4. 4.4 The ORDER BY Clause
      5. 4.5 AND, OR, and NOT
      6. 4.6 BETWEEN, IN, and LIKE
      7. 4.7 CASE Expressions
      8. 4.8 Subqueries
      9. 4.9 Null Values
      10. 4.10 Truth Tables
      11. 4.11 Exercises
    12. CHAPTER 5: Retrieval: Functions
      1. 5.1 Overview of Functions
      2. 5.2 Arithmetic Functions
      3. 5.3 Text Functions
      4. 5.4 Regular Expressions
      5. 5.5 Date Functions
      6. 5.6 General Functions
      7. 5.7 Conversion Functions
      8. 5.8 Stored Functions
      9. 5.9 Exercises
    13. CHAPTER 6: Data Manipulation
      1. 6.1 The INSERT Command
      2. 6.2 The UPDATE Command
      3. 6.3 The DELETE Command
      4. 6.4 The MERGE Command
      5. 6.5 Transaction Processing
      6. 6.6 Locking and Read Consistency
    14. CHAPTER 7: Data Definition, Part II
      1. 7.1 The CREATE TABLE Command
      2. 7.2 More on Datatypes
      3. 7.3 The ALTER TABLE and RENAME Commands
      4. 7.4 Constraints
      5. 7.5 Indexes
      6. 7.6 Performance Monitoring with SQL Developer AUTOTRACE
      7. 7.7 Sequences
      8. 7.8 Synonyms
      9. 7.9 The CURRENT_SCHEMA Setting
      10. 7.10 The DROP TABLE Command
      11. 7.11 The TRUNCATE Command
      12. 7.12 The COMMENT Command
      13. 7.13 Exercises
    15. CHAPTER 8: Retrieval: Multiple Tables and Aggregation
      1. 8.1 Tuple Variables
      2. 8.2 Joins
      3. 8.3 The JOIN Clause
      4. 8.4 Outer Joins
      5. 8.5 The GROUP BY Component
      6. 8.6 Group Functions
      7. 8.7 The HAVING Clause
      8. 8.8 Advanced GROUP BY Features
      9. 8.9 Partitioned Outer Joins
      10. 8.10 Set Operators
      11. 8.11 Exercises
    16. CHAPTER 9: Retrieval: Some Advanced Features
      1. 9.1 Subqueries Continued
      2. 9.2 Subqueries in the SELECT Clause
      3. 9.3 Subqueries in the FROM Clause
      4. 9.4 The WITH Clause
      5. 9.5 Hierarchical Queries
      6. 9.6 Analytic Functions
      7. 9.7 Row Limiting
      8. 9.8 Flashback Features
      9. 9.9 Exercises
    17. CHAPTER 10: Views
      1. 10.1 What Are Views?
      2. 10.2 View Creation
      3. 10.3 What Can You Do with Views?
      4. 10.4 Data Manipulation via Views
      5. 10.5 Data Manipulation via Inline Views
      6. 10.6 Views and Performance
      7. 10.7 Materialized Views
      8. 10.8 Global Temporary Table
      9. 10.9 Invisible Columns
      10. 10.10 Exercises
    18. CHAPTER 11: SQL*Plus Basics and Scripting
      1. 11.1 Introduction to SQL*Plus
      2. 11.2 SQL*Plus Variables
      3. 11.3 Bind Variables
      4. 11.4 SQL*Plus Scripts
      5. 11.5 Report Generation with SQL*Plus
      6. 11.6 HTML in SQL*Plus
      7. 11.7 Building SQL*Plus Scripts for Automation
      8. 11.8 Exercises
    19. CHAPTER 12: Object-Relational Features
      1. 12.1 More Datatypes
      2. 12.2 Varrays
      3. 12.3 Nested Tables
      4. 12.4 User-Defined Types
      5. 12.5 Multiset Operators
      6. 12.6 Exercises
    20. APPENDIX A: The Seven Case Tables
      1. ERM Diagram
      2. Table Structure Descriptions
      3. Columns and Foreign Key Constraints
      4. Contents of the Seven Tables
      5. Hierarchical Employees Overview
      6. Course Offerings Overview
    21. APPENDIX B: Answers to the Exercises
      1. Chapter 1, 2 and 3: No exercises
      2. Chapter 4 Exercises
      3. Chapter 5 Exercises
      4. Chapter 6: No exercises
      5. Chapter 7 Exercises
      6. Chapter 8 Exercises
      7. Chapter 9 Exercises
      8. Chapter 10 Exercises
      9. Chapter 11 Exercises
      10. Chapter 12 Exercises
    22. Index

    Product information

    • Title: Beginning Oracle SQL: for Oracle Database 12c, Third Edition
    • Author(s): Lex De Haan, Tim Gorman, Inger Jørgensen, Melanie Caffrey
    • Release date: March 2014
    • Publisher(s): Apress
    • ISBN: 9781430265566