Sapientia - Erdélyi Magyar TudományEgyetem (EMTE ) Csíkszereda

1. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda • 4. Előadás • Az SQL adatbázisnyelv • Sorváltozók • Alkérdések • Ismétlődő sorok • Összesítések

2. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Sorváltozók Olyan lekérdezéseknél, amelyik UGYANAZON reláció két vagy több sorát kombináljuk össze. A FROM záradékban R minden előfordulásához hozzárendelünk egy másodnevet. Ezt nevezzük sorváltozónak. A FROM záradékban R minden előfordulása után következhet az AS szócska és a sorváltozó neve. R attribútumait megkülönböztetjük a SELECT és a WHERE záradékokban egy előtag segítségével, amelyik a megfelelő sorváltozóból és egy pontból áll.

3. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Sorváltozók Keressük azon színész párokat, akik egy címen laknak. (házastársak pl.) SELECT Szinesz1.nev, Szinesz2.nev FROM Filmszinesz AS Szinesz1, Filmszinesz AS Szinesz2 WHERE Szinesz1.cim=Szinesz2.cim AND Szinesz1.nev<Szinesz2.nev;

4. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Alkérdések Az alkérdések segítségével sorokat vagy relációkat tudunk összehasonlítani. Egy alkérdés egy olyan kifejezés, mely egy relációt eredményez, például egy select-from-where kifejezés. SELECT SQL parancs általános formája, mely tartalmaz alkérdést a következő: SELECT <attribútum_lista> FROM <tábla> WHERE <kifejezés> <operátor> (SELECT <attribútum_lista> FROM <tábla>);

5. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda • Alkérdéseket csoportosíthatjuk annak megfelelően, hogy az eredménye hány sort és hány oszlopot tartalmaz: • egy oszlopot, egy sort, vagyis egy skalár értéket ad vissza (single-row); • egy oszlopot, több sort, u. n. több soros alkérdés (multiple-row subquery); • több oszlopot, több sort, u.n. több oszlopos alkérdés (multiple-column); • Használt operátorok: • IN (NOT IN)-benne van, tartalmazza • ALL-mind • ANY-valamelyik

6. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda • Példa: “Keressük a ‘Tervezés’ nevű részleg menedzserének a nevét”. A megoldás alkérdés segítségével: • 1) SELECT Név • 2) FROM Alkalmazottak • 3) WHERE SzemSzám = • 4) (SELECT MenSzemSzám • 5) FROM Részlegek • WHERE Név = ‘Tervezés’); • A skalár értéket adó alkérdéssel használható operátorok az: =, <, <=, >, >=, <>.

7. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Példa: “Keressük azon alkalmazottakat, kiknek fizetése nagyobb, mint annak az alkalmazottnak, kinek a személyi száma 333333.” SELECT Név FROM Alkalmazottak WHERE Fizetés > (SELECT Fizetés FROM Alkalmazottak WHERE SzemSzám = 333333);

8. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Példa: “Keressük azon részlegeket és az alkalmazottak minimális fizetését a részlegből, ahol a minimális fizetés nagyobb, mint a minimális fizetés a 2-es ID-jű részlegből”. SELECT RészlegID, MIN(Fizetés) FROM Alkalmazottak GROUP BY RészlegID HAVING MIN(Fizetés) > (SELECT MIN(Fizetés) FROM Alkalmazottak WHERE RészlegID = 2);

9. A több-soros alkérdések esetén a WHERE záradék feltétele tartalmazhat olyan operátorokat, amelyeket egy R relációra alkalmazhatunk és az eredmény logikai érték lesz. Bizonyos operátoroknak egy skaláris s értékre is szükségük van. Ilyen operátorok: EXISTS R – feltétel, mely akkor és csak akkor igaz, ha R nem üres. Példa: SELECT Név FROM Alkalmazottak, Managerek WHERE Alkalmazottak. SzemSzám = Managerek.SzemSzám AND EXISTS (SELECT * FROM Alkalmazottak WHERE Fizetés > 500); A fenti példa csak abban az esetben adja meg a managerek nevét, ha van legalább egy alkalmazott, kinek a fizetése nagyobb, mint 500 Euro.

10. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda s IN R, mely akkor igaz, ha s egyenlő valamelyik R-beli értékkel. Az s NOT IN R akkor igaz, ha s egyetlen R-beli értékkel sem egyenlő. Példa: “Adjuk meg azon szállítók nevét és címét, kik szállítanak valamilyen csokit” (Áruk.Név LIKE ‘*csoki*’) 1) SELECT Név, Helység, UtcaSzám 2) FROM Szállítók 3) WHERE SzállID IN 4) (SELECT SzállID 5) FROM Szállít 6) WHERE ÁruID IN 7) (SELECT ÁruID 8) FROM Áruk 9) WHERE Név LIKE ‘%csoki%’) );

11. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda s > ALL R, mely akkor igaz, ha s nagyobb, mint az R reláció minden értéke, ahol az R relációnak csak egy oszlopa van. A > operátor helyett használhatjuk bármelyik összehasonlítási operátort. Az s <> ALL R eredménye ugyanaz, mint az s NOT IN R feltételnek. Példa: Legyen a következő lekérdezés: SELECT SzemSzám, Név FROM Alkalmazottak WHERE Fizetés > ALL (SELECT MIN(Fizetés) FROM Alkalmazottak GROUP BY RészlegID);

12. MySQL alkérdések a FROM-ban • Alkérdések legálisak a SELECT utasítás FROM direktívájában. Az aktuális szintaxis: • SELECT ... FROM (alkérdés) [AS] név ... • Sok esetben az alkérdésekkel megoldunk bizonyos alproblémákat. • Hogy ne bonyolítsuk a komplexitását a lekérdezésnek, ezen alkérdést ugyanúgy használhatjuk, mint a relációkat.

13. A 7-ik előadásban szereplő lekérdezés megvalósítása e) Melyek azok a gyártók, akik laptopot árulnak, PC-t viszont nem gyártó(Laptop Termék) gyártó(PCTermék) SELECT DISTINCT Gyarto FROM Termek INNER JOIN Laptop ON Termek.modell=Laptop.modell WHERE Gyarto NOT IN (SELECT DISTINCT Gyarto FROM Termek INNER JOIN PC ON Termek.modell=PC.modell);

14. Adott az adatbázis sémája, amelyik négy relációból áll: • Termék(gyártó, modell, típus) • PC(modell, sebesség, memória, merevlemez, cd, ár) • Laptop(modell, sebesség, memória, merevlemez, képernyő, ár) • Nyomtató(modell, színes, típus, ár) • Keressük meg a legalább 160-as sebességű PC-k gyártóit? SELECT gyarto FROM Termek INNER JOIN PC ON Termek.modell=PC.modell WHERE PC.sebesseg>=160; SELECT gyarto FROM Termek WHERE modell IN (SELECT modell FROM PC WHERE sebesseg>=160);

15. b) Keressük meg a legdrágább nyomtatókat? c) Keressük meg azokat a laptopokat, amelyek minden PC-nél lassúbbak? d) Keressük meg a modellszámát a legdrágább terméknek (PC, laptop vagy nyomtató) SELECT modell FROM Nyomtato WHERE ar=(SELECT MAX(ar) FROM Nyomtato); SELECT modell FROM Laptop WHERE sebesseg< ALL (SELECT sebesseg FROM PC); Q1=(SELECT modell,ar FROM PC) UNION (SELECT modell,ar FROM Laptop) UNION (SELECT modell,ar FROM Nyomtato); SELECT modell FROM Q1 WHERE ar=(SELECT MAX(ar) FROM Q1)

16. e) Keressük meg a legolcsóbb színes nyomtató gyártóját? Melyik a jó válasz, és miért? SELECT gyarto FROM Termek WHERE modell IN (SELECT modell FROM Nyomtato WHERE szines AND ar=(SELECT MIN(ar) FROM Nyomtato)); SELECT gyarto FROM Termek WHERE modell IN (SELECT modell FROM Nyomtato WHERE ar=(SELECT MIN(ar) FROM Nyomtato WHERE szines)); SELECT gyarto FROM Termek WHERE modell IN (SELECT modell FROM Nyomtato WHERE szines AND ar=(SELECT MIN(ar) FROM Nyomtato WHERE szines));

17. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda f) Keressük meg annak a nyomtatónak a gyártóját, amely a leggyorsabb processzorú PC-t gyártja a legkisebb memóriájú PC-k között. Q2=SELECT gyarto, sebessegFROM Termek INNER JOIN PC ON Termek.modell=PC.modellWHERE sebesseg IN (SELECT sebesseg FROM PC WHERE memoria=(SELECT MIN(memoria) FROM PC)); SELECT gyarto FROM Q2 WHERE sebesseg IN (SELECT MAX(sebesseg) FROM Q2)

18. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Összesítések: Az összesítés művelete egy oszlop értékeiből egy új értéket hoz létre Összesítő függvények a következők: SUM, megadja az oszlop értékeinek az összegét; AVG, megadja az oszlop értékeinek a átlag értékét; MIN, megadja az oszlop értékeinek a minimumát; MAX, megadja az oszlop értékeinek a maximumát; COUNT, megadja az oszlopban szereplő értékek számát, beleértve az ismétlődéseket is, ha azok nincsenek megszüntetve a DISTINCT kulcsszóval;

19. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Példa: A következő lekérdezés segítségével megkapjuk az alkalmazottak átlagos fizetését: SELECT AVG(Fizetés) FROM Alkalmazottak; Példa: Az Egyetem adatbázis esetén keressük azon csoportoknak a számát, amelyekben vannak diákok, akik átlaga kisebb, mint 7: SELECT COUNT(DISTINCT CsopKod) FROM Diákok WHERE Átlag < 7

20. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda Csoportosítások: A reláció sorait csoportosítani szeretnénk egy vagy több oszlop értékei szerint. A parancs általános formája: SELECT < csoportosító oszlopok listája >, <összesítőfüggvény>(<oszlop>) FROM <reláció> [WHERE <feltétel>] [GROUP BY <csoportosító oszlopok listája>] [HAVING <csoportosítási-feltétel>] [ORDER BY <oszlop>];

21. Példa: A részlegeken belüli átlag fizetést a következő parancs segítségével kapjuk meg: SELECT RészlegID, AVG(Fizetés), MIN(Fizetés), MAX(Fizetés), SUM(Fizetés) FROM Alkalmazottak GROUP BY RészlegID; Eredmény:

22. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda A csoportosítás után kapott eredmény reláció soraira alkalmazhatunk egy feltételt a HAVING kulcsszót használva. Példa: Keressük azon részlegeket, ahol az alkalmazottak átlag fizetése nagyobb, mint 500 Euro, átlag fizetés szerint növekvő sorrendben. SELECT RészlegID, AVG(Fizetés) FROM Alkalmazottak GROUP BY RészlegID HAVING AVG(Fizetés) > 500 ORDER BY AVG(Fizetés);

23. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda • Keressük meg a PC-k átlagos sebességét? • Keressük meg a 2500$-nál drágább laptopok átlagos sebességét? • Keressük meg az “A” gyártó által gyártott PC-k átlagos árát? a) SELECT AVG(sebesseg) AS atlagsebesseg FROM PC; b) SELECT AVG(sebesseg) FROM Laptop WHERE ar>2500; c) SELECT AVG(ar) FROM Termek INNER JOIN PC ON Termek.modell=PC.modell WHERE Gyarto=‘A’;

24. d) Keressük meg a “D” gyártó által gyártott PC-k és laptopok átlagos árát? e) Keressük meg minden egyes PC-sebességéhez az ilyen sebességű PC-k átlagos árát? f) Keressük meg minden gyártó esetén a laptopok átlagos képernyőméretét? d)Q1=(SELECT ar FROM Termek INNER JOIN PC ON Termek.modell=PC.modell WHERE Termek.gyarto=‘D’) UNION ALL(SELECT ar FROM Termek INNER JOIN Laptop ON Termek.modell=Laptop.modell WHERE Termek.gyarto=‘D’); SELECT AVG(ar) FROM Q1; e) SELECT sebesseg, AVG(ar) FROM PC GROUP BY (sebesseg); f) SELECT gyarto, AVG(kepernyo) FROM Termek INNER JOIN laptop ON Termek.modell=laptop.modell GROUP BY (gyarto);

25. g) Keressük meg azokat a gyártókat, akik legalább háromfajta PC-t gyártanak? h) Keressük meg minden gyártó esetén a maximális PC árat? i) Keressük meg minden 150-nél nagyobb sebességű PC átlagos árát? j) Keressük meg minden olyan gyártóhoz, akik nyomtatót gyártanak, a PC-k átlagos merevlemezméretét g) SELECT gyarto FROM Termek INNER JOIN PC ON Termek.modell=PC.modell GROUP BY gyarto HAVING Count(*)>2; h) SELECT gyarto, MAX(ar) FROM Termek INNER JOIN PC ON Termek.modell=pc.modell GROUP BY (gyarto); i) SELECT AVG(ar) FROM PC WHERE sebesseg>150GROUP BY sebesseg; i) SELECT gyarto, AVG(merevlemez) FROM Termek INNER JOIN PC ON Termek.modell=PC.modell WHERE gyarto IN (SELECT DISTINCT gyarto FROM Termek WHERE tipus=‘nyomtato’) GROUP BY gyarto;

26. Hajóosztályok(osztály, típus, ország, ágyúkSzáma, kaliber, vízkiszorítás) Hajók(név, osztály, felavatva) Csaták(név, dátum) Kimenetek(hajó, csata, eredmény) 1. Adjuk meg a hajóosztályokat a gyártó országok nevével együtt, amelyeknek az ágyúi legalább 16-os kaliberűek. SELECT osztaly,orszag FROM Hajoosztalyok WHERE kaliber>=16;

27. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda 2. Melyek azok a hajók, amelyeket 1921-ben avattak fel? 3. Adjuk meg a North Atlantic csatában elsüllyedt hajók nevét. SELECT nev FROM Hajok WHERE felavatva=1921; SELECT hajo FROM Kimenetelek WHERE csata=‘North Atlantic’ AND Allapot=‘elsullyedt’;

28. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda 4. Az 1921-es washingtoni egyezmény betiltotta a 35.000 tonnánál súlyosabb hajókat. Adjuk meg azokat a hajókat, amelyek megszegték az egyezményt. SELECT hajo FROM Hajoosztalyok INNER JOIN Hajok ON Hajoosztaly.osztaly=Hajok.osztaly WHERE vizkiszoritas>35000 AND Felavatva>1921;

29. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda 5. Adjuk meg a Guadalcanal csatában részt vett hajók nevét, vízkiszorítását és ágyúinak a számát. SELECT Hajo.nev, hajoosztaly.vizkiszoritas, hajoosztaly.agyukszama FROM Hajoosztaly, Kimenetelek, Hajok WHERE Hajoosztaly.osztaly=Hajo.osztaly AND Hajo.nev=Kimenetelek.hajo AND Csata=‘Guadalcanal’;

30. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda 6. Adjuk meg az adabázisban szereplő összes hadihajó nevét (ne feledjük, hogy a Hajók relációban nem szerepel az összes hajó!) (SELECT hajo FROM Kimenetelek) UNION (SELECT nev FROM Hajok);

31. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda 7. Adjuk meg azokat az osztályokat, amelyekbe csak egyetlenegy hajó tartozik SELECT hajoosztalyok.osztaly FROM hajoosztalyok, hajok WHERE hajoosztalyok.osztaly=hajok.osztaly GROUP BY hajoosztalyok.osztaly HAVING COUNT(*)=1;

32. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda 8. Melyek azok az országok, amelyeknek csatahajóik és cirkálóhajóik is voltak? SELECT Hajoosztaly.orszag FROM Hajoosztaly, Hajoosztaj AS HO2 WHERE Hajoosztaly.orszag=HO2.orszag AND Hajoosztaly.tipus<>HO2.tipus;

33. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE)Csíkszereda 9 Adjuk meg azokat a hajókat, amelyek újjáéledtek, azaz egyszer már megsérültek egy csatában, de egy későbbi csatában újra harcoltak. Q=SELECT hajo, csata, allapot, datum FROM Csatak INNER JOIN Kimenetelek ON Csatak.nev=Kimenetelek.csata WHERE ALLAPOT<>’elsullyedt’; SELECT Q.hajo FROM Q, Q AS Q2 WHERE Q.hajo=Q2.hajo AND Q.allapot=‘serult’ AND Q.datum<Q2.datum