Question

SQL Exercise:

I am not sure of how to interpret the following instructions. I have to build an SQL query from them:

"For each director, find the number of awards won by the movie he has directed."

That is literally it. But I assume the professor means that the director could direct more than one movie. So I am interpreting it so far as "the number of awards won by all the movies he directed."

Now, the tables I am given include one for Directors and another for Movies. Each movie has columns for the number of awards won, and a foreign key that points to its director's data in the...

Answer 1

... Directors table.

At first I thought this could be a join (although I am still confused about when to use one join or another –inner, left, right, full, etc.), then I thought it could be a sum of all the awards he has won with all his movies combined.

Answer 2

They seriously need to increase the post length already...

Answer 3

It would help to see the actual data if you have it...

Answer 4

This is what I am trying to do right now. But it doesn't seem to be working. Either the syntax is wrong, or the join I am using is not the right one, or I can't combine joins and aggregate functions (that would be questionable, though). I don't know.

I'll get you the data as soon as I can. I don't have it in a text document nor anything like that. I entered it manually into my database from the given paper.

Answer 5

Can send the schema (DDL) so I can reproduce the tables?

Answer 6

Sorry, my program won't let me export a database schema. Honestly, I wasn't expecting to need anything too advanced for this homework, so I went with LibreOffice Base.

Answer 7

Try this:

sqlite> select D.name, SUM(M.awards) from directors D, movies M where D.id = M.director_id group by D.id;

(Of course, you'll need to change the column names to suit your needs)

Answer 8

What system are you running on (Mac, Ubuntu, etc)? SQlite3 is a very useful file-based database for quick tests

Answer 9

SELECT MOVIE.DIRNUMB, SUM( "MOVIE"."AWRD" ) AS AWARDS FROM "MOVIE" INNER JOIN DIRECTOR ON DIRECTOR.DIRNUMB = MOVIE.DIRNUMB GROUP BY "MOVIE"."DIRNUMB"

This works, but it will give me a "Not in aggregate function or group by clause" error if I try to select an additional column, which belongs to the Directors table: DIRECTOR.DIRNAME.

Answer 10

I am on Mac OS X. I know SQLite (and it's awesome), but I am short on time, and need to generate views and such for this homework, so I went with Libreoffice Base because it let me generate some basic forms and views very quickly.

Answer 11

That's because you need to "GROUP BY DIRECTOR.DIRNUMB"

Answer 12

Okay... so let's see...

Answer 13

SELECT D.dirnumb, D.dirname, SUM(M.awrd) FROM movie M, director D WHERE D.dirnumb = M.dirnumb GROUP BY D.dirnumb;

Answer 14

OK, I just did that!

SELECT MOVIE.DIRNUMB, DIRECTOR.DIRNAME, SUM( "MOVIE"."AWRD" ) AS AWARDS FROM "MOVIE" INNER JOIN DIRECTOR ON DIRECTOR.DIRNUMB = MOVIE.DIRNUMB GROUP BY "MOVIE"."DIRNUMB", DIRECTOR.DIRNAME

And it worked. :) Thanks!

Answer 15

Great, glad it works! =P