Tag: Oracle SQL

Oracle SQL Language Training Course

Posted on by admin

Duration

14 hours (usually 2 days including breaks)

Requirements

  • Computer skills
  • Knowledge of any operating system

Overview

The course answers questions

How to build a query?

What possibilities have SQL?

What is a relational database?

What is the structure and SQL commands

Course Outline

Relational database models

  • The structure of a relational database
  • Connection types of tables
  • The normalization and denormalization database
  • Database Management System (RDBMS)

Relational Operators

Characteristics of declarative SQL language SQL Syntax Division language DQL, DML, DDL, DCL Language DQL (Data Query Language)

  • SELECT queries
  • Aliases columns, tables
  • Service date (DATE types, display functions, formatting)
  • Group Features
  • Combining internal and external tables
  • UNION operator
  • Nested Subqueries (the WHERE clause, the table name, column name)
  • Correlated subqueries

Data Modification Language

  • Inserting rows (INSERT clause)
  • Inserting via query
  • Updating rows (UPDATE)
  • Removing rows (DELETE)

Data Definition Language

  • Create, change of ownership, remove tables (CREATE, ALTER, DROP) Creating tables by asking (CREATE TABLE …. AS SELECT …)

CONSTRAINTS

  • Options NULL and NOT NULL
  • CONSTRAINT clause
  • Condition PRIMARY KEY
  • Condition UNIQUE
  • Condition FOREIGN KEY
  • DEFAULT clause

Transactions

  • The command COMMIT, ROLLBACK, SAVEPOINT

Language DCL

  • Granting and revoking permissions (GRANT, REVOKE)
  • Roles
  • Creating Users
  • sequences
  • Synonyms
  • The views (perspective)

Oracle SQL for development and database management Training Course

Posted on by admin

Duration

35 hours (usually 5 days including breaks)

Overview

This 5-day course demonstrates how to use Oracle SQL for database development and database management.

Course Outline

Database Development

  • Recapping the basic principles behind relational databases
    • Concepts and terminology     
  • Retrieving data using the SELECT statement
  • Using simple and more complex JOINS to retrieve data from multiple tables
    • SELF, INNER and OUTER joins
  • Restricting and sorting data, conditional expressions
    • Single row functions: string, date and time manipulation
    • IF-THEN-ELSE statements
  • Conversion of data between types
  • Creating aggregated reports
  • Using correlated and uncorrelated subqueries in SELECT statements
  • Retrieving and manipulating data using subqueries
  • Running Data Manipulation Statements in Oracle to manage database transactions
  • Query optimisation and efficiency

Database Management

  • The Oracle Data Dictionary: introduction and usage
  • Creating views, indexes, constraints and synonyms
  • Controlling and revoking user access to schema objects (tables, views)
  • Managing indexes and constraints

ORACLE SQL Fundamentals Training Course

Posted on by admin

Duration

14 hours (usually 2 days including breaks)

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

This 2 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

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

Oracle SQL for beginners Training Course

Posted on by admin

Duration

21 hours (usually 3 days including breaks)

Requirements

  • Computer literacy
  • Knowledge of any operating system

Overview

Listeners

This training is addressed for people starting to work with the SQL language in Oracle database

The course answer for questions:

  • How to build a query?
  • What possibilities have SQL?
  • What is a relational database?
  • What is the structure and SQL commands

Course Outline

Relational database models

  • The structure of a relational database
  • Connection types of tables
  • The normalization and denormalization database
  • Relational Operators

Download the data

  • Rules for writing SQL queries
  • The syntax for the SELECT
  • Selecting all columns
  • Inquiries from arithmetic operations
  • Aliases columns
  • Literals
  • Concatenation operator

Limiting results

  • The WHERE clause
  • Comparison operators
  • LIKE Condition
  • Prerequisite BETWEEN … AND
  • IS NULL condition
  • Condition IN
  • Boolean operators AND, OR and NOT
  • Many of the conditions in the WHERE clause
  • The order of the operators.
  • DISTINCT clause

Sorting Data

  • The ORDER BY clause
  • Sorting by multiple columns or expressions

SQL functions

  • The differences between the functions of one and multilines
  • Features text, numeric, date,
  • Explicit and implicit conversion
  • Conversion functions
  • Nesting functions
  • Viewing the performance of the functions – dual table
  • Getting the current date function SYSDATE
  • Handling of NULL values

Aggregating data using the grouping

  • Grouping functions
  • How grouping functions treat NULL values
  • Create groups of data – the GROUP BY clause
  • Grouping multiple columns
  • Limiting the function result grouping – the HAVING clause

Retrieving data from multiple tables

  • Types of connectors
  • The use NATURAL JOIN
  • Aliases tables
  • Joins in the WHERE clause
  • INNER JOIN Inner join
  • External Merge LEFT, RIGHT, FULL OUTER JOIN
  • Cartesian product

Subqueries

  • Place subqueries in the SELECT command
  • Subqueries single and multi-lineage
  • Operators Subqueries single-line
  • Features grouping in subquery
  • Operators Subqueries multi-IN, ALL, ANY
  • How NULL values ​​are treated in subqueries

Operators collective

  • UNION operator
  • UNION ALL operator
  • INTERSECT operator
  • MINUS operator

Insert, update, and delete data

  • INSERT command
  • Copy data from another table
  • UPDATE command
  • DELETE command
  • TRUNCATE command

Transactions

  • Commands COMMIT, ROLLBACK, and SAVEPOINT

DDL commands

  • The main database objects
  • Rules for naming objects
  • Creating tables
  • The data types available for columns
  • DEFAULT option
  • Option NULL and NOT NULL

Managing tables

  • Referential integrity CHECK, PRIMARY KEY, FOREIGN KEY, UNIQUE
  • Create a table by the query
  • Delete a table DROP TABLE
  • DESCRIBE command

Other schema objects

  • Sequences
  • Synonyms
  • Views