search our courses:
Quick Navigation
Oracle 11g SQL Fundamentals course
Download PDF version of Oracle 11g SQL Fundamentals course outline
Overview
Oracle is an integrated, standards-based software platform for database management. This course is designed to provide new Oracle users practical experience in writing Oracle SQL statements and scripts using Oracle’s version of SQL. It is an essential building block for all users of Oracle’s full technology range, including databases, development tools, and web technologies.
Audience
This course will be appropriate Oracle Developers, Database Administrators, Technicians or Analyst programmers.
Course Objectives
At the end of this course, delegates will be able to interrogate an Oracle database using SQL commands.
Prerequisites
No prior knowledge of Oracle is assumed however, any knowledge of database terminology and/or working knowledge of databases would be useful.
Course Contents (5 days)
- RELATIONAL DATABASE CONCEPTS
- What is an Oracle Database
- Relational Database Structures
- Tables, Rows and Columns
- Indexes, Primary Keys & Foreign Keys
- Supported Datatypes
- The Data Dictionary
- USING SQL*PLUS
- What is SQL*Plus
- Getting Started
- Enter/Execute SQL Statements
- Editing SQL Statements
- Create, Edit and Execute SQL Files
- USING SQL DEVELOPER
- What is Oracle SQL Developer
- Starting SQL Developer
- Configure a Connection
- Navigation Tabs
- SQL Worksheet
- RETRIEVING DATA WITH THE
SELECT STATEMENT - The SELECT Statement
- The SELECT and FROM Clauses
- Conditions and the WHERE Clause
- Other Conditional Operators
- Logical Operators
- The ORDER BY Clause
- Column Aliases
- Arithmetic Expressions
- Precedence of Operators
- AGGREGATE FUNCTIONS
- Overview of Aggregate Functions
- The GROUP BY Clause
- The HAVING Clause
- JOINING TABLES
- Overview of Table Joins
- Inner Joins
- Table Aliases
- Outer Joins
- Self Joins
- ANSI Standard Joins
- Set Operators
- NUMERIC, CHARACTER AND DATE
FUNCTIONS - Function Types
- Using Table dual to try out Functions
- Numeric Functions
- Character Functions
- String Concatenation
- Date Arithmetic and Date Functions
- CONVERSION AND MISCELLANEOUS
FUNCTIONS - Conversion Functions
- NVL and NVL2
- DECODE
- CASE Expressions
- COALESCE and NULLIF
- SQL PARAMETERS
- Command Line Substitution Parameters
- The Accept Command
- The Define and Undefine Commands
- USING SUBQUERIES
- Overview of Subqueries
- In-Line Views
- Top-N Queries
- Complex Subqueries
- Multi Column Subqueries
- Correlated Subqueries
- Subquery Rules
- Using the ANY, ALL and SOME Operators
- MANAGING DATA
- Inserting Rows
- Updating Rows
- Deleting Rows
- Verifying Updates
- Transaction Control
- Commit and Rollback
- Savepoints
- Commits and Constraints
- Amending Data in SQL Developer
- MANAGING TABLES
- Creating Tables
- Specifying Constraints
- Altering Tables, Columns and Constraints
- Dropping Tables, Columns and Constraints
- Copying Tables
- MANAGING INDEXES AND VIEWS
- Creating Indexes
- Dropping Indexes
- Listing Indexes
- Creating and Using Views
- Dropping Views
- Listing Views
- MANAGING SEQUENCES AND SYNONYMS
- Create a Sequence
- View Sequence Details
- Create a Synonym
- List Synonyms
- RETRIEVE DATA USING SUBQUERIES
- ANY, ALL or SOME Operators
- Correlated Subqueries
- In-Line Views
- The Exists Operator
- The WITH Clause
- Multi-Column Subqueries
- Insert and Update Using a Query
- Correlated Update and Delete
- HIERARCHICAL QUERIES
- Hierarchical data
- The START WITH and CONNECT BY clauses
- The LEVEL pseudo-column
- Sequencing the output
- Eliminating nodes and branches
- ENHANCED GROUPING FEATURES
- Review of basic grouping concepts
- The ROLLUP and CUBE extensions
- The GROUPING SETS extension
- Using the GROUPING and
GROUPING_ID functions - MANAGE LARGE DATA SETS
- Multi-table Inserts
- Conditional and Unconditional Inserts
- Merging Data into a Table
- Table and View Based Merge
- A Sub Query Based Merge
- FLASHBACK TECHNOLOGY
- Flashback Query
- The AS Clause
- Flashback Table
- Flashback Drop
- DATE, TIME AND TIMEZONE SUPPORT
- Date, Timestamp and Interval datatypes
- Handling dates and times
- Handling intervals
- Date, timestamp and interval
functions and literals - Related NLS parameters
- REGULAR EXPRESSION SUPPORT
- Regular Expression Notation
- Character matching
- Repetition operators
- Sub expression grouping
- Regular expression functions
- MANAGE OBJECTS WITH THE
DATA DICTIONARY - The Data Dictionary
- Useful Data Dictionary Tables
- Using the Data Dictionary
- MANAGE SCHEMA OBJECTS
- Column Operations
- Constraint Operations
- Creating and Dropping Indexes
- B-Tree Indexes
- Function Based Indexes
- Descending Indexes
- Creating and Using External Tables
- ACCESS CONTROL
- System Privileges and Roles
- Create Users
- Alter User Details
- Grant and Revoke Object Privileges