Introduction

• Listing the features of Oracle Database 11g
• Discussing the basic design, theoretical and physical aspects of a relational database
• Describing the development environments for SQL
• Describing Oracle SQL Developer
• Describing the data set used by the course

Retrieving Data Using the SQL SELECT Statement

• Listing the capabilities of SQL SELECT statements.
• Generating a report of data from the output of a basic SELECT statement
• Using arithmetic expressions and NULL values in the SELECT statement
• Using Column aliases
• Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
• Displaying the table structure using the DESCRIBE command

Restricting and Sorting Data

• Writing queries with a WHERE clause to limit the output retrieved
• Using the comparison operators and logical operators
• Describing the rules of precedence for comparison and logical operators
• Using character string literals in the WHERE clause
• Writing queries with an ORDER BY clause to sort the output
• Sorting output in descending and ascending order
• Using the Substitution Variables

Using Single-Row Functions to Customize Output

• Differentiating between single row and multiple row functions
• Manipulating strings using character functions
• Manipulating numbers with the ROUND, TRUNC and MOD functions
• Performing arithmetic with date data
• Manipulating dates with the date functions

Using Conversion Functions and Conditional Expressions

• Describing implicit and explicit data type conversion
• Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nesting multiple functions
• Applying the NVL, NULLIF, and COALESCE functions to data
• Using conditional IF THEN ELSE logic in a SELECT statement

Reporting Aggregated Data Using the Group Functions

• Using the aggregation functions in SELECT statements to produce meaningful reports
• Using AVG, SUM, MIN, and MAX function
• Handling Null Values in a group function
• Creating queries that divide the data in groups by using the GROUP BY clause
• Creating queries that exclude groups of date by using the HAVING clause

Displaying Data From Multiple Tables

• Writing SELECT statements to access data from more than one table
• Joining Tables Using SQL:1999 Syntax
• Viewing data that does not meet a join condition by using outer joins
• Joining a table by using a self join
• Creating Cross Joins

Using Sub-queries to Solve Queries

• Using a Subquery to Solve a Problem
• Executing Single-Row Sub-queries
• Using Group Functions in a Sub-query
• Using Multiple-Row Subqueries
• Using the ANY and ALL Operator in Multiple-Row Sub-queries

Using the SET Operators

• Describing the SET operators
• Using a SET operator to combine multiple queries into a single query
• Using UNION, UNION ALL, INTERSECT, and MINUS Operator
• Using the ORDER BY Clause in Set Operations

Manipulating Data

• Adding New Rows to a Table Using the INSERT statement
• Changing Data in a Table Using the UPDATE Statement
• Using DELETE and TRUNCATE Statements
• Saving and discarding changes with the COMMIT and ROLLBACK statements
• Implementing Read Consistency
• Using the FOR UPDATE Clause

Using DDL Statements to Create and Manage Tables

• Categorizing Database Objects
• Creating Tables using the CREATE TABLE Statement
• Describing the data types
• Describing Constraints
• Creating a table using a subquery
• Altering and Dropping a table

Creating Other Schema Objects

• Creating, modifying, and retrieving data from a view
• Performing Data manipulation language (DML) operations on a view
• Dropping a view
• Creating, using, and modifying a sequence
• Creating and dropping indexes
• Creating and dropping synonyms