1 – RELATIONAL DATABASES & DATA MODELS
- About Data Models
- About the Relational Model
- The Electronics Data Model
- About the Relational DBMS
2 – 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
3 – USING THE DATABASE INTERFACE
- About Bind & Substitution Variables
- Using SQL Developer
- Using SQL*Plus
4 – INTRODUCTION TO THE SQL LANGUAGE
- About the SQL Language
- Characteristics of SQL
- Introducing SQL Using Select
- SQL Rules
5 – THE SELECT STATEMENT
- The Select Statement
- Distinct/Unique Keyword
- Using Alias Names
6 – RESTRICTING RESULTS WITH THE WHERE CLAUSE
- About Logical Operators
- Equality Operator
- Boolean Operators
- REGEXP_LIKE()
- In Operator
7 – 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
8 – 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
9 – JOINING TABLES
- About Joins
- Inner Joins
- Reflexive Join
- Non-Key Join
- Outer Join
10 – USING THE SET OPERATORS
- About the Set Operators
- SQL Set Operator Examples
- UNION Example
- INTERECT Example
- MINUS Example
- UNION All
11 – SUMMARY FUNCTIONS USING SUB-QUERIES
- Finding Data with Sub-Queries
- Standard Sub-Queries
- Correlated Sub-Queries
- The EXISTS Operator
12 – AGGREGATING DATA WITHIN GROUPS
- About Summary Groups
- Find Groups within the Tables
- Select Data From the Base Tables
- Select Groups from the Results
13 – 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
14 – 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
15 – UNDERSTANDING THE DATA MODELS
- The Company Data Model
- The Electronics Data Model
16 – 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
17 – ENHANCING GROUPS WITH ROLLUP & CUBE
- Using Rollup
- The Grouping() Function
- Using Cube
18 – 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
19 – 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
20 – 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
21 – DATABASE OBJECTS: ABOUT DATABASE OBJECTS
- About Database Objects
- About Schemas
- Making Object References
22 – 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
23 – DATABASE OBJECTS: INDEXES
- About Indexes
- Create & Drop Index Statements
- Indexes & Performance
- Data Dictionary Storage
24 – 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
25 – DATABASE OBJECTS: OBJECT MANAGEMENT USING DDL
- The RENAME Statement
- TABLESPACE Placement
- CREATE TABLE…TABLESPACE
- The Comment Statement
- The TRUNCATE TABLE Statement
26 – DATABASE OBJECTS: SECURITY
- About Object Security
- Grant Object Privileges
- Revoke Object Privileges
- Object Privileges & SQL Developer
27 – 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
28 – MANAGING CONSTRAINT DEFINITIONS
- RENAMING & DROPPING Constraints
- ENABLING & DISABLING Constraints
- DEFERRED Constraint Enforcement
- SET CONSTRAINTS
- Handling Constraint Exceptions
- Constraints with Views
- DATA Dictionary Storage
29 – THE DATA DICTIONARY STRUCTURE
- More About the Data Dictionary
- OBJECT-SPECIFIC Dictionary Views
- USER_UPDATABLE_COLUMNS
- The Dictionary Structure
- METADATA & SQL Developer