When you interact with an Oracle database, you do so
with a given identity that can take certain actions.
This identity is represented by an Oracle user.
The actions the user takes occur within a subsection
of the database that can have database objects associated
with it. This subsection is known as a schema.
The Oracle HR Schema contains Human Resources data
for a fictional company. The tables and data provide
an area where you can experiment with Oracle Database
features without concern of damaging production data.
The tables and relationships are structured in a way
that allows for the use of most of the features of SQL.
In addition, most of the examples in Oracle's own
documentation utilize data from the HR Schema.
Oracle SQL can do more than
simply retrieve data from tables.
There are features of the language
which allow certain values that are not
explicitly included in the database to
be derived or calculated. This chapter will
explore several of the features of the language
which provide these capabilities.
A subquery is a SQL SELECT statement that is contained
within another SELECT statement. Subqueries can appear in
various parts of a query, including the SELECT clause, the
FROM clause, the WHERE clause and the HAVING clause. The innermost
query is evaluated prior to queries that contain it. Performance
problems can result if an "expensive" query is nested as
an inner query.
Oracle SQL queries are not limited to selecting data from a single table. Several tables can be accessed within a query and the data aggregated into a single result set which is returned to the user. The relationship between two tables is known as a join and consists of a mapping of values between specified columns in each table. In this chapter you will learn about various types of joins and their application to data retrieval.
Many programming languages have the concept of conditional processing.
Among the most common construct is an if/then structure. Oracle SQL does
allow for conditional processing, but does not use if/then statements
to accomplish it. Instead, the DECODE function and a CASE statement
SQL Functions return values based upon specified input. They
provide functionality that is traditionally addressed in a procedural
rather than a set-oriented programming paradigm. This lesson will
provide an overview of functions included with Oracle that relate
to character data.
SQL Data Manipulation Language is also known as DML. It
is used to manipulate data in the database.
The ability to query data is a different kind
of responsibility than the ability
to change data. Because of this,
a database user must have been granted appropriate
security privileges to perform an
INSERT, UPDATE, or DELETE on a given table.
The target table is the table that is
having rows added, changed or removed.