Course Details
In this course, you will receive a hands-on introduction to SQL Basics and SQL*Plus, and you will learn the basics of relational databases and how to access them through SQL and SQL*Plus. This course also covers topics necessary to the development and administration of an Oracle 11g database.
Course Prerequisites
An understanding of relational database design concepts
Course Agenda
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/DisplayingConstraint 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