Oracle Database 10g: Introduction to SQL

  • Introduction
    • List the Oracle Database 10g Main Features
    • An Overview of: components, internet platform, apps server and developer suite
    • Describe Relational and Object Relational Database Designs
    • Review the System Development Life Cycle
    • Define the term Data Models
    • Describe different means of Sorting Data
    • Show how Multiple Tables can be related
    • Describe how SQL Communicates to the Database
  • Writing SQL SELECT Statements
    • Define projection, selection, and join terminology
    • Review the basic SQL SELECT statement syntax
    • Select all columns using a wildcard notation from a table
    • State simple rules and guidelines for writing SQL statements
    • Write a query containing the arithmetic operators
    • Create a character expression with the concatenation operator
    • Using the iSQL*Plus Environment
    • SQL statements versus iSQL*Plus commands
  • Restricting and Sorting Data
    • Limit rows using a selection
    • Using the WHERE clause to retrieve specific rows
    • Using the comparison conditions in the WHERE clause
    • Use the LIKE condition to compare literal values
    • List the logical conditions AND, OR, NOT
    • Describe the rules of precedence for the conditions
    • Sort rows with the ORDER BY clause
    • Use ampersand substitution in iSQL*Plus to restrict and sort output at run time
  • Using Single-Row Functions to Customize Output
    • Show the differences between single row and multiple row SQL functions
    • Categorize the character functions into case manipulation and character manipulation types
    • Use the character manipulation functions in the SELECT and WHERE clauses
    • Explain and use the DATE and numeric functions
    • Use the SYSDATE function to retrieve the current date in the default format
    • Introduce the DUAL table as a means to view function results
    • List the rules for applying the arithmetic operators on dates
    • Use the arithmetic operators with dates in the SELECT clause
  • Reporting Aggregated Data Using the Group Functions
    • Describe and categorize the group functions
    • Use the group functions
    • Utilize the DISTINCT keyword with the group functions
    • Describe how nulls are handled with the group functions
    • Create groups of data with the GROUP BY clause
    • Group data by more than one column
    • Copyright © 2009, Oracle. All rights reserved. Page 2
    • Avoid illegal queries with the group functions
    • Exclude groups of data with the HAVING clause
  • Displaying Data from Multiple Tables
    • Identify Types of Joins
    • Retrieve Records with Natural Joins
    • Use Table Aliases to write shorter code and explicitly identify columns from multiple tables
    • Create a Join with the USING clause to identify specific columns between tables
    • Use the ON clause to specify arbitrary conditions or specify columns to Join
    • Create a Three-way join with the ON clause to retrieve information from 3 tables
    • List the Types of Outer Joins LEFT, RIGHT, and FULL
    • Generating a Cartesian Product
  • Using Sub queries to Solve Queries
    • List the syntax for sub queries in a SELECT statements WHERE clause
    • List the guidelines for using sub queries
    • Describe the types of sub queries
    • Execute single row sub queries and use the group functions in a sub query
    • Identify illegal statements with sub queries
    • Execute multiple row sub queries
    • Analyze how the ANY and ALL operators work in multiple row sub queries
  • Using the SET Operators
    • Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
    • Use the UNION ALL operator to return all rows from multiple tables
    • Describe the INTERSECT operator
    • Use the INTERSECT operator
    • Explain the MINUS operator
    • Use the MINUS operator
    • List the SET operator guidelines
    • Order results when using the UNION operator
  • Manipulating Data
    • Write INSERT statements to add rows to a table
    • Copy rows from another table
    • Create UPDATE statements to change data in a table
    • Generate DELETE statements to remove rows from a table
    • Use a script to manipulate data
    • Save and discard changes to a table through transaction processing
    • Show how read consistency works
    • Describe the TRUNCATE statement
  • Using DDL Statements to Create and Manage Tables
    • List the main database objects and describe the naming rules for database objects
    • Introduce the schema concept
    • Display the basic syntax for creating a table and show the DEFAULT option
    • Explain the different types of constraints
    • Show resulting exceptions when constraints are violated with DML statements
    • Create a table with a sub query
    • Describe the ALTER TABLE functionality
    • Remove a table with the DROP statement and Rename a table
    • Copyright © 2009, Oracle. All rights reserved. Page 3
  • Creating Other Schema Objects
    • Categorize simple and complex views and compare them
    • Create a view
    • Retrieve data from a view
    • Explain a read-only view
    • List the rules for performing DML on complex views
    • Create a sequence
    • List the basic rules for when to create and not create an index
    • Create a synonym
  • Managing Objects with Data Dictionary Views
    • Describe the structure of each of the dictionary views
    • List the purpose of each of the dictionary views
    • Write queries that retrieve information from the dictionary views on the schema objects
    • Use the COMMENT command to document objects
  • Controlling User Access
    • Controlling User Access
    • System versus Objects Privileges
    • Using Roles to define user groups
    • Changing Your Password
    • Granting Object Privileges
    • Confirming Privileges Granted
    • Revoking Object Privileges
    • Using Database Links
  • Manage Schema Objects
    • Using the ALTER TABLE statement
    • Adding a Column
    • Modifying a Column
    • Dropping a Column, Set Column UNUSED
    • Adding, Enabling and Disabling Constraints
    • Creating Function-Based Indexes
    • Performing FLASHBACK operations
    • External Tables
  • Manipulating Large Data Sets
    • Using the MERGE Statement
    • Performing DML with Sub queries
    • Performing DML with a RETURNING Clause
    • Overview of Multi-table INSERT Statements
    • Tracking Changes in DML
  • Generating Reports by Grouping Related Data
    • Overview of GROUP BY Clause
    • Overview of Having Clause
    • Aggregating data with ROLLUP and CUBE Operators
    • Determine subtotal groups using GROUPING Functions
    • Compute multiple groupings with GROUPING SETS
    • Define levels of aggregation with Composite Columns
    • Create combinations with Concatenated Groupings
    • Copyright © 2009, Oracle. All rights reserved. Page 4
  • Managing Data in Different Time Zones
    • Time Zones
    • Using date and time functions
    • Identifying TIMESTAMP Data Types
    • Differentiating between DATE and TIMESTAMP
    • Performing Conversion Operations
  • Searching Data Using Advanced Sub queries
    • Sub query Overview
    • Using a Sub query
    • Comparing several columns using Multiple-Column Sub queries
    • Defining a Data source Using a Sub query in the FROM Clause
    • Returning one Value using Scalar Sub query Expressions
    • Performing ROW by-row processing with Correlated Sub queries
    • Reusing query blocks using the WITH Clause
  • Hierarchical Retrieval
    • Sample Data from the EMPLOYEES Table
    • The Tree Structure of Employee data
    • Hierarchical Queries
    • Ranking Rows with LEVEL
    • Formatting Hierarchical Reports Using LEVEL and LPAD
    • Pruning Branches with the WHERE and CONNECT BY clauses
    • Regular Expression Support
    • Regular Expression Support Overview
    • Describing simple and complex patterns for searching and manipulating data

 

 



 
Label