Data retrieval using the SQL SELECT statement

Capabilities of the SELECT statement

Relational database tables are built using relational theory. Relational theory uses relational algebra formal language.

According to relational theory SELECT statement uses three concepts:

  1. Projection – restriction of columns to be returned
  2. Selection – limiting the result set in accordance to some criteria
  3. Joining – interaction of tables with each other in a query.

The special keywords and reserved words case does not matter but they cannot be used as column or other database objects names.

The two mandatory clauses of a SELECT statement are:

  1. SELECT
  2. FROM

The * (asterisk) usage in a query is referred to as blind query, because the exact columns to be fetched are not specified.

When using SELECT you may use an alias – an alternative name for referencing a column or an expression.

Aliases are used for:

  • displaying output in a user-friendly manner
  • serving a shorthand when referencing columns or expressions to reduce typing.

Multi word aliases must be enclosed in double quotes or you’ll get ORA-00923 errors. When declaring an alias AS keyword may be used for better readability.

SQL expressions and operators

Columns and expressions are selectable.

Expressions are made up of an operation being performed on one or more column values (remember Excel formulas).

Operators by data type of columns:

  • Arithmetic operations – for numeric columns – (+ – * /)
  • Concatenation operator – for character or string columns
  • Addition or subtraction – for date and timestamp

Arithmetic operations on date columns return days number.

Basic SELECT statements notes

Letters case matters only when dealing with literals and aliases. SQL reserved words don’t have to be written in upper case.

When writing long expressions it is better to spread them on multiple lines – for debugging purposes, as debugger will point out the line number on failure.

Characters concatenation is done by || operator (in MySQL you would have to use CONCAT() function.

There is a special table called DUAL that is used for literal expressions to be calculated – these are returned as a single row from this table.

Ex:

For literals single quotes are used like:

Simplest way of escaping a quote in a literal is by writing 2 single quotes (1st is for escaping the 2nd)

For escaping an alternative quote operator may be used. It uses delimiters, these can be braces pairs like: [], {}, () and also any single or multi byte character. This operator is used as follows:

qdelimiter some text delimiter

SQL statements should be terminated with a semicolon. As an alternative, a new line can be added after a statement and a forward slash can be used to execute the statement (forward slash is a command – execute).

Null

Null refers to absence of data. Nullable is a term describing a columns may have null values.

  • Any arithmetic calculation with a NULL returns a NULL
  • Character concatenation operators ignore NULL

Any column that is a primary key is not nullable.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.