OCA Oracle Database 11g: SQL Fundamentals I: A Real World Certification Guide ( 1ZO-051 )

Table of Contents

• Preface

• Chapter 1: SQL and Relational Databases
• Relational Database Management Systems
• Flat file databases
• Limitations of the flat file paradigm
• Normalization
• The relational approach
• Bringing it into the Oracle world
• Tables and their structure
• Structured Query Language
• A language for relational databases
• Commonly-used SQL tools
• SQL*Plus
• TOAD
• DBArtisan
• SQL Worksheet (Enterprise Manager)
• PL/SQL Developer
• Oracle SQL Developer
• Working with SQL
• Introducing the Companylink database
• An introduction to Oracle SQL Developer
• Setting up SQL Developer
• Getting around in SQL Developer
• Summary
• Test your knowledge

• Chapter 2: SQL SELECT Statements
• The purpose and syntax of SQL
• The syntax of SQL
• Case sensitivity
• The use of whitespace
• Statement terminators
• Retrieving data with SELECT statements
• Projecting columns in a SELECT statement
• Selecting a single column from a table
• Selecting multiple columns from a table
• Selecting all columns from a table
• Displaying the structure of a table using DESCRIBE
• Using aliases to format output of SELECT statements
• Using arithmetic operators with SELECT
• The DUAL table and the use of string literals
• Mathematical operators with SELECT
• The meaning of nothing
• Using DISTINCT to display unique values
• Concatenating values in SELECT statements
• Summary
• Certification objectives covered
• Test your knowledge

• Chapter 3: Using Conditional Statements
• Implementing selectivity using the WHERE clause
• Understanding the concept of selectivity
• Understanding the syntax of the WHERE clause
• Using conditions in WHERE clauses
• Using equality conditions
• Implementing non-equality conditions
• Examining conditions with multiple values
• Constructing range conditions using the BETWEEN clause
• Using the IN clause to create set conditions
• Pattern-matching conditions using the LIKE clause
• Understanding Boolean conditions in the WHERE clause
• Examining the Boolean OR operator
• Understanding the Boolean AND operator
• The Boolean NOT operator
• Using ampersand substitution with runtime conditions
• Sorting data
• Understanding the concepts of sorting data
• Sorting data using the ORDER BY clause
• Changing sort order using DESC and ASC
• Secondary sorts
• Summary
• Certification objectives covered
• Test Your Knowledge

• Chapter 4: Data Manipulation with DML
• Persistent storage and the CRUD model
• Understanding the principles of persistent storage
• Understanding the CRUD model and DML
• Creating data with INSERT
• Examining the syntax of the INSERT statement
• Using single table inserts
• Inserts using positional notation
• Inserts using named column notation
• Inserts using NULL values
• Multi-row inserts
• Conditional Inserts—INSERT...WHEN
• Modifying data with UPDATE
• Understanding the purpose and syntax of the UPDATE statement
• Writing single-column UPDATE statements
• Multi-column UPDATE statements
• Removing data with DELETE
• The purpose and syntax of the DELETE statement
• Deleting rows by condition
• Deleting rows without a limiting condition
• Removing data unconditionally with TRUNCATE
• Transaction control
• Transactions and the ACID test
• Completing transactions with COMMIT
• Undoing transactions with ROLLBACK
• DELETE and TRUNCATE revisited
• Recognizing errors
• Summary
• Certification objectives covered
• Test your knowledge

• Chapter 5: Combining Data from Multiple Tables
• Understanding the principles of joining tables
• Accessing data from multiple tables
• The ANSI standard versus Oracle proprietary syntax
• Using ANSI standard joins
• Understanding the structure and syntax of ANSI join statements
• Examining ambiguous Cartesian joins
• Using equi joins—joins based on equivalence
• Implementing two table joins with a table-dot notation
• Using two table joins with alias notation
• Understanding the row inclusiveness of outer joins
• Retrieving data from multiple tables using n-1 join conditions
• Working with less commonly-used joins—non-equi joins and self-joins
• Understanding Oracle join syntax
• Using Cartesian joins with Cross join
• Joining columns ambiguously using NATURAL JOIN
• Joining on explicit columns with JOIN USING
• Constructing fully-specified joins using JOIN ON
• Writing n-1 join conditions using Oracle syntax
• Creating multi-table natural joins
• Building multi-table joins with JOIN USING
• Summary
• Certification objectives covered
• Test your knowledge

• Chapter 6: Row Level Data Transformation
• Understanding functions and their use
• Comprehending the principles of functions
• Using single-row functions for data transformation
• Understanding String functions
• Using case conversion functions
• UPPER()
• LOWER()
• INITCAP()
• Writing SQL with String manipulation functions
• LENGTH()
• Padding characters with LPAD() and RPAD()
• RTRIM() and LTRIM()
• CONCAT()
• SUBSTR()
• INSTR()
• Exploring nested functions
• Handling DATE functions
• Distinguishing SYSDATE and CURRENT_TIMESTAMP
• Utilizing datatype conversion functions
• Using date to character conversion with TO_CHAR
• Converting characters to dates with TO_DATE()
• Converting numbers using TO_NUMBER()
• Using arithmetic functions
• ROUND()
• TRUNC()
• Using ROUND() and TRUNC() with dates
• MOD()
• Understanding date arithmetic functions
• MONTHS_BETWEEN()
• ADD_MONTHS()
• Examining functions that execute conditional retrieval
• NVL()
• NVL2()
• DECODE()
• Summary
• Certification Objectives Covered
• Test your knowledge

• Chapter 7: Aggregate Data Transformation
• Understanding multi-row functions
• Examining the principles of grouping data
• Using multi-row functions in SQL
• COUNT()
• MIN() and MAX()
• SUM()
• AVG()
• Grouping data
• Grouping data with GROUP BY
• Avoiding pitfalls when using GROUP BY
• Extending the GROUP BY function
• Using statistical functions
• STDDEV()
• VARIANCE()
• Performing row group exclusion with the HAVING clause
• Putting it all together
• Certification objectives covered
• Summary
• Test your knowledge

• Chapter 8: Combining Queries
• Understanding the principles of subqueries
• Accessing data from multiple tables
• Solving problems with subqueries
• Examining different types of subqueries
• Using scalar subqueries
• Using scalar subqueries with WHERE clauses
• Using scalar subqueries with HAVING clauses
• Using scalar subqueries with SELECT clauses
• Processing multiple rows with multi-row subqueries
• Using IN with multi-row subqueries
• Using ANY and ALL with multi-row subqueries
• Using multi-row subqueries with HAVING clauses
• Using correlated subqueries
• Using multi-column subqueries
• Using multi-column subqueries with WHERE clauses
• Multi-column subqueries with the FROM clause
• Investigating further rules for subqueries
• Nesting subqueries
• Using subqueries with NULL values
• Using set operators within SQL
• Principles of set theory
• Comparing set theory and relational theory
• Understanding set operators in SQL
• Using the INTERSECT set operator
• Using the MINUS set operator
• Using the UNION and UNION ALL set operators
• Summary
• Certification objectives covered
• Test your knowledge

• Chapter 9: Creating Tables
• Introducing Data Definition Language
• Understanding the purpose of DDL
• Examining Oracle's schema-based approach
• Understanding the structure of tables and datatypes
• CHAR
• VARCHAR2
• NUMBER
• DATE
• Other datatypes
• Using the CREATE TABLE Statement
• Understanding the rules of table and column naming
• Creating tables
• Avoiding datatype errors
• Avoiding character datatype errors
• Avoiding numeric datatype errors
• Copying tables using CTAS
• Modifying tables with ALTER TABLE
• Adding columns to a table
• Changing column characteristics using ALTER TABLE... MODIFY
• Removing columns using ALTER TABLE... DROP COLUMN
• Removing tables with DROP TABLE
• Using database constraints
• Understanding the principles of data integrity
• Enforcing data integrity using database constraints
• NOT NULL
• PRIMARY KEY
• Natural versus synthetic
• FOREIGN KEY
• Deleting values with referential integrity
• UNIQUE
• CHECK
• Extending the Companylink Data Model
• Adding constraints to Companylink tables
• Adding referential integrity
• Adding a NOT NULL constraint
• Adding a CHECK constraint
• Adding tables to the Companylink model
• Summary
• Certification objectives covered
• Test your knowledge

• Chapter 10: Creating Other Database Objects
• Using indexes to increase performance
• Scanning tables
• Understanding the Oracle ROWID
• Examining B-tree indexes
• Creating B-tree indexes
• Using composite B-tree indexes
• Working with bitmap indexes
• Understanding the concept of cardinality
• Examining the structure of bitmap indexes
• Creating a bitmap index
• Working with function-based indexes
• Modifying and dropping indexes
• Working with views
• Creating a view
• Creating selective views
• Distinguishing simple and complex views
• Configuring other view options
• Changing or removing a view
• Using sequences
• Using sequences to generate primary keys
• Object naming using synonyms
• Schema naming
• Using synonyms for alternative naming
• Creating private synonyms
• Creating public synonyms
• Summary
• Certification objectives covered
• Test your knowledge

• Chapter 11: SQL in Application Development
• Using SQL with other languages
• Why SQL is paired with other languages
• Using SQL with PL/SQL
• Using SQL with Perl
• Using SQL with Python
• Using SQL with Java
• Understanding the Oracle optimizer
• Rule-based versus cost-based optimization
• Gathering optimizer statistics
• Viewing an execution plan with EXPLAIN PLAN
• Advanced SQL statements
• Exam preparation
• Helpful exam hints
• A recommended strategy for preparation
• Summary

• Appendix A: Companylink Table Reference
• The Companylink data model
• ADDRESS
• AWARD
• BLOG
• BRANCH
• DIVISION
• EMAIL
• EMPLOYEE
• EMPLOYEE_AWARD
• MESSAGE
• PROJECT
• WEBSITE

• Appendix B: Getting Started with APEX
• Oracle Application Express
• What is APEX?
• Signing up for APEX
• Using APEX

• Index