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