SELECT Clause

Description

The SELECT statement specifies columns that you want to retrieve from a table.

Syntax

SELECT [ qualifier ] select_expression [ { TO | INTO }
variable [ {, variable } ] ]

qualifier :  
ALL
DISTINCT
UNIQUE

select_expression :
*
table_name. *
expression [ {, expression}...]

variable :
[:] identifier

• qualifier : A qualifier. It can be omitted. When omitted, it is set to ALL.
  • ALL : Retrieves all instances of the table.
  • DISTINCT : Retrieves only instances with unique values without allowing duplicates.
  • UNIQUE : Like DISTINCT, retrieves only instances with unique values without allowing duplicates.

• select_expression :
  • * : By using SELECT * statement, you can retrieve all the columns from the table specified in the FROM clause.
  • table_name. * : Specifying the table name with * works the same as specifying all the columns from the given table.
  • expression [ {, expression}...] : expression can be a path expression, variable or table name. All general expressions including arithmetic operations can also be used. Use a comma (,) to separate each expression in the list.
    As AVG, COUNT, MAX, MIN, or SUM, an aggregate function that manipulates the retrieved data can also be used in the expression. When an aggregate function is used as the expression, but not used together with the GROUP BY clause, all elements of the retrieval list must be the aggregate function.

• variable : The data retrieved by the select_expression can be saved in more than one variables.
  • [:]identifier : By using the :identifier after TO (or INTO), you can save the data to be retrieved in the ':identifier' variable.

Example

• Example 1
• The following is an example of retrieving host countries of the Olympic Games without any duplicates. This example generates, as the result, a list with no duplicated host_nation values from the olympic table.
  The DISTINCT or UNIQUE keyword allows only unique values in the query result set. For example, when there are multiple olympic instances whose host_nation values are 'Greece', you can use such keywords to display only one value in the query result.

SELECT DISTINCT host_nation FROM olympic;

=== <Result of SELECT Command in Line 1> ===
  host_nation
======================
  'Australia'
  'Belgium'
  'Canada'
  'Finland'
  'France'
...
18 rows selected.

• Example 2
• The following is an example of retrieving all information on the Olympic Games that have been held. To retrieve all the columns, you can use the asterisk (*) instead of the list of column names. In the query result, the column values are displayed in the order defined in the table.

SELECT * FROM olympic;
 
=== <Result of SELECT Command in Line 1> ===
    host_year  host_nation              host_city     opening_date  closing_date  mascot      slogan                           introduction
=================================================================================================================================
        1988  'Korea'                    'Seoul'         09/17/1988    10/02/1988  'HODORI'    'Harmony and progress'             'The 1988 Seoul Games we're the first Olympics to allow professional athletes to compete in certain events…
         1992 'Spain'                    'Barcelona '    07/25/1992    08/09/1992   'Cobi'     'Friends Forever '                 'For the first time in decades, no nations boycotted the 1992 Barcelona Games…
         1996 'United States of America' 'Atlanta '      07/19/1996    08/09/1996   'Izzy'     'The Celebration of the Century '  'The 1996 Atlanta Games celebrated 100 years of the Modern Olympic Games.
...
25 rows selected.