Exercise 4

Solution Notes

SQL> set feedback 1
SQL> set pagesize 45
SQL> set echo on
SQL> @qmax
SQL> -- cae Sept 2006
SQL> -- COMP2400
SQL> 
SQL> -- (using the sql function max)
SQL> 
SQL> -- Which musician has played the most gigs?
SQL> 
SQL> ttitle "The musician(s) who played the most gigs"
SQL> 
SQL> select Family_Name, Given_Name
  2  from musician
  3  where  Musician_Id in
  4       (select Musician_Id
  5        from gig
  6        group by Musician_Id
  7        having count(*) =
  8                       (select max(count(*))
  9                        from gig
 10                        group by Musician_Id
 11                       )
 12       );

Sat Sep 30                                                             page    1
                    The musician(s) who played the most gigs

FAMILY_NAME          GIVEN_NAME
-------------------- --------------------
MCCARTNEY            Paul

1 row selected.

SQL> 
SQL> ttitle off
SQL> 
SQL> @qmax2
SQL> -- Carol Edmondson 18-09-2006
SQL> -- COMP6240
SQL> 
SQL> -- (using the sql function max)
SQL> 
SQL> -- Which musician has played the most gigs?
SQL> 
SQL> ttitle "The musician(s) who played the most gigs"
SQL> 
SQL> select m.Musician_Id, m.Family_Name, m.Given_Name, count(*) as "GIGS"
  2  from musician m, gig g
  3  where  (m.Musician_Id = g.Musician_Id)     /* join condition */
  4  group by m.Musician_Id, m.Family_Name, m.Given_Name
  5  having count(*) =
  6                (select max(count(*))
  7                 from gig
  8                 group by Musician_Id);

Sat Sep 30                                                             page    1
                    The musician(s) who played the most gigs

MUSICIAN_ID FAMILY_NAME          GIVEN_NAME                 GIGS
----------- -------------------- -------------------- ----------
          3 MCCARTNEY            Paul                          7

1 row selected.

SQL> 
SQL> ttitle off
SQL> 

[Top]