OCA Oracle Database 11g: SQL Fundamentals I: A Real-World Certification Guide

OCA Oracle Database 11g: SQL Fundamentals I: A Real-World Certification Guide

by Steve Ries
Released December 2011
Publisher(s): Packt Publishing
ISBN: 9781849683647

Explore a preview version of OCA Oracle Database 11g: SQL Fundamentals I: A Real-World Certification Guide right now.

O’Reilly members get unlimited access to live online training experiences, plus books, videos, and digital content from 200+ publishers.

Start your free trial

Book Description

Ace the 1Z0-051 SQL Fundamentals I exam and become a successful DBA by learning how SQL concepts work in the real world

  • Successfully clear the first stepping stone towards attaining the Oracle Certified Associate Certification on Oracle Database 11g
  • This book uses a real world example-driven approach that is easy to understand and makes engaging
  • Complete coverage of the prescribed syllabus
  • Learn from a range of self-test questions to fully equip you with the knowledge to pass this exam
  • After reading this book, you can enter the exam room with confidence, knowing that you have done all you can to prepare for the big day

In Detail

The Oracle Database 11g: SQL Fundamentals I exam is the first stepping stone in getting the Oracle Certified Associate Certification for Oracle Database 11g. The SQL programming language is used in every major relational database today and understanding the real world application of it is the key to becoming a successful DBA.

This book gives you: the essential real world skills to master relational data manipulation with Oracle SQL and prepares you to become an Oracle Certified Associate. Beginners are introduced to concepts in a logical manner while practitioners can use it as a reference to jump to relevant concepts directly.

We begin with the essentials of why databases are important in today's information technology world and how they work.

We continue by explaining the concepts of querying and modifying data in Oracle using a range of techniques, including data projection, selection, creation, joins, sub-queries and functions. Finally, we learn to create and manipulate database objects and to use them in the same way as today's expert SQL programmers.

This book prepares you to master the fundamentals of the SQL programming language using an example-driven method that is easy to understand

This definitive certification guide provides a disciplined approach to be adopted for successfully clearing the 1Z0-051 SQL Fundamentals I exam, which is the first stepping stone towards attaining the OCA on Oracle Database 11g certification.

Each chapter contains ample practice questions at the end. A full-blown mock test is included for practice so you can test your knowledge and get a feel for the actual exam.

Table of Contents

  1. OCA Oracle Database 11g: SQL Fundamentals I: A Real-World Certification Guide
    1. Table of Contents
    2. OCA Oracle Database 11g: SQL Fundamentals I: A Real-World Certification Guide
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers and more
        1. Why Subscribe?
        2. Free Access for Packt account holders
        3. Instant Updates on New Packt Books
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    8. 1. SQL and Relational Databases
      1. Relational Database Management Systems
        1. Flat file databases
        2. Limitations of the flat file paradigm
        3. Normalization
        4. The relational approach
        5. Bringing it into the Oracle world
        6. Tables and their structure
      2. Structured Query Language
        1. A language for relational databases
        2. Commonly-used SQL tools
          1. SQL*Plus
          2. TOAD
          3. DBArtisan
          4. SQL Worksheet (Enterprise Manager)
        3. PL/SQL Developer
        4. Oracle SQL Developer
      3. Working with SQL
        1. Introducing the Companylink database
      4. An introduction to Oracle SQL Developer
        1. Setting up SQL Developer
        2. Getting around in SQL Developer
      5. Summary
      6. Test your knowledge
    9. 2. SQL SELECT Statements
      1. The purpose and syntax of SQL
        1. The syntax of SQL
          1. Case sensitivity
          2. The use of whitespace
          3. Statement terminators
      2. Retrieving data with SELECT statements
        1. Projecting columns in a SELECT statement
          1. Selecting a single column from a table
          2. Selecting multiple columns from a table
          3. Selecting all columns from a table
      3. Displaying the structure of a table using DESCRIBE
      4. Using aliases to format output of SELECT statements
      5. Using arithmetic operators with SELECT
        1. The DUAL table and the use of string literals
        2. Mathematical operators with SELECT
        3. The meaning of nothing
      6. Using DISTINCT to display unique values
      7. Concatenating values in SELECT statements
      8. Summary
        1. Certification objectives covered
      9. Test your knowledge
    10. 3. Using Conditional Statements
      1. Implementing selectivity using the WHERE clause
        1. Understanding the concept of selectivity
        2. Understanding the syntax of the WHERE clause
      2. Using conditions in WHERE clauses
        1. Using equality conditions
        2. Implementing non-equality conditions
      3. Examining conditions with multiple values
        1. Constructing range conditions using the BETWEEN clause
        2. Using the IN clause to create set conditions
        3. Pattern-matching conditions using the LIKE clause
        4. Understanding Boolean conditions in the WHERE clause
        5. Examining the Boolean OR operator
        6. Understanding the Boolean AND operator
        7. The Boolean NOT operator
        8. Using ampersand substitution with runtime conditions
      4. Sorting data
        1. Understanding the concepts of sorting data
        2. Sorting data using the ORDER BY clause
        3. Changing sort order using DESC and ASC
        4. Secondary sorts
      5. Summary
      6. Certification objectives covered
      7. Test Your Knowledge
    11. 4. Data Manipulation with DML
      1. Persistent storage and the CRUD model
        1. Understanding the principles of persistent storage
        2. Understanding the CRUD model and DML
      2. Creating data with INSERT
        1. Examining the syntax of the INSERT statement
        2. Using single table inserts
          1. Inserts using positional notation
          2. Inserts using named column notation
          3. Inserts using NULL values
        3. Multi-row inserts
        4. Conditional Inserts—INSERT...WHEN
      3. Modifying data with UPDATE
        1. Understanding the purpose and syntax of the UPDATE statement
        2. Writing single-column UPDATE statements
        3. Multi-column UPDATE statements
      4. Removing data with DELETE
        1. The purpose and syntax of the DELETE statement
        2. Deleting rows by condition
        3. Deleting rows without a limiting condition
        4. Removing data unconditionally with TRUNCATE
      5. Transaction control
        1. Transactions and the ACID test
        2. Completing transactions with COMMIT
        3. Undoing transactions with ROLLBACK
          1. DELETE and TRUNCATE revisited
      6. Recognizing errors
      7. Summary
        1. Certification objectives covered
      8. Test your knowledge
    12. 5. Combining Data from Multiple Tables
      1. Understanding the principles of joining tables
        1. Accessing data from multiple tables
        2. The ANSI standard versus Oracle proprietary syntax
      2. Using ANSI standard joins
        1. Understanding the structure and syntax of ANSI join statements
        2. Examining ambiguous Cartesian joins
        3. Using equi joins—joins based on equivalence
          1. Implementing two table joins with a table-dot notation
          2. Using two table joins with alias notation
        4. Understanding the row inclusiveness of outer joins
        5. Retrieving data from multiple tables using n-1 join conditions
        6. Working with less commonly-used joins—non-equi joins and self-joins
      3. Understanding Oracle join syntax
        1. Using Cartesian joins with Cross join
        2. Joining columns ambiguously using NATURAL JOIN
        3. Joining on explicit columns with JOIN USING
        4. Constructing fully-specified joins using JOIN ON
        5. Writing n-1 join conditions using Oracle syntax
          1. Creating multi-table natural joins
          2. Building multi-table joins with JOIN USING
      4. Summary
        1. Certification objectives covered
      5. Test your knowledge
    13. 6. Row Level Data Transformation
      1. Understanding functions and their use
        1. Comprehending the principles of functions
        2. Using single-row functions for data transformation
      2. Understanding String functions
        1. Using case conversion functions
          1. UPPER()
          2. LOWER()
          3. INITCAP()
        2. Writing SQL with String manipulation functions
          1. LENGTH()
          2. Padding characters with LPAD() and RPAD()
          3. RTRIM() and LTRIM()
          4. CONCAT()
          5. SUBSTR()
          6. INSTR()
          7. Exploring nested functions
            1. Substituting values with REPLACE()
      3. Handling DATE functions
        1. Distinguishing SYSDATE and CURRENT_TIMESTAMP
        2. Utilizing datatype conversion functions
          1. Using date to character conversion with TO_CHAR
          2. Converting characters to dates with TO_DATE()
          3. Converting numbers using TO_NUMBER()
      4. Using arithmetic functions
        1. ROUND()
        2. TRUNC()
        3. Using ROUND() and TRUNC() with dates
        4. MOD()
        5. Understanding date arithmetic functions
          1. MONTHS_BETWEEN()
          2. ADD_MONTHS()
      5. Examining functions that execute conditional retrieval
        1. NVL()
        2. NVL2()
        3. DECODE()
      6. Summary
        1. Certification objectives covered
      7. Test your knowledge
    14. 7. Aggregate Data Transformation
      1. Understanding multi-row functions
        1. Examining the principles of grouping data
        2. Using multi-row functions in SQL
          1. COUNT()
          2. MIN() and MAX()
          3. SUM()
          4. AVG()
      2. Grouping data
        1. Grouping data with GROUP BY
        2. Avoiding pitfalls when using GROUP BY
        3. Extending the GROUP BY function
        4. Using statistical functions
          1. STDDEV()
          2. VARIANCE()
        5. Performing row group exclusion with the HAVING clause
      3. Putting it all together
      4. Certification objectives covered
      5. Summary
      6. Test your knowledge
    15. 8. Combining Queries
      1. Understanding the principles of subqueries
        1. Accessing data from multiple tables
        2. Solving problems with subqueries
      2. Examining different types of subqueries
        1. Using scalar subqueries
          1. Using scalar subqueries with WHERE clauses
          2. Using scalar subqueries with HAVING clauses
          3. Using scalar subqueries with SELECT clauses
        2. Processing multiple rows with multi-row subqueries
          1. Using IN with multi-row subqueries
          2. Using ANY and ALL with multi-row subqueries
          3. Using multi-row subqueries with HAVING clauses
          4. Using correlated subqueries
        3. Using multi-column subqueries
          1. Using multi-column subqueries with WHERE clauses
          2. Multi-column subqueries with the FROM clause
      3. Investigating further rules for subqueries
        1. Nesting subqueries
        2. Using subqueries with NULL values
      4. Using set operators within SQL
        1. Principles of set theory
        2. Comparing set theory and relational theory
        3. Understanding set operators in SQL
          1. Using the INTERSECT set operator
          2. Using the MINUS set operator
          3. Using the UNION and UNION ALL set operators
      5. Summary
      6. Certification objectives covered
      7. Test your knowledge
    16. 9. Creating Tables
      1. Introducing Data Definition Language
        1. Understanding the purpose of DDL
        2. Examining Oracle's schema-based approach
        3. Understanding the structure of tables and datatypes
          1. CHAR
          2. VARCHAR2
          3. NUMBER
          4. DATE
          5. Other datatypes
        4. Using the CREATE TABLE Statement
          1. Understanding the rules of table and column naming
        5. Creating tables
        6. Avoiding datatype errors
          1. Avoiding character datatype errors
          2. Avoiding numeric datatype errors
        7. Copying tables using CTAS
        8. Modifying tables with ALTER TABLE
          1. Adding columns to a table
          2. Changing column characteristics using ALTER TABLE... MODIFY
          3. Removing columns using ALTER TABLE... DROP COLUMN
        9. Removing tables with DROP TABLE
      2. Using database constraints
        1. Understanding the principles of data integrity
        2. Enforcing data integrity using database constraints
          1. NOT NULL
          2. PRIMARY KEY
          3. Natural versus synthetic
          4. FOREIGN KEY
          5. Deleting values with referential integrity
          6. UNIQUE
          7. CHECK
      3. Extending the Companylink Data Model
        1. Adding constraints to Companylink tables
          1. Adding referential integrity
          2. Adding a NOT NULL constraint
          3. Adding a CHECK constraint
        2. Adding tables to the Companylink model
      4. Summary
        1. Certification objectives covered
      5. Test your knowledge
    17. 10. Creating Other Database Objects
      1. Using indexes to increase performance
        1. Scanning tables
        2. Understanding the Oracle ROWID
        3. Examining B-tree indexes
        4. Creating B-tree indexes
          1. Using composite B-tree indexes
        5. Working with bitmap indexes
          1. Understanding the concept of cardinality
          2. Examining the structure of bitmap indexes
          3. Creating a bitmap index
        6. Working with function-based indexes
        7. Modifying and dropping indexes
      2. Working with views
        1. Creating a view
          1. Creating selective views
          2. Distinguishing simple and complex views
          3. Configuring other view options
        2. Changing or removing a view
      3. Using sequences
        1. Using sequences to generate primary keys
      4. Object naming using synonyms
        1. Schema naming
        2. Using synonyms for alternative naming
          1. Creating private synonyms
          2. Creating public synonyms
      5. Summary
        1. Certification objectives covered
      6. Test your knowledge
    18. 11. SQL in Application Development
      1. Using SQL with other languages
        1. Why SQL is paired with other languages
        2. Using SQL with PL/SQL
        3. Using SQL with Perl
        4. Using SQL with Python
        5. Using SQL with Java
      2. Understanding the Oracle optimizer
        1. Rule-based versus cost-based optimization
        2. Gathering optimizer statistics
        3. Viewing an execution plan with EXPLAIN PLAN
      3. Advanced SQL statements
      4. Exam preparation
        1. Helpful exam hints
        2. A recommended strategy for preparation
      5. Summary
      6. Practice test paper
    19. A. Companylink Table Reference
      1. The Companylink data model
        1. ADDRESS
        2. AWARD
        3. BLOG
        4. BRANCH
        5. DIVISION
        6. EMAIL
        7. EMPLOYEE
        8. EMPLOYEE_AWARD
        9. MESSAGE
        10. PROJECT
        11. WEBSITE
    20. B. Getting Started with APEX
      1. Oracle Application Express
        1. What is APEX?
        2. Signing up for APEX
        3. Using APEX
    21. Index

Product Information

  • Title: OCA Oracle Database 11g: SQL Fundamentals I: A Real-World Certification Guide
  • Author(s): Steve Ries
  • Release date: December 2011
  • Publisher(s): Packt Publishing
  • ISBN: 9781849683647