Home / IT Books / OCA Oracle Database 11g

OCA Oracle Database 11g

OCA Oracle Database 11g

Oracle SQL Developer, originally called Raptor, is a GUI database interface that takes a somewhat different approach from its competitors. While many of the major licensable GUI administration products have continued to expand their product offering through more and more add-on components, SQL Developer is a much more dedicated tool. It’s a streamlined SQL interface to the Oracle database. You can create and manipulate database objects in the GUI interface as well as write and execute SQL statements from a command line. Administration-oriented activities such as storage control are left to Enterprise Manager. SQL Developer aims to be a strong SQL and PL/SQL editor with some GUI functionalities.

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
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
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
Certification objectives covered
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
Certification objectives covered
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
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()
Certification Objectives Covered
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
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
Certification objectives covered
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
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
Certification objectives covered
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
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

OCA Oracle Database 11g


Top books

About huydam

Check Also

[UWP] The program could not build Windows Universal Samples

If you get this error like this: “Type universe cannot resolve assembly: System.Runtime, Version=4.0.0.0, Culture=neutral, …

Leave a Reply

Your email address will not be published. Required fields are marked *