Welcome to ONLC Training Centers
Welcome to ONLC Training Centers


Oracle Database 12c: SQL Fundamentals Course Outline

 (5 days)
Version 12c

Overview
Learning the SQL language is one of the most basic tasks required for the use of a relational database. SQL proficiency is essential for business users, database developers, database administrators and any other database professional. This course introduces the basics of the SQL language and the Oracle Relational Database Management System (RDBMS). One will become acquainted with the differences in the working environment between a traditional on-premise database installation and the Oracle database service cloud-computing platform. This course also 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.

Since SQL is an industry standard language, many of the topics presented and many of the skills you will acquire will be applicable to other database platforms, such as Microsoft SQL Server, IBM DB2, the open-source databases MySQL and PostgreSQL, and others.

This course takes a unique 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 vehicle.

You 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 crucial topic of data integrity and how this is protected using declarative constraints is covered.

With this course we will also leave the idyllic realm of the learning environment and begin to explore such practical real-world considerations as database object security and database performance.

Target Audience
The target 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 database developers
Business Intelligence (BI) analysts and consumers
Database administrators
Web server administrators

Certification
This course and the subsequent ones within this series consider subjects applicable to certification as an Oracle Database Certified SQL Expert. The topics considered are included within "Exam 1Z0-047: Oracle Database: SQL
Certified Expert".

Objectives
The first portion of this course considers the logical models upon which a relational database is based and the various configurations and environments in which you may work with the Oracle database. The next segment focuses on the actual SQL syntax for writing database queries. You will begin with the simplest of queries and then proceed onto moderately complex query scenarios. Finally, this textbook covers the DDL, DML and transaction control portions of the SQL language that allow one to create, maintain and manipulate application database objects and application data.

This course also 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 for one 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 one completes 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 needs to create and manage views, indexes, constraints and other object types.

Contents

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

2014 Sideris Courseware Corporation
View outline in Word

OSQ12C

Attend hands-on, instructor-led Oracle Database 12c: SQL Fundamentals training classes at ONLC's more than 300 locations. Not near one of our locations? Attend these same live classes from your home/office PC via our Remote Classroom Instruction (RCI) technology.

For additional training options, check out our list of Oracle Courses and select the one that's right for you.

Microsoft Gold Partner
Class Dates
(click date for class times)
(click Enroll for locations)

Fee:  $2995

Savings options:

 15 Day Pass
 CEA Tech Saver
Need a price quote?

Follow the link to our self-service price quote form to generate an email with a price quote.

Email Alert

Receive an email when this class is available as "Ready to Run" or "Early Notice" status.

Attend from your office or home

If you have high-speed internet and two computers you can likely take this class from your office or home.

Need a class for a group?

We can deliver this class for a private group at your location. Follow the link to request more information.

Attend computer classes from ONLC Training Centers Request a copy via mail

GENERAL INFO

Class Format
Class Policies
Student Reviews

Bookmark and Share


HAVE QUESTIONS?
First Name

Last Name

Company

Phone

Email

Location

Question/Comment



ONLC TRAINING CENTERS
800-288-8221
www.onlc.com