Skip to content
info@bluemarkacademy.com0212 706 07 05
Contact

Introduction to Oracle SQL

Course type: Virtual Classroom / Online Duration: 5 Days Download PDF
Home›Courses›Introduction to Oracle SQL

Description

This Oracle Database: Introduction to SQL training helps you write subqueries, combine multiple queries into a single query using SET operators and report aggregated data using group functions. Learn this and more through hands-on exercises.

Delegates will learn

  • Understand the basic concepts of relational databases ensure refined code by developers.
  • Create reports of sorted and restricted data.
  • Run data manipulation statements (DML).
  • Control database access to specific objects.
  • Manage schema objects.
  • Manage objects with data dictionary views.
  • Retrieve row and column data from tables.
  • Control privileges at the object and system level.
  • Create indexes and constraints; alter existing schema objects.
  • Create and query external tables.

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
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
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
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
Control user access
  • User Management
  • System permissions and object
  • Granting
  • Receiving permission
  • Roles
  • Using the links
Managing schema objects
  • ALTER TABLE command
  • Adding, modifying, and deleting columns
  • Add, remove, turn off constraintów
  • Create indexes
  • Flashback operations
  • External tables
Operations on large data sets
  • MERGE command
  • DML operations of podzapytaniami
  • DML operations with RETURNING clause
  • INSERT command multi tables
Conditional expressions
  • 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
Managing data in different time zones
  • Time zones
  • Variations TIMESTAMP
  • Differences between DATE and TIMESTAMP
  • Conversion operations
Advanced subqueries
  • Subqueries
  • Multi-column subqueries
  • The subquery in the FROM clause
  • Correlated subqueries
  • WITH clause – re-use query blocks
Join tables
  • Inequality in the WHERE clause and the FROM clause
  • Semijoin
  • Antijoin
The processing of hierarchical data
  • The tree structure
  • hierarchical Queries
  • Pseudo column
  • Sort data in a hierarchical query
  • Useful functions
Regular expressions
  • Simple and complex patterns

Prerequisites

There are no prerequisites