Consider the given relational database moviedb which has the following relation
ID: 2246303 • Letter: C
Question
Consider the given relational database moviedb which has the following relation schemas:
Movie(title, production year, country, run time, major genre) primary key : {title, production year} Person(id, first name, last name, year born) primary key : {id}
Director(id, title, production year) primary key : {title, production year} foreign keys : [title, production year] Movie[title, production year] [id] Person[id]
Writer(id, title, production year, credits) primary key : {id, title, production year} foreign keys : [title, production year] Movie[title, production year] [id] Person[id] write an SQL query for the below two: 1) Assume persons who were born in the same year are the same age and there is only one youngest person (with no ties/draws) in this database, who is/are the second youngest person(s) in the database? List the id(s) of the person(s) 2) A person has worked on a movie if this person is a director, a writer, or both a director and writer of this movie. Who has/have worked on the largest number of distinct movies in this database? List the id(s) of the person(s).
Explanation / Answer
1
SELECT id FROM Person WHERE year born IN (
SELECT min(a.year) from Person a, Person b WHERE a.year born > b.year born
)
2
SELECT id FROM
(
SELECT id, count(title, production) FROM (
(SELECT id, title, production year FROM Director)
UNION
(SELECT id, title, production year FROM Writer)
) GROUB BY id HAVING count(title, production) =
SELECT MAX(idcount) FROM
(
SELECT id, count(title, production) idcount FROM (
(SELECT id, title, production year FROM Director)
UNION
(SELECT id, title, production year FROM Writer)
) GROUB BY id
)
)
I hope you like the answer. If you have any query or doubt, please let me know. Shall be glad to help you.