/* Select the average song length for each album */

SELECT OnAlbum1.groupName, OnAlbum1.albumName, AVG(length)
FROM OnAlbum1, Song1
WHERE OnAlbum1.groupName <='artistaaab' AND OnAlbum1.groupName = Song1.groupName AND Song1.songName = OnAlbum1.songName
GROUP BY OnAlbum1.groupName, OnAlbum1.albumName;

Results:

GROUPNAME
--------------------------------------------------------------------------------
ALBUMNAME
--------------------------------------------------------------------------------
AVG(LENGTH)
-----------
artistaaaa
albumaaaa
        373

artistaaab
albumaaab
      174.3

GROUPNAME
--------------------------------------------------------------------------------
ALBUMNAME
--------------------------------------------------------------------------------
AVG(LENGTH)
-----------

artistaaab
albumaaac
      257.2

artistaaab
albumaaad

GROUPNAME
--------------------------------------------------------------------------------
ALBUMNAME
--------------------------------------------------------------------------------
AVG(LENGTH)
-----------
      168.6

artistaaab
albumaaae
      217.1

artistaaab

GROUPNAME
--------------------------------------------------------------------------------
ALBUMNAME
--------------------------------------------------------------------------------
AVG(LENGTH)
-----------
albumaaaf
      240.2


6 rows selected.

Elapsed: 00:00:00.02


/* Select all tab modified after January 1, 2000 */

SELECT url, modified
FROM Tablature1
WHERE modified >= '01-JAN-00'
ORDER BY modified;

Results:
URL
--------------------------------------------------------------------------------
MODIFIED
---------
main/m/mahavishnu_orchestra/miles_beyond.tab
31-MAR-00

main/d/daniele_pino/ninnananinnannoe.crd
13-JUN-00

main/c/cranberries/fee_fi_fo.crd
30-OCT-00


URL
--------------------------------------------------------------------------------
MODIFIED
---------
main/a/arena_tina/aller_plus_haut.tab
09-NOV-00

main/k/kottke_leo/piece_17.tab
16-NOV-00

main/i/iron_maiden/iron_maiden.tab
15-DEC-00


6 rows selected.

Elapsed: 00:00:00.02


/* Selects sets of groups that have a song with the same name */

SELECT Song1.groupName, Song2.groupName, Song1.songName
FROM Song1, Song1 Song2
WHERE Song1.songName = Song2.songName AND Song1.groupName <Song2.GroupName;

Results:
no rows selected

Elapsed: 00:01:08.09


/* Selects the number of members in each group */

SELECT InGroup1.groupName, COUNT(name) AS NumMembers
FROM Musician1, InGroup1
WHERE name <= 'bill aabb' AND name = InGroup1.musicianName
GROUP BY (InGroup1.groupName);

Results:
GROUPNAME
--------------------------------------------------------------------------------
NUMMEMBERS
----------
artistaaaa
         1

artistaaab
         5

artistaaac
         5


GROUPNAME
--------------------------------------------------------------------------------
NUMMEMBERS
----------
artistaaad
         1


Elapsed: 00:00:00.08


/* Associate a musician with his/her record company */

SELECT DISTINCT MusicianOnAlbum.name, ProducedByRC.name
FROM
	(SELECT name, albumName
	FROM Musician1, OnAlbum1
	WHERE groupName IN
	      (SELECT groupName
	      FROM InGroup1
	      WHERE musicianName = name)) MusicianOnAlbum,
	(SELECT name, albumName
	FROM RecordCompany1, ProducedBy1
	WHERE name = companyName) ProducedByRC
WHERE MusicianOnAlbum.name <= 'bill aabb' AND MusicianOnAlbum.albumName = ProducedByRC.albumName;

Results:
NAME
--------------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
bill aaaa
rc aaaa

bill aaab
rc aaab

bill aaac
rc aaab


NAME
--------------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
bill aaad
rc aaab

bill aaae
rc aaab

bill aaaf
rc aaab


NAME
--------------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
bill aaag
rc aaab

bill aaah
rc aaab

bill aaai
rc aaab


NAME
--------------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
bill aaaj
rc aaab

bill aaba
rc aaab

bill aabb
rc aaab


12 rows selected.

Elapsed: 00:00:09.39