slide1
Download
Skip this Video
Download Presentation
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE ) Csíkszereda

Loading in 2 Seconds...

play fullscreen
1 / 33

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


  • 62 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Sapientia - Erdélyi Magyar TudományEgyetem (EMTE ) Csíkszereda' - pancho


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
slide1

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
slide2

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.

slide3

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;

slide4

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

slide5

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
slide6

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

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

slide8

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

slide9

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.

slide10

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

);

slide11

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.
slide13

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

slide14

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

slide15

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)

slide16

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

slide17

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)

slide18

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;

slide19

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

slide20

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

slide21

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:

slide22

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

slide23

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

slide24

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

slide25

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;

slide26

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;

slide27

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

slide28

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;

slide29

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

slide30

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

slide31

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;

slide32

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;

slide33

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

ad