Oracle Database 12c: SQL Fundamentals

IN-101
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