Additional selection criteria
Select all the episode names of the first season of each series and sort them by name.
Note
We assume that you already created the 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 in the WHERE clause defines the set of values
-- 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 result
-- 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, DELETE,
-- and in clauses such as IN, WHERE, ORDER BY.
WHEN series_id = 1
THEN "IT Crowd"
ELSE "Other serial"
END AS series_title -- GROUP BY can be performed on
-- an arbitrary expression.
-- Result is available in SELECT
-- via the alias specified with AS.
;
COMMIT;