Course I: Introduction to Oracle Online Course

There are 12 units of study 

Relational Database Concepts

In this lesson, you'll gain a solid understanding of terms and concepts surrounding a relational database. You'll learn what's involved in the planning of a database and the steps involved in designing a database. Then we'll talk specifically about the Structured Query Language (SQL) and the database tools you'll use throughout the course.

Creating a Database

Today you'll jump right into the course by creating the actual database you'll use in all the lessons that follow. You'll learn about the most common object in Oracle: the table. Then, I'll explain addresses the common datatypes that describe the kind of information a column in a table will store. 

Users and Tables

In this lesson, you'll learn about database users and you'll even have an opportunity to create a user of your very own. This lesson will not only provide you with a crash course in SQL*Plus, but it will also help you create all the tables you'll need for your database. We'll also discuss managing tables and how to modify their structure by altering and deleting information.

Constraints

Today, we'll put together all the pieces of the database puzzle. We'll discuss defining and managing table constraints, then you'll learn about the five integrity constraints that Oracle supports, as well as what their purposes are. I'll show you how to define integrity constraints during table creation with the CREATE TABLE statement and after a table have been created with the ALTER TABLE statement. With this information, you'll create the primary and foreign keys for your database's tables.

Basic SQL Statements

You'll gain considerably more knowledge about the Structured Query Language in this lesson, where you'll be introduced to two new commands—the INSERT and SELECT commands. Then, you'll execute the commands to insert rows of data into the tables and query the data you inserted.

SQL*Plus

Today you'll gain experience with additional SQL*Plus know-how. You'll learn how to correct errors and manipulate lines in the SQL buffer. Then you'll understand additional syntax of the SELECT statement.

Restricting and Sorting Data

In this lesson, you're going to learn more about the SELECT command, the most commonly used SQL statement. The SELECT command allows you to query the data in a database. You'll find that you need to be familiar with the data in your database, and that queries provide you with means to view what's stored in your tables.

Inserting Records into a Table

Today, you'll go beyond the INSERT command's basics. I'll introduce you to more advanced features of the INSERT command, then you'll learn the different methods of inserting rows of data and apply those skills to load data into your tables. You'll also learn some troubleshooting techniques to perform when Oracle returns an error message during an INSERT command.

Updating Rows in a Table

In today's lesson, you're going to learn how to modify data in the database's tables using the UPDATE command. You'll learn how to manipulate the data in your tables by updating the values in the columns and by deleting rows of data. You'll learn how to make those changes permanent with the COMMIT command, as well as how to undo a change with the ROLLBACK command.

Displaying Data from Multiple Tables

Up to this point in the course, you've been creating queries based on a single table. Today, you're going to learn how to display data from multiple tables. You'll learn how to utilize your working knowledge of the SELECT statement and explore the world of joins.

Creating Other Database Objects

Today, you're going to learn more about common database objects and their uses. At this point, you'll have gained considerable knowledge of the most common database object—the table. In this lesson, you'll learn about other database objects and learn how to put them to use, too. Then you'll make use of the data dictionary and query some useful views.

Creating Reports in SQL*Plus

In our final lesson, you're going to learn how to format query results to generate a finished report. You'll use SQL*Plus to create reports by using SELECT statements. Then you'll modify the display of the query results to create a polished report.

Course II: Intermediate Oracle Online Course

There are 12 units of study

Introduction to PL/SQL

In our first lesson, you'll master the basics of PL/SQL and discover how you can best get started using this powerful language.You'll also learn the differences between SQL, SQL*Plus, and PL/SQL today.

Datatypes and Block Structure

The block is the basic unit of PL/SQL programming. In this lesson, you'll learn and understand the various datatypes available to you when declaring variables. You'll also explore the PL/SQL's block structure, its use, and its impact on the scope of variable declarations.

Operators, Expressions, and Datatype Conversions

Today, you're going to learn how to use operators to manipulate variables. You'll build simple expressions that compute two values, as well as complex expressions that consist of function calls, operations using variables, and relational comparisons. You'll also learn about datatype conversions for expressions that contain operands of multiple datatypes.

PL/SQL Functions, IF Statements, and Looping Statements

In this lesson, you'll take a closer look at how to create your own functions. The two main reasons for writing functions are the reduction of code and their ease of use. You'll learn how to code PL/SQL functions, how to define parameters in the functions, and how to store them. I'll also show you how to control the execution of PL/SQL blocks using the IF and looping statements. Then, we'll explore the various IF statements and how they operate, and you'll gain experience working with a simple loop.

GOTO Statement, Statement Labels, and Loop Constructs

In today’s lesson, you'll begin to master more methods for changing the order of execution of PL/SQL blocks. We'll cover statement labels, the GOTO statement, and the different kinds of loops. You'll learn how to branch by using the GOTO statement followed by a statement label. Then, we'll discuss why the statement label must appear in the same block and within the same scope as the GOTO statement. Next, we'll look at control structures. You'll learn about the different kinds of loops and how they operate. Finally, you'll discover techniques for exiting loops and how to simulate a REPEAT...UNTIL loop.

Oracle's Built-in Functions

We'll look at a number of Oracle’s built-in functions in this lesson, including string functions, number functions, date functions, and conversion functions.

Procedures and Packages

In this lesson, we'll focus on procedures and packages and how they allow you to organize your PL/SQL code into logical groups for uncomplicated maintenance and implementation.

Errors and Exceptions

Errors that occur from hardware or network failures, application logic errors, data integrity errors, and other sources are called exceptions. In this lesson, I'll introduce you to the concepts behind PL/SQL exception handling. I'll explain the different types of exceptions and show you how to identify and handle errors in your code.

Types of SQL Statements, Declaring Variables in PL/SQL, and Transaction Management

The Structured Query Language (SQL) is the industry standard for accessing data from relational databases. In this lesson, we'll address the connection to the underlying Oracle database through SQL (Structured Query Language). We'll cover the usage of SQL's Data Manipulation Language (DML) commands within a PL/SQL block. Then you'll learn how to define DML transactions that insert, update, delete, and query the information.

Using Cursors

PL/SQL cursors provide a method for selecting multiple rows of data from the database and processing each row individually. You can use cursors for simple procedures and complex processing. In this lesson, I'll introduce you to cursors and how they may be used.

Database Triggers, Advanced Cursor Concepts, and Managing PL/SQL Code

In this lesson, you'll learn about triggers and experiment with implementing a few different types of functionality. We'll discuss advanced cursor concepts, which include the SELECT...FOR UPDATE statement, the WHERE CURRENT OF clause, and subqueries in cursors. Finally, you'll learn techniques to managing PL/SQL code.

Debugging Your Code

Although some may never admit to it, everyone makes mistakes, so you will inevitably make some coding errors. These errors are usually comprised of syntax and logic errors. In our final lesson, I'll demonstrate how to find these bugs and then how to reduce the number of coding mistakes you may encounter.