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
Want To Share This?



Send Whatsapp Message To Us