CV 7 pohledy KOLAC

  CREATE OR REPLACE FORCE VIEW "ST43249"."CV7_V_DETI_PRUMER" ("MATKA", "OTEC", "PRUM_VEK") AS
  SELECT UPPER(matka.prijmeni) ||' '|| matka.jmeno AS MATKA, UPPER(otec.prijmeni) ||' '|| otec.jmeno AS OTEC,
  TO_CHAR(AVG((SYSDATE- dite.narozen)/365),'00.00') AS PRUM_VEK FROM A_CLOVEK.lide dite
  JOIN A_CLOVEK.lide otec ON otec.POHLAVI = 'M'
  JOIN A_CLOVEK.lide matka ON matka.pohlavi ='F'
  WHERE dite.ID_matky = matka.ID AND dite.ID_OTCE = otec.ID
  GROUP BY matka.jmeno,matka.prijmeni,otec.jmeno,otec.prijmeni HAVING COUNT(dite.ID_matky)>2
  ORDER BY ROUND(AVG((SYSDATE- dite.narozen)/365),2) DESC;


  CREATE OR REPLACE FORCE VIEW "ST43249"."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;