Cname = ClubMembership.Cname
GROUP BY Club.Cname
ORDER BY Club.Cname;
Here a left join on Club and ClubMembership insures that all clubs are included in the report, even if
the clubs have no members (no entries in ClubMembership). Also, since Cname is a column heading in both
tables, each reference to Cname must also specify which table is being referenced. This query says to execute
an outer join on Club and ClubMembership so that all clubs are included, and then count the occurrences
of records for each club by grouping on club names.
CHAP. 8] DATABASE 153
If one wanted to report the revenues for each of the clubs, this query using the SUM function would work:
SELECT CM.Cname, SUM( C.Dues ) AS Revenue
FROM ClubMembership CM JOIN Club C
ON CM.Cname = C.Cname
GROUP BY CM.Cname;
This statement introduces the use of aliases for table names. In the FROM clause one may follow the name
of the table with an abbreviation. In that case, the abbreviation may be used wherever the table name would
otherwise be required, even in the beginning of the SELECT clause before the FROM clause is encountered!
Most experienced SQL people make extensive use of table aliases to reduce the size of SQL statements and
make the statements easier to read.
Pages:
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425