0

I'm doing the pset8 tasks. Currently, I have some issues with sql7.

In 7.sql, write a SQL query to list all movies released in 2010 and their ratings, in descending order by rating. For movies with the same rating, order them alphabetically by title. Your query should output a table with two columns, one for the title of each movie and one for the rating of each movie. Movies that do not have ratings should not be included in the result.

When I do two separate queries, it works fine: request 1:

SELECT title
FROM movies
WHERE year = 2010

returns 11945 rows

request 2

SELECT rating
FROM ratings
WHERE movie_id IN (SELECT id FROM movies WHERE year = 2010)

returns 6835 rows

But when I'm trying to combine them:

SELECT title, rating
FROM movies, ratings 
WHERE (year = 2010) AND (movie_id IN (SELECT id FROM movies WHERE year = 2010))

it returns 81644075 rows. Considering that initial db movies has only 344276 rows, it looks strange.

  1. What I'm doing wrong?
  2. Why does the combined request not work?
  3. Should I join tables before selecting?
Improve this question

2 Answers 2

Reset to default
4

I also completed pset7 recently. You can easily make this query by joining the two tables using the JOIN and ON keywords and with the id as the primary key. The code should be as follows.

SELECT title, rating FROM movies JOIN ratings ON id = movie_id WHERE year = 2010 ORDER BY rating DESC, title ASC

As for your method, I'm a beginner in SQL and I didn't even know that syntax could be written like that! I'm surprised your program even run in other words! But I'd like to know how exactly it works as well!

Improve this answer
2
  • Thanks. Now I need to figure out how your method works
    – Vladimir Khudokormov
    Nov 22, 2020 at 7:47
  • We're just combining two tables into one! To explain primary key, let's take an example. Let's say the record for movie id 001 exists in both the tables. But in the combined table we want the two rows to be combined into one. So we specify id as the primary key, meaning combine two rows into one where id = movie_id. Thus we import the year and title field from the movies table and the rating field from the ratings table, and store it all in one row. Of course, this combined table is only temporary and not stored as a separate file in the database!
    – RubyShanks
    Nov 22, 2020 at 16:40
1

I just recently solved this PSET, so I'm no expert, so I'm going to try and explain what is different about what I did in comparison to what you have (meaning I can't answer the three questions you asked directly).

The biggest difference between mine and yours is that I joined the tables, which was important so that I could access the titles and ratings "FROM" only the movies table. By joining the two tables, I didn't have to have both "WHERE" conditions, only one that asked for the year.

Also make sure that you order your results as stated in the prompt. Otherwise, our code looks very similar!

P.S. I'm sorry I can't give a more complete answer. From experience, I have found that getting an early answer that partially solved the problem to be much more alleviating than a late answer that fully helped. Once you make some headway (as hopefully possible with what I have given you), you might find it easier to figure out what you did wrong. So I apologize if this doesn't help you as much as you wanted, but that was all I could give.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .