SELECT MIN(albumCount) FROM (SELECT groupName, COUNT(albumName) AS albumCount FROM Album1 GROUP BY groupName); MIN(ALBUMCOUNT) --------------- 1 Elapsed: 00:00:00.03 1 DECLARE 2 3 /* this script removes any instances of albums and artists where 4 the artist has put out at least an album, but less than all the 5 artists. we wouldn't want an unsuccessul bands in our db, would 6 we? :) */ 7 8 minAlbums NUMBER; 9 numAlbums NUMBER; 10 currentGroup VARCHAR(255); 11 CURSOR albumCursor IS 12 SELECT groupName 13 FROM album1 14 FOR UPDATE; 15 BEGIN 16 SELECT MIN(albumCount) INTO minAlbums 17 FROM 18 (SELECT COUNT(albumName) AS albumCount 19 FROM album1 20 GROUP BY groupName); 21 22 OPEN albumCursor; 23 LOOP 24 FETCH albumCursor INTO currentGroup; 25 EXIT WHEN albumCursor%NOTFOUND; 26 27 SELECT COUNT(*) INTO numAlbums 28 FROM Album1 29 WHERE groupName=currentGroup; 30 31 IF numAlbums <= minAlbums THEN 32 DELETE FROM album1 WHERE CURRENT OF albumCursor; 33 DELETE FROM artist1 WHERE name=currentGroup; 34 DELETE FROM song1 WHERE groupName=currentGroup; 35 DELETE FROM ingroup1 WHERE groupName=currentGroup; 36 DELETE FROM guestartist1 WHERE groupName=currentGroup; 37 DELETE FROM influences1 WHERE groupName=currentGroup; 38 DELETE FROM onAlbum1 WHERE groupName=currentGroup; 39 DELETE FROM producedBy1 WHERE groupName=currentGroup; 40 DELETE FROM TabFor1 WHERE groupName=currentGroup; 41 END IF; 42 43 END LOOP; 44 CLOSE albumCursor; 45* END; PL/SQL procedure successfully completed. Elapsed: 00:00:03.93 SELECT MIN(albumCount) FROM (SELECT groupName, COUNT(albumName) AS albumCount FROM album1 GROUP BY groupName); MIN(ALBUMCOUNT) --------------- 2 Elapsed: 00:00:00.03 ROLLBACK; Rollback complete. Elapsed: 00:00:01.88 INSERT INTO Transcriber VALUES ('senor taco', 'taco@foo.com'); 1 row created. Elapsed: 00:00:00.01 INSERT INTO Transcriber VALUES ('s-dawg', 's@dawg.com'); 1 row created. Elapsed: 00:00:00.00 SELECT * FROM Transcriber; TRANSCRIBER -------------------------------------------------------------------------------- EMAIL -------------------------------------------------------------------------------- Bobby Jones bobby@jones.com Sarah Kitchell sarah@kitchell.com Bill Smith bill@smith.com TRANSCRIBER -------------------------------------------------------------------------------- EMAIL -------------------------------------------------------------------------------- Kate Digrigorio kate@dg.com Steve van Loben Sels sevls@leland.stanford.edu s-dawg s@dawg.com TRANSCRIBER -------------------------------------------------------------------------------- EMAIL -------------------------------------------------------------------------------- senor taco taco@foo.com 7 rows selected. Elapsed: 00:00:00.01 1 DECLARE 2 3 /* this script removes any instances of transcribers who 4 have not created any tablature */ 5 6 currTrans Transcriber.transcriber%TYPE; 7 CURSOR curs IS 8 SELECT transcriber 9 FROM Transcriber 10 WHERE transcriber NOT IN 11 (SELECT transcriber 12 FROM Tablature) 13 FOR UPDATE; 14 BEGIN 15 OPEN curs; 16 LOOP 17 FETCH curs INTO currTrans; 18 EXIT WHEN curs%NOTFOUND; 19 DELETE FROM Transcriber WHERE CURRENT OF curs; 20 END LOOP; 21* END; PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SELECT * FROM Transcriber; TRANSCRIBER -------------------------------------------------------------------------------- EMAIL -------------------------------------------------------------------------------- Bobby Jones bobby@jones.com Sarah Kitchell sarah@kitchell.com Bill Smith bill@smith.com TRANSCRIBER -------------------------------------------------------------------------------- EMAIL -------------------------------------------------------------------------------- Kate Digrigorio kate@dg.com Steve van Loben Sels sevls@leland.stanford.edu Elapsed: 00:00:00.01