SQL Glossary of Terms

SQL, and database development in general, contains a lot of terms and acronyms. It can be hard to understand different articles, books, and even concepts that are explained to you if you're not sure what these terms and acronyms mean. Check out this SQL glossary to help understand them.

So, that's where this page comes in.

This SQL Glossary contains a massive list of terms and acronyms and words that are specific to SQL, relational databases, and data warehouses.

In each of the explanations below, I explain what the meaning of each term is. If I've written an article that explains it in more detail, I'll link to it.

You should be able to use this page as a reference for all terms and acronyms related to SQL and database development in general.

These terms are relevant and helpful for all kinds of database developers. If you like it or find it valuable, please share it with your coworkers and people you know! Thanks!

ACID

ACID stands for Atomicity, Consistency, Isolation, and Durability. They are the four attributes that ensure a database transaction is valid.

• Atomicity: The transaction must be treated as a single unit. All statements complete or none at all (there are no half-finished transactions).
• Consistency: The database must remain in a consistent state after the transaction.
• Isolation: The transaction will run without being impacted by other transactions. The results of this transaction are not visible to other transactions until this transaction is committed.
• Durability: The database can hold any updates that were made even in the case of system failures.

ADF

ADF, or Oracle ADF, stands for Application Development Framework. It's a web development framework that is based on JSF (Java Server Faces).

It provides a framework for J2EE developers to develop on.

There are more details from developers who have worked on it in this Stack Overflow question.

ADR

ADR stands for Automatic Diagnostic Repository. It is a hierarchical file-based repository for handling diagnostic information.

It was introduced in Oracle 11g to help DBAs with resolving problems on their databases.

Aggregation

Aggregation is the process of collapsing or grouping many records into a smaller number of records, based on unique values of a certain field.

This is often done in SQL using aggregate functions. For example, using the COUNT function with a GROUP BY on a field will aggregate your data by that field.

1SELECT department_id, COUNT(*)
2FROM employee
3GROUP BY department_id;

This query will show the department_id and a count of employees with each department_id value. This data has been aggregated.

Alias

An alias in SQL allows you to define a temporary name for a column or a table.

A column alias allows you to provide a temporary name for a column in your query. This is often done to clean up the name of a column or to rename a column inside a subquery for easier reference from outside the subquery.

For example:

1SELECT department_id, COUNT(*)
2FROM employee
3GROUP BY department_id;

This will show the COUNT in a column heading of COUNT(*). However, we can give this a column alias.

1SELECT department_id, COUNT(*) AS dept_count
2FROM employee
3GROUP BY department_id;

Now, you can see that the column shown here is labelled dept_count. It's a bit easier to read.

The AS keyword is optional here, but is recommended so that it's clear you meant for this to be a column alias and you didn't miss a column between fields.

A table alias is where you give a temporary (and often shorter) name to a table in your query. This is often used to help speed up writing queries, help with auto-complete in IDEs, and is often required in some instances (e.g. self joins)

For example:

1SELECT e.department_id, e.COUNT(*)
2FROM employee e
3GROUP BY e.department_id;

This example uses a table alias of "e" for the employee table. It can then be used in other clauses (e.g. SELECT, GROUP BY).

ALL Operator

The ALL operator is used with comparison operators (such as > or <=) to compare a value against every value in a list. The condition is TRUE if the value to compare passes the comparison to every value in the list.

For example:

1WHERE score > ALL (30, 45, 60);

This clause will only show records that have a score value greater than 30, 45, AND 60.

Ambiguous

The word ambiguous means "open to more than one interpretation; not having one obvious meaning".

In SQL and database development, it usually occurs when you're trying to SELECT a column that has the same name in two different tables, and the database doesn't know what column to display.

Anonymous Block

An anonymous block is a piece of PL/SQL code that has not been created as a function, package, or procedure.

You can run PL/SQL code by typing the code into an SQL window and running it, without creating a function, procedure, or package.

A simple anonymous block would be:

1BEGIN
2DBMS_OUTPUT.PUT_LINE('Hello world!');
3END;

The opposite of an anonymous block would be a named or stored block, which is in a procedure, function, or package.

ANSI

ANSI stands for American National Standards Institute. It describes many standards, including the standard for the SQL language.

When ANSI-standard SQL or ANSI SQL is mentioned, it refers to the SQL standard and is included in all databases that adopt the standard (MySQL, Oracle, SQL Server, etc).

In Oracle SQL, there are two methods of joining tables: the Oracle method (using + signs) and the ANSI standard method (using join keywords).

ANY Keyword

The ANY keyword is used with comparison operators (such as > or <=) to compare a value against every value in a list. The condition is TRUE if the value to compare passes the comparison to at least one value in the list.

For example:

1WHERE score > ANY (30, 45, 60);

This clause will only show records that have a score value greater than 30, 45, OR 60.

ASCII

ASCII stands for American Standard Code for Information Interchange. It is the numerical representation of character codes.

In Oracle SQL, the ASCII function allows you to return the ASCII numeric code for a specified character.

More information here: Oracle ASCII Function.

ASH

ASH stands for Active Session History. It's an Oracle feature that allows you to see current and historical information about active database sessions, based on the V$ dynamic performance views.

It's useful for performance tuning and tracking session activity.

ASM

ASM, or Oracle ASM, stands for Automatic Storage Management. It's a feature that was introduced in Oracle 10g to simplify the storage of data files, control files, and log files.

Audit

Audit is a command that can be used on the Oracle database to track SQL statements in user sessions.

There are several privileges you need, depending on what you want to audit.

Autonomous transaction

An autonomous transaction is a transaction generated by another transaction. The generated transaction executes without interfering with the transaction that created it.

The creating transaction is suspended until the autonomous transaction is completed using a COMMIT or ROLLBACK.

There are some benefits and things to watch out for when working with autonomous transactions, which I'll write a post about in the future. SQL Developer has an autocommit feature if you're using that IDE.

B-Tree (or B-Tree Index)

A b-tree is a type of index in SQL.

It is structured like a tree, where the data required is at the leaf and the optimizer starts at the trunk. It uses logic to determine which branches to use to get to the leaf or data that you need.

It is the default type of index in many databases.

BFILE

A BFILE is a data type in Oracle that allows you to store a link to a file on the server.

To use a BFILE, you create a table that uses the BFILE data type in one of the columns. You then create a directory object on the database, and then use the BFILENAME function on the directory object and filename to store that in the database.

Read more on the BFILE data type and how to use it here.

BI

BI stands for Business Intelligence. It refers to the area of study and work that uses technology and tools to view a company's data in more meaningful ways, which allows people at a company to make better decisions.

BI technologies include development practices to change the data into a more usable format for reporting (related to ETL or data warehousing) and tools to view this data (Cognos, Business Objects, etc).

Binary Integer

A binary integer (or BINARY_INTEGER) is a PL/SQL data type used for storing signed integers.

It can store values from between -2^31 and 2^31, which is values between -2,147,483,648 and 2,147,483,647 (about 2 billion).

See more on the Oracle data types, including BINARY_INTEGER, here.

Bind Variable

A bind variable is a variable in an SQL statement that acts as a placeholder. When the statement is run (either in an IDE or in an application that calls the statement), the value needs to be specified for this bind variable.

They are used for performance reasons and security reasons.

Queries that are the same except for different values can be written to use bind variables, and subsequent runs of the query will be faster (as they are treated the same).

They can improve security as they are often used to prevent SQL injection.

Here's an example of a query that uses a bind variable:

1SELECT first_name, last_name
2FROM employee
3WHERE employee_id = :EMP_ID;

When this statement is run, the value for :EMP_ID needs to be supplied.

Bitmap Index

A bitmap index is a type of index.

A two-dimensional array is created that consists of each row in the table and each value of the column being indexed.

There are pros and cons to using a bitmap index.

BLOB

BLOB stands for Binary Large Object and is a data type. It's used for storing digital information from other formats (e.g. images and audio) and can store data up to 4GB.

See this article on Oracle data types for more information on BLOB.

Block

A block, in a database, is the smallest unit of storage. A database has a default block size, and blocks in different tablespaces can have different sizes.

A block corresponds to a specific number of bytes of data store on a disk.

In Oracle, the hierarchy goes:

Tablespace > Segment > Extent > Block

Boolean

The concept of true or false. Can also be represented as 1 or 0.

In Oracle, it's a PL/SQL data type. It can hold the values of TRUE, FALSE, or NULL. In other databases, there are specific data types for booleans.

More information on boolean can be found in the SQL boolean data type article here.

Bounce

To shut down and restart a database or server.

Candidate Key

A candidate key is a key or column that can uniquely identify rows in a table.

Any of the candidate keys in a table can be used as the primary key for the table.

Candidate keys are usually determined when designing your database, which you can read more about here:

6 Easy And Actionable Steps On How To Design A Database

Cardinality

Cardinality has two different meanings, depending on the context: data modelling or database query development.

In data modelling, cardinality refers to the relationships between tables. It can be specified as one to one, one to many, or many to many.

In database query development, cardinality refers to how many unique values are in a column. The lower the cardinality, the smaller the number of unique values.

Cartesian Join

A cartesian join, or cartesian product, is a result of data from two tables that have not been joined to each other.

The result will include all combinations of table A matching to all combinations of table B, and will be a lot more data than you probably anticipated.

In almost every situation, a cartesian join is not desirable and is an issue in your query.

For example, if you select from the employee table (which has 100 records) and the department table (which has 6 records), you might expect 100 records with matching departments. However, if you have a cartesian join, you'll get 600 records (100 employees * 6 departments).

Cascade Constraints

The CASCADE CONSTRAINTS keyword is added to the DROP TABLE keyword. It allows you to drop all referential integrity constraints (foreign keys) that refer to the primary keys and unique keys of the table you're dropping.

Without this keyword, dropping a table with these constraints will cause an error and the table is not dropped.

See this article for more information: A Guide to the DROP TABLE Statement to Delete Tables in SQL.

CASE

A type of statement that allows for conditional logic (IF THEN ELSE) in SQL.

There are two ways to write a CASE statement, both of which are detailed in this article on the CASE statement.

In Oracle, CASE is preferred over DECODE because it's easier to read and more flexible.

CBO

CBO stands for Cost-Based Optimizer. It's a method of query optimisation that uses statistics to generate several execution plans, and uses the one with the lowest number of resources required.

CHAR

A CHAR is a data type in SQL for holding character or string data. It is a fixed length, which means the number you provide is the number of characters that are stored. Shorter values are padded with spaces to meet the length.

It's different to VARCHAR and VARCHAR2 in several ways, as detailed in this article.

You can also read more on the Oracle data types in this article.

Check Constraint

A check constraint is a type of constraint that can be placed on a table or column.

It allows you to specify a rule for a column where the values need to meet the condition.

For example, you can use a check constraint to specify a valid range of values for a number field or possible options for a character field.

Clause

A clause refers to a component of an SQL query that includes a keyword and all data that relates to it.

For example, the SELECT clause includes the keyword SELECT, the word DISTINCT if it is used, and any columns or functions that are to be displayed.

The FROM clause would include any tables that are being selected from, the JOIN keywords, and any subqueries used within the FROM clause.

Other clauses could include GROUP BY, WHERE, HAVING, INSERT, UPDATE, or DELETE.

CLI

CLI stands for Command Line Interface. It's a type of tool that uses text and a command line to allow users to run commands on a database.

Oracle's CLI tool is called SQL*Plus. They have also recently developed a tool called SQLcl, which is a more powerful version of SQL*Plus.

Read more information on SQL*Plus and SQLcl.

CLOB

CLOB stands for Character Large Object and is a data type in SQL databases.

In Oracle, It can hold up to 4GB of data, and is helpful for storing text values.

For more information on Oracle data types, refer to this article.

Cluster

A cluster means two or more computers that share resources and work together to form a larger unit.

Oracle RAC is used to access Oracle clustered systems.

COALESCE

COALESCE is a function that takes many parameters and returns the first parameter that is not NULL.

It's a common function in many SQL vendors.

More information on the COALESCE function can be found here.

Column

A column exists in a table and contains related information and has a value specified for each row. It can also be called a field.

Columns have a specific data type (more on data types here) and can be added, altered, and removed from tables.

Column Alias

A column alias is a temporary name given to a column in the SELECT clause of a query.

This is often done to clean up the name of a column or to rename a column inside a subquery for easier reference from outside the subquery.

For example:

1SELECT department_id, COUNT(*)
2FROM employee
3GROUP BY department_id;

This will show the COUNT in a column heading of COUNT(*). However, we can give this a column alias.

1SELECT department_id, COUNT(*) AS dept_count
2FROM employee
3GROUP BY department_id;

Now, you can see that the column shown here is labelled dept_count. It's a bit easier to read.

The AS keyword is optional here, but is recommended so that it's clear you meant for this to be a column alias and you didn't miss a column between fields.

Column-Level Constraint

A column-level constraint is a constraint that is specified at the column level. This means it can only access information about that column.

An example of a column-level constraint is:

1CREATE TABLE customer (
2  customer_id NUMBER PRIMARY KEY
3  customer_name VARCHAR2(100) NOT NULL
4);

Both the PRIMARY KEY and the NOT NULL constraints here are column-level because they are defined next to each column.

The other type of constraint is a table constraint, which is defined after all of the columns. A table constraint can refer to a single column - it's only a table constraint or column constraint because of where it is defined.

Commit

The act of saving a transaction to the database permanently. Any changes (Insert, Update, or Delete) are not saved permanently to the database until a commit is done.

This is done using the COMMIT statement.

The opposite of a COMMIT is ROLLBACK.

Complex View

A complex view is a type of view that cannot have DML (INSERT, UPDATE, or DELETE) performed on them. This is because complex views can contain join conditions, a GROUP BY clause, and an ORDER BY clause.

Composite Key

A composite key is a primary key that contains more than one column.

A primary key doesn't have to have just one column. If you define it as having multiple columns, you have a composite key.

For example, a car's model name might not be unique. However, a car's model name and year might be unique. So, you could create a primary key including both of these columns, which would make it a composite key.

CONCAT

CONCAT is short for "concatenate" and is an Oracle SQL function for joining two strings together.

It only allows for two strings to be concatenated. So, if you want to concatenate three or more, you'll need to use nested CONCAT functions, or use the double pipe || characters to join many strings.

Connection String

A connection string, or connect string, is a string used to identify and connect to a database.

In Oracle, they are specified in a TNSNAMES.ORA file.

It includes the protocol, host, port, SID, service name, and database connection type.

Constant

A constant is a variable that does not change value.

It is defined in procedural code, such as T-SQL or PL/SQL, as a constant variable, and any attempts to change it will result in an error.

Constants help to ensure that a variable is the same throughout the program, and if it needs to change, it only needs changing in one place.

Constraint

A constraint is a limitation or rule places on a table or columns within a table.

There are five types of constraints:

• Primary Key
• Foreign Key
• Unique Constraint
• Not Null Constraint
• Check Constraint

Conversion

The process of converting a value from one data type to another.

Many functions exist for this, which you can find on my Oracle SQL Functions page.

Some of the more common ones are TO_DATE, TO_CHAR, TO_NUMBER, and CAST.

Cross Join

A type of join in SQL that creates a cartesian product on purpose.

It joins every record in the first table against every record in the second table.

The keyword is used to be clear to other developers or readers that you intended for this to be a cross join, rather than omitting it and have people assume you have made a mistake by not specifying a join type.

CRUD

An acronym that refers to a type of application or set of operations that perform data storage:

• Create - create or insert data
• Read - read or select data
• Update - update data
• Delete - delete data

CTAS

CTAS stands for Create Table As Select. It's a type of query where you create a new table based on the columns from a SELECT statement and populated with the rows from the same SELECT statement.

It's helpful to speed up development and copy an existing table. It can also be used to copy just the structure of a table without the data, by specifying a WHERE clause that always equals FALSE (such as WHERE 1=0).

CTE

CTE stands for Common Table Expression. It's a feature in many databases that allow you to define a subquery and give it a name, which is then treated like a view inside your query. It helps to simplify your query and can sometimes improve performance. It's done using the WITH clause in SQL.

Read more on CTEs here.

CURRVAL

CURRVAL is short for Current Value, and is a property of a sequence object. It returns the current value of a sequence, without incrementing it.

Cursor

A cursor is a pointer to a result set in procedural languages such as PL/SQL.

A cursor can be explicitly used by defining a named cursor, or implicitly used by using the SQL% cursor.

Cursors have pros and cons in PL/SQL and can cause performance issues if not developed in the right way.

Data Dictionary

A data dictionary is a collection of metadata (which is data about other data) in the database. It contains information about the relationships to other data, formats, and what it means.

It's stored in Oracle's SYS schema. MySQL has an information_schema.

Queries can be run against tables and views in the data dictionary.

Data integrity

Data integrity refers to the accuracy and consistency of data in a database.

This is achieved in databases by using ACID transactions, referential integrity (primary keys and foreign keys), and check constraints that match to business rules.

Data mart

A data mart is an access layer of a data warehouse. It's a subset of a data warehouse that is built for a specific purpose, usually a particular business unit.

Data marts are read-only and usually used for reporting.

Data model

A data model defines how data is related to other data. It can be represented as entities and attributes in an Entity Relationship Diagram.

Data type

A data type denotes the type of data that is stored in a particular column. Different data types are used for different reasons, and using the correct data type makes the database perform better and your queries easier to write.

Data warehouse

A data warehouse is a database that has been designed in a way that maximises reading and reporting.

It is usually a large database and follows a different design method to transactional databases. A data warehouse could be designed as a star schema, and contains fact and dimension tables.

BI developers work on creating and maintaining data warehouses, and the ETL process is often used to create data warehouses.

Read more in this guide to data warehouses.

Database link

A database link is an object in one database that allows you to access objects (such as tables or views) in another database.

It's helpful to get data from another database when the query is run, without having to import it and export it.

Database link objects contain the server and database information that is required to connect to the other database.

DBA

DBA stands for Database Administrator. A DBA is a person responsible for maintaining the database.

Their role includes monitoring the database for issues, applying patches, performing backups, and much more.

For more information on how to become a DBA, read this article.

DBMS

Stands for Database Management System. It's the software used to store and manipulate data in a database.

Oracle, MySQL, SQL Server, and PostgreSQL are examples of a DBMS.

DCL

DCL stands for Data Control Language, and it is the category of SQL statements that control access to the database and its data.

It includes the GRANT and REVOKE statements.

DDL

DDL stands for Data Definition Language, and it is the category of SQL statements that allow users to define and alter database objects.

Some examples are CREATE TABLE, DROP, ALTER.

DDL statements also include a COMMIT operation when they are run.

Delete

A statement used to remove data from a table.

Denormalize

The process to transform a database design from a normalized format ideal for transactions to a format more suited to data warehouses.

It often results in larger tables but fewer tables.

Dimension Table

A table in a data warehouse that contains more information about an attribute in a fact table.

Some common dimensions are time, product, employee, customer.

Directory

A database object on the Oracle database that refers to a folder or directory on the server. SQL statements need to refer to a directory object if they wish to access a file.

Distinct

A keyword that means a SELECT statement will eliminate duplicate records from the result set.

Note that the same value in a column may be repeated, if the values in other columns are different to other rows.

DML

DML stands for Data Manipulation Language, and is the category of SQL statements used to add, modify, and delete data from tables.

Statements include INSERT, UPDATE, DELETE, and SELECT.

Drop

A command used to remove an object from the database, such as a table or a view.

If you delete data, it's called deleting. However, if you want to delete an object, it's called dropping.

Dual

A table in the Oracle database that can be used in SELECT queries when no table is needed.

In Oracle SQL, all SELECT statements need a FROM clause (unlike other databases). If you want to select a value that does not need a table (e.g. the SYSDATE function), you can SELECT from DUAL.

Example:

1SELECT SYSDATE
2FROM dual;

The DUAL table contains a single column and single value.

Read more on the DUAL table here.

Dynamic SQL

Dynamic SQL is an SQL statement that is constructed and executed during run time.

The opposite of this is a static SQL statement, which is written and known before the program is run.

Dynamic SQL can be enclosed in a string and executed using the EXECUTE IMMEDIATE command.

EBS

EBS stands for Oracle E-Business Suite, and it is a suite of applications with different modules, such as:

• Oracle Financials
• Oracle Service Management
• Oracle CRM
• Oracle Supply Chain

It has since been replaced with Oracle Fusion.

Embedded SQL

Embedded SQL, or static SQL, is SQL statements that are coded into the program and cannot be changed by the application.

They may include bind variables that allow for some variation, but the statements themselves are set.

The opposite of an embedded or static SQL is dynamic SQL.

Equi-join

A type of join comparison which is done using an equals sign. It allows for one field to be equal to another field. It's probably the most common type of join comparison.

ERD

ERD stands for Entity Relationship Diagram. It's a diagram that shows the different entities, their attributes, and how they relate to each other in a system.

It can be done as a high-level ERD (which just shows entities), or a detailed ERD which directly maps to tables and fields.

There are many programs that let you create ERDs, and I've made a list of them here (with prices and reviews).

Escape

The process of marking some special characters as being part of the string you want to capture. Often done with single quote characters.

ETL

ETL stands for Extract, Transform and Load. It is the process to populate a data warehouse.

Extract is where data is read from one or many source systems and populated into a staging area.

Transform is where this data is cleaned up and changed into the new required structure.

Load is where data is loaded into the data warehouse to be used.

Exception

An object that is created, either by the database or by a developer, when something goes wrong in procedural code.

They can be created, or raised, in functions, procedures, packages, or anonymous blocks.

Explain Plan

A command in SQL for showing the estimated execution cost and method of a query. It's helpful to see how the query runs and identify any opportunities for improvement.

Expression

A combination of one or more values, operators, and functions that result in a value.

Extent

A set of side-by-side blocks allocated to a segment within a tablespace.

The hierarchy goes:

Tablespace > Segment > Extent > Block

External Table

AN external table is an object in Oracle that allows you to query data that is stored in flat files outside the database.

They are commonly used with CSV files and need to be specified in a certain way.

Fact Table

A type of data warehouse table that stores the measurements or values that you want to track in your data warehouse. It's the centre of the design and relates to dimension tables.

Fetch

A PL/SQL statement that is used to load data from a cursor.

Field

Another name for a column in a database. It stores a specific piece of data for a record or row in a table.

First Normal Form (1NF)

The first stage of normalizing a database. First normal form means that "each set of columns must uniquely identify a row".

The tables and their columns in a database are reorganised to ensure they satisfy this rule. Then, the database is said to be in first normal form.

Related: Database First Normal Form (1NF) Explained

Flashback

Flashback is an Oracle database feature to move the database to a previous state so it can be recovered.

It can also be used to recover tables after they have been dropped.

More information on dropping tables and flashback can be found here.

Flat file

A file that stores data in a series of single rows. A CSV file can also be called a flat file, as there are no concepts of tables and relationships.

Foreign Key

A field in one table that relates to the primary key in another table. Used for referential integrity and relating tables to each other.

Fourth Normal Form (4NF)

Fourth normal form is the next step of normalization after third normal form. It means the database must satisfy the rules of third normal form, and also ensure there are "no non-trivial multivalued dependencies other than a candidate key".

You can read more on fourth normal form here.

Most of the time, third normal form is acceptable for transactional databases. There may be a case to go to fourth normal form, depending on your requirements.

FROM Clause

The FROM clause includes the keyword FROM, any tables that are required for the columns being selected, and the JOIN keywords.

Full outer join

A type of outer join where values in the left table are shown along with NULL values where there are no matching values in the right table, and values in the right table are shown along with NULL values where there is no matching value in the left table.

It's a combination of a LEFT JOIN and a RIGHT JOIN.

Function

A piece of code that is stored in the database and can be used by your SQL queries.

They could be built-in functions (such as SUM) or user-defined functions that you create yourself.

Functions need to return a value and can be used in SQL queries.

Procedures are similar, but do not need to return a value and cannot be used in SQL queries.

Functional dependency

A functional dependency exists when the value of one field is completely determined by the value of another.

For example a table such as:

car (car_id, car_name, price)

The attribute of car_name is functionally dependent on the car_id. This is because, if we know the car_id, we can find out the name.

GRANT

A keyword (part of the DCL category) that is used to give privileges for certain tasks to a specified user or role.

GROUP BY clause

A clause in an SQL statement that allows you to specify how the data should be grouped in the SELECT query's output.

It is required when you are using aggregate functions.

There's a lot you can do in the GROUP BY clause to change how data is grouped, and I've written the complete guide on the GROUP BY statement here.

Hash join

A hash join is where two tables are joined based on a hashing algorithm. You may see this in your Explain Plan output.

HAVING Clause

This clause allows you to limit the rows returned by a SELECT query after the GROUP BY clause is applied. It is often used with aggregate functions.

Related: The Difference Between the WHERE and HAVING Clause

Heap-organized table

A heap-organized table is a table in Oracle where the rows are not stored in any particular order. When a specific order is required, it is retrieved using a SELECT query with an ORDER BY clause.

The other types of tables are index-organized tables and external tables.

Hint

A hint is a special keyword that can be used in a query to alter the execution plan.

They are used mainly when the developer or DBA knows things about the query that the database doesn't.

Histogram

A graph that shows different sizes depending on the frequency of a value in a table.

IDE

IDE stands for Integrated Development Environment, and it is software that allows developers to write code and perform many other tasks for application development.

IDEs exist for many languages, including SQL. Some of the more common IDEs are SQL Developer, Visual Studio, and SQL Server Management Studio.

IF THEN ELSE

A concept of performing different logic depending on a condition.

The IF part checks a condition, THEN an operation is done if the condition is true, and ELSE something else is done if the condition is false.

This logic is implemented in many ways in Oracle SQL in different functions, but if you're designing your own conditional logic in SQL, use the CASE statement

Index

An object on the database that contains references to each value in a field that lets you quickly locate records in a table.

They are helpful to improve database performance, but they are not the only method.

Indexes can be created by developers or DBAs, depending on their privilege. Indexes can also be created by the database automatically. In SQL Server, there are two types of indexes: clustered and non-clustered.

Index-organized table

An index-organized table (or IOT) is a type of table that stores the primary key and non-key data stored in a B-tree structure.

There are several advantages to storing tables in this way.

Inline view

A SELECT statement which is in the FROM clause of another SELECT statement. It is treated like a view but is not stored as a database object.

Inner Join

A type of join where the results of the joined tables are shown where the fields match in both columns. If there is a record in one table but not the other, then data for both records is not shown.

INSERT

An SQL command for adding data to a table.

Related: SQL INSERT: The Complete Guide

INTERSECT

A set operator in SQL that allows you to view the results of one query that exist in the results of another query.

Interval

A data type in Oracle that stores a duration of time. It is different to a date or timestamp, which store a period in time.

The two interval data types are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.

More information on these data types can be found here.

Isolation level

An isolation level is a property that can be set on a session that determines what kind of data can be seen.

There are five isolation levels in Oracle:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE
• READ ONLY

JDBC

JDBC stands for Java Database Connectivity, and is a standard API that allows Java programs to access databases.

JDK

JDK stands for Java Development Kit, and it is a software development environment that's used for developing Java applications.

It is required for using SQL Developer, but is bundled with recent versions.

Join

The concept of linking two tables together on related field, so a query can retrieve data from two tables. It's one of the main features of relational databases.

Keyword

The range of reserved words that are used by the database to process SQL queries. They include commands (such as SELECT and INSERT), set operators (such as UNION) and other words such as JOIN and FROM.

LIKE

A keyword that allows you to compare on partial strings. It is often used in the WHERE clause and uses either % wildcards or _ wildcards for string comparison.

Listener

An Oracle process for listening for incoming database connections on the server.

If the listener process is not running, then you cannot connect to the database.

Literal

A number, character, or string that is enclosed in single quotes in a query.

LOB

LOB stands for Large Object and is implemented in Oracle as CLOB, NCLOB, or BLOB data types.

It replaces the LONG and LONG RAW data types from older Oracle versions.

Lock

A feature of a database that allows concurrency control and data consistency.

It ensures that only one user can change a particular record at one time, and that a user cannot delete a record while another user is updating it.

Logical operator

A logical operator is a keyword that allows two expressions to be compared and an overall value of TRUE or FALSE to be returned.

The logical operators in Oracle are AND, OR, and NOT.

Many to Many Relationship

A type of relationship between two tables that means many records in one table can relate to many records in another table.

For example, a student taking a class. Many students can be in a class, and many classes can be taken by a student.

This is best represented in a database by creating a joining or junction table, which captures the different combinations of each of these tables.

Materialized View

A materialized view is a type of view in a database where the data is stored along with the query that created it.

A view is created by saving an SQL query to a database. Whenever the view object is queried, the SQL for that view is run, which causes the data to be loaded each time.

With materialized views, the data is loaded once and stored, like a table. Any further queries on the view get the data from this object, without rerunning the query.

They are used for performance reasons and are helpful for data that does not change a lot.

Related: SQL Views: The Complete Guide

Merge

An SQL command that allows you to check if data exists, insert data into a table if the data does not exist, and update the data if it does exist. This is all done in a single command and is an efficient command used in ETL processes.

It's often called an "upsert" because it performs an update and an insert.

Read more: SQL Merge: The Complete Guide

Metadata

Data about data. Metadata is what's stored in the data dictionary, as it is data about the tables and objects in the database.

MINUS

A set operator that allows you to see the results of one query that don't exist in another query.

Multi-row subquery

A subquery that returns more than one row. This is often handled with keywords such as IN, NOT IN, > ALL, < ANY.

The opposite is a single-row subquery.

Multivalued attribute

An attribute that has a value of a set of values. Oracle includes the Nested Table and VARRAY data types for this.

Mutating Table

A mutating table is a table that is being changed.

Errors can happen if a table is being modified, which calls a trigger to refer to that same table. You can find out more about this in my post on triggers: Oracle Triggers - The Complete Guide.

Natural Join

A join type where two tables are joined without specifying the columns. The columns that have the same names in both tables are used as the join fields.

While it is simpler to write, it's recommended not to use this join type, because fields with the same name may not be equal in two tables.

It's better to use an INNER JOIN.

Natural key

A natural key is a primary key made up of columns already in the table.

The alternative is a surrogate key, which is where a new column is created for the primary key.

NCHAR

An Oracle datatype, similar to CHAR, but can hold Unicode characters. It has a fixed length, just like the CHAR data type.

For more information on data types, read this guide to Oracle data types

Nested Table

An Oracle data type that can support entire tables of values in a single column.

NEXTVAL

A property of a sequence that increments the current sequence value and returns it. Often used for inserting primary key values into a table.

NLS

NLS stands for National Language Support), and it is used to define the date, number, currency, and language settings for different nations.

Non-equi-join

A type of join condition where the columns being compared are not equal.

This can be done using the not equals operator != or <>, or even variations of greater or less than.

For example:

1FROM table1 INNER JOIN table2 ON table1.id <> table2.otherid

Normalization

The process of modifying a database design to align with a standard set of rules and make it ideal for transactions.

Normalization usually aims to transform a database into third normal form.

NULLIF

A function that returns NULL if the two expressions are equal, but returns the first expression if they are not.

More information and examples can be found in the SQL NULLIF Function article.

Number

An SQL datatype used for storing whole numbers and decimal numbers.

NVARCHAR2

An Oracle data type that stores a variable-length string, like VARCHAR2, but can store Unicode characters.

OBIEE

OBIEE stands for Oracle Business Intelligence Enterprise Edition. It's a set of business intelligence and analytics platforms that perform business intelligence functions on a data warehouse.

OCA

OCA stands for Oracle Certified Associate. It is the entry-level Oracle DBA certification. Once you have this certification, you have the skills for a junior level DBA.

OCM

OCM stands for Oracle Certified Master, and it is the third level of the DBA certification track and is targeted to senior DBAs and consultants.

OCP

OCP stands for Oracle Certified Professional, and is the second certification on the Oracle DBA track. It is targeted to mid to senior level DBAs.

ODI

ODI stands for Oracle Data Integrator, and is an ETL tool used to move data between different systems.

OLAP

OLAP stands for Online Analytical Processing, and is a type of system that allows users to analyse and manipulate the display of data to make decisions.

OLAP databases are also referred to as data warehouses.

OLTP

OLTP stands for Online Transactional Processing, and is a type of system that allows for large numbers of transactions (insert, update, delete, and select) to be performed on a system.

They are usually normalized to third normal form.

On Delete Cascade

A setting when creating a foreign key that says if a record in the parent table is deleted, then the corresponding records in the child table are also deleted.

On Delete Set Null

A setting when creating a foreign key that says if a record in the parent table is deleted, then the corresponding records in the child table have their key value set to NULL.

One to Many Relationship

A type of relationship where a record in one table can relate to many records in another table.

An example is a sales and customer table. A customer can have many sales, but a sale can only be for one customer.

One to One Relationship

A type of relationship where a record in one table can have only one record in another table, in both directions.

For example, a country can only have one capital city, and a capital city only belongs to one country.

Operator

A symbol (>, =, <=, etc) or keyword (NOT, IN, ALL, UNION) that is placed between expressions to compare them.

Oracle Fusion

A set of applications that help with enterprise resource planning. It is the replacement for EBS (Oracle E-Business Suite), and includes financials, supply chain management, HR, and many other areas.

ORACLE_HOME

The directory or environment variable that points to where Oracle is installed.

OTN

OTN stands for Oracle Technology Network and is Oracle's community website. It includes forums and product documentation.

Outer Join

A type of join where records from one table are shown, and if there are records in the second table, they are shown, but if no records match, then show a NULL value.

There are three types of outer joins: LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN.

In Oracle, the OUTER keyword is optional. So, a LEFT OUTER JOIN is the same as a LEFT JOIN.

Package

A collection of related PL/SQL code (functions and procedures) into a single unit. Packages are used to improve the security and design of your code.

PGA

PGA stands for Process Global Area, and is an area in RAM that stores data for a single process.

PLS_INTEGER

A data type, only available in PL/SQL, that is used for storing signed integers.

They are generally faster than using NUMBER variables in PL/SQL.

As of Oracle 10g, PLS_INTEGER and BINARY_INTEGER are identical.

You can read more about these data types and others in my guide on Oracle data types.

Pragma

A pragma is a compiler directive, or hint, that is used to provide an instruction to the compiler.

There are several pragma directives available in Oracle:

• PRAGMA AUTONOMOUS_TRANSACTION
• PRAGMA SERIALLY_REUSABLE
• PRAGMA RESTRICT_REFERENCES
• PRAGMA EXCEPTION_INIT
• PRAGMA INLINE

It's commonly used to bind a user-defined exception to a particular error number (PRAGMA EXCEPTION_INIT)

Predicate

The expression used in a WHERE clause to specify which rows should be returned.

For example, let's look at this statement:

1SELECT *
2FROM customer
3WHERE status = 'Active';

The predicate in this query is "status = 'Active' ".

Primary Key

A field in a table that is used to uniquely identify a row.

A table can only have one primary key, but it can be made up of different columns.

If a value is inserted or updated and the primary key already exists, an ORA-00001 (or equivalent) error will be shown.

More information on primary keys can be found here.

Privilege

A permission that can be granted to or revoked from a specific user or role, which allows the user or role to perform certain actions.

Procedure

A stored piece of procedural code that allows parameters to be provided and returned, and perform complex functionality.

Pseudocolumn

A column that is automatically added to every database table to display data about the record.

These columns can be selected, but can't be inserted, updated or deleted.

Some examples of pseudocolumns are ROWNUM and ROWID.

Query

A collection of SQL keywords which allows for a specific action to be taken. A query is run on its own as part of a transaction.

A query starts with an SQL keyword, such as SELECT, INSERT, UPDATE, DELETE, GRANT, and so on.

The term "query" is often used interchangeably with the word "statement".

RAC

RAC stands for Oracle Real Application Clusters. It is a database solution that allows more than one system to share a single Oracle database.

Clusters have nodes, and they can access the same files at the same time. Clustering has its advantages and requirements.

RAW

An old Oracle data type used to store binary data, such as images or audio. It has been replaced by the BLOB data type.

RBO

Stands for Rules-Based Optimizer, and is a query optimizer that uses different rules to define the best query execution plan.

However, it is an old method of optimizing queries. The Cost-Based Optimizer should be used instead.

RDBMS

RDBMS stands for Relational Database Management System and is a type of DBMS where the tables are organised according to relationships between the data.

Popular RDBMS systems include Oracle, SQL Server, MySQL, DB2, and PostgreSQL.

REF CURSOR

A data type that holds a cursor value. It is used to pass the single cursor object to a different part of the code, instead of the individual values within it.

Relation

A mathematical term for a table.

Relationship

The link or association between two entities in an ERD or tables in a database.

There are several types of relationships:

• One to one
• One to many
• Many to many

REVOKE

A command used to remove a privilege from a user or role. The opposite of a GRANT command.

RMAN

RMAN stands for Recovery Manager and is a utility used to backup, restore, clone, and recover Oracle databases.

Role

A group of privileges that can be granted or revoked from users.

The benefit of using roles is that it allows for easier privilege management

ROLLBACK

A command used to undo any changes made to the data in the tables during your current transaction. The opposite of the COMMIT keyword.

Rollback segment

An object that contains images of before data was written to the database. They are used to undo changes when a transaction is rolled back.

In Oracle 10g and newer, undo segments should be used instead.

ROWID

A value for a row that is unique within the database. It contains a 10-byte character value that represents the location on the physical disk that the row lives.

It can be seen in a SELECT query as it is a pseudocolumn on every table.

ROWID values can change in some instances, though, so it's not a good idea to store them in other tables as a reference

ROWNUM

A pseudocolumn that shows the row's position in a result set.

The ROWNUM value is determined after the rows are selected but before the ORDER BY is run.

For more information on ROWNUM, refer to this article: What's The Difference Between Oracle ROWNUM vs Oracle ROW_NUMBER?

Savepoint

A savepoint is a marker in a transaction that can be set as a point to roll back to at a later time.

Schema

A schema is the set of objects (tables, views, sequences) that belong to a user account.

SCN

SCN stands for System Change Number and represents a unique number for every time a commit is made.

Second Normal Form (2NF)

The second stage of normalization. A database is in second normal form if it meets the rules of first normal form, and "all of the non-key attributes are dependent on the primary key".

For more information on second normal form, read this article: Second Normal Form (2NF) Explained with Examples.

Segment

A database object that has space allocated to it.

It consists of one or more extents, and lives in a tablespace.

The hierarchy goes:

Tablespace > Segment > Extent > Block

SELECT

An SQL keyword for viewing data in the database.

Self Join

A type of join where you join a table to itself.

This is often done when you have a hierarchy set up in your table, such as for parents and children, or employees and managers.

Two instances of the same table can be selected from in the same query and joined to each other.

Sequence

A database object that is used to generate unique numbers. It's great for primary key values and IDs, and ensures different transactions get different values. Read more about sequences here.

Session

The set of events that happen from when a user connects to a database to when the user disconnects from the database.

SGA

SGA stands for System Global Area. It forms part of the RAM and contains all of the information needed to operate the instance.

The SGA includes:

• buffer cache
• dictionary cache
• redo log buffer
• shared pool
• Java pool
• large pool
• stream pool
• log buffer

SID

SID stands for System Identifier, and it uniquely identifies an Oracle database instance on a system.

It can also identify a unique session in the session database views.

Single-row subquery

A subquery that always returns a single row, either using a unique constraint in the WHERE clause or an aggregate function.

The result of the subquery can be compared using an operator that expects a single value, such as =.

SQL

SQL stands for Structured Query Language and is the language used to communicate with a database.

There is an ANSI-standard collection of SQL and each database vendor has expanded on it for their own purposes.

SQLCODE

An Oracle function that returns the number code for the most recent exception. The SQLCODE function is often used along with the SQLERRM function.

A complete list of the SQLCODE values can be found here.

Star schema

A database design for data warehouses and data marts that contains a single fact table and multiple dimension tables linked to it.

Subquery

A SELECT query that is run inside another query. Often used to get the results of one query and use it as an input into another query.

Surrogate key

A new field created for the purpose of a primary key for a table, instead of using an existing field.

Synonym

A database object that is a pointer to another object, such as a table or view.

It allows for improved security (users don't know what the underlying logic is), a layer of abstraction (change the tables without affecting end users) and several other benefits. Read more about synonyms here.

SYS

The username and schema that contains the data dictionary in Oracle.

Table-level constraint

A constraint that is defined at the table level, after all of the columns have been defined.

Tablespace

A logical storage unit that stores all of the data for the tables.

The hierarchy goes:

Tablespace > Segment > Extent > Block

Third normal form (3NF)

The third and ideal stage of normalization.

A database is in third normal form if it meets the criteria of second normal form, and "every non-prime attribute of a table is not transitively dependent on the primary key of the table."

For more information on third normal form, read this article: Third Normal Form (3NF) Explained with Examples.

TIMESTAMP

A data type in Oracle that stores the date, time, and fractional time in seconds.

Read my guide to Oracle data types for more information.

TIMESTAMP WITH TIME ZONE

A data type in Oracle that stores the date, time, fractional time in seconds, and a timezone value.

Read my guide to Oracle data types for more information.

TKProf

TKProf is an Oracle utility used to format the output of SQL Trace into something that's more readable.

TNS

TNS stands for Transparent Network Substrate, and is a technology for connecting to Oracle databases.

There are many error messages that relate to TNS errors.

There's also a TNSNAMES.ORA file that contains connection data.

TNSNAMES.ora

A file that contains network service names and how they map to connection descriptors or connection strings.

Toad

An IDE for SQL developers, allowing developers to connect to an Oracle database, write and run queries, and much more.

Transaction

A single unit of work that must be saved or rolled back in its entirety. Transactions start when a user connects to a database and end when a user commits or rolls back.

Transitive dependency

Transitive dependency means that one attribute determines a second attribute indirectly, as it involves a third attribute in the middle.

For example, let's say we have a table with three columns: A, B, and C.

A can be used to determine B.

B can be used to determine C.

So, A can be used to determine C. This is called a transitive dependency. It should be avoided if a database is to be changed into third normal form.

Trigger

A database object that is attached to a table that is called when a row is inserted, updated, or deleted. Triggers contain procedural code and allow you to check that changes are correct or add in any data that needs to be there but is not specified by the SQL statement.

Related: Oracle Triggers - The Complete Guide.

TRUNCATE

An SQL statement that deletes all data from a table. Generally faster than a DELETE statement as there is no log and the transaction is committed.

Related: SQL TRUNCATE TABLE Statement: The Complete Guide.

Tuple

Another name for a row in a table. A tuple exists in a relation.

UDT

UDT stands for User Defined Type and is a type that is created by a user in a PL/SQL program.

UID

UID stands for User Identification and is a pseudocolumn that holds a numeric value for the currently logged-in user.

UML

UML stands for Unified Modelling Language, and it is a general-purpose modelling language for visualizing the design of a system.

UNION

The UNION keyword is a set operator that allows for the results of one query to be combined with the results of another query, and duplicates removed.

Related: SQL Set Operators: The Complete Guide to UNION, INTERSECT & MINUS

UNION ALL

The UNION ALL keyword is a set operator that allows for the results of one query to be combined with the results of another query, and duplicates kept.

Related: SQL Set Operators: The Complete Guide to UNION, INTERSECT & MINUS

Unique key/Unique constraint

A unique key, or unique constraint, is a constraint that ensures only unique values are entered into the column specified.

It's similar to a primary key, but multiple unique constraints can exist on a table.

UPDATE

An SQL command used for making changes to data that exists in a table already.

Related: A Guide to the SQL UPDATE Statement.

User

It can refer to a user account on the database that someone logs in as.

Or, it can refer to a pseudocolumn on tables that returns the name of the currently logged-in user.

VARCHAR

A data type in the SQL standard that allows for variable-length character data to be stored.

Similar to the CHAR and VARCHAR2 data types, and their differences are mentioned in this article.

VARCHAR2

A variable length data type for holding character data.

Similar to the CHAR and VARCHAR data types, and their differences are mentioned in this article.

VARRAY

A data type used to support a column that contains multiple values. Kind of like an array.

View

A SELECT query stored on the database, but has a name and is treated like a table. Other queries can select from this view, and the underlying query is run each time.

Helps security, database design, and to improve the simplicity of queries.

Wildcard

A character that can be substituted for one or more characters.

In SQL the % character is a wildcard that represents any number of characters, and the _ character represents a single character.

XE

XE stands for Express Edition, and it refers to the freely available Oracle database.

It is also the SID for connecting to the Express Edition.

You can download and install Oracle Express Edition by following the instructions in this article: How To Install Oracle Express

Lastly, if you enjoy the information and career advice I've been providing, sign up to my newsletter below to stay up-to-date on my articles. You'll also receive a fantastic bonus. Thanks!