Select all the episode names of the first season of each series and sort them by name.

Note

We assume that you already created tables in step Creating a table and populated them with data in step Adding data to a table.

SELECT
series_title,               -- series_title is defined below in GROUP BY

String::JoinFromList(       -- calling a C++ UDF,
-- see below

AGGREGATE_LIST(title),  -- an aggregate function that
-- returns all the passed values as a list

", "                    -- String::JoinFromList concatenates
-- items of a given list (the first argument)
-- to a string using the separator (the second argument)
) AS episode_titles
FROM episodes
WHERE series_id IN (1,2)        -- IN defines the set of values in the WHERE clause,
-- to be included into the result.
-- Syntax:
-- test_expression (NOT) IN
-- ( subquery | expression  ,...n  )
-- If the value of test_expression is equal
-- to any value returned by subquery or is equal to
-- any expression from the comma-separated list,
-- the result value is TRUE. Otherwise, it's FALSE.
-- using NOT IN negates the result of subquery
-- or expression.
-- Warning: using null values together with
-- IN or NOT IN may lead to undesirable outcomes.
AND season_id = 1
GROUP BY
CASE                        -- CASE evaluates a list of conditions and
-- returns one of multiple possible resulting
-- expressions. CASE can be used in any
-- statement or with any clause
-- that supports a given statement. For example, you can use CASE in
-- statements such as SELECT, UPDATE, and DELETE,
-- and in clauses such as IN, WHERE, and ORDER BY.
WHEN series_id = 1
THEN "IT Crowd"
ELSE "Other series"
END AS series_title         -- GROUP BY can be performed on
-- an arbitrary expression.
-- The result is available in a SELECT
-- via the alias specified with AS.
;

COMMIT;