Question

Given the following relational schema for a movie database: Movie(m-id, title, year, genre, directorName, country, rating) directorName is a foreign key to Director(name) MovieStar(s-id, name, country, gender, birthYear) StarIn( m-id, s-id )m-id is a foreign key to Movie, s-id is a foreign key to MovieStar Director(name, birthYear, country) For each of the following queries, give an expression in SQL.

Find the title and year of the movies directed by the directors younger than 40 from Turkey.

Answer 1

To find movie titles and years directed by Turkish directors younger than 40, a SQL query should join the Movie and Director tables, filtering on country and calculating the director's current age.

Step-by-step explanation:

The question pertains to constructing a SQL query for a given relational movie database schema. The specific task is to find the title and year of movies directed by directors who are younger than 40 years old and from Turkey. To accomplish this, we need to join the Movie and Director tables, filter by the director's age and country, and then select the relevant movie information.

Sample SQL Query

Here is one way the SQL query can be written:

SELECT Movie.title, Movie.year
FROM Movie
JOIN Director ON Movie.directorName = Director.name
WHERE Director.country = 'Turkey' AND
(YEAR(CURRENT_DATE) - Director.birthYear) < 40;

This query joins the Movie and Director tables on the director's name, checks the director's country, and calculates the director's age to filter for those younger than 40.