Index: db.sql===================================================================--- db.sql (revision 43)+++ db.sql (working copy)@@ -102,21 +102,22 @@ ); CREATE TABLE ektith (- ekth_at_kwdikos integer REFERENCES antik(at_kwdikos) ON DELETE CASCADE, --na allaxte se RESTRICT- ekth_ekth_kwdikos integer REFERENCES ekthesi(ekth_kwdikos) ON DELETE CASCADE, + ekth_at_kwdikos integer REFERENCES antik(at_kwdikos) ON DELETE CASCADE, --na allaxte se RESTRICT+ ekth_ekth_kwdikos integer REFERENCES ekthesi(ekth_kwdikos) ON DELETE CASCADE, PRIMARY KEY (ekth_at_kwdikos, ekth_ekth_kwdikos) ); CREATE TABLE meso (- mes_kwdikos serial PRIMARY KEY,- mes_onoma varchar(16) UNIQUE,- mes_typos varchar(16)+ mes_kwdikos serial PRIMARY KEY,+ mes_onoma varchar(16) UNIQUE,+ mes_typos varchar(16),+ CHECK (mes_typos in ('tv', 'radio', 'efimerida', 'internet')) ); CREATE TABLE diafimisi (- diaf_ekth_kwdik integer REFERENCES ekthesi(ekth_kwdikos) ON DELETE RESTRICT,- diaf_mes_kwdik integer REFERENCES meso(mes_kwdikos) ON DELETE RESTRICT,- diaf_date date,- diaf_kostos money,+ diaf_ekth_kwdik integer REFERENCES ekthesi(ekth_kwdikos) ON DELETE RESTRICT,+ diaf_mes_kwdik integer REFERENCES meso(mes_kwdikos) ON DELETE RESTRICT,+ diaf_date date,+ diaf_kostos money, PRIMARY KEY (diaf_ekth_kwdik, diaf_mes_kwdik, diaf_date) );Index: queries.sql===================================================================--- queries.sql (revision 41)+++ queries.sql (working copy)@@ -3,40 +3,48 @@ -- emfanisi twn onomatwn olwn twn kallitexnon-select distinct k.art_eponymo-from artist k;+--select distinct k.art_eponymo+--from artist k;+--select distinct art_eponymo from artist;+--^^ den exei nohma to distinct+++ --logw apografis ek8ematwn ola ta danismena prepei na epistrafoun stis 1/5/2008 (allagi1) OK update dan_se set dan_imer_ep = '2008-05-01';+--^^to field date tha to dinei o xrhsths-SELECT * FROM dan_se;+--to mouseio paei gia louketo opote meiwnei to kostos diafimisis se efimerides kata 50% (allagi2) OK---to mouseio paei gia louketo opote meionei to kwstos diafimisis se efimerides kata 50% (allagi2) OK- update diafimisi set diaf_kostos = diaf_kostos*0.50 from meso where mes_typos='efimerida';+--^^ omoiws gia pososto kai meso --oi ek8eseis pou arxisan fetos pairnoun paratasi mexri tis diakopes tou Pasxa (allagi3) OK- update ekthesi set ekth_im_liks = '2008-04-20' where ekth_im_enarks>='2008-01-01';+--^^ omoiws gia tis 2 imerominies+ --au3isiki tis timis tou eisitiriou kata 5% twn ek8esewn me pano apo 200 episkeptes (allagi4) OK update ekthesi set ekth_ticket=ekth_ticket*1.05 where ekth_visitors>200;+--....+ --afairesi olwn twn ergwn tou kallitexni Picasso mazi me ton idio ton kallitexni kai osous kallitexnes dn exoun erga (allagi5) OK- delete from antik where at_kwdikos in (select fa_at_kwdikos from filot_apo, artist where fa_art_kwdik=art_kwdikos and art_eponymo='Picasso');-delete from artist where art_kwdikos not in ( select fa_art_kwdik from filot_apo);+delete from artist where art_kwdikos not in (select fa_art_kwdik from filot_apo);+--mipws na kratisoume mono to deytero ws "maintainance" vashs? --pe8ane o kalitexnis kakomoiris (allagi6) OK update artist@@ -50,20 +58,32 @@ select ekth_onoma, max(ekth_visitors) from ekthesi where ekth_im_enarks>='2008-01-01' and ekth_im_liks<='2008-12-31'-group by ekth_onoma;+group by ekth_onoma; --giati group by? --2 gia ka8e xwra emfanise ton giraiotero kallitexnh (aggr2) gia na exei pio poly noima 8a prepei kapws na to kanoume na emfanizetai kai o to onoma artist+--select art_xwra, min(art_im_genn) from artist group by art_xwra;+select art_xwra, art_onoma, art_eponymo, art_im_genn+from artist +where (art_im_genn, art_xwra) in+ (select min(art_im_genn), art_xwra + from artist + group by art_xwra);-select art_xwra, min(art_im_genn) from artist group by art_xwra;+--3 emfanise ka8e xwra pou ekproswpeitai apo toulaxiston 2 kallitexnes Renaissance (having1) OK+--select art_xwra+--from artist+--where art_epoxi='Renaissance'+--group by art_xwra+--having count(*)>=2;----3 emfanise ka8e xwra pou ekproswpeitai apo toulaxiston 2 kallitexnes Renaissance (having1) OK-select art_xwra+select art_xwra,art_epoxi from artist-where art_epoxi='Renaissance'-group by art_xwra+where art_epoxi in (select distinct art_epoxi from artist)+group by art_xwra,art_epoxi having count(*)>=2;+--emfanizei olous tous syndiasmous xwrwn kai styl, mporoume na kanoume metavliti to >=$x+ --4 emfanise tous titlous ton antikeimenwn stul mparok pou einai stin monimi sullogh select at_titlos, at_perigrafh from antik @@ -71,29 +91,33 @@ (select at_kwd_syl from syllogh where syl_onoma='Monimh syllogh');+--metavliti to mparok? --5 emfanise ta erga pou filotexni8ikan stin xwra katagogis tou dimiourgou tous k tin xwra gennisis dimiourgou (join1) OK+ select at_titlos, art_eponymo, at_katagwgh-from antik, artist-where at_katagwgh=art_xwra+from antik, artist, filot_apo+where at_katagwgh=art_xwra and art_kwdikos=fa_art_kwdik and at_kwdikos=fa_at_kwdikos --kati den pigaine kala prin e? group by at_titlos, art_eponymo, at_katagwgh;- --7 emfanise ola ta antikeimena pou einai daneismena k prepei na epistrafoun entos 15 imeron mazi me onoma upeu8unou (join3) OK select at_titlos, eks_ep_ypefth, eks_on_ypefth, til_ypefth from eks_syllogh, se_dan, antik, til_ypefth where se_imer_ep-CURRENT_DATE<15 and eks_kwdikos=se_eks_kwdikos and se_imer_ep-CURRENT_DATE>=0 and se_at_kwdikos=at_kwdikos and eks_ep_ypefth=til_ep_ypefth;+--? --6 na upen8umisoume ston upeu8uno oti prepei na mas epistrepsei antikeimmeno suntoma k pio antikeimeno OK select at_titlos, eks_ep_ypefth, til_til, dan_imer_ep from eks_syllogh, dan_se, til_ypefth, antik where dan_imer_ep-CURRENT_DATE<10 and dan_imer_ep-CURRENT_DATE>=0 and eks_kwdikos=dan_eks_kwdikos and eks_ep_ypefth=til_ep_ypefth and at_kwdikos=dan_at_kwdikos;+--?+ --8 emfanisi olon ton trexouson ek8eseon me panw apo 2 ek8emata (having2) OK select ekth_onoma from ekthesi, ektith-where ekth_im_enarks<CURRENT_DATE and ekth_im_liks>CURRENT_DATE and ekth_ekth_kwdikos=ekth_kwdikos+where ekth_im_enarks<CURRENT_DATE and ekth_im_liks>CURRENT_DATE and ekth_ekth_kwdikos=ekth_kwdikos group by ekth_onoma having count(*)>=2;@@ -111,9 +135,10 @@ from meso, diafimisi where mes_typos='internet' and mes_kwdikos=diaf_mes_kwdik) and ekth_im_enarks-CURRENT_DATE<30);+--kourasmeno :( --11 parousiasi olwn twn antikeimenwn texnhs pou dn exoun perilif8ei se kapoia katigoria LOGIKA OK ALLA DN EXOUME DATA-select *+select * --mporei na mpei o titlos kai o kallitexnhs edw sto select from antik, allo where at_kwdikos=allo_at_kwdikos;@@ -123,6 +148,36 @@ where diaf_mes_kwdik=mes_kwdikos group by mes_typos;++with diafimisi_meso (value,kwdikos) as+ select max(diaf_kostos), diaf_mes_kwdik+ from diafimisi+ group by diaf_mes_kwdik+++select mes_typos, mes_onoma, diaf_kostos+from diafimisi, meso+where (diaf_mes_kwdik=mes_kwdikos) and (mes_typos, diaf_kostos) in+ (select mes_typos, max(diaf_kostos)+ from diafimisi, meso+ group by mes_typos);++--max diafimisi gia kathe meso+select max(diaf_kostos), diaf_mes_kwdik+from diafimisi+group by diaf_mes_kwdik;+++select art_xwra, art_onoma, art_eponymo, art_im_genn+from artist +where (art_im_genn, art_xwra) in+ (select min(art_im_genn), art_xwra+ from artist + group by art_xwra);+++with max_diafimistiko_kostos (value) + --13 o ari8mos twn pinakwn modernas texnotropias pou uparxoun sto mouseio se monimh sullogh(join5) OK select count(*) from pinakas