Nézet táblák (VIEW)

A lekérdezésekhez SILA felhasználó tulajdonában levő táblákat használjuk

SILA.DOLGOZO    (dkod, dnev, foglalkozas, fonoke, belepes, fizetes, jutalek, oazon)
SILA.OSZTALY    (oazon, onev, telephely)
SILA.CIKK       (ckod, cnev, szin, suly)
SILA.PROJEKT    (pkod, pnev, helyszin)
SILA.SZALLITO   (szkod, sznev, statusz, telephely)
SILA.SZALLIT    (szkod, ckod, pkod, mennyiseg, datum)

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
--  1. Feladat: 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 

-- Melyik cikket szállítják a legtöbb projekthez?

-- -- -- -- -- --
-- Megoldás -- 1: 
-- -- -- -- -- --

CREATE OR REPLACE VIEW cikk_proj_db
AS 
SELECT cikk.ckod ckod, cnev, COUNT(DISTINCT pkod) darab 
FROM sila.cikk, sila.szallit
WHERE cikk.ckod=szallit.ckod
GROUP BY cikk.ckod, cnev;

SELECT ckod, cnev FROM cikk_proj_db
WHERE darab = (SELECT MAX(darab) FROM cikk_proj_db);


-- -- -- -- -- -- 
-- Megoldás -- 2: 
-- -- -- -- -- -- 

-- A lekérdezés úgy is megadható, hogy ne kelljen nézetet létrehozni.
-- Az alábbi lekérdezésben a "nézet" csak a lekérdezés idejére jön létre.
-- Ezt úgy is hívjuk, hogy INLINE nézet.

SELECT ckod, cnev FROM
  (SELECT cikk.ckod ckod, cnev, COUNT(DISTINCT pkod) darab 
   FROM sila.cikk, sila.szallit
   WHERE cikk.ckod=szallit.ckod
   GROUP BY cikk.ckod, cnev) cikk_proj_db
WHERE darab = (SELECT MAX(darab) FROM 
                   (SELECT cikk.ckod ckod, cnev, COUNT(DISTINCT pkod) darab 
                    FROM sila.cikk, sila.szallit
                    WHERE cikk.ckod=szallit.ckod
                    GROUP BY cikk.ckod, cnev) 
               ) 
;


-- -- -- -- -- -- 
-- Megoldás -- 3: 
-- -- -- -- -- -- 

-- A fenti lekérdezésben kétszer kellett leírnunk ugyanazt a "nézetet", lásd cikk_proj_db
-- Ezt megspórolhatjuk az alábbi szintaxissal, WITH záradék használatával.  
-- Most is csak a lekérdezés idejére jön létre a nézet.

WITH 
  cikk_proj_db AS (
    SELECT cikk.ckod ckod, cnev, COUNT(DISTINCT pkod) darab 
    FROM sila.cikk, sila.szallit
    WHERE cikk.ckod=szallit.ckod
    GROUP BY cikk.ckod, cnev)
SELECT ckod, cnev FROM cikk_proj_db
WHERE darab = (SELECT MAX(darab) FROM cikk_proj_db);


-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- 2. Feladat: 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 

-- Képezzük osztályonként az összfizetést, vegyük ezen számok átlagát, és 
-- adjuk meg, hogy mely osztályokon nagyobb ennél az átlagnál az összfizetés.

-- -- -- -- -- -- 
-- Megoldás -- 4: 
-- -- -- -- -- -- 

CREATE OR REPLACE VIEW osztaly_osszfiz 
AS 
SELECT onev, SUM(fizetes) ossz_fiz
FROM sila.dolgozo d, sila.osztaly o
WHERE d.oazon = o.oazon
GROUP BY onev;

CREATE OR REPLACE VIEW atlag_koltseg 
AS
SELECT SUM(ossz_fiz)/COUNT(*) atlag
FROM osztaly_osszfiz;

SELECT * FROM osztaly_osszfiz
WHERE ossz_fiz  >  (SELECT atlag FROM atlag_koltseg)


-- -- -- -- -- -- 
-- Megoldás -- 5: 
-- -- -- -- -- -- 

-- Ugyanez WITH-del megadva:

WITH
  osztaly_osszfiz AS (
     SELECT onev, SUM(fizetes) ossz_fiz
     FROM sila.dolgozo d, sila.osztaly o
     WHERE d.oazon = o.oazon
     GROUP BY onev),
  atlag_koltseg AS (
     SELECT SUM(ossz_fiz)/COUNT(*) atlag
     FROM osztaly_osszfiz)
SELECT * FROM osztaly_osszfiz
WHERE ossz_fiz  >  (SELECT atlag FROM atlag_koltseg)