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:
Birmingham
Start Date:
Working Days:
Price:
£1250.00 +vat
Availability:
Exam:
Residential:
Course ID:
438494

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

Birmingham - Quayside Tower

Description:

The Centre is open from 8:30am and is located on the 4th floor of the Quayside Tower. When you enter the building, proceed to the lifts.  The lifts are located beyond the front desk, on the left around the corner. On the 4th floor the Highbury Apprenticeships reception is located to your right.  Once you have registered, you will be directed to the refectory area where refreshments will be available for you. The instructor will call you into the room about 9:30am.

Note: Please be cautious if you take any hot or cold drinks to the training room - this is to protect your own health and safety as well as the equipment.

Amenities

Disabled Access

Quayside Tower has step free access, the lifts will take you to the Highbury Apprenticeships floor where again there are no steps.  If you are attending a course at the centre and will be using a wheelchair please notify us before your attendance and we make sure the staff will be ready to assist if needed.  A disabled access toilet is also available.

Toilets

Male, female and disabled WCs are located on the floor.

Lunch

Luncheon vouches will be provided on each day of the training. There is a selection of shops/restaurant within 5-10 min walk where the vouchers can be used.

Location:

Broad Street
Birmingham
B1 2HF

 

Directions:

Our training facilities in Birmingham are easily accessible from all major routes in and out of Birmingham and only 15 minutes walk or a short bus ride from Birmingham New Street Rail Station.  Birmingham Snow Hill Station is also nearby.

Travelling by Foot

From New Street Train Station

Exit into New Street follow sign for Victoria Square. At the square keep left and walk onto Paradise Street.

Turn right towards Fletchers Walk, walk under the dual carriageway. Turn right and walk along Paradise Circus.

Turn left into Broad Street and walk past the Hyatt Hotel (on your left) and The International Convention Centre on your right.

The entrance to Quayside Tower is on your left and next door to the Highlight Comedy Club and the Risa restaurant, the entrance is almost opposite to The Brasshouse.

Enter through the tinted glass doors and report to the reception desk at the 4th floor.

From Snow Hill Train Station

Exit Snow Hill station onto Livery Street. Turn right onto Edmund Street.

Carry on until you pass Birmingham City Council on your right and Birmingham Museum and Art Gallery on your left. Turn left and carry on until you reach Paradise Street. Turn right onto Paradise Street.

Turn right towards Fletchers Walk, walk under the dual carriageway. Turn right and walk along Paradise Circus.

Turn left into Broad Street and walk past the Hyatt Hotel (on your left) and The International Convention Centre on your right.

The entrance to Quayside Tower is on your left and next door to the Highlight Comedy Club and the Risa restaurant, the entrance is almost opposite to The Brasshouse.

Enter through the tinted glass doors and report to the reception desk at the 4th floor.


Travelling by Car

From the M5 / south and west

From Junction 3 M5 and from Dudley/Halesowen take the A456 Hagley Road.

Continue along the A456 until you reach Five Ways – move over to the right and take the underpass into Broad Street.

After about 100 metres at the first main traffic lights turn left into Sheepcote Street.

After about 50 metres at the roundabout turn right into Brindley Place where there is a multi-storey pay car park.

Exit the car park onto Brunswick Street, turn right and walk towards Central Square.

Cross the square and proceed onto Oozells Street.

At the end turn left onto Broad Street. Quayside Tower is on your right.

From M6 Junction 6 and Sutton Coldfield

Take the Aston Expressway A38M towards the City Centre.

Go over the flyover into St Chad’s Queensway and through the underpass.

As you leave the underpass move off to the left and do not enter the next underpass.

Continue across the traffic lights top Paradise Queensway.

Take the second exit, following signs to the ICC.

Carry straight on up Broad Street, pass the Hyatt Hotel (on your left) until the second set of traffic lights.

Turn right into Sheepcote Street.

After about 50 metres at the roundabout turn right into Brindley Place where there is a multi-storey pay car park.

Exit the car park onto Brunswick Street, turn right and walk towards Central Square.

Cross the square and proceed onto Oozells Street.

At the end turn left onto Broad Street. Quayside Tower is on your right.


Travelling by Bus

From New Street Train Station

Walk east towards Bullring Link, turn left at Bullring Link, turn right towards Smallbrook Queensway and right again onto Smallbrook Queensway.

Walk about 7 minutes and turn right onto Suffolk Street Queensway.

Walk to bus stop “Alexandra Theatre” (Stop SF1), take bus no. 22, 23, 24 or 29 and go two stops.

Get off at bus stop “Brindley Place” (Stop BR3), turn left and walk for about 1 minute you will reach Quayside Tower where Highbury Apprenticeships  is based on the 4th floor.


Travelling by Air

From Birmingham International Airport take a train to New Street Station.

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