IT Technical & End User
Course Listings

For further information, or to make a telephone booking, or to receive a competitive quotation for an in-house course, you can call Pentland Training now on 0800 328 2766.

All of our training courses can be delivered on your own site, at Pentland Training facilities, or at an off-site venue.

Hot Topics

3-day ITIL Foundation

5-day PRINCE2™ Practitioner

4-day Business Analysis

3-day Project Management Fundamentals

Course Title:
Fundamentals of SQL and SQL*Plus

Duration: 5 days

Course Overview
This course gives delegates a sound foundation in the Structured Query Language (SQL) and SQL*Plus. SQL is the universal language used to access data and manipulate objects in a Relational Database. SQL is used to query data, enter and change data as well as for creating objects on the database.

Anybody who needs to access data from a database, either through reporting or applications, will need to have a thorough understanding of SQL. Database Administrators who need to maintain and create database objects will also find this course a benefit.
This course covers querying data, entering, changing and deleting information as well as creating a variety of database objects.

Target Audience


Developers, database administrators as well as end users who access and query data from an database.

Course Objectives


By the end of the course attendees will be able to:

• Understand the structure of a database
• Understand the uses of SQL and SQL*Plus
• Create simple and complex reports using SQL*Plus
• Write simple and complex queries using SQL
• Understand and use many SQL functions within querying
• Understand what the data dictionary is used for and be able to query it
• Understand the concepts of locking in a database
• Understand the need for security in a database
• Create users and grant and revoke privileges
• Insert, update and delete rows of data
• Understand transactions and use commits, rollbacks and savepoints
• Create and manipulate database objects

Prerequisites


None

Course Content


Introduction
• Terminology
• Properties of a Relational Table
• Overview of Course Database

Getting Started with SQL and SQL*Plus
• Logging On
• Getting Help
• Viewing Table Definitions
• What is SQL?
• The SQL Command Set
• Writing SQL
• The SELECT Statement
• SQL*Plus Editing Facilities
• Selecting Specific Columns
• Arithmetic Expressions
• Operator Precedence
• Column Aliases
• Concatenation
• Using Literals
• Dealing with NULL Values
• Omitting Duplicates from Queries
• The ORDER BY Clause
• The WHERE Clause
• Using Multiple Conditions in Queries

Runtime Variables
• Using Substitution Variables
• The DEFINE Command
• Using Script Variables
• The ACCEPT Statement

The DUAL Table
• Displaying System Variable Values

Single Row Functions
• What is a Single Row Function?
• Character Functions
• Numeric Functions
• Date Functions

Conversion Functions
• The TO_CHAR Function
• The TO_NUMBER Function
• The TO_DATE Function
• The DECODE Function

Group Functions
• What are Group Functions?
• What Group Functions are Available?
• The GROUP BY Clause
• The HAVING Clause

Table Joins
• Why Join Tables?
• The Cartesian Product
• Equi-join
• Table Aliases
• Non-equi Joins
• Outer Joins
• Self Joins
• Set Operators

Subqueries
• What is a Subquery?
• Single Row Subqueries
• Multiple Row Subqueries
• Comparing More Than One Value
• SOME and ANY Operators
• The ALL Operator
• Nesting Subqueries
• Subquery in a FROM Clause
• HAVING Clause with a Subquery
• Correlated Subqueries (aka Scalar Queries)
• The EXISTS Operator

Tree Walking
• What is Tree Walking?
• An Example of Tree Walking
• Tree Walking Clauses

DDL – Creating Tables
• The CREATE TABLE Command
• Column Data Types
• Creating a Table from Another Table
• Table Constraints
• Constraint Types
• Adding a Column to a Table
• Adding a Constraint to a Table
• Modifying a Column
• Constraints in the Data Dictionary
• Removing Constraints
• Enabling and Disabling Constraints
• Deleting a Table
• Renaming a Table
• Removing All Data from a Table

Using the Data Dictionary
• Accessing the Data Dictionary
• Public Synonyms

Data Manipulation Language
• The INSERT Statement
• The UPDATE Statement
• The DELETE Statement
• Transaction Processing

Sequences
• Creating a Sequence
• Altering a Sequence
• NEXTVAL Pseudocolumn
• CURRVAL Pseudocolumn

Views
• Creating a View
• Simple Views
• Complex Views
• The Force Option
• The WITH CHECK OPTION Clause
• Rules When Using Views
• Removing a View

Users and Security
• Creating a User
• Using Roles
• System Level Privileges
• Object Level Privileges
• Granting and Revoking Privileges
• Creating Synonyms
• Creating Public Synonyms

Locking
• Types of Lock
• Manual Locking
• SELECT...FOR UPDATE
• The LOCK TABLE Statement
• Deadlock

Indexes
• What is an Index?
• How does an Index Work?
• Creating an Index
• Dropping an Index
• When Will an Index Be Used
• What is a ROWID?
• Indexes and the Data Dictionary

Datetime Functions
• Using DATETIME functions
• Using the NVL2 Function to Handle NULL Values

Enhancements to the GROUP BY Clause
• Using ROLLUP as an Extension to the GROUP BY Clause to Produce Subtotal Values
• Using CUBE as an Extension to the GROUP BY Clause to Produce Cross-Tabulation Values
• Using the GROUPING Function to Identify the Row Values Created by ROLLUP or CUBE Operators
• Using GROUPING SETS to Produce a Single Result Set That Is Equivalent to a UNION ALL Approach
• Using the WITH Clause

Advanced Subqueries
• Multiple-Column Subqueries
• Writing a Subquery in the FROM Clause
• Writing and describing Correlated Subquery
• Using EXISTS and NOT EXISTS Operators
• Updating and Deleting Rows Using Correlated Subqueries
• Using Scalar Subqueries in SQL

Hierarchical Retrieval
• Discussing the Benefits of the Hierarchical Query
• Ordering the Rows Retrieved by a Query in a Hierarchical Manner
• Formatting Hierarchical Data so That It Is Easy to Read
• Excluding Branches from the Tree Structure

Extensions to DML and DDL Statements
• Discussing Multitable Inserts
• Creating and Using External Tables
• Naming the Index and Using the CREATE INDEX Command at the Time of Creating Primary Key Constraint

Enter your email address to register for our newsletter: