SELECT Clause
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 an asterisk (*), literal values, and column values. The asterisk (*) is a pointer or indirect reference to rows returned by the query. There are two types of literal values. They 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.
Instruction Details →
The query shows you that you must use single quotes to delimit a string literal value. It also shows you how to use a column alias. The AS
keyword is option in most databases but using it makes reading your aliases simpler. Aliases in a SQL context must begin with a letter or underscore and may contain letters, numbers, and underscores.
The query also uses a FROM
clause with the dual
pseudo table, which effectively lets you select a string not otherwise found in a table within the database.
SELECT 'Hello World!' AS string FROM dual; |
The query returns the following string literal value with a column header set by the column alias of the query.
STRING ------------ Hello World! |
You have the ability to have multiple columns in a SELECT
-list. The literal values or columns are comma delimited.
The next sample SELECT
-list queries one string literal and another pseudo column that concatenates a string literal and date. Like the prior example, the FROM
clause uses the dual
pseudo table.
SELECT 'Hello World!' AS string , 'Leap day is: ' || '29-FEB-2016' AS "Leap Year Day" FROM dual; |
The comma between the two literal values is like separating two columns from a table. They are effectively pseudo columns. The double pipe (||) command lets you concatenate (or glue two strings together as one). The “Leap Year Day” column alias is enclosed in single quotes, which lets you embed case sensitive strings and whitespaces.
The query returns the following two columns values:
STRING Leap Year Day ------------ ------------------------ Hello World! Leap day is: 29-FEB-2016 |
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.
Instruction Details →
This section uses a cereal
table. The cereal
table has the following description:
Name Null? Type ----------------------------------------- -------- ---------------------------- CEREAL_ID NOT NULL NUMBER MANUFACTURER NOT NULL VARCHAR2(24) BRAND NOT NULL VARCHAR2(30) MASCOT VARCHAR2(30) OUNCES NOT NULL NUMBER(4,2) SUGGESTED_RETAIL NOT NULL NUMBER(5,2) |
The cereal
table uses a cereal_id
column as its primary key column. The manufacturer
, brand
, ounces
, and suggested_retail
columns are mandatory columns because they are not null constrained. You must provide a value for any mandatory columns when you try to add a new row to a table, whereas you can provide a null value to any optional columns. Optional columns are unconstrained.
You can get the cereal.sql
file at this URL, and then you can run the script to create the cereal table and add seventy rows to it. If you put the cereal.sql
file in the /home/student/Data/cit225/oracle/demo
directory, you should launch SQL*Plus from that same directory assuming you have a student
user with a student
password, like:
sqlplus student/student |
Inside SQL*Plus, you’ll see the SQL>
prompt. You can run the file with the following syntax:
@cereal.sql |
At the end of the cereal.sql
script, it queries the number of rows found in the cereal
table. The cereal.sql
script includes SQL*Plus formatting commands. The COLUMN
command is a SQL*Plus formatting command. You can use COLUMN
keyword or its first three letters COL
as an abbreviation.
The following COL
command formats the rows
column as a four digit number. The SQL*Plus HEADING
clause and double quotes lets you reuse a title case Rows
string as the title case displayed column name.
COLUMN ROWS FORMAT 9999 HEADING "Rows" |
The following query counts the number of rows
in the cereal
table:
SELECT COUNT(*) AS "rows" FROM cereal; |
The double quotes around the keyword rows
are required because the column alias rows
is an identifier in Oracle’s implementation of SQL. The double quotes let you reuse a rows
keyword as an ordinary column name.
The query should return the following because the COUNT(*)
function counts the number of rows in the table (effectively the asterisk (*
) is an indirect variable reference, or pointer, to the instances of rows in the table):
Rows
-----
70
You have the ability to query all columns in a SELECT
-list by using only the asterisk (*
), which is effectively a pointer complete rows. Unfortunately, Oracle’s SQL*Plus command-line interface (CLI) displays only 80 characters and most tables hold more than 80 characters, so you have the ability of limiting the SELECT
-list by qualifying only the columns you want to see.
The following query returns the cereal_id
, manufacturer
, and brand
columns. The SQL*Plus formatting commands set the cereal_id
column to a four digit number, the manufacturer column as a twenty character string, and the brand column as a thirty character string.
While the SQL*Plus COLUMN
command uses a FORMAT 9999
clause to format a number, a FORMAT A99
formats an alphanumeric string. A new SQL*Plus PAGESIZE
command sets the line break to 999
rows.
COL cereal_id FORMAT 9999 HEADING "Cereal|ID #" COL manufacturer FORMAT A20 HEADING "Manufacturer" COL brand FORMAT A30 HEADING "Brand" SELECT cereal_id , manufacturer , brand FROM cereal; |
The returns the following:
Cereal ID # Manufacturer Brand ------ -------------------- ------------------------------ 1 Kellogg Frosted Flakes 2 Kellogg Frosted Flakes 3 Kellogg Frosted Flakes ... 67 Kellogg Special K Original 68 Kellogg Special K Original 69 Kellogg Special K Red Berries 70 Kellogg Special K Red Berries |
You have the ability to query all columns in a SELECT
-list by using only the asterisk (*
), which is effectively a pointer complete rows. Unfortunately, Oracle’s SQL*Plus command-line interface (CLI) displays only 80 characters and most tables hold more than 80 characters, so you have the ability of limiting the SELECT
-list by qualifying only the columns you want to see.
The next sample SELECT
-list queries three actual columns found in the table, and creates a fourth column by performing a calculation on mathematical result between two of the actual columns. The numeric columns use a combination of the LPAD
and TO_CHAR
SQL built-in functions to right align a number formatted as a string result.
The SQL*Plus formatting combined with the query below:
COL brand FORMAT A30 HEADING "Brand" COL suggested_retail FORMAT A10 HEADING "Suggested|Retail" COL ounces FORMAT A10 HEADING "Ounces" COL cost_per_ounce FORMAT A10 HEADING "Cost|Ounces" SELECT brand , LPAD(TO_CHAR(suggested_retail,'$90.00'),10,' ') AS suggested_retail , LPAD(TO_CHAR(ounces,'00.00'),10,' ') AS ounces , LPAD(TO_CHAR(suggested_retail / ounces,'$0.00'),10,' ') AS cost_per_ounce FROM cereal; |
produces the following results:
Suggested Cost Brand Retail Ounces Ounces ------------------------------ ---------- ---------- ---------- Frosted Flakes $2.50 17.00 $0.15 Frosted Flakes $3.47 19.00 $0.18 Frosted Flakes $3.99 27.50 $0.15 Chocolate Frosted Flakes $4.99 24.70 $0.20 Corn Flakes $2.50 17.00 $0.15 Corn Flakes $3.47 19.00 $0.18 ... Special K Original $2.50 12.50 $0.20 Special K Original $3.29 16.90 $0.19 Special K Red Berries $2.50 12.50 $0.20 Special K Red Berries $3.29 16.90 $0.19 |
These examples have shown you how to return actual columns and derived columns; and also how you format them for display in a SELECT
-list.