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 tables in step Creating a table and populated them with data in step Adding data to a table.

1. SELECT
2. series_title,               -- series_title is defined below in GROUP BY

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

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

10. ", "                    -- String::JoinFromList concatenates
11. -- items of a given list (the first argument)
12. -- to a string using the separator (the second argument)
13. ) AS episode_titles
14. FROM episodes
15. WHERE series_id IN (1,2)        -- IN defines the set of values in the WHERE clause,
16. -- to be included into the result.
17. -- Syntax:
18. -- test_expression (NOT) IN
19. -- ( subquery | expression ` ,...n ` )
20. -- If the value of test_expression is equal
21. -- to any value returned by subquery or is equal to
22. -- any expression from the comma-separated list,
23. -- the result value is TRUE. Otherwise, it's FALSE.
24. -- using NOT IN negates the result of subquery
25. -- or expression.
26. -- Warning: using null values together with
27. -- IN or NOT IN may lead to undesirable outcomes.
28. AND season_id = 1
29. GROUP BY
30. CASE                        -- CASE evaluates a list of conditions and
31. -- returns one of multiple possible resulting
32. -- expressions. CASE can be used in any
33. -- statement or with any clause
34. -- that supports a given statement. For example, you can use CASE in
35. -- statements such as SELECT, UPDATE, and DELETE,
36. -- and in clauses such as IN, WHERE, and ORDER BY.
37. WHEN series_id = 1
38. THEN "IT Crowd"
39. ELSE "Other series"
40. END AS series_title         -- GROUP BY can be performed on
41. -- an arbitrary expression.
42. -- The result is available in a SELECT
43. -- via the alias specified with AS.
44. ;

46. COMMIT;