CV07

CREATE OR REPLACE VIEW cv7_v_cetnost_jmeno(jmeno,pocet) AS
SELECT jmeno,COUNT(jmeno) FROM A_CLOVEK.LIDE GROUP BY JMENO
HAVING COUNT(jmeno)>1;

SELECT * FROM cv7_v_cetnost_jmeno;


CREATE OR REPLACE VIEW cv7_v_starsi_2003(prijmeni, jmeno) AS
SELECT Jmeno, UPPER(prijmeni) FROM A_CLOVEK.LIDE
WHERE NAROZEN<TO_DATE('1.1.2003','dd.mm.yyyy')
ORDER BY prijmeni DESC,jmeno ASC;

SELECT * FROM cv7_v_starsi_2003;

CREATE OR REPLACE VIEW cv7_v_dite_rodice(dite,matka,otec) AS SELECT
d.jmeno ||' '|| d.prijmeni AS "DITE", m.JMENO ||' '|| m.PRIJMENI AS "MATKA",
o.jmeno ||' '||o.prijmeni AS "OTEC" FROM A_CLOVEK.LIDE d INNER JOIN
A_clovek.lide m ON d.ID_MATKY = m.ID  INNER JOIN A_clovek.lide o ON
d.ID_OTCE = o.ID
ORDER BY  d.prijmeni ASC,d.jmeno ASC;

SELECT * FROM cv7_v_dite_rodice;

CREATE OR REPLACE VIEW cv7_v_deti_prumer(matka, otec, prum_vek) AS SELECT
UPPER(m.prijmeni)||' '|| m.jmeno AS "MATKA", UPPER(o.prijmeni)||' '||o.jmeno
AS "OTEC", avg((sysdate-d.narozen)/365) AS "Prum_vek" FROM A_clovek.lide d
INNER JOIN A_clovek.lide m ON d.ID_MATKY = m.ID INNER JOIN A_clovek.lide o ON
d.ID_OTCE = o.ID GROUP BY UPPER(m.PRIJMENI)||' '||m.jmeno, UPPER(o.prijmeni)||' '||o.jmeno HAVING COUNT(m.id)>2;

SELECT * FROM cv7_v_deti_prumer;

CREATE OR REPLACE VIEW cv7_v_stud_kn_prumer(prijmeni, jmeno)  AS SELECT Jmeno, Prijmeni
FROM A_SKOLA.STUDENTI JOIN A_SKOLA.KNIHY ON A_SKOLA.STUDENTI.ID_Studenta = A_SKOLA.KNIHY.ID_STUDENTA
JOIN A_SKOLA.UCI ON A_SKOLA.STUDENTI.ID_Studenta = A_SKOLA.Uci.ID_STUDENTA WHERE
A_SKola.Uci.Predmet ='Databáze'
GROUP BY Jmeno, Prijmeni  HAVING AVG(A_SKola.Uci.Znamka)< 2.5
AND COUNT(A_SKOLA.KNIHY.ID_STUDENTA)>=2 ORDER BY prijmeni ;

SELECT * FROM cv7_v_stud_kn_prumer;

CREATE OR REPLACE VIEW cv7_v_ucitele_nastup(jmeno, prijmeni, nastup) AS
SELECT a_skola.ucitele.jmeno AS "JMENO",a_skola.ucitele.prijmeni AS
"PRIJMENI", UPPER(to_char(MIN(a_skola.studenti.nastup),'DD.Month YYYY')) AS "NASTUP"
FROM A_SKOLA.UCITELE JOIN a_skola.uci ON a_skola.ucitele.id_ucitele = a_skola.uci.ID_UCITELE
JOIN a_skola.studenti ON a_skola.uci.id_studenta = a_skola.studenti.id_studenta
 WHERE a_skola.uci.predmet='Databáze' GROUP BY a_skola.ucitele.jmeno, a_skola.ucitele.prijmeni
 ORDER BY nastup DESC;

 SELECT * FROM cv7_v_ucitele_nastup;