OCA Oracle Database 11g SQL Fundamentals I Exam Guide

OCA Oracle Database 11g SQL Fundamentals I Exam Guide

by John Watson, Roopesh Ramklass
Released June 2008
Publisher(s): Oracle Press
ISBN: 9780071643801

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

A Fully Integrated Study System for OCA Exam 1Z0-051

Prepare for the Oracle Certified Associate Oracle Database 11g: SQL Fundamentals I exam with help from this exclusive Oracle Press guide. In each chapter, you'll find challenging exercises, practice questions, a two-minute drill, and a chapter summary to highlight what you've learned. This authoritative guide will help you pass the test and serve as your essential on-the-job reference. Get complete coverage of all OCA objectives for exam 1Z0-051, including:

  • SQL SELECT statements
  • Restricting and sorting data
  • Single-row functions
  • Conversion functions and conditional expressions
  • Group functions
  • Displaying data from multiple tables
  • Subqueries
  • Set operators
  • DML and DDL statements
  • Schema objects

On the CD-ROM:

  • One full practice exam that simulates the actual OCA exam
  • Detailed answers and explanations
  • Score report performance assessment tool
  • Complete electronic book
  • Bonus exam available free with online registration

Table of contents

  1. Cover 
  2. About the Author
  3. Copyright
  4. Dedication
  5. Contents at a Glance
  6. Contents 
  7. Introduction
  8. 1. Oracle Server Technologies and the Relational Paradigm
    1. Position the Server Technologies
      1. The Oracle Server Architecture
      2. The Oracle Application Server
      3. Oracle Enterprise Manager
      4. Grid Computing
      5. Exercise 1-1: Investigate Your Database and Application Environment
      6. Development Tools and Languages
    2. Understand Relational Structures
      1. Rows and Tables
      2. Data Normalization
      3. Exercise 1-2: Perform an Extended Relational Analysis
    3. Summarize the SQL Language
      1. SQL Standards
      2. SQL Commands
      3. A Set-Oriented Language
    4. Use the Client Tools
      1. SQL*Plus
      2. SQL Developer
    5. Create the Demonstration Schemas
      1. Users and Schemas
      2. The HR and OE Schemas
      3. Demonstration Schema Creation
    6. Two-Minute Drill
    7. Q&A Self Test
      1. Lab Question
      2. Self Test Answers
      3. Lab Answer
  9. 2. Data Retrieval Using the SQL SELECT Statement
    1. List the Capabilities of SQL SELECT Statements
      1. Introducing the SQL SELECT Statement
      2. The DESCRIBE Table Command
      3. Exercise 2-1: Describing the Human Resources Schema
      4. Capabilities of the SELECT Statement
    2. Execute a Basic SELECT Statement
      1. Syntax of the Primitive SELECT Statement
      2. Rules Are Meant to be Followed
      3. Exercise 2-2: Answering Our First Questions with SQL
      4. SQL Expressions and Operators
      5. NULL Is Nothing
      6. Exercise 2-3: Experimenting with Expressions and the DUAL Table
    3. Two-Minute Drill
    4. Q&A Self Test
      1. Lab Question
      2. Self Test Answers
      3. Lab Answer
  10. 3. Restricting and Sorting Data
    1. Limit the Rows Retrieved by a Query
      1. The WHERE clause
      2. Comparison Operators
      3. Exercise 3-1: Using the LIKE Operator
      4. Boolean Operators
      5. Precedence Rules
    2. Sort the Rows Retrieved by a Query
      1. The ORDER BY Clause
      2. Exercise 3-2: Sorting Data Using the ORDER BY Clause
    3. Ampersand Substitution
      1. Substitution Variables
      2. Define and Verify
      3. Exercise 3-3: Using Ampersand Substitution
    4. Two-Minute Drill
    5. Q&A Self Test
      1. Lab Question
      2. Self Test Answers
      3. Lab Answer
  11. 4. Single-Row Functions
    1. Describe Various Types of Functions Available in SQL
      1. Defining a Function
      2. Types of Functions
    2. Use Character, Number, and Date Functions in SELECT Statements
      1. Using Character Case Conversion Functions
      2. Exercise 4-1: Using the Case Conversion Functions
      3. Using Character Manipulations Functions
      4. Exercise 4-2: Using the Case Manipulation Functions
      5. Using Numeric Functions
      6. Working with Dates
      7. Using Date Functions
      8. Exercise 4-3: Using the Date Functions
    3. Two-Minute Drill
    4. Q&A Self Test
      1. Lab Question
      2. Self Test Answers
      3. Lab Answer
  12. 5. Using Conversion Functions and Conditional Expressions
    1. Describe Various Types of Conversion Functions Available in SQL
      1. Conversion Functions
    2. Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions
      1. Using the Conversion Functions
      2. Exercise 5-1: Converting Dates into Characters Using the TO_CHAR Function
    3. Apply Conditional Expressions in a SELECT Statement
      1. Nesting Functions
      2. Exercise 5-2: Using NULLIF and NVL2 for Simple Conditional Logic
      3. Conditional Functions
      4. Exercise 5-3: Using the DECODE Function
    4. Two-Minute Drill
    5. Q&A Self Test
      1. Lab Question
      2. Self Test Answers
      3. Lab Answer
  13. 6. Reporting Aggregated Data Using the Group Functions
    1. Describe the Group Functions
      1. Definition of Group Functions
      2. Types and Syntax of Group Functions
    2. Identify the Available Group Functions
      1. Using the Group Functions
      2. Exercise 6-1: Using the Group Functions
      3. Nested Group Functions
    3. Group Data Using the GROUP BY Clause
      1. Creating Groups of Data
      2. The GROUP BY Clause
      3. Grouping by Multiple Columns
      4. Exercise 6-2: Grouping Data Based on Multiple Columns
    4. Include or Exclude Grouped Rows Using the HAVING Clause
      1. Restricting Group Results
      2. The HAVING Clause
      3. Exercise 6-3: Using the HAVING Clause
    5. Two-Minute Drill
    6. Q&A Self Test
      1. Lab Question
      2. Self Test Answers
      3. Lab Answer
  14. 7. Displaying Data from Multiple Tables
    1. Write SELECT Statements to Access Data from More Than One Table Using Equijoins and Nonequijoins
      1. Types of Joins
      2. Joining Tables Using SQL:1999 Syntax
      3. Qualifying Ambiguous Column Names
      4. The NATURAL JOIN Clause
      5. Exercise 7-1: Using the NATURAL JOIN
      6. The Natural JOIN USING Clause
      7. The Natural JOIN ON Clause
      8. Exercise 7-2: Using the NATURAL JOIN…ON Clause
      9. N-Way Joins and Additional Join Conditions
      10. Nonequijoins
    2. Join a Table to Itself Using a Self-Join
      1. Joining a Table to Itself Using the JOIN…ON Clause
      2. Exercise 7-3: Performing a Self-Join
    3. View Data that Does Not Meet a Join Condition by Using Outer Joins
      1. Inner versus Outer Joins
      2. Left Outer Joins
      3. Right Outer Joins
      4. Full Outer Joins
      5. Exercise 7-4: Performing an Outer-Join
    4. Generate a Cartesian Product of Two or More Tables
      1. Creating Cartesian Products Using Cross Joins
      2. Exercise 7-5: Performing a Cross-Join
    5. Two-Minute Drill
    6. Q&A Self Test
      1. Lab Question
      2. Self Test Answers
      3. Lab Answer
  15. 8. Using Subqueries to Solve Problems
    1. Define Subqueries
      1. Exercise 8-1: Types of Subquery
    2. Describe the Types of Problems That the Subqueries Can Solve
      1. Use of a Subquery Result Set for Comparison Purposes
      2. Star Transformation
      3. Generate a Table from Which to SELECT
      4. Generate Values for Projection
      5. Generate Rows to be Passed to a DML Statement
      6. Exercise 8-2: More Complex Subqueries
    3. List the Types of Subqueries
      1. Single- and Multiple-Row Subqueries
      2. Correlated Subqueries
      3. Exercise 8-3: Investigate the Different Types of Subquery
    4. Write Single-Row and Multiple-Row Subqueries
      1. Exercise 8-4: Write a Query That Is Reliable and User Friendly
    5. Two-Minute Drill
    6. Q&A Self Test
      1. Lab Question
      2. Self Test Answers
      3. Lab Answer
  16. 9. Using the Set Operators
    1. Describe the Set Operators
      1. Sets and Venn Diagrams
      2. Set Operator General Principles
      3. Exercise 9-1: Describe the Set Operators
    2. Use a Set Operator to Combine Multiple Queries into a Single Query
      1. The UNION ALL Operator
      2. The UNION Operator
      3. The INTERSECT Operator
      4. The MINUS Operator
      5. More Complex Examples
      6. Exercise 9-2: Using the Set Operators
    3. Control the Order of Rows Returned
      1. Exercise 9-3: Control the Order of Rows Returned
    4. Two-Minute Drill
    5. Q&A Self Test
      1. Lab Question
      2. Self Test Answers
      3. Lab Answer
  17. 10. Manipulating Data
    1. Describe Each Data Manipulation Language (DML) Statement
      1. INSERT
      2. UPDATE
      3. DELETE
      4. MERGE
      5. TRUNCATE
      6. DML Statement Failures
    2. Insert Rows into a Table
      1. Exercise 10-1: Use the INSERT Command
    3. Update Rows in a Table
      1. Exercise 10-2: Use the UPDATE Command
    4. Delete Rows from a Table
      1. Removing Rows with DELETE
      2. Exercise 10-3: Use the DELETE Command
      3. Removing Rows with TRUNCATE
      4. MERGE
    5. Control Transactions
      1. Database Transactions
      2. The Transaction Control Statements
      3. Exercise 10-4: Use the COMMIT and ROLLBACK Commands
    6. Two-Minute Drill
    7. Q&A Self Test
      1. Lab Question
      2. Self Test Answers
      3. Lab Answer
  18. 11. Using DDL Statements to Create and Manage Tables
    1. Categorize the Main Database Objects
      1. Object Types
      2. Users and Schemas
      3. Naming Schema Objects
      4. Object Namespaces
      5. Exercise 11-1: Determine What Objects Are Accessible to Your Session
    2. Review the Table Structure
      1. Exercise 11-2: Investigate Table Structures
    3. List the Data Types That Are Available for Columns
      1. Exercise 11-3: Investigate the Data Types in the HR schema
    4. Create a Simple Table
      1. Creating Tables with Column Specifications
      2. Creating Tables from Subqueries
      3. Altering Table Definitions after Creation
      4. Dropping and Truncating Tables
      5. Exercise 11-4: Create Tables
    5. Explain How Constraints Are Created at the Time of Table Creation
      1. The Types of Constraints
      2. Defining Constraints
      3. Exercise 11-5: Work with Constraints
    6. Two-Minute Drill
    7. Q&A Self Test
      1. Lab Question
      2. Self Test Answers
      3. Lab Answer
  19. 12. Creating Other Schema Objects
    1. Create Simple and Complex Views
      1. Why Use Views at All?
      2. Simple and Complex Views
      3. CREATE VIEW, ALTER VIEW, and DROP VIEW
      4. Exercise 12-1: Create Views
    2. Retrieve Data from Views
      1. Exercise 12-2: Use Views
    3. Create Private and Public Synonyms
      1. Exercise 12-3: Create and Use Synonyms
    4. Create, Maintain, and Use Sequences
      1. Creating Sequences
      2. Using Sequences
      3. Exercise 12-4: Create and Use Sequences
    5. Create and Maintain Indexes
      1. What Indexes Are For
      2. Types of Index
      3. Creating and Using Indexes
      4. Modifying and Dropping Indexes
      5. Exercise 12-5: Creating Indexes
    6. Two-Minute Drill
    7. Q&A Self Test
      1. Lab Question
      2. Self Test Answers
      3. Lab Answer
  20. Appendix
  21. Glossary
  22. Index

Product information

  • Title: OCA Oracle Database 11g SQL Fundamentals I Exam Guide
  • Author(s): John Watson, Roopesh Ramklass
  • Release date: June 2008
  • Publisher(s): Oracle Press
  • ISBN: 9780071643801