Oracle Database 12c: SQL Fundamentals

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 JOIN
•      REFLEXIVE JOIN
•      NON-KEY JOIN
•      OUTER JOIN

USING THE SET OPERATORS

•      ABOUT THE SET OPERATORS
•      SQL SET OPERATOR EXAMPLES
•      UNION Example
•      INTERSECT 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

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