-- Illusztrációk az SQL DDL és DML nyelvi elemeihez
-- az SE Adatbázisok hallgatói részére
--
-- v01 - 2017-04-24
-- Marton József
--
-- SQL DDL: SQL Data Definition Language:
--          az adatbázisokjektumok (pl. táblák) létrehozására és módosítására való utasítások
-- SQL DML: SQL Data Manipulation Language:
--          az adatbázis adatinak (táblák rekordjai) beszúrására/módosítására/törlésére való utasítások
--

-- SQL DDL
-- Táblák létrehozására használhatjuk az SQL Developer "New Table" parancsára
-- megjelenő grafikus felületet is, ami a háttérben SQL utasításokat generál.
-- Ajánlott az Advanced mód bekapcsolása.
--

-- Töröljük a korábban meglevő táblákat, hogy tuszta lappal induljunk.
--   A "cascade constraints" hatására akkor is törli a táblát, ha mutat rá másik táblából idegen kulcs.
--   A "purge" opció hatására a tábla véglegesen törlésre kerül, nélküle lehet, hogy csak a lomtárba kerül.
drop table ugyfel cascade constraints purge;
drop table arucikk cascade constraints purge;
drop table szamla cascade constraints purge;
drop table szamlasor cascade constraints purge;

-- Kulcsok fajtái
-----------------
-- A modellezett világ fogalmi rendszeréből származó ún. *üzleti kulcs*ok mellett
-- az adatbázistáblák legtöbbször tartalmaznak egy-egy numerikus mesterséges kulcsot
-- `id` vagy `<táblanév>_id` néven. Ez a mesterséges kulcs a tábla elsődleges kulcsa,
-- és ezt használjuk a külső kulcsokkal leírt hivatkozások megvalósításához is!

-- Ügyfelek tábla létrehozása
create table ugyfel (
  -- a tábla mesterséges kulcsát konvenció szerint id névvel és szám típussal hozzuk létre
  -- a "generated always as identity" az Oracle Database 12cR1 verziójától kezdődően automatikus kitöltést biztosít
  -- a korábbi verziókban ezt trigger + sequence objektumokkal kellett megoldani.
  id number generated always as identity increment by 3 start with 201 nocache
  -- ez lesz az ugyfel tábla üzleti kulcsa
  -- a "not null" jelentése, hogy a mező nem lehet kitöltetlen értékű
, ugyfelkod nvarchar2(30) not null
, nev nvarchar2(100) not null
, varos nvarchar2(100)
, cim nvarchar2(200)
  -- a tábla elsődleges kulcs (primary key) kényszerét a mesterséges kulcsra definiáljuk
  -- és konvenció szerint a neve TÁBLANÉV_pk
  -- Megjegyzés: az id mezőnél nem kötöttük ki, hogy "not null", mert a primary key az egyediség mellett a nem lehet üres tulajdonságot is előírja.
, constraint ugyfel_pk primary key (id)
  -- a tábla üzleti kulcsára egyediséget biztosító unique key kényszert definiálunk
  -- melynek konvenció szerint a neve TÁBLANÉV_uk_OSZLOPNÉV
, constraint ugyfel_uk_ugyfelkod unique (ugyfelkod)
);
comment on table ugyfel is 'Ügyfél törzsadatok táblája.';

-- Termékek tábla létrehozása
create table arucikk (
  id number generated always as identity start with 3000 nocache
, cikkszam nvarchar2(30) not null
, megnevezes nvarchar2(100) not null
, netto_egysegar number
  -- a mező üresen hagyása esetén a default érték kerül be az új rekordokba
, afa_kulcs number default 27
-- felvehetnénk külön mezőként a bruttó egységárat, de mivel számítható, ezt nem tesszük
--, brutto_egysegar number
, me nvarchar2(30)
, vonalkod nvarchar2(30)
, constraint arucikk_pk primary key (id)
, constraint arucikk_uk_cikkszam unique (cikkszam)
);
comment on table arucikk is 'Árucikkek törzsadat táblája.';
comment on column arucikk.me is 'Mennyiségi egység, pl. db, hónap, kg.';

-- Számla tábla létrehozása
create table szamla (
  id number generated always as identity start with 1 nocache
, szamlaszam nvarchar2(30) not null
  -- "default sysdate": a számla kelte automatikusan az aktuális dátum+időpontal kerüljön kitöltésre, ha üresen hagyjuk
, kelt date default sysdate not null
, fizetesi_hatarido date
  -- az idegen kulcsok konvekció szerint a mesterséges kulcsra hivatkoznak
  -- és a nevük végén _id áll
, vevo_id number
, constraint szamla_pk primary key (id)
, constraint szamla_uk_szamlaszam unique (szamlaszam)
  -- az idegen kulcsokat megadó kényszer neve konvenció szerint
  -- HIVATKOZÓ-TÁBLANÉV_HIVATKOZOTT-TÁBLANÉV_fk
, constraint szamla_ugyfel_fk foreign key (vevo_id) references ugyfel (id)
  -- A mezők közötti logikai összefüggések az ún. check kényszerrel írhatók elő,
  -- amelyek az SQL lekérdezés WHERE részében szereplő feltételekre hasonlítanak.
  -- Az adatbáziskezelő mindig biztosítja, hogy a táblában szereplő rekordok teljesítik az így előírt feltételt.
  --
  -- Az alábbi példa előírja, hogy ha kitöltöttük a kelt és a fizetesi_hatarido mezőket is,
  -- akkor utóbbi nem előzheti meg a számla keltét.
, constraint szamla_ck_datumok check (kelt <= fizetesi_hatarido)
);
comment on table szamla is 'Számlák táblája.';
comment on column szamla.fizetesi_hatarido is 'A számla fizetési határideje. Ha kitöltött, akkor nem lehet korábbi, mitn a számla kelte.';

-- Számla tételeit tartalmazó számlasor tábla létrehozása
create table szamlasor (
  id number generated always as identity start with 1 nocache
, arucikk_id number not null
, szamla_id number not null
, mennyiseg number not null
, netto_egysegar number not null
, afa_kulcs number not null
, brutto_osszar number not null
, constraint szamlasor_pk primary key (id)
, constraint szamlasor_szamla_fk foreign key (szamla_id) references szamla (id)
, constraint szamlasor_arucikk_fk foreign key (arucikk_id) references arucikk (id)
);
comment on table szamlasor is 'Számla tételeit tartalmazó számlasor tábla';
comment on column szamlasor.brutto_osszar is 'Bár az adat számítható, adóügyi okból pontosan tároljuk.';

-- Adatok bevitele: az INSERT utasítás
-- A tábla neve után felsoroljuk, hogy melyik mezők adatait adjuk meg,
-- majd a VALUSES után ebben a sorrendben kell megadni a mezők értékét.
--
-- A mesterséges kulcs (id) mezőbe nem viszünk be adatot, annak kitöltéséről az adatbáziskezelő gondoskodik.
insert into arucikk
  (cikkszam, megnevezes, netto_egysegar, afa_kulcs, me, vonalkod)
values
  ('T001', 'Termelői tej, 3,6%', 300, 5, 'liter', '2001001000000')
;
insert into arucikk
  (cikkszam, megnevezes, netto_egysegar, afa_kulcs, me, vonalkod)
values
  ('T002', 'ESL tej, 2,8%', 270, 27, 'liter', '2001002000000')
;
insert into arucikk
  (cikkszam, megnevezes, netto_egysegar, afa_kulcs, me, vonalkod)
values
  ('Z001', 'Friss tojás M', 35, 5, 'darab', '2002001000000')
;
insert into arucikk
  (cikkszam, megnevezes, netto_egysegar, afa_kulcs, me, vonalkod)
values
  ('K001', 'Tükörtojás (reggeli)', 140, 27, 'darab', '2006001000000')
;
insert into arucikk
  (cikkszam, megnevezes, netto_egysegar, afa_kulcs, me, vonalkod)
values
  ('K002', 'Főtt záptojás', 70, 27, 'darab', '2006003000000')
;
insert into arucikk
  (cikkszam, megnevezes, netto_egysegar, afa_kulcs, me, vonalkod)
values
  ('K003', 'Bundáskenyér (reggeli)', 96, 27, 'darab', '2006004000000')
;

-- Adatok módosítása: az UPDATE utasítás
-- a tábla neve után megadjuk, hogy melyik mező milyen értékre módosul

-- A tojás (Z001 cikkszám) ára 32 Ft-ra módosul, és javítjuk a megnevezését is
update arucikk
   set netto_egysegar = 32
     , megnevezes = 'Friss tyúktojás M'
 where cikkszam = 'Z001'
;

-- A tejek ára 10%-kal emelkedik
update arucikk
   set netto_egysegar = netto_egysegar * 1.1
 where 1=1
    -- cikkszam in (elem1, elem2, ...) jelentése: cikkszam = elem1 OR cikkszam = elem2 OR ...
   and cikkszam in ('T001', 'T002')
;

-- Rekordok törlése: a DELETE utasítás
-- főtt záptojást soha senki nem vett, ezért kikerül a kínálatunkból
delete from arucikk
 where cikkszam = 'K002'
;

-- Adatbevitel importtal
-- az alábbi adatokból készítsünk táblázatot, és importáljuk az ügyfél táblába négy lépésben:
--  1. előbb egy tmp_ugyfel ideiglenes táblába importáljuk, majd
--  2. a bevitel ellenőrzése után SQL utasítással töltjük át
--  3. módosítások mentése (commit)
--  4. az ideiglenes tábla eldobása
/*
"UGYFELKOD";"NEV";"TELEPULES";"CIM"
"AF80999";"Fatér Aurél";"Bábolna";"Arany János utca 73/N"
"EW52347";"Csaplár Berta";"Baktalórántháza";"Kassai utca 37/J"
"FG11008";"Benes Viktor";"Balatonfüred";"Kassai utca 62/J"
"VV76141";"Marik Elek";"Bátonyterenye";"Kölcsey utca 13/E"
"WC20625";"Almássy Annamária";"Beled";"Liget tér 59/I"
"JO27566";"Varga Viktor";"Berhida";"Jázmin utca 19/B"
"XF52917";"Gyapas Hortenzia";"Csepreg";"Pagony utca 128/G"
"OH08909";"Tegzes Fédra";"Csongrád";"Barackos tér 119/X"
"XT89868";"Pánczél Péter";"Debrecen";"Erdélyi tér 54/R"
"ZX76595";"Sellei Zsombor";"Derecske";"Menyecske utca 87/N"
"UN17251";"Kóbor Flórián";"Dombóvár";"Jázmin utca 91/J"
"QD79710";"Spáth Narcisszusz";"Dunavecse";"Lövőház utca 2/V"
"GT25675";"Fehér Luca";"Eger";"Tóvirág utca 9/C"
"CX13930";"Rafay Rafaella";"Ercsi";"Arany János utca 85/X"
"II30033";"Ecsery Szabina";"Fehérgyarmat";"Ferenc körút 9/M"
"NH53424";"Maul Berta";"Fertőd";"Ferenc körút 59/X"
"QG82020";"Molnár Irma";"Fonyód";"Széchenyi tér 17/F"
"XV29823";"Gát Kató";"Füzesabony";"Széchenyi tér 2/H"
"QX98176";"Baán Stella";"Füzesabony";"Lövőház utca 37/L"
"SE76067";"Marcz Violetta";"Hajdúdorog";"Szilas park 55/J"
"HZ74539";"Sörös Hajnalka";"Isaszeg";"Gogol utca 4/P"
"HL98792";"Paál Réka";"Sajóbábony";"Krisztina tér 103/T"
"OD85396";"Csaplár Magdolna";"Szabadszállás";"Körte dűlő 93/U"
"ML42949";"Simon Etel";"Szeged";"Felüljáró út 91/Q"
"KG52594";"Ország Botond";"Szeghalom";"Erdélyi tér 106/J"
"VB56014";"Szigedi Noémi";"Székesfehérvár";"Kálvin tér 67/R"
"QO02290";"Olasz Mirella";"Szerencs";"Hargitai utca 86/Z"
"SX73554";"Urbán Grizelda";"Szikszó";"Iskola utca 46/I"
"BL42315";"Henn Leó";"Szombathely";"Petőfi Sándor utca 96/L"
"EI32257";"Sellei Antal";"Tata";"Erkel utca 92/X"
"ON74675";"Benes Matild";"Tiszakécske";"Lövőház utca 13/W"
"AJ32622";"Erdős Celesztin";"Tiszalök";"Alföldi utca 100/Q"
"DX45109";"Havelda Krisztina";"Törökszentmiklós";"Kossuth Lajos utca 113/K"
"EZ72014";"Szabados Odett";"Vámospércs";"Batthyány tér 75/L"
"ZB51471";"Fellegi Antal";"Villány";"Hunyadi János út 84/W"
"IT88697";"Havelda Rita";"Kecel";"Pacsirta utca 74/B"
"WG46743";"Henn Sára";"Kecskemét";"Dohány utca 114/P"
"AL08849";"Pál Károly";"Keszthely";"Bérkert utca 68/A"
"MJ43594";"Somfalvy Péter";"Kiskunmajsa";"Barackos tér 101/L"
"EL77632";"Erdős Barbara";"Kistarcsa";"Ferenc körút 93/C"
"ID66204";"Almássy Cecília";"Kisújszállás";"Hargitai utca 25/X"
"PM79417";"Kis Dömötör";"Komádi";"Erkel utca 89/I"
"TO14121";"Fellegi Irén";"Kunhegyes";"Kossuth tér 38/V"
"ZZ04263";"Pittner Álmos";"Medgyesegyháza";"Barackos tér 107/I"
"HP60160";"Kutas Domonkos";"Mezőcsát";"Fő utca 23/I"
"NB65779";"Ruzsin Imre";"Mezőhegyes";"Móricz Zsigmond utca 113/T"
"QX70252";"Gát Eszter";"Orosháza";"Szilas park 30/H"
"XE76517";"Ördög Gusztáv";"Putnok";"Diófa utca 65/J"
*/
-- nézzük meg a létrejött import táblát
select *
  from tmp_ugyfel
;
-- vigyük át az adatokat az ügyfél táblába:
-- az INSERT utasítás VALUES része helyett egy SELECT utasítás is írható,
-- ami szolgáltatja a bevinni kívánt rekordokat
insert into ugyfel
  (ugyfelkod, nev, varos, cim)
select ugyfelkod, nev, telepules, cim
  from tmp_ugyfel
;
select *
  from ugyfel
;
-- mentsük a bevitelt. Ezt csinálja a zöld pipa ikon is.
commit;
-- dobjuk el az ideiglenes táblát
drop table tmp_ugyfel purge;

-- Join update
--------------
-- Néhány termék ára megváltozott, amit az üzletvezető egy táblázatban ad át
-- Ezeket a módosításokat kell átvezetnünk az adatbázison.
--
-- Ehhez:
--  1.a. töltsük be az alábbi adatokat egy tmp_uj_arak ideiglenes táblába, és
--  1.b. jelöljük kulcsnak a cikkszámot
--  2. írjunk egy SQL lekérdezést, ami egymás mellé írja a régi és az új árat
--  3. módosítsuk az árakat
--  4. ellenőrizzük az eredményt
--  5. mentsük a módosításokat (commit)
--  6. dobjuk el az ideiglenes táblát
/*
"CIKKSZAM";"NETTO_EGYSEGAR"
"T001";290
"T002";260
"K001";130
"K003";100
*/
select *
  from tmp_uj_arak
  ;

-- 1.b.
alter table tmp_uj_arak
  add constraint tmp_uj_arak_uk_cikkszam unique (cikkszam)
;
-- 2.
select t.cikkszam
     , t.netto_egysegar
     , u.netto_egysegar as uj_ar
  from arucikk t
     , tmp_uj_arak u
 where 1=1
   and t.cikkszam = u.cikkszam
;
-- 3.
update (
select t.cikkszam
     , t.netto_egysegar
     , u.netto_egysegar as uj_ar
  from arucikk t
     , tmp_uj_arak u
 where 1=1
   and t.cikkszam = u.cikkszam
) ar
   set netto_egysegar = uj_ar
;
-- 4. (végezhetjük (2) szerint is)
select *
  from arucikk
;
-- 5.
commit;
-- 6.
drop table tmp_uj_arak purge;

-- Adatbevitel imporrtal a termék táblába az ügyfélhez hasonlóan
/*
"cikkszám";"megnevezés";"nettó ár";"áfa kulcs";"mennyiségi egység";"vonalkód"
"E001";"Fodros nagykocka 500g";420;27;"darab";200900000012
"E002";"Balaton szelet 33g";50;27;"darab";200900000013
"E003";"Tejes kifli";12;27;"darab";200900000014
"E004";"Baracklé 1 liter";220;27;"darab";200900000015
"V006";"Vezeték";10;27;"méter";200900000016
"V007";"Biztosíték";1640;27;"darab";200900000017
"V008";"AA szárazelem";100;27;"darab";200900000018
"V009";"AAA szárazelem";110;27;"darab";200900000019
"V010";"Tartós elem, AA, 2db";250;27;"csomag";200900000020
"V011";"Tartós elem, AAA, 2db";280;27;"csomag";200900000021
"V012";"Tartós elem, AA, 4db";400;27;"csomag";200900000022
"V013";"Tartós elem, AAA, 4db";450;27;"csomag";200900000023
*/