Introduction to Oracle SQL

▪ Overview
▪ Aims and Objectives
▪ Sample Data
▪ Schedule
▪ Introductions
▪ Pre-requisites

▪ The Database
▪ The Relational Database
▪ Tables
▪ Rows and Columns
▪ Sample Database
▪ Selecting Rows
▪ Supplier Table
▪ Saleord Table
▪ Primary Key Index
▪ Secondary Indexes
▪ Relationships
▪ Analogy
▪ Foreign Key
▪ Foreign Key
▪ Joining Tables
▪ Referential Integrity
▪ Types of Relationship
▪ Many to Many Relationship
▪ Resolving a Many-to-Many Relationship
▪ One to One Relationship
▪ Completing the Design
▪ Resolving Relationships
▪ Microsoft Access – Relationships
▪ Entity Relationship Diagram
▪ Data Modelling
▪ CASE Tools
▪ Sample Diagram
▪ The RDBMS
▪ Advantages of an RDBMS
▪ Structured Query Language
▪ DDL – Data Definition Language
▪ DML – Data Manipulation Language
▪ DCL – Data Control Language
▪ Why Use SQL?
▪ Course Tables Handout 

▪ SQL Developer
▪ SQL Developer – Connection
▪ Viewing Table Information
▪ Using SQL, Where Clause
▪ Using Comments
▪ Character Data
▪ Users and Schemas
▪ AND and OR Clause
▪ Using Brackets
▪ Date Fields
▪ Using Dates
▪ Formatting Dates
▪ Date Formats
▪ TO_DATE
▪ TRUNC
▪ Date Display
▪ Order By Clause
▪ DUAL Table
▪ Concatenation
▪ Selecting Text
▪ IN Operator
▪ BETWEEN Operator
▪ LIKE Operator
▪ Common Errors
▪ UPPER Function
▪ Single Quotes
▪ Finding Metacharacters
▪ Regular Expressions
▪ REGEXP_LIKE Operator
▪ Null Values
▪ IS NULL Operator
▪ NVL
▪ Accepting User Input

▪ TO_CHAR
▪ TO_NUMBER
▪ LPAD
▪ RPAD
▪ NVL
▪ NVL2 Function
▪ DISTINCT Option
▪ SUBSTR
▪ INSTR
▪ Date Functions
▪ Aggregate Functions
▪ COUNT
▪ Group By Clause
▪ Rollup and Cube Modifiers
▪ Having Clause
▪ Grouping By Functions
▪ DECODE
▪ CASE 

▪ Single Row Sub-queries
▪ Union
▪ Union – All
▪ Intersect and Minus
▪ Multiple Row Sub-queries
▪ Union – Checking Data
▪ Outer Join
▪ More On Joins
▪ Joins
▪ Cross Join or Cartesian Product
▪ Inner Join
▪ Implicit Join Notation
▪ Explicit Join Notation
▪ Natural Join
▪ Equi-Join
▪ Cross Join
▪ Outer Joins
▪ Left Outer Join
▪ Right Outer Join
▪ Full Outer Join
▪ Using UNION
▪ Join Algorithms
▪ Nested Loop
▪ Merge Join
▪ Hash Join
▪ Reflexive or Self Join
▪ Single Table Join

▪ ROWNUM and ROWID
▪ Top N Analysis
▪ Inline View
▪ Exists and Not Exists
▪ Correlated Sub-queries
▪ Correlated Sub-queries with Functions
▪ Correlated Update
▪ Snapshot Recovery
▪ Flashback Recovery
▪ All
▪ Any and Some Operators
▪ Insert ALL
▪ Merge

▪ ORDER Tables
▪ FILM Tables
▪ EMPLOYEE Tables
▪ The ORDER Tables
▪ The FILM Tables

▪ What is a Utility?
▪ Export Utility
▪ Using Parameters
▪ Using a Parameter file
▪ Import Utility
▪ Using Parameters
▪ Using a Parameter file
▪ Unloading Data
▪ Batch Runs
▪ SQL*Loader Utility
▪ Running the Utility
▪ Appending Data

▪ User Management
▪ System permissions and object
▪ Granting
▪ Receiving permission
▪ Roles
▪ Using the links

▪ ALTER TABLE command
▪ Adding, modifying, and deleting columns
▪ Add, remove, turn off constraintów
▪ Create indexes
▪ Flashback operations
▪ External tables 

▪ MERGE command
▪ DML operations of podzapytaniami
▪ DML operations with RETURNING clause
▪ INSERT command multi tables 

▪ CASE expression
▪ DECODE expression
▪ Generate reports by grouping related data
▪ The GROUP BY clause
▪ The HAVING clause
▪ Aggregating data – ROLLUP and CUBE operators
▪ Identification summaries – GROUPING function
▪ Aggregating data – GROUPING SETS operator

▪ Time zones
▪ Variations TIMESTAMP
▪ Differences between DATE and TIMESTAMP
▪ Conversion operations

▪ Subqueries
▪ Multi-column subqueries
▪ The subquery in the FROM clause
▪ Correlated subqueries
▪ WITH clause – re-use query blocks

▪ Inequality in the WHERE clause and the FROM clause
▪ Semijoin
▪ Antijoin

▪ The tree structure
▪ hierarchical Queries
▪ Pseudo column
▪ Sort data in a hierarchical query
▪ Useful functions 

▪ Simple and complex patterns