Basic SQL Question

[KGuy]

I have a question on a practice assignment that I can't solve. Can someone
help me out?

Question:

The table Arc(x,y) currently has the following tuples (note there are
duplicates): (1,2), (1,2), (2,3), (3,4), (3,4), (4,1), (4,1), (4,1), (4,2).
Compute the result of the query:

SELECT a1.x, a2.y, COUNT(*)
FROM Arc a1, Arc a2
WHERE a1.y = a2.x
GROUP BY a1.x, a2.y;

Which of the following tuples is in the result?


a) (2,3,2)
b) (2,4,6)
c) (4,2,6)
d) (3,2,6)

[Aggro]

Re: Basic SQL Question

KGuy wrote:[color=blue]
> I have a question on a practice assignment that I can't solve. Can someone
> help me out?[/color]

Yes, create the table in question to your database, insert the given
values to there and then execute the given query and check which of
given results matches to the actual result.

This task is so simple that you don't even need brains to solve it,
since all you have to do is follow the instructions, compare few rows
and tell what you see.

If you don't have database, you can get one for free, for example mysql:

http://www.mysql.com/

[KGuy]

Re: Basic SQL Question

> Yes, create the table in question to your database, insert the given[color=blue]
> values to there and then execute the given query and check which of given
> results matches to the actual result.[/color]

Of course, I could do that, but I would like to understand why the output is
what it is. Sorry if I was unclear. Thanks for the reply.

-Imran

[Aggro]

Re: Basic SQL Question

KGuy wrote:
[color=blue]
> Of course, I could do that[/color]

Don't say you could do that, just do it. When you tell the corrent
answer, someone might be able explain it. And if want to make a guess,
be sure not choose wrong one.

Please understand that if we just give the correct answers it would be
the same as just shooting you in the head. It would do you more harm
than good. Point of practise assignments is that you learn by doing them.

[KGuy]

Re: Basic SQL Question

>I have a question on a practice assignment that I can't solve. Can someone[color=blue]
> help me out?[/color]

Thanks everybody. I managed to solve it by hand using tips from someone
(Andy Hassall). It takes a while, but at least it's doable and I understand
it. If the answer is important to you, reply to this post.

[Hugo Kornelis]

Re: Basic SQL Question

On Sun, 23 Jan 2005 13:00:20 -0500, KGuy wrote:

(crossposting removed)
[color=blue]
>I have a question on a practice assignment that I can't solve. Can someone
>help me out?[/color]

Hi KGuy,

In a few weeks time, you'll have a test. If you don't learn to work out
your assignments now, you'll certainly fluke the test.

And if you're lucky and pass the test, you'll be in even more trouble when
you're hired and you have to debug some real SQL.

[color=blue]
>Question:
>
>The table Arc(x,y) currently has the following tuples (note there are
>duplicates)[/color]
(snip)

If there are duplicates, you don't have a table at all. A collection of
data that may hold duplicates is a heap. I'm truly amazed that there are
still schools where SQL is taught with text books that don't include a
primary key on every table in every example or every assignment.

[color=blue]
> (1,2), (1,2), (2,3), (3,4), (3,4), (4,1), (4,1), (4,1), (4,2).
>Compute the result of the query:
>
>SELECT a1.x, a2.y, COUNT(*)
>FROM Arc a1, Arc a2
>WHERE a1.y = a2.x
>GROUP BY a1.x, a2.y;[/color]

Almost all professionals prefer the (more verbose, but better documenting)
infixed join notation. For outer join, the infixed notation is the only
way to avoid ambiguity. For inner joins, beth versions are allowed, but
the infixed notation is more popular. Also, avoiding the optional AS
between table name and table alias is not recommended either!

SELECT a1.x, a2.y, COUNT(*)
FROM Arc AS a1
INNER JOIN Arc AS a2
ON a1.y = a2.x
GROUP BY a1.x, a2.y;

This is how the query should (IMO) appear in a decent studybook.

[color=blue]
>Which of the following tuples is in the result?
>
>
> a) (2,3,2)
> b) (2,4,6)
> c) (4,2,6)
> d) (3,2,6)[/color]

Easy to work out, actually. As an example, I'll show you why the answer
isn't a. You can then work out the three remaining options.

Each row in the output that shows 2 as the first value has a1.x=2. This
must stem from the row (2, 3), as that is the only row with an x value of
2. The join condition (a1.y=a2.x) means that the a2 row must have an x
value of 3 (as the y value in the a1 row is 3). Two rows qualify: (3, 4)
and (3, 4). Both have an y value of 4, so before grouping, there are 2
rows with a1.x=2 and a2.y=4. After grouping, this is 1 group with a row
count of 2. The result set should contain (2, 4, 2) as the only row
starting with 2.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)