set pagesize 1000 set echo on set markup html on spool on head "<title>X36DBS - ÄŚtvrtek 7.30 - ZbynÄ›k LstibĹŻrek - VĂ˝stup SQL pĹ™ĂkazĹŻ </title> <style type ='text/css'><!--body {background: ffffc6} --></style>" body "<h2>X36DBS - ÄŚtvrtek 7.30 - ZbynÄ›k LstibĹŻrek - VĂ˝stup SQL pĹ™ĂkazĹŻ</h2>" spool dotazy.html -- Seznam koncertĹŻ, na nichĹľ bylo vĂce neĹľ 100 divákĹŻ SELECT * FROM Koncert WHERE divaku > 100; -- Seznam zpÄ›vákĹŻ, jejichĹľ vedoucĂm je Alena HavlĂková. SELECT Zpevak.* FROM Zpevak JOIN ( SELECT zkratka FROM Vedeni NATURAL JOIN Zpevak WHERE jmeno='Alena' AND prijmeni='HavlĂková' ) ON hlas=zkratka; -- StejnĂ© zadánĂ jako pĹ™edchozĂ, nynĂ s poddotazem v klauzuli WHERE. SELECT * FROM Zpevak WHERE hlas = ( SELECT zkratka FROM Vedeni NATURAL JOIN Zpevak WHERE jmeno='Alena' AND prijmeni='HavlĂková' ); -- Názvy všech skladeb A. Dvořáka v repertoiru. SELECT nazev FROM Skladba NATURAL JOIN Autorstvi NATURAL JOIN Skladatel WHERE jmeno='AntonĂn' AND prijmeni='Dvořák'; -- Seznam zpÄ›vákĹŻ, kteřà nemajĂ zapĹŻjÄŤeny šaty. SELECT * FROM Zpevak MINUS SELECT Zpevak.* FROM Zpevak JOIN Zapujcka_satu ON Zpevak.RC = Zapujcka_satu.RC; -- StejnĂ© zadánĂ jako pĹ™edchozĂ, Ĺ™ešenĂ pomocĂ poddotazu. SELECT * FROM Zpevak WHERE RC NOT IN (SELECT RC FROM Zapujcka_satu); -- Seznam dosud neprovedenĂ˝ch skladeb (nebyly na programu žádnĂ©ho koncertu), vÄŤetnÄ› autorĹŻ. SELECT jmeno, prijmeni, nazev FROM ( SELECT id, nazev FROM Skladba MINUS SELECT id, nazev FROM Skladba NATURAL JOIN Cast_programu NATURAL JOIN Koncert ) LEFT JOIN Autorstvi USING (id) LEFT JOIN Skladatel USING(jmeno, prijmeni); -- Seznam vĂcedĂlnĂ˝ch skladeb. SELECT jmeno, prijmeni, nazev FROM (SELECT * FROM Skladba WHERE id IN (SELECT id FROM Cast_skladby) )LEFT JOIN Autorstvi USING (id) LEFT JOIN Skladatel USING(jmeno, prijmeni); -- Program koncertu 18. 1. 2005. ALTER Session SET NLS_DATE_FORMAT='DD.MM.YYYY'; SELECT jmeno, prijmeni, nazev FROM Skladatel JOIN Autorstvi using (jmeno, prijmeni) JOIN Skladba using (id) JOIN Cast_programu using (id) JOIN Koncert using (misto, cas) WHERE TRUNC(cas)=TRUNC(TO_DATE('18.01.2005')); -- Seznam zpÄ›vákĹŻ se 100% docházkou. SELECT Zpevak.* FROM Zpevak JOIN ( SELECT DISTINCT RC FROM Ucast MINUS SELECT DISTINCT RC FROM Ucast WHERE pritomen=0 ) Vzorni ON Zpevak.RC=Vzorni.RC; -- StejnĂ˝ dotaz jako pĹ™edchozĂ, tentokrát Ĺ™ešenĂ˝ poddotazem. SELECT * FROM Zpevak WHERE RC NOT IN ( SELECT RC FROM Ucast WHERE pritomen=0 ); -- OpÄ›t stejnĂ˝ dotaz Ĺ™ešenĂ˝ vztaĹľenĂ˝m poddotazem. SELECT * FROM Zpevak WHERE NOT EXISTS ( SELECT * FROM Ucast WHERE pritomen=0 AND Ucast.RC=Zpevak.RC ); -- Seznam zpÄ›vákĹŻ, kteřà nemajĂ noty na ÄŚtyĹ™i pĂsnÄ› o Marii. SELECT jmeno, prijmeni FROM Zpevak MINUS SELECT jmeno, prijmeni FROM Zpevak JOIN Zapujcka_not using (RC) JOIN Publikace using (kat_cislo) JOIN Zarazeni using (kat_cislo) JOIN Skladba using (id) WHERE nazev='ÄŚtyĹ™i pĂsnÄ› o Marii'; -- SkladatelĂ©, kteřà jsou autorem nÄ›jakĂ© skladby a zároveĹ upravovatelem nÄ›jakĂ© skladby. SELECT jmeno, prijmeni FROM Skladatel JOIN Autorstvi using (jmeno, prijmeni) WHERE je_uprava=0 INTERSECT SELECT jmeno, prijmeni FROM Skladatel JOIN Autorstvi using (jmeno, prijmeni) WHERE je_uprava<>0; -- Seznam publikacĂ, od kterĂ˝ch je v inventáři mĂ©nÄ› neĹľ pÄ›t exemplářů. PrvnĂ realizaci mĹŻĹľeme zjednodušit tak, vybereme ty publikace, kde existujĂ jen exempláře s poĹ™adovĂ˝mi ÄŤĂsly < 5. SELECT * FROM Publikace A WHERE NOT EXISTS ( SELECT * FROM Vytisk WHERE A.kat_cislo=Vytisk.kat_cislo AND cislo_exemplare >= 5); -- StejnĂ˝ dotaz s pouĹľitĂm agregace, navĂc bez výše pouĹľitĂ©ho zjednodušeni. SELECT * FROM Publikace WHERE 5 > (SELECT COUNT(*) FROM Vytisk WHERE Publikace.kat_cislo=Vytisk.kat_cislo); -- Seznam publikacĂ, kterĂ© obsahujĂ vĂce neĹľ jednu skladbu. SELECT * FROM Publikace NATURAL JOIN ( SELECT DISTINCT A.kat_cislo FROM Zarazeni A JOIN Zarazeni B ON A.kat_cislo=B.kat_cislo AND A.id < B.id ); -- StejnĂ˝ dotaz pĹ™ehlednÄ›ji s agregacĂ. SELECT * FROM Publikace WHERE 1 < ( SELECT COUNT(*) FROM Zarazeni WHERE Zarazeni.kat_cislo=Publikace.kat_cislo ); -- Seznam zpÄ›vákĹŻ, kteřà nikdy nezpĂvali žádnou skladbu Jana Hanuše. SELECT jmeno, prijmeni FROM Zpevak WHERE RC NOT IN ( SELECT RC FROM Ucast WHERE (misto, cas) IN ( SELECT misto, cas FROM Skladatel JOIN Autorstvi using (jmeno, prijmeni) JOIN Skladba using (id) JOIN Cast_programu using (id) JOIN Udalost using (misto, cas) WHERE jmeno='Jan' AND prijmeni LIKE 'Hanuš' ) AND pritomen <> 0 ); -- Seznam všech koncertĹŻ a generálek. SELECT cas, misto, 'Koncert' AS druh FROM Koncert UNION SELECT cas, misto, 'Generálka' AS druh FROM Zkouska WHERE je_generalka<>0 ORDER BY cas; -- Seznam koncertĹŻ, na nichĹľ byly provedeny všechny skladby B. MartinĹŻ na repertoáru. SELECT misto, cas FROM Koncert MINUS SELECT misto, cas FROM (SELECT id FROM Skladatel JOIN Autorstvi using (jmeno, prijmeni) JOIN Skladba using (id) WHERE jmeno='Bohuslav' AND prijmeni='MartinĹŻ' ) CROSS JOIN Koncert WHERE (id, misto, cas) NOT IN (SELECT id, misto, cas FROM Cast_programu); -- KterĂ˝ zpÄ›vák má nejvÄ›tšà z pĹŻjÄŤenĂ˝ch šatĹŻ? SELECT jmeno, prijmeni, velikost FROM Zpevak JOIN Zapujcka_satu using (RC) JOIN Saty using (inv_cislo) WHERE velikost=(SELECT MAX(velikost) FROM Saty NATURAL JOIN Zapujcka_satu); -- PĹ™ehled účasti na zkouškách (v procentech). SELECT jmeno, prijmeni, CAST((SELECT COUNT(*) FROM Ucast NATURAL JOIN Zkouska WHERE Zpevak.RC=Ucast.RC AND pritomen<>0) AS REAL) /(SELECT COUNT(*) FROM Ucast NATURAL JOIN Zkouska WHERE Zpevak.RC=Ucast.RC )*100 || ' %' AS dochazka FROM Zpevak ORDER BY prijmeni; -- Seznam hlasovĂ˝ch skupin: název, poÄŤet ÄŤlenĹŻ, jmĂ©no vedoucĂho. SELECT nazev_h, poc_clenu, jmeno || ' ' || prijmeni AS vedouci FROM ( SELECT zkratka, nazev_h, COUNT(RC) AS poc_clenu FROM Hlas LEFT JOIN Zpevak ON Hlas.zkratka=Zpevak.hlas GROUP BY zkratka, nazev_h ) LEFT JOIN Vedeni USING(zkratka) LEFT JOIN Zpevak USING(RC); -- Seznam mĂst, na kterĂ˝ch se koncertovalo vĂcekrát a vĹľdy bylo pĹ™Ătomno vĂce neĹľ 50 divákĹŻ. SELECT misto, count(misto) AS pocet_koncertu, AVG(divaku) AS prum_divaku FROM Koncert WHERE divaku > 50 GROUP BY misto HAVING count(misto) > 1; -- Skladatel nejvĂce zastoupenĂ˝ na koncertech co do poÄŤtu skladeb. --VyuĹľĂvám pohled rating_skladatelu: SELECT jmeno, prijmeni, pocet FROM Rating_skladatelu WHERE pocet = (SELECT MAX(pocet) FROM Rating_skladatelu); -- Pro divácky nejĂşspěšnÄ›jšà koncert urÄŤit rozdĂl ceny vstupnĂ©ho od prĹŻmÄ›ru. SELECT ( SELECT vstupne FROM Koncert WHERE divaku = ( SELECT max(divaku) FROM Koncert ) ) - ( SELECT AVG(vstupne) FROM Koncert ) AS odchylka FROM Dual; -- SkladatelĂ©, kteřà se mohli bÄ›hem Ĺľivota setkat s Bohuslavem MartinĹŻ. SELECT * FROM skladatel WHERE rok_narozeni < (SELECT rok_umrti FROM skladatel WHERE jmeno='Bohuslav' AND prijmeni='MartinĹŻ') AND rok_umrti > (SELECT rok_narozeni FROM skladatel WHERE jmeno='Bohuslav' AND prijmeni='MartinĹŻ'); -- Seznam skladatelĹŻ, jejichĹľ skladby byly na programu koncertĹŻ s nadprĹŻmÄ›rnou návštÄ›vou. SELECT DISTINCT jmeno, prijmeni FROM skladatel JOIN autorstvi using (jmeno, prijmeni) JOIN skladba using (id) JOIN cast_programu using (id) JOIN koncert using (misto, cas) WHERE divaku > (SELECT AVG(divaku) FROM koncert); -- Koncert, na nÄ›mĹľ bylo uvedeno nejvĂce rĹŻznĂ˝ch skladeb. SELECT * FROM ( SELECT misto, cas, COUNT(*) AS pocet FROM cast_programu NATURAL JOIN koncert GROUP BY misto, cas ) WHERE pocet = ( SELECT MAX(pocet) FROM ( SELECT COUNT(*) AS pocet FROM cast_programu NATURAL JOIN koncert GROUP BY misto, cas ) ); -- PĹ™ehled ošacenĂ - všichni zpÄ›váci a jejich šaty, zároveĹ všechny šaty a jejich drĹľitelĂ©. SELECT COALESCE(prijmeni, 'VOLNÉ') AS drzitel, inv_cislo, velikost FROM Zpevak LEFT JOIN Zapujcka_satu USING(RC) FULL JOIN Saty USING(inv_cislo); -- SkladatelĂ© a poÄŤet uvedenĂ jejich skladeb CREATE OR REPLACE VIEW rating_skladatelu AS SELECT jmeno, prijmeni, count(*) AS pocet FROM Skladatel JOIN Autorstvi using (jmeno, prijmeni) JOIN Skladba using (id) JOIN Cast_programu using (id) JOIN Koncert using (misto, cas) GROUP BY jmeno, prijmeni; select * from rating_skladatelu; -- VytvoĹ™Ăme redundantnĂ tabulku ucasti_zpevákĹŻ, kde budou pĹ™edpoÄŤĂtanĂ© hodnoty). Tuto tabulku naplnĂme daty. Atribut koef vyjadruje koeficient (ponechme stranou, co to znamená). drop table ucasti_zpevaku; create table ucasti_zpevaku ( rc number(10), jmeno varchar2(30), prijmeni varchar2(30), zkousek number(4), koncertu number(4), koef number(1)); insert into ucasti_zpevaku SELECT rc, jmeno, prijmeni, (SELECT COUNT(*) FROM Ucast NATURAL JOIN Zkouska WHERE Zpevak.RC=Ucast.RC AND pritomen<>0) as zkousek, (SELECT COUNT(*) FROM Ucast NATURAL JOIN Koncert WHERE Zpevak.RC=Ucast.RC AND pritomen<>0) as koncertu, 0 as koef FROM Zpevak ORDER BY prijmeni; commit; select * from ucasti_zpevaku; drop table ucasti_zpevaku; create table UCASTI_ZPEVAKU as SELECT rc, jmeno, prijmeni, (SELECT COUNT(*) FROM Ucast NATURAL JOIN Zkouska WHERE Zpevak.RC=Ucast.RC AND pritomen<>0) as zkousek, (SELECT COUNT(*) FROM Ucast NATURAL JOIN Koncert WHERE Zpevak.RC=Ucast.RC AND pritomen<>0) as koncertu, 0 as koef FROM Zpevak ORDER BY prijmeni; select * from ucasti_zpevaku; -- ZpÄ›vákĹŻm, kteřà zpĂvajĂ alt nastavĂme v relaci ucasti_zpevaku hodnotu atributu koef tak, Ĺľe vyjadĹ™uje poÄŤet jimi zapĹŻjÄŤenĂ˝ch vĂ˝tiskĹŻ not. update ucasti_zpevaku u set koef = (select count(*) from zapujcka_not z where u.rc = z.rc) where u.rc in (select rc from zpevak where hlas = 'A'); commit; -- Z tabulky ucasti_zpevaku vymaĹľeme ty zpÄ›váky, kteřà zpĂvajĂ basy. delete from ucasti_zpevaku where rc in (select rc from zpevak where hlas = 'B'); commit; set markup html off spool off