The Problem

We have a Cinema table with the following schema:

The id field is a primary key. Each row contains information about a movie, its description, and its rating. We need to write an SQL query to report the movies with an odd-numbered ID and a description that doesn't include "boring". The result should be ordered by rating in descending order.

Explanation

Here's an example:

Cinema table:

Output:

There are three movies with odd-numbered IDs: 1, 3, and 5. The movie with ID = 3 has a description labeled as "boring", so we exclude it.

The Solution

Let's review three SQL solutions, each utilizing a slightly different logic to reach the result.

Source Code 1

The first solution checks for odd id values by using the modulus operator (%). It excludes rows with a description of 'boring' and sorts the output by rating in descending order.

SELECT
    id,
    movie,
    description,
    rating
FROM Cinema
WHERE
    id%2 != 0
    AND
    description != 'boring'
ORDER BY
    rating DESC

This solution performs in 343ms, beating 83.24% of other submissions.

Source Code 2

The second solution is similar to the first but uses a different method to check for odd IDs and uses NOT LIKE to check for 'boring' in the description.

SELECT
    id,
    movie,
    description,
    rating
FROM Cinema
WHERE
    (id+1)%2 = 0
    AND
    description NOT LIKE '%boring%'
ORDER BY
    rating DESC

This solution takes 590ms, outperforming 17.90% of other solutions.

Source Code 3

The third solution uses a NOT clause to negate conditions that we want to exclude, i.e., even IDs and descriptions equal to 'boring'.

SELECT
    id,
    movie,
    description,
    rating
FROM Cinema
WHERE NOT
    (
        id%2 = 0
        OR
        description = 'boring'
    )
ORDER BY
    rating DESC

This solution has a runtime of 349ms, beating 79.36% of submissions.

Conclusion

All solutions correctly provide the required output but have differing performances due to the varied logic utilized.

Here is the rank of the solutions from the best to worst for overall performance: Source Code 1 > Source Code 3 > Source Code 2.

You can find the original problem at LeetCode.

For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.

ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai