Oracle Database 12c: SQL II Intermediate

Request more details:

Description:
This course builds upon the prerequisite introductory course and considers intermediate-level SQL topics such as writing database queries using the SQL-99 syntax and exploiting the power of built-in functions that extend the capabilities of SQL.
You will learn how to complete an application schema definition by creating database objects such as relational views, sequences, synonyms, indexes and others to compliment the table definitions. The crucial topic of data integrity and how this is protected using declarative constraints is covered.
Prerequisites:
The audience for this course is all Oracle professionals, both business and systems professionals. Among the specific groups for whom this course will be helpful are:

Business and non-IT professionals
Application designers and developers
Business Intelligence (BI) analysts and consumers
Database administrators
Web server administrators
Objectives:
This course demonstrates how one can build intermediate-level and even advanced queries using the SQL-99 join syntax, along with other advanced query topics. It also considers both ANSI/ISO and native Oracle SQL built-in functions and the tremendous power that functions offer to SQL operations. It is difficult to use SQL within a production environment without liberal use of the built-in functions. Among many other tasks, the built-in functions allow one to move beyond the use of primitive date data types and values to include timestamps, time zones and to address other realistic date and time challenges. Finally attention is given to how you complete an application schema by creating database objects to compliment table definitions. One cannot implement a production database application simply with table and column definitions but need to create and manage views, indexes, constraints and other object types.
UNDERSTANDING THE DATA MODEL:
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
DATABASE OBJECTS: CREATING OTHER OBJECTIVES:
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