This textbook takes a unique approach to SQL training in that it incorporates data modeling theory, relational database theory, graphical depictions of theoretical concepts and numerous examples of actual SQL syntax into one learning vehicle
TARGET AUDIENCE
The target audience for this textbook is all Oracle professionals, both business and systems professionals. Among the specific groups for whom this textbook will be helpful are
Business and non-IT professionals
Application designers and database developers
Business Intelligence (BI) analysts and consumers
Database administrators
Web server administrators
COURSE OBJECTIVES
The first portion of this textbook considers the logical models upon which a relational database is based and the various configurations and environments in which you may work with the Oracle database. The next segment focuses on the actual SQL syntax for writing database queries. You will begin with the simplest of queries and then proceed onto moderately complex query scenarios. Finally, this textbook covers the DDL, DML and transaction control portions of the SQL language that allow one to create, maintain and manipulate application database objects and application data.
COURSE OUTLINE
RELATIONAL DATABASES & DATA MODELS
About Data Models
About the Relational Model
The Electronics Data Model
About the Relational DBMS
SELECTION & SETUP OF THE DATABASE INTERFACE
Considering Available Tools
Selecting the Appropriate Tool
Oracle Net Database Connections
Oracle PAAS Database Connections
Setup SQL Developer
Setup SQL*Plus
Setup Jdeveloper
USING THE DATABASE INTERFACE
About Bind & Substitution Variables
Using SQL Developer
Using SQL*Plus
INTRODUCTION TO THE SQL LANGUAGE
About the SQL Language
Characteristics of SQL
Introducing SQL Using Select
SQL Rules
THE SELECT STATEMENT
The Select Statement
Distinct/Unique Keyword
Using Alias Names
RESTRICTING RESULTS WITH THE WHERE CLAUSE
About Logical Operators
Equality Operator
Boolean Operators
REGEXP_LIKE()
In Operator
SORTING DATA WITH THE ORDER BY CLAUSE
About the Order by Clause
Multiple Column Sorts
Specify the Sort Sequence
About Null Values within Sorts
Using Column Aliases
PSEUDO COLUMNS, FUNCTIONS & TOP-N QUERIES
Rowid Pseudo Column
ORA_ROWSCN PSEUDO Column
ROWNUM Pseudo Column
About the Built-In Functions
SYSDATE
User & UID
SESSIONTIMEZONE Function
Using the Dual Table
Row Limiting & Top-N Queries
FETCH First x Rows Only Clause
OFFSET x Rows Clause
FETCH…PERCENT Clause
The WITH TIES Option
JOINING TABLES
About Joins
Inner Joins
Reflexive Join
Non-Key Join
Outer Join
USING THE SET OPERATORS
About the Set Operators
SQL Set Operator Examples
UNION Example
INTERECT Example
MINUS Example
UNION All
SUMMARY FUNCTIONS USING SUB-QUERIES
Finding Data with Sub-Queries
Standard Sub-Queries
Correlated Sub-Queries
The EXISTS Operator
AGGREGATING DATA WITHIN GROUPS
About Summary Groups
Find Groups within the Tables
Select Data From the Base Tables
Select Groups from the Results
USE DDL TO CREATE & MANAGE TABLES
Create Table Statement
Column Data Types
Not Null
Default
Describe
Alter Table Statement
Drop Table Statement
Table DDL Using SQL Developer
Alter User Statement
Alter Session Statement
NLS_Language
NLS_Date
USE DML TO MANIPULATE DATA
The Insert Statement
The Delete Statement
The Update Statement
About Transactions
Transaction Rollback
Transaction Commit
Transaction Savepoint
The Set Transaction Statement
Set Transaction Read Only Statement Rules
UNDERSTANDING THE DATA MODELS
The Company Data Model
The Electronics Data Model
ABOUT THE SQL-99 STANDARD
SQL-92 & SQL-99
Cross Joins
Natural Joins
Inner Joins
Implicit Inner Join
Outer Joins
Anti Joins
Named Sub-Queries
ENHANCING GROUPS WITH ROLLUP & CUBE
Using Rollup
The Grouping() Function
Using Cube
SQL FUNCTIONS: CHARACTER HANDLING
What are the SQL Functions?
String Formatting Functions
UPPER(), LOWER() Example
INITCAP() Example
Character Codes Functions
CHR((), ASCII() Examples
PAD & TRIM Functions
RPAD() Example
RTRIM() Example
TRIM() Example
String Manipulation Functions
DECODE () Example
SUBSTR() Example
INSTR() Example
TRANSLATE() Example
REPLACE() Example
String Comparison Functions
LEAST() Example
Phonetic Search Function
SOUNDEX() Example
SQL FUNCTIONS: NUMERIC HANDLING
About the Numeric Data Functions
GREATEST() Example
ABS() Example
ROUND() Example
TRUNC() Example
SIGN() Example
TO_NUMBER() Example & Data ype Conversions
NULL VALUES FUNCTIONS
NVL() & NVL2() Function
NVL() Example (Character)
NVL() Example (Numeric Loss of Data)
NVL() Example (Numeric Output)
NVL2() Example
COALESCE() Function
NULLIF() Function
SQL FUNCTIONS: DATE HANDLING
Date Formatting Functions
TO_CHAR() & TO_DATE() Format Patterns
TO_CHAR() Examples
TO_DATE() Examples
EXTRACT() Example
Date Arithmetic Functions
MONTHS_BETWEEN() Example
ADD_MONTHS() Example
LAST_DAY() Example
NEXT_DAY() Example
TRUNC(), ROUND() Dates Example
NEW_ TIME() Example
About V$TIMEZONE_NAMES
CAST() FUNCTION & TIME ZONES
DATABASE OBJECTS: ABOUT DATABASE OBJECTS
About Database Objects
About Schemas
Making Object References
DATABASE OBJECTS: RELATIONAL VIEWS
About Relations Views
The Create View Statement
Why Use Views?
Accessing Views with DML
Maintaining View Definitions
Alter View
Drop View
DDL Using SQL Developer
DATABASE OBJECTS: INDEXES
About Indexes
Create & Drop Index Statements
Indexes & Performance
Data Dictionary Storage
DATABASE OBJECTS: CREATING OTHER OBJECTS
About Sequences
Referencing NEXTVAL
Referencing CURRVAL
Within the DEFAULT Clause
Alter Sequence & Drop Sequence
ALTER SEQUENCE
DROP SEQUENCE
About Identity Columns
CREATE TABLE…GENERATED AS IDENTITY
ALTER TABLE…GENERATED AS IDENTITY
Start With Limit Value
ALTER TABLE…DROP IDENTITY
About Synonyms
CREATE & DROP SYNONYM Statements
CREATE SYNONYM
DROP SYNONYM
Public Vs Private Synonyms
CREATE SCHEMA AUTHORIZATION
DATABASE OBJECTS: OBJECT MANAGEMENT USING DDL
The RENAME Statement
TABLESPACE Placement
CREATE TABLE…TABLESPACE
The Comment Statement
The TRUNCATE TABLE Statement
DATABASE OBJECTS: SECURITY
About Object Security
Grant Object Privileges
Revoke Object Privileges
Object Privileges & SQL Developer
2 DATA INTEGRITY USING CONSTRAINTS
About Constraints
NOT NULL Constraint
NOT NULL Example
CHECK Constraint
UNIQUE Constraint
PRIMARY KEY Constraint
REFERENCES Constraint
ON DELETE CASCADE Example
ON DELETE SET NULL Example
Constraints on Existing Tables
Constraints & SQL Developer
MANAGING CONSTRAINT DEFINITIONS
RENAMING & DROPPING Constraints
ENABLING & DISABLING Constraints
DEFERRED Constraint Enforcement
SET CONSTRAINTS
Handling Constraint Exceptions
Constraints with Views
DATA Dictionary Storage
THE DATA DICTIONARY STRUCTURE
More About the Data Dictionary
OBJECT-SPECIFIC Dictionary Views
USER_UPDATABLE_COLUMNS
The Dictionary Structure
METADATA & SQL Developer
