· 9 years ago · Oct 06, 2016, 10:22 AM
1CREATE DATABASE IF NOT EXISTS `firma1` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_polish_ci */;
2USE `firma1`;
3-- MySQL dump 10.13 Distrib 5.6.24, for Win64 (x86_64)
4--
5-- Host: 127.0.0.1 Database: firma1
6-- ------------------------------------------------------
7-- Server version 5.6.27-log
8
9/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
10/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
11/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
12/*!40101 SET NAMES utf8 */;
13/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
14/*!40103 SET TIME_ZONE='+00:00' */;
15/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
16/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
17/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
18/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19
20--
21-- Table structure for table `biuro`
22--
23
24DROP TABLE IF EXISTS `biuro`;
25/*!40101 SET @saved_cs_client = @@character_set_client */;
26/*!40101 SET character_set_client = utf8 */;
27CREATE TABLE `biuro` (
28 `biuroNr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
29 `ulica` varchar(25) COLLATE utf8_polish_ci NOT NULL,
30 `miasto` varchar(25) COLLATE utf8_polish_ci NOT NULL,
31 `kod` varchar(6) COLLATE utf8_polish_ci NOT NULL,
32 PRIMARY KEY (`biuroNr`)
33) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
34/*!40101 SET character_set_client = @saved_cs_client */;
35
36--
37-- Dumping data for table `biuro`
38--
39
40LOCK TABLES `biuro` WRITE;
41/*!40000 ALTER TABLE `biuro` DISABLE KEYS */;
42INSERT INTO `biuro` VALUES ('B001','Piekna 46','Bialystok','15-900'),('B002','Cicha 56','Lomza','18-400'),('B003','Mala 63','Bialystok','15-900'),('B004','Miodowa 32','Grajewo','19-300'),('B005','Dobra 22','Lomza','18-400'),('B006','Sloneczna 55','Bialystok','15-900'),('B007','Akacjowa 16','Augustów','16-300');
43/*!40000 ALTER TABLE `biuro` ENABLE KEYS */;
44UNLOCK TABLES;
45
46--
47-- Table structure for table `biuro2`
48--
49
50DROP TABLE IF EXISTS `biuro2`;
51/*!40101 SET @saved_cs_client = @@character_set_client */;
52/*!40101 SET character_set_client = utf8 */;
53CREATE TABLE `biuro2` (
54 `biuroNr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
55 `miasto` varchar(25) COLLATE utf8_polish_ci NOT NULL,
56 PRIMARY KEY (`biuroNr`)
57) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
58/*!40101 SET character_set_client = @saved_cs_client */;
59
60--
61-- Dumping data for table `biuro2`
62--
63
64LOCK TABLES `biuro2` WRITE;
65/*!40000 ALTER TABLE `biuro2` DISABLE KEYS */;
66INSERT INTO `biuro2` VALUES ('B002','Lomza'),('B003','Bialystok'),('B004','Grajewo');
67/*!40000 ALTER TABLE `biuro2` ENABLE KEYS */;
68UNLOCK TABLES;
69
70--
71-- Table structure for table `klient`
72--
73
74DROP TABLE IF EXISTS `klient`;
75/*!40101 SET @saved_cs_client = @@character_set_client */;
76/*!40101 SET character_set_client = utf8 */;
77CREATE TABLE `klient` (
78 `klientnr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
79 `imie` varchar(25) COLLATE utf8_polish_ci NOT NULL,
80 `nazwisko` varchar(25) COLLATE utf8_polish_ci NOT NULL,
81 `adres` varchar(45) COLLATE utf8_polish_ci NOT NULL,
82 `telefon` varchar(15) COLLATE utf8_polish_ci DEFAULT NULL,
83 `preferencje` varchar(25) COLLATE utf8_polish_ci DEFAULT NULL,
84 `max_czynsz` smallint(5) unsigned DEFAULT NULL,
85 PRIMARY KEY (`klientnr`)
86) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
87/*!40101 SET character_set_client = @saved_cs_client */;
88
89--
90-- Dumping data for table `klient`
91--
92
93LOCK TABLES `klient` WRITE;
94/*!40000 ALTER TABLE `klient` DISABLE KEYS */;
95INSERT INTO `klient` VALUES ('CO16','Alicja','Stefanska','15-900 Bialystok','0-85-333 2222','mieszkanie',450),('CO17','Katarzyna','Winiarska','15-900 Bialystok',' ','mieszkanie',420),('CO18','Anna','Nowak','15-900 Bialystok',' ','dom',850),('CR51','Michal','Rafalski','Tykocin,','0-86-123 4567','dom',750),('CR52','Ludwik','Wierzba','19-200 Grajewo',' ','mieszkanie',375),('CR53','Janusz','Kalinowski','18-400 Lomza','0-86-444 5555','mieszkanie',425),('CR54','Maria','Tomaszewska','16-300 Augustów, Trawiasta 5','0-87-111 6666','mieszkanie',600);
96/*!40000 ALTER TABLE `klient` ENABLE KEYS */;
97UNLOCK TABLES;
98
99--
100-- Table structure for table `nieruchomosc`
101--
102
103DROP TABLE IF EXISTS `nieruchomosc`;
104/*!40101 SET @saved_cs_client = @@character_set_client */;
105/*!40101 SET character_set_client = utf8 */;
106CREATE TABLE `nieruchomosc` (
107 `nieruchomoscnr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
108 `ulica` varchar(25) COLLATE utf8_polish_ci NOT NULL,
109 `miasto` varchar(25) COLLATE utf8_polish_ci NOT NULL,
110 `kod` varchar(6) COLLATE utf8_polish_ci NOT NULL,
111 `typ` varchar(25) COLLATE utf8_polish_ci NOT NULL,
112 `pokoje` tinyint(3) unsigned NOT NULL,
113 `czynsz` smallint(5) unsigned NOT NULL,
114 `wlascicielNr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
115 `personelNr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
116 `biuroNr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
117 PRIMARY KEY (`nieruchomoscnr`),
118 KEY `wlascicielNr` (`wlascicielNr`),
119 KEY `personelNr` (`personelNr`),
120 KEY `biuroNr` (`biuroNr`),
121 CONSTRAINT `biuroNr2` FOREIGN KEY (`biuroNr`) REFERENCES `biuro` (`biuroNr`) ON DELETE CASCADE ON UPDATE CASCADE,
122 CONSTRAINT `personelNr2` FOREIGN KEY (`personelNr`) REFERENCES `personel` (`personelNr`) ON DELETE CASCADE ON UPDATE CASCADE,
123 CONSTRAINT `wlascicielNr2` FOREIGN KEY (`wlascicielNr`) REFERENCES `wlasciciel` (`wlascicielnr`) ON DELETE CASCADE ON UPDATE CASCADE
124) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
125/*!40101 SET character_set_client = @saved_cs_client */;
126
127--
128-- Dumping data for table `nieruchomosc`
129--
130
131LOCK TABLES `nieruchomosc` WRITE;
132/*!40000 ALTER TABLE `nieruchomosc` DISABLE KEYS */;
133INSERT INTO `nieruchomosc` VALUES ('A14','Handlowa 16','Augustów','16-300','dom',6,715,'CO46','SA9','B007'),('B16','Nowa 5','Bialystok','15-900','mieszkanie',4,495,'CO93','SB21','B003'),('B17','Mala 2','Bialystok','15-900','mieszkanie',3,412,'CO93','SB23','B003'),('B18','Leœna 6','Bialystok','15-900','mieszkanie',3,385,'CO40','SB23','B003'),('B21','Dobra 18','Bialystok','15-900','dom',5,660,'CO87','SB22','B003'),('G01','Dluga 33','Grajewo','19-200','dom',7,830,'CO15','SG21','B004'),('L94','Akacjowa 6','Lomza','18-400','mieszkanie',4,440,'CO87','SL31','B005');
134/*!40000 ALTER TABLE `nieruchomosc` ENABLE KEYS */;
135UNLOCK TABLES;
136
137--
138-- Table structure for table `nieruchomosc2`
139--
140
141DROP TABLE IF EXISTS `nieruchomosc2`;
142/*!40101 SET @saved_cs_client = @@character_set_client */;
143/*!40101 SET character_set_client = utf8 */;
144CREATE TABLE `nieruchomosc2` (
145 `nieruchomoscNr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
146 `miasto` varchar(25) COLLATE utf8_polish_ci NOT NULL,
147 PRIMARY KEY (`nieruchomoscNr`)
148) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
149/*!40101 SET character_set_client = @saved_cs_client */;
150
151--
152-- Dumping data for table `nieruchomosc2`
153--
154
155LOCK TABLES `nieruchomosc2` WRITE;
156/*!40000 ALTER TABLE `nieruchomosc2` DISABLE KEYS */;
157INSERT INTO `nieruchomosc2` VALUES ('A14','Augustów'),('B4','Bialystok'),('L94','Lomza');
158/*!40000 ALTER TABLE `nieruchomosc2` ENABLE KEYS */;
159UNLOCK TABLES;
160
161--
162-- Table structure for table `personel`
163--
164
165DROP TABLE IF EXISTS `personel`;
166/*!40101 SET @saved_cs_client = @@character_set_client */;
167/*!40101 SET character_set_client = utf8 */;
168CREATE TABLE `personel` (
169 `personelNr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
170 `imie` varchar(25) COLLATE utf8_polish_ci NOT NULL,
171 `nazwisko` varchar(25) COLLATE utf8_polish_ci NOT NULL,
172 `stanowisko` varchar(25) COLLATE utf8_polish_ci NOT NULL,
173 `plec` enum('K','M') COLLATE utf8_polish_ci NOT NULL,
174 `dataUr` date NOT NULL,
175 `pensja` smallint(4) unsigned NOT NULL,
176 `biuroNr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
177 PRIMARY KEY (`personelNr`),
178 KEY `biuroNr` (`biuroNr`),
179 CONSTRAINT `biuroNr` FOREIGN KEY (`biuroNr`) REFERENCES `biuro` (`biuroNr`) ON DELETE CASCADE ON UPDATE CASCADE
180) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
181/*!40101 SET character_set_client = @saved_cs_client */;
182
183--
184-- Dumping data for table `personel`
185--
186
187LOCK TABLES `personel` WRITE;
188/*!40000 ALTER TABLE `personel` DISABLE KEYS */;
189INSERT INTO `personel` VALUES ('SA8','Katarzyna','Morawska','kierownik','K','1971-05-06',1700,'B007'),('SA9','Maria','Hojna','asystent','K','1970-02-19',900,'B007'),('SB20','Sabina','Bober','dyrektor','K','1940-06-03',2400,'B003'),('SB21','Daniel','Frankowski','kierownik','M','1958-03-24',1800,'B003'),('SB22','Malgorzata','Kowalska','asystent','K','1972-03-15',1000,'B003'),('SB23','Anna','Bialy','asystent','K','1960-11-10',1200,'B003'),('SB30','Katarzyna','Michalska','dyrektor','K','1960-11-17',2500,'B006'),('SB31','Dawid','Piotrowski','asystent','M','1975-03-22',1100,'B006'),('SB32','Malgorzata','Plichta','asystent','K','1971-10-03',1200,'B006'),('SG20','Karolina','Mucha','dyrektor','K','1953-03-03',2200,'B004'),('SG21','Piotr','Cybulski','asystent','M','1974-12-06',1300,'B004'),('SL20','Pawel','Nowak','kierownik','M','1962-02-02',1500,'B002'),('SL21','Pawel','Kowalski','asystent','M','1969-05-05',1000,'B002'),('SL22','Monika','Munk','asystent','K','1977-07-26',1100,'B002'),('SL30','Jan','WiÂœniewski','dyrektor','M','1945-10-01',3000,'B005'),('SL31','Julia','Lisicka','asystent','K','1965-07-13',900,'B005'),('SL32','Michal','Brzeczyk','asystent','M','1959-03-15',1000,'B005');
190/*!40000 ALTER TABLE `personel` ENABLE KEYS */;
191UNLOCK TABLES;
192
193--
194-- Table structure for table `rejestracja`
195--
196
197DROP TABLE IF EXISTS `rejestracja`;
198/*!40101 SET @saved_cs_client = @@character_set_client */;
199/*!40101 SET character_set_client = utf8 */;
200CREATE TABLE `rejestracja` (
201 `klientnr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
202 `biuronr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
203 `personelnr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
204 `data_rejestracji` date NOT NULL,
205 PRIMARY KEY (`klientnr`,`biuronr`,`personelnr`)
206) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
207/*!40101 SET character_set_client = @saved_cs_client */;
208
209--
210-- Dumping data for table `rejestracja`
211--
212
213LOCK TABLES `rejestracja` WRITE;
214/*!40000 ALTER TABLE `rejestracja` DISABLE KEYS */;
215INSERT INTO `rejestracja` VALUES ('CR56','B003','G37','2000-04-11'),('CR62','B007','A9','2000-03-07'),('CR74','B003','G37','1999-11-16'),('CR76','B005','L41','2001-01-02');
216/*!40000 ALTER TABLE `rejestracja` ENABLE KEYS */;
217UNLOCK TABLES;
218
219--
220-- Table structure for table `wizyta`
221--
222
223DROP TABLE IF EXISTS `wizyta`;
224/*!40101 SET @saved_cs_client = @@character_set_client */;
225/*!40101 SET character_set_client = utf8 */;
226CREATE TABLE `wizyta` (
227 `klientnr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
228 `nieruchomoscnr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
229 `data_wizyty` date NOT NULL,
230 `uwagi` varchar(255) COLLATE utf8_polish_ci DEFAULT NULL,
231 PRIMARY KEY (`klientnr`,`nieruchomoscnr`)
232) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
233/*!40101 SET character_set_client = @saved_cs_client */;
234
235--
236-- Dumping data for table `wizyta`
237--
238
239LOCK TABLES `wizyta` WRITE;
240/*!40000 ALTER TABLE `wizyta` DISABLE KEYS */;
241INSERT INTO `wizyta` VALUES ('CR51','A14','2001-05-24','za maly'),('CR51','B36','2001-04-28',' '),('CR51','B4','2001-05-26',' '),('CR52','A14','2001-05-14','brak jadalni'),('CR53','B4','2001-04-20','za daleko');
242/*!40000 ALTER TABLE `wizyta` ENABLE KEYS */;
243UNLOCK TABLES;
244
245--
246-- Table structure for table `wlasciciel`
247--
248
249DROP TABLE IF EXISTS `wlasciciel`;
250/*!40101 SET @saved_cs_client = @@character_set_client */;
251/*!40101 SET character_set_client = utf8 */;
252CREATE TABLE `wlasciciel` (
253 `wlascicielnr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
254 `imie` varchar(25) COLLATE utf8_polish_ci NOT NULL,
255 `nazwisko` varchar(25) COLLATE utf8_polish_ci NOT NULL,
256 `adres` varchar(45) COLLATE utf8_polish_ci NOT NULL,
257 `telefon` varchar(15) COLLATE utf8_polish_ci NOT NULL,
258 PRIMARY KEY (`wlascicielnr`)
259) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
260/*!40101 SET character_set_client = @saved_cs_client */;
261
262--
263-- Dumping data for table `wlasciciel`
264--
265
266LOCK TABLES `wlasciciel` WRITE;
267/*!40000 ALTER TABLE `wlasciciel` DISABLE KEYS */;
268INSERT INTO `wlasciciel` VALUES ('CO15','Pawel','Kowalewski','19-200 Grajewo, Jarzebinowa 2','0-87-444 5555'),('CO40','Tatiana','Marcinkowski','15-900 Bialystok, Wodna 63','0-85-111 5555'),('CO46','Jan','Kowalski','16-300 Augustow, Fabryczna 2','0-87-555 4444'),('CO87','Karol','Frankowski','15-900 Bialystok, Agrestowa 6','0-85-222 6666'),('CO93','Tomasz','Szymañski','15-900 Bialystok, Parkowa 12','0-85-333 4444');
269/*!40000 ALTER TABLE `wlasciciel` ENABLE KEYS */;
270UNLOCK TABLES;
271
272--
273-- Table structure for table `wynajecie`
274--
275
276DROP TABLE IF EXISTS `wynajecie`;
277/*!40101 SET @saved_cs_client = @@character_set_client */;
278/*!40101 SET character_set_client = utf8 */;
279CREATE TABLE `wynajecie` (
280 `umowanr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
281 `nieruchomoscNr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
282 `klientnr` varchar(4) COLLATE utf8_polish_ci NOT NULL,
283 `czynsz` smallint(5) unsigned NOT NULL,
284 `forma_platnosci` varchar(25) COLLATE utf8_polish_ci NOT NULL,
285 `kaucja` smallint(5) unsigned NOT NULL,
286 `zaplacona` tinyint(1) DEFAULT NULL,
287 `od_kiedy` date NOT NULL,
288 `do_kiedy` date NOT NULL,
289 PRIMARY KEY (`umowanr`),
290 KEY `nieruchomoscNr` (`nieruchomoscNr`),
291 KEY `klientnr` (`klientnr`),
292 CONSTRAINT `klientnr` FOREIGN KEY (`klientnr`) REFERENCES `klient` (`klientnr`) ON DELETE CASCADE ON UPDATE CASCADE,
293 CONSTRAINT `nieruchomoscNr` FOREIGN KEY (`nieruchomoscNr`) REFERENCES `nieruchomosc` (`nieruchomoscnr`) ON DELETE CASCADE ON UPDATE CASCADE
294) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
295/*!40101 SET character_set_client = @saved_cs_client */;
296
297--
298-- Dumping data for table `wynajecie`
299--
300
301LOCK TABLES `wynajecie` WRITE;
302/*!40000 ALTER TABLE `wynajecie` DISABLE KEYS */;
303INSERT INTO `wynajecie` VALUES ('1001','B16','CO16',450,'gotówka',900,1,'2003-10-01','2004-03-31'),('1002','B21','CR51',600,'czek',1200,1,'2003-11-01','2004-04-30'),('1003','B17','CO17',400,'gotówka',800,1,'2003-12-01','2004-05-31'),('1004','B18','CR52',350,'karta visa',700,1,'2004-01-01','2004-06-30'),('1005','A14','CR54',650,'karta Visa',1300,1,'2004-02-01','2004-07-31'),('1006','L94','CR53',400,'gotówka',800,1,'2004-03-01','2004-08-31'),('1007','G01','CO18',800,'karta visa',1600,1,'2004-04-01','2004-09-30'),('1011','B16','CO16',450,'gotówka',900,1,'2004-04-01','2004-09-30'),('1012','B21','CR51',600,'gotówka',1200,1,'2004-05-01','2005-04-30'),('1013','B17','CO17',400,'karta visa',800,1,'2004-06-01','2004-11-30'),('1014','B18','CR52',350,'gotówka',700,1,'2004-07-01','2005-06-30'),('1015','A14','CR54',650,'gotówka',1300,1,'2004-08-01','2005-01-31'),('1016','L94','CR53',400,'gotówka',800,0,'2004-09-01','2005-05-30'),('1017','G01','CO18',800,'gotówka',1600,0,'2004-10-01','2005-07-30'),('1021','B16','CO16',450,'gotówka',900,1,'2004-10-01','2005-09-30');
304/*!40000 ALTER TABLE `wynajecie` ENABLE KEYS */;
305UNLOCK TABLES;
306/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
307
308/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
309/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
310/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
311/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
312/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
313/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
314/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
315
316-- Dump completed on 2016-10-02 20:18:33
317
318drop procedure zad1;
319delimiter //
320create procedure zad1(in podwyzka int,in numer varchar(30))
321begin
322update klient set max_czynsz=max_czynsz+podwyzka where klientnr=numer;
323end//
324call zad1(12,'CO16');
325//
326delimiter //
327create procedure zad2(in podwyzka int,in numer varchar(30))
328begin
329update nieruchomosc set czynsz=czynsz+podwyzka where wlascicielNr=numer;
330end//
331//
332call zad2(12,'A14');
333//
334delimiter //
335create procedure zad3(in podwyzka int,in numer varchar(30))
336begin
337update personel set pensja=pensja+podwyzka where personelNr=numer;
338end//
339call zad3(300,'SA9');
340//
341delimiter //
342create function zad4(czynsz int,wlascicielNr varchar(30)) returns varchar(20)
343begin
344declare x varchar(20);
345select wlascicielNr into x from nieruchomosc where czynsz=(select max(czynsz) from nieruchomosc);
346return x;
347end//
348//
349delimiter //
350create function zad5(nr_wlasc varchar(30)) returns varchar(30)
351begin
352declare x varchar(30);
353set x=(select max(czynsz) from nieruchomosc where wlascicielNr=nr_wlasc );
354return x;
355end//
356delimiter ;
357/*zad6*/
358CREATE TABLE usuniete_nieruchomosci (
359 `usunnieruchomoscnr` varchar(4),
360 `ulica` varchar(25),
361 `miasto` varchar(25),
362 `kod` varchar(6),
363 `typ` varchar(25),
364 `pokoje` tinyint(3),
365 `czynsz` smallint(5),
366 `data_usuniecia` date,
367 PRIMARY KEY (`usunnieruchomoscnr`)
368);
369
370delimiter //
371CREATE TRIGGER wyzwalacz
372BEFORE DELETE
373ON nieruchomosc
374FOR EACH ROW
375BEGIN
376
377insert into usuniete_nieruchomosci values(old.nieruchomoscnr, old.ulica, old.miasto, old.kod, old.typ, old.pokoje, old.czynsz, now());
378
379END//
380delimiter ;
381
382select *from nieruchomosc;
383select *from usuniete_nieruchomosci;
384
385INSERT INTO `nieruchomosc` VALUES ('A11','Handlowa 16','Augustów','16-300','dom',6,715,'CO46','SA9','B007');
386delete from nieruchomosc where ulica like 'Handlowa 16';
387
388set sql_safe_updates=0;
389
390/* zadanie 7 */
391create view zadanie7 as
392select count(nieruchomoscnr), biuroNr from nieruchomosc group by biuroNr;
393
394select *from zadanie7;