Problem Description –
Write an SQL query to report the movies with an odd-numbered ID and a description that is not
Return the result table in descending order by
The query result format is in the following example:
Difficulty Level – Easy
SELECT id, movie, description, rating FROM ( SELECT *, CASE WHEN MOD(id,2) != 0 THEN 1 ELSE 0 END as flag FROM cinema ) t1 WHERE flag = 1 AND description != 'boring' ORDER BY rating DESC
The decide which id’s are odd, we are using the MOD() function. This function returns the remainder of a number divided by another number. So, if we divide an odd-numbered id by 2 then its remainder is not going to be 0 that is why we used the condition and assign the value of 1, and in the case of an even number, we assign a value of 0 in the flag column. Now, in the outer query, all we are doing is just selecting the rows where flag = 1 and description!= ‘boring’ to get the desired result. Also, we have to order the result by rating in descending order.