We have a
Cinema table with the following schema:
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.
Here's an example:
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.
Let's review three SQL solutions, each utilizing a slightly different logic to reach the result.
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.
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.
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.
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.