Oracle Database 10g SQL Fundamentals I

Day 1

• Introduction
• Lesson 1 : Retrieving Data Using the SQL SELECT Statement
• Lesson 2 : Restricting and Sorting Data
• Lesson 3 : Using Single-Row Functions to Customize Output

Day 2

• Lesson 4 : Reporting Aggregated Data Using the Group Functions
• Lesson 5 : Displaying Data from Multiple Tables
• Lesson 6 : Using Subqueries to Solve Queries
• Lesson 7 : Using the Set Operators

Day 3

• Lesson 8 : Manipulating Data
• Lesson 9 : Using DDL Statements to Create and Manage Tables
• Lesson 10 : Creating Other Schema Objects
• Lesson 11 : Managing Objects with Data Dictionary Views

Lesson 1 : Retrieving Data Using the SQL SELECT Statement

• List the capabilities of SQL SELECT statements
• Execute a basic SELECT statement
• Differentiate between SQL statements and iSQL*Plus commands

Lesson 2 : Restricting and Sorting Data

• Limit the rows that are retrieved by a query
• Sort the rows that are retrieved by a query
• Use ampersand substitution in iSQL*Plus to restrict and sort output at run time

Lesson 3 : Using Single-Row Functions to Customize Output

• Describe various types of functions that are available in SQL
• Use character, number, and date functions in SELECT statements
• Describe the use of conversion functions

Lesson 4 : Reporting Aggregated Data Using the Group Functions

• Identify the available group functions
• Describe the use of group functions
• Group data by using the GROUP BY clause
• Include or exclude grouped rows by using the HAVING clause

Lesson 5 : Displaying Data from Multiple Tables

• Write SELECT statements to access data from more than one table using equijoins and nonequijoins
• Join a table to itself by using a self-join
• View data that generally does not meet a join condition by using outer joins
• Generate a Cartesian product of all rows from two or more tables

Lesson 6 : Using Subqueries to Solve Queries

• Define subqueries
• Describe the types of problems that subqueries can solve
• List the types of subqueries
• Write single-row and multiple-row subqueries

Lesson 7 : Using the Set Operators

• Describe set operators
• Use a set operator to combine multiple queries into a single query
• Control the order of rows returned

Lesson 8 : Manipulating Data

• Describe each data manipulation language (DML) statement
• Insert rows into a table
• Update rows in a table
• Delete rows from a table

Control transactions

Lesson 9 : 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
• Describe how schema objects work

Lesson 10 : Creating Other Schema Objects

• Create simple and complex views
• Retrieve data from views
• Create, maintain, and use sequences
• Create and maintain indexes
• Create private and public synonyms

Lesson 11 : Managing Objects with Data Dictionary Views

• Use the data dictionary views to research data on your objects
• Query various data dictionary views