ORACLE SQL Fundamentals
Duration 2-3 days
This course gives an introduction to SQL Developer and to SQL, the Structured Query Language used to access a relational database. It includes the new features of the latest version of ORACLE and the principles learnt may also be applied to databases as diverse as Microsoft SQL Server, MySQL, Access, Informix and DB2.
The course takes the format of a workshop, with a mix of lecture, working examples and practical exercises. Although the content may be customised, at least 1 day is needed to cover the core elements.
Full course notes are provided along with sample database files, example SQL files and free software tools for use in accessing an ORACLE database.
Desktop and ORACLE Version
The course may be run on a Windows or Linux desktop and may be customised to run on ORACLE version 9i, 10g, 11g or 12c.
The course is suitable for those who have some knowledge of SQL as well as those who are using ORACLE for the first time. Previous experience with an interactive computer system is desirable but not essential.
A natural follow-up to this course would be the Introduction to ORACLE PL/SQL course. This describes the ORACLE application development environment which allows the writing of stored procedures, functions and triggers.
There should be no more than 12 delegates on the course and all delegates should meet the above pre-requisites. Each delegate should have access to a PC running Microsoft Windows with at least 1GB of memory, 20GB of free disc space and a DVD drive. The course uses the free software ORACLE Virtual Box, which may be installed before the course starts if preferred, and a supplied image file. For the trainer, a whiteboard, flipchart and pens are required and an overhead
projector that can link to a laptop would be a great help.
Overview, Aims and Objectives, Sample Data, Schedule, Introductions, Pre-requisites, Getting Value, Responsibilities, Self Study Sections, Contents Relational Databases Objectives, The Database, The Relational Database, Tables, Rows and Columns, Sample Database, Selecting Rows, Supplier Table, Primary Key Index, Secondary Indexes, Relationships, Analogy, Foreign Key, Foreign Key, 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, The RDBMS, Advantages of an RDBMS, Structured Query Language, DML - Data Manipulation Language, DDL – Data Definition Language, DCL - Data Control Language, Why Use SQL?, What is PL/SQL?, The ORACLE RDBMS, Course Tables Handout
Objectives, SQL Tools, 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, DUAL Table, Order By Clause, Top-N Queries, Concatenation, Selecting Text, IN Operator, BETWEEN Operator, LIKE Operator, UPPER/LOWER Functions, Single Quotes,
Finding Metacharacters, Regular Expressions, REGEXP_LIKE Operator, Null Values, IS NULL Operator, NVL, Accepting User Input, Finding & Character, Common Errors, Self Study, && Variables, Define and Undefine, Verify Option, Single Quotes – q Operator, Date Comparisons, Day Of The Week, Date
Display, More on Regular Expressions
Objectives, Creating a Table, Datatypes, Simple Create Example, Naming Tables, Identity Columns, Constraints, Not Null, Primary Key, Check, Unique, Foreign Key, Altering Constraints, Full Create Example, Data Dictionary, Alter Table, Secondary Indexes, Create Index, Explain Plan, Using Indexes, Drop Statement, Rename, Flashback Table, Self Study, B-tree Index, Bitmap Index, Managing the Recycle Bin
Objectives, Insert, Some Values, Insert, All Values, Insert, Date Values, Insert, SYSDATE, Insert, TRUNC, Insert, TO_DATE, Insert, Default Values, Using Substitution Variables, Transactions, Commit, Rollback, Update, Delete, Truncate, Update, TO_DATE, Update, TRUNC, Create As Select, Insert As Select, Insert ALL, Sequences, Grant, Create Synonym, Create Public Synonym, Locking, Revoke, Privileges, Self Study, Savepoint, Auto Commit, Date Arithmetic, Temporary Tables, Using Constraints, Workshop - optional
Objectives, Calculations, Order of Precedence, Remember BODMAS, ROUND Function, Column Alias, Date Arithmetic, Using Aliases, Cartesian Product, Table Join, Table Alias, Selecting the Join Column, Joining without Selecting, CEIL and FLOOR, ANSI 92 Join Syntax, Implicit Join Notation, Explicit Join Notation, Equi-Join, Outer Joins, Left Outer Join, Right Outer Join, Full Outer Join, Views, Dropping Views, Finding Views, Derived Columns, With Check Option, With Read Only Option, Flashback Query, Self Study, ORACLE Outer Join, NON EquiJoin, Natural Join, Cross Join, Materialized Views, Merge, Workshop
Objectives, Character Functions, UPPER, LOWER, INITCAP, DISTINCT Option, SUBSTR, INSTR, Number Functions, MOD, NULL Functions, NVL, Nested Functions, NVL2 Function, NULLIF Function, COALESCE Function, DATE Functions, Conversion Functions, TO_CHAR, TO_NUMBER, Aggregate Functions, COUNT, Group By Clause, Rollup and Cube Modifiers, Grouping Clause, Having Clause, Grouping By Functions, Grouping By Date, DECODE, CASE, Grouping By Time Interval, Self Study, TRIM, LTRIM, RTRIM, LPAD, RPAD, REPLACE, LENGTH
Sub-Queries And Union
Objectives, Single Row Sub-queries, Multiple Row Sub-queries, EXISTS and NOT EXISTS, Inline View, Union, Union - All, Intersect and Minus, Union – Checking Data, Outer Join, Self Study, ALL, ANY and SOME Operators, Workshop - optional
Objectives, ROWNUM and ROWID, Top N Analysis, RANK Analysis, Reflexive or Self Join, Correlated Sub-queries, Correlated Sub-queries with Functions, Correlated Update, Hierarchical Queries, Examples, Master Detail Relationship
ORDER Tables, FILM Tables, The ORDER Tables, The FILM Tables, The FILM Tables, The ORDER Tables, The FILM Tables, The FILM Tables
Objectives, Object-oriented Database, Object-relational Database, Creating Objects, Creating Tables with Objects, Using Objects in Tables, Large Object Support, LOB Datatypes, Creating Tables with LOBs, Inserting an Empty LOB, Creating Tables with BFILEs, Creating Directories for BFILEs, Inserting a BFILE
Objectives, What is PL/SQL?, Why Use PL/SQL?, Block Structure, Sample Code, SELECT Statement, Using Variables, Accepting User Input, Exceptions, Other DML Statements, Creating Procedures, Showing Errors, Calling Procedures, Creating and Running Functions, Showing Errors, Calling Functions, Creating Triggers, Showing Errors
Objectives, What is a Utility?, Export Utility, Using Parameters, Import Utility, Using Parameters, SQL*Loader Utility, Running the Utility
Objectives, Query Optimisation, Parsing SQL, Syntax Check and Semantic Analysis, Hard vs Soft Parse, Why not Check the Shared Pool First?, PLSQL Example, Timing SQL Statements, Other Timing Statements, Explain Plan, Using SET AUTOTRACE, Collecting Statistics, Explain Results, The Query Optimizer, Rule Based Optimization, Cost Based Optimization, Choose Keyword, Gathering Statistics, Optimizer Hints, How to Specify Hints, Using Indexes, Index Types, B*tree Indexes, Bitmap Indexes, Index-organized table, When to Create Indexes, Choosing Composite Indexes
Objectives, SQL*Plus Login, Easy Connect, Using /NOLOG, Using SQL*Plus, Ending the Session, SQL*Plus Commands, SQL*Plus Environment, SQL*Plus Prompt, LOGIN.SQL File, Finding Information about Tables, Getting Help, Using SQL Files, ACCEPT and PROMPT, Define and Undefine, Creating an SQL*Plus Report, Break Command, Compute Command, Saving the Output in a File, SQL*Plus Commands