EKYb4Dsf

1drop table if exists diamerisma CASCADE;
2drop table if exists katoikos CASCADE;
3drop table if exists dimotis CASCADE;
4drop table if exists eklogiko_kentro CASCADE;
5drop table if exists dimopsifisma CASCADE;
6drop table if exists psifodeltio CASCADE;
7drop table if exists epilogi CASCADE;
8drop table if exists psifizei CASCADE;
9
10create table diamerisma(
11onomasia_dd varchar(60) not null,
12primary key (onomasia_dd));
13
14create table katoikos(
15adt varchar(10) not null,
16onoma_kat varchar(60) not null,
17epitheto_kat varchar(60) not null,
18address_kat varchar(60) not null,
19onomasia_dd varchar(60) not null,
20foreign key (onomasia_dd) references diamerisma(onomasia_dd),
21primary key (adt));
22
23create table dimotis(
24adt varchar(10) not null,
25ar_dimotologiou int not null,
26foreign key (adt) references katoikos(adt),
27primary key (adt));
28
29create table eklogiko_kentro(
30kwdikos_kentrou varchar(10) not null,
31onomasia_kentrou varchar(60) not null,
32address_kentrou varchar(60) not null,
33tilefwno_kentrou varchar(10) not null,
34onomasia_dd varchar(60) not null,
35foreign key (onomasia_dd) references diamerisma(onomasia_dd),
36primary key (kwdikos_kentrou));
37
38create table dimopsifisma(
39kwdikos_dimopsif varchar(10) not null,
40perigrafi_dimopsif varchar(100) not null,
41primary key (kwdikos_dimopsif));
42
43create table epilogi(
44kwdikos_dimopsif varchar(10) not null,
45perigrafi_deltiou varchar(60) not null,
46foreign key (kwdikos_dimopsif) references dimopsifisma(kwdikos_dimopsif),
47primary key (kwdikos_dimopsif,perigrafi_deltiou));
48
49create table psifodeltio(
50kwdikos_dimopsif varchar(10) not null,
51aa_deltiou int not null,
52katastasi varchar(10) not null,
53kwdikos_kentrou varchar(10) not null,
54perigrafi_deltiou varchar(60) not null,
55foreign key (kwdikos_kentrou) references eklogiko_kentro(kwdikos_kentrou),
56foreign key (kwdikos_dimopsif,perigrafi_deltiou) references epilogi(kwdikos_dimopsif,perigrafi_deltiou),
57primary key (kwdikos_dimopsif,aa_deltiou));
58
59create table psifizei(
60kwdikos_dimopsif varchar(10) not null,
61adt varchar(10) not null,
62kwdikos_kentrou varchar(10) not null,
63foreign key (kwdikos_kentrou) references eklogiko_kentro(kwdikos_kentrou),
64foreign key (kwdikos_dimopsif) references dimopsifisma(kwdikos_dimopsif),
65foreign key (adt) references dimotis(adt),
66primary key (kwdikos_dimopsif,adt));
67
68INSERT INTO diamerisma(onomasia_dd) VALUES ('Gaziou');
69INSERT INTO diamerisma(onomasia_dd) VALUES ('Stauromenos');
70INSERT INTO diamerisma(onomasia_dd) VALUES ('Agiou Iwannh');
71
72INSERT INTO katoikos(adt, onoma_kat, epitheto_kat, address_kat, onomasia_dd) VALUES ('AA-9911', 'Ioshfidhs', 'Petros', 'ADDRESS 1', 'Gaziou');
73INSERT INTO katoikos(adt, onoma_kat, epitheto_kat, address_kat, onomasia_dd) VALUES ('AA-7777', 'Aleksandrou', 'Triadafilos', 'ADDRESS 2', 'Gaziou');
74INSERT INTO katoikos(adt, onoma_kat, epitheto_kat, address_kat, onomasia_dd) VALUES ('AA-5555', 'Paterakhs', 'Emmanouhl', 'ADDRESS 3', 'Stauromenos');
75INSERT INTO katoikos(adt, onoma_kat, epitheto_kat, address_kat, onomasia_dd) VALUES ('AA-3333', 'Psalidhs', 'Iwannhs', 'ADDRESS 4', 'Agiou Iwannh');
76INSERT INTO katoikos(adt, onoma_kat, epitheto_kat, address_kat, onomasia_dd) VALUES ('AA-8888', 'Lazarou', 'Mixahl', 'ADDRESS 5', 'Agiou Iwannh');
77INSERT INTO katoikos(adt, onoma_kat, epitheto_kat, address_kat, onomasia_dd) VALUES ('AA-4444', 'Tsagkarakhs', 'Stylianos', 'ADDRESS 6', 'Stauromenos');
78
79INSERT INTO dimotis(adt, ar_dimotologiou) VALUES ('AA-9911', 1222999);
80INSERT INTO dimotis(adt, ar_dimotologiou) VALUES ('AA-7777', 1344455);
81INSERT INTO dimotis(adt, ar_dimotologiou) VALUES ('AA-8888', 1666888);
82INSERT INTO dimotis(adt, ar_dimotologiou) VALUES ('AA-4444', 1777555);
83
84INSERT INTO eklogiko_kentro(kwdikos_kentrou, onomasia_kentrou, address_kentrou, tilefwno_kentrou, onomasia_dd) VALUES ('EK_01', 'GAZIOU A', 'ODOS A 1','2810222222', 'Gaziou');
85INSERT INTO eklogiko_kentro(kwdikos_kentrou, onomasia_kentrou, address_kentrou, tilefwno_kentrou, onomasia_dd) VALUES ('EK_02', 'GAZIOU B', 'ODOS A 2','2810555555', 'Gaziou');
86INSERT INTO eklogiko_kentro(kwdikos_kentrou, onomasia_kentrou, address_kentrou, tilefwno_kentrou, onomasia_dd) VALUES ('EK_03', 'STAUROMENOS A', 'ODOS B 1','2810666666', 'Stauromenos');
87INSERT INTO eklogiko_kentro(kwdikos_kentrou, onomasia_kentrou, address_kentrou, tilefwno_kentrou, onomasia_dd) VALUES ('EK_04', 'STAUROMENOS B', 'ODOS B 2','2810777777', 'Stauromenos');
88INSERT INTO eklogiko_kentro(kwdikos_kentrou, onomasia_kentrou, address_kentrou, tilefwno_kentrou, onomasia_dd) VALUES ('EK_05', 'AGIOU IWANNH A', 'ODOS C 1','2810888888', 'Agiou Iwannh');
89INSERT INTO eklogiko_kentro(kwdikos_kentrou, onomasia_kentrou, address_kentrou, tilefwno_kentrou, onomasia_dd) VALUES ('EK_06', 'AGIOU IWANNH B', 'ODOS C 2','2810999999', 'Agiou Iwannh');
90
91INSERT INTO epilogi(kwdikos_dimopsif, perigrafi_deltiou) VALUES ('DIM-01', 'KRHTH');
92INSERT INTO epilogi(kwdikos_dimopsif, perigrafi_deltiou) VALUES ('DIM-01', 'RODOS');
93INSERT INTO epilogi(kwdikos_dimopsif, perigrafi_deltiou) VALUES ('DIM-01', 'ZAKYNTHOS');
94INSERT INTO epilogi(kwdikos_dimopsif, perigrafi_deltiou) VALUES ('DIM-02', 'ELLADA');
95INSERT INTO epilogi(kwdikos_dimopsif, perigrafi_deltiou) VALUES ('DIM-02', 'HNWMENO BASILEIO');
96INSERT INTO epilogi(kwdikos_dimopsif, perigrafi_deltiou) VALUES ('DIM-02', 'SOUIDIA');
97
98INSERT INTO dimopsifisma(kwdikos_dimopsif, perigrafi_dimopsif) VALUES ('DIM-01', 'Ποια επιλογη θα επιλεγατε για θερινες διακοπες?');
99INSERT INTO dimopsifisma(kwdikos_dimopsif, perigrafi_dimopsif) VALUES ('DIM-02', 'Που θα θελατε να εργαστειτε μετα τη ληψη του πτυχιου σας?');
100
101INSERT INTO psifodeltio(kwdikos_dimopsif, aa_deltiou, katastasi, kwdikos_kentrou, perigrafi_deltiou) VALUES ('DIM-01', 220,'EGKIRO', 'EK_01', 'KRHTH');
102INSERT INTO psifodeltio(kwdikos_dimopsif, aa_deltiou, katastasi, kwdikos_kentrou, perigrafi_deltiou) VALUES ('DIM-01', 221, 'EGKIRO', 'EK_01', 'KRHTH');
103INSERT INTO psifodeltio(kwdikos_dimopsif, aa_deltiou, katastasi, kwdikos_kentrou, perigrafi_deltiou) VALUES ('DIM-01', 222, 'EGKIRO', 'EK_03', 'KRHTH');
104INSERT INTO psifodeltio(kwdikos_dimopsif, aa_deltiou, katastasi, kwdikos_kentrou, perigrafi_deltiou) VALUES ('DIM-01', 223, 'AKIRO', 'EK_06', 'RODOS');
105INSERT INTO psifodeltio(kwdikos_dimopsif, aa_deltiou, katastasi, kwdikos_kentrou, perigrafi_deltiou) VALUES ('DIM-02', 111, 'EGKIRO', 'EK_01', 'ELLADA');
106INSERT INTO psifodeltio(kwdikos_dimopsif, aa_deltiou, katastasi, kwdikos_kentrou, perigrafi_deltiou) VALUES ('DIM-02', 112, 'EGKIRO', 'EK_01', 'SOUIDIA');
107INSERT INTO psifodeltio(kwdikos_dimopsif, aa_deltiou, katastasi, kwdikos_kentrou, perigrafi_deltiou) VALUES ('DIM-02', 113, 'EGKIRO', 'EK_03', 'HNWMENO BASILEIO');
108INSERT INTO psifodeltio(kwdikos_dimopsif, aa_deltiou, katastasi, kwdikos_kentrou, perigrafi_deltiou) VALUES ('DIM-02', 114, 'EGKIRO', 'EK_06', 'HNWMENO BASILEIO');
109
110INSERT INTO psifizei(kwdikos_dimopsif, adt, kwdikos_kentrou) VALUES ('DIM-01', 'AA-9911', 'EK_01');
111INSERT INTO psifizei(kwdikos_dimopsif, adt, kwdikos_kentrou) VALUES ('DIM-01', 'AA-7777', 'EK_01');
112INSERT INTO psifizei(kwdikos_dimopsif, adt, kwdikos_kentrou) VALUES ('DIM-01', 'AA-8888', 'EK_06');
113INSERT INTO psifizei(kwdikos_dimopsif, adt, kwdikos_kentrou) VALUES ('DIM-01', 'AA-4444', 'EK_03');
114INSERT INTO psifizei(kwdikos_dimopsif, adt, kwdikos_kentrou) VALUES ('DIM-02', 'AA-9911', 'EK_01');
115INSERT INTO psifizei(kwdikos_dimopsif, adt, kwdikos_kentrou) VALUES ('DIM-02', 'AA-7777', 'EK_01');
116INSERT INTO psifizei(kwdikos_dimopsif, adt, kwdikos_kentrou) VALUES ('DIM-02', 'AA-8888', 'EK_06');
117INSERT INTO psifizei(kwdikos_dimopsif, adt, kwdikos_kentrou) VALUES ('DIM-02', 'AA-4444', 'EK_03');
118
119SELECT adt,epitheto_kat,onoma_kat from katoikos where adt not IN (select adt from dimotis);
120
121SELECT adt from dimotis where adt not IN (select adt from psifizei);
122
123SELECT onoma_kat, epitheto_kat from katoikos where adt IN(SELECT adt from psifizei where kwdikos_dimopsif='DIM-01');
124--select onoma_kat,epitheto_kat from katoikos as k join psifizei as ps on ps.adt=k.adt and ps.kwdikos_dimopsif='DIM-01' and ps.adt in (select adt from dimotis);
125
126SELECT onoma_kat, epitheto_kat from katoikos where adt not IN(SELECT adt from psifizei where kwdikos_dimopsif='DIM-01');
127
128SELECT onoma_kat, epitheto_kat from katoikos where adt IN (SELECT adt from psifizei where kwdikos_dimopsif = 'DIM-01' and kwdikos_kentrou = 'EK_01');
129
130SELECT count(kwdikos_dimopsif) from psifodeltio where kwdikos_dimopsif='DIM-01';
131
132SELECT count(katastasi) from psifodeltio where kwdikos_dimopsif= 'DIM-01' and katastasi='EGKIRO';
133
134SELECT (SELECT COUNT(perigrafi_deltiou) from psifodeltio where  psifodeltio.perigrafi_deltiou='KRHTH' and psifodeltio.kwdikos_dimopsif='DIM-01'),(SELECT count(perigrafi_deltiou) FROM psifodeltio where  psifodeltio.perigrafi_deltiou='RODOS' and psifodeltio.kwdikos_dimopsif='DIM-01'),(SELECT count(perigrafi_deltiou) FROM psifodeltio where psifodeltio.perigrafi_deltiou='ZAKYNTHOS' and psifodeltio.kwdikos_dimopsif='DIM-01'),(SELECT COUNT(perigrafi_deltiou) from psifodeltio where psifodeltio.perigrafi_deltiou='ELLADA' and psifodeltio.kwdikos_dimopsif='DIM-02'),(SELECT COUNT(perigrafi_deltiou) from psifodeltio where psifodeltio.perigrafi_deltiou='SOUIDIA' and psifodeltio.kwdikos_dimopsif='DIM-02'),(SELECT COUNT(perigrafi_deltiou) from psifodeltio where psifodeltio.perigrafi_deltiou='HNWMENO BASILEIO' and psifodeltio.kwdikos_dimopsif='DIM-02');
135
136SELECT (SELECT COUNT(perigrafi_deltiou) from psifodeltio where  psifodeltio.perigrafi_deltiou='KRHTH' and psifodeltio.kwdikos_dimopsif='DIM-01'),(SELECT count(perigrafi_deltiou) FROM psifodeltio where  psifodeltio.perigrafi_deltiou='RODOS' and psifodeltio.kwdikos_dimopsif='DIM-01'),(SELECT count(perigrafi_deltiou) FROM psifodeltio where psifodeltio.perigrafi_deltiou='ZAKYNTHOS' and psifodeltio.kwdikos_dimopsif='DIM-01');
137
138Select count(perigrafi_deltiou) from psifodeltio where   psifodeltio.kwdikos_dimopsif = 'DIM-02' and psifodeltio.kwdikos_kentrou = 'EK_01';
139
140SELECT (select count(kwdikos_dimopsif ) from epilogi where kwdikos_dimopsif='DIM-01'),(SELECT COUNT(perigrafi_deltiou) from psifodeltio where  psifodeltio.perigrafi_deltiou='KRHTH' and psifodeltio.kwdikos_dimopsif='DIM-01'),(SELECT count(perigrafi_deltiou) FROM psifodeltio where  psifodeltio.perigrafi_deltiou='RODOS' and psifodeltio.kwdikos_dimopsif='DIM-01'),(SELECT count(perigrafi_deltiou) FROM psifodeltio where psifodeltio.perigrafi_deltiou='ZAKYNTHOS' and psifodeltio.kwdikos_dimopsif='DIM-01'),(SELECT COUNT(perigrafi_deltiou) from psifodeltio where psifodeltio.perigrafi_deltiou='ELLADA' and psifodeltio.kwdikos_dimopsif='DIM-02'),(SELECT COUNT(perigrafi_deltiou) from psifodeltio where psifodeltio.perigrafi_deltiou='SOUIDIA' and psifodeltio.kwdikos_dimopsif='DIM-02'),(SELECT COUNT(perigrafi_deltiou) from psifodeltio where psifodeltio.perigrafi_deltiou='HNWMENO BASILEIO' and psifodeltio.kwdikos_dimopsif='DIM-02');
141
142--select onomasia_kentrou,count(aa_deltiou) from eklogiko_kentro natural join psifodeltio where psifodeltio.katastasi='AKIRO'
143--GROUP BY eklogiko_kentro.onomasia_kentrou
144
145--select onomasia_kentrou,count(aa_deltiou) from eklogiko_kentro natural join psifodeltio group by eklogiko_kentro.onomasia_kentrou having count(aa_deltiou) < 4;
146
147select onoma_kat,epitheto_kat,ar_dimotologiou from katoikos natural join dimotis where dimotis.adt not in (select distinct adt from psifizei)
148
149--ksana to 10
150select count(adt) as psifoi, psifizei.kwdikos_dimopsif,psifizei.kwdikos_kentrou from psifizei where kwdikos_dimopsif='DIM-02' and kwdikos_kentrou='EK_01'
151GROUP BY psifizei.kwdikos_dimopsif, psifizei.kwdikos_kentrou
152