Hot answers tagged

8 votes

PSET7 Movies Q9: Why is my number of rows generated different from what the assignment requires?

I had the same problem. In the end I've solved it by getting rid of DISTINCT and using both ORDER BY and GROUP BY. Hope that helps!
user avatar
  • 101
7 votes
Accepted

pset7 movies Q12

It doesn't select any rows because there is no people record with both those names. The immediate suggestion would be to use OR instead of AND. Another option would be to use an alias of the people ...
user avatar
6 votes
Accepted

PSET7 Movies Q9: Why is my number of rows generated different from what the assignment requires?

Your DISTINCT would make the result records appear only once. But that means the name, and multiple stars might share a name. In other SQL dialects you could use DISTINCT ON (col1, col2) with an "ON" ...
user avatar
  • 20.7k
5 votes
Accepted

PSET7 Movies Q8 - Why these queries return different results?

It's because in your first query you said where id = (.... this query here is expecting only 1 value so in the query inside the parentheses the first value it gets is the only value that is checked. ...
user avatar
  • 711
5 votes

pset7 movies Q12

I've found GROUP BY to be very useful: WHERE name = 'Johnny Depp' OR name = 'Helena Bonham Carter' GROUP BY title HAVING COUNT(*) > 1;
user avatar
  • 51
3 votes

Pset7 8.sql is not giving output

Ok, nevermind i found it. It was literally a "%" after the 'Toy Story' in the last line of movies.title. But then it printed out 'Toy Story' in the output of 4 rows instead of the people who starred....
user avatar
3 votes
Accepted

pset7 movies sql7

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,...
user avatar
2 votes

pset7 movies Q12

You are also able to use the INTERSECT keyword. SELECT name FROM people JOIN *tablesyouwanttojoin* WHERE name = "actor number 1" INTERSECT SELECT name FROM people JOIN *tablesyouwanttojoin* ...
user avatar
2 votes
Accepted

How can I check my number of rows on pset7 movies?

You can redirect the output to a file and then open that and go to the bottom. $ cat 1.sql | sqlite3 movies.db > 1.txt alternatively, if you only want the number of rows $ cat 1.sql | sqlite3 ...
user avatar
  • 18.4k
1 vote

pset7 movies sql7

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 ...
user avatar
1 vote

How can I check my number of rows on pset7 movies?

You can also count the number of rows using SELECT COUNT(title) instead of SELECT title in the query.
user avatar
  • 992
1 vote

How to exclude 'Kevin Bacon's' name from query table?

Use and person_id not in (select id from people where name ="Kevin Bacon" AND birth=1958) before the group statement. Also, Use "select distinct" on first line in place of "...
user avatar
  • 992
1 vote
Accepted

Pset7 (Movies) 12.sql: Table outputs 96 rows rather than 5 rows

Using a previous post on this forum, I was able to deduce I had to use the INTERSECT operator, which instantly solves the problem.
user avatar
1 vote

pset7/movies/13.sql: exclude Kevin Bacon from the resulting list

Try: SELECT name FROM people WHERE id in ( SELECT person_id FROM stars WHERE movie_id in ( SELECT id FROM movies WHERE id in ( SELECT movie_id FROM stars WHERE person_id in ( SELECT id FROM people ...
user avatar
  • 11

Only top scored, non community-wiki answers of a minimum length are eligible