Course Description
Course Objectives
The first portion of this textbook 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.
Agenda
Agenda
1 - RELATIONAL DATABASES & DATA MODELS
About Data Models
About the Relational Model
The Electronics Data Model
About the Relational DBMS
2 - 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
3 - USING THE DATABASE INTERFACE
About Bind & Substitution Variables
Using SQL Developer
Using SQL*Plus
4 - INTRODUCTION TO THE SQL LANGUAGE
About the SQL Language
Characteristics of SQL
Introducing SQL Using Select
SQL Rules
5 - THE SELECT STATEMENT
The Select Statement
Distinct/Unique Keyword
Using Alias Names
6 - RESTRICTING RESULTS WITH THE WHERE CLAUSE
About Logical Operators
Equality Operator
Boolean Operators
REGEXP_LIKE()
In Operator
7 - 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
8 - 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
9 - JOINING TABLES
About Joins
Inner Joins
Reflexive Join
Non-Key Join
Outer Join
10 - USING THE SET OPERATORS
About the Set Operators
SQL Set Operator Examples
UNION Example
INTERECT Example
MINUS Example
UNION All
11 - SUMMARY FUNCTIONS USING SUB-QUERIES
Finding Data with Sub-Queries
Standard Sub-Queries
Correlated Sub-Queries
The EXISTS Operator
12 - AGGREGATING DATA WITHIN GROUPS
About Summary Groups
Find Groups within the Tables
Select Data From the Base Tables
Select Groups from the Results
13 - 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
14 - 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
15 - UNDERSTANDING THE DATA MODELS
The Company Data Model
The Electronics Data Model
16 - 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
17 - ENHANCING GROUPS WITH ROLLUP & CUBE
Using Rollup
The Grouping() Function
Using Cube
18 - 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
19 - SQL FUNCTIONS: NUMERIC HANDLING
About the Numeric Data Functions
GREATEST() Example
ABS() Example
ROUND() Example
TRUNC() Example
SIGN() Example
TO_NUMBER() Example & Data ype 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
20 - 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
21 - DATABASE OBJECTS: ABOUT DATABASE OBJECTS
About Database Objects
About Schemas
Making Object References
22 - DATABASE OBJECTS: RELATIONAL VIEWS
About Relations Views
The Create View Statement
Why Use Views?
Accessing Views with DML
Maintaining View Definitions
Alter View
Drop View
DDL Using SQL Developer
23 - DATABASE OBJECTS: INDEXES
About Indexes
Create & Drop Index Statements
Indexes & Performance
Data Dictionary Storage
24 - 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
25 - DATABASE OBJECTS: OBJECT MANAGEMENT USING DDL
The RENAME Statement
TABLESPACE Placement
CREATE TABLE…TABLESPACE
The Comment Statement
The TRUNCATE TABLE Statement
26 - DATABASE OBJECTS: SECURITY
About Object Security
Grant Object Privileges
Revoke Object Privileges
Object Privileges & SQL Developer
27 - 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
28 - MANAGING CONSTRAINT DEFINITIONS
RENAMING & DROPPING Constraints
ENABLING & DISABLING Constraints
DEFERRED Constraint Enforcement
SET CONSTRAINTS
Handling Constraint Exceptions
Constraints with Views
DATA Dictionary Storage
29 - THE DATA DICTIONARY STRUCTURE
More About the Data Dictionary
OBJECT-SPECIFIC Dictionary Views
USER_UPDATABLE_COLUMNS
The Dictionary Structure
METADATA & SQL Developer