MacLochlainns Weblog

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

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/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.