Skip to content Skip to main navigation Skip to footer

Oracle Application Express

Objectives

Oracle Application Express — This tutorial covers the following objectives:

  • Distinguish between application software and system software and give an example of each
  • Log-in to the Oracle Application Express practice environment
  • Execute a simple query to retrieve information from the Database
  • Apply the rules of SQL to display all columns and a subset of columns specified by criteria

Purpose of Oracle Application Express.

  • Every day, in one way or another, we come in contact with computer applications.
  • If you checked your email today, it was probably done using an application.
  • If you bought an item at a grocery store, the clerk scanned the item using an application that calculated your bill and updated the store inventory.
  • In this course, you will learn the syntax of SQL using the application called Oracle Application Express.

Application Programs

  • Although computers have been around for a very long time (possibly before you were born), their use for business and personal computing didn’t take place until application software programs were developed.
  • Application programs allowed the end user—people like you and me—to be able to buy fully developed, ready-to-use programs.
  • It was no longer necessary to know how the program worked, just that it did work and accomplished what we wanted it to do.
  • Application program software is different from system software.
  • System software consists of low-level programs designed to interact with the computer hardware.
  • Operating systems, compilers, and system utilities are examples of system software.
  • In contrast, application software includes programs for word processing, databases, gaming, email, and graphics.
  • Yahoo.com uses the Oracle database to store data.
  • Rather than having everyone who wants to search the database or retrieve email learn SQL, an application has all of the SQL (and other coding languages) pre-programmed into it.
  • With a few mouse clicks, users have access to all of the information they need.

In short, application software is a program that gives computer instructions that provide the user with tools to accomplish a task.

System software is a program that interacts with the computer hardware.

Using Applications

  • An application is like a bike.
  • To ride a bike, you need to know enough to make it work.
  • It has a way how to be ridden.
  • Could you ever learn how to ride it, you will be able to enjoy and make the most of it.

Oracle Application Express

Oracle Application Express
  • In this SQL database tutorial series, you will use Oracle Application Express.
  • This application enables many developers to build and access applications as if they were running in separate databases.
  • With built-in features such as design themes, navigational controls, form handlers, and flexible reports, Oracle Application Express accelerates the application development process.
  • Two components in Oracle Application Express are:
    • SQL Workshop
    • Application Builder
  • To learn SQL, you will use the SQL Workshop component.
  • To design an application, you use Application Builder.
  • Oracle Application Express (APEX) is the tool that we will use to allow you to build tables and retrieve information from an Oracle database.
  • When retrieving information from a database, you will often have to find a subset of the data based on specific search criteria.
  • Becoming familiar with SQL will help you more quickly find the information that you need.
  • Oracle Application Express (APEX) accounts are supplied without tables or data.
  • A script file and instructions how to run the script can be found in the attachements at the end of this article.

Basic SELECT Statement

  • The SELECT * command returns all the rows in a table.
  • The syntax is (The rules governing the formation of statements in a programming language):
SELECT *
FROM <table name>;
  • For example:
SELECT *
FROM employees;

SELECT Statement with a Condition

  • To return a subset of the data, modify the SELECT statement.
  • The syntax is:
SELECT <column name 1, column name 2, etc.>
FROM <table name>
WHERE <condition>;
  • For example:
SELECT first_name, last_name, job_id
FROM employees
WHERE job_id = 'SA_REP';
  • The starts with the word WHERE followed by a and a Comparison Operator (=, >, <, etc.) followed by a value or IS NULL, IS NOT NULL.
  • Subset: a part of a larger group of related things.
  • Comparison Operator: used in conditions that compare one expression with another expression or value.
  • The syntax of SELECT statements and operators are discussed in more detail in subsequent tutorials.

Correcting errors

When learning to write SQL statements, it is normal to make mistakes in the beginning. With practice and experience, you make less mistakes, and are able to rectify errors more easily.

  • When entering SQL commands, it is important to use the correct spelling, otherwise you will get an error message.
  • For example (SELECT: spelling incorrect):
    • Would result in the error message: ORA-00900: Invalid SQL Statement
SEECT *
FROM employees;
  • To rectify, simply correct the spelling and run again.
  • It is also important to use the correct names and spelling for columns and tables.
  • For example (employees table name – spelling incorrect)
    • Would result in the following error: ORA-00942: table or view does not exist
SELECT *
FROM employee;
  • To rectify, simply correct the spelling and run again
  • For example (first_name column – entered incorrectly):
    • Would result in the error message: ORA-00904: “NAME”: invalid identifier
SELECT name
FROM employees;
  • To rectify, simply enter the correct column name and run again.

Terminology

Key terms used in this tutorial included:

  • Application software
  • System software
  • Oracle Application Express
  • Syntax
  • Subset
  • Comparison Operator

Summary

In this tutorial, you should have learned how to:

  • Distinguish between application software and system software and give an example of each
  • Log-in to the Oracle Application Express practice environment
  • Execute a simple query to retrieve information from the Database
  • Apply the rules of SQL to display all columns and a subset of columns specified by criteria

Attachments

Was This Article Helpful?

2
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *