Sapientia -
This presentation is the property of its rightful owner.
Sponsored Links
1 / 33

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


  • 42 Views
  • Uploaded on
  • Presentation posted in: General

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. Sapientia - Erdélyi Magyar TudományEgyetem (EMTE ) Csíkszereda. Sorv áltozók

Download Presentation

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

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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.


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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;


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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>);


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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: =, <, <=, >, >=, <>.


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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);


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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)

FROMAlkalmazottak

GROUP BY RészlegID

HAVING MIN(Fizetés) >

(SELECT MIN(Fizetés)

FROMAlkalmazottak

WHERE RészlegID = 2);


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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.


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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%’)

);


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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);


Mysql alk rd sek a from ban

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.


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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);


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

  • 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);


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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)


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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));


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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)


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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;


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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>];


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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:


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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);


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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’;


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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);


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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;


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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;


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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’;


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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;


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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’;


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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);


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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;


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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;


Sapientia erd lyi magyar tudom nyegyetem emte cs kszereda

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

9Adjuk 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


  • Login