Duration: 5 Days
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.
What You Will Learn
- 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
Audience
Application designers, developers, and database administrators
Prerequistes
An understanding of relational database design concepts
Course Outline
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
Course Labs
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
