MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

1 – SELECT Clause

without comments

SELECT Clause

Learning Outcomes

  • Learn how to work with comma delimited literal values.
  • Learn how to work with comma delimited literal values with column aliases.
  • Learn how to work with comma delimited column values.
  • Learn how to concatenate literals and columns.
  • Learn how to use math operators to add, subtract, multiply and divide literals and column values.

Lesson Materials

As explained in the SELECT statement page, the minimum SELECT statement has a SELECT clause or SELECT-list and FROM clause. The SELECT-list is a comma delimited set of literals, column names, calculations of literals and column values. The FROM clause may reference one table or two or more tables.

Some modern databases support the ability to query string literals without referring to a table or they refer to a pseudo table. A pseudo table is essentially a placeholder value for a table in the FROM clause that lets you have one to many column values in a single row.

The pseudo table an Oracle database (as well as MySQL and SQL Server) is the dual table. Some of the examples use the dual table and others use a cereal table. You can download a script to create the cereal table from this URL on GitHub.com’s server.

SELECT-list

SQL (Structured Query Language) supports two types of literal values. The available literal types are numbers and strings. Strings are delimited with single quotes in most databases, like Oracle.

Date literals are strings that conform to the standard date format. Oracle supports two default string formats for dates. They are:

  • One uses a two-digit number for day, a hyphen, a three digit string abbreviation for month, a hyphen, and a two-digit number for a relative year:

    DD-MON-RR
  • One uses a two-digit number for day, a hyphen, a three digit string abbreviation for month, a hyphen, and a four-digit number for an absolute year:

    DD-MON-YYYY

Literal Values

This section shows you how to return literal values, concatenate (or glue together) literal strings, and perform basic mathematics by assigning aliases to these literal values and results from concatenation and math operations.

Column Values

This section shows you how to return column values, concatenate (or glue together) string column values, and perform basic mathematics. It also shows you how to assign conforming aliases and non-conforming aliases to SELECT-list values. A conforming alias starts with a letter and a non-conforming alias starts with a number or underscore.

Written by maclochlainn

December 12th, 2017 at 11:15 pm