Main-Stage Fest Events Ranked by Rating — SQL Easy Problem

Problem statement

At the college fest, events with an odd event_id are staged in the open-air main arena; the even ones run in classrooms. The organisers want a running order for the main arena that leaves out the workshops.

Return event_id, title and rating for every event with an odd event_id whose category is not 'Workshop', ordered by rating from highest to lowest. When two events have the same rating, the one with the smaller event_id comes first.

Tables

Table: FestEvent

Primary key: event_id.

rating is the average audience rating out of 5, to one decimal place. category is never NULL.

Examples

Example 1

FestEvent

Output

How to solve Main-Stage Fest Events Ranked by Rating

Three requirements, each mapping onto one clause.

• Odd id. MOD(event_id, 2) = 1 (or event_id % 2 = 1) is true exactly for odd numbers. The ids are positive, so there is no negative-remainder surprise.
• Not a workshop. category <> 'Workshop'. The category is never NULL, so the NULL trap of <> does not apply here — but it is worth checking the schema for it every time you write <>.
• Order. ORDER BY rating DESC puts the best-rated event first. Two events can share a rating, and the statement fixes their order too, so a second key, event_id ascending, is needed. Without it the database may return tied rows in any order, and a comparison that checks order can fail at random.

The two filters are combined with AND in one WHERE, and the sort runs over the rows that are left: one scan plus a sort of the kept rows, O(n log n).

Equivalent spellings: the odd test as event_id - 2 * (event_id DIV 2) = 1 (subtract the even part and see what remains), and the category test as NOT IN ('Workshop').

Reference solution (MySQL)

SELECT event_id, title, rating
FROM FestEvent
WHERE MOD(event_id, 2) = 1 AND category <> 'Workshop'
ORDER BY rating DESC, event_id

Another way

SELECT event_id, title, rating FROM FestEvent WHERE event_id % 2 = 1 AND category NOT IN ('Workshop') ORDER BY rating DESC, event_id ASC

Another way

SELECT event_id, title, rating FROM FestEvent WHERE event_id - 2 * (event_id DIV 2) = 1 AND category <> 'Workshop' ORDER BY rating DESC, event_id