5EkMAuCM

1IF  NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'AKSLearnDB')
2    BEGIN
3        PRINT 'Creating database AKSLearnDB...'
4        CREATE DATABASE [AKSLearnDB]
5    END;
6GO
7
8-- IF  EXISTS (SELECT * FROM sys.databases WHERE name = N'AKSLearnDB')
9--     BEGIN
10--         PRINT 'Dropping existing database...'
11--         DROP DATABASE [AKSLearnDB]
12--     END;
13-- GO
14
15-- PRINT 'Creating database AKSLearnDB...'
16-- CREATE DATABASE [AKSLearnDB]
17
18SELECT Name from sys.Databases
19GO
20
21USE AKSLearnDB
22GO
23
24if not exists (select * from sysobjects where name='Genres' and xtype='U')
25    BEGIN
26        PRINT 'Creating Genres table'
27        CREATE TABLE Genres 
28        (
29            Id INT IDENTITY(1,1) PRIMARY KEY,
30            gen_title NVARCHAR(50)
31        )
32
33        PRINT 'Inserting default values in Genres table...'
34        INSERT INTO Genres(gen_title) VALUES('Action');
35        INSERT INTO Genres(gen_title) VALUES('Adventure');
36        INSERT INTO Genres(gen_title) VALUES('Romance');
37        INSERT INTO Genres(gen_title) VALUES('Thriller');
38        INSERT INTO Genres(gen_title) VALUES('War');
39    END
40GO
41
42if not exists (select * from sysobjects where name='Stars' and xtype='U')
43    BEGIN
44        PRINT 'Creating Level table'
45        CREATE TABLE Stars 
46        (
47            Id INT IDENTITY(1,1) PRIMARY KEY,
48            starValue NVARCHAR(50)
49        )
50
51        PRINT 'Inserting default values in Genres table...'
52        INSERT INTO Stars(starValue) VALUES('5 - Star');
53        INSERT INTO Stars(starValue) VALUES('4 - Star');
54        INSERT INTO Stars(starValue) VALUES('3 - Star');
55        INSERT INTO Stars(starValue) VALUES('2 - Star');
56		INSERT INTO Stars(starValue) VALUES('1 - Star');
57    END
58GO
59
60if not exists (select * from sysobjects where name='Movie' and xtype='U')
61    BEGIN
62        PRINT 'Creating Movie table'
63        CREATE TABLE Movie 
64        (
65            Id INT IDENTITY(1,1) PRIMARY KEY,             
66            movieName NVARCHAR(50), 
67			directorName NVARCHAR(50),
68			description NVARCHAR(200),
69            genreId INT REFERENCES Genres(Id),
70            starId INT REFERENCES Stars(Id)
71        )
72
73        PRINT 'Inserting default values into Movie table'
74        INSERT INTO Movie(movieName, directorName, description, genreId, starId) VALUES ('Titanic', 'James Cameron', 'A seventeen-year-old aristocrat falls in love with a kind but poor artist aboard the luxurious, ill-fated R.M.S. Titanic', 3, 1); 
75        INSERT INTO Movie(movieName, directorName, description, genreId, starId) VALUES ('Die Another Day', 'Lee Tamahori', 'James Bond is sent to investigate the connection between a North Korean terrorist and a diamond mogul, who is funding the development of an international space weapon', 1, 2);
76        INSERT INTO Movie(movieName, directorName, description, genreId, starId) VALUES ('Jurassic Park', 'Steven Spielberg', 'During a preview tour, a theme park suffers a major power breakdown that allows its cloned dinosaur exhibits to run amok', 1, 2);
77		INSERT INTO Movie(movieName, directorName, description, genreId, starId) VALUES ('Mission Impossible', 'Brian De Palma', 'An American agent, under false suspicion of disloyalty, must discover and expose the real spy without the help of his organization', 4, 1);
78		INSERT INTO Movie(movieName, directorName, description, genreId, starId) VALUES ('Gone in sixty seconds', 'Dominic Sena', 'A retired master car thief must come back to the industry and steal fifty cars with his crew in one night to save his brothers life', 1, 1);
79    END
80GO
81
82if not exists (select * from sysobjects where name='KeyValue' and xtype='U')
83    BEGIN
84        CREATE TABLE KeyValue ([Key] NVARCHAR(10), [Value] NVARCHAR(100))
85
86        INSERT INTO KeyValue VALUES('BBT', 'Bing Bang Theory');
87       
88    END
89GO