0345 4506120

Oracle SQL

Course Details

Name Oracle SQL
DescriptionThe Oracle SQL course is designed to give delegates practical experience in writing SQL statements and scripts using Oracle SQL. The basic SELECT statement, the use of SQL functions and the basic table and view handling statements are introduced. Skills Gained The delegates will practise: Creating SQL statements to query database tables Using standard aggregate functions and related SELECT statement clauses Implementing extended SQL functions Joining Tables Using subqueries Creating and altering tables and views Inserting, updating and deleting rows in database tables Who will the Course Benefit? Anyone who needs to use and understand Oracle SQL to query and update data in an Oracle database. Course Objectives To provide the skills needed to query and update data held in an Oracle Relational Database. Examinations The course covers the major topics required for the Oracle examinations 1Z0-051: Oracle Database 11g: SQL Fundamentals I and 1Z0-007: Introduction to Oracle9i SQL, respectively. Together with the Oracle SQL Advanced course, the Oracle SQL course also covers the major topics required for the following Oracle examination: 1Z0-047: Oracle Database SQL Expert. Before taking any exam, ensure you have the recommended experience. The Oracle website lists all exam requirements and these are updated regularly. Exams are not included as part of the course. Requirements There are no formal pre-requisites for the Oracle SQL course, although an understanding of databases and exposure to information technology in general would be useful. Follow-On Courses Oracle PL/SQL Oracle SQL Advanced
URL
Location:
Virtual Classroom
Start Date:
Working Days:
Price:
£1250.00 +vat
Availability:
Exam:
Residential:
Course ID:
487351

Overview

The Oracle SQL course is designed to give delegates practical experience in writing SQL statements and scripts using Oracle SQL.   The basic SELECT statement, the use of SQL functions and the basic table and view handling statements are introduced.

The delegate will learn and acquire skills as follows:

  • Creating SQL statements to query database tables
  • Using standard aggregate functions and related SELECT statement clauses
  • Joining Tables
  • Using basic subqueries and the set operators
  • Using numeric, character and date functions
  • Using conversion and other miscellaneous functions
  • Using SQL parameters
  • Using complex subqueries
  • Creating and altering tables and views
  • Inserting, updating and deleting rows in database tables
  • Managing sequences and synonyms

Who will the Course Benefit?

Anyone who needs to use and understand Oracle SQL to query and update data in an Oracle database.

Learning Objectives

Course Objectives

To provide the skills needed to query and update data held in an Oracle Relational Database.

Pre-Requisites

Requirements

There are no formal pre-requisites for the Oracle SQL course, although an understanding of databases and exposure to information technology in general would be useful.

Follow-On Courses

  • Oracle PL/SQL
  • Oracle SQL Advanced
  • Oracle Database 11g Release 2 DBA - Part I
  • Oracle 12c SQL Performance Tuning
  • Oracle Database 12cR2 Administration
Notes:
  • Course technical content is subject to change without notice.
  • Course content is structured as sessions, this does not strictly map to course timings. Concepts, content and practicals often span sessions.

Course Content

Course Contents - DAY 1

Session 1: RELATIONAL DATABASE CONCEPTS

  • What is an Oracle Database
  • Relational Database Structures
  • Tables, Rows and Columns
  • Indexes, Primary Keys and Foreign Keys
  • Supported Datatypes
  • The Data Dictionary

Session 2: USING SQL*PLUS

  • What is SQL*Plus
  • Getting Started
  • Entering and Executing SQL Statements
  • The SQL*Plus Command Line History
  • Editing SQL Statements
  • The login.sql File
  • Creating, Editing and Executing SQL Files
  • The SQLcl Utility

Session 3: USING SQL DEVELOPER

  • What is Oracle SQL Developer
  • Starting SQL Developer
  • Configure a Connection
  • Navigation Tabs
  • SQL Worksheet
  • Query Builder

Session 4: 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
  • Markup CSV

Session 5: AGGREGATE FUNCTIONS

  • Overview of Built In Aggregate Functions
  • The GROUP BY Clause
  • The HAVING Clause

Session 6: JOINING TABLES

  • Overview of Table Joins
  • Inner Joins
  • Table Aliases
  • Outer Joins
  • Self Joins
  • ANSI Standard Joins

Session 7: BASIC SUBQUERIES AND SET OPERATORS

  • Overview of Subqueries
  • Basic Subqueries
  • Set Operators
  • The Union, Intersect and Minus Operators

Course Contents - DAY 2

Session 8: NUMERIC, CHARACTER AND DATE FUNCTIONS

  • Function Types
  • Using the Table dual to try out Functions
  • Numeric Functions
  • Character Functions
  • String Concatenation
  • Date Arithmetic and Date Functions

Session 9: CONVERSION AND MISCELLANEOUS FUNCTIONS

  • Conversion Functions
  • The NVL and NVL2 Functions
  • The DECODE Function
  • CASE Expressions
  • The COALESCE and NULLIF Functions

Session 10: SQL PARAMETERS

  • Command Line Substitution Parameters
  • The Accept Command
  • The Define and Undefine Commands

Session 11: COMPLEX SUBQUERIES

  • Subqueries Usage
  • In-Line Views
  • Top-N Queries
  • Subqueries with Joins
  • Multi Column Subqueries
  • Correlated Subqueries
  • Subquery Rules
  • Using the ANY, ALL and SOME Operators

Course Contents - DAY 3

Session 12: MANAGING DATA

  • Inserting Rows
  • Updating Rows
  • Deleting Rows
  • Verifying Updates
  • Transaction Control
  • Commit and  Rollback
  • Savepoints
  • Commits and Constraints
  • Amending Data in SQL Developer

Session 13: MANAGING TABLES

  • Creating Tables
  • Specifying Constraints
  • Altering Tables, Columns and Constraints
  • Dropping Tables, Columns and Constraints
  • Recovering Dropped Tables
  • Copying Tables

Session 14: MANAGING INDEXES AND VIEWS

  • Creating Indexes
  • Dropping Indexes
  • Listing Indexes
  • Creating and Using Views
  • Dropping Views
  • Listing Views

Session 15: MANAGING SEQUENCES AND SYNONYMS

  • Create a Sequence
  • View Sequence Details
  • Create a Synonym
  • List Synonyms

Virtual Training

Description:

Quite simply, delegates based at home or in their office (or anywhere where there is an internet connection) link into a live instructor led class via their browser.

 

Using our Virtual Classroom Connect solution, the remote (virtual) delegate will connect to the training room via the internet, where they will be able to see, hear and interact with the instructor and, if it is a scheduled course, the classroom based delegates. They will be able to see all trainer demonstrations, slides and whiteboard work and also have access to the practical workshops/labs as though they were attending in the classroom.

 

Virtual delegates can interact during the sessions, joining in discussions with the trainer and, if relevant, the other delegates. If preferred, delegates can raise questions via the text-based virtual interface where the trainer can answer either in a private one to one conversation or to the class in general.

 

 

Headsets (please note that delegates must provide their own headsets for Virtual courses)

We strongly recommend that the delegates attending this training have high quality headsets, which have noise cancelling microphone capability. This is to enable them to clearly hear the course delivery and contribute effectively within the class setting, ensuring clear reciprocal communication between lecturer and delegates throughout the course.

 

Electronic Course Materials

Delegates will be accessing Electronic Course Material throughout the course. To achieve an optimum learning experience, we highly recommend that the delegates have access to a second screen in order to view their course materials separately from their main screen.

 

Optional Additional Service (Printed Course Materials)

In addition to providing electronic course materials, Printed Course Materials can also be provided at an extra cost per manual supplied. The fee charged includes the cost of couriering the printed course materials to the training venue. Please advise at the time of booking if this optional additional service is required.

 

  

VIRTUAL SOLUTION (REMOTE ACCESS SERVICE)

 

To support the practical workshops/labs element of the Virtual Solution, we provide a Remote Access service whereby the delegates connect to servers or individual systems hosted in our London office.  This eliminates the expense of couriering systems to clients’ sites and the uncertainty associated with using clients’ own systems.  Clients will still need to provide a system for the delegates, however these systems will only require one of the following (freely available) connection tools installed: ssh client (eg putty), VNC viewer (eg UltraVNC) or the Windows Remote Desktop client.

 

For the Remote Access solution, we recommend a fast internet connection and minimum of Windows 7 with 4Gb Ram.  Delegates can also connect using Apple MAC or Unix/Linux based client systems and similar connection software.  Depending on the course being delivered there may be further requirements (such as minimum screen resolution, supported browsers etc).  If there are further requirements, a member of our technical team will advise accordingly once we have a clearer understanding of the systems being provided by the client for the training.

 

The client is responsible for the provision, technical setup, installation, testing and connectivity of the training systems to our servers or individual systems in London prior to the training commencing. Performance will be dependent on a good internet connection from the client’s site. As with all internet access, because the internet is outside of our control we are unable to guarantee response times.

 

VIRTUAL CLASSROOM CONNECTION TEST (ADOBE CONNECT WEB SERVICE)

 

We are responsible for providing the Adobe Connect software and Remote Access to the labs/workshops for the course.  To guarantee the successful delivery of the course, each delegate will need to carry out the required pre-course connectivity tests to both the Adobe Connect server and the labs/workshops server well in advance of the course start date.

 

Before we can fully commit to this training being delivered using Remote Access, it would be necessary to test the connectivity between our servers or individual systems hosted in our London office and the delegate's training systems.  We also need to ensure that the delegates can connect onto the course that they will be attending via the Adobe Connect server.

 

To enable the delegates to carry out the required connectivity tests, our booking procedures will provide connection details for the training prior to the event start date. All connectivity tests will need to be carried out well in advance of the training delivery date.

 

Please note, these tests must be performed on the computer system the delegate will be using on their training course and from the same location where they will be sitting during their course attendance.

 

 

This is to ensure that the delegates have the correct levels of software required on their systems. In the instance where their system may not have the required software, they have the ability to download it onto their system (the delegate may need to discuss this with their technical support if they are attending their course within an office environment).

 

This also confirms if the Adobe Connect web service is able to work through any firewalls the delegates’ company may have in place. Details of any required software is provided as and when the test is run.

 

Note: The delegate's systems will also need a working sound card.

 

To run the Adobe Connect connectivity test from their designated system and location, the delegate should use the following link:

http://admin.adobeconnect.com/common/help/en/support/meeting_test.htm

 

Below is a link to a QuickStart guide that delegates attending courses using Adobe Connect may find useful:

http://seminars.adobe.acrobat.com/vqs-participants/

 

NOTES

 

  • The client will be responsible for all the setups for the delegate's systems including user logins, permissions, etc.

 

  • The client will need to carry out the required Remote Access and Adobe Connect connectivity tests as outlined above. These tests will need to take place well in advance of the training delivery date.

 

  • Training cannot be undertaken using the client’s systems without discussion taking place between our technical team and the client with regard to the systems being provided.

 

  • We strongly recommend that the delegates attending this training are provided with high quality headsets, which have noise cancelling microphone capability. This is to enable them to clearly hear the course delivery and contribute effectively within the class setting, ensuring clear reciprocal communication between lecturer and delegates throughout the course.

 

  • Delegates will be accessing Electronic Course Material throughout the course. To achieve an optimum learning experience, we highly recommend that the delegates have access to a second screen in order to view their course materials separately from their main screen.

 

  • If the client is unable to provide suitable systems for this training course, then pre-configured systems could be provided or the course could be delivered at one of our training centres. Please refer to your account manager for further details.

None

Examinations

Together with the Oracle SQL Advanced course, the Oracle SQL  course also covers the major topics required for the following Oracle examination:

  • Oracle Database SQL Fundamentals 1Z0-071

Before taking this exam, ensure you have the recommended experience.  The Oracle website lists all exam requirements and these are updated regularly.

This exam is not included as part of the course.

Our Customers Include