v449Cy5q

1--SÅ‚awomir Jankowski
2--nr albumu ga40456
3
4--sprawdzam, czy istnieją zaplanowane przeze mnie tabele, a jeżeli tak, usuwam je
5if exists (select * from information_schema.tables where table_name = 'inwentarz')
6drop table inwentarz;
7
8if exists (select * from information_schema.tables where table_name = 'statystyki')
9drop table statystyki;
10
11if exists (select * from information_schema.tables where table_name = 'czary')
12drop table czary;
13
14if exists (select * from information_schema.tables where table_name = 'zdolnosci')
15drop table zdolnosci;
16
17if exists (select * from information_schema.tables where table_name = 'bron')
18drop table bron;
19
20if exists (select * from information_schema.tables where table_name = 'pancerz')
21drop table pancerz;
22
23if exists (select * from information_schema.tables where table_name = 'eliksiry')
24drop table eliksiry;
25
26if exists (select * from information_schema.tables where table_name = 'podstawowe')
27drop table podstawowe;
28
29--tworzÄ™ tabele zawierajÄ…ce dane
30create table podstawowe(
31id_postaci int identity(1,1) primary key,
32name varchar(24) not null,
33age int check(age between 16 and 64) not null,
34race varchar(16) not null,
35sex char(1) not null,
36category varchar(16) not null);
37
38insert into podstawowe(name,age,race,sex,category)
39values('Twonk',30,'ork','m','barbarzyńca');
40
41insert into podstawowe(name,age,race,sex,category)
42values('Rungar',25,'człowiek','m','mag');
43
44insert into podstawowe(name,age,race,sex,category)
45values('Barrya',17,'skrzat','k','złodziej');
46
47insert into podstawowe(name,age,race,sex,category)
48values('Ruby',23,'człowiek','k','bard');
49
50insert into podstawowe(name,age,race,sex,category)
51values('Polter',54,'krasnolud','m','rycerz');
52
53
54create table statystyki(
55id_postaci int foreign key references podstawowe(id_postaci) on delete set null,
56strenght int check(strenght >= 0) not null,
57dexterity int check(dexterity >= 0) not null,
58agility int check(agility >= 0) not null,
59inteligence int check(inteligence >= 0) not null,
60wisdom int check(wisdom >= 0) not null,
61charisma int check(charisma >= 0) not null);
62
63insert into statystyki(id_postaci,strenght,dexterity,agility,inteligence,wisdom,charisma)
64values(1,10,6,8,4,4,6);
65
66insert into statystyki(id_postaci,strenght,dexterity,agility,inteligence,wisdom,charisma)
67values(2,4,6,6,10,8,8);
68
69insert into statystyki(id_postaci,strenght,dexterity,agility,inteligence,wisdom,charisma)
70values(3,6,10,10,6,6,4);
71
72insert into statystyki(id_postaci,strenght,dexterity,agility,inteligence,wisdom,charisma)
73values(4,4,10,8,6,8,10);
74
75insert into statystyki(id_postaci,strenght,dexterity,agility,inteligence,wisdom,charisma)
76values(5,8,4,6,6,8,8);
77
78
79create table czary(
80id_czaru int identity(1,1) primary key,
81id_postaci int foreign key references podstawowe(id_postaci) on delete set null,
82lightning bit not null,
83firebolt bit not null,
84iceneedle bit not null,
85windstorm bit not null,
86haste bit not null);
87
88insert into czary(id_postaci,lightning,firebolt,iceneedle,windstorm,haste)
89values(1,0,0,0,0,1);
90
91insert into czary(id_postaci,lightning,firebolt,iceneedle,windstorm,haste)
92values(2,1,1,1,1,0);
93
94insert into czary(id_postaci,lightning,firebolt,iceneedle,windstorm,haste)
95values(3,0,0,1,0,1);
96
97insert into czary(id_postaci,lightning,firebolt,iceneedle,windstorm,haste)
98values(4,0,1,0,1,1);
99
100insert into czary(id_postaci,lightning,firebolt,iceneedle,windstorm,haste)
101values(5,1,0,0,0,1);
102
103
104create table zdolnosci(
105id_zdolnosci int identity(1,1) primary key,
106id_postaci int foreign key references podstawowe(id_postaci) on delete set null,
107crouch bit not null,
108high_jump bit not null,
109critical_attack bit not null,
110hide bit not null,
111tower_defence bit not null);
112
113insert into zdolnosci(id_postaci,crouch,high_jump,critical_attack,hide,tower_defence)
114values(1,1,1,1,0,0);
115
116insert into zdolnosci(id_postaci,crouch,high_jump,critical_attack,hide,tower_defence)
117values(2,1,0,0,0,0);
118
119insert into zdolnosci(id_postaci,crouch,high_jump,critical_attack,hide,tower_defence)
120values(3,1,1,1,1,0);
121
122insert into zdolnosci(id_postaci,crouch,high_jump,critical_attack,hide,tower_defence)
123values(4,1,1,0,1,0);
124
125insert into zdolnosci(id_postaci,crouch,high_jump,critical_attack,hide,tower_defence)
126values(5,0,0,1,0,1);
127
128
129create table bron(
130id_bron int identity(1,1) primary key,
131id_postaci int foreign key references podstawowe(id_postaci) on delete set null,
132sword bit not null,
133dagger bit not null,
134bow bit not null,
135axe bit not null,
136staff bit not null);
137
138insert into bron(id_postaci,sword,dagger,bow,axe,staff)
139values(1,0,0,0,1,0)
140
141insert into bron(id_postaci,sword,dagger,bow,axe,staff)
142values(2,0,0,0,0,1)
143
144insert into bron(id_postaci,sword,dagger,bow,axe,staff)
145values(3,0,1,0,0,0)
146
147insert into bron(id_postaci,sword,dagger,bow,axe,staff)
148values(4,0,0,1,0,0)
149
150insert into bron(id_postaci,sword,dagger,bow,axe,staff)
151values(5,1,0,0,0,0)
152
153
154create table pancerz(
155id_pancerz int identity(1,1) primary key,
156id_postaci int foreign key references podstawowe(id_postaci) on delete set null,
157helmet bit not null,
158breastplate bit not null,
159boots bit not null,
160gloves bit not null,
161greaves bit not null);
162
163insert into pancerz(id_postaci,helmet,breastplate,boots,gloves,greaves)
164values(1,0,1,1,1,0)
165
166insert into pancerz(id_postaci,helmet,breastplate,boots,gloves,greaves)
167values(2,0,0,1,1,0)
168
169insert into pancerz(id_postaci,helmet,breastplate,boots,gloves,greaves)
170values(3,0,0,1,1,1)
171
172insert into pancerz(id_postaci,helmet,breastplate,boots,gloves,greaves)
173values(4,1,0,1,1,1)
174
175insert into pancerz(id_postaci,helmet,breastplate,boots,gloves,greaves)
176values(1,1,1,1,1,1)
177
178
179create table eliksiry(
180id_eliksir int identity(1,1) primary key,
181id_postaci int foreign key references podstawowe(id_postaci) on delete set null,
182invisibility bit not null,
183levitate bit not null,
184silence bit not null,
185strenght bit not null,
186bomb bit not null,
187acid bit not null);
188
189insert into eliksiry(id_postaci,invisibility,levitate,silence,strenght,bomb,acid)
190values(1,0,0,0,1,0,1)
191
192insert into eliksiry(id_postaci,invisibility,levitate,silence,strenght,bomb,acid)
193values(2,1,1,1,0,1,1)
194
195insert into eliksiry(id_postaci,invisibility,levitate,silence,strenght,bomb,acid)
196values(3,1,0,1,0,1,0)
197
198insert into eliksiry(id_postaci,invisibility,levitate,silence,strenght,bomb,acid)
199values(4,0,1,1,1,0,1)
200
201insert into eliksiry(id_postaci,invisibility,levitate,silence,strenght,bomb,acid)
202values(5,0,0,0,1,0,0)
203
204
205create table inwentarz(
206id_postaci int foreign key references podstawowe(id_postaci) on delete set null,
207weapon int foreign key references bron(id_bron) on delete set null,
208armor int foreign key references pancerz(id_pancerz) on delete set null,
209elixir int foreign key references eliksiry(id_eliksir) on delete set null,
210spell int foreign key references czary(id_czaru) on delete set null);
211
212insert into inwentarz(id_postaci,weapon,armor,elixir,spell)
213values(1,1,1,1,1);
214
215insert into inwentarz(id_postaci,weapon,armor,elixir,spell)
216values(2,2,2,2,2);
217
218insert into inwentarz(id_postaci,weapon,armor,elixir,spell)
219values(3,3,3,3,3);
220
221insert into inwentarz(id_postaci,weapon,armor,elixir,spell)
222values(4,4,4,4,4);
223
224insert into inwentarz(id_postaci,weapon,armor,elixir,spell)
225values(5,5,5,5,5);
226
227
228select * from podstawowe;
229select * from statystyki;
230select * from czary;
231select * from zdolnosci;
232select * from bron;
233select * from pancerz;
234select * from eliksiry;
235select * from inwentarz;
236
237
238--sprawdzam czy istnieją, a jeżeli tak, to usuwam widoki, funkcje, procedury
239if exists (select * from information_schema.views where table_name = 'heroes_spells')
240drop view heroes_spells;
241
242if exists (select * from information_schema.views where table_name = 'smart_heroes')
243drop view smart_heroes;