Oracle Database 11g SQL Fundamentals I: Preparation for OCA Exam 1Z0-051

Oracle Server Technologies and the Relational Paradigm

• Position the Server Technologies
• Understand Relational Structures
• Summarize the SQL Language
• Use the Client Tools
• Create the Demonstration Schemas

Data Retrieval Using the SQL SELECT Statement

• List the Capabilities of SQL SELECT Statements
• Execute a Basic SELECT Statement

Restricting and Sorting Data

• Limit the Rows Retrieved by a Query
• Sort the Rows Retrieved by a Query
• Ampersand Substitution

Single-Row Functions

• Describe Various Types of Functions Available in SQL
• Use Character, Number, and Date Functions in SELECT Statements

Using Conversion Functions and Conditional Expressions

• Describe Various Types of Conversion Functions Available in SQL
• Use the TO_CHAR, TO_NUMBER, and TO_DATE Conversion Functions
• Apply Conditional Expressions in a SELECT Statement

Reporting Aggregated Data Using the Group Functions

• Describe the Group Functions
• Identify the Available Group Functions
• Group Data Using the GROUP BY Clause
• Include or Exclude Grouped Rows Using the HAVING Clause

Displaying Data from Multiple Table

• Write SELECT Statements tAccess Data from More Than One Table Using Equijoins and
• Nonequijoins
• Join a Table to itself Using a Self-join
• View Data that Does Not Meet a Join Condition Using Outer Joins
• Generate a Cartesian Product of Twor More Tables

Using Subqueries to Solve Problems

• Define Subqueries
• Describe the Types of Problems That the Subqueries Can Solve
• List the Types of Subqueries
• Write Single-row and Multiple-row Subqueries

Using the Set Operators

• Describe the Set Operators
• Use a Set Operator tCombine Multiple Queries inta Single Query
• Control the Order of Rows Returned

Manipulating Data

• Describe Each Data Manipulation Language (DML) Statement
• Insert Rows inta Table
• Update Rows in a Table
• Delete Rows from a Table
• Control Transactions

Using DDL Statements to Create and Manage Tables

• Categorize the Main Database Objects
• Review the Table Structure
• List the Data Types That Are Available for Columns
• Create a Simple Table
• Explain How Constraints Are Created at the Time of Table Creation

Creating Other Schema Objects

• Create Simple and Complex Views
• Retrieve Data from Views
• Create Private and Public Synonyms
• Create, Maintain, and Use Sequences
• Create and Maintain Indexes