Oracle Database: SQL Fundamentals

 

Course Description:

  The Oracle Database 12c: SQL Fundamentals course is a 5-day course that is designed to teach students the basics of the SQL language and the Oracle Relational Database Management System (RDBMS).

Students will become familiar with the differences in the working environment between a traditional on-premise database installation and the Oracle database service cloud-computing platform. The course will also cover 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.

This course takes a holistic approach to SQL training in that it incorporates data modeling theory, relational database theory, graphical depictions of theoretical concepts and numerous examples of actual SQL syntax into one learning experience. Students will learn how to complete of an application schema definition by creating database objects such as relational views, sequences, synonyms, indexes and others to compliment the table definitions. The course will also cover the topic of data integrity and how this is protected using declarative constraints.

Students will also learn about database object security and database performance.

This course is taught using version 12c, but the topics are applicable for either version 11g or 12c.

Topics covered in the course include:

• RELATIONAL DATABASES & DATA MODELS
• SELECTION & SETUP OF THE DATABASE INTERFACE
• USING THE DATABASE INTERFACE
• INTRODUCTION TO THE SQL LANGUAGE
• THE SELECT STATEMENT
• RESTRICTING RESULTS WITH THE WHERE CLAUSE
• SORTING DATA WITH THE ORDER BY CLAUSE
• PSEUDO COLUMNS, FUNCTIONS & TOP-N QUERIES
• JOINING TABLES
• USING THE SET OPERATORS
• SUMMARY FUNCTIONS
• AGGREGATING DATA WITHIN GROUPS
• USE DDL TO CREATE & MANAGE TABLES
• USE DML TO MANIPULATE DATA
• UNDERSTANDING THE DATA MODELS
• ABOUT THE SQL-99 STANDARD
• ENHANCING GROUPS WITH ROLLUP & CUBE
• USING THE CASE EXPRESSION
• SQL FUNCTIONS: NUMERIC HANDLING
• SQL FUNCTIONS: DATE HANDLING
• DATABASE OBJECTS: ABOUT DATABASE OBJECTS
• DATABASE OBJECTS: RELATIONAL VIEWS
• DATABASE OBJECTS: INDEXES
• DATABASE OBJECTS: CREATING OTHER OBJECTS
• DATABASE OBJECTS: OBJECT MANAGEMENT USING DDL
• DATABASE OBJECTS: SECURITY
• DATA INTEGRITY USING CONSTRAINTS

Target Student:This course is intended for Oracle professionals, both business and systems professionals.

 

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 SEQUENCES

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 SEQUENCES & DROP SEQUENCES

ALTER SEQUENCES

DROP SEQUENCES

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 CONSTRAINTS

NOT NULL Example

CHECK CONSTRAINTS

UNIQUE CONSTRAINTS

PRIMARY KEY CONSTRAINTS

REFERENCES CONSTRAINTS

ON DELETE CASCADE Example

ON DELETE SET NULL Example

CONSTRAINTS ON EXISTING TABLES

 

 

 

We offer live-instructor group training classes for your team.

Upskill your team from the comfort of your office or online.

Benefits of Group Training Include:

• Experienced Professional Instructor Trains Your Team
• Content Focused On Your Team's Needs
• Convenient Scheduling and Class Setup
• Significant Per/Student Cost Savings
• Online, On-Site and Blended Options Available

Get A Free Consultation