Exercise10

CREATE DATABASE Exercise10
GO

USE Exercise10
GO

CREATE TABLE Genre (
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](256) NOT NULL,
  [Description] [nvarchar](max) NOT NULL,
  PRIMARY KEY ([Id])
)
GO

SET IDENTITY_INSERT Genre ON
GO

INSERT INTO Genre (Id, [Name], [Description])
VALUES
  (1, 'Rock', 'Otherwise known as ‘Rock & Roll,’ rock music has been a popular genre since the early 1950s.'),
  (2, 'Jazz', 'Identifiable with blues and swing notes, Jazz has origins in European and West African culture.'),
  (3, 'Electronic Dance Music', 'Typically referred to as EDM, this type of music is created by DJs who mix a range of beats and tones to create unique music.'),
  (4, 'Dubstep', 'Dubstep is an electronic dance music subgenre that originated in the late 1990s’ in South London.'),
  (5, 'Techno', 'Techno is yet another sub-genre of electronic dance music. This genre became popular in Germany towards the end of the 1980s and was heavily influenced by house music, funk, synthpop, and futuristic fiction.'),
  (6, 'Rhythm and Blues (R&B)', 'R & B is one of the world’s top music genres combining gospel, blues, and jazz influences.'),
  (7, 'Country', 'Country music is another one of the world’s top music genres. Originating in the 1920s, Country has its roots in western music and American folk.'),
  (8, 'Pop', 'The term ‘Pop’ is derived from the word ‘popular.’ Therefore, Pop music is a genre that contains music generally favored throughout society.'),
  (9, 'Indie Rock', 'In terms of genre, Indie Rock lies somewhere between pop music and rock and roll.'),
  (10, 'Electro', 'Electro blends electronic music and hip hop to create music that is similar to disco in sound.')
GO

SET IDENTITY_INSERT Genre OFF
GO

CREATE TABLE Artist (
  Id int NOT NULL IDENTITY (1, 1),
  [Name] nvarchar(256) NOT NULL
  CONSTRAINT PK_Artist
    PRIMARY KEY (Id)
)

SET IDENTITY_INSERT Artist ON
GO

INSERT INTO Artist (Id, [Name])
VALUES
  (1, 'Tina Turner'),
  (2, 'Van Halen'),
  (3, 'DJ Snake'),
  (4, 'Louis Armstrong')
GO

SET IDENTITY_INSERT Artist OFF
GO

CREATE TABLE Song (
  Id int NOT NULL IDENTITY (1, 1),
  [Name] nvarchar(256) NOT NULL,
  [Year] int NULL,
  GenreId int NOT NULL,
  ArtistId int NOT NULL,
  DeletedAt datetime2(7) NULL,
  CONSTRAINT PK_Song
    PRIMARY KEY (Id),
  CONSTRAINT FK_Song_Genre
    FOREIGN KEY(GenreId)
    REFERENCES dbo.Genre (Id),
  CONSTRAINT FK_Song_Artist
    FOREIGN KEY(ArtistId)
    REFERENCES dbo.Artist (Id)
)

SET IDENTITY_INSERT Song ON
GO

INSERT INTO Song (Id, [Name], [Year], GenreId, ArtistId)
VALUES
  (1, 'What''s Love Got to Do with It', 1984, 8, 1),
  (2, 'The Best', 1989, 8, 1),
  (3, 'Jump', 1984, 1, 2),
  (4, 'Lean On', 2015, 10, 3),
  (5, 'What a Wonderful World', 1967, 2, 4),
  (6, 'We Have All The Time In The World', 1969, 2, 4)
GO

SET IDENTITY_INSERT Song OFF
GO