Not Boring Movies is the question asked in many interviews and listed on leetcode at 596. 


Let's start with SQL Schema for the table named cinema:


Table Cinema:


CREATE TABLE cinema (id int, movie varchar(255), description varchar(255), rating float(2, 1))


INSERT INTO cinema (id, movie, description, rating) values ('1', 'War', 'great 3D', '8.9')

INSERT INTO cinema (id, movie, description, rating) values ('2', 'Science', 'fiction', '8.5')

INSERT INTO cinema (id, movie, description, rating) values ('3', 'irish', 'boring', '6.2')

INSERT INTO cinema (id, movie, description, rating) values ('4', 'Ice song', 'Fantacy', '8.6')

INSERT INTO cinema (id, movie, description, rating) values ('5', 'House card', 'Interesting', '9.1')



id movie description rating
1 War great 3D 8.9
2 Science fiction 8.5
3 irish boring 6.2
4 Ice song Fantacy 8.5
5 House card Interesting 9.1


Question: Write an SQL query to report the movies with an odd-numbered ID and a description that is not "boring".

Return the result table ordered by rating in descending order.

The query result will be:




id movie description rating
5 House card Interesting 9.1
1 War great 3D 8.9



You have the table and question to find all the classes that have at least five students.

Now, let's start with the Algorithm:


Let's first remove 'boring' from the list,

SELECT * 
  FROM cinema 
    WHERE description NOT IN ('boring');


The output of the above query will look like this:

id movie description rating
1 War great 3D 8.9
2 Science fiction 8.5
4 Ice song Fantacy 8.5
5 House card Interesting 9.1


Now table we have to display only odd ids for that we will just find the modulus of id with 2 and if the remainder is 1 then that is an odd Id.

So, our query will look like this,


SELECT * 
  FROM cinema 
    WHERE description NOT IN ('boring') AND id%2 = 1;


The output of the above query will look like this:



id movie description rating
1 War great 3D 8.9
5 House card Interesting 9.1


Now output table requires this in descending order of rating.

So the query will be:


SELECT * 
  FROM cinema 
    WHERE description NOT IN ('boring') AND id%2 = 1 
      ORDER BY rating DESC;


We can use the function for i%2 = 1 which will increase the speed of our query that is mod(id,2) = 1

SELECT * 
  FROM cinema 
    WHERE description NOT IN ('boring') AND mod(id,2) = 1 
      ORDER BY rating DESC;

 
Both queries can be used to get the final result.


I hope you understood the algorithm if you have any queries or doubts please let us know in the comment section.