XpfKsRyR

1drop database if exists ti3_180206;
2create database if not exists ti3_180206;
3use ti3_180206;
4create table kategorie(
5id smallint not null primary key,
6nazwa varchar(32) not null
7);
8create table wyroby(
9id integer not null primary key,
10nazwa varchar(32) not null,
11kategoriaId smallint not null
12);
13create table magazyny(
14id integer not null primary key,
15wyrobId integer not null,
16ilosc integer not null
17);
18alter table wyroby add constraint foreign key fk_katId(kategoriaId)
19references kategorie(id) on update restrict on delete restrict;
20alter table magazyny add constraint foreign key fk_wyrId(wyrobId)
21references wyroby(id) on update restrict on delete restrict;
22
23
24insert into kategorie values
25(1, 'owoce'), (2, 'warzywa');
26insert into wyroby values
27(1, 'cytryna', 1),
28(2, 'banan', 1),
29(3, 'burak', 2);
30
31insert into magazyny values(1,2,68);
32insert into magazyny values(2,1,48);
33insert into magazyny values(3,3,20);
34insert into magazyny values(4,1,33);
35insert into magazyny values(5,1,34);
36insert into magazyny values(6,3,5);
37insert into magazyny values(7,1,14);
38insert into magazyny values(8,2,65);
39insert into magazyny values(9,2,51);
40insert into magazyny values(10,3,3);
41insert into magazyny values(11,3,20);
42insert into magazyny values(12,3,91);
43insert into magazyny values(13,3,21);
44insert into magazyny values(14,3,69);
45insert into magazyny values(15,3,50);
46insert into magazyny values(16,2,96);
47insert into magazyny values(17,3,14);
48insert into magazyny values(18,2,33);
49insert into magazyny values(19,1,15);
50insert into magazyny values(20,3,8);
51insert into magazyny values(21,1,69);
52insert into magazyny values(22,1,14);
53insert into magazyny values(23,2,35);
54insert into magazyny values(24,2,69);
55insert into magazyny values(25,3,66);
56insert into magazyny values(26,2,2);
57insert into magazyny values(27,3,52);
58insert into magazyny values(28,2,47);
59insert into magazyny values(29,3,33);
60insert into magazyny values(30,2,9);
61insert into magazyny values(31,3,95);
62insert into magazyny values(32,1,19);
63insert into magazyny values(33,3,86);
64insert into magazyny values(34,1,24);
65insert into magazyny values(35,1,64);
66insert into magazyny values(36,2,14);
67insert into magazyny values(37,3,43);
68insert into magazyny values(38,3,29);
69insert into magazyny values(39,1,11);
70insert into magazyny values(40,3,42);
71insert into magazyny values(41,1,24);
72insert into magazyny values(42,2,83);
73insert into magazyny values(43,3,7);
74insert into magazyny values(44,2,49);
75insert into magazyny values(45,3,80);
76insert into magazyny values(46,3,80);
77insert into magazyny values(47,2,66);
78insert into magazyny values(48,3,7);
79insert into magazyny values(49,3,81);
80insert into magazyny values(50,1,80);
81insert into magazyny values(51,1,96);
82insert into magazyny values(52,2,32);
83insert into magazyny values(53,1,68);
84insert into magazyny values(54,3,61);
85insert into magazyny values(55,3,59);
86insert into magazyny values(56,3,37);
87insert into magazyny values(57,3,18);
88insert into magazyny values(58,1,16);
89insert into magazyny values(59,2,14);
90insert into magazyny values(60,3,62);
91insert into magazyny values(61,3,75);
92insert into magazyny values(62,2,8);
93insert into magazyny values(63,2,30);
94insert into magazyny values(64,3,33);
95insert into magazyny values(65,1,25);
96insert into magazyny values(66,2,49);
97insert into magazyny values(67,1,12);
98insert into magazyny values(68,2,74);
99insert into magazyny values(69,1,76);
100insert into magazyny values(70,1,10);
101insert into magazyny values(71,3,17);
102insert into magazyny values(72,1,34);
103insert into magazyny values(73,2,67);
104insert into magazyny values(74,2,90);
105insert into magazyny values(75,3,21);
106insert into magazyny values(76,3,67);
107insert into magazyny values(77,1,40);
108insert into magazyny values(78,1,2);
109insert into magazyny values(79,3,84);
110insert into magazyny values(80,1,98);
111insert into magazyny values(81,3,35);
112insert into magazyny values(82,1,58);
113insert into magazyny values(83,2,84);
114insert into magazyny values(84,1,66);
115insert into magazyny values(85,1,64);
116insert into magazyny values(86,1,39);
117insert into magazyny values(87,3,23);
118insert into magazyny values(88,2,93);
119insert into magazyny values(89,3,64);
120insert into magazyny values(90,2,62);
121insert into magazyny values(91,3,28);
122insert into magazyny values(92,3,96);
123insert into magazyny values(93,1,31);
124insert into magazyny values(94,3,33);
125insert into magazyny values(95,2,6);
126insert into magazyny values(96,2,89);
127insert into magazyny values(97,2,1);
128insert into magazyny values(98,2,2);
129insert into magazyny values(99,3,1);
130insert into magazyny values(100,3,96);
131insert into magazyny values(101,2,8);
132insert into magazyny values(102,2,55);
133insert into magazyny values(103,2,78);
134insert into magazyny values(104,3,88);
135insert into magazyny values(105,3,62);
136insert into magazyny values(106,1,61);
137insert into magazyny values(107,1,15);
138insert into magazyny values(108,3,60);
139insert into magazyny values(109,1,52);
140insert into magazyny values(110,1,49);
141insert into magazyny values(111,1,38);
142insert into magazyny values(112,1,17);
143insert into magazyny values(113,3,27);
144insert into magazyny values(114,2,19);
145insert into magazyny values(115,1,51);
146insert into magazyny values(116,3,31);
147insert into magazyny values(117,3,76);
148insert into magazyny values(118,3,42);
149insert into magazyny values(119,1,10);
150insert into magazyny values(120,3,56);
151insert into magazyny values(121,2,80);
152insert into magazyny values(122,3,42);
153insert into magazyny values(123,1,50);
154insert into magazyny values(124,3,19);
155insert into magazyny values(125,2,90);
156
157
158select w.id, w.nazwa,
159(select count(m.wyrobId) from magazyny as m where m.wyrobid = w.id),
160(select coalesce(sum(m.wyrobId), 0) from magazyny as m where m.wyrobId = w.id)
161from wyroby as w order by w.nazwa asc;
162
163
164select count(m.id) from magazyny as m where m.wyrobId = 1;
165select coalesce(sum(m.id), 0) from magazyny as m where m.wyrobId = 1;
166
167
168select avg(m.ilosc) from magazyny as m;
169
170select m.* from magazyny as m where
171m. ilosc > (select avg(m.ilosc) from magazyny as m);
172
173select * from magazyny where ilosc = 0;
174-- update magazyny  set ilosc = ilosc + 10;