· 8 years ago · Apr 20, 2018, 10:58 AM
1DROP DATABASE IF EXISTS fakturowanie;
2
3CREATE DATABASE fakturowanie;
4
5USE fakturowanie;
6
7CREATE TABLE wojewodztwa
8(kod_w CHAR(3) not null,
9 PRIMARY KEY (kod_w),
10 wojewodztwo VARCHAR(30) not null
11);
12
13INSERT INTO wojewodztwa (kod_w, wojewodztwo)
14VALUES ('dol', 'dolno零kie'), ('kuj', 'kujawsko-pomorskie'), ('lub','lubelskie'), ('lue','lubuskie'), ('lod','å ¯dzkie'), ('mal','ma這polskie'), ('maz','mazowieckie'), ('opo','opolskie'), ('pod','podkarpackie'), ('pol','podlaskie'), ('pom','pomorskie'), ('sla','零kie'), ('swi','içš»okrzyskie'), ('war','warmi雟ko-mazurskie'), ('wie','wielkopolskie'), ('zac','zachodniopomorskie');
15
16CREATE TABLE miasta
17(kod_pocztowy VARCHAR(6) not null, primary key(kod_pocztowy),
18 kod_w char(3) not null,
19 foreign key (kod_w) references wojewodztwa(kod_w),
20 miasto varchar(30) not null
21);
22
23INSERT INTO miasta (kod_pocztowy, kod_w, miasto)
24VALUES ('50-100', 'dol', 'Wroc豉w'), ('61-772', 'wie', 'Pozna?), ('00-229', 'maz', 'Warszawa'), ('31-010', 'mal', 'Krak闚'), ('80-324', 'pom', 'Gda雟k');
25
26CREATE TABLE kontrahenci(id_k int AUTO_INCREMENT not null, PRIMARY KEY (id_k), nazwa VARCHAR(50) not null, nazwa_skr VARCHAR(20) not null, ulica VARCHAR(30), nr VARCHAR(6), nr2 VARCHAR(6), kod_pocztowy VARCHAR(6), foreign key (kod_pocztowy) references miasta(kod_pocztowy));
27
28INSERT INTO `kontrahenci` (`id_k`, `nazwa`, `nazwa_skr`, `ulica`, `nr`, `nr2`, `kod_pocztowy`) VALUES ('1', 'Polski Koncern Olejowy', 'PKOl', 'Dolna', '2', NULL, '00-229'),
29('2','Kogutek Sp. z o.o.','Kogutek','Prawoazowa','2',6,'00-229'),
30('3','Mirabelka s.a.','Mirabelka','Mi這sna','56',NULL,'00-229'),
31('4','Party Keeper','Party Keeper','Niew零ka','23',NULL,'00-229'),
32('5','Kuskus Mierzejski Wies豉w Co.','Kuskus','Kwaa','12',NULL,'00-229'),
33('6','Gigant Hobby','Gigant','Pokurczona','22','987','50-100'),
34('7','Huta Metali Nieçž laznych','Metale','Demoniczna','1',NULL,'50-100'),
35('8','Szewc Dratewka s.j.','Dratewka','Smoka Wawelskiego','2',NULL,'50-100'),
36('9','Combo Maxi Grand Wypas','Combo','Nieszcz?niké—š','92',123,'50-100'),
37('10','Wy窺za Szko豉 Balonowa','WSBa','Kosmonaut闚','1',NULL,'50-100'),
38('11','Serwis Gitarowy M璚zystruna','M璚zystruna','Sm皻na','2',NULL,'50-100'),
39('12','Zesp馧 Weselny Mezalians','Mezalians','Komercyjna','5',NULL,'50-100'),
40('13','Instytut Pi瘯na - ç•bka','ç•bka','Kacza','2',NULL,'61-772'),
41('14','Hipermarket Zbysio','Zbysio','Nuklearna','7',NULL,'61-772'),
42('15','PHU Kartofelek','Kartofelek','Denna','2',NULL,'61-772'),
43('16','Bar Agatka','Agatka','iat這czu豉','9',NULL,'61-772'),
44('17','Karczma Wioletka','Wioletka','Rozs鉅na','2',NULL,'61-772'),
45('18','Toaleta Miejska','Toaleta Miejska','Sadowa','12',NULL,'31-010'),
46('19','Przytu貫k dla bezdomnych','Przytu貫k dla bezdomnych','Szczepionkowa','3',NULL,'31-010'),
47('20','Targowisko Miejskie','TM','Szczypiorkowa','88',234,'31-010'),
48('21','Dom spokojnej m這doi LUZIK','Dom spokojnej m這doi','Mokrych kapci','102',NULL,'31-010'),
49('22','PH Sklep Obuwniczy Podeszwa','Podeszwa','Wyka豉czkowa','876','4c','31-010'),
50('23','Butik egzotyczny Malwa','Malwa','Naturalna','2',NULL,'80-324'),
51('24','Telewizja Regionalna 3','TR3','Biomedyczna','7',NULL,'80-324'),
52('25','Przedszkole Miejskie nr 4','Przedszkole nr 3','Nerwowa','2',NULL,'80-324'),
53('26','Przedsi瑿iorstwo Informatyczne KWERENDA','Kwerenda','Doskona豉','9',NULL,'80-324'),
54('27','Spy - Monta?Luster Wiede雟kich','Spy','Spokojna','2',NULL,'80-324'),
55('28','Fundacja wspierajé‰a Zamek ï–©edniowieczny','Zamek','Wa這wa','12',NULL,'80-324');
56
57
58
59CREATE TABLE faktury(id_f int(6) AUTO_INCREMENT not null, PRIMARY KEY (id_f), numer varchar(14) not null, data_wystawienia datetime not null, data_sprzedazy datetime not null, data_platnosci datetime null, id_k int, foreign key (id_k) references kontrahenci(id_k));
60
61INSERT INTO `faktury` (`id_f`, `numer`, `data_wystawienia`, `data_sprzedazy`, `data_platnosci`, `id_k`) VALUES (56, 'FA001/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '1'),
62(1, 'FA001/2018', '2018-04-10 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '1'),
63(2, 'FA002/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '1'),
64(3, 'FA003/2018', '2018-04-11 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '3'),
65(4, 'FA004/2018', '2018-04-09 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '3'),
66(5, 'FA005/2018', '2018-04-09 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '3'),
67(6, 'FA006/2018', '2018-04-10 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '5'),
68(7, 'FA007/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '5'),
69(8, 'FA008/2018', '2018-04-11 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '5'),
70(9, 'FA009/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '7'),
71(10, 'FA010/2018', '2018-04-08 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '7'),
72(11, 'FA011/2018', '2018-04-06 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '8'),
73(12, 'FA012/2018', '2018-04-07 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '9'),
74(13, 'FA013/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '9'),
75(14, 'FA014/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '11'),
76(15, 'FA015/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '11'),
77(16, 'FA016/2018', '2018-04-09 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '11'),
78(17, 'FA017/2018', '2018-04-10 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '13'),
79(18, 'FA018/2018', '2018-04-10 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '13'),
80(19, 'FA019/2018', '2018-04-11 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '14'),
81(20, 'FA020/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '14'),
82(21, 'FA021/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '16'),
83(22, 'FA022/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '16'),
84(23, 'FA023/2018', '2018-04-11 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '16'),
85(24, 'FA024/2018', '2018-04-11 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '16'),
86(25, 'FA025/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '19'),
87(26, 'FA026/2018', '2018-04-10 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '19'),
88(27, 'FA027/2018', '2018-04-10 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '19'),
89(28, 'FA028/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '20'),
90(29, 'FA029/2018', '2018-04-10 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '20'),
91(30, 'FA030/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '20'),
92(31, 'FA031/2018', '2018-04-09 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '20'),
93(32, 'FA032/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '21'),
94(33, 'FA033/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '22'),
95(34, 'FA034/2018', '2018-04-09 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '23'),
96(35, 'FA035/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '23'),
97(36, 'FA036/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '24'),
98(37, 'FA037/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '25'),
99(38, 'FA038/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '26'),
100(39, 'FA039/2018', '2018-04-08 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '26'),
101(40, 'FA040/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '27'),
102(41, 'FA041/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '28'),
103(42, 'FA042/2018', '2018-04-07 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '28'),
104(43, 'FA043/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '28'),
105(44, 'FA044/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '28'),
106(45, 'FA045/2018', '2018-04-11 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '17'),
107(46, 'FA046/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '17'),
108(47, 'FA047/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '15'),
109(48, 'FA048/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '6'),
110(49, 'FA049/2018', '2018-04-10 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '26'),
111(50, 'FA050/2018', '2018-04-10 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '26'),
112(51, 'FA051/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '6'),
113(52, 'FA052/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '7'),
114(53, 'FA053/2018', '2018-04-11 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '4'),
115(54, 'FA054/2018', '2018-04-12 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '4'),
116(55, 'FA055/2018', '2018-04-05 00:00:00', '2018-04-12 00:00:00', '2018-04-19 00:00:00', '22');
117
118CREATE TABLE asortyment(id_a int(6) AUTO_INCREMENT not null, PRIMARY KEY (id_a), produkt VARCHAR(20), jm VARCHAR(4), cena decimal(8,2), vat decimal(5,3));
119
120INSERT INTO asortyment (id_a, produkt, jm, cena, vat)
121VALUES
122(1, 'gwozie 5 calowe', 'kg', 23, 0.23),
123(2, 'drut stalowy', 'm', 67.60, 0.23),
124(3, 'uba fi 18', 'szt.', 5, 0.23),
125(4, 'uba fi 22', 'szt.', 7.90, 0.23),
126(5, 'lina stalowa', 'm', 29, 0.23),
127(6, 'teownik', 'szt.', 53, 0.23),
128(7, 'ceg豉 klinkierowa', 'szt.', 8.2, 0.23),
129(8, 'kostka brukowa', 'm2', 59.89, 0.23),
130(9, 'glazura', 'm2', 99.99, 0.23),
131(10, 'gres', 'm2', 89, 0.23),
132(11, 'terakota', 'm2', 78, 0.23),
133(12, 'farba akrylowa', 'l', 123, 0.23),
134(13, 'rozpuszczalnik', 'l', 16.5, 0.23),
135(14, 'listwa', 'szt.', 12.3, 0.23),
136(15, 'panele pod這gowe', 'm2', 97.01, 0.23),
137(16, 'ké–ownik', 'szt.', 23, 0.23),
138(17, 'ceg豉 zwyk豉', 'szt.', 5.09, 0.23),
139(18, 'wieî¶ a dachowa', 'kg', 20, 0.23),
140(19, 'pé€ ta kartonowo-gipsowa', 'm2', 60.06, 0.23),
141(20, 'uszczelka fi 52mm', 'szt.', 8.97, 0.23),
142(21, 'uszczelka fi 44mm', 'szt.', 7.99, 0.23);
143
144CREATE TABLE pozycje(id_p int(6) AUTO_INCREMENT not null, PRIMARY KEY (id_p), id_f int(6), id_a int(6), ilosc decimal(5,2), foreign key (id_f) references faktury(id_f), foreign key (id_a) references asortyment(id_a) );
145
146INSERT INTO pozycje (id_f, id_a, ilosc)
147VALUES
148(1, 17, 4), (1, 12, 500), (1, 7, 12), (2, 1, 3),
149(2, 7, 3), (4, 1, 4), (4, 17, 300), (4, 4, 4),
150(5, 7, 4), (5, 1, 4), (6, 17, 400), (6, 11, 4),
151(6, 7, 3), (6, 1, 4), (7, 17, 500), (7, 12, 4),
152(7, 7, 4), (7, 1, 4), (8, 17, 600), (8, 13, 4),
153(8, 7, 4), (9, 1, 4), (9, 17, 750), (9, 14, 4),
154(10, 7, 4), (10, 1, 4), (10, 17, 800), (10, 14, 4),
155(11, 7, 4), (12, 1, 4), (14, 17, 700), (15, 16, 4),
156(16, 7, 4), (1, 1, 4), (1, 17, 300), (1, 15, 4),
157(17, 7, 4), (17, 1, 4), (17, 17, 200), (17, 2, 4),
158(18, 7, 2), (19, 1, 2), (19, 17, 400), (21, 2, 2),
159(21, 7, 2), (21, 1, 2), (22, 17, 550), (23, 2, 2),
160(24, 7, 2), (25, 1, 2), (26, 17, 2), (27, 3, 2),
161(30, 7, 2), (30, 1, 2), (30, 17, 230), (31, 3, 2),
162(31, 7, 2), (32, 1, 2), (33, 17, 100), (34, 3, 2),
163(35, 7, 2), (36, 1, 2), (36, 17, 2), (37, 3, 2),
164(37, 7, 3), (38, 1, 10), (38, 17, 100), (38, 4, 10),
165(39, 7, 3), (39, 1, 10), (40, 17, 120), (41, 5, 10),
166(41, 7, 3), (41, 1, 10), (41, 17, 444), (41, 6, 10),
167(42, 7, 6), (43, 1, 10), (43, 17, 10), (44, 8, 10),
168(46, 7, 3), (48, 1, 10), (49, 17, 3), (49, 9, 10),
169(51, 7, 3), (51, 1, 10), (52, 17, 6), (53, 10, 10),
170(55, 7, 3), (55, 1, 10), (55, 17, 35), (56, 10, 10);