Informatika 2 - Adatbázisok használata labor

Ez az oldal a korábbi SCH wikiről lett áthozva.

Ha úgy érzed, hogy bármilyen formázási vagy tartalmi probléma van vele, akkor, kérlek, javíts rajta egy rövid szerkesztéssel!

Ha nem tudod, hogyan indulj el, olvasd el a migrálási útmutatót.

Ha valaki kedvet érez megformázni, nyugodtan...

Pár megjegyzés, amiben ez a nyelv különbözik az eddigiektől:

 - itt szimpla egyenlőségjel az egyenlőségvizsgálat, nem dupla, mint a C-ben;
 - a sztringeket nem idézőjel ("), hanem aposztróf (') határolja;
 - a WHERE-en belüli logikai kifejezésben nem && jelöli az és műveletet, hanem AND, és nem || a vagy, hanem OR;


1.  Milyen szintaktikájú SQL utasítással lehet létrehozni egy rekordot egy táblában?

CREATE TABLE létrehozandó_tábla_neve
(
	első_oszlop_neve első_oszlopban_lévő_adatok_típusa, 
	...
)

Példa:

CREATE TABLE tanszekek
(
	id int primary key,
	shortname varchar(5),
	longname varchar(40),
	teachers int
)

Ez létrehoz egy táblát a tanszékeknek, amiben mindegyiknek van egy egyedi azonosítószáma (id, ez a tábla elsődleges kulcsa), 

tároljuk a legfeljebb 5 karakteres rövid nevét, és a legfeljebb 40 karakteres teljes nevét.

2.  Milyen szintaktikájú SQL utasítással lehet módosítani rekordokat?

UPDATE módosítandó_tábla_neve
SET első_módosítandó_oszlop_neve = első_módosítandó_oszlopba_rakandó_érték,
...
WHERE logikai_kifejezés_ami_azokra_a_sorokra_igaz_amiket_módosítani_akarunk

Példa:

UPDATE tanszekek
SET longname = 'Iszonyatos Info2 Tanszék'
WHERE shortname = 'IIT'

Ez módosítja az IIT teljes nevét, már amennyiben szerepelt a táblában. Ha nem szerepelt, nem hozza létre (!).

3.  Milyen szintaktikájú SQL utasítással lehet törölni rekordokat?

DELETE FROM tábla_amiből_néhány_sort_törölni_akarunk
WHERE logikai_kifejezés_ami_azokra_a_sorokra_igaz_amiket_törölni_akarunk

vagy:

DELETE tábla_amiből_minden_sort_törölni_akarunk

Példa:

DELETE FROM tanszekek WHERE shortname = 'IIT'

Ez törli a tanszékek közül az IIT-t (bár ilyen egyszerű lenne...).

4.  Hogyan lehet vizsgálni egy kifejezés nullitását?

Rossz:
SELECT FROM tanszekek WHERE shortname = null

Ez azért rossz, mert ha valamit null-lal hasonlítunk össze, az összehasonlítás eredménye mindig false, még akkor is, ha 

mindkettő null (hasonlít a lebegőpontos számításoknál a NaN-nal való összehasonlításhoz), mert a null az semmivel nem egyenlő 

(még önmagával se).

Jó:
SELECT FROM tanszekek WHERE shortname is null

Ezt a külön nyelvi konstrukciót lehet a null-ság vizsgálatára használni.

5.  Miképp mőködik a like operátor? Milyen joker karaktereket lehet használni?

Valami reguláris kifejezés-szerű (de nem azonos vele! annak külön szintaxisa van) dolog, ami lehetőséget ad arra, hogy 

stringek egy csoportját kijelöljük, hogy megadjunk egy mintát, ami több sztringre is illeszkedhet. Pl. ha egy százalékjel (%) 

van benne, az tetszőleges (akár nulla hosszú) karaktersorozatot jelenthet, az aláhúzásjel egy akármilyen karaktert jelenthet, 

a dupla aposztróf pedig egy szimpla aposztrófot jelent, és csak azért kell, mert egy szimpla aposztrófot leírva vége lenne a 

sztringnek.

Példa:

SELECT FROM tanszekek WHERE longname like '%barack_''alma'

Ez azokat a tanszékeket fogja visszaadni, amiknek a neve tartalmazza a barack karaktersorozatot, utána valamilyen karakter 

jön, utána egy idézőjel, és végül 'alma' a vége (nincs % a végén, ezért nem lehet az 'alma' után már semmi).

6.  Hogyan működnek az oszlopfüggvények?

Előfordul, hogy nem a konkrét értékek érdekelnek, amik egy táblából kinyerhetőek, hanem pl. azok átlaga, vagy csak az, hogy 

hány, a feltételnek megfelelő sor volt a táblában (pl. bejelentkezés: csak az az érdekes, hogy volt-e megadott nevet és 

jelszót tartalmazó felhasználó a táblában, vagy nem). Ezeket a dolgokat el lehetne úgy is végezni a legtöbb esetben, hogy 

lekérjük az összes lényeges sort a táblából, majd az adatbázison kívül kiszámítjuk, ami érdekel. Viszont ez azért nem a 

legjobb, mert általában sok fölösleges pluszmunka ezt a rengeteg adatot az adatbázis és a külső feldolgozó közt átvinni - pl. 

az átlagszámításhoz a legtöbb nyelvben külön ciklus kéne, lefoglalni az adatoknak sok memóriát, stb. Ezért a legtöbb 

adatbázisengine képes pár ilyen dolgot maga is elvégezni. Pl. ha szeretnénk a tanárok átlagos számát kiszámítani azon 

tanszékek közt, amiknek a nevében van 'SZ' (EVTSZ, SZIT, stb.), elküldhetjük a következő lekérést:

SELECT teachers FROM tanszekek WHERE longname like '%SZ%'

amivel lekérjük a megfelelő tanszékeken dolgozó tanárok számát, majd az adatbáziskezelőn kívül végigfuthatunk a kapott 

sorokon, és kiszámíthatjuk az átlagot, de ez nem valami hatékony. Helyette az alábbi lekéréssel megmondhatjuk az engine-nek, 

hogy ne a tanárok számát adja vissza, minden sorból egyet, hanem csak az összes sorban lévő teacher mező átlagát kérjük, és 

nevezze ezt mondjuk average_teachers-nek:

SELECT avg(teachers) as average_teachers FROM tanszekek WHERE longnamelike '%SZ%'

Így az engine-től egyetlen sort kapunk majd vissza, és annak egyetlen, average_teachers mezője lesz, pont a kívánt értékkel.

A többi oszlopfüggvény, értelemszerű funkcióval, hasonló használattal és működéssel: max, min, sum, count.

7.  Mire szolgál a having kulcsszó?

Előfordul, hogy két táblából van szükségünk egyszerre adatra, és a két tábla közt valamiféle kapcsolat van. Pl. az elsőben 

cégek adatai vannak, a másodikban pedig dolgozók adatai, és a második tábla egyik mezője azt tartalmazza, hogy melyik cégnél 

dolgozik az adott ember (méghozzá a hellyel való spórolás miatt csak a cég azonosítószámát, nincs benne a teljes cégnév). 

Tegyük fel, hogy statisztikai célokra szükségünk lenne az összes olyan cég nevére, ahol dolgozik Smith nevű alkalmazott. Ezt 

megoldhatjuk úgy, hogy először lekérjük az összes Smith vezetéknevű emberhez tartozó sort a dolgozók táblából, majd ezekből 

mind kivesszük a cégazonosítót, és egy ciklussal mindegyikhez lekérjük a hozzá tartozó nevet az első táblából. Ez, az előző 

pontban leírtakhoz hasonló okok miatt nem hatékony. Helyette erre is van egy eszköz magában az engine-ben, és ez a JOIN. 

Megtehetjük, hogy egy ilyen lekérést intézünk az engine-hez:

SELECT DISTINCT ceg_neve FROM cegek JOIN dolgozok ON dolgozok.ceg_azonosito = cegek.ceg_azonosito WHERE dolgozok.nev LIKE 

'%Smith%'

Ez arra utasítja az engine-t, hogy fogja meg mindkét táblát, és kapcsolja össze őket. Konkrétan a két tábla 

Descartes-szorzatát hozza létre; ez az összes lehetséges pár halmaza. Vagyis, ha az első táblában volt 5 elem, a másodikban 

pedig 10, akkor a Descartes-szorzatnak 50 eleme lesz, lesz egy olyan, ahol a dolgozok első sora van párban a cegek első 

sorával, egy olyan, ahol a dolgozok első sora van párban a cegek második sorával, stb. Ezek közül aztán kiválasztja azokat, 

amikre az ON utáni kifejezés igaz, vagyis csak azokkal a cég-dolgó párosokkal dolgozik tovább, ahol a cég alkalmazottja a 

dolgozó. Ezekből aztán kiválasztja azokat, ahol a dolgozó neve tartalmazza a 'Smith' szöveget, végül a maradék sorokból csak 

a cégnevet szedi ki, azok közül is kiszórja azokat, amik kétszer vannak (DISTINCT ceg_neve), és végül a kívánt listával térr 

vissza.

Ez egészen addig jó is, amíg a kiválasztási feltétel csak egyes sorokkal dolgozik, de ha pl. azok a cégek kellenének, ahol az 

összes dolgozó átlagfizetése 200 ezer fölött van, azt nem lenne ilyen egyszerű. Ugyanis, használva az oszlopfüggvényeket, le 

akarhatnánk írni valami ilyesmit:

Rossz:
SELECT ceg_neve FROM cegek JOIN dolgozok ON dolgozok.ceg_azonosito = cegek.ceg_azonosito WHERE avg(dolgozok.fizetes)>200000

Ez viszont nem működne, mert az avg() oszlopfüggvény csak a már elvégzett lekérésekből számít ki valamit, amíg még a 

feltételek kiértékelése és a sorok kiválogatása zajlik, addig nem is létezik, és nem használható. Ezért ezt a feltételt nem a 

WHERE után kell írni, hanem van egy külön kulcsszó, a HAVING:

Jó:

SELECT ceg_neve FROM cegek JOIN dolgozok ON dolgozok.ceg_azonosito = cegek.ceg_azonosito HAVING avg(dolgozok.fizetes)>200000

És ez már a kívánt dolgot csinálja.

Megjegyzés: bizonyos esetekben a JOIN-t nem kell kiírni, és a lekérés mégis azt csinálja, mintha írtunk volna bele (implicit 

JOIN).

8.  Írja fel a select utasítás általános szintaktikáját!

A szögletes zárójelbeliek nem kötelezőek:

SELECT [DISTINCT] oszlopok_amiknek_az_értéke_kell
FROM táblák_amikből_adat_kell
[where logikai_kifejezés_ami_igaz_a_sorokra_amiket_ki_akarunk_választani] 
[group by mi_szerint_akarjuk_csoportosítani_a_visszaadott_sorokat] 
[having logikai_kifejezés_ami_JOINnál_használatos] 
[order by mi_szerint_akarjuk_rendezni_a_visszaadott_sorokat] 

9.  Mire szolgál az isnull függvény MS SQL Serveren?

Arra jó, hogy ilyen "elromlás-biztos" módon szedjem le egy változó értékét: sokszor van olyan, hogy egy változó (táblamező, 

számított kifejezés) lehet, hogy null, és ha null, akkor helyette valami mással (pl. "üres mező" stringgel) akarok dolgozni. 

Ezt az isnull(p1, p2) függvény teszi lehetővé: ha p1 nem null, akkor annak az értékét adja vissza, de ha null, akkor p2 

értékét, ami lehet pl. ilyen helyettesítő érték, hibaüzenet, stb.

10. Miképp lehet megadni két tábla outer joinját MS SQL Serveren?

A LEFT OUTER JOIN abban tér el a már leírt INNER JOIN-tól, hogy az INNER JOIN, ha mondjuk az első táblának volt olyan sora, 

ami semmilyen módon nem került be a kiválasztott sorok közé (vagyis nem volt olyan második táblabeli sor, amivel 

összepárosítva az ON-beli kifejezés igazzá vált volna), akkor egyszerűen nem kerül be. Itt viszont bekerül, és a hozzá 

tartozó második táblabeli oszlopok értékei mind NULL-ok lesznek. A RIGHT OUTER JOIN ugyanez, csak a két tábla szerepe 

felcserélve. A szintaktika pedig:

SELECT gyarto.* 
FROM gyarto left OUTER JOIN termek ON gyartoid=gyarto.id

-- G - 2008.03.25.

• Ezen a helyen volt linkelve a(z) SQL20nyelv.pdf nevű fájl ("SQL20nyelv.pdf" link szöveggel) a régi wiki http://wiki-old.sch.bme.hu/bin/view/Villanyalap/LaborBeugro20080326 oldaláról. (Ha szükséged lenne a fájlra, akkor a pontos oldalmegnevezéssel együtt küldd el a wiki@NO-SPAM.sch.bme.hu címre a kérésedet)

A beugró maga

Itt van egy tömörebb változat, amit én csináltam, tényleg csak a kérdésre a válasz, semmi plusz rizsa.
Az első kérdésre a válasz az enyémben jobb szerintem, de az utolsóban nagyon nem vagyok biztos.
-szerkesztve: 10-es kérdést kaptam, és megkaptam rá a max pontot. - 2009.03.23.
Szóval itt van:

1. Milyen szintaktikájú SQL utasítással lehet létrehozni egy rekordot egy táblában?
a, insert into táblanév (oszlopok listája)
	 values (értékek listája)
b, insert into táblanév (oszlopok listája)
	select...
oszlopok listája elhagyható, select rész lekérdezést csinál, a visszaadott sorok kerülnek be

2. Milyen szintaktikájú SQL utasítással lehet módosítani rekordokat?
update táblanév
set oszlopnév1=érték1, oszlopnév2=érték2,…
where logikai feltétel														//where elhagyható

3. Milyen szintaktikájú SQL utasítással lehet törölni rekordokat?
delete from táblanév where logikai felétel
vagy
delete táblanév			<- így minden sort törlünk a táblából

4. Hogyan lehet vizsgálni egy kifejezés nullitását?
kifejezés is null	 <- csak így lehet, a kifejezés = null értéke akkor sem lesz igaz, ha a
kifejezés értéke egyébként null, mivel ha valamit null-al hasonlítunk össze, az mindig false

5. Miképp működik a like operátor? Milyen joker karaktereket lehet használni?
kifejezés like string minta
String összehasonlító operátor, a kifejezésben keres a mintának megfelelően, és ha a minta
illeszthető a kifejezésre, akkor az operátor igaz értékkel tér vissza. A mintaillesztés
case sensitive, a használható joker karakterek:
_: egy betű helyettesítése
%: tetszőleges hosszúságú szöveg helyettesítése
dupla aposztróf: szimpla aposztróf, mivel, a szimpla aposztróf jel önmagában a stringhatároló
karakter
pl.: select * from termek where nev like ’%er%’
azon termékek listája, amelyek neve tartalmazza az er karaktersorozatot



6. Hogyan működnek az oszlopfüggvények?
A lekérdezés eredményeként kapott rekordhalmazon lehet utólagos kalkulációt végezni velük:
sum: értékek összegzése
min: legkisebb érték megkeresése
max: legnagyobb érték
avg: átlag számítása
count: rekordok megszámolása
A kiszámítandó oszlopfüggvényeket a select listában kell megadni. Az adatbázis-kezelő szerver
először lefuttatja a lekérdezést az oszlopfüggvények nélkül, majd az egyes rekordokat átadja
az oszlopfüggvényeknek, az értékek kiszámítására. Az oszlopfüggvények a null értéket nem
veszik figyelembe, kivéve a count(*) függvény.

7. Mire szolgál a having kulcsszó?
Ha az oszlopfüggvény eredményére teszünk megkötést, akkor ezt a where részben nem adhatjuk meg,
mivel az kiértékelődik még az oszlopfüggvények kiszámítása előtt. Ezért ha az oszlopfüggvények
kimenetére szeretnénk szűrni, akkor azt külön a having kulcsszó után lehet megadni.

8. Írja fel a select utasítás általános szintaktikáját!
select [discint] oszloplista
from táblalista
[where logikai kifejezés]
[group by oszloplista]
[having logikai kifejezés]
[order by oszloplista]

9. Mire szolgál az isnull függvény MS SQL Serveren?
isnull(kifejezés1,kifejezés2)	  Ha a kifejezés1 értéke nem null, akkor a kifejezés1-et adja
vissza, ha null, akkor a kifejezés2-t. Néha van olyan, hogy egy változó értéke lehet, hogy
null, és ha null, akkor lehet, hogy helyette valami mással szeretnék dolgozni.

10. Miképp lehet megadni két tábla outer joinját MS SQL Serveren?
A join művelet tulajdonsága, hogy a nem párosítható rekordokat nem tartalmazza az
eredményhalmaz. A left outer join abban tér el ettől, hogy ha mondjuk az első táblának volt
olyan sora, ami semmilyen módon nem került be a kiválasztott sorok közé, akkor nem kerül be,
itt viszont bekerül, és a hozzá tartozó második táblabeli oszlopok értékei mind null-ok
lesznek. A right outer join ugyanez, csak a két tábla szerepe felcserélve.
select *
from termek right outer join gyarto on gyartoid=gyarto.id
select *
from gyarto left outer join termek on gyartoid=gyarto.id

-- Main.meilinger - Meilinger Ákos - 2009.03.22.