Oracle 11g Foundations: SQL & SQL Plus

1. In the Beginning.Database Management System Overview

• What is a Database?
• Types of DBMSs

2. Introduction to Entity Relationship Modeling

• What is a Data Model?
• Why Use an Entity Relationship Model?
• Input and Components of an Entity Relationship Model
• Reading the Entity Relationship Model
• Guidelines for a Model Review

3. Fundamentals of Relational Database Management Systems

• What is an RDBMS?
• History of SQL
• Physical Data Storage
• Relationships between Tables
• Domains - Data Integrity Constraint

4. SQL Basics and SQL*Plus

• Difference Between SQL, SQL*Plus, and PL/SQL
• Invoking and Leaving SQL*Plus
• Entering and Executing Commands
• The SQL Buffer, SQL*Plus Default Output
• LOGIN.SQL
• The SELECT Command and Mandatory Clauses

5. Using SQL Developer

• SQL Developer Environment and Commands

6. The WHERE and ORDER BY Clause

• WHERE, ORDER BY Clause
• Using the Data Dictionary
• Hierarchical Queries

7. Single Row Functions

• Single Row vs. Group Functions
• Date and Numeric Format Models
• Data Conversion
• Control Statements
• Regular Expressions

8. Joining Data from Multiple Tables in Queries

• The JOIN Condition/The Cartesian Product
• Outer JOINs
• SET Operators
• ANSI SQL99 Joins

9. Group Functions and the GROUP BY Clause

• Group Functions
• Distinct Operator in Group Functions
• GROUP BY, WITH Clause, ROLLUP, CUBE
• GROUPING SETS

10. Using Subqueries and CASE Clauses

• Single-Row, Multi-Row, and Multi-Column Subqueries
• Subqueries in a WHERE Clause
• Subqueries in the FROM Clause - In-Line Views
• Subqueries in the SELECT Clause - Scalar Subqueries
• Correlated Subqueries
• CASE Clause

11. Analytic Functions (Optional)

• Single-Row Analytic Functions
• Aggregating Analytic Functions
• PIVOT/UNPIVOT

12. SQL*Plus Reporting

• SQL*Plus Report Writing Commands
• COLUMN Command/Titles, System Variables
• Master-Detail Report with TITLE and COLUMN Commands
• Control Breaks, Computing Aggregate Amounts
• Using SET Variables in SQL*Plus, SQL to Generate SQL

13. Data Manipulation Language (DML) Commands

• INSERT, UPDATE, DELETE, MERGE Command
• Use of Subqueries
• Transaction Control Commands/Read Consistency/Locking
• The MERGE Command
• Flashback Queries

14. Data Definition Language (DDL) Commands

• DDL Commands, Object Naming, Dropping Objects
• TRUNCATE, COMMENT, RENAME Command
• Creating Tables/Integrity Constraints
• CREATE TABLE, ALTER TABLE Command
• Datatypes, Types of Declarative Constraints
• Default Values
• Creating, Deferrable, Dropping, Disabling/Enabling Constraints/Displaying Constraint Information
• Handling Exceptions
• Views, Synonyms, Sequences, Indexes, Virtual Columns
• Temporary Tables, External Tables

15. Security

• Users, Privileges, Roles, Changing Passwords

16. Getting Data In And Out Of Oracle

• SQL*Loader Basics
• Importing Oracle Data Into Excel
• Direct Database Queries Using ODBC

• Relational database terminology
• Structured Query Language basics
• Format reports using SQL*Plus commands
• Extract and organize information from the database
• Insert, update, and delete information in database tables
• Create and drop tables, views, synonyms, and indexes

Lab 1: Entity Identification

Lab 2: Identifying/Resolving Relationships

Lab 3: Data Normalization

Lab 4: RDBMS Principles

Lab 5: Basic Database Design

Lab 6: Basic Data Definition Language

Lab 7: Creating Tables And Constraints

Lab 8: Creating Views

Lab 9: Data Manipulation

Lab 10: SQL Queries

Lab 11: Filtering And Sorting Data

Lab 12: Single Row Functions

Lab 13: Joining Multiple Tables

Lab 14: Set Operations

Lab 15: Subqueries

Lab 16: Grouping And Group Functions

Lab 17: Advanced Data Analysis

Lab 18: SQL*Plus Reporting

Lab 19: Advanced DDL

Lab 20: Advanced DML

Lab 21: Privileges And Roles

Application designers, developers, and database administrators