Lekérdezés nem szigorú illesztéssel

Bognár Veronika tanárjelölt zárótanítása

Óra típusa: Új ismeret feldolgozó
Tanítási cél: MSQuery és az SQL nyelv további lehetőségeinek megismertetése

 

Óra logikai menete

Módszertani megjegyzések
A két használt adattábla:

tanulo(nev, osztaly, kod, ferfi, szul_ido, testver)
hianyzas(kod, oraszam, igazolt, datum)		  
1. Sorold fel, mely osztályokba járnak olyan tanulók, akiknek 4-nél több testvére van.

SELECT DISTINCT Osztaly
FROM tanulo
WHERE Testver > 4

Többen nem vették figyelembe, hogy lehet olyan osztály, amelyben több diáknak is a testvérszáma nagyobb, mint 4.

 Felhívom a figyelmet a DISTINCT fontosságára
Egy tanulót kihívok a táblához.
2.Add meg, hogy hány tanulónak van 1, 2, 3 ... stb. számú testvére.

SELECT Testver, COUNT(*)
FROM tanulo
GROUP BY Testver

Hogy kiderüljön, hány tanulónak van 1 testvére, 2 testvére, stb., ezért kiíratom a testvér mezőt is az értékei szerint csoportosítva.
Ha valaki bizonytalan abban, hogy mely mező darabszámát kérdezte meg a COUNT(*) paranccsal, akkor inkább használja a COUNT(mezőnév) alakot.

 Ismét kihívok egy tanulót a táblához.






Átismételjük a COUNT utasítás helyes használatát.
3. Mennyi az igazolatlan hiányzása a lányoknak az egyes osztályokban?

SELECT Osztaly, SUM(Oraszam)
FROM hianyzas, tanulo
WHERE Ferfi=0 AND Igazolt=0
GROUP BY Osztaly

Ebben az esetben nem a COUNT-ot kell használni, mert nem a mező darabszámára vagyok kíváncsi, hanem a SUM -ot, mert a mező értékeinek összegére vagyok kíváncsi.

 Ismét kihívok egy tanulót a táblához.




Felhívom a figyelmet a COUNT és a SUM utasítások különbözőségére.
4. Hozzon létre egy olyan ING nevű adattáblát, mely segítségével egy ingről megállapíthatjuk az anyagát, színét, kódját. (A típusok hosszát önállóan határozd meg)

CREATE TABLE ing
(anyag char(10), szin char(10), kod numeric(2))

 Ismét kihívok egy tanulót a táblához.
5. Írassuk ki az 1980.03.01 és 1980.05.31 között született fiúk nevét születési idejük szerint növekvő sorrendben.

SELECT Nev
FROM tanulo
WHERE Ferfi=1 AND Szul_ido BETWEEN {1980.03.01} AND {1990.05.31}
ORDER BY Szul_ido

 Ismét kihívok egy tanulót a táblához.



Átismételjük a BETWEEN és az. ORDER BY utasításokat.
Mindenki jelentkezzen be, s indítsa el az MSQuery-t.
Felkerül a táblára a cím: Lekérdezések nem szigorú illesztéssel		 Új anyag kezdete.
A dolgozat utolsó feladatában rendeztük a születési időket növekvő sorrendbe. Mindenkiben felmerülhetett a kérdés, milyen SQL utasítást kellet volna írni, ha csökkenő sorrendbe akartuk volna rendezni valamely mezőt. Ennek megfelelő utasítás:

DESCENDING, röviden DESC vagy D- csökkenés, párja :
ASCENDING, röviden ASC vagy A - növekedés


Új utasítások:
DESCENDING,
ASCENDING
A dolgozatban az ORDER BY Szul_ido-t használtuk, mely alapértelmezés szerint növekvő sorrendbe rendezi a születési időket.  
Használjuk a m:\munka\adat\tanulo.dbf adattáblát.
1. Kérdezzük le a születési időket, majd a ikonnal rendezzük csökkenő sorrendbe. Nézzük meg az ikon segítségével a lekérdezést SQL nyelven.
Láthatjuk, hogy megjelenik a DESC utasítás:

SELECT Szul_ido
FROM tanulo
ORDER BY Szul_ido DESC

 Tanári irányítás.




Körbejárva ellenőrzöm a munkát.
Füzetbe leíratás.
2. Írassuk ki az Irén nevű lányokat ABC szerint csökkenő sorrendben!
Meg tudjuk-e oldani a feladatot?

Az eddig tanultak alapján nem, mivel a tanulo adatbázisban a diákok vezeték- és utónevét egy mezőben tároljuk.

Meg tudjuk oldani, ha nem szigorú illesztést használunk. Ez a következőt jelenti a példánkban: A keresett névnek csak egy részletét, vagyis az 'Irén' részt adjuk meg. Erre szolgál a LIKE SQL kulcsszó.

 Tanári kérdés.

Új anyag: LIKE
Használata:

1. példa. A-val kezdődő nevek kiírására:

mezőnév LIKE '%rész%', vagyis

nev LIKE 'A%'

2. példa. Olyan nevek kiírása, melynek második karaktere 'L', negyedik karaktere 'M'.

nev LIKE '_L_M%'

 Füzetbe leíratás.

Tanultunk-e máshol helyettesítő, vagy un. joker karakterekről? (DOS: ?,*)
Visszatérés a 2. feladathoz: Kérek egy jelentkezőt az Irén nevű lányok megkeresésére.  
Megoldás:

SELECT Nev
FROM tanulo
WHERE Nev LIKE '%Irén'
ORDER BY Nev DESC

 Diák a táblánál.

Füzetbe leíratás.
Valósítsuk meg az MSQuery programmal a következőképpen:

A Feltétel szerkesztése párbeszédpanel Műveleti jel mezőjében válasszuk ki a TARTALMAZ műveletet, és adjuk meg értékül az 'Irén' szövegrészletet.

Hajtsuk végre a lekérdezést!

A következőket kell látnunk:

 Tanári irányítás.

Körbejárok, és ellenőrzöm, hogy mindenkinek sikerült-e a lekérdezés.
Ellenőrizzük az ikon segítségével a lekérdezést.

Megjegyzés: A TARTALMAZ művelet mellett használható a VÉGE, valamint az ELEJE művelet is, és mindezek tagadása, a nem szigorú illesztések végrehajtásánál.

 Figyelem felhívása az Érték mező tartalmára, a LIKE használatának módjára.
3. Hány C-s lány jár az iskolába, akinek a keresztneve 'A' betűvel kezdődik? Diák a táblánál.
Megoldás:

SELECT COUNT(Nev)
FROM tanulo
WHERE Ferfi = 0 AND Nev LIKE '% A%' AND
Osztaly LIKE '_/C'

 Füzetbe leíratás.
Hajtsuk végre a MSQuery-ben a fenti lekérdezést.

A lekérdezés eredménye:

 Körbejárok, s ellenőrzöm, hogy sikerült-e mindenkinek.
 
Ellenőrizzük az ikon segítségével a lekérdezést.  
4. Kik születtek 1981-ben?
Az eddigi ismeretek alapján a következő megoldás adódik:

a, SELECT Nev
FROM tanulo
WHERE Szul_ido LIKE '1981%'
Tanuló a táblánál
Ezt a megoldást megvalósíttatom a diákokkal Query-ben. Észrevesszük, hogy a dátum típusnál nem engedi meg a Query a részre hivatkozást a TARTALMAZ művelettel.
Ellenben, ha a feltételmezőhöz manuálisan beírjuk a LIKE '1981%' értéket, akkor helyes megoldást kapunk.		 Figyelem felhívása a dátum és a numerikus típusoknál a TARTALMAZ, KEZDETE, VÉGE,... műveletek nem használhatók
Az MSQuery nem véletlenül nem engedi a dátum típusnál a TARTALMAZ műveletet, helyette más utasításokkal tudunk hivatkozni az évre, a hónapra.

Ezek az utasítások a YEAR és a MONTH. Használatuk:
YEAR(mezőnév) illetve MONTH(mezőnév).


Új anyag
Így a 4. feladat megoldása:

b, SELECT Nev
FROM tanulo
WHERE YEAR(Szul_ido)=1981

 Ismét diákot szólítok a táblához.
Ezt a megoldást szintén megvalósítjuk Queryval, mégpedig úgy, hogy a feltétel mezőhöz a Szul_ido helyett a YEAR(Szul_ido)-t írjuk. Tanári magyarázat.

Körbejárok, megnézem, hogy mindenkinek sikerült-e a lekérdezés
5. Írassuk ki azoknak a tanulóknak a nevét, akik 1981-ben születtek, és az iskolai éveik valamelyik októberé-ben hiányoztak.  

SELECT DISTINCT Nev
FROM tanulo, hianyzas
WHERE YEAR(Szul_ido)=1981 AND
MONTH(Datum)=10

 Diák a táblánál.
MSQuery-ben a megvalósítása az előzőek alapján egyértelmű.  
6. Írassuk ki azoknak a lányoknak a nevét, akiknek a születési hónapja 2-vel kevesebb, mint a születési évüknek a 198-ad része.  

SELECT Nev
FROM tanulo
WHERE Ferfi=0 AND
MONTH(Szul_ido)+2=YEAR(Szul_ido)/198

 Diák a táblánál.
Hogy valósítom meg ezt a feladatot Query-vel?
Megoldás:
A feltételmezőbe a Szul_ido helyett a MONTH(Szul_ido)+2 kerül, és értékül a YEAR([Szul_ido])/198 kerül.		 Tanári kérdés

Figyelem felhívása az érték mezőre:
YEAR([Szul_ido])/198



Felhívom a figyelmet, hogy a feltételmező és az érték felcserélésével is ugyanolyan jó eredményt kapunk
Amennyiben marad még időnk:

7. Írassuk ki azoknak a nevét, akiknek a születési hónapja 2-vel kevesebb, mint a hiányzási hónapja.

  

SELECT Nev
FROM tanulo, hianyzas
WHERE MONTH(Szul_ido)=MONTH(Datum)-2

  
Házi feladat: ÖFGY. 63.o. XVIII fejezet 16/c,d.  

Táblakép

tanulo(nev, osztaly, kod, ferfi, szul_ido, testver)
hianyzas(kod, oraszam, igazolt, datum)

1. Sorold fel, mely osztályokba járnak olyan tanulók, akiknek 4-nél több testvére van.

SELECT DISTINCT Osztaly
FROM tanulo
WHERE Testver > 4

2.Add meg, hogy hány tanulónak van 1, 2, 3 ... stb. számú testvére.

SELECT Testver, COUNT(*)
FROM tanulo
GROUP BY Testver

3. Mennyi az igazolatlan hiányzása a lányoknak az egyes osztályokban?

SELECT Osztaly, SUM(Oraszam)
FROM hianyzas, tanulo
WHERE Ferfi=0 AND Igazolt=0
GROUP BY Osztaly

4. Hozzon létre egy olyan ING nevű adattáblát, mely segítségével egy ingről megállapíthatjuk az anyagát, színét, kódját. (A típusok hosszát önállóan határozd meg)

CREATE TABLE ing (anyag char(10), szin char(10), kod numeric(2))

5. Írassuk ki az 1980.03.01 és 1980.05.31 között született fiúk nevét születési idejük szerint növekvő sorrendben.

SELECT Nev
FROM tanulo
WHERE Ferfi=1 AND Szul_ido BETWEEN {1980.03.01} AND {1990.05.31}
ORDER BY Szul_ido

Lekérdezések nem szigorú illesztéssel

DESCENDING, röviden DESC vagy D- csökkenés,
párja : ASCENDING, röviden ASC vagy A - növekedés

1. Kérdezzük le a születési időket, majd rendezzük csökkenő sorrendbe.

SELECT Szul_ido
FROM tanulo
ORDER BY Szul_ido DESC

2. Írassuk ki az Irén nevű lányokat ABC szerint csökkenő sorrendben!

A LIKE (hasonlító) kulcsszó használata:

1. példa. A-val kezdődő nevek kiírására:

mezőnév LIKE '%rész%', vagyis

nev LIKE 'A%'

2. példa. Olyan nevek kiírása, melynek második karaktere 'L', negyedik karaktere 'M'.

nev LIKE '_L_M%'

SELECT Nev
FROM tanulo
WHERE Nev LIKE '%Irén'
ORDER BY Nev DESC

3. Hány C-s lány jár az iskolába, akinek a keresztneve 'A' betűvel kezdődik?

SELECT COUNT(Nev)
FROM tanulo
WHERE Ferfi = 0 AND Nev LIKE '%A%' AND
Osztaly LIKE '_/C'

4. Kik születtek 1981-ben?

a, SELECT Nev
FROM tanulo
WHERE Szul_ido LIKE '1981%'

b, helyesen:
SELECT Nev
FROM tanulo
WHERE YEAR(Szul_ido)=1981

5. Írassuk ki azoknak a tanulóknak a nevét, akik 1981-ben születtek, és az iskolai éveik valamelyik októberé-ben hiányoztak.

SELECT DISTINCT Nev
FROM tanulo, hianyzas
WHERE YEAR(Szul_ido)=1981 AND
MONTH(Datum)=10

6. Írassuk ki azoknak a lányoknak a nevét, akiknek a születési hónapja 2-vel kevesebb, mint a születési évüknek a 198-ad része.

SELECT Nev
FROM tanulo
WHERE Ferfi=0 AND
MONTH(Szul_ido)+2=YEAR(Szul_ido)/198

7. Írassuk ki azoknak a nevét, akiknek a születési hónapja 2-vel kevesebb, mint a hiányzási hónapja.

SELECT Nev
FROM tanulo, hianyzas
WHERE MONTH(Szul_ido)=MONTH(Datum)-2

Házi feladat: ÖFGY. 63.o. XVIII fejezet 16/c,d.

2002. január 30.