DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to design tables for a CD collection?

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 UNIQUE,

  [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 UNIQUE,

  [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 UNIQUE,

  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

 

cdTrackID cdTitleID SongTitle TrackNo DateTime
1 2 Good Time Boys 1 2/21/2015 13:33
2 2 Higher Ground 2 2/21/2015 13:33
3 2 Subway to Venus 3 2/21/2015 13:33
4 2 Magic Johnson  4 2/21/2015 13:33
5 2 Nobody Weird Like Me 5 2/21/2015 13:33
6 2 Knock Me Down 6 2/21/2015 13:33
7 2 Taste the Pain  7 2/21/2015 13:33
8 2 Stone Cold Bush  8 2/21/2015 13:33
9 2 Fire 9 2/21/2015 13:33
10 2 Pretty Little Ditty 10 2/21/2015 13:33
11 2 Punk Rock Classic  11 2/21/2015 13:33
12 2 S*xy Mexican Maid 12 2/21/2015 13:33
13 2 Johnny, Kick a Hole in the Sky  13 2/21/2015 13:33
14 2 Song That Made Us What We Are Today - (demo) 14 2/21/2015 13:33
15 2 Knock Me Down - ( original long version) 15 2/21/2015 13:33
16 2 S*xy Mexican Maid - ( original long version) 16 2/21/2015 13:33
17 2 Salute to Kareem - ( demo) 17 2/21/2015 13:33
18 2 Castles Made of Sand - (live, 11/21/89)  18 2/21/2015 13:33
19 2 Crosstown Traffic - ( live, 11/21/89) 19 2/21/2015 13:33

 

 

-- 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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE