ORACLE SQL Fundamentals Training Course

Primary tabs

Course Language

This course is delivered in Polish or English.

Course Code

osqlfun

Duration Duration

21 hours (usually 3 days including breaks)

Requirements Requirements

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.

Overview Overview

This 3 day course gives an introduction to SQL Developer, SQL*Plus and to SQL, the Structured Query Language used to access a Relational Database and includes the new features of the latest version of ORACLE. 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 2 days are 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.

Course Outline Course Outline

Introduction

  • Overview
  • Aims and Objectives
  • Sample Data
  • Schedule
  • Introductions
  • Pre-requisites
  • Responsibilities

Relational Databases

  • 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*Plus

  • 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
  • Changing the Password
  • Finding Information about Tables
  • Getting Help
  • Where Clause
  • Using SQL Files
  • iSQL*Plus
  • SQL*Plus Commands

Data Retrieval

  • 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

Data Definition

  • Creating a Table
  • Datatypes
  • Simple Create Example
  • Naming Tables
  • Constraints
  • Not Null
  • Primary Key
  • Foreign Key
  • Check
  • Unique
  • Altering Constraints
  • Full Create Example
  • Data Dictionary
  • Alter Table
  • Secondary Indexes
  • B-tree Index
  • Bitmap Index
  • Create Index
  • Explain Plan
  • Using Indexes
  • Clusters
  • Partitioned Tables
  • Creating a Partitioned Table
  • Rename
  • Drop Statement
  • Flashback Table
  • Managing the Recycle Bin

Data Update

  • Insert
  • Some Values
  • Insert
  • All Values
  • Insert
  • Date Values
  • Insert
  • TO_DATE
  • Default Values
  • Using Substitution Variables
  • Transactions
  • Commit
  • Rollback
  • Using Constraints
  • Update
  • Date Arithmetic
  • Update
  • TO_DATE
  • TRUNC
  • Delete
  • Truncate
  • Sequences
  • Grant
  • Create Synonym
  • Create Public Synonym
  • Locking
  • Revoke
  • Savepoint
  • Auto Commit

Multi-Table Retrieval

  • Calculations
  • Precedence
  • ROUND Function
  • Column Alias
  • Date Arithmetic
  • Using Aliases
  • CEIL and FLOOR
  • Cartesian Product
  • Table Join
  • Table Alias
  • Selecting the Join Column
  • Joining without Selecting
  • Views
  • Dropping Views
  • Finding Views
  • Derived Columns
  • With Check Option
  • Snapshot Views
  • Flashback Query

Using Functions

  • 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
  • Workshop

Sub-Query & Union

  • 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
  • Workshop

Advanced Queries

  • 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

Sample Data

  • ORDER Tables
  • FILM Tables
  • EMPLOYEE Tables
  • The ORDER Tables
  • The FILM Tables

PL/SQL

  • 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
  • Describe a Procedure
  • Calling Procedures
  • Creating and Running Functions
  • Showing Errors
  • Describe a Function
  • Calling Functions
  • Creating Triggers
  • Showing Errors

Query Optimisation

  • Query Optimisation
  • Creating The Tables
  • Timing SQL Statements
  • Other Timing Statements
  • Explain Plan
  • Creating the PLAN_TABLE Table
  • Using SET AUTOTRACE
  • Collecting Statistics
  • Primary Key
  • Secondary Indexes
  • 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

Using Objects

  • 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
  • SQL*PLUS REPORTS
  • Objectives
  • ACCEPT and PROMPT
  • Define and Undefine
  • Creating an SQL*Plus Report
  • Break Command
  • Compute Command
  • Saving the Output in a File

Utilities

  • 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

Guaranteed to run even with a single delegate!
Public Classroom Public Classroom
Participants from multiple organisations. Topics usually cannot be customised
From 5206PLN
(83)
Private Classroom Private Classroom
Participants are from one organisation only. No external participants are allowed. Usually customised to a specific group, course topics are agreed between the client and the trainer.
From 5206PLN
Request quote
Private Remote Private Remote
The instructor and the participants are in two different physical locations and communicate via the Internet
From 15200PLN
Request quote
SelfStudy SelfStudy
Self-study courses allow you to learn at your own pace on your own time. There is no live instructor involved. The participants use recorded video, quizzes and reading at their own convenience.
Price not set yet
Register Interest

The more delegates, the greater the savings per delegate. Table reflects price per delegate and is used for illustration purposes only, actual prices may differ.

Number of Delegates Public Classroom Private Classroom Private Remote
1 5206PLN 5206PLN 15200PLN
2 4358PLN 4258PLN 9255PLN
3 4075PLN 3942PLN 7273PLN
4 3934PLN 3784PLN 6283PLN
Cannot find a suitable date? Choose Your Course Date >>
Too expensive? Suggest your price

Related Categories


Course Discounts

Course Venue Course Date Course Price [Remote/Classroom]
Programming in C# 5.0 with Visual Studio 2012 Poznan, Garbary Mon, 2016-05-30 09:00 2685PLN / 1822PLN
Introduction to Machine Learning Wrocław, ul.Ludwika Rydygiera 2a/22 Mon, 2016-05-30 09:00 2730PLN / 1940PLN
Adobe Captivate Kielce Tue, 2016-05-31 09:00 1318PLN / 1127PLN
Excel and VBA Programming for Audit and Finance Professionals Szczecin Tue, 2016-05-31 09:00 1913PLN / 1513PLN
Programming in WPF 4.5 Warszawa, ul. Złota 3/11 Tue, 2016-05-31 09:00 2359PLN / 1355PLN
SQL Fundamentals Warszawa, ul. Złota 3/11 Wed, 2016-06-01 09:00 1358PLN / 853PLN
MS Excel - poziom średniozaawansowany Łódź, ul. Tatrzańska 11 Wed, 2016-06-01 09:00 1044PLN / 840PLN
SQL language in MSSQL Toruń, ul. Żeglarska 10/14 Wed, 2016-06-01 09:00 1568PLN / 1198PLN
Bezpieczeństwo aplikacji internetowych Katowice Wed, 2016-06-01 09:30 3606PLN / 2531PLN
Test Automation with Selenium Kraków Mon, 2016-06-06 09:00 3200PLN / 2433PLN
Test Automation with Selenium Katowice Tue, 2016-06-07 09:30 3431PLN / 2469PLN
MS Excel - poziom średniozaawansowany Katowice Wed, 2016-06-08 09:00 700PLN / 771PLN
Programming in C++ Olsztyn, ul. Kajki 3/1 Mon, 2016-06-13 09:00 2936PLN / 2395PLN
Container management with Docker Trójmiasto Tue, 2016-06-14 09:00 4360PLN / 2774PLN
Excel Advanced Katowice Mon, 2016-06-20 09:00 775PLN / 933PLN
Test Automation with Selenium Warszawa, ul. Złota 3/11 Mon, 2016-06-20 09:00 3431PLN / 2327PLN
PostgreSQL Administration and Development Lublin Mon, 2016-06-20 09:30 4025PLN / 3134PLN
Introduction to R Warszawa, ul. Złota 3/11 Tue, 2016-06-21 09:00 3058PLN / 2123PLN
Creating and managing Web sites Wrocław, ul.Ludwika Rydygiera 2a/22 Mon, 2016-06-27 09:00 3410PLN / 2555PLN
Programming in C Gdynia Mon, 2016-06-27 09:00 1590PLN / 1143PLN
Distributed Messaging with Apache Kafka Katowice Mon, 2016-06-27 09:30 4998PLN / 3288PLN
Design Patterns in C# Wrocław, ul.Ludwika Rydygiera 2a/22 Wed, 2016-06-29 09:00 1865PLN / 1392PLN
Visual Basic for Applications (VBA) for Analysts Poznan, Garbary Mon, 2016-07-04 09:00 1912PLN / 1278PLN
Debian Administration Poznan, Garbary Mon, 2016-07-04 09:00 3157PLN / 2083PLN
A Practical Guide to Successful Pricing Strategies Poznan, Garbary Wed, 2016-07-06 09:00 1427PLN / 1093PLN
Excel and VBA Programming for Audit and Finance Professionals Warszawa, ul. Złota 3/11 Mon, 2016-07-11 09:00 1913PLN / 1441PLN
Machine Learning Fundamentals with R Warszawa, ul. Złota 3/11 Mon, 2016-07-18 09:00 2523PLN / 1828PLN
Building Web Apps using the MEAN stack Szczecin Mon, 2016-07-18 09:00 5538PLN / 3351PLN
Microsoft Access - download the data Poznan, Garbary Wed, 2016-07-20 09:00 1117PLN / 856PLN
Python Programming Warszawa, ul. Złota 3/11 Mon, 2016-08-01 09:00 5790PLN / 3753PLN
Programming in WPF 4.5 Warszawa, ul. Złota 3/11 Mon, 2016-09-05 09:00 2359PLN / 1355PLN
BPMN 2.0 for Business Analysts Wrocław, ul.Ludwika Rydygiera 2a/22 Tue, 2016-09-27 09:00 3110PLN / 2337PLN

Upcoming Courses

VenueCourse DateCourse Price [Remote/Classroom]
Wrocław, ul.Ludwika Rydygiera 2a/22Tue, 2016-06-14 09:006283PLN / 3817PLN
Łódź, ul. Tatrzańska 11Mon, 2016-06-20 09:006283PLN / 3899PLN
Zielona Góra, ul. Reja 6Mon, 2016-06-20 09:006283PLN / 3858PLN
Toruń, ul. Żeglarska 10/14Tue, 2016-06-21 09:006283PLN / 3899PLN
Bydgoszcz, ul. Dworcowa 94Tue, 2016-06-21 09:006283PLN / 3867PLN

Some of our clients