Skip to content
GATEiit

GATE DA 2024 Q55: Indexes that speed up a three-table SQL join

Question

An OTT company is maintaining a large disk-based relational database of different movies with the following schema:

Movie(ID, CustomerRating)

Genre(ID, Name)

Movie_Genre(MovieID, GenreID)

Consider the following SQL query on the relation database above:

SELECT *
FROM Movie, Genre, Movie_Genre
WHERE
  Movie.CustomerRating > 3.4 AND
  Genre.Name = "Comedy" AND
  Movie_Genre.MovieID = Movie.ID AND
  Movie_Genre.GenreID = Genre.ID;

This SQL query can be sped up using which of the following indexing options?

Try it: multiple select: one or more correct options

Select one or more options
Select all correct options. No negative marking, no partial marks.
Show answer and explanation

Answer: A, B (official GATE 2024 final answer key)

Concept used: Hash indexes support equality lookups only; B+ trees support both equality and range conditions.

First step: CustomerRating > 3.4 is a range condition, so it needs a B+ tree; the equality conditions can use either index type.

Full step-by-step solution

Detailed working, common mistakes and linked mock tests will be available with a free GATEiit account. Log-in is coming soon.

Share this question

WhatsAppDownload image

Source: official GATE 2024 DA question paper and final answer key, organised by IISc Bangalore. Question paper · Answer key

More Database Management and Warehousing PYQs

PracticePractise Database Management and Warehousing questions →Full paperAll GATE DA 2024 questions →