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