Oracle SQL Definitions

 

"DML" stands for Data Manipulation Language. SELECT, UPDATE, INSERT, and DELETE are the "DML" statements.

A "SELECT" statement must consist of a "SELECT" and a "FROM" clause.

A Mathematical Operation can be performed on the "SELECT" statement.

The "DUAL" table would be used when a user does not want to pull data from a table but rather wants simply to use an arithmetic operation. It contains only one row and one column.

The "NULL" value will be used when you don't know the value of the column. Remember that the Null value means "I don't know;" and any mathematical operation on the null value will return a null result.

The null value function (NVL) can convert a null value an assigned value.

A "column heading" can be used in place of the actual column name. If your column heading is case sensitive, a reserved word, or contains white space, it must be enclosed within double quotes.

A "table alias" can be used in place of the actual table name to make a column a unique identifier.

Two or more columns or strings can be concatenated using a double-pipe.

The "ORDER BY" clause in a select statement will sort the order of a listed table.

The "WHERE" clause can contain comparison operations linked together.

The "LIKE" clause can be used for pattern matching.

The "BETWEEN" clause would be used for a range operation.

The "DECODE" function will match the column values with appropriate return values. It continues matching until it has identified all cases. The last variable is used for the default return value.

A "JOIN" table is: when a query obtains data from more than one table and merges the data together. You may join tables together using "inner join" or "equijoin", "outer join", and "self join".

"inner join" also known as equijoin is an equality operation linking the data in the common columns.

"outer join" returns data in one table even when there is no match in the other table.

A "self join" is based on an equality operation linking the data to itself.

A "Cartesian" product" is caused by joining "N" number of tables while you have less than "N-1" join conditions in the query.

An "Anonymous Column" is caused by joining two tables when they have a common column name in them. You can use table aliases or table names next to a column name to avoid causing the "anonymous column."

The "GROUP BY" clause will assist you in grouping data together.

The "EXISTS" operation produces a "TRUE" or "FALSE" value based on the related sub-query data output. You may use the global column name in your sub-query.

The "IN" operation produces a "TRUE" or "FALSE" value based on the related sub-query data output or list of values.

"MIN," "MAX," and "SUM" are grouping functions that allow you to perform operations on data in a column.

You can assign a variable in a "SELECT" statement at run time with use of a runtime variable.

A Table is a collection of records.

Use the "VARCHAR2" datatype when your input data string varies and does not exceed more than 2000 characters.

Use the "CHAR" datatype when your input data string is fixed and does not exceed more than 2000 characters.

If your input data is number, use the "NUMBER" datatype.

The "DATE" datatype should be used when your input data is "date", "time", or "date and time".

The "RAW" datatype should be used when your input data contains binary data and does not exceed more than 2000 bytes.

If your input data contains text data and does not exceed more than 2 gig, use the "LONG" datatype.

The "LONG RAW" datatype is used if your input data is binary and does not exceed more than 2 Gig.

Use the "ROWID" datatype when your application references to the "rowid" of a table.

The "BLOB" (Binary Large Object) datatype would be used for binary long objects and can store up to 4 gig.

Use the "CLOB" (Character Large Object) datatype if you have to store a book in a column. Its size should not exceed more than 4 gig. Try to use "CLOB" instead of the "LONG" datatype. It is searchable; also more than one column can be defined as Large Object in a table.

The "BFILE" datatype would be used for the large external files. The content of this column points to system files.

The DATA DICTIONARY is a repository of all the database objects that were created by different schemas.

All the information about the database objects is stored in the data dictionary. You will retrieve the data dictionary information using the data dictionary views.

DDL" stands for Data Definition Language. CREATE TABLE, CREATE USER, DROP TABLE, ALTER TABLE are examples of the DDL statements.

The "ALTER" command changes an object.

The "DROP" command removes an object.

The "TRUNCATE" or "DELETE" command removes records from an object. When you use the truncate statement, the "high watermark" will change to the beginning of the table. The truncate statement is a "DDL" statement; and on all DDL statements, the commit is implicit. That is the reason that you can not rollback on the truncate statement. Also, when a table is removed all its indexes, constraints, and references will be removed as well.

The Oracle9i ANSI standard JOIN syntax

You can use Oracle9i ANSI standard JOIN syntax to join the contents of two or more tables together in a single result according to the following syntax.

Syntax:

SELECT col1, col2

FROM table1 JOIN table2

ON condition;

The ANSI standard NATURAL JOIN syntax

A natural join is a join between two or more tables where Oracle joins the tables according to the column(s) in the two or more tables sharing the same name with the following syntax.

Syntax:

SELECT col1, col2

FROM table1 NATURAL JOIN table2;

The USING clause

You can use Oracle9i ANSI standard JOIN syntax to join the contents of two or more tables together in a single result according to the columns in the two tables sharing the same name and be used in the USING clause with the following syntax.

Syntax:

SELECT col1, col2

FROM table1 JOIN table2

USING (col);

The ANSI standard CROSS JOIN syntax

A cross-join is produced when you use the CROSS keyword in your ANSI/ISO -compliant join query. You use it when you want to retrieve a Cartesian product.

Syntax:

SELECT col1, col2

FROM table1 CROSS JOIN table2;

The OUTER JOIN clause

A OUTER JOIN is a join between two tables where you want to see information from tables even when no corresponding records exist in the common column. You can have RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN.

Syntax:

SELECT col1, col2

FROM table1 RIGHT/LEFT/FULL OUTER JOIN table2;

RIGHT OUTER JOIN

A RIGHT OUTER JOIN is a join between two tables where you want to see information from table on the right side even when no corresponding records exist in the common column.

Syntax:

SELECT col1, col2

FROM table1 RIGHT OUTER JOIN table2;

LEFT OUTER JOIN

A LEFT OUTER JOIN is a join between two tables where you want to see information from table on the left side even when no corresponding records exist in the common column.

Syntax:

SELECT col1, col2

FROM table1 LEFT OUTER JOIN table2;

FULL OUTER JOIN

A FULL OUTER JOIN is a join between two tables where you want to see information from both tables on the left and right sides even when no corresponding records exist in the common column.

Syntax:

SELECT col1, col2

FROM table1 FULL OUTER JOIN table2;

The (WITH name AS) statement

Oracle9i provides you with the WITH clause that lets you factor out the sub-query, give it a name, then reference that name multiple times within the original complex query.

The (inline view)

A sub-query that appears in the FROM clause is called an inline view. You must enclose the query text for the inline view in parentheses and also give a label for the inline view so that columns in it can be referenced later.

The MERGE statement

Oracle9i provides you with the MERGE statement so that you can identify a table into which you would like to update data in an existing row or add new data if the row does not already exist.

Materialized View

Unlike an ordinary view, which only contains an SQL statement, a materialized view contains the rows of data resulting from an SQL query against one or more base tables. The materialized view can be set up to automatically keep itself in synch with those base tables.

Materialized view log

Whenever a change is made to one of the underlying base tables; the database stores a log on each change.

Using DBMS_SNAPSHOT package

You can use the REFRESH procedure of the DBMS_SNAPSHOT package to refresh periodically a snapshot manually.

UNION

The UNION set operator combines the results of two queries into a single result with no record duplication.

INTERSECT

The INTERSECT set operator returns all the rows contained in both tables.

MINUS

The MINUS set operator returns all the rows in one table minus the rows contained in other table.

Tablespace

A tablespace is a logical database structure that is designed to store other logical database structures. Oracle sees a tablespace as a large area of space into which Oracle can place new objects. Space in tablespace is allocated in segments.

Partitioned table

Partitioned tables are just like regular tables except for an important small feature-they enable you to reference the individual segments that might support larger tables directly.

The ROLLUP function

It is simple extension to the SELECT statement’s GROUP BY clause. It creates subtotals at any level of aggregation needed, from the most detailed up to a grand total.

The CUBE function

It is simple extension to the SELECT statement’s GROUP BY clause. It calculates subtotals for all the combinations of a group of dimensions. It also calculates a grand total.

JAVA

JAVA is a language that is portable, due to the fact that it is run within your environment. This environment may be a web browser, a database, or an application server.

Nested Table

If an object is in an object, it is a nested object. A nested table is a table that is nested in another table.

The “THE” sub-query

The "THE" sub-query is used to identify the nested table to insert into. Note only one row may be inserted into the nested table at once using this method-as would be the case if you were inserting into any table.

The REF Cursor

REF cursors hold cursors in the same way that VARCHAR2 variables hold strings. This is an added feature that comes with PL/SQL v2.2. A REF cursor allows a cursor to be opened on the server and passed to the client as a unit rather than one row at a time. One can use a Ref cursor as a target of assignments and can be passed as parameters to the Program Units. Ref cursors are opened with an OPEN FOR statement and in all other ways, they are the same as regular cursors.

Table of Records

A table of records is a new feature added in PL/SQL v2.3. It is the equivalent of a database table in memory. If you structure the PL/SQL table of records with a primary key (an index) you can have array-like access to the rows.

Table of records differ from arrays in that they are not bound by a fixed lower or higher limit. Nor do they require consecutive index numbers as arrays do. Consult a PL/SQL reference manual (version 2.3 or higher) for further explanation. There are three steps involved in creating a table of records.

Oracle Server is an object-relational database management system that provides an open, comprehensive, and integrated approach to information management. It consists of an Oracle database and instance.

The Oracle Database contains all user data information. It has a physical and a logical structure.

The “Physical Structure” is determined by the operating system files; such as data files, control files, and parameter files.

The “Logical Structure” is determined by the Oracle Database, such as tablespaces, tables, and segments.

An instance is a combination of Oracle background processes and memory buffers.

The memory buffer is called System Global Area (SGA) and is shared by the database users. Every time a database is started the system global area is allocated and Oracle background processes are started.

Oracle Background Processes Server tasks between memory and disk.

A user establishes connection and then requests information using a client application from the Oracle Server.

The Listener process waits for connection requests from a client application, and routes each client to a server process.

Server processes are created on behalf of each user’s application to read a user request and return the results.

The TNSNAMES.ORA and SQLNET.ORA files establish client session connectivity to a server using the local naming option.