OCA Oracle Database 12c SQL Fundamentals I Exam Guide (Exam 1Z0-061), 2nd Edition

OCA Oracle Database 12c SQL Fundamentals I Exam Guide (Exam 1Z0-061), 2nd Edition

by Roopesh Ramklass
Released April 2014
Publisher(s): McGraw-Hill
ISBN: 9780071820295

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 Complete Study System for OCA Exam 1Z0-061

Prepare for the Oracle Certified Associate Oracle Database 12c SQL Fundamentals I exam with this Oracle Press guide. Each chapter features challenging exercises, a certification summary, a two-minute drill, and a self-test to reinforce the topics presented. This authoritative resource helps you pass the exam and also serves as an essential, on-the-job reference. Get complete coverage of all OCA objectives for exam 1Z0-061, including:

  • Data retrieval using the SQL SELECT statement
  • Restricting and sorting data
  • Single-row functions
  • Using conversion functions and conditional expressions
  • Reporting aggregated data with the group functions
  • Displaying data from multiple tables with joins
  • Using subqueries to solve problems
  • Using the set operators
  • Manipulating data with DML statements
  • Using DDL statements to create and manage tables

Electronic content includes:

  • 150+ practice exam questions with detailed answers and explanations
  • Score report performance assessment tool

Table of contents

  1. Cover 
  2. Title Page
  3. Copyright Page
  4. Acknowledgments
  5. About the Author
  6. Contents at a Glance
  7. Contents 
  8. Preface
  9. Introduction
  10. 1. Relational Database Design Using Oracle
    1. Position the Server Technologies
      1. The Oracle Server Architecture
      2. The Oracle WebLogic Server
      3. Oracle Enterprise Manager
      4. Cloud Computing
      5. Exercise 1-1: Investigate Your Database and Application Environment
      6. Development Tools and Languages
    2. Understand Relational Structures
      1. Real-World Scenarios
      2. Data Modeling
      3. Entities and Relations
      4. Exercise 1-2: Design an Entity-Relationship Diagram for the Geological Cores Scenario
      5. Rows and Tables
    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
  11. 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. The Primitive SELECT Statement
      2. Syntax Rules
      3. Exercise 2-2: Answering Our First Questions with SQL
      4. SQL Expressions and Operators
      5. The NULL Concept
      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
  12. 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
  13. 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 Manipulation Functions
      4. Exercise 4-2: Using the Character 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
  14. 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. Nested Functions
      2. General Functions
      3. Exercise 5-2: Using NULLIF and NVL2 for Simple Conditional Logic
      4. Conditional Functions
      5. 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
  15. 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
  16. 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 ANSI SQL Syntax
      3. Qualifying Ambiguous Column Names
      4. The NATURAL JOIN Clause
      5. Exercise 7-1: Using the NATURAL JOIN
      6. The JOIN USING Clause
      7. The 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
  17. 8. Using Subqueries to Solve Problems
    1. Define Subqueries
      1. Exercise 8-1: Types of Subqueries
    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 Subqueries
    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
  18. 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
  19. 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
  20. 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
  21. A: About the Download
    1. System Requirements
    2. Installing and Running Total Tester
    3. About Total Tester
    4. Technical Support
  22. Glossary
  23. Index

Product information

  • Title: OCA Oracle Database 12c SQL Fundamentals I Exam Guide (Exam 1Z0-061), 2nd Edition
  • Author(s): Roopesh Ramklass
  • Release date: April 2014
  • Publisher(s): McGraw-Hill
  • ISBN: 9780071820295