Database Tutorial

Querying data from a single table is fairly straightforward because we choose the table, then the columns, and finally the filtering rules to select the data. We list the desired columns in the SELECT clause, the table where the columns are found in the FROM clause, and then we put the filtering conditions in the WHERE clause.

When the data doesn’t reside in one table, we use joins to create super tables. Super tables are really only result sets. A result set exists whether its source is a single table or multiple tables.

Result sets are two-dimensional structures that contain a list of structures, where the structures are defined by a one or more column definitions. A list is like an array except it doesn’t have an upward element limit. Lists typically keep track of elements by numerically indexing them. A single column list is typically identified by the numeric index alone but a database may resolve it through either the row identifier or a combination of the row identifier and column name. A multiple column list identifies the row by the index, and the column by the element name. Element names are defined when you create structures, and in databases their definition becomes part of the data catalog (or metadata). Some databases only support tables and views (a preserved join between tables or other views) in the database catalog. More advanced databases, like Oracle, support object types and tables of object types in the database catalog.

The idea of columns is simple by contrast to the output from SQL functions. SQL functions can process column results into new values on a row-by-row basis. The processing can convert a column’s data type or value. Some functions let you cast from one data type to another without changing a value.

You also have the ability to concatenate columns into new columns with new names. You assign the new names by using column aliases. Lastly, you can preserve a query that does any of this in the data dictionary by creating a VIEW.

This lesson focuses on the skills to: (a) query rows of data from single or multiple tables, (b) apply SQL functions to transform elements of a row in the result set, (c) apply SQL functions to aggregate rows of data based on some unique subset of column values, and (d) glue elements of a row together into complex strings (gluing is formally known as concatenation).

Single Table Queries

Learn how to write a query against a single table with column and table aliases.

Multiple Table Queries

Learn how to join tables by using surrogate keys, example demonstrate ANSI SQL:89 with Oracle’s proprietary extentions and ANSI SQL:92.

Inline Views

Learn how to create and use an inline view.

SQL Functions

Learn how to use SQL functions to change values or data types.

SQL Concatenation

Learn how to concatenate, or glue together, strings into mega-strings, and how the process is supported differently by SQL dialects.

SQL Date Math

Learn how to use SQL dialect to add, subtract, and manage dates and timestamps.