set pagesize 5000

--------------------------
--DROP TABLES
--------------------------
DROP TABLE Track;
DROP TABLE Album;
DROP TABLE Artist;
DROP TABLE Genre;
DROP TABLE MediaType;
--------------------------
--Create Tables
--------------------------
--Artist Table
CREATE Table ARTIST
(
    ArtistID Number (3,0) NOT NULL
    , Name VARCHAR2 (120) NOT NULL
);

--ALBUM Table
CREATE Table ALBUM
(
    AlbumId Number (3,0) NOT NULL
    , Title VARCHAR2 (160) NOT NULL
    , ArtistID Number (3,0) NOT NULL
);

--Genre Table
CREATE Table Genre
(
    GenreID Number(3,0) NOT NULL
    , Name VARCHAR2 (120) NOT NULL
);

--MediaType
CREATE Table MediaType
(
    MediaTypeID Number(3,0) NOT NULL
    , Name VARCHAR2 (120) NOT NULL
);

--Track
CREATE Table Track
(
    TrackID Number (4,0) NOT NULL
    ,Name VARCHAR2 (200) Not NULL
    ,AlbumID Number (3,0) 
    ,MediaTypeID Number (3,0) NOT NULL
    ,GenreID Number (3,0)
    ,Composer VARCHAR2 (220)
    ,Milliseconds Number (10,0)
    ,Bytes Number (10,0)
    ,UnitPrice Number (10,2)
);

--------------------------
--Create Primary Keys
--------------------------
--PK Artist
ALTER TABLE Artist
ADD Constraint PK_ArtistID
PRIMARY KEY(ArtistID);

--PK Album
ALTER TABLE Album
ADD Constraint PK_AlbumID
PRIMARY KEY(AlbumID);

--PK GenreID
ALTER TABLE Genre
ADD Constraint PK_GenreID
PRIMARY KEY(GenreID);

--PK MediaType
ALTER TABLE MediaType
ADD Constraint PK_MediaTypeID
PRIMARY KEY(MediaTypeID);

--PK TrackID
ALTER TABLE Track
ADD Constraint PK_TrackID
PRIMARY KEY(TrackID);

--------------------------
--Create Foreign Keys
--------------------------
--Track AlbumID
ALTER TABLE Track
ADD CONSTRAINT FK_Track_AlbumID
FOREIGN KEY (AlbumID) REFERENCES Album (AlbumID);

--Track MediaTypeID
ALTER TABLE Track
ADD CONSTRAINT FK_Track_MediaTypeId
FOREIGN KEY (MediaTypeID) REFERENCES MediaType (MediaTypeID);

--Track GenreID
ALTER TABLE Track
ADD CONSTRAINT FK_Track_GenreID
FOREIGN KEY (GenreID) REFERENCES Genre (GenreID);

--Album ArtistID
ALTER TABLE Album
ADD CONSTRAINT FK_Album_ArtistID
FOREIGN KEY (ArtistID) REFERENCES Artist (ArtistID);
------------------------------------------------------------------------------
INSERT INTO Genre
(GenreID, Name)
VALUES
(1, 'Rock');

INSERT INTO Genre
(GenreID, Name)
VALUES
(2, 'Jazz');

INSERT INTO Genre
(GenreID, Name)
VALUES
(3, 'Metal');

INSERT INTO Genre
(GenreID, Name)
VALUES
(4, 'Alternative Punk');

INSERT INTO Genre
(GenreID, Name)
VALUES
(5, 'Rock and Roll');

INSERT INTO Genre
(GenreID, Name)
VALUES
(6, 'Blues');

INSERT INTO Genre
(GenreID, Name)
VALUES
(7, 'Latin');

INSERT INTO Genre
(GenreID, Name)
VALUES
(8, 'Reggae');

INSERT INTO Genre
(GenreID, Name)
VALUES
(9, 'Pop');

INSERT INTO Genre
(GenreID, Name)
VALUES
(10, 'Soundtrack');

INSERT INTO Genre
(GenreID, Name)
VALUES
(11, 'Bossa Nova');

INSERT INTO Genre
(GenreID, Name)
VALUES
(12, 'Easy Listening');

INSERT INTO Genre
(GenreID, Name)
VALUES
(13, 'Heavy Metal');

INSERT INTO Genre
(GenreID, Name)
VALUES
(14, 'R '||CHR(38)||' B Soul');

INSERT INTO Genre
(GenreID, Name)
VALUES
(15, 'Electronic Dance');

INSERT INTO Genre
(GenreID, Name)
VALUES
(16, 'World');

INSERT INTO Genre
(GenreID, Name)
VALUES
(17, 'Hip Hop Rap');

INSERT INTO Genre
(GenreID, Name)
VALUES
(18, 'Science Fiction');

INSERT INTO Genre
(GenreID, Name)
VALUES
(19, 'TV Shows');

INSERT INTO Genre
(GenreID, Name)
VALUES
(20, 'Sci Fi and Fantasy');

INSERT INTO Genre
(GenreID, Name)
VALUES
(21, 'Drama');

INSERT INTO Genre
(GenreID, Name)
VALUES
(22, 'Comedy');

INSERT INTO Genre
(GenreID, Name)
VALUES
(23, 'Alternative');

INSERT INTO Genre
(GenreID, Name)
VALUES
(24, 'Classical');

INSERT INTO Genre
(GenreID, Name)
VALUES
(25, 'Opera');

------------------------------------------------------------------------------
INSERT INTO MediaType
(MediaTypeId, Name)
VALUES 
(1, 'MPEG audio file');

INSERT INTO MediaType
(MediaTypeId, Name)
VALUES 
(2, 'Protected AAC audio file');

INSERT INTO MediaType
(MediaTypeId, Name)
VALUES 
(3, 'Protected MPEG4 video file');

INSERT INTO MediaType
(MediaTypeId, Name)
VALUES 
(4, 'Purchased AAC audio file');

INSERT INTO MediaType
(MediaTypeId, Name)
VALUES 
(5, 'AAC audio file');

------------------------------------------------------------------------------
INSERT INTO Artist
(ArtistID, Name)
VALUES
(1, 'AC DC');

INSERT INTO Artist
(ArtistID, Name)
VALUES
(2, 'Accept');

INSERT INTO Artist
(ArtistID, Name)
VALUES
(3, 'Aerosmith');

INSERT INTO Artist
(ArtistID, Name)
VALUES
(4, 'Alanis Morissette');

INSERT INTO Artist
(ArtistID, Name)
VALUES
(5, 'Alice In Chains');

INSERT INTO Artist
(ArtistID, Name)
VALUES
(6, 'Antonio Carlos Jobim');

INSERT INTO Artist
(ArtistID, Name)
VALUES
(7, 'Apocalyptica');

INSERT INTO Artist
(ArtistID, Name)
VALUES
(8, 'Audioslave');

INSERT INTO Artist
(ArtistID, Name)
VALUES
(9, 'BackBeat');

INSERT INTO Artist
(ArtistID, Name)
VALUES
(10, 'Billy Cobham');

INSERT INTO Artist
(ArtistID, Name)
VALUES
(11, 'Black Label Society');

INSERT INTO Artist
(ArtistID, Name)
VALUES
(12, 'Black Sabbath');

------------------------------------------------------------------------------
INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(1, 'For Those About To Rock We Salute You', 1);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(2, 'Balls to the Wall', 2);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(3, 'Restless and Wild', 2);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(4, 'Let There Be Rock', 1);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(5, 'Big Ones', 3);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(6, 'Jagged Little Pill', 4);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(7, 'Facelift', 5);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(8, 'Warner 25 Anos 6', 6);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(9, 'Plays Metallica By Four Cellos', 7);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(10, 'Audioslave', 8);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(11, 'Out Of Exile', 8);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(12, 'BackBeat Soundtrack', 9);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(13, 'The Best of Billy Cobham', 10);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(14, 'Alcohol Fueled Brewtality Live! [Disc 1]', 11);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(15, 'Alcohol Fueled Brewtality Live! [Disc 2]', 11);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(16, 'Black Sabbath', 12);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(17, 'Black Sabbath Vol. 4 (Remaster)', 12);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(34, 'Chill: Brazil (Disc 2)', 6);

INSERT INTO Album
(AlbumID, Title, ArtistID)
VALUES
(271, 'Revelations', 8);

------------------------------------------------------------------------------
INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(1,	'For Those About To Rock (We Salute You)',	1,	1,	1,	'Angus Young, Malcolm Young, Brian Johnson',	343719,	11170334,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(6,	'Put The Finger On You',	1	,1	,1	,'Angus Young, Malcolm Young, Brian Johnson',	205662,	6713451	,0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(2,	'Balls to the Wall',	2,	2,	1, NULL, 342562,	5510424,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(3,	'Fast As a Shark',	3,	2,	1,	'F. Baltes, S. Kaufman, U. Dirkscneider and W. Hoffman',	230619,	3990994, 0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(4,	'Restless and Wild',3,	2,	1,	'F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider and W. Hoffman',	252051,	4331779,0.99);


INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(4,	'Restless and Wild',3,	2,	1,	'F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider and W. Hoffman',	252051,	4331779,0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(16,	'Dog Eat Dog',	4,	1,	1,	'AC DC',	215196,	7032162,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(17,	'Let There Be Rock',	4	,1,	1,	'AC DC',	366654,	12021261,0.99);


INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(23, 'Walk On Water',	5,	1,	1,	'Steven Tyler, Joe Perry, Jack Blades, Tommy Shaw',	295680,	9719579	,0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(24, 'Love In An Elevator',	5,	1,	1,	'Steven Tyler, Joe Perry',	321828	,10552051,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(38, 'All I Really Want',	6,	1	,1	,'Alanis Morissette and Glenn Ballard',	284891,	9375567	,0.99);


INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(39,	'You Oughta Know',	6,	1,	1,	'Alanis Morissette and Glenn Ballard',	249234,	8196916,	0.99);


INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(40,	'Perfect',	6,	1,	1,	'Alanis Morissette and Glenn Ballard',	188133,	6145404,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(51,	'We Die Young',	7,	1,	1,	'Jerry Cantrell',	152084,	4925362,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(52,	'Man In The Box',	7,	1,	1,	'Jerry Cantrell, Layne Staley',	286641,	9310272,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(63,	'Desafinado',	8,	1,	2, NULL,	185338,	5990473,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(64,	'Garota De Ipanema',	8,	1,	2	, NULL,	285048	,9348428,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(77,	'Enter Sandman',	9,	1,	3,	'Apocalyptica',	221701	,7286305,	0.99);


INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(78,	'Master Of Puppets',	9,	1,	3,	'Apocalyptica',	436453,	14375310,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(85,	'Cochise',	10,	1,	1,	'Audioslave Chris Cornell',	222380,	5339931,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(86,	'Show Me How to Live',	10,	1,	1,	'Audioslave Chris Cornell',	277890,	6672176,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(99,	'Your Time Has Come',	11	,1,	4,	'Cornell, Commerford, Morello, Wilk',	255529,	8273592,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(100,	'Out Of Exile',	11,	1,	4,	'Cornell, Commerford, Morello, Wilk',	291291,	9506571,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(111,	'Money',	12,	1,	5	,'Berry Gordy, Jr. Janie Bradford',	147591,	2365897	,0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(112,	'Long Tall Sally',	12,	1,	5,	'Enotris Johnson Little Richard Robert "Bumps" Blackwell',	106396,	1707084,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(123,	'Quadrant',	13,	1,	2,	'Billy Cobham',	261851,	8538199,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(124,	'Snoopy''s search-Red baron',	13,	1,	2,	'Billy Cobham',	456071,	15075616,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(131,	'Intro/ Low Down',	14,	1,	3, NULL,	323683,	10642901,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(132,	'13 Years Of Grief',	14,	1,	3, NULL,	246987,	8137421	,0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(144, 'Heart Of Gold',	15,	1,	3	, NULL,	194873,	6417460,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(145,	'Snowblind',	15	,1	,3, NULL,	420022,	13842549,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(149,	'Black Sabbath',	16,	1,	3, NULL,		382066,	12440200,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(150,	'The Wizard',	16,	1,	3, NULL,		264829,	8646737,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(156,	'Wheels Of Confusion The Straightener',	17,	1,	3,	'Tony Iommi, Bill Ward, Geezer Butler, Ozzy Osbourne',	494524,	16065830,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(157,	'Tomorrow''s Dream',	17,	1,	3,	'Tony Iommi, Bill Ward, Geezer Butler, Ozzy Osbourne',	192496,	625207,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(403,	'Bumbo Da Mangueira',	34,	1	,7,	'Various',	270158,	9073350,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(404,	'Mr Funk Samba',	34,	1,	7,	'Various',	213890,	7102545,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(3389,	'Revelations',	271,	2,	3, NULL,		252376,	4111051,	0.99);

INSERT INTO Track
(TrackID, Name, AlbumID, MediaTypeID, GenreID, Composer, Milliseconds, Bytes, UnitPrice)
VALUES
(3390,	'One and the Same',	271,	2,	3, NULL,	217732,	3559040,	0.99);