· 8 years ago · Aug 12, 2018, 02:44 PM
1USE master
2GO
3if exists (select * from sysdatabases where name='ships')
4 DROP DATABASE ships
5GO
6
7CREATE DATABASE ships
8GO
9USE ships
10GO
11
12----- Tables -----
13CREATE TABLE BATTLES (
14 NAME VARCHAR(20) NOT NULL ,
15 DATE DATETIME NOT NULL
16);
17
18CREATE TABLE CLASSES(
19 CLASS VARCHAR(50) NOT NULL ,
20 TYPE VARCHAR(2) NOT NULL ,
21 COUNTRY VARCHAR (20) NOT NULL ,
22 NUMGUNS INT,
23 BORE REAL,
24 DISPLACEMENT INT
25);
26
27CREATE TABLE SHIPS(
28 NAME VARCHAR(50) NOT NULL ,
29 CLASS VARCHAR(50) NOT NULL ,
30 LAUNCHED INT
31);
32
33CREATE TABLE OUTCOMES(
34 SHIP VARCHAR(50) NOT NULL ,
35 BATTLE VARCHAR(20) NOT NULL ,
36 RESULT VARCHAR(10) NOT NULL
37);
38
39----- Constraints -----
40ALTER TABLE BATTLES ADD CONSTRAINT PK_BATTLES PRIMARY KEY(NAME);
41
42ALTER TABLE CLASSES ADD CONSTRAINT PK_CLASSES PRIMARY KEY(CLASS);
43
44ALTER TABLE SHIPS ADD CONSTRAINT PK_SHIPS PRIMARY KEY(NAME);
45
46ALTER TABLE SHIPS ADD CONSTRAINT FK_SHIPS_CLASSES FOREIGN KEY(CLASS) REFERENCES CLASSES(CLASS);
47
48ALTER TABLE OUTCOMES ADD CONSTRAINT PK_OUTCOMES PRIMARY KEY(SHIP,BATTLE);
49
50ALTER TABLE OUTCOMES ADD CONSTRAINT FK_OUTCOMES_BATTLES FOREIGN KEY(BATTLE) REFERENCES BATTLES(NAME);
51
52ALTER TABLE OUTCOMES ADD CONSTRAINT FK_OUTCOMES_SHIPS FOREIGN KEY(SHIP) REFERENCES SHIPS(NAME);
53
54----- Classes -----
55INSERT INTO CLASSES
56 VALUES ('Bismarck', 'bb', 'Germany', 8, 15, 42000);
57
58INSERT INTO CLASSES
59 VALUES ('Iowa', 'bb', 'USA', 9, 16, 46000);
60
61INSERT INTO CLASSES
62 VALUES ('Kongo', 'bc', 'Japan', 8, 14, 32000);
63
64INSERT INTO CLASSES
65 VALUES ('North Carolina', 'bb', 'USA', 12, 16, 37000);
66
67INSERT INTO CLASSES
68 VALUES ('Renown', 'bc', 'Gt.Britain', 6, 15, 32000);
69
70INSERT INTO CLASSES
71 VALUES ('Revenge', 'bb', 'Gt.Britain', 8, 15, 29000);
72
73INSERT INTO CLASSES
74 VALUES ('Tennessee', 'bb', 'USA', 12, 14, 32000);
75
76INSERT INTO CLASSES
77 VALUES ('Yamato', 'bb', 'Japan', 9, 18, 65000);
78
79----- Battles -----
80INSERT INTO BATTLES
81 VALUES ('Guadalcanal', '1942-11-15');
82
83INSERT INTO BATTLES
84 VALUES ('North Atlantic', '1941-05-25');
85
86INSERT INTO BATTLES
87 VALUES ('North Cape', '1943-12-26');
88
89INSERT INTO BATTLES
90 VALUES ('Surigao Strait', '1944-10-25');
91
92----- Ships -----
93INSERT INTO SHIPS
94 VALUES ('California', 'Tennessee', 1921);
95
96INSERT INTO SHIPS
97 VALUES ('Haruna', 'Kongo', 1916);
98
99INSERT INTO SHIPS
100 VALUES ('Hiei', 'Kongo', 1914);
101
102INSERT INTO SHIPS
103 VALUES ('Iowa', 'Iowa', 1943);
104
105INSERT INTO SHIPS
106 VALUES ('Kirishima', 'Kongo', 1915);
107
108INSERT INTO SHIPS
109 VALUES ('Kongo', 'Kongo', 1913);
110
111INSERT INTO SHIPS
112 VALUES ('Missouri', 'Iowa', 1944);
113
114INSERT INTO SHIPS
115 VALUES ('Musashi', 'Yamato', 1942);
116
117INSERT INTO SHIPS
118 VALUES ('New Jersey', 'Iowa', 1943);
119
120INSERT INTO SHIPS
121 VALUES ('North Carolina', 'North Carolina', 1941);
122
123INSERT INTO SHIPS
124 VALUES ('Ramillies', 'Revenge', 1917);
125
126INSERT INTO SHIPS
127 VALUES ('Renown', 'Renown', 1916);
128
129INSERT INTO SHIPS
130 VALUES ('Repulse', 'Renown', 1916);
131
132INSERT INTO SHIPS
133 VALUES ('Resolution', 'Renown', 1916);
134
135INSERT INTO SHIPS
136 VALUES ('Revenge', 'Revenge', 1916);
137
138INSERT INTO SHIPS
139 VALUES ('Royal Oak', 'Revenge', 1916);
140
141INSERT INTO SHIPS
142 VALUES ('Royal Sovereign', 'Revenge', 1916);
143
144INSERT INTO SHIPS
145 VALUES ('Tennessee', 'Tennessee', 1920);
146
147INSERT INTO SHIPS
148 VALUES ('Washington', 'North Carolina', 1941);
149
150INSERT INTO SHIPS
151 VALUES ('Wisconsin', 'Iowa', 1944);
152
153INSERT INTO SHIPS
154 VALUES ('Yamato', 'Yamato', 1941);
155
156INSERT INTO SHIPS
157 VALUES ('Yamashiro', 'Yamato', 1947);
158
159INSERT INTO SHIPS
160 VALUES ('South Dakota', 'North Carolina', 1941);
161
162INSERT INTO SHIPS
163 VALUES ('Bismarck', 'North Carolina', 1911);
164
165INSERT INTO SHIPS
166 VALUES ('Duke of York', 'Renown', 1916);
167
168INSERT INTO SHIPS
169 VALUES ('Fuso', 'Iowa', 1940);
170
171INSERT INTO SHIPS
172 VALUES ('Hood', 'Iowa', 1942);
173
174INSERT INTO SHIPS
175 VALUES ('Rodney', 'Yamato', 1915);
176
177INSERT INTO SHIPS
178 VALUES ('Yanashiro', 'Yamato', 1918);
179
180INSERT INTO SHIPS
181 VALUES ('Schamhorst', 'North Carolina', 1917);
182
183INSERT INTO SHIPS
184 VALUES ('Prince of Wales', 'North Carolina', 1937);
185
186INSERT INTO SHIPS
187 VALUES ('King George V', 'Iowa', 1942);
188
189INSERT INTO SHIPS
190 VALUES ('West Virginia', 'Iowa', 1942);
191
192----- Outcomes -----
193INSERT INTO OUTCOMES
194 VALUES ('Bismarck', 'North Atlantic', 'sunk');
195
196INSERT INTO OUTCOMES
197 VALUES ('California', 'Surigao Strait', 'ok');
198
199INSERT INTO OUTCOMES
200 VALUES ('Duke of York', 'North Cape', 'ok');
201
202INSERT INTO OUTCOMES
203 VALUES ('Fuso', 'Surigao Strait', 'sunk');
204
205INSERT INTO OUTCOMES
206 VALUES ('Hood', 'North Atlantic', 'sunk');
207
208INSERT INTO OUTCOMES
209 VALUES ('King George V', 'North Atlantic', 'ok');
210
211INSERT INTO OUTCOMES
212 VALUES ('Kirishima', 'Guadalcanal', 'sunk');
213
214INSERT INTO OUTCOMES
215 VALUES ('Prince of Wales', 'North Atlantic', 'damaged');
216
217INSERT INTO OUTCOMES
218 VALUES ('Rodney', 'North Atlantic', 'ok');
219
220INSERT INTO OUTCOMES
221 VALUES ('Schamhorst', 'North Cape', 'sunk');
222
223INSERT INTO OUTCOMES
224 VALUES ('South Dakota', 'Guadalcanal', 'damaged');
225
226INSERT INTO OUTCOMES
227 VALUES ('Tennessee', 'Surigao Strait', 'ok');
228
229INSERT INTO OUTCOMES
230 VALUES ('Washington', 'Guadalcanal', 'ok');
231
232INSERT INTO OUTCOMES
233 VALUES ('West Virginia', 'Surigao Strait', 'ok');
234
235INSERT INTO OUTCOMES
236 VALUES ('Yamashiro', 'Surigao Strait', 'sunk');
237
238INSERT INTO OUTCOMES
239 VALUES ('California', 'Guadalcanal', 'damaged');