Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the creation of a small database for CD album information.

USE AdventureWorks;

CREATE TABLE cdArtist (

  cdArtistID INTEGER    IDENTITY ( 1 , 1 )    PRIMARY KEY,

  Name       VARCHAR(50)    NOT NULL,

  [DateTime] SMALLDATETIME    DEFAULT (getdate()));

CREATE TABLE cdTitle (

  cdTitleID  INTEGER    IDENTITY ( 1 , 1 )    PRIMARY KEY,

  cdArtistID INTEGER    NOT NULL     REFERENCES cdArtist(cdArtistID),

  Title      VARCHAR(100)    NOT NULL,

  [DateTime] SMALLDATETIME    DEFAULT (getdate()));

CREATE TABLE cdTrack (

  cdTrackID  INTEGER    IDENTITY ( 1 , 1 )    PRIMARY KEY,

  cdTitleID  INTEGER    NOT NULL     REFERENCES cdTitle(cdTitleID),

  SongTitle      VARCHAR(100)    NOT NULL,

  TrackNo    INTEGER    NOT NULL,

  [DateTime] SMALLDATETIME    DEFAULT (getdate()));

GO

-- Populate cdArtist

insert cdArtist(Name) select 'Muse'

insert cdArtist(Name) select 'Snow Patrol'

insert cdArtist(Name) select 'Red Hot Chili Peppers'

insert cdArtist(Name) select 'Lostprophets'

insert cdArtist(Name) select 'Razorlight'

insert cdArtist(Name) select 'Keane'

insert cdArtist(Name) select 'Allen, Lily'

insert cdArtist(Name) select 'Simone, Nina'

insert cdArtist(Name) select 'Fatboy Slim'

insert cdArtist(Name) select 'Farka Toure, Ali'

insert cdArtist(Name) select 'Yorke, Thom'

insert cdArtist(Name) select 'Pink Floyd'

insert cdArtist(Name) select 'Rihanna'

insert cdArtist(Name) select 'Thom, Sandi'

insert cdArtist(Name) select 'Lamontagne, Ray'

insert cdArtist(Name) select 'Simon, Paul'

insert cdArtist(Name) select 'Nutini, Paolo'

insert cdArtist(Name) select 'Feeder'

insert cdArtist(Name) select 'Feeling'

insert cdArtist(Name) select 'Pussycat Dolls'

insert cdArtist(Name) select 'Morrison, James'

insert cdArtist(Name) select 'Zero'

insert cdArtist(Name) select 'Furtado, Nelly'

insert cdArtist(Name) select 'Raconteurs'

insert cdArtist(Name) select 'Morrissey'

insert cdArtist(Name) select 'Spektor, Regina'

insert cdArtist(Name) select 'Dixie Chicks'

insert cdArtist(Name) select 'Pipettes'

insert cdArtist(Name) select 'Automatic'

insert cdArtist(Name) select 'Springsteen, Bruce'

insert cdArtist(Name) select 'Beautiful South'

insert cdArtist(Name) select 'Shakira'

insert cdArtist(Name) select 'Mendes, Sergio'

insert cdArtist(Name) select 'Jackson, Jack'

insert cdArtist(Name) select 'Killers'

insert cdArtist(Name) select 'Flaming Lips'

insert cdArtist(Name) select 'Rolling Stones'

insert cdArtist(Name) select 'Gnarls Barkley'

insert cdArtist(Name) select 'Dirty Pretty Things'

insert cdArtist(Name) select 'Kooks'

insert cdArtist(Name) select 'Zutons'

insert cdArtist(Name) select 'Massive Attack'

insert cdArtist(Name) select 'Streets'

insert cdArtist(Name) select 'Johnson, Jack'

insert cdArtist(Name) select 'Rae, Corinne Bailey'

insert cdArtist(Name) select 'Embrace'

insert cdArtist(Name) select 'Hot Chip'

insert cdArtist(Name) select 'Arctic Monkeys'

insert cdArtist(Name) select 'Knopfler, Mark & Emmylou Harris'

insert cdArtist(Name) select 'Guns n'' Roses'

go

SELECT * FROM cdArtist

go

/* Partial results

cdArtistID  Name                    DateTime

1           Muse                    2015-02-21 13:02:00

2           Snow Patrol             2015-02-21 13:02:00

3           Red Hot Chili Peppers   2015-02-21 13:02:00

4           Lostprophets            2015-02-21 13:02:00

*/

-- Populate cdTitle table

INSERT cdTitle (cdArtistID, Title)

VALUES (3, 'Maximum Red Hot Chili Peppers')

INSERT cdTitle (cdArtistID, Title)

VALUES (3, 'Mother''s Milk')

SELECT * FROM cdTitle

GO

/* Results

cdTitleID   cdArtistID  Title                         DateTime

1           3           Maximum Red Hot Chili Peppers 2015-02-21 13:07:00

2           3           Mother's Milk                 2015-02-21 13:07:00

*/

-- Populate the cdTrack table

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2, 1, 'Good Time Boys')  

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2, 2, 'Higher Ground')  

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  3, 'Subway to Venus')  

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  4, 'Magic Johnson ') 

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  5, 'Nobody Weird Like Me')  

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  6, 'Knock Me Down')  

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  7, 'Taste the Pain ') 

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  8, 'Stone Cold Bush ') 

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  9, 'Fire')

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  10, 'Pretty Little Ditty')

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  11, 'Punk Rock Classic ') 

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  12, 'S*xy Mexican Maid')  

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  13, 'Johnny, Kick a Hole in the Sky ')

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  14, 'Song That Made Us What We Are Today - (demo)')

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  15, 'Knock Me Down - ( original long version)')  

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  16, 'S*xy Mexican Maid - ( original long version)')  

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  17, 'Salute to Kareem - ( demo)')

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  18, 'Castles Made of Sand - (live, 11/21/89) ')

INSERT cdTrack (cdTitleID, TrackNo, SongTitle) VALUES ( 2,  19, 'Crosstown Traffic - ( live, 11/21/89)')

SELECT * FROM cdTrack

Go

-- Results

-- SQL inner join

SELECT   a.Name       AS Artist,

         t.Title      AS Album,

         tr.SongTitle AS Song,

         tr.TrackNo   AS Track

FROM     cdArtist a

         INNER JOIN cdTitle t

           ON a.cdArtistID = t.cdArtistID

         INNER JOIN cdTrack tr

           ON t.cdTitleID = tr.cdTitleID

ORDER BY Artist,

         Album,

         Track

GO

/* Results

Artist      Album Song  Track

Red Hot Chili Peppers   Mother's Milk     Good Time Boys    1

Red Hot Chili Peppers   Mother's Milk     Higher Ground     2

Red Hot Chili Peppers   Mother's Milk     Subway to Venus   3

Red Hot Chili Peppers   Mother's Milk     Magic Johnson     4

Red Hot Chili Peppers   Mother's Milk     Nobody Weird Like Me    5

Red Hot Chili Peppers   Mother's Milk     Knock Me Down     6

Red Hot Chili Peppers   Mother's Milk     Taste the Pain    7

Red Hot Chili Peppers   Mother's Milk     Stone Cold Bush   8

Red Hot Chili Peppers   Mother's Milk     Fire  9

Red Hot Chili Peppers   Mother's Milk     Pretty Little Ditty     10

Red Hot Chili Peppers   Mother's Milk     Punk Rock Classic       11

Red Hot Chili Peppers   Mother's Milk     S*xy Mexican Maid 12

Red Hot Chili Peppers   Mother's Milk     Johnny, Kick a Hole in the Sky      13

Red Hot Chili Peppers   Mother's Milk     Song That Made Us What We Are Today - (demo)    14

Red Hot Chili Peppers   Mother's Milk     Knock Me Down - ( original long version)  15

Red Hot Chili Peppers   Mother's Milk     S*xy Mexican Maid - ( original long version)    16

Red Hot Chili Peppers   Mother's Milk     Salute to Kareem - ( demo)    17

Red Hot Chili Peppers   Mother's Milk     Castles Made of Sand - (live, 11/21/89)   18

Red Hot Chili Peppers   Mother's Milk     Crosstown Traffic - ( live, 11/21/89)     19

*/

-- Cleanup

drop table cdArtist

drop table cdTitle

drop table cdTrack

go