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!
- 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 ...
- 26.7k
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" ...
- 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. ...
- 701
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;
- 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....
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,...
- 44
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*
...
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 ...
- 18.3k
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 ...
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.
- 957
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 "...
- 957
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.
- 23
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 ...
- 11
Only top scored, non community-wiki answers of a minimum length are eligible