--TODO sto query no12 : epeidh to mes_onoma einai UNIQUE me vash tis apaitiseis tis askiseis, zitaei na to exei sto group by... omws etsi dn vgazei swsto apotelesma to query. Sto view success: gia kapoio logo i ek8esi eisagetai stin opsi 3-4 fores anti gia 1.. d
--episis dn douleuei k to extra

-- emfanisi twn onomatwn olwn twn kallitexnon

--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'
WHERE dan_imer_ep>'2008-05-01';
--^^to field date tha to dinei o xrhsths

--to mouseio paei gia louketo opote meiwnei to kostos 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);

--mipws na kratisoume mono to deytero ws "maintainance" vashs?

--pe8ane o kalitexnis kakomoiris (allagi6) OK
update artist
set art_im_than=CURRENT_DATE
where art_eponymo='kakomoiris';

--eisagwgi neou mesou diafimisis (allagi7) OK
insert into meso (mes_onoma,mes_typos) values ('MEGA','TV');

--1 vres ton ari8mo ton ek8esewn poy eginan fetos kai ton meso oro episkeptwn tous (aggr1) OK
 select count(*), avg(ekth_visitors)
 from ekthesi
 where ekth_im_enarks>='2008-01-01' and ekth_im_liks<='2008-12-31' and ekth_im_enarks<=CURRENT_DATE;

--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);

--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;

select art_xwra,art_epoxi
from artist
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 
where at_styl='mparok' and at_kwd_syl in
			(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, 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
group by ekth_onoma
having count(*)>=2;

--9 na vre8ei to sunoliko kostos gia ka8e meso diafimisis(aggr3) OK
select mes_typos, sum(diaf_kostos)
from diafimisi,meso
where diaf_mes_kwdik=mes_kwdikos
group by mes_typos;

--10 na emfanistoun ta onomata olon ton ek8eseon pou arxisoun se ena mina k exoun diafimistei mesw internet (exists1) OK
select ekth_onoma from ekthesi where ekth_kwdikos in
(select ekth_kwdikos
from ekthesi
where exists 	(select *
		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 OK
select * --mporei na mpei o titlos kai o kallitexnhs edw sto select
from antik, allo
where at_kwdikos=allo_at_kwdikos;

--dior8wmeno na emfanizei kai eponymo

select art_eponymo, at_titlos, allo_typos
from antik, allo, filot_apo, artist
where at_kwdikos in (select allo_at_kwdikos from allo) and fa_at_kwdikos=at_kwdikos and fa_art_kwdik=art_kwdikos;


--12 to onoma tou mesou diafimisis sto opoio exoun ependu8ei ta perissotera lefta gia ka8e tupo (join4) PROVLHMA IDIO ME 2 
--select a.type, max(m.cost) from advertisement as a,media as m where a.diafimisi_id = m.media_id group by m.type;

--select mes_typos, max(diaf_kostos)
--from diafimisi, meso
--where diaf_mes_kwdik = mes_kwdikos
--group by mes_typos;

--select m.mes_typos, max(d.diaf_kostos) from diafimisi as d, meso as m where a.diaf_mes_kwdik = m.mes_kwdikos group by m.mes_typos;
--enallaktiko

--with diafimisi_meso (typos, onoma, kostos) as
--    select mes_typos, mes_onoma, diaf_kostos
--   from diafimisi, meso
--    where diaf_mes_kwdik = mes_kwdikos
--select typos, onoma, kostos
--from diafimisi_meso
--where (typos, kostos) in
--    (select typos, max(kostos)
--    from diafimisi_meso
--    group by typos);

create temporary view diafimisi_meso (typos, onoma, kostos) as
    select mes_typos, mes_onoma, diaf_kostos
    from diafimisi, meso
    where diaf_mes_kwdik = mes_kwdikos;

select typos, onoma, kostos
from diafimisi_meso
where (typos, kostos) in
    (select typos, max(kostos)
    from diafimisi_meso
    group by typos);

-- or

select mes_typos, mes_onoma, diaf_kostos
from diafimisi inner join meso on diaf_mes_kwdik = mes_kwdikos
where (mes_typos, diaf_kostos) in
    (select mes_typos, max(diaf_kostos)
    from diafimisi inner join meso on diaf_mes_kwdik = mes_kwdikos
    group by mes_typos);

-- or

-- 01:31 < olspookishmagus> ara mporeis na kaneis prwta sort ton pinaka pros to megista tou ka8e typou
-- 01:31 < olspookishmagus> kai meta apo to join na pareis ta onomata
-- 01:31 < olspookishmagus> ta megista*

--13 o ari8mos twn pinakwn modernas texnotropias pou uparxoun sto mouseio se monimh sullogh(join5) OK
select count(*)
from pinakas
where pin_texnotr='modern' and pin_at_kwdikos in (select at_kwdikos from antik where at_daneiko=FALSE);

--13 kai gourika pame na ftia3oume kamia opsi twra

--1 ena view me tis petuximenes ek8eseis (>10000 eisitiria) k to diafimistiko tous kostos. TIN VGAZEI 3 FORES TIN IDIA EGGRAFH DN 3ERO GIATI
<<<<<<< .mine
create view sucess (Onoma, Thema, Hmeromonia_Enarksis, eishthria,  kostos)
as select ekth_onoma, ekth_thema, ekth_im_enarks, ekth_ticket, diaf_kostos
from ekthesi, diafimisi 
where ekth_visitors>10000 and ekth_kwdikos in (select ekth_kwdikos from ekthesi, diafimisi where ekth_kwdikos=diaf_ekth_kwdik) 
=======
--create view success (onoma, thema, hmeromonia_enarksis, eishthria, diafimisi) as
--    select ekth_onoma, ekth_thema, ekth_im_enarks, ekth_ticket, diaf_kostos
--    from ekthesi, diafimisi
--    where ekth_visitors > 10000 and ekth_kwdikos in (select ekth_kwdikos from ekthesi, diafimisi where ekth_kwdikos=diaf_ekth_kwdik);
>>>>>>> .r54

--create view success (onoma, thema, esoda, eksoda) as
--    select ekth_onoma, ekth_thema, ekth_ticket * ekth_visitors, diaf_kostos
--    from ekthesi, diafimisi
--    where ekth_visitors > 10000
--    and ekth_kwdikos in
--        (select ekth_kwdikos 
--        from ekthesi, diafimisi
--        where ekth_kwdikos = diaf_ekth_kwdik)
--    and diaf_kostos in
--        (select sum(diaf_kostos)
--        from diafimisi inner join ekthesi on diaf_ekth_kwdik = ekth_kwdikos);


create view success (onoma, esoda, eksoda) as
    select ekth_onoma, (ekth_ticket * ekth_visitors) as tickvis, sum(diaf_kostos)
    from diafimisi inner join ekthesi on diaf_ekth_kwdik = ekth_kwdikos
    where ekth_visitors > 10000
    group by ekth_onoma, tickvis;

--2 ena view pou perilamvanei antikeimena pou exoume einai daneismena k epistrefontai sintoma mazi me onoma upeu8unou k tilefono - OK
<<<<<<< .mine
create view todo (antikeimeno, due_date, eponimo_upeu8unou, tilefwno)
as select at_titlos, dan_imer_ep, eks_ep_ypefth, til_til
from antik, dan_se, eks_syllogh, til_ypefth 
where dan_imer_ep-CURRENT_DATE<50 and dan_imer_ep>=CURRENT_DATE  and at_kwdikos=dan_at_kwdikos and eks_kwdikos=dan_eks_kwdikos and eks_ep_ypefth=til_ep_ypefth;

SELECT * FROM todo;
=======
create view todo (antikeimeno, due_date, eponimo_upeu8unou, tilefwno) as
    select at_titlos, dan_imer_ep, eks_ep_ypefth, til_til
    from antik, dan_se, eks_syllogh, til_ypefth 
    where dan_imer_ep-CURRENT_DATE<5 and dan_imer_ep>=CURRENT_DATE and at_kwdikos=dan_at_kwdikos and eks_kwdikos=dan_eks_kwdikos and eks_ep_ypefth=til_ep_ypefth;
>>>>>>> .r54