GROUP BY:

• Csoportosítás: A GROUP BY parancsot akkor használjuk, amikor egy táblát oszlop(ok) alapján csoportosítani szeretnénk. Ez különösen hasznos, ha aggregáló függvényeket (pl. COUNT, AVG, SUM, MAX, MIN) szeretnénk alkalmazni csoportokra.
• Működése: A megadott oszlop(ok) alapján csoportosítja az adatokat, és minden egyes csoporthoz egy sor eredményt ad vissza.

SELECT varos, COUNT(*) AS 'Dolgozók száma' FROM dolgozo GROUP BY varos;

Ez megadja, hogy dolgozó van városonként. 

HAVING:

• Utólagos szűrés: A HAVING kifejezés az aggregált adatokra vonatkozó szűrési feltételeket határozza meg. Ez különbözik a WHERE-től, mert WHERE-rel csak az eredeti adatokra lehet szűrni, míg HAVING-gel az aggregált eredményeket szűrhetjük.

  SELECT osztaly, AVG(fizetes) AS 'Átlagfizetés' FROM dolgozo GROUP BY osztaly HAVING AVG(fizetes) > 500000;

Ez a lekérdezés megadja, hogy mely osztályokban haladja meg az átlagfizetés az 500.000 forintot.

FELADATOK

1. Listázd ki, hogy az egyes városokból hány dolgozó érkezik, és nevezd el a várost tartalmazó oszlopot "Város"-nak, a darabszámot pedig "Dolgozók száma"-nak.

SELECT varos AS 'Város', COUNT(*) AS 'dolgozók száma'
FROM dolgozo
GROUP BY varos;

2.  Listázd ki, hogy az egyes osztályokban hány dolgozó van. Az oszlopok neve legyen "Osztály" és "Létszám".
SELECT o.onev AS 'Osztály', COUNT(*) AS 'Létszám' FROM dolgozo d INNER JOIN osztaly o ON d.oid = o.oid GROUP BY o.onev;

3. Listázd ki azoknak az osztályoknak a nevét, ahol az átlagfizetés meghaladja a 400000 Forintot
SELECT o.onev AS 'Osztály', AVG('d.fizetes') AS 'átlagfizetés' FROM dolgozo d INNER JOIN osztaly o ON o.oid = d.oid GROUP BY o.onev HAVING AVG(d.fizetes) > 400000;

 4. Melyik városból származik a legtöbb dolgozó? Használj MAX és COUNT függvényeket a megoldáshoz.
SELECT varos, COUNT(*) AS 'Dolgozok száma' FROM dolgozo GROUP BY varos ORDER BY COUNT(*) DESC LIMIT 1;

5. Az egyes osztályok átlagfizetése mellett listázd ki a minimális fizetést is az adott osztályban.
SELECT o.onev AS 'Osztály', AVG(d.fizetes) AS 'Átlagfizetés', MIN(d.fizetes) AS 'Minimális fizetés' FROM dolgozo d INNER JOIN osztaly o ON d.oid = o.oid GROUP BY o.onev;

Az aggregáló szó azt jelenti, hogy valami összegez, csoportosít vagy összefoglal adatokat. Az SQL aggregáló függvények olyan speciális függvények, amelyek egy adathalmaz értékeit egyetlen eredménybe sűrítik.

Például:

• AVG: Kiszámolja az adathalmaz átlagát.
• SUM: Összeadja az adathalmaz elemeit.
• COUNT: Megszámolja az elemek számát.
• MIN: Kiválasztja a legkisebb értéket.
• MAX: Kiválasztja a legnagyobb értéket.

Az SQL aggregáló függvények segítségével a táblázatokból adatokat lehet összesíteni és különböző számításokat végezni. Ezeket gyakran használjuk statisztikai és riportkészítési feladatokhoz. Az alábbiakban részletezve bemutatom a legfontosabb függvényeket és azok használatát.

Aggregáló függvények:

1. AVG (átlag): Egy oszlop értékeinek átlagát számítja ki.
   • Példa: SELECT AVG(fizetes) AS 'Átlagfizetés' FROM dolgozo;
2. COUNT (darabszám): Az adott oszlop értékeinek számát adja vissza.
   • Példa: SELECT COUNT(*) AS 'Dolgozók száma' FROM dolgozo;
3. MIN (legkisebb): Az oszlop legkisebb értékét adja vissza.
   • Példa: SELECT MIN(fizetes) AS 'Legalacsonyabb fizetés' FROM dolgozo;
4. MAX (legnagyobb): Az oszlop legnagyobb értékét adja vissza.
   • Példa: SELECT MAX(fizetes) AS 'Legmagasabb fizetés' FROM dolgozo;
5. SUM (összeg): Az oszlop értékeinek összegét számítja ki.
   • Példa: SELECT SUM(fizetes) AS 'Összes fizetés' FROM dolgozo;

Feladatok 

1. Mikor született a legfiatalabb dolgozó?
SELECT MAX(szuldat) AS 'Legfiatalabb születési dátum' FROM dolgozo;

 2. Hány női dolgozó van?
SELECT COUNT(*) AS 'Nők száma' FROM dolgozo WHERE nem = 'N';

3: Mekkora a dolgozók átlagos életkora?
SELECT AVG(YEAR(CURRENT_DATE()) - YEAR(szuldat)) AS 'Átlag életkor' FROM dolgozo;

Részletes bontás

• YEAR(CURRENT_DATE()): Ez a rész kinyeri az aktuális év számát. Például, ha ma 2025. január 5-e van, akkor az eredmény 2025.

• YEAR(szuldat): Ez a függvény az adott dolgozó születési dátumából (pl. 1985-07-20) csak az évet veszi ki. Ha az alkalmazott 1985-ben született, akkor az eredmény 1985.

• YEAR(CURRENT_DATE()) - YEAR(szuldat): Ez a különbség megadja a dolgozó életkorát az aktuális év alapján. Például:

  • 2025 (aktuális év) - 1985 (születési év) = 40 év.

• AVG(...): Az AVG függvény kiszámolja az összes dolgozó életkorának átlagát. Tehát összeadja az összes életkort, majd elosztja a dolgozók számával.

• AS 'Átlag életkor': Ez csak egy alias, amely nevet ad az eredménynek, hogy az oszlop neve az eredménytáblában "Átlag életkor" legyen.

4. Listázd ki a legmagasabb fizetést
SELECT MAX(fizetes) AS 'legmagasabb fizetes' FROM dolgozo

5. Számold meg, hány alkalmazott van a cégnél
SELECT COUNT(*) AS 'dolgozok_szama' FROM dolgozo

6. Add meg a dolgozok összes fizetését
SELECT SUM(fizetes) AS 'Összes fizetés' FROM dolgozo;

7. Listázd ki a legidősebb dolgozó születési dátumát
SELECT MIN(szuldat) AS 'Legidősebb születési dátum' FROM dolgozo;

8. Számold meg, hány dolgozó van, akinek a fizetése 400000 vagy annál nagyobb
SELECT COUNT(*) AS 'dolgozok száma' FROM dolgozo WHERE fizetes >= 400000;

Az SQL-ben a többtáblás lekérdezések lehetővé teszik, hogy különböző táblák adatait kombináljuk és egységes eredményként jelenítsük meg. Ehhez az JOIN utasítást használjuk. Nézzük meg részletesen, hogyan működnek ezek a lekérdezések.

1. INNER JOIN – Csak az egyező sorok

Az INNER JOIN segítségével csak azok a sorok kerülnek be az eredménybe, amelyeknél a két tábla közötti feltétel teljesül.
Példa: SELECT dolgozo.*, osztaly.onev FROM dolgozo INNER JOIN osztaly ON dolgozo.oid = osztaly.oid WHERE varos = 'Veszprém';

• dolgozo: Ez a dolgozók adatait tartalmazza.
• osztaly: Ez a dolgozók osztályát tartalmazza.
• ON dolgozo.oid = osztaly.oid: Ez a kapcsolódási feltétel (a dolgozó és az osztály kapcsolatát adja meg).
• WHERE varos = 'Veszprém': Csak a veszprémi dolgozókat listázza ki.

Eredmény:

Az eredmény csak azokat a dolgozókat tartalmazza, akiknek van érvényes oid értékük mindkét táblában.

2. LEFT JOIN – Minden az első táblából, hiányzó értékekkel

A LEFT JOIN az első táblából (jelen esetben a dolgozo) minden rekordot megjelenít, akkor is, ha a második táblában (osztaly) nincs hozzá tartozó adat.

SELECT dolgozo.*, osztaly.onev FROM dolgozo LEFT JOIN osztaly ON dolgozo.oid = osztaly.oid WHERE varos = 'Veszprém';

Az eredmény tartalmazza mind a veszprémi dolgozókat, mind azokat, akiknek nincs osztályuk (az onev mezőjük NULL lesz).

3. RIGHT JOIN – Minden a második táblából

A RIGHT JOIN ritkábban használt, de fordított logikát követ: a második táblából minden rekordot megjelenít, akkor is, ha az első táblában nincs hozzá tartozó adat.

SELECT dolgozo.*, osztaly.onev FROM dolgozo RIGHT JOIN osztaly ON dolgozo.oid = osztaly.oid;

Magyarázat a példák alapján

1. Kapcsolódó mezők: A dolgozo és az osztaly táblák az oid mezőn keresztül kapcsolódnak.
2. Feltételek: A WHERE feltétel szűkíti az eredményt, például csak veszprémi dolgozókat mutat meg.
3. NULL értékek: A LEFT JOIN esetében a második táblából hiányzó adatok helyett NULL érték jelenik meg.

Gyakorlatban

• INNER JOIN: Használd, ha csak az egyező rekordokra van szükséged.
• LEFT JOIN: Használd, ha az első tábla összes rekordjára szükséged van, de a második táblából csak a meglévő adatok érdekelnek.
• RIGHT JOIN: Használd, ha fordított logikával dolgozol (ritkábban használt).

Feladatok

1. Listázd ki a veszprémi dolgozók nevét és osztálynevét
SELECT d.nev, o.onev FROM dolgozo d INNER JOIN osztaly o ON d.oid = o.oid WHERE d.varos = 'Veszprém';

Ez a lekérdezés egy többtáblás SQL lekérdezés, amely két táblát – dolgozo és osztaly – kapcsol össze az oid mezőn keresztül. Lássuk részletesen, miért van szükség a d.nev, o.onev, valamint a kapcsolás (ON d.oid = o.oid) megadására:

1. Miért kell d.nev és o.onev?

Amikor két vagy több táblát kapcsolsz össze, a lekérdezés során a mezők nevét egyértelműen meg kell határozni, hogy a rendszer tudja, melyik táblából hivatkozol az adott mezőre.

• d.nev: Ez azt jelenti, hogy a nev mezőt a dolgozo táblából szeretnéd használni. Az alias (d) egy rövidítés, amelyet a dolgozo tábla azonosítására hoztál létre. Ha csak nev-et írnál, a rendszer nem tudná, hogy a nev mezőt a dolgozo vagy az osztaly táblából kéred (ha esetleg mindkét táblában van ilyen nevű mező).

• o.onev: Ugyanígy, ez azt jelenti, hogy a onev mezőt az osztaly táblából szeretnéd használni. Az alias (o) itt is segít az osztaly tábla egyértelmű azonosításában.

Alias használat előnyei:

• Rövidebb, áttekinthetőbb kódot írhatsz (pl. d helyett nem kell mindig dolgozo-t kiírni).
• Egyértelművé teszi, hogy melyik táblából származik az adott mező.

2. Miért kell az ON d.oid = o.oid?

Ez a lekérdezés INNER JOIN típusú kapcsolást alkalmaz, ami azt jelenti, hogy csak azokat a sorokat adja vissza, amelyek mindkét táblában egyeznek a megadott feltétel szerint.

• d.oid: A dolgozo táblában az oid mezőt használjuk az osztály azonosítására.
• o.oid: Az osztaly táblában az oid mező az osztály azonosítója.

Az ON d.oid = o.oid feltétel azt mondja meg a rendszernek, hogy csak azokat a dolgozókat kapcsolja össze a megfelelő osztályokkal, ahol a két tábla oid értékei megegyeznek.

Kapcsolás nélkül: Ha az ON feltételt kihagynád, a rendszer nem tudná, hogyan kapcsolja össze a táblákat, és minden sor minden sorral párosítva jelenne meg (ezt cross join-nak hívjuk), ami hibás eredményt adna.

3. Mit csinál a teljes lekérdezés?

1. A dolgozo tábla és az osztaly tábla oid mezője alapján összekapcsolódik.
2. Csak azokat a dolgozókat listázza, akik Veszprémből származnak.
3. A visszaadott eredmény tartalmazza:
   • A dolgozó nevét a dolgozo táblából (d.nev).
   • Az osztály nevét az osztaly táblából (o.onev).

2. feladat: Listázd ki csak azokat a dolgozókat, akik "Veszprém" városban dolgoznak, az osztály nevükkel együtt!

SELECT d.nev, d.fizetes, o.onev FROM dolgozo d INNER JOIN osztaly o ON d.oid = o.oid WHERE d.varos = 'Veszprém';

3. feladat: Listázd ki az összes osztályt és az ahhoz tartozó dolgozók számát!
SELECT o.onev, COUNT(d.szigszam) AS dolgozok_szama FROM osztaly o LEFT JOIN dolgozo d ON o.oid = d.oid GROUP BY o.onev;

Azért használtam itt LEFT JOIN-t, hogy kiadja azokat az értékeket is, ahol nincs hozzárendelve senki (pl egy olyan osztályt, ahol nincs alkalmazott)

4. feladat: Listázd ki azokat az osztályokat, amelyekhez még nem tartozik dolgozó!
SELECT o.onev FROM osztaly o LEFT JOIN dolgozo d ON o.oid = d.oid
WHERE d.oid IS NULL

5. feladat: Listázd ki a legmagasabb fizetéssel rendelkező dolgozót minden osztályban!

SELECT o.onev, d.nev, MAX(d.fizetes) AS max_fizetes FROM osztaly o INNER JOIN dolgozo d ON o.oid = d.oid GROUP BY o.onev;

6. feladat: Listázd ki a dolgozókat, akik ugyanazon az osztályon dolgoznak, mint Fehér Alajos

SELECT d.nev FROM dolgozo d INNER JOIN osztaly o ON d.oid = o.oid WHERE d.oid = ( SELECT oid FROM dolgozo WHERE nev = 'Fehér Alajos' ) AND d.nev != 'Fehér Alajos';

Részekre bontva: SELECT d.nev FROM dolgozo d INNER JOIN osztaly o ON d.oid = o.oid - a dolgozó táblából kilistázza a neveket és összekapcsolja az osztály táblával, így mindkét tábla értékei elérhetőek ebben a lekérdezésben

WHERE d.oid = ( SELECT oid FROM dolgozo WHERE nev = 'Fehér Alajos' )

• Kiválasztja azokat a dolgozókat, akik ugyanahhoz az oid-hoz (osztályhoz) tartoznak, mint "Fehér Alajos".
• Az allekérdezés (SELECT oid FROM dolgozo WHERE nev = 'Fehér Alajos') visszaadja az oid értékét, amely az "Fehér Alajos"-hoz tartozik.
• A WHERE d.oid = (...) feltétel ezt az oid értéket használja, hogy megtalálja azokat a dolgozókat, akik ugyanabban az osztályban vannak, mint "Fehér Alajos".

  AND d.nev != 'Fehér Alajos'; - kizárja Fehér Alajost az eredmények közül. 

7. feladat: Listázd ki a dolgozók városait, de csak egyszer jelenjen meg minden város!

SELECT DISTINCT varos FROM dolgozo;

 8. feladat: Listázd ki azokat a dolgozókat, akiknek a fizetése meghaladja az osztályuk átlagfizetését!

SELECT d.nev, d.fizetes, o.onev FROM dolgozo d INNER JOIN osztaly o ON d.oid = o.oid WHERE d.fizetes > ( SELECT AVG(d2.fizetes) FROM dolgozo d2 WHERE d2.oid = d.oid );

9. feladat: Listázd ki az osztályokat, amelyekben több mint 2 dolgozó van!

SELECT o.onev, COUNT(d.szigszam) AS dolgozok_szama FROM osztaly o INNER JOIN dolgozo d ON o.oid = d.oid GROUP BY o.onev HAVING COUNT(d.szigszam) > 2;

Feladatok
(Az előző bejegyzésben készített adatbázist veszem alapul)

1. Listázd ki a dolgozók adatait név szerint csökkenő sorrendben
SELECT FROM dolgozo
ORDER BY dolgozo.nev DESC

2. Készíts egy lekérdezést, amely megjeleníti a dolgozó nevét, egy mezőben az irányítószámot és várost '-'-el elválasztva város néven, valamint az utca_hsz mezőt.
SELECT nev AS dolgozó_neve, CONCAT(irsz, '-', varos) AS város, utca_hsz FROM dolgozo;

• nev AS dolgozó_neve:

  • A dolgozó neve (nev mező) jelenik meg egyértelmű címkével (dolgozó_neve).

• CONCAT(irsz, '-', varos) AS város:

  • A CONCAT függvény összefűzi az irsz (irányítószám) és varos (város) mezőket, egy - karakterrel középen.
  • Az eredményt "város" néven aliasolja, hogy az oszlop neve egyértelmű legyen.

• utca_hsz:

  • Az utca és házszámot tartalmazó mezőt közvetlenül jeleníti meg.

• FROM dolgozo:

  • A dolgozo tábla az adatforrás.

3. Listázd ki, milyen városokból jönnek a dolgozók
SELECT DISTINCT varos AS városok FROM dolgozo;

• SELECT DISTINCT:

  • Csak az egyedi városokat jeleníti meg, így egy város csak egyszer fog szerepelni az eredményben.

• varos AS városok:

  • A varos mező értékeit "városok" néven jeleníti meg.

• FROM dolgozo:

  • A dolgozo táblából kérdezi le az adatokat.

4. Listázd ki ABC sorrendben az első 5 dolgozót
SELECT nev FROM dolgozo ORDER BY nev ASC LIMIT 5;

• SELECT nev:

  • Csak a nev (név) mezőt jeleníti meg az eredményben.

• ORDER BY nev ASC:

  • A nev mező szerint rendezi az adatokat növekvő (ABC) sorrendben. Az ASC az alapértelmezett, de expliciten is megadható.

• LIMIT 5:

  • Csak az első 5 rekordot adja vissza az eredményben.

5. Listázd ki ABC sorrendben az 5-7. dolgozót
SELECT nev FROM dolgozo ORDER BY nev ASC LIMIT 3 OFFSET 4;

• SELECT nev:

  • Csak a nev (név) mezőt jeleníti meg az eredményben.

• ORDER BY nev ASC:

  • A nev mező szerint rendezi az adatokat növekvő (ABC) sorrendben.

• LIMIT 3:

  • Csak 3 rekordot ad vissza, mert az 5., 6. és 7. dolgozót szeretnénk látni.

• OFFSET 4:

  • Kihagyja az első 4 rekordot (az 1-4. dolgozót).

6. Listázd ki az 5 legjobban kereső alkalmazott adatait

SELECT nev, fizetes, szigszam, varos, utca_hsz FROM dolgozo ORDER BY fizetes DESC LIMIT 5;

• SELECT nev, fizetes, szigszam, varos, utca_hsz:

  • Csak azokat az oszlopokat jeleníti meg, amelyekre szükséged van: név, fizetés, személyi szám (szigszam), város, utca és házszám.

• ORDER BY fizetes DESC:

  • A dolgozókat a fizetes (fizetés) mező szerint rendezi csökkenő sorrendben (DESC).

• LIMIT 5:

  • Az eredményhalmazból csak az első 5 rekordot jeleníti meg, azaz a legjobban kereső 5 dolgozót.

7. Listázd ki a veszprémi vagy ajkai férfi alkalmazottak nevét, fizetését
SELECT nev, fizetes FROM dolgozo WHERE (varos = 'Veszprém' OR varos = 'Ajka') AND nem = 'F';    vagy
SELECT nev, fizetes FROM dolgozo WHERE varos IN ('Veszprém', 'Ajka') AND nem = 'F';

• SELECT nev, fizetes:

  • Csak a nev (név) és a fizetes (fizetés) oszlopokat jeleníti meg.

• WHERE (varos = 'Veszprém' OR varos = 'Ajka'):

  • A varos mezőt szűri úgy, hogy csak azok a rekordok jelenjenek meg, ahol a város "Veszprém" vagy "Ajka".

• AND nem = 'F':

  • További feltételként a nem mezőt szűri, hogy csak a férfi alkalmazottakat ('F') vegye figyelembe.

Utasítások:

SELECT [DISTINCT / ALL] – Mit kérdezel le?

• DISTINCT: Csak egyedi értékeket szeretnél az eredményben.
• ALL: Minden értéket megjelenít, még az ismétlődőket is (ez az alapértelmezett).
  Példa: SELECT DISTINCT nev FROM dolgozo; - az összes egyedi nevet adja vissza a dolgozo táblából
  SELECT ALL város FROM dolgozó; - minden várost visszaad, az ismétlésekkel együtt

{* / [mezőlista [AS alias]]} - melyik mezőket kérdezed le?

• * (csillag): Minden mezőt lekér a táblából.
  Példa: SELECT * FROM dolgozó; → A dolgozó tábla összes adata megjelenik.
• Mezőlista: Csak bizonyos mezőket (oszlopokat) kérünk le.
  Példa: SELECT nev, fizetes FROM dolgozó; → Csak a nevek és fizetések jelennek meg.
• AS alias: A mezők vagy táblák nevéhez egyedi megjelenítési nevet (alias) rendelhetünk.
  Példa: SELECT nev AS dolgozo_neve FROM dolgozó; → A nev mező helyett dolgozo_neve lesz látható.

[WHERE feltétel] – Milyen feltétel alapján szűrünk?

• Szűri az adatokat, csak a feltételnek megfelelő rekordokat adja vissza.
  Példa: SELECT nev, fizetes FROM dolgozo WHERE fizetes > 300000;
  Ez csak azokat a dolgozókat mutatja, akiknek a fizetése 300.000 felett van.

FROM tábla [[AS] alias] – Honnan jönnek az adatok?

• • FROM: Meghatározza, melyik táblából kérdezed le az adatokat.
  • [AS alias]: A táblának adhatunk rövidebb nevet a lekérdezéshez.
  • Példa: SELECT d.nev FROM dolgozo AS d;
    Itt a dolgozo tábla rövid neve d, így rövidebb lesz az utasítás.

[GROUP BY mezőlista] – Hogyan csoportosítasz?

• Az eredményeket megadott oszlopok szerint csoportosítja, pl. osztályok szerint.
  Példa: SELECT osztaly_id, AVG(fizetes) AS ÁtlagFizetés FROM dolgozo GROUP BY osztaly_id;
  Ez az osztályok átlagfizetését adja meg.
  

[HAVING feltétel] – Milyen feltételeket alkalmazol a csoportokra?

• A csoportosított eredmények szűrésére használod (a WHERE a rekordokra vonatkozik, a HAVING pedig a csoportokra).
  Példa: SELECT osztaly_id, AVG(fizetes) AS ÁtlagFizetés FROM dolgozo GROUP BY osztaly_id HAVING ÁtlagFizetés > 400000;
  Csak azok az osztályok jelennek meg, ahol az átlagfizetés meghaladja a 400.000-et.
  

[ORDER BY mezőlista] – Hogyan rendezed az eredményt?

• Rendezheted az adatokat egy vagy több oszlop szerint, növekvő (ASC) vagy csökkenő (DESC) sorrendben.
  Példa: SELECT nev, fizetes FROM dolgozo ORDER BY fizetes DESC;
  Ez a dolgozókat a fizetésük szerint csökkenő sorrendben rendezi.
  

 Összefoglalva:
SELECT DISTINCT nev, fizetes
FROM dolgozo
WHERE fizetes > 300000
GROUP BY osztaly_id
HAVING AVG(fizetes) > 400000
ORDER BY fizetes DESC;

Ez az utasítás megadja a dolgozók neveit és fizetéseit, szűri a 300.000 feletti fizetéseket, csoportosít osztály szerint, csak a 400.000 feletti átlaggal rendelkező osztályokat jeleníti meg, és csökkenő sorrendben rendezi a fizetéseket.