Detailed 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
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
Using The CASE Expression
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 Type 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 Relational 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
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