8
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!
7
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 table to join it as "another" table, each (people and "another") joined on one of the names.
It would be easy to create a list of Johnny Depp ...
6
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" and a list of columns to use for the DISTINCT, but SQLite does not seem to support such thing.
I actually solved this by applying the DISTINCT to a sub-query ...
5
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. To make your query correct all you have to do is change the = with IN, the IN command here checks a list of values.
e.g.
SELECT * FROM smth WHERE name IN ('...
4
I've found GROUP BY to be very useful:
WHERE name = 'Johnny Depp' OR name = 'Helena Bonham Carter'
GROUP BY title
HAVING COUNT(*) > 1;
3
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. I accidentally wrote SELECT title FROM people instead of SELECT name FROM people.
Sorry for wasting your time.
2
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*
WHERE name = "actor number 2"
This was the most elegant suggestion I could find online since I had the same problem...
2
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 movies.db | wc -l
The wc -l counts the lines, that would include the header, so you would have to subtract 1 to get the number of data rows.
2
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 ...
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 &...
1
You can also count the number of rows using
SELECT COUNT(title)
instead of
SELECT title
in the query.
1
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 "select".
If this helped, please click the tickmark.
1
Using a previous post on this forum, I was able to deduce I had to use the INTERSECT operator, which instantly solves the problem.
Only top voted, non community-wiki answers of a minimum length are eligible