Learn SQL decision logic

Welcome to the SQL decision logic tutorial, which describes useful ways to craft your SQL statements using a sample dataset. Using Conditional expressions, implement decision logic within each SQL statement.

Note

The Sample cluster used in the following example is not available in Starburst Enterprise (SEP). For more information on the sample dataset used in this tutorial, see the Starburst Galaxy Sample dataset documentaion.

Navigate the query editor

In the query editor, navigate to the Cluster explorer.

1. In the navigation menu, choose or name your catalog and adjust the queries in the tutorials accordingly.

2. Select the catalog in the location drop-down menus.

In the location drop-down menus, select the cluster and the name of your catalog in order to run the queries without having to specify the full table path location in each query.

The namespace for a table is typically specified as catalog_name.schema_name.table_name. For the predefined sample dataset, this configuration is as follows:

sample.demo.<table_name>

Conditional expressions

Conditional expressions are used to define logic based on an appropriately satisfied condition. Before implementing the Conditional expressions, count the number of missions associated with each astronaut.

SELECT
  name,
  count() AS nbr_missions
FROM
  sample.demo.astronauts
GROUP BY
  name
ORDER BY
  nbr_missions DESC;

Classify the astronauts as either rookies or veterans using the IF expression and the count() aggregate function.

SELECT
  name,
  count() AS nbr_of_missions,
  IF(count() > 1, 'Veteran', 'Rookie') AS nbr_of_mission_qualification
FROM
  sample.demo.astronauts
GROUP BY
  name;

Expand on the astronaut mission classification by assigning a space rank based on the specific number of completed of missions. Use the CASE expression to assign the new astronaut mission classification. Since there are multiple entries for each astronaut to correspond to the number of completed missions, observe the astronauts rise through the space ranks for each completed mission.

SELECT
  name,
  nationality,
  mission_number,
  CASE
    WHEN mission_number < 3 THEN 'Space Cadet'
    WHEN mission_number = 3 THEN 'Space Captain'
    WHEN mission_number = 4 THEN 'Space Colonel'
    WHEN mission_number = 5 THEN 'Space General'
    WHEN mission_number = 6 THEN 'Space Warrior'
    WHEN mission_number > 6 THEN 'Space Avenger'
    ELSE 'unknown'
  END AS space_rank
FROM
  sample.demo.astronauts
ORDER BY
  name,
  mission_number;

Observe the trajectory of one of the space avengers as he rose through the space ranks for each completed mission.

SELECT
  name,
  nationality,
  mission_number,
  year_of_mission,
  mission_title,
  CASE
    WHEN mission_number < 3 THEN 'Space Cadet'
    WHEN mission_number = 3 THEN 'Space Captain'
    WHEN mission_number = 4 THEN 'Space Colonel'
    WHEN mission_number = 5 THEN 'Space General'
    WHEN mission_number = 6 THEN 'Space Warrior'
    WHEN mission_number > 6 THEN 'Space Avenger'
    ELSE 'unknown'
  END AS space_rank
FROM
  sample.demo.astronauts
WHERE
  name = 'Ross, Jerry L.'
ORDER BY
  name,
  mission_number;

Instead of assigning a space rank for multiple entries of the same astronaut, use the GROUP BY clause to calculate the space rank of each astronaut from their completed total number of missions.

SELECT
  name,
  total_number_of_missions,
  CASE
    WHEN total_number_of_missions < 3 THEN 'Space Cadet'
    WHEN total_number_of_missions = 3 THEN 'Space Captain'
    WHEN total_number_of_missions = 4 THEN 'Space Colonel'
    WHEN total_number_of_missions = 5 THEN 'Space General'
    WHEN total_number_of_missions = 6 THEN 'Space Warrior'
    WHEN total_number_of_missions > 6 THEN 'Space Avenger'
    ELSE 'unknown'
  END AS space_rank
FROM
  sample.demo.astronauts
GROUP BY
  name, total_number_of_missions
ORDER BY
  total_number_of_missions DESC;

Only view astronauts of the rank of ‘Space Colonel’ or higher by adding the WHERE clause to the query.

SELECT
  name,
  total_number_of_missions,
  CASE
    WHEN total_number_of_missions < 3 THEN 'Space Cadet'
    WHEN total_number_of_missions = 3 THEN 'Space Captain'
    WHEN total_number_of_missions = 4 THEN 'Space Colonel'
    WHEN total_number_of_missions = 5 THEN 'Space General'
    WHEN total_number_of_missions = 6 THEN 'Space Warrior'
    WHEN total_number_of_missions > 6 THEN 'Space Avenger'
    ELSE 'unknown'
  END AS space_rank
FROM
  sample.demo.astronauts
WHERE
  total_number_of_missions >= 4
GROUP BY
  name, total_number_of_missions
ORDER BY
  total_number_of_missions DESC;

Next steps

Check out our other SQL tutorials, or dive right into the SQL documentation and experiment with your own data.