-- Emelt szintű SQL gyakorló megoldások - SmartHome

-- 1. feladat: Eszközök listázása (Xiaomi VAGY zár)
SELECT nev, gyarto 
FROM eszkozok 
WHERE gyarto = 'Xiaomi' OR tipus = 'zár';

-- 2. feladat: Emeleti helyiségek rendezve
SELECT nev 
FROM helyisegek 
WHERE emelet > 0 
ORDER BY alapterulet DESC;

-- 3. feladat: Hány különböző eszköztípus van?
SELECT COUNT(DISTINCT tipus) 
FROM eszkozok;

-- 4. feladat: Napló szűrés (érték 20-25 között, 2024-ben)
SELECT * FROM naplo 
WHERE (ertek BETWEEN 20 AND 25) 
  AND YEAR(rogzites_ideje) = 2024;

-- 5. feladat: 'Kovács' nevű felhasználók
SELECT teljes_nev, szuletesi_ev 
FROM felhasznalok 
WHERE teljes_nev LIKE '%Kovács%';

-- 6. feladat: Automata bejegyzések (felhasználó nélküli)
SELECT id, rogzites_ideje 
FROM naplo 
WHERE felhasznalo_id IS NULL;

-- 7. feladat: 'Nappali' eszközei (Tábla összekapcsolás)
SELECT eszkozok.nev, eszkozok.tipus
FROM eszkozok, helyisegek
WHERE eszkozok.helyiseg_id = helyisegek.id
  AND helyisegek.nev = 'Nappali';

-- 8. feladat: Átlagérték típusonként
SELECT tipus, AVG(ertek) AS atlag_ertek
FROM eszkozok, naplo
WHERE eszkozok.id = naplo.eszkoz_id
GROUP BY tipus;

-- 9. feladat: Legutolsó bejegyzés ideje
SELECT MAX(rogzites_ideje) 
FROM naplo;

-- 10. feladat: Mely napokon volt aktivitás januárban?
SELECT DISTINCT DAY(rogzites_ideje)
FROM naplo
WHERE MONTH(rogzites_ideje) = 1;

-- --- INNENTŐL NEHEZEBB ---

-- 11. feladat: Eszközök, ahol a mért maximum érték > 30 (HAVING)
SELECT eszkozok.nev
FROM eszkozok, naplo
WHERE eszkozok.id = naplo.eszkoz_id
GROUP BY eszkozok.id, eszkozok.nev
HAVING MAX(naplo.ertek) > 30;

-- 12. feladat: Inaktív felhasználók (több mint 30 napja nem volt aktivitás)
-- A feladat kedvéért a referencia dátum: '2025-02-01'
SELECT f.teljes_nev
FROM felhasznalok f, naplo n
WHERE f.id = n.felhasznalo_id
GROUP BY f.id, f.teljes_nev
HAVING DATEDIFF('2025-02-01', MAX(n.rogzites_ideje)) > 30;

-- 13. feladat: Üres helyiségek (ahol nincs eszköz)
-- Megoldás allekérdezés NOT IN-nel
SELECT nev 
FROM helyisegek
WHERE id NOT IN (
    SELECT helyiseg_id FROM eszkozok
);

-- 14. feladat: Helyiségek, ahol legalább két KÜLÖNBÖZŐ típusú eszköz van
-- "Self-Join" logika a WHERE-ben
SELECT DISTINCT h.nev, e1.tipus, e2.tipus
FROM helyisegek h, eszkozok e1, eszkozok e2
WHERE h.id = e1.helyiseg_id 
  AND h.id = e2.helyiseg_id
  AND e1.id < e2.id
  AND e1.tipus <> e2.tipus;

-- 15. feladat: Helyiségek, melyek alapterülete nagyobb az átlagnál
-- Megoldás skalár allekérdezéssel a WHERE-ben
SELECT nev
FROM helyisegek
WHERE alapterulet > (
    SELECT AVG(alapterulet) FROM helyisegek
);

-- 16. feladat: Felhasználók minősítése (Aktív/Átlagos) IF függvénnyel
SELECT f.teljes_nev, 
       IF(COUNT(n.id) > 10, 'Aktív', 'Átlagos') AS aktivitas
FROM felhasznalok f, naplo n
WHERE f.id = n.felhasznalo_id
GROUP BY f.id, f.teljes_nev;

-- 17. feladat: 'Xiaomi' eszközöket használó felhasználók
-- Megoldás allekérdezéssel (IN operátor)
SELECT teljes_nev
FROM felhasznalok
WHERE id IN (
    SELECT felhasznalo_id 
    FROM naplo, eszkozok 
    WHERE naplo.eszkoz_id = eszkozok.id 
      AND eszkozok.gyarto = 'Xiaomi'
);

-- 18. feladat: Eszközök, amelyek átlaga nagyobb a globális átlagnál
-- Megoldás: HAVING-ben lévő skalár allekérdezés
SELECT e.nev
FROM eszkozok e, naplo n
WHERE e.id = n.eszkoz_id
GROUP BY e.id, e.nev
HAVING AVG(n.ertek) > (
    SELECT AVG(ertek) FROM naplo
);

-- 19. feladat: Helyiségek, ahol legalább 3 'Xiaomi' eszköz van
SELECT h.nev
FROM helyisegek h, eszkozok e
WHERE h.id = e.helyiseg_id
  AND e.gyarto = 'Xiaomi'
GROUP BY h.id, h.nev
HAVING COUNT(e.id) >= 3;

-- 20. feladat: Átlagos bejegyzésszám eszközönként
-- Megoldás Derived Table (Allekérdezés a FROM után) segítségével
SELECT AVG(db)
FROM (
    SELECT COUNT(id) AS db
    FROM naplo
    GROUP BY eszkoz_id
) AS stat;