NJIT

CS 331: Database System Design and Management

14. Find the person id, average budget, and average revenue for all movies starring Al Pacino where

revenue is greater than thrice its budget. (1 row)

15. Find the number of movies for each genre id for movies released in 1996. (12 rows)

16. Find the genre id, genre name, and total revenue made by comedy movies whose budget was

more than a hundred million. (1 row)

17. Find the number of movies and average revenue for all action movies (genre_name='Action')

starring Harrison Ford. (1 row)

18. Find the genre id, genre name, average genre budget, and average genre revenue for all genres

whose average revenue is greater than five times the average genre budget. (7 rows)

19. Find all horror movies along with the movie id, title, and genre id. (42 rows)

20. Find the id, title, and budget for all movies, where the budget is greater than the average budget

for those movies starring George Clooney whose revenue is over three times its budget. (104

rows)/nQuestion 3 [100 Points (5 points each)].

For answering the following questions, use the file containing the data. You MUST provide the

following for each query:

RA queries written over the given template AND WRITE RA QUERY on the

answer sheet as well; not doing so will result in zero point for this question. (2 points

for writing the query on the template + 2 point to write the same query on the

answer sheet + 1 point for pasting screenshot of the output = 5 points)

Use relational algebra to perform these queries. You see the number of returned rows in

front of each query.

1. Find the movie titles of all action movies (genre_id = 28). (158 rows)

2.

Find all movie titles, the release date, and the character name of the lead cast member (cast order

0) for movies released in 2005. (17 rows)

3.

Find the movies that have 8 or more crew members. (58 rows)

4. Find the movie ids where cast members have a person id of 114, or crew members have a person

id of 60. (7 rows)

5. Find all movie ids with cast members with person id 1461 minus those with genre id 28. (5 rows)

6. Find the movie ids of movies with a character named Ricky Fitts and a crew member with person

id 494. (1 row)

7. Find the movie title, character names, and person names of cast members with a cast order of 1

for movies with an average vote greater than 8 and a runtime greater than 150 minutes. (9 rows)

8. Find all genre names and the sum of movie budgets for each genre where the sum of budgets is

more than a billion. (14 rows)

9. Find the number of movies released in 2001 and 2002 grouped by genre id. (17 rows)

10. Find the character names and movie ids of all Harrison Ford movies. (6 rows)

11. Find the genre name and average revenue for each movie genre. (18 rows)

12. Find the person id, person name, and number of movies for all cast members with more than 3

movies. (28 rows)

13. Find the titles of all movies with a total profit greater than 750 million. (15 rows)

Note: a movie is said to be profitable if its revenue is greater than its budget.