-----[1]-----

SELECT szsz
FROM z_dolgozo 
  WHERE osz = 5
UNION ALL
SELECT fonok_szsz
FROM z_dolgozo
  WHERE osz = 5;

SELECT fonok_szsz
FROM z_dolgozo
  WHERE osz = 5
UNION
SELECT szsz
FROM z_dolgozo
  WHERE osz = 5;

SELECT vnev,knev,osz
FROM z_dolgozo
  ORDER BY nem,vnev DESC; /* Először nem, majd azon belül vezeték név szerint rendez */

SELECT vnev,knev,osz
FROM z_dolgozo
  ORDER BY 3; /* A kiválasztandók közül a 3. oszlopa szerint */

SELECT vnev as VezetékNév, knev as KeresztNév, osz as "Osztály száma"
FROM z_dolgozo
  ORDER BY "Osztály száma" DESC;

SELECT *
FROM z_dolgozo
  WHERE fonok_szsz IS NULL; /* Nem lehet = jelet használni, hanem "is"-elni kell */
  
SELECT *
FROM z_dolgozo
  WHERE fonok_szsz IS NOT NULL
  ORDER BY fizetes DESC;

SELECT 'A neve: ' || vnev || ' ' || knev as "Név"
FROM z_dolgozo
  WHERE fonok_szsz IS NOT NULL;

SELECT onev as "Osztály neve", oszam as "Osztály száma"
FROM z_osztaly;

SELECT onev as "Osztály neve", vnev || ' ' || knev as "Vezető neve"
FROM z_dolgozo, z_osztaly
  WHERE szsz = vez_szsz AND osz>1;

SELECT onev as "Osztály neve", concat(concat(vnev,' '),knev) as "Vezető neve"
FROM z_dolgozo, z_osztaly
  WHERE szsz = vez_szsz AND osz>1;

/* Most jön a jajdejó */

SELECT onev as "Osztály neve", concat(concat(vnev,' '),knev) as "Vezető neve" /* Ugyanazt csinálja, mint az előző */
FROM z_dolgozo JOIN z_osztaly ON szsz = vez_szsz /* ON: Megadja, hogy mi alapján kapcsoljuk össze a táblákat */
  WHERE osz>1;

SELECT onev as "Osztály neve", concat(concat(vnev,' '),knev) as "Vezető neve" /* Ugyanazt csinálja, mint az előző */
FROM z_dolgozo JOIN (SELECT onev, vez_szsz szsz, oszam FROM z_osztaly) USING (szsz) /* vez_szsz-t átneveztük szsz-re*/
  WHERE oszam>1; /* USING: azonos nevű oszlopok esetén kiválasztja azokat a sorokat, ahol a megadott oszlopok tartalma megegyezik */

SELECT onev as "Osztály neve", concat(concat(vnev,' '),knev) as "Vezető neve" /* Ugyanazt csinálja, mint az előző */
FROM z_dolgozo NATURAL JOIN (SELECT onev, vez_szsz szsz, oszam FROM z_osztaly) /* Ekkor automatikusan felismeri az azonos oszlopokat*/
  WHERE oszam>1;

SELECT *
FROM z_dolgozo NATURAL JOIN (SELECT onev, vez_szsz szsz, oszam FROM z_osztaly); /* Hozzáfűzi a vele JOIN-olt tábla megadott oszlopait is*/

/* És még ez is megy! Sőt az AS parancs el is hagyható! */
SELECT onev "Osztály", (SELECT concat(concat(vnev,' '),knev) FROM z_dolgozo WHERE szsz=vez_szsz) "Osztályvezető"
FROM z_osztaly
  WHERE oszam > 1;

-----[2]-----

SELECT vnev || ' ' || knev "Név",  onev "Osztály"
FROM z_dolgozo, z_osztaly
  WHERE szsz = vez_szsz;

SELECT vnev || ' ' || knev "Név", 'Nem vezet osztályt' "Osztály"
FROM z_dolgozo
  MINUS
SELECT vnev || ' ' || knev "Név",  onev "Osztály"
FROM z_dolgozo, z_osztaly
  WHERE szsz = vez_szsz;

/* Ez már elég komplex */
SELECT vnev || ' ' || knev "Név",  onev "Osztály"
FROM z_dolgozo, z_osztaly
  WHERE szsz = vez_szsz
    UNION
SELECT vnev || ' ' || knev "Név", null
FROM z_dolgozo
  WHERE szsz not in (SELECT vez_szsz FROM z_osztaly);

SELECT onev "Osztály",
  (SELECT vnev || ' ' || knev
  FROM z_dolgozo
    WHERE szsz = vez_szsz) "Osztályvezető"
FROM z_osztaly;

SELECT vnev || ' ' || knev "Név",
  (SELECT onev
  FROM z_osztaly
    WHERE vez_szsz = szsz) "Osztály"
FROM z_dolgozo;

SELECT vnev || ' ' || knev "Név", onev "Osztály"
FROM z_dolgozo LEFT JOIN z_osztaly ON szsz = vez_szsz; /* LEFT: A bal oldalon szereplő tábla összes rekordját látni akarjuk, ha nincs a jobb oldali táblából hozzá pár, akkor az (null) lesz */
/* Ha a RIGHT-ot akarunk használni, a két táblát kell megcserélni (nem pedig a kiválasztott oszlopokat) */

SELECT vnev || ' ' || knev "Név", onev "Osztály"
FROM z_dolgozo, z_osztaly
  WHERE szsz = vez_szsz (+); /* Ugyanazt csinálja, mint az előző */
  /*  WHERE vez_szsz (+) = szsz; */ /* Itt nem a táblák felsorolási sorrendje számít, hanem az, hogy hol van a (+) jel */

SELECT vnev || ' ' || knev "Név", onev "Osztály"
FROM z_osztaly FULL JOIN z_dolgozo ON szsz = vez_szsz;

SELECT vnev || ' ' || knev "Név", kapcsolat "Kapcsolat", hozzatartozo_nev "Hozzátartozó neve"
FROM z_dolgozo LEFT JOIN z_hozzatartozo ON szsz = dszsz;

SELECT vnev || ' ' || knev "Név", pnev "Projekt neve", NVL(orak,0) "Dolgozott órák" /* Az NVL képes alapértelmezett értéket megadni */
FROM z_dolgozo, z_projekt, z_dolgozik_rajta
WHERE szsz = dszsz (+) AND psz = pszam (+) /* Ha lenne olyan dolgozó, aki nem dolgozik semmin, azt is kiírná (a (+) miatt) */
  ORDER BY "Név";

/* 5ös osztály, azok neve, heti 10óránál többet dolgoznak az X termék nevű projekten */
SELECT vnev || ' ' || knev "Név"
FROM z_dolgozo d, z_projekt p, z_dolgozik_rajta dr /* Alias neveket adtunk a tábláknak */
  WHERE d.szsz = dr.dszsz AND dr.psz = p.pszam AND p.pnev = 'X termék' AND dr.orak>10 AND d.osz = 5;

SELECT vnev || ' ' || knev "Név" /* Ugyanazt csinálja, mint az előző */
FROM z_dolgozo
    NATURAL JOIN (SELECT dszsz szsz FROM z_dolgozik_rajta
          NATURAL JOIN (SELECT pszam psz FROM z_projekt
            WHERE pnev = 'X termék')
      WHERE orak > 10)
  WHERE osz = 5;

SELECT vnev || ' ' || knev "Név" /* Ugyanazt csinálja, mint az előző */
FROM z_dolgozo
  WHERE osz = 5 AND
  szsz IN (SELECT dszsz
          FROM z_dolgozik_rajta
            WHERE orak > 10 AND 
            psz = (SELECT pszam
                  FROM z_projekt
                    WHERE pnev = 'X termék'
            )
          );

SELECT vnev || ' ' || knev "Név" /* Ugyanazt csinálja, mint az előző */
FROM z_dolgozo
WHERE osz = 5 AND
  EXISTS (SELECT 0 /* Azt nézi, hogy a lekérdezés eredménye hány sort tartalmaz, ha 0 sort kap, akkor false értéket kap */
         FROM z_dolgozik_rajta
         WHERE dszsz = szsz AND orak > 10 AND
            EXISTS (SELECT 0 /* Írhatnánk ide bármit, csak azt nézi meg, hogy hány sornyi 0-t kapunk (a sorokat számolja csak) */
                    FROM z_projekt
                    WHERE pszam = psz AND pnev = 'X termék'
                    )
         );

-----[3]-----

SELECT COUNT(*) "Dolgozók száma"
FROM z_dolgozo;

SELECT COUNT( DISTINCT fizetes) "Hányféle fizetés van"
FROM z_dolgozo;

SELECT COUNT(osz) "Tényleges dolgozók száma"
FROM z_dolgozo;

SELECT AVG(fizetes) "Átlagfizetés" FROM z_dolgozo;

SELECT SUM(fizetes) "Összkifizetés" FROM z_dolgozo;

-----[4]-----

/* Dolgozók száma */
SELECT COUNT(*)
FROM z_dolgozo;

/* Hányan dolgoznak az egyes osztályokban */
SELECT osz "Osztály száma", COUNT(*) "Dolgozók száma"
FROM z_dolgozo
  GROUP BY osz;

/* Mennyi az osztályonkénti fizetés */
SELECT osz "Osztály", SUM(fizetes) "Fizetés"
FROM z_dolgozo
  GROUP BY osz;

/* EZ NEM MEGY - DE RÁ KÉNE ISMERNI MAJD A KÖVETKEZŐKBEN */
SELECT osz
FROM z_dolgozo
  WHERE SUM(fizetes) > 800000
  GROUP BY osz;

-- Mi azoknak az osztályoknak a száma, ahol kettőnél többen dolgoznak? (A)
SELECT *
FROM
  (
  SELECT osz "Osztály száma", COUNT(*) "Dolgozók száma"
  FROM z_dolgozo
    GROUP BY osz
  )
  WHERE "Dolgozók száma" > 1;

-- Mi azoknak az osztályoknak a száma, ahol kettőnél többen dolgoznak? (B)
SELECT osz "Osztály száma", COUNT(*) "Dolgozók száma"
FROM z_dolgozo
  GROUP BY osz
  HAVING COUNT(*) > 1;

-- A probléma megoldása
SELECT osz, SUM(fizetes)
FROM z_dolgozo
  GROUP BY osz
  HAVING SUM(fizetes) > 800000;

-- Hányan dolgoznak a kutatás nevű osztályon? (A)
SELECT COUNT(*) "Dolgozók"
FROM z_dolgozo
  JOIN z_osztaly ON osz = oszam
  WHERE onev = 'Kutatás';

-- Hányan dolgoznak a kutatás nevű osztályon? (B)
SELECT COUNT(*) "Dolgozók"
FROM z_dolgozo
  NATURAL JOIN
  (
  SELECT oszam osz
  FROM z_osztaly
    WHERE onev = 'Kutatás'
  );

-- Hányan dolgoznak a kutatás nevű osztályon? (C)
SELECT COUNT(*) "Dolgozók"
FROM z_dolgozo
  WHERE osz = (
              SELECT oszam
              FROM z_osztaly
                WHERE onev = 'Kutatás'
              );

-- Listázzuk ki, hogy melyik dolgozó hány projekten dolgozik. (A)
SELECT vnev || ' ' || knev "Név", COUNT(dszsz) "Projektek"
FROM z_dolgozik_rajta JOIN z_dolgozo ON dszsz = szsz
  GROUP BY vnev || ' ' || knev;

-- Listázzuk ki, hogy melyik dolgozó hány projekten dolgozik. (B)
SELECT vnev || ' ' || knev "Név", (
                                  SELECT COUNT(*)
                                  FROM z_dolgozik_rajta
                                    WHERE dszsz = szsz
                                  ) "Projektek"
FROM z_dolgozo;

-- 

-- Listázzuk ki azoknak a dolgozóknak a neveit, akik a legkevesebb projekten dolgoznak
SELECT *
FROM (SELECT vnev || ' ' || knev,
             COUNT(dszsz) "Projektek száma"
      FROM z_dolgozik_rajta RIGHT JOIN z_dolgozo ON dszsz = szsz
        GROUP BY vnev || ' ' || knev, szsz
      )
      WHERE "Projektek száma" = (SELECT MIN(COUNT(dszsz))
                                FROM z_dolgozik_rajta RIGHT JOIN z_dolgozo ON dszsz = szsz
                                  GROUP BY dszsz);

-- Listázzuk ki az összes osztályt és azt, hogy melyik hány projektet irányít.
-- Listázzuk ki az összes projektet, és azt, hogy az egyes projekteken hány órát dolgoztak összesen a dolgozók.
-- Listázzuk ki azokat a projekteket, amelyeken a legtöbben dolgoznak.

-- Adjuk meg nemenként az átlagfizetéseket.
SELECT DECODE (nem,
              'F', 'Férfiak',
              'N', 'Nők',
              'Marslakók') "Nem", AVG(fizetes)
FROM z_dolgozo
  GROUP BY nem;

-- Listázzuk ki a férfiak átlagfizetését. (A)
SELECT AVG(fizetes)
FROM z_dolgozo
  WHERE nem = 'F';

-- Listázzuk ki a férfiak átlagfizetését. (B)
SELECT AVG(fizetes)
FROM z_dolgozo
  GROUP BY nem HAVING nem = 'F'; /* Először csoportokra szűr és abból választja ki az F csoportot */

-----[5]-----

-- Listázzuk ki azon dolgozók nevét, akik a legrégebb óta osztályvezetők, valamit azt is, hogy melyik osztályt vezetik
SELECT vnev || ' ' || knev "Név", onev "Osztály"
FROM z_dolgozo, z_osztaly
      WHERE szsz = vez_szsz AND vez_kezdo_datum = (SELECT MIN(vez_kezdo_datum) FROM z_osztaly);

-- Ugyanez máshogy
SELECT vnev || ' ' || knev "Név", onev "Osztály"
FROM z_osztaly JOIN z_dolgozo ON vez_szsz = szsz
  WHERE vez_kezdo_datum = (SELECT MIN(vez_kezdo_datum) FROM z_osztaly);

-- Listázzuk ki a budapesti projektek nevét
SELECT pnev "Projekt neve"
FROM z_projekt
  WHERE phelyszin = 'Budapest';

-- -- Listázzuk ki azon budapesti projektek nevét, amelyen legalább hárman dolgoznak
SELECT pnev "Projekt neve"
FROM z_dolgozik_rajta JOIN z_projekt ON pszam = psz
  WHERE phelyszin = 'Budapest'
  GROUP BY pnev,pszam
  HAVING COUNT(dszsz) > 2;
  -- a GROUP BY előtti részt bontja osztályokra

-- Ugyanez máshogy
SELECT pnev "Projekt neve"
FROM z_projekt
  WHERE phelyszin = 'Budapest' AND pszam IN (SELECT psz FROM z_dolgozik_rajta GROUP BY psz HAVING COUNT(*) > 2);

--
SELECT *
FROM dual;

--
SELECT 1
FROM dual;

--
SELECT SIN(1) * LENGTH('Adatbázisrendszerek')
FROM z_dolgozo;

--
SELECT SYSDATE, 1, ATAN(1) * LENGTH('Adatabázisrendszerek')
FROM dual;

--
SELECT TO_CHAR( SYSDATE, 'YYYY-MM-DD' )
FROM dual;

--
SELECT TO_CHAR( SYSDATE, 'CC W Q D J SS day' )
FROM dual;
-- SS: Másodpercek a percben
-- SSSSS: Éjfél óta eltelt másodpercek
-- D: A hét hanyadik napja van (angolban vasárnaptól indul a hét)
-- W: A hónap hanyadik hete van most

--
SELECT TO_CHAR( SYSDATE, 'YYYY. month DD. day, HH24:MI:SS' ) "Pontos idő"
FROM dual;

--
SELECT TO_DATE( '2010-01-12-15:39', 'YYYY-MM-DD-HH24:MI' )
FROM dual;

--
SELECT SYSDATE - TO_DATE('1991-03-10', 'YYYY-MM-DD')
FROM dual;

-- Listázzuk ki azok nevét és születési dátumát, akik még nem tölöttékbe az 50. életévüket
SELECT vnev || ' ' || knev "Név", TO_DATE(szdatum, 'YYYY-MM-DD') "Születési idő"
FROM z_dolgozo
  WHERE SYSDATE-szdatum < 50*365.25;

-- Listázzuk ki azon dolgozók összes adatát, akik fizetése meghaladja az osztályvezetőjük fizetését
SELECT d.*
FROM z_dolgozo d, z_osztaly o, z_dolgozo ov
  WHERE d.osz = o.oszam AND o.vez_szsz = ov.szsz AND d.fizetes > ov.fizetes;

-- Listázzuk ki azon dolgozók nevét, akik az 5ös osztály minden dolgozójánál többet keresnek
SELECT vnev || ' ' || knev "Név"
FROM z_dolgozo
  WHERE fizetes > (SELECT MAX(fizetes) FROM z_dolgozo WHERE osz=5);
  -- ALL (SELECT fizetes FROM z_dolgozo WHERE osz=5);
  -- ALL (SELECT fizetes FROM z_dolgozo WHERE osz=5 AND fizetes IS NOT NULL);
  -- A két ALL-os megoldás közül az első nem adna jó eredményt, ha lenne null értékű fizetés is a dolgozók között

-----[6]-----

/*
DML utasítások

INSERT INTO [felhasználó.]tábla [másodlagos név] [(oszlop [,oszlop]...)]
  { VALUES ( {kifejezés | default}, [, {kifejezés | default}]...)
  | alkérdés }
*/

INSERT INTO z_dolgozo
  VALUES ('Győri', 'László', '372765EA', TO_DATE('1991-03-10','YYYY-MM-DD'), '4254 Nyíradony', 'F', 320000, null, null);

INSERT INTO z_dolgozo (vnev, knev, szsz, szdatum, lakcim, nem, fizetes)
  VALUES ('Győri', 'László', '372765EB', TO_DATE('1991-03-10','YYYY-MM-DD'), '4254 Nyíradony', 'F', 320000);
  
/*
DCL utasítások

GRANT SELECT ON z_dolgozo TO { public | felhasználó [, felhasználó]...} }
// engedélyezi (public = mindenkinek) felhasználó (Pl.: P_DVPQVZ) számára az adatbázis elérését

REVOKE SELECT ON z_dolgozo FROM { public | felhasználó [, felhasználó]...}
// elveszi a jogot
*/

INSERT INTO P_BOGRWO.z_dolgozo
  VALUES ('Győri', 'László', '372765EA', TO_DATE('1991-03-10','YYYY-MM-DD'), '4254 Nyíradony', 'F', 320000, null, null);

GRANT SELECT ON z_dolgozo TO P_BOGRWO;
-- SELECT helyett OR-t írva mindent engedélyez

-- Azon sorok, amelyek nincsenek meg mindkét félnek
SELECT * FROM P_BOGRWO.z_dolgozo
  WHERE szsz NOT IN (SELECT szsz FROM z_dolgozo)
UNION
SELECT * FROM z_dolgozo
  WHERE szsz NOT IN (SELECT szsz FROM P_BOGRWO.z_dolgozo);

-- Beszúrjuk azokat a sorokat, amelyek nekünk nincsenek meg
INSERT INTO z_dolgozo
  SELECT * FROM C d
  WHERE NOT EXISTS ( SELECT 0 FROM z_dolgozo WHERE szsz = d.szsz);

SELECT * FROM z_dolgozo;

SELECT * FROM P_BOGRWO.z_dolgozo;
-- Nem láthatom a partner megváltozott tábláját, amíg tranzakcióban van
-- Ennek lezárásának módja: COMMIT;

COMMIT;

INSERT INTO P_BOGRWO.z_dolgozo
  VALUES ('Kecske', 'Béka', '57957e35', TO_DATE('1911-11-13','YYYY-MM-DD'), '4211 Biri', 'F', 1311313, null, null );

COMMIT;

/*
UPDATE [felhasználó].tábla [másodlagos név]
  SET { oszlop = { kifejezés | alkérdés | default } | (oszlop [, oszlop]...) = (alkérdés) }
   [, { oszlop = { kifejezés | alkérdés | default } | (oszlop [, oszlop]...) = (alkérdés) } ]...
   [ WHERE feltétel ];

(oszlop1, oszlop2) = (alkérdés)
estén alkérdés 2 oszloppal visszatérő lekérdezés
*/

UPDATE z_dolgozo
  SET fizetes = (fizetes*1.1);

SELECT * FROM z_dolgozo;

-- Tegyük a legnagyobb fizetésű dolgozó osztályába azokat, akinek nincs osztályszáma
UPDATE z_dolgozo
  SET osz = (
              SELECT osz
              FROM z_dolgozo
                WHERE fizetes = (
                                  SELECT MAX(fizetes)
                                  FROM z_dolgozo
                                )
            )
  WHERE osz IS NULL;

-- ROLLBACK: visszavonás

UPDATE P_BOGRWO.z_dolgozo d
  SET vnev = knev, knev = vnev, nem = 'F'
  WHERE nem = 'N';

ROLLBACK;
COMMIT;

REVOKE ALL ON z_dolgozo FROM public;

-----[7]-----

/*
  DML-utasítások: SELECT, INSERT, DELETE, UPDATE
  DCL-utasítások: GRANT, REVOKE, ROLLBACK, COMMIT, SAVEPOINT

  DELETE
  ------

  DELETE [FROM] [felhasználó].tábla [másodlagos név] [WHERE feltétel]
*/

DELETE FROM z_hozzatartozo;
ROLLBACK;

-- Ez hibát ír, mert észleli a rendszer, hogy az adott táblából való törlés információvesztéssel járhat
DELETE FROM z_dolgozo
  WHERE vnev='Szabó' AND knev='Mária';

-- Kiírja, hogy kinek milyen jogot adtam meg az egyes tábláim kezeléséhez
SELECT * FROM user_tab_privs_made;

-- COMMIT [WORK] // opcionális
-- SAVEPOINT mentéspont neve

/*
t (idő tengely)
|
|---- Kezdődik a tranzakció
|
|-- csinálunk mindent, amit akarunk
|
|---- Befejezzük a tranzakciót (pl.: COMMIT utasítással // Minden DDL-utasítás ezt is megteszi)

A tranzakció alatt létrehozhatunk SAVEPOINT-okat, amelyek megjegyzik az adatbázis aktuális állapotát.

ROLLBACK [WORK] [ TO [SAVEPOINT] mentéspont neve ]
Opcionális tagok nélkül a ROLLBACK az adatbázst a tranzakció kezdésekori állapotára állítja vissza.

*/

UPDATE z_dolgozo
  SET lakcim='6320 Solt'
  WHERE lakcim like '%Debrecen%';
SAVEPOINT solt;
SELECT * FROM z_dolgozo;

INSERT INTO z_projekt (pnev, pszam) VALUES ('Zabhegyezés',99);
SAVEPOINT zab;
SELECT * FROM z_projekt;

DELETE FROM z_dolgozik_rajta
  WHERE dszsz = ( SELECT szsz FROM z_dolgozo WHERE vnev='Szabó' AND knev='Mária' );
SAVEPOINT mari;
SELECT * FROM z_dolgozik_rajta;

ROLLBACK solt;
ROLLBACK;

SELECT * FROM z_dolgozo; -- Már nem Solt-on laknak a debreceniek, a Zabegyezés sem létezik, sőt Mária is visszakapta munkahelyét
-- Fluxuskondenzátor POWA!

/*
  Felső N analízis
  -- Listázzuk ki a 3 legmagasabb fizetésű dolgozót!
*/

-- SELECT vnev || ' ' || knev "Név", fizetes "Fizetés"
SELECT * FROM
 (SELECT vnev || ' ' || knev "Név", nvl(fizetes,0) "Fizetés"
  FROM z_dolgozo
    -- ORDER BY fizetes DESC NULLS LAST; -- a nullokat a sor végére (first: sor elejére)
    ORDER BY fizetes DESC
  )
WHERE ROWNUM < 4; -- ( csak úgy meg, hogy < vagy <= )


-- Ki a 3. legnagyobb fizetésű?
SELECT * FROM
 (SELECT vnev || ' ' || knev "Név", nvl(fizetes,0) "Fizetés"
  FROM z_dolgozo
    ORDER BY fizetes DESC
  )
WHERE ROWNUM < 4
MINUS
SELECT * FROM
 (SELECT vnev || ' ' || knev "Név", nvl(fizetes,0) "Fizetés"
  FROM z_dolgozo
    ORDER BY fizetes DESC
  )
WHERE ROWNUM < 3;

--
SELECT * 
FROM
  (
  SELECT t.*, ROWNUM sorszam
  FROM
    ( 
    SELECT vnev || ' ' || knev "Név", szsz "Személyi szám", nvl(fizetes,0) "Fizetés"
    FROM z_dolgozo
    ORDER BY 3 DESC
    ) t
  WHERE ROWNUM < 4
  )
WHERE sorszam = 3;

-- Melyik az a település, ahol a legtöbben laknak? ( & Melyik az a 3?)
--...

/*
  Hierarchikus lekérdezés
  
  SELECT { LEVEL | oszlopkifejezés }
  [, { LEVEL | oszlopkifejezés } ]...
  [ WHERE feltétel ]
  [ [ START WITH startfeltétel ]
    [ CONNECT BY PRIOR kapcsolási feltétel ] ]
*/

SELECT LEVEL, lpad(' ',3*(LEVEL-1)) || vnev || ' ' || knev "Név"
FROM z_dolgozo
START WITH fonok_szsz IS NULL
CONNECT BY PRIOR szsz = fonok_szsz; -- bal oldalt a szülő, jobb oldalt a leszármazott

SELECT LEVEL, vnev || ' ' || knev "Név"
FROM z_dolgozo
START WITH vnev = 'Tóth' AND knev = 'János'
CONNECT BY PRIOR fonok_szsz = szsz;

-----[8]-----

/*
  DDL utasítások
  Táblák létrehozása: CREATE

  CREATE TABLE tábla
  ({ oszlop típus [ DEFAULT érték ] [ oszlopmegszorítás [ oszlopmegszorítás ] ... ]
  [, oszlop típus [ DEFAULT érték ] [ oszlopmegszorítás [ oszlopmegszorítás ] ... ]  ]...
  | táblamegszorítás [, táblamegszorítás ]... })

  Típusok:
  - NUMBER[( n [, m] )] // m: hány tizedes jegy pontosságú
  - DATE
  - CHAR(n)
  - VARCHAR2(n)

  Oszlopmegszorítások:
  [ CONSTRAINT megszorítás ]  { [NOT] NULL | UNIQUE | PRIMARY KEY | REFERENCES tábla[(oszlop)] | CHECK (feltétel) }
  // NOT NULL = nem lehet benne NULL érték
  // NULL = nincs megszorítás
  // UNIQUE = egyéni értékek szerepelnek az oszlopbban (kulcsok)
  // Nem feltétlenül kötelező elsődleges kulcsnak szerepelnie egy adott oszlopban
  // REFERENCES = külső hivatkozás (ha nem adok meg oszlopnevet, akkor azonos oszlopnevet követel, mint amelyik mellé írtuk)
  // ekkor csak null, illetve olyan értékek szerepelhetnek a tábla adott oszlopában, amelyek megtalálhatóak a hivatkozott tábla oszlopában
  // CHECK: pl életkor vizsgálatkor (negatív nem lehet)

  Táblamegszorítások:
  [ CONSTRAINT megszorításnév ] { UNIQUE (oszlop [, oszlop ]...) | PRIMARY KEY (oszlop [, oszlop ] ...) |
  FOREIGN KEY (oszlop [, oszlop]...) REFERENCES tábla [ (oszlop [, oszlop ]...) ] | CHECK (feltétel) }
*/

SELECT * FROM user_users;

-- Látni, hogy mindegy hány karakter a személyi szám, fetöltődnek szóközzel az "üres" részek
SELECT szsz, LENGTH(szsz), vnev, LENGTH(vnev) FROM z_dolgozo;

SAVEPOINT most;

CREATE TABLE SZABADSAG
  ( DSZSZ CHAR(11)
      CONSTRAINT szabadsag_fk REFERENCES z_dolgozo(szsz),
    METTOL DATE
      DEFAULT SYSDATE -- előbb a default
      CONSTRAINT szabadsag_nn NOT NULL, -- aztán a megszorítás
    MEDDIG DATE,

    CONSTRAINT szabadsag_pk PRIMARY KEY (dszsz, mettol, meddig),
    CONSTRAINT szabadsag_ch CHECK (mettol < meddig)
  );

CREATE TABLE JUTALOM
  ( DSZSZ CHAR(11)
      CONSTRAINT jutalom_fk REFERENCES z_dolgozo(szsz),
    OSSZEG NUMBER(10)
      DEFAULT 10000
      CONSTRAINT jutalom_ch CHECK (OSSZEG >= 1000),
    DATUM DATE
      CONSTRAINT jutalom_nn NOT NULL,
    INDOK CHAR(50),

    CONSTRAINT jutalom_pk PRIMARY KEY (dszsz, datum)
  );

/*
  Tábla létrehozásának másik módja:

  CREATE TABLE tábla
  [(  oszlop [ oszlopmegszorítás [ oszlopmegszorítás ]...]
    [,
      oszlop [ oszlopmegszorítás [ oszlopmegszorítás ]...]
    ]...
  )] AS alkérdés

*/

CREATE TABLE OSZTVEZ
AS
  SELECT d.vnev, d.knev, o.onev
  FROM z_dolgozo d JOIN z_osztaly o ON szsz = vez_szsz;

-----[9]-----

/*
{} jelek jelentése: kötelezően választandó opció

  ALTER TABLE tábla
  {
    ADD ( oszlop típus [ DEFAULT kifejezés ] [ oszlopmegszorítás [ oszlopmegszorítás ] ... ]
        [,oszlop típus [ DEFAULT kifejezés ] [ oszlopmegszorítás [ oszlopmegszorítás ] ... ] ]... )
    | ADD táblamegszorítás [ táblamegszorítás ] ...
    | MODIFY ( oszlop [ típus ] [ DEFAULT kifejezés ] [ oszlopmegszorítás [ oszlopmegszorítás ] ... ]
             [,oszlop [ típus ] [ DEFAULT kifejezés ] [ oszlopmegszorítás [ oszlopmegszorítás ] ... ] ] ... )
    | MODIFY { CONSTRAINT megszorítás | PRIMARY KEY | UNIQUE ( oszlop [, oszlop ] ... ) } // megszorításállapot
    | RENAME TO új_tábla
    | RENAME COLUMN oszlop TO új_oszlop
    | RENAME CONSTRAINT megszorítás TO új_megszorítás
    | DROP { COLUMN oszlop | ( oszlop [, oszlop ] ... }
    | DROP CONSTRAINT megszorítás | PRIMARY KEY | UNIQUE ( oszlop [, oszlop ] ... ) }
  }

Ha a tábla nem üres
- új oszlop, date típus, not null, z_dolgozo
*/

-- Nem hajtható végre, mert a sorok NULL értéket kapnának, amit mi nem engedünk
ALTER TABLE z_dolgozo
ADD (uj DATE NOT NULL);

-- Így már végrehajtható, mert alapértelmezetten a rendszeridővel tölti fel az oszlop sorait
ALTER TABLE z_dolgozo
ADD (uj DATE DEFAULT SYSDATE NOT NULL);

CREATE TABLE tabla ( a varchar(3) );

-- Az összes tábla kiíratása
SELECT * FROM tab;

DROP table tabla;

-- Ott marad a törölt tábla maradványa (a szemetesben van épp)
SELECT * FROM tab;

/*
  DROP TABLE tábla [ PURGE ]
  A szemetesben levő táblával 2 dolgot tehetünk:
  
  PURGE TABLE { tábla (végelegesen törli a táblát) | RECYCLEBIN (teljes kuka tartalmát törli) }
  FLASHBACK TABLE tábla [, tábla ] ... TO BEFORE DROP [ RENAME TO tábla ]
  
  Tehát:
  DROP TABLE tábla PURGE esetén végleges a tábla törlése
*/

PURGE TABLE tabla;

/*
  Nézettábla
  ----------
  CREATE [ OR REPLACE ] [ FORCE ] VIEW nézet_név
  [(
       másodlagos_név [ oszlopmegszorítás [ oszlopmegszorítás ] ... ] | táblamegszorítás
    [, másodlagos_név [ oszlopmegszorítás [ oszlopmegszorítás ] ... ] | táblamegszorítás ] ...
  )]
  AS alkérdés;
*/

CREATE OR REPLACE VIEW szomszed
AS
    SELECT * FROM
    p_bogrwo.z_dolgozo;

SELECT * FROM szomszed;

SELECT * FROM user_views;

GRANT ALL ON z_dolgozo TO public;
REVOKE ALL ON z_dolgozo FROM public;

CREATE VIEW MIKULAS (szsz, vezetek, kereszt, darab)
AS
  SELECT d.szsz, d.vnev, d.knev, COUNT(*)
  FROM z_dolgozo d JOIN z_hozzatartozo h ON d.szsz = h.dszsz
    GROUP BY d.szsz, d.vnev, d.knev;

SELECT * FROM MIKULAS;

-- A manipuláció nem engedélyezett, a z_hozzatartozo táblában is át kellene írni az adatokat
UPDATE MIKULAS SET darab=2;
-- A manipuláció akkor engedélyezett, ha a tábla nincs kapcsolatban más táblákkal

/*
  ALTER VIEW nézet_név
  {
    ADD táblamegszorítás
    | MODIFY CONSTRAINT megszoríás { REKY | NORELY }
    | DROP { CONSTRAINT megszorítás | PIRMARY KEY | UNIQUE ( oszlop [, oszlop ] ... ) }
  }
  
  Minden megszorítást megszűntet a nézetre vonatkozóan
  DROP VIEW nézet [ CASCADE CONSTRAINTS ]
  DROP TABLE table [ CASCADE CONSTRAINTS ] [ PURGE ]
  
  Szekvencia
  ----------
  CREATE SEQUENCE szekvencia_nev
  [ [ { START WITH | INCREMENT BY } egész ]
    [ { MAXVALUE egész | NOMAXVALUE } ]
    [ { MINVALUE egész | NOMINVALUE } ]
    [ { CYCLE | NOCYCLE } ]
  ] ...
  
  szekvencia_nev.NEXTVAL
  szekvencia_nev.CURRVAL
*/

CREATE SEQUENCE szek
START WITH 25
  INCREMENT BY 8;

SELECT szek.NEXTVAL FROM DUAL;
SELECT szek.CURRVAL FROM DUAL;

-----[10]-----

CREATE TABLE tábla
(
  sorszam NUMBER PRIMARY KEY,
  adat VARCHAR(30) NOT NULL
);

CREATE SEQUENCE szek5
START WITH 10
INCREMENT BY 3;

INSERT INTO tábla (sorszam, adat) VALUES (szek5.nextval,'alma');
INSERT INTO tábla (sorszam, adat) VALUES (szek5.nextval,'körte');
INSERT INTO tábla (sorszam, adat) VALUES (szek5.nextval,'dió');
COMMIT;

SELECT * FROM tábla;

/*
  ALTER SEQUENCE szekvencia
  { INCREMENT BY egész | { MAXVALUE egész | NOMAXVALUE } | { MINVALUE egész | NOMINVALUE } | { CYCLE | NOCYCLE } }
  [ -||- ] ...
*/

-- A szek5 nevű szekvenciát átdefiniáljuk, hogy 5össével növekedjen
ALTER SEQUENCE szek5 INCREMENT BY 5;
INSERT INTO tábla (sorszam, adat) VALUES (szek5.nextval,'mogyoró');
SELECT * FROM tábla;

ALTER SEQUENCE szek5 MAXVALUE 200 CYCLE;
-- Nem lehet MAXVALUE olyan érték, amely kisebb a current value értékénél

DROP SEQUENCE szek5;

-- Szinoníma
/*
  CREATE [ PUBLIC ] SYNONYM szinonima FRO objektum;
  // ALTER utasítás nem ételmezett
  DROP [ PUBLIC ] SYNONYM szinonima [ FORCE ];
  FORCE: a rá vonatkozó megszorításokat is törli
*/

-- Publikus szinonímát egyszerű felhasználók nem tudnak létrehozni
CREATE PUBLIC SYNONYM alma FOR z_dolgozo;

CREATE SYNONYM alma FOR z_dolgozo;
SELECT * FROM alma;

SELECT * FROM user_synonyms;
SELECT COUNT(*) FROM all_synonyms;

--

SELECT
  CASE Nem
    WHEN 'F' THEN 'férfiak'
    WHEN 'N' THEN 'nők'
    ELSE 'marslakók' -- Nem kötelező, de ha nincs egyezés valahol, és nincs ELSE ág, akkor (null) értéket ad
  END "Nem",
  COUNT( szsz ) "Létszám"
FROM z_dolgozo
  GROUP BY nem;

--

SELECT
  vnev || ' ' || knev "Név",
  CASE
    WHEN szsz LIKE '1%' THEN 'férfi'
    WHEN szsz LIKE '2%' THEN 'nő'
  END "Nem"
FROM z_dolgozo;

--

SELECT vnev || ' ' || knev "Név",
  CASE
    WHEN fizetes > 400000 THEN 'gazdag' -- Az első igaz ágon kiértékeli a kifejezést
    WHEN fizetes > 200000 THEN 'módos'
    ELSE 'szegény'
  END || ' ember' "Anyagi helyzet"
FROM z_dolgozo;

--

SELECT
  CASE
    WHEN fizetes > 400000 THEN 'gazdag'
    WHEN fizetes > 200000 THEN 'módos'
    ELSE 'szegény'
  END || ' ember' "Anyagi helyzet",
  COUNT(*) "Létszám"
FROM z_dolgozo
  GROUP BY
  CASE
    WHEN fizetes > 400000 THEN 'gazdag'
    WHEN fizetes > 200000 THEN 'módos'
    ELSE 'szegény'
  END || ' ember';

--

SELECT osz "Osztály szám", AVG(fizetes) "Átlagbér"
FROM z_dolgozo
  GROUP BY osz;

SELECT osz "Osztály szám", AVG(fizetes) "Átlagbér"
FROM z_dolgozo
  GROUP BY ROLLUP(osz); -- A lekérdezés utolsó sora az eredeti tábla sorainak "osz" mezőinek átlagát adja

--

-- Göngyölegszámítás
SELECT vonatszam, ulohelyszam, COUNT(*), SUM(ar)
FROM v_helyfoglalas
  GROUP BY ROLLUP(vonatszam, ulohelyszam);

SELECT vonatszam, ulohelyszam || ' ' || 'összesen', SUM(ar)
FROM v_helyfoglalas
  GROUP BY vonatszam, ulohelyszam || ' ' || 'összesen';

--

-- 1
CREATE TABLE sajat_adatok 
(
  VNEV, KNEV, EVFOLYAM, SZAK, USERNEV
)
AS
  SELECT 'Győri', 'László', 2, 'PTI', username
  FROM USER_USERS;

-- 2
ALTER TABLE sajat_adatok
  MODIFY
  (
    VNEV VARCHAR2(30),
    KNEV VARCHAR2(30),
    EVFOLYAM
      DEFAULT 2
      CONSTRAINT min_1 CHECK (EVFOLYAM >= 1),
      CONSTRAINT max_5 CHECK (EVFOLYAM <= 5),
    SZAK VARCHAR(50),
    USERNEV
      VARCHAR(10)
      CONSTRAINT egyedi UNIQUE,
    CONSTRAINT kulcs PRIMARY KEY (VNEV,KNEV)
  );

-----[11]-----

SELECT * FROM USER_USERS;

CREATE VIEW teszt AS
  SELECT * FROM negyzet;

INSERT INTO negyzet SELECT szam, szam*szam FROM szamok;

SELECT FLOOR(SYSDATE - TO_DATE('1991-03-10', 'YYYY-MM-DD')) || ' napja élek.' FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD, HH24:MI:SS') FROM DUAL;

-----[12]-----

SELECT * FROM USER_USERS;
SELECT * FROM P_DVPQVZ.z_hozzatartozo;

CREATE TABLE gyak1
(
  SZAM, GYOK
)
AS
  SELECT 1, 0 FROM DUAL;

INSERT INTO gyak1 VALUES (2,0);
INSERT INTO gyak1 VALUES (3,0);

-- Ki<null>ozzuk, hogy a tartomány szűkítés szabályos legyen
UPDATE gyak1 SET gyok=null;

ALTER TABLE gyak1
  MODIFY
  (
    GYOK NUMBER(7,3) -- 10 helyiértéken ábrázolható egy szám, ebből 3 tizedes helyiérték
  );

UPDATE gyak1 SET gyok=sqrt(szam);

/*
  Ha NUMBER(9)
    - 123456789 = Jó
    - 1234567890 = Nem jó
    - 12345678.9 = 12345679 = Jó
  Ha NUMBER(7,3)
    - 1234567 = Nem Jó
    - 1234 = Jó
    - 1234567.123 = Nem Jó
    - 1234.1234 = Nem jó
    - 1234.123 = Jó
*/

-- Gyakorlás 2

CREATE TABLE szamok
(
  SZAM NUMBER(10,2)
    CONSTRAINT szam_nemnull NOT NULL
    CONSTRAINT szam_elsodleges PRIMARY KEY,
  TIPUS VARCHAR(5)
    DEFAULT 'N/A'
    CONSTRAINT tipus_milehet CHECK (TIPUS IN('egesz','valos','N/A'))
);

CREATE SEQUENCE szamok_szek
START WITH 1
INCREMENT BY 1;

INSERT INTO szamok (szam) VALUES (szamok_szek.nextval);
INSERT INTO szamok VALUES (3.14,'valos');

CREATE TABLE negyzet
(
  SZAM NUMBER(10,2)
    CONSTRAINT szam2_nemnull NOT NULL
    CONSTRAINT szam2_elsodleges PRIMARY KEY
    CONSTRAINT szam2_kapcsolat REFERENCES szamok(szam),
  NEGYZET NUMBER(10,2)
    CONSTRAINT negyzet_nemnull NOT NULL
);

INSERT INTO negyzet SELECT szam, szam*szam FROM szamok;

SELECT szamok.szam, negyzet
  FROM szamok
  JOIN negyzet ON szamok.szam = negyzet.szam;

UPDATE szamok SET tipus = 
(
  CASE
    WHEN szam LIKE '%,%' THEN 'valos'
    ELSE 'egesz'
  END
);