Introduction to Oracle9i SQL

Course Overview: This course provides you with a solid foundation in SQL programming. This course also aims to prepare you for the ‘Introduction to Oracle 9i SQL’ exam provided by Oracle. This is one of the exams that contribute towards becoming an Oracle Certified Associate (OCA).

Pre-requisite knowledge: Basic computer skills

Related Job roles: Database Administrator, Programmer, Data Analyst

Course Duration: 4 weeks (evenings) or 7 weeks (weekends)

Price: £540

Course Contents:

Writing Basic SQL Select Statements:

• List the capabilities of SQL SELECT statements
• Execute a basic SELECT statement
• Differentiate between SQL statements and iSQL*Plus commands

Restricting and Sorting Data:

• Limit the rows retrieved by a query
• Sort the rows retrieved by a query

Single-Row Functions:

• Describe various types of functions available in SQL
• Use character, number, and date functions in SELECT statements
• Use conversion functions

Displaying Data from Multiple Tables:

• Write SELECT statements to access data from more than one table using equality and non-equality joins
• View data that generally does not meet a join condition by using outer joins
• Join a table to itself using a self-join

Aggregating Data using Group Functions:

• Identify the available group functions
• Use group functions
• Group data using the GROUP BY clause
• Include or exclude grouped rows by using the HAVING clause

Subqueries:

• Describe the types of problems that subqueries can solve
• Define subqueries
• List the types of subqueries
• Write single-row and multiple-row subqueries

Producing Readable Output with iSQL*Plus:

• Produce queries that require a substitution variable
• Produce more readable output
• Create and execute script files

Manipulating Data:

• Describe each DML statement
• Insert rows into a table
• Update rows in a table
• Delete rows from a table
• Merge rows in a table
• Control transactions

Creating and Managing Tables:

• Describe the main database objects
• Create tables
• Describe the datatypes that can be used when specifying column definition
• Alter table definitions
• Drop, rename and truncate tables

Including Constraints:

• Describe constraints
• Create and maintain constraints

Creating Views:

• Describe a view
• Create, alter the definition, and drop a view
• Retrieve data through a view
• Insert, update and delete data through a view

Creating Other Database Objects:

• Create, maintain and use sequences
• Create and maintain indexes
• Create private and public synonyms