/* teljes táblázat lekérdezése */
SELECT * FROM alkalmazott;

/* projekció, csak a kiválasztott oszlopok */
SELECT oszt_azon, nev, fonok FROM alkalmazott;

/* kifejezések az oszlopokban */

/* éves fizetés, (szögletes zárójel a névbeli SPACE miatt kell!) */
SELECT nev, fizetes * 12 AS [Éves fizetés] FROM alkalmazott;

/* szöveges oszlopok összevonása, konstans mező a lekérdezésben */
SELECT alk_azon & '-' & nev AS Dolgozó, 'Osztály :' AS _, oszt_azon FROM alkalmazott;

/* NULL értékek a kifejezésben */
/* Hibás eredmény a NULL értékek miatt */
SELECT nev, fizetes * 12 + jutalom AS [Éves jövedelem] FROM alkalmazott;

/* NULL helyett 0-val számolunk */
SELECT nev, fizetes * 12 + IIf(jutalom, jutalom, 0) AS [Éves jövedelem]  FROM alkalmazott;

/* ugyanez Nz() függvénnyel */
SELECT nev, fizetes * 12 + Nz(jutalom) AS [Éves jövedelem]  FROM alkalmazott;


/* duplikált sorok elhagyása */
SELECT oszt_azon FROM alkalmazott;
SELECT DISTINCT oszt_azon FROM alkalmazott;
SELECT DISTINCT oszt_azon, beosztas FROM alkalmazott;
SELECT  oszt_azon, beosztas, Count(*) As Fő FROM alkalmazott GROUP BY oszt_azon, beosztas;

/* eredmény sorok rendezése */
SELECT nev, beosztas, fizetes * 12 As [Éves fizetés], oszt_azon FROM alkalmazott ORDER BY nev;

/* csökkenő sorrend */
SELECT nev, beosztas, belepes FROM alkalmazott ORDER BY belepes DESC;

/* több rendezési szempont */
SELECT oszt_azon, beosztas, nev FROM alkalmazott ORDER BY oszt_azon, fizetes DESC;

/* a rendezési szempont az oszlop sorszámával */
SELECT nev, fizetes FROM alkalmazott ORDER BY 2;

/* válogatás a sorok közül */
SELECT * FROM alkalmazott WHERE nev = 'SKÓT';
SELECT * FROM alkalmazott WHERE fizetes < 1200;
SELECT * FROM alkalmazott WHERE jutalom >= 500;
SELECT * FROM alkalmazott WHERE belepes >= #01/01/1982#;
SELECT * FROM alkalmazott WHERE fizetes BETWEEN 1000 AND 2000;
SELECT * FROM alkalmazott WHERE fonok IN (7902, 7566, 7788);
SELECT * FROM alkalmazott WHERE nev LIKE 'J*';
SELECT * FROM alkalmazott WHERE nev LIKE '?[I,O]*';
SELECT * FROM alkalmazott WHERE fonok IS NULL;
SELECT * FROM alkalmazott WHERE fonok IS NOT NULL;

/* összetett feltételek */
SELECT alk_azon, nev, beosztas, fizetes FROM alkalmazott
	WHERE fizetes BETWEEN 1000 AND 2000 AND beosztas = 2;

/* osztályok kódjai és nevei rendezve */
SELECT oszt_azon, nev FROM osztaly ORDER BY oszt_azon;

/* különböző beosztások */
SELECT DISTINCT beosztas FROM alkalmazott;

/* 10-es és a 20-as osztályon dolgozók név szerint rendezve */
SELECT * FROM alkalmazott
	WHERE oszt_azon IN (10,20)
	ORDER BY nev;

/* 20-as osztályon dolgozó előadók neve és beosztása */
SELECT nev, beosztas FROM alkalmazott WHERE oszt_azon = 20;

/* "ES"-t vagy "IR"-t tartalmazó nevek */
SELECT nev FROM alkalmazott
	WHERE nev LIKE '*ES*' OR nev LIKE '*IR*';

/*----------- 1. alkalommal eddig elég lesz eljutni -------------------*/


/* név, beosztás, fizetés azokra, akiknek van főnökük */
SELECT nev, beosztas, fizetes FROM alkalmazott
	WHERE fonok IS NOT NULL;

/* az 1981-ben belépett alkalmazottak */
SELECT nev, oszt_azon, belepes FROM alkalmazott
	WHERE belepes LIKE '1981*';

SELECT nev, oszt_azon, belepes FROM alkalmazott
	WHERE Year(belepes) = 1981;

/* összevont oszlopok */
SELECT nev & ' ' & beosztas & '. kódú beosztásban dolgozik '& belepes & ' óta'
	AS "ki, mit csinál, mióta" FROM alkalmazott;

/* -------------------------- karakteres függvények----------------------------- */
/* nagybetűssé alakítás */
SELECT nev, beosztas FROM alkalmazott
	WHERE nev LIKE UCase('m*');

/* kisbetűssé alakitás */
SELECT LCase(nev) FROM alkalmazott;

/* sztring pozíciója az oszlopban */
SELECT nev, InStr(nev, 'A') AS [A pozíció] FROM alkalmazott;

/* sztring hossza */
SELECT nev, Len(nev) AS Névhossz FROM alkalmazott;

/* numerikus függvények */
/* napi fizetés kerekítése a SZÁMLÁZÁS osztályon */
/1. verzió - hibás - más SQL rendszerben viszont működik! */
SELECT fizetes/30, Round(fizetes/30), Round(fizetes/30,2), Round(fizetes/30,-1) FROM alkalmazott
	WHERE oszt_azon = 10;
/* 2. verzió */
SELECT fizetes/30, Round(fizetes/30), Round(fizetes/30,2), Round(fizetes/300)*10 FROM alkalmazott
	WHERE oszt_azon = 10;

/* fizetés 15%-al növelt értéke */
SELECT nev, Round(fizetes*1.15) AS [15%-al növelt fizetés] FROM alkalmazott;

/* dátum függvények */
/* aktuális dátum lekérdezése */
SELECT Now(), Date();

/* aritmetikai műveletek dátumokkal */
SELECT belepes, belepes+21, Now()-belepes, Date()-belepes FROM alkalmazott
	WHERE oszt_azon = 10;

/* 300 hónapnál nem régebben belépettek */
SELECT * FROM alkalmazott WHERE DateAdd('m', 300, belepes) > Now();
/* 9000 napnál nem régebben belépettek */
SELECT * FROM alkalmazott WHERE DateAdd('d', 9000, belepes) > Now();
/* 25 évnél nem régebben belépettek */
SELECT * FROM alkalmazott WHERE DateAdd('yyyy', 25, belepes) > Now();

/* konverziós függvények */
/* szám sztringgé alakitása */
SELECT nev, beosztas, Format(fizetes, '$0.00') FROM alkalmazott
	WHERE beosztas=12;

/* dátum sztringgé alakítása */
SELECT Format(Now(), 'yyyy mm dd');
    
/* idő és dátum */
SELECT Format(Now(), 'yyyy.mm.dd HH:MM:SS');

/* mához 2 évre milyen nap lesz */
SELECT Format(DateAdd('m',24,Now()), 'dddd') ;

/* sztring dátummá alakítása */
SELECT Format(#20/11/1996#, 'YYYY.MM.DD');

/* csoport - aggregáló függvények */
/* teljes táblára, a jutalom átlaga 4 elemből jön, a NULL elemek kimaradtak */
SELECT Min(fizetes), Max(fizetes), Sum(fizetes), Avg(fizetes), Avg(jutalom), Count(fizetes)
	FROM alkalmazott;

/* Nz() függvénnyel a NULL helyett 0-val számolunk */
SELECT Min(fizetes), Max(fizetes), Sum(fizetes), Avg(fizetes), Avg(Nz(jutalom)), Count(fizetes)
	FROM alkalmazott;

/* beosztásonkénti csoportokra */
SELECT beosztas, Avg(fizetes) AS Átlag
FROM alkalmazott
GROUP BY beosztas;

/* osztályonkénti csoportra */
SELECT oszt_azon, Min(fizetes), Max(fizetes), Sum(fizetes), Avg(fizetes), Count(fizetes)
	FROM alkalmazott
	GROUP BY oszt_azon;

/* válogatás a csoportok közül */
SELECT oszt_azon, Avg(fizetes) FROM alkalmazott
	GROUP BY oszt_azon HAVING Count(*) > 3;

/* válogatás a sorok és csoportok közül is. 
A nem ELŐADÓ-k közül osztályonként azon osztályok átlagfizetése, ahol minimális fizetés > 1000-nél */
SELECT oszt_azon, Avg(fizetes) FROM alkalmazott
	WHERE NOT (beosztas = 2)
	GROUP BY oszt_azon HAVING Min(fizetes) > 1000;

/* beosztásonként a legkisebb és legnagyobb fizetés */
SELECT beosztas, Min(fizetes) AS MIN, Max(fizetes) AS MAX FROM alkalmazott
	GROUP BY beosztas;

/* managerek - 12-es beosztáskódúak - száma */
SELECT Count(*) AS Managerek FROM alkalmazott WHERE beosztas = 12;

/* ------------------------- lekérdezés több táblából ---------------------- */
/* egyén összekapcsolás */
SELECT alkalmazott.nev, beosztas, osztaly.nev FROM alkalmazott, osztaly
	WHERE alkalmazott.oszt_azon = osztaly.oszt_azon;

/* Descartes szorzat, összekapcsolás minden kombinációban */
SELECT osztaly.nev, alkalmazott.nev FROM osztaly, alkalmazott;

/* nem egyén összekapcsolás, táblák alias nevének használatával */
SELECT A.nev, A.fizetes, F.f_oszt FROM alkalmazott A, fiz_oszt F
	WHERE A.fizetes BETWEEN f.mIN AND f.max;

/* Budapesten dolgozók névsora */
SELECT A.nev FROM alkalmazott A, osztaly O
	WHERE A.oszt_azon = O.oszt_azon AND O.varos = 'BUDAPEST';

/* összekapcsolás */
SELECT O.oszt_azon, O.nev, A.nev FROM alkalmazott A, osztaly O
	WHERE A.oszt_azon  = O.oszt_azon;

/* ugyanez lekérdezés-tervezővel, amikor a kapcsolatot grafikusan összehoztuk */
SELECT O.oszt_azon, O.nev, A.nev
FROM osztaly AS O INNER JOIN alkalmazott AS A ON O.oszt_azon= A.oszt_azon;

/* most azokat osztályokat is kérjük, ahol nincs alkalmazott */
SELECT O.oszt_azon, O.nev, A.nev
FROM osztaly AS o LEFT JOIN alkalmazott AS a ON O.oszt_azon= A.oszt_azon;

/* ha lenne olyan alkalmazott, aki nem létező osztályba van sorolva, akkor azt is látnánk */
SELECT O.oszt_azon, O.nev, A.nev
FROM osztaly AS O RIGHT JOIN alkalmazott AS A ON O.oszt_azon= A.oszt_azon;

/* tábla összekapcsolása önmagával */
/* főnök - beosztott párok */
SELECT A.nev AS főnök, B.nev AS beosztott FROM alkalmazott A, alkalmazott B
	WHERE B.fonok = A.alk_azon;

/* ugyanez INNER JOIN-nal */
SELECT A.nev AS főnök, B.nev AS beosztott
FROM alkalmazott AS A INNER JOIN alkalmazott AS B ON A.alk_azon = B.fonok;

/* most még az is megjelenik a párok mellett, akinek nincs beosztottja:
   baloldali A tábla minden rekordja megjelenik még az is,
   melynek nincs párja a jobboldali B táblában */
SELECT A.nev AS főnök, B.nev AS beosztott
	FROM alkalmazott AS A LEFT JOIN  alkalmazott AS B
		ON B.fonok = A.alk_azon;

/* most még az is megjelenik a párok mellett, akinek nincs főnöke: 
   jobboldali B tábla minden rekordja megjelenik még az is,
   melynek nincs párja a baloldali A táblában */
SELECT A.nev AS főnök, B.nev AS beosztott
	FROM alkalmazott AS A RIGHT JOIN  alkalmazott AS B
		ON B.fonok = A.alk_azon;

/* ezt a hierarchiát fokozzuk 4 szintig */
SELECT A.nev AS Dolgozó, B.nev AS Főnöke, C.nev AS NagyobbFőnöke, D.nev AS MégNagyobbFőnöke
FROM alkalmazott AS D RIGHT JOIN 
          (alkalmazott AS C RIGHT JOIN 
          (alkalmazott AS B RIGHT JOIN alkalmazott AS A  ON 
                   B.alk_azon = A.fonok) ON 
                   C.alk_azon = B.fonok) ON 
                   D.alk_azon = C.fonok;

/* Azt is ellenőrizzük, hogy a főnök besztáskódja a dolgozó kódjának többszöröse-e */
SELECT A.nev AS Dolgozó, B.nev AS Főnöke, 
              IIf(Int(B.beosztas/A.beosztas)*A.beosztas = B.beosztas, 'Ok.', 'Not Ok.') AS OK
FROM alkalmazott AS B INNER JOIN alkalmazott AS A ON B.alk_azon = A.fonok;


/* alkalmazottak, akik a főnökük előtt léptek be */
SELECT A.nev AS BEOSZTOTT, A.belepes AS Kezdés, B.nev AS FŐNÖK, B.belepes AS Belépés
	FROM alkalmazott A, alkalmazott B
	WHERE A.fonok = B.alk_azon AND A.belepes < B.belepes;

/* osztályonkénti átlagfizetés */
SELECT osztaly.nev, Avg(fizetes) AS Átlag
FROM osztaly, alkalmazott
WHERE osztaly.oszt_azon=alkalmazott.oszt_azon
GROUP BY osztaly.nev;


/* halmaz műveletek */
/* egyesítő lekérdezés: azon osztályok, ahol manager vagy ügynök dolgozik */
SELECT DISTINCT oszt_azon FROM alkalmazott
	WHERE beosztas = 12
UNION
SELECT DISTINCT oszt_azon FROM alkalmazott
	WHERE beosztas = 3;

/* egymásba ágyazott lekérdezések */
/* legkisebb fizetesű dolgozó */
SELECT nev, fizetes FROM alkalmazott
	WHERE fizetes = (SELECT Min(fizetes) FROM alkalmazott);

/* SZABÓ-val azonos munkakörben dolgozók */
SELECT nev, beosztas FROM alkalmazott
	WHERE beosztas = (SELECT beosztas FROM alkalmazott
		WHERE nev = 'SZABÓ');

/* legkisebb fizetésű dolgozó osztályonként */
SELECT nev, fizetes, oszt_azon FROM alkalmazott
	WHERE fizetes & oszt_azon IN
	(SELECT Min(fizetes) & oszt_azon FROM alkalmazott
	GROUP BY oszt_azon);


/* 30-as osztály minimum fizetésénél többet keresők */
SELECT nev, beosztas, fizetes FROM alkalmazott
	WHERE fizetes > ANY
	(SELECT fizetes FROM alkalmazott
	WHERE oszt_azon = 30);

/* a 30-as osztályon legtöbbet keresőnél magasabb fizetésűek */
SELECT fizetes, beosztas, nev FROM alkalmazott
	WHERE fizetes > ALL
	(SELECT fizetes FROM alkalmazott WHERE oszt_azon = 30);

/* legmagasabb átlagfizetésű beosztás - EZ NEM MŰKÖDIK az ACCESS-nél!!
Az ACCESS nem engedi meg az aggregáló függvények egymásba ágyazását */
SELECT beosztas, Avg(fizetes) FROM alkalmazott
	GROUP BY beosztas
	HAVING Avg(fizetes) = 
	(SELECT Max(Avg(fizetes)) FROM alkalmazott
	GROUP BY beosztas);

/* így viszont MŰKÖDIK */
SELECT Max(xx & ' átlagfizetés a ' & yy & ' beosztáskódnál') AS [Max átlagfizetés]
  FROM (SELECT Avg(fizetes) AS xx, beosztas AS yy  
              FROM alkalmazott GROUP BY beosztas);

/* korreláció */
/* akiknek nagyobb a fizetesük, mint az osztály átlag */
SELECT oszt_azon, nev, fizetes FROM alkalmazott A
	WHERE fizetes > (SELECT Avg(fizetes) FROM alkalmazott
		WHERE oszt_azon = A.oszt_azon)
	ORDER BY oszt_azon;

/* akiknek legalább egy beosztottja van */
SELECT nev, beosztas, oszt_azon FROM alkalmazott a
	WHERE EXISTS (SELECT * FROM alkalmazott
		WHERE fonok = A.alk_azon);

/* osztályonként az utolsónak belépett dolgozó */
SELECT oszt_azon, nev, belepes FROM alkalmazott
	WHERE (oszt_azon & belepes) IN (SELECT oszt_azon & Max(belepes)
		FROM alkalmazott GROUP BY oszt_azon)
	ORDER BY belepes DESC;

/* osztály amelyiknek nincs dolgozója - özvegy rekordok */
SELECT nev FROM osztaly O
	WHERE NOT EXISTS (SELECT * FROM alkalmazott
		WHERE oszt_azon = O.oszt_azon);

/* kereszttáblás (pivot táblás) lekérdezés:
  Osztályonként és beosztásonkénti csoportosításban megjelenítjük a dolgozók számát és átlagfizetésüket,
  majd belépési évek szerinti alcsoportokban is megjelenítjük az átlagfizetést */

TRANSFORM Avg(A.FIZETES) AS AvgOfFIZETES
SELECT O.nev AS Osztály, A.beosztas, Avg(A.fizetes) AS Átlagfizetés, Count(A.alk_azon) AS Fő
FROM osztaly AS O INNER JOIN alkalmazott AS A ON O.oszt_azon = A.oszt_azon
GROUP BY O.nev, A.beosztas
PIVOT Str(Year([belepes]))+' évi átlag';