basketballsql exercise 4/a

[rolandpinter]

Unfortunately, I do not really understand the phrasing of exercise 4/a, could you please help me (and probably others as well) a bit? The exercise is the following: "Pick a game and list all field shots and free throw attempts, including points".
(1) First of all, field shot means a "dbo.shot"? I've checked dbo.shot_type-s, because I thought that I could identify a dbo.shot to be a field shot, but I did not see field shot listed in the types.
(2) How do I know from a free throw (dbo.freethrow) whether it was an attempt or an actual shot which resulted in a score?
(3) Where can I find the information which tells me how much point did a shot resulted in?
(4) In exercise 4/b, it is written that: "Combine the previous two into a single table and only show successful attempts". This suggests me that in 4/a, I should have 2 tables? So 4/a should not be like an inner join or something like that we did in class?

Thanks a lot in advance!

[jegesm]

(1) As far as I know all shots that are not free throws are field shots so dbo.shot contains all the field shots

(2) the 'points' table holds the information about scoring. Every shot has a 'cntr' (counter) through which you can identify its scoring outcome.

(3) 'points' table again

(4) if in 4/a you had two separate tables then you can use UNION to combine the two. Though the instruction written in 4/a suggests already a combined table, which makes 4/b the same as 4/a :)

Thanks for the comment, this will help me revise the sentences later!

[rolandpinter]

Thanks very much for your reply! However, there are still some things which are quite strange for me. When I do the following SQL query:

SELECT freethrow.cntr AS 'Freethrow cntr', point.points AS 'Freethrow Point'
FROM freethrow
INNER JOIN point ON freethrow.cntr = point.cntr
INNER JOIN game ON game.id = freethrow.game_id
WHERE game.id = 1

This should list the freethrows identified by their CNTR value, and next to them, I put the corresponding point.

Well, from your previous reply, I concluded that CNTR serves as kind of a 'unique id' to identify throws. But it turns out that there are several freethrows for instance with cntr=8, and for these, the point value differs.

So what is still unclear for me is the following: what is the meaning of CNTR?

Thanks in advance!

[jegesm]

If you use SELECT * , so reveal all columns of this query, then you can see that you will have different game_ids in the same row coming from the freethrow and points columns.

Let's now forget about the game table! When listing freethrows of the choosen game along with the points then we have JOIN them along game_id and the cntr columne at the same time.

SELECT *
FROM freethrow
LEFT JOIN point ON freethrow.cntr = point.cntr AND point.game_id = freethrow.game_id
WHERE freethrow.game_id = 1 

Here we used LEFT JOIN, because we wanted to list all attempts. If we want to list only the successful attempts, then we us INNER JOIN.

And the same goes for the shots table as well

[dBenedek]

Hello,
I would like to ask you, if I left join shot and point tables, or freethrow and point table (on cntr and game_id columns), the rows with empty points value are unsuccessful attempts?
Thanks in advance.

[jegesm]

Yes, they are unsuccessful attempts.