0

I have 2 tables I am working with, one is movie_score which contains an id, name, and score. I have another table that is movie_cast which contains mid, cid, and name. Mid is movie id and cid is cast id. the problem I must do is as follows:

Find top 10 (distinct) cast members who have the highest average movie scores. The output list must be sorted by score (from high to low), and then, by cast name in alphabetical order, if/when they have the same average score. The search must NOT include: (a) movies with scores lower than 50 AND (b) cast members who have appeared in less than 3 movies (again, only counting the number of appearances in movies with scores of at least 50). (Expected Output: cid, cname, average score)

I have tried to put the command together but so far this is all I was able to get:

SELECT DISTINCT movie_cast.cid, movie_cast.cname, FROM movie_score INNER JOIN movie_cast ON movie_score.id=movie_cast.mid ORDER BY cname LIMIT 10;

movie-name-score.txt goes with movie_score:

Example of .txt file

9,"Star Wars: Episode III - Revenge of the Sith 3D",80

24214,"The Chronicles of Narnia: The Lion, The Witch and The Wardrobe",76

1789,"War of the Worlds",74

10009,"Star Wars: Episode II - Attack of the Clones 3D",67

771238285,"Warm Bodies",-1

770785616,"World War Z",-1

771303871,"War Witch",89

771323601,"War of the Worlds the True Story",-1

movie-cast.txt goes with movie_cast:

Example:

9,162652153,"Hayden Christensen"

9,162652152,"Ewan McGregor"

9,418638213,"Kenny Baker"

9,548155708,"Graeme Blundell"

9,358317901,"Jeremy Bulloch"

9,178810494,"Anthony Daniels"

9,770726713,"Oliver Ford Davies"

9,162652156,"Samuel L. Jackson"

9,162655731,"James Earl Jones"

I expect to have an output something like:

162655731,"James Earl Jones",average score of the movies they have been in

Does anyone know the best way to create this command?

shamp113
  • 11
  • 3
  • 3
    Please tag with the database you are actually using, and also provide us with some sample data from both tables. – Tim Biegeleisen Sep 06 '19 at 01:50
  • and also show your desired output. – Keith Sep 06 '19 at 01:50
  • See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/q/333952/62576). You need to tag with the specific DBMS, and provide sample data and the output you expect to obtain from that data in order for us to help. Also, your posted SQL statement isn't valid syntax (it contains an error and won't execute), so it's either not your actual statement or you've not bothered to try it yourself. – Ken White Sep 06 '19 at 01:56
  • Possible duplicate of [Find the movies with the highest average rating using SQL max()](https://stackoverflow.com/questions/34578223/find-the-movies-with-the-highest-average-rating-using-sql-max) – Ken White Sep 06 '19 at 01:58
  • you cannot process `.txt` file in sql DBMS, so you must imported this data in table structure and your question should start from there. Provide data in table structure. Also post your expected output. And mention appropriate tag since `sql` is just language not DBMS... – DarkRob Sep 06 '19 at 04:05

0 Answers0