Skip Ribbon Commands
Skip to main content

ORA11g001 - Oracle Database 11g R2: SQL Fundamentals I - Complete Library

Price:

Duration: 5 Days

Audience:

Level:

Technology:

Delivery Method:

Software Assurance Value:

Microsoft CPE:

Course Information

Course Description

​This course is a common starting point in the Sideris Oracle database curriculum for administrators, developers and business users. The objective of this course is to provide an introduction to the SQL database language within the context of an Oracle database, based upon the latest features available. 

The primary objective of this course is to consider advanced subjects and techniques pertaining to the SQL database language, based upon the latest features available with the Oracle release. Even professionals experienced in other implementations of the industry-standard SQL language will benefit from the advanced and Oracle-specific features of SQL discussed in this course.

Course Objectives

​Specific subject areas to be explored and objectives to be reached are:


A summation of relational database principles and an introduction to the Oracle relational database server.

Using SQL Developer and other available SQL interfaces.

Writing simple SQL queries which isolate exactly the data desired, and then format and sort the data as needed.

Writing moderately complex SQL queries using various join techniques.

Supplement SQL code with references to pseudo columns and system functions.

Summarizing, grouping and combining data to obtain more meaningful query results and to allow one to draw conclusions and make business decisions based upon the data processed.

Advanced query techniques such as set operations, sub-queries and summary functions, allowing one to solve some of the more complex theoretical query challenges.

Creating and maintaining database tables using the SQL Data Definition Language (DDL).

Managing data within tables using the SQL Data Manipulation Language (DML).

Taking advantage of the latest features found within the database such as the use of regular expressions, support for international data, time zones and other topics.

Learn to use the SQL99 join syntax and learn about the full range of join operations available, such as natural join, inner join, cross join, outer joins and others.

Perform advanced SQL queries using techniques such as CASE structures, rollup and cube operations.

Moving beyond basic table definitions, learn to define the full range of database objects, such as advanced table definitions and the creation of indexes, sequences, views and others.

Use the powerful set of built-in SQL functions. These functions provide sophisticated manipulation of character, numeric and date data elements.

Taking advantage of the latest features found within the Oracle release, such as the handling and tolerating DML errors without application interruption, newly added functions, and other topics.

Obtain database object metadata from the data dictionary.

Understand basic database security and object privileges.

Protect database integrity and extend the conceptual data model using declarative constraints.

Using SQL Developer and other available SQL interfaces.

Develop complex SQL*Plus reports and reusable SQL*Plus scripts, as well as control the SQL*Plus environment.

Course 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:

End-Users and Analysts

Database administrators

Business users and non-technical senior end users

Course Outline

​RELATIONAL DATABASES & SQL


ABOUT RELATIONAL DATABASES

ELEMENTS OF SQL

CHOOSING A SQL & PL/SQL INTERFACE


ABOUT DATABASE CONNECTIONS

ABOUT BIND VARIABLES

USING SQL DEVELOPER

USING SQL*PLUS

USING APPLICATION EXPRESS

BUILDING A SELECT STATEMENT


ABOUT THE SELECT STATEMENT

USING ALIAS NAMES

RESTRICTING DATA WITH THE WHERE CLAUSE


ABOUT LOGICAL OPERATORS

EQUALITY OPERATOR

BOOLEAN OPERATORS

NULL & BETWEEN OPERATORS

IS [NOT] NULL Operator

[NOT] BETWEEN Operator

FINDING TEXT STRINGS

[NOT] LIKE Operator

REGEXP_LIKE()

IN OPERATOR

SORTING DATA WITH THE ORDER BY CLAUSE


ABOUT THE ORDER BY CLAUSE

MULTIPLE COLUMN SORTS

SPECIFYING THE SORT SEQUENCE

ABOUT NULL VALUES WITHIN SORTS

USING COLUMN ALIASES

PSEUDO COLUMNS & FUNCTIONS


USING ROWID

USING ROWNUM

USING THE FUNCTIONS

SYSDATE

USER & UID

USING THE DUAL TABLE

SESSIONTIMEZONE FUNCTION

JOINING TABLES


ABOUT JOINS

INNER JOIN

REFLEXIVE JOIN

NON-KEY JOIN

OUTER JOIN

USING THE SET OPERATORS


ABOUT THE SET OPERATORS

SET OPERATOR EXAMPLES

SUMMARY FUNCTIONS


ABOUT SUMMARY FUNCTIONS

SUMMARY FUNCTIONS WITH DISTINCT

USING SUB-QUERIES


ABOUT SUB-QUERIES

STANDARD SUB-QUERIES

CORRELATED SUB-QUERIES

AGGREGATING DATA WITHIN GROUPS


ABOUT SUMMARY GROUPS

FINDING GROUPS WITHIN THE BASE TABLES

SELECTING DATA FROM THE BASE TABLES

SELECTING GROUPS FROM THE RESULT TABLE

BUILD SIMPLE SQL*PLUS REPORTS


FORMAT COLUMN OUTPUT WITH COLUMN

DEFINING REPORT BREAKS WITH BREAK

PRODUCE SUBTOTALS WITH COMPUTE

USE DATA DEFINITION LANGUAGE TO CREATE & MANAGE TABLES


CREATE TABLE STATEMENT

NOT NULL

DEFAULT

ALTER TABLE STATEMENT

DROP TABLE STATEMENT

SUPPORTIVE STATEMENTS

DESCRIBE

RENAME

USE DATA MANIPULATION LANGUAGE TO MANIPULATE DATA


ABOUT THE INSERT STATEMENT

ABOUT THE DELETE STATEMENT

ABOUT THE UPDATE STATEMENT

ABOUT TRANSACTIONS

ROLLBACK

COMMIT

SAVEPOINT

SET TRANSACTION

TRUNCATE TABLE

COMPLEX TABLE REFERENCES

SQL99 JOIN TECHNIQUES


CROSS JOINS

NATURAL JOINS

INNER JOINS

OUTER JOINS

ANTI JOINS

USING NAMED SUBQUERIES

ENHANCING GROUPS WITH ROLLUP & CUBE


USING ROLLUP

USING CUBE

USING THE CASE EXPRESSION

SQL FUNCTIONS: CHARACTER

STRING FORMATTING FUNCTIONS

ASCII CODES FUNCTIONS

PAD & TRIM FUNCTIONS

STRING MANIPULATION FUNCTIONS

STRING COMPARISON FUNCTIONS

PHONETIC SEARCH FUNCTIONS

SQL FUNCTIONS: NUMERIC


ABOUT THE NUMERIC FUNCTIONS

NULL VALUE FUNCTIONS

SQL FUNCTIONS: DATE


DATE FORMAT FUNCTIONS

DATE ARITHMETIC FUNCTIONS

DATABASE OBJECTS: RELATIONAL VIEWS


ABOUT DATABASE OBJECTS

ABOUT RELATIONAL VIEWS

UPDATING VIEW DATA

MAINTAINING VIEW DEFINITIONS

ALTER VIEW

DROP VIEW

DATABASE OBJECTS: DATA DICTIONARY STORAGE


ABOUT THE DATA DICTIONARY

OBJECT-SPECIFIC DICTIONARY VIEWS

USER_UPDATABLE_COLUMNS

UNDERSTANDING THE DATA DICTIONARY STRUCTURE

DATABASE OBJECTS: INDEXES


ABOUT INDEXES

USE B-TREE INDEXES

DATABASE OBJECTS: OTHER OBJECTS


MORE ABOUT CREATING TABLES

ABOUT SEQUENCES

ALTER SEQUENCE & DROP SEQUENCE

ALTER SEQUENCE

DROP SEQUENCE

ABOUT SYNONYMS

DROP SYNONYM

CREATE SCHEMA AUTHORIZATION

DATABASE OBJECTS: SECURITY


USER PASSWORDS

OBJECT SECURITY

DATA INTEGRITY USING CONSTRAINTS


ABOUT CONSTRAINTS

NOT NULL CONSTRAINT

CHECK CONSTRAINT

UNIQUE CONSTRAINT

PRIMARY KEY CONSTRAINT

REFERENCES CONSTRAINT

DEFINING CONSTRAINTS ON EXISTING TABLES

MANAGING CONSTRAINT DEFINITIONS


RENAMING & DROPPING CONSTRAINTS

ENABLING & DISABLING CONSTRAINTS

DEFERRED ENFORCEMENT

SET CONSTRAINTS

HANDLING CONSTRAINT EXCEPTIONS

CONSTRAINTS WITH VIEWS

DATA DICTIONARY STORAGE

ADVANCED SQL*PLUS REPORTS


BREAK

COMPUTE

COLUMN

TTITLE & BTITLE

OPTIONAL - ADVANCED SQL*PLUS REPORTS


REPORT-1

REPORT-2

SOLUTIONS

REPORT-1

REPORT-2

OPTIONAL - BUILDING SQL*PLUS SCRIPTS


USING SQL*PLUS VARIABLES

DEFINE & UNDEFINE

ACCEPT

PROMPT

PAUSE

OPTIONAL - SQL*PLUS SESSION CONTROL


CONTROLLING SYSTEM VARIABLES

SET

SHOW

APPENDIX A


REFERENCE INFORMATION

SQL DDL STATEMENT REFERENCE

SQL DML STATEMENT REFERENCE

SQL TRANSACTION CONTROL STATEMENT REFERENCE

SQL OPERATORS REFERENCE

Course Prerequisites

​No mandatory prerequisites exist for this course. The following prerequisites are recommended:

A-162, Relational Database Design Concepts

Course Schedule
This course is not scheduled yet.

Upcoming Training

SEC+ - Security+ Prep
SEC+ - Security+ Prep