SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
USE master GO DROP DATABASE IF EXISTS one_hundred_albums GO CREATE DATABASE one_hundred_albums GO USE one_hundred_albums GO CREATE TABLE album( artist_id smallint NOT NULL, album_name varchar(100) NOT NULL, release_date date NOT NULL, length smallint NULL, producer_id smallint NULL, record_label varchar(50) NULL, number_of_sales integer, sales_value decimal(12,2) CONSTRAINT album_pk PRIMARY KEY ( artist_id ASC, album_name ASC ) ) GO CREATE TABLE album_track( artist_id smallint NOT NULL, album_name varchar(100) NOT NULL, track_name varchar(100) NOT NULL, track_order tinyint NULL, CONSTRAINT album_track_pk PRIMARY KEY ( artist_id ASC, album_name ASC, track_name ASC ) ) GO CREATE TABLE artist( artist_id smallint IDENTITY(1,1) NOT NULL, name varchar(100) NOT NULL, royalty decimal(3,2) NOT NULL, PRIMARY KEY CLUSTERED ( artist_id ASC ) ) GO CREATE TABLE genre( artist_id smallint NOT NULL, album_name varchar(100) NOT NULL, genre varchar(50) NOT NULL, CONSTRAINT genre_pk PRIMARY KEY ( artist_id ASC, album_name ASC, genre ASC ) ) GO CREATE TABLE producer( producer_id smallint IDENTITY(1,1) NOT NULL, name varchar(100) NULL, PRIMARY KEY CLUSTERED ( producer_id ASC ) ) GO CREATE TABLE record_label ( name VARCHAR(50) PRIMARY KEY, country VARCHAR(50) NOT NULL ) GO INSERT INTO album (artist_id, album_name, release_date, length, producer_id, record_label, number_of_sales, sales_value) VALUES (1, 'Meddle', CAST('1971-10-31' AS DATE), 46, 1, 'Harvest', 3138000, 39225000.00), (2, 'OK', CAST('1998-11-09' AS DATE), 60, 2, 'Island', 60000, 990000.00), (1, 'Animals', CAST('1977-01-21' AS DATE), 41, 1, 'Harvest',6531000,97965000.00), (2, 'Ha', CAST('2001-04-04' AS DATE), 73, 2, 'Island', 45000, 675000.00), (1,'Wish You Were Here', CAST('12/09/1975' AS DATE),44,5,'Harvest', 23000000, 339250000.00), (1,'Obscured by Clouds', CAST('2/06/1972' AS DATE),40,5,'Harvest', 785000, 7850000.00) GO INSERT INTO album_track (artist_id, album_name, track_name, track_order) VALUES (1, 'Meddle', 'A Pillow of Winds', 2), (1, 'Meddle', 'Echoes', 6), (1, 'Meddle', 'Fearless', 3), (1, 'Meddle', 'One of These Days', 1), (1, 'Meddle', 'San Tropez', 4), (1, 'Meddle', 'Seamus', 5), (2, 'OK', 'Butterfly', 2), (2, 'OK', 'Decca', 5), (2, 'OK', 'Eclipse', 6), (2, 'OK', 'Light', 8), (2, 'OK', 'Mombasstic', 4), (2, 'OK', 'OK', 7), (2, 'OK', 'Sutrix', 3), (2, 'OK', 'Traveller', 1), (1, 'Animals', 'Pigs on the Wing (Part One)', 1), (1, 'Animals', 'Dogs', 2), (1, 'Animals', 'Pigs (Three Different Ones)', 3), (1, 'Animals', 'Sheep', 4), (1, 'Animals', 'Pigs on the Wing (Part Two)', 5), (1,'Wish You Were Here','Shine On You Crazy Diamond (Parts I-V)',1), (1,'Wish You Were Here','Welcome to the Machine',2), (1,'Wish You Were Here','Have a Cigar',3), (1,'Wish You Were Here','Wish You Were Here',4), (1,'Wish You Were Here','Shine On You Crazy Diamond (Parts VI-IX)',5), (1,'Obscured by Clouds','Obscured by Clouds',1), (1,'Obscured by Clouds','When You''re In',2), (1,'Obscured by Clouds','Burning Bridges',3), (1,'Obscured by Clouds','The Gold It''s in the...',4), (1,'Obscured by Clouds','Wot''s... Uh the Deal?',5), (1,'Obscured by Clouds','Mudmen',6), (1,'Obscured by Clouds','Childhood''s End',7), (1,'Obscured by Clouds','Free Four',8), (1,'Obscured by Clouds','Stay',9), (1,'Obscured by Clouds','Absolutely Curtains',10) GO SET IDENTITY_INSERT artist ON GO INSERT INTO artist (artist_id, name, royalty) VALUES (1, 'Pink Floyd', 0.25), (2, 'Talvin Singh', 0.2), (3, 'Margaret Urlich', 0.2), (4, 'Trinity Roots', 0.18) GO SET IDENTITY_INSERT artist OFF GO INSERT INTO genre (artist_id, album_name, genre) VALUES (1, 'Meddle', 'Progressive Rock'), (1, 'Meddle', 'Rock'), (2, 'OK', 'Electronica'), (1, 'Animals', 'Rock') GO SET IDENTITY_INSERT producer ON GO INSERT INTO producer (producer_id, name) VALUES (1, 'Roger Waters'), (2, 'Talvin Singh'), (3, 'Laurie Anderson'), (4, 'Villette'), (5, 'Pink Floyd') GO SET IDENTITY_INSERT producer OFF GO INSERT INTO record_label VALUES ('Harvest', 'UK'), ('Island', 'UK'), ('Sony', 'Japan'), ('Flying Nun Records', 'New Zealand') GO ALTER TABLE album WITH CHECK ADD FOREIGN KEY(artist_id) REFERENCES artist (artist_id) GO ALTER TABLE album WITH CHECK ADD FOREIGN KEY(producer_id) REFERENCES producer (producer_id) GO ALTER TABLE album_track WITH CHECK ADD CONSTRAINT album_track_album_fk FOREIGN KEY(artist_id, album_name) REFERENCES album (artist_id, album_name) GO ALTER TABLE album_track CHECK CONSTRAINT album_track_album_fk GO ALTER TABLE genre WITH CHECK ADD CONSTRAINT genre_album_fk FOREIGN KEY(artist_id, album_name) REFERENCES album (artist_id, album_name) GO ALTER TABLE genre CHECK CONSTRAINT genre_album_fk GO ALTER TABLE album ADD CONSTRAINT album_record_label_fk FOREIGN KEY (record_label) REFERENCES record_label(name) GO
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear