NYUGAT-MAGYARORSZÁGI EGYETEM FAIPARI MÉRNÖKI KAR INFORMATIKA INTÉZET
INFORMATIKA GYAKORLATOK ADATBÁZIS-KEZELÉS
RÁCS ÁGNES SZÁMÍTÁSTECHNIKAI FŐMUNKATÁRS
2003.
-1-
TARTALOM
Rövid útmutató az ACCESS program használatához ............................................. 3 FELADATOK ....................................................................................................... 10 MEGOLDÁSOK ................................................................................................... 17 1. FELADAT ..................................................................................................... 17 2. FELADAT ..................................................................................................... 23 3. FELADAT ..................................................................................................... 29 4. FELADAT ..................................................................................................... 32
-2-
Rövid útmutató az ACCESS program használatához Az ACCESS program adatbázis-kezelést végez, ezért definiáljuk először az adatbázis fogalmát: Adatbázis = tartalmilag összefüggő adatok halmaza a kezelésükhöz szükséges eszközökkel együtt. Az ACCESS adatbázis az alábbi objektumokat tartalmazza: TÁBLA
LEKÉRDEZÉS
ŰRLAP
JELENTÉS
MAKRÓ
MODUL
Az egyes objektumok funkciói: TÁBLA: LEKÉRDEZÉS: ŰRLAP: JELENTÉS: MAKRÓ: MODUL:
Az adatokat tárolja. Szelektálja, rendezi, elemzi, módosítja az adatokat, új táblát hozhat létre. Jelentések, űrlapok alapját képezheti. Adatok bevitele, megjelenítése és módosítása rekordonként, diagram. Táblák és lekérdezések adatait összegzi elemzés, nyomtatás végett. Műveletsort tárol automatikus végrehajtáshoz. ACCESS Basic programot tartalmaz.
A fogalmak részletezése A tábla sorokból áll, egy sor összetartozó adatokat (pl. címjegyzék) tartalmaz, amit REKORDnak hívunk. A rekordot MEZŐK-kel tagoljuk alapadatokra. A MEZŐK a tábla oszlopai, minden oszlopnak saját neve (MEZŐNÉV) van, s az egész oszlop azonos típusú adatot tartalmaz. A mezők neveit, adattípusát, egyéb tulajdonságait a „tábla létrehozása” menüben definiálhatjuk. Ha az adattípus Számláló, akkor a hozzá tartozó mező adatbevitelkor automatikusan töltődik fel a következő, még nem használt egész számmal. Más esetekben lehetőség van a mező tartalmát inicializálni (alapértelmezési érték), a felhasználó által bevitt érték szabályosságát (érvényességi szabály) ellenőrizni, a megjelenítés formátumát, az adat hosszát előírni. Ha egy mezőt INDEXnek definiálunk, akkor ezen mező szerint meggyorsítjuk az adatfeldolgozást (pl. a sorbarendezést), de a rekord módosításának elmentése tovább tart. Az ELNEVEZÉSI SZABÁLY írja elő, milyen karaktersorozatokat használhatunk az objektumok elnevezésére. Eszerint egy (mező-, tábla-, jelentés-, lekérdezés-, űrlap-, makró-,
-3-
modul-) név legfeljebb 64 karakter hosszú lehet, és nem tartalmazhatja a szóköz, tabulátor, pont (.) és ’ ! [ ] karaktereket. ELSŐDLEGES KULCS az a kitüntetett indexelt mező (vagy mezőkombináció), amely a rekordokat egyértelműen azonosítja. Ha nincs ilyen, azt az ACCESS automatikusan is létrehozhatja (Számláló). Az adatbázis több táblát is tartalmazhat, melyek között – a mezőneveken keresztül – KAPCSOLATokat definiálhatunk. Ezen kapcsolatok lehetnek egy az egyhez vagy egy a többhöz típusúak. Gyakran az egyik tábla Elsődleges Kulcs mezőjét kötjük össze a másik tábla Külső Kulcs mezejével. Az összekapcsolhatóság feltétele az, hogy a kapcsolt mezők adattípusa megegyezzen. Számmezőnél a mezőméretnek is egyeznie kell. . A mező adattípusa lehet (a fent látott Számláló típuson kívül):
Szöveg és Feljegyzés, Szám és Pénznem, Dátum és Idő, Igen/Nem
Az egyes adattípusoknál használható formátumok: A formátum-definíciókkal írhatjuk elő, hogy az aktuális mező tartalma milyen formában legyen látható a képernyőn. A definíció általában több részből áll, ezeket ; karakter választja el egymástól. Esetenként lehetőségünk van a mező tartalmától függő megjelenítésre is. A definíciók szintaxisa a mező adattípusától függ. Eleinte fogadjuk el az ACCESS nyújtotta alapértelmezést, mert a formázási igényeket alapfokon az is kielégíti.
Szöveg és Feljegyzés: vezérlő karakterek; szöveg, ha üres; szöveg, ha NULL Vezérlő karakterek: @ karakter kötelező & karakter nem kötelező < minden karakter kisbetű lesz > minden karakter nagybetű lesz
Szám és Pénznem: Vezérlő karakterek:
vezérlő karakterek, ha pozitív; vezérlő karakterek, ha negatív; szöveg, ha 0;szöveg, ha NULL vessző vagy szóköz elválasztó 0 számjegy vagy 0 # számjegy vagy üres $ $ karakter % % karakter E+, E-, e+, etudományos jelölés
Dátum és Idő: a felkínált standard formátumok és példák: Általános dátum 93.04.03 05.34.00 PM Hosszú dátum 1993. április 3. Egyszerű dátum 93. ápr. 03 Rövid dátum 93.04.03 Hosszú idő 5.34.00 PM Közepes idő 05.34 PM Rövid idő 17.34 Vezérlő karakterek: a fogalmak angol nevének kezdőbetűiből állnak (pl. H=hour), és annyiszor leírandók, ahány karakterből áll az adat.
-4-
Igen/Nem formátum:
;szöveg, ha igaz; szöveg, ha hamis ahol igaz = -1 és hamis = 0
Egyéb definíciók SQL: struktúrált adatbáziskezelő módosítására és lekérdezésére.
nyelv
(szabvány)
relációs
adatbázisok
kezelésére,
GYORSMENÜ: az egér jobb billentyűjének megnyomásakor jelenik meg, a belőle elérhető parancsok az aktuális objektumtól függenek. RÖGZÍTETT OSZLOP: ha az oszlopot rögzítjük, akkor az automatikusan átkerül az ablak bal oldalára (Nézet üzemmódban), és helyben marad akkor is, ha a lista többi része egyébként jobbra vagy balra elcsúszik (pl. a vízszintes görgetőlécen történt mozgatás miatt). BEMENETI MASZK: segítségével formázhatjuk a bemeneti mezőt (tagolhatjuk különböző hosszú szakaszokra), és előírhatjuk, hogy a beírt adatot a tagoló karakterekkel együtt mentsük-e vagy nélkülük, illetve, hogy az egyes pozíciókon milyen típusú karakter fordulhat elő. KIFEJEZÉS: tartalmazhat: Operátort, Azonosítót, Függvényt, Konkrét adatot Állandót,
pl. pl. pl. pl. pl.
+ , < , > , = , And, Or, Like mezőnév (mindig szögletes zárójelbe kell írni) DATE, SUM, DLOOKUP szám, #dátum#, „szöveg” True, False, NULL,
KIFEJEZÉSSZERKESZTŐ: a gyorsmenüből (egér jobb billentyű), vagy a […] ikonra kattintással indítható, megkönnyíti a kifejezések létrehozását, ugyanis az ablakból folyamatosan elérhetők a képletbe beépíthető operátorok, objektumok. SZÁMÍTOTT MEZŐ: olyan mező (pl. lekérdezésben), mely nem adatokat, hanem kifejezést tárol, mely kifejezés értéke az adatok aktuális értékétől függ, vagyis az adatok változása értékváltozást okoz a számított mezőben. Az értékváltozás nem automatikus, csak futtatás [!] hatására érvényesül! A számított mező nevét a lekérdezésben a mezőnevek sorában adjuk meg, a név után kötelező a : , majd a számítandó képlet. ÉRVÉNYESSÉGI SZABÁLY: olyan logikai kifejezés, melynek hamis volta azt jelenti, hogy az adatbevitel nem felel meg elképzeléseinknek, pl. a bevitt szám nem az előírt intervallumba esik. A mezőre vonatkozó ~ szabály, mint logikai képlet nem tartalmazhat más mezőre vonatkozó utalásokat, de a rekordra vonatkozó ~ szabály (TULAJDONSÁGOK ikonhoz tartozik) már igen. Hibajelzésként standard szöveg vagy az ÉRVÉNYESSÉGI SZÖVEG utal arra, hogy az adott mezőbe írt adat nem elfogadható, és az adatbevitelt meg kell ismételni. SZŰRÉSI FELTÉTEL: az Érvényességi Szabályhoz hasonlóan logikai kifejezések halmaza, melyek mezőkhöz rendelhetők a lekérdezésekben. Az egy sorba írt feltételeknek egyidejűleg kell teljesülnie, míg a sorok között ’vagy’ kapcsolat van. Egy rekord akkor választódik ki, ha a hozzárendelt szűrési feltétel értéke igaz (TRUE).
-5-
ALAPÉRTELMEZETT ÉRTÉK: ha előírjuk, akkor konkrét adatbevitel nélkül is feltöltődik vele az új rekord kijelölt mezője, de ez menet közben átírható. IRÁSVÉDETT: csak megtekinthető, de nem módosítható. Lehet ilyen egész adatbázis, vagy csak űrlap és rekord is . KIZÁRÓLAGOS: hálózati környezetben így tiltható le mások hozzáférése az adatokhoz. Az írásvédett opcióval együtt alkalmazva az adatok megtekintését másoknak engedélyezhetjük, de a módosítást letiltjuk. CSATOLT TÁBLA: a nyitott adatbázison kívüli tábla, melynek adataihoz a csatolás révén hozzáférhetünk, a csatolt tábla felépítését nem, de rekordjait megváltoztathatjuk. KASZKÁDOLT TÖRLÉS, FRISSÍTÉS: ha a táblák közötti kapcsolatokban (illesztéseknél) hivatkozási integritás (folyamatosan kielégítendő összefüggés) van érvényben, akkor az elsődleges tábla egy rekordjának módosítása maga után vonja a kapcsolódó rekordok módosítását (törlését). IMPORTÁLÁS: adatok (objektumok) átvétele másolással adatbázisból szövegfájlból, táblázatkezelőből. CÍM: Ha az adatra nem a hozzá tartozó mezőnévvel akarunk hivatkozni, akkor ez a rovat is kitöltendő. Előírható az is, hogy a mezőbe KÖTELEZŐ-e adatott írni, illetve lehet-e benne NULL (üres adat).
Az adatbázis tervezése 1. Az adatbázis céljának meghatározása: Milyen adataink vannak, Milyen információkat kívánunk levezetni. 2. A táblák meghatározása: Témakörök meghatározása (táblák) Rekordszerkezet meghatározása (mezők) 3. Megállapítjuk az egyes táblákat összekapcsoló mezőket. 4. A modell finomítása (szerkezet), ellenőrzés rövid mintapéldákkal.
Az adatbázis kezelése Az ACCESS adatbázis 2.3 verzióig két fájlból áll, az első fájl nevének kiterjesztése .MDB, ezen fájl tárolja a tényleges adatokat, míg a másik fájl nevének kiterjesztése .LDB, amely pedig az adatbázis szerkezetét tárolja. Adatbázist nem lehet „mentés másként” menüvel többszörözni. A mentés mindig csak a részfeladatra vonatkozik (tábla, lekérdezés, űrlap, stb), és az előre kijelölt fájl(oka)t módosítja. (Az adatbázis többszörözése intézőből másolással érhető el.)
-6-
Ha az adatbázist már megnyitottuk, akkor választhatunk az objektumai közül. Mindegyik objektumot két különböző üzemmódban tekinthetünk meg, a TERVEZŐ NÉZETben, ill. az ADATLAP (máshol ŰRLAP vagy NYOMTATÁSI KÉP) nézetben. Amíg egy objektumot nem definiáltunk, addig csak tervező nézetben nyithatjuk meg! Ha az objektummal kapcsolatos feladatainkat elvégeztük, zárjuk be, mert ennek hiánya a más objektumokkal végzett munkát megzavarhatja! A Tábla objektum Tervező Nézetében definiálhatjuk a táblaszerkezetet és a mezőjellemzőket. Az Adatlap Nézetben tekinthetjük meg a Tábla tartalmát, elemezhetjük és módosíthatjuk (feltölthetjük) azt. Az adatbázis táblái között logikai kapcsolatok létesíthetők bizonyos mezők egymáshoz rendelésével (Kapcsolatok ikon, táblák hozzáadása után az egérrel átvonszoljuk a mezőnevet a másik tábla megfelelő mezőjére), illetve a kapcsolóvonal kijelölése után a kapcsolat a DEL billentyű megnyomásával törölhető.
Lekérdezés = információ levezetése az adatbázisból Új lekérdezés objektum definiálása a lekérdezésben szerepet játszó táblák kijelölésével kezdődik (Tábla hozzáadása ablak. A kifelejtett táblákat később is pótolhatjuk, az eszköztárból aktivizálhatjuk ezt az ablakot.) Ha egy kifejezésben ismeretlen névre, pl. egy ki nem jelölt tábla mezőjére hivatkozunk, akkor azt a z ACCESS paraméternek tekinti, és feldolgozás közben az értéke lekérdezésre kerül. A lekérdezések létrehozása illetve módosítása Tervező vagy SQL nézetben történhet. SQL nézetben egyfajta parancsnyelven fogalmazhatjuk meg elvárásainkat, míg a tervező nézetben felhasználóbarát környezetben, menük és az egér segítségével juthatunk célba. Alaphelyzetben ún. VÁLASZTÓ lekérdezést hozunk létre, mely közvetlenül nem módosítja a kijelölt tábla(k) adatait, legfeljebb akkor, ha a segítségével kiválasztott rekordok adatait kézzel átírjuk. Mit érhetünk el Választó lekérdezéssel? - A lekérdezés eredménye általában egy új táblázat, mely az eredeti táblák kiválasztott (szűrt) rekordjait és mezőit tartalmazza, esetleg sorrendileg átrendezve, és új, számított oszlopokkal kiegészítve. Az eredménytábla oszlopa valamely bemenő tábla mezője vagy számított mező lehet. Első esetben a mezőnevet egy legördülő menüből választjuk ki, vagy a kijelölt tábla mezőnevét vonszoljuk a lekérdezés-definíció Mező rovatába, egyébként a billentyűzetről (pl. a kifejezés-szerkesztővel) töltjük fel a számított mezőt Új_név: Kifejezés alakban, ahol Új_név nem lehet létező mezőnév. A kiválasztott oszlopok megjelenítése a Megjelenítés rovatokra történő rákattintással kérhető. Ha a kiválasztott mező szerint rendezve (nagyság vagy ABC szerint növekvő vagy csökkenő sorrendben) szeretnénk látni az eredménytáblát, akkor rákattintással aktivizáljuk a Rendezés sor megfelelő rovatát. Ha lekérdezésünkkel valamilyen összesítést, kivonatot szeretnénk készíteni, akkor kattintsunk tervezéskor a ∑ ikonra, mely új Összesítés sort illeszt a lekérdezés definíciójába, melynek legördülő menüje adathalmazokra alkalmazható függvényeket (Sum = elemek összege, Avg = elemek átlaga, Min = legkisebb elem, Max = legnagyobb elem, Count = elemek száma, StDev = elemek szórása, Var = ennyi különböző elem van. First = első elem, Last = utolsó elem) és csoportosítási szempontokat (Where = a számítás ezen mező szerinti szűrés alapján történi, de az eredménytáblában ez a mező nem lesz látható, Group by = a megszűrt rekordokból ezen
-7-
mező alapján csoportokat képezünk, és a számolás csoportonként történik, Expression = számított mezőt jelez, kiértékelés csoportonként, a mezőnévbe írt, csoportfüggvényt tartalmazó képlet alapján) tartalmaz. Ha a lekérdezés definíciója elkészült, mentsük el és futtassuk le (kattintás a [!] ikonra, vagy átlépés Adatlap nézetbe). Választő lekérdezésünk egy ikonra kattintással vagy menüből alakítható másfajta lekérdezéssé: MÓDOSÍTÓ lekérdezés: új, Módosítás sor szúródik be a lekérdezés definícióba, ahova képletet (spec. esetben konstanst) írhatunk , és ennek értékével a tábla szűrt rekordjainak megjelölt mezői felülíródnak. Itt Összesítés, Rendezés, Megjelenítés rovatok nem fordulhatnak elő! TÖRLŐ lekérdezés: a lekérdezés végrehajtásakor a Feltételek kiválasztotta rekordok törlődnek a kijelölt táblából. Itt sem fordulhatnak elő Összesítés, Rendezés és Megjelenítés rovatok! (Több kapcsolt tábla esetén azon tábla * mezőjét a lekérdezés mező sorába kell vinni, amelyből törölni kívánjuk a szűrt sorokat.) TÁBLAKÉSZÍTŐ lekérdezés: A választó lekérdezés végeredménye ezután már nemcsak lekérdezésként, hanem új táblaként is megmarad, mely tábla nevét nekünk kell definiálnunk (de más teendőnk ezután már nincs). Az így képzett tábla adatai már nem módosulnak az eredeti táblák adatainak módosítása után, míg a lekérdezésben futtatáskor megjelenő adatok az eredeti táblák megváltozott adatait mutatják. KERESZTTÁBLÁS lekérdezés: korábban már megismertük az Összesítés sor használatát elemzések készítésekor. Ennek egyfajta továbbfejlesztése ez a lekérdezése, amely az eredmények megjelenítését még áttekinthetőbben, sor-és oszlopfejléccel kombinálva teszi lehetővé. A lekérdezés definíciójában most is szerepelni fog az Összesítés sor, de számítás csak egy mezőre kérhető, amely mezőnek a Kereszttábla attribútuma Érték legyen. A csoportosításra (Group by) kijelölt mezőknek csak egyike lehet Oszlopfejléc, a többit sorfejlécként definiáljuk. HOZZÁFŰZŐ lekérdezés: választó lekérdezésből induljunk, és azt a táblát jelöljük ki (forrás), melynek kiválasztott rekordjait kívánjuk a céltáblához hozzáfűzni. A szűrési feltételek megadása és kipróbálás után kattintsunk a Hozzáfűző lekérdezés ikonjára, minek hatására lekérdezésre kerül a kiegészítendő (cél-) tábla neve, és a lekérdezés-definíció kibővül a Hozzáfűzés sorral, ahol beállíthatjuk, hogy a forrástábla egyes mezői a céltábla mely mezőibe kerülnek át (ha a két táblában azonos mezőnevek fordulnak elő, akkor a alapértelmezésként ezek lesznek egymásnak megfeleltetve). Vigyázzunk rá, hogy a céltábla elsődleges kulcsot tartalmazó mezőjébe (pl. Számláló) nehogy olyan adat kerüljön, ami ott korábban már előfordult. A VÁLASZTÓ lekérdezésnek is van saját ikonja, ezért amíg egy akció- (törlő, módosító vagy hozzáfűző) lekérdezést nem futtattunk le, addig a tervezés fázisában még meggondolhatjuk magunkat, és visszatérve a választó lekérdezésre az eredeti tábla tartalma nem változik. A Választó lekérdezés generálta táblázat nem kiszámított mezőibe ’belenyúlhat’ a felhasználó, és így a forrástábla adatát is módosíthatja, ha nem használtunk Összesítés opciót, illetve nem kerülünk ellentmondásba az elsődleges kulccsal. Az ŰRLAP objektum Tervező Nézetében állíthatunk elő űrlapot, mely a Lekérdezéshez hasonlóan a kijelölt tábla vagy lekérdezés részhalmazát ( a Rekordok és a Mezők tekintetében) választja ki, és az űrlapban funkcióbillentyűket is lehelyezhetünk. Az Űrlap Nézetben
-8-
rekordonként nézhetjük meg, módosíthatjuk és elemezhetjük a táblát. Itt kérhetjük adatainknak az Excel táblázatkezelésen megszokott formájú Diagramját is. Amennyiben az Űrlap tervezésekor a varázslót használjuk, gyorsabban jutunk többféle informatív megjelenítéshez, de szűrésre és rendezésre nem lesz lehetőségünk. Az ábrázolandó mezőket a forrástábla mezőinek a célterületre történő áthelyezésével jelöljük ki – az áthelyezés a [>] (egy mezőnév átvitele) [>>] (minden mezőnév átvitele) [<] (egy mezőnév vissza) [<<] (minden mezőnév vissza) ikonokra kattintással kérhető. Diagramot rajzolni csak akkor tudunk, ha a kijelölt mezők között van numerikus is! Űrlapot leggyorsabban (minimális felhasználói beavatkozással) az Űrlapvarázsló AutoŰrlap funkciójával gyárthatunk. A JELENTÉS objektum Tervező Nézetében állíthatjuk elő a jelentés szerkezetét, amely összegzéseket, részátlagokat is tartalmazhat a kijelölt mezők szerinti csoportosításban. A kész jelentést a Nyomatatási Kép nézetben tekinthetjük meg. A tervezés az Űrlapnál már látottnál hasonlóan történik, vagyis most is használhatunk Varázslót, de grafikus megjelenítésre ez az objektum nincs felkészítve, viszont a táblázat adataiból boríték és címek is előállítható. Jelentést leggyorsabban (minimális felhasználói beavatkozással) a Jelentésvarázsló AutoJelentés funkciójával gyárthatunk.
Általános megjegyzések Bármely objektum bármely nézetét használva ne felejtsük el, hogy az általunk végrehajtott módosítások csak akkor tárolódnak, ha kérjük a File menü Mentés funkcióját, vagy rákattintunk a Mentés ikonra. Enélkül a nézetek és az objektumok közötti váltás a módosítások elvesztését okozza! Az ACCESS program használata során gyakran igénybe vehetjük a különböző VARÁZSLÓK segítségét munkánk felgyorsítására, megkönnyítésére. Ezek a Segédletek közé tartoznak, mint ilyenek, a Segédletkezelő segítségével telepíthetők, módosíthatók és törölhetők. Az ACCESS programcsomag szinte teljes körű megismerése lehetséges a Súgó Menü intenzív használatával, illetve a beépített mintaalkalmazások tanulmányozása révén. Alaphelyzetben a képernyőn látható beépített eszköztárak (ikonok) objektumfüggőek, de természetesen az lehetővé teszi egyéni eszköztárak, fontkészletek , színek, stb. használatát.
Függvények A legfontosabb függvényekkel táblázatkezelőben már megismerkedtünk. Itt csupán az angol elnevezését adjuk meg néhánynak, mivel Accessben ezeket kell használni. Paraméterezésükről a kifejezés-szerkesztőben tájékoztatást kaphatunk. A kifejezés-szerkesztő megkönnyíti a mezőnevek beillesztését is a képletekbe. Abs Abszolútérték Left$ Balról kiválaszt Atn Arcustangens Len Szöveg HOSSZa Avg Mid$ Középről kiválaszt Átlag Count Elemszám Month Hónapot kiválaszt dátumból Day Napot kiválaszt dátumból NOW MOST, aktuális gépi idő DateValue Dátumértékké alakít szöveget Nper Periódusszám iif Rate Kamatláb Ha In Tartalmaz(-ban, -ben) Pmt Részlet Int Sqr Egészrész Négyzetgyök Right$ Jobbról kiválaszt Year Évet kiválaszt dátumból -9-
FELADATOK 1. feladat Készítsük el az alábbi ELADÁS táblát a vásárlásokról! Számlaszám Termék Mennyiség Számláló Szöveg(15) Egész szám
Sony TV Walkman ITT TV Sony TV Walkman Wall TV CD Rádió sony CD egér CD
Év-hó-nap Dátum (rövid) Alapértelmezési érték. Gépi idő(ma) Érvényességi szabály. Aktuális napnál korábbi érték
1 3 2 15 35 2 3 1 35 3 1
Átutalás-e Vevőkód Igen/nem Szöveg(6) Alapértelmezési érték. „xy”
nem nem igen igen igen nem igen nem igen nem igen
nagyi iskola
Lekérdezések: 1. Válogassunk az oszlopokból. 2. Rendezzük a táblázatot (csak megjelenítéskor). a) Elsődleges szempont terméknév, másodlagos mennyiség b) Elsődleges szempont mennyiség, másodlagos terméknév 3. A) Válogassuk ki azokat a sorokat, amelyekben készpénzzel fizettek és 1-nél többet vásároltak B) Válogassuk ki azokat a sorokat, amelyekben készpénzzel fizettek vagy 1-nél többet vásároltak 4. Válogassuk ki azokat a sorokat, amelyekben nem „xy” a vevő és 10-nél többet vásároltak 5. A) Válogassuk ki azokat a sorokat, amelyekben TV-t vásároltak (like). B) Válogassuk ki azokat a sorokat, amelyekben W-vel kezdődik a terméknév. C) A)és B) illetve A)vagy B) feltételek( AND művelet 1 oszlopon belül)
-10-
Gyakoroljuk a dátum-műveleteket! (Írjunk át néhány dátumot 2000, 2001-es dátumra, legyenek decemberiek is!) 6. Válogassuk ki 2002.01.01-nél régebbi, és 2000.12.01-nél későbbi vásárlásokat! 7. Képezzünk számított oszlopot (csak a lekérdezésben, a táblában nem megjelenő oszlop), amely a dátumból kiválasztja az évet, illetve hónapot. Gyakoroljuk az összesítő függvényeket! 8.
Hány darab terméket adtak el készpénzzel, illetve átutalással?
9.
Hány vásárlás történt készpénzzel, illetve átutalással?
10.
Hány vásárlás történt az egyes években?
11.
Hány darab terméket adtak el az egyes termékekből?
12.
Hány darab TV-t adtak el összesen?
13.
Készítsük el a TERMÉKEK táblát! A Terméknév oszlopot vigyük át táblakészítő lekérdezéssel! (Minden terméknév egyszer legyen az új táblában! Alkalmazzuk a csoportosítást! Egészítsük ki egységár oszloppal a táblát!)
14.
Számítsuk ki a számlaértéket (csak a lekérdezésben megjelenő oszlop) a két tábla összekapcsolásával!
15.
Készítsünk kereszttáblás lekérdezést a 14. feladat továbbfejlesztéseként. Mennyi volt a bevétel az egyes termékekből az egyes években?
16.
Módosító lekérdezéssel növeljük az egységárat 20%-kal!
17.
Módosító lekérdezéssel növeljük az egységárat azon sorokban, ahol az egységár 50000 Ft felett van!
18.
Módosító lekérdezéssel növeljük az egységárat 10%-kal a TV-kre, 15%-kal a többire! (iif függvény)
-11-
2. feladat Hozzon létre egy új adatbázist BGYAR néven, mely az alábbi, TERMÉK Név készlet Anyagár Bérköltség Amortizáció Ár Asztal1
312
800
400
300
Asztal2
564
1400
1700
300
Asztal3
1245
1600
400
300
Asztal4
6548
900
900
300
Szék1
9654
800
600
300
Szék2
1236400
900
354
300
Szék3
4562
700
456
300
Szék4
789
400
521
300
Szék5
0
500
854
300
Szék6
2548
654
456
300
Szekrény1
2564
1236
1256
300
Szekrény2
567
4521
2321
300
Szekrény3
0
1258
1212
300
Szekrény4
453
4751
1232
300
Szekrény5
0
2365
1313
300
Szekrény6
987
589
3212
300
illetve MEGRENDELÉS nevű táblákat tartalmazza: Áru
Megrendelő
Mennyi
Kiszállítandó
Asztal1
Vevő1
1001
1997.06.12.
Szék2
Vevő2
400
1997.06.24.
Szék2
Vevő3
678
1997.06.12.
Szék2
Vevő4
1800
1998.06.24.
Szekrény1
Vevő3
10
1997.06.07.
Szekrény4
Vevő6
1500
1998.06.12.
Szék4
Vevő2
800
1998.06.24.
Szekrény6
Vevő3
100
1. Korlátozza 20 karakterre a Név és Áru rovatok adatainak hosszát
-12-
Érvényességi szabály előírásával biztosítsa, hogy a Megrendelés tábla továbbiakban beírandó sorainak Kiszállítandó mezőjébe beírandó érték ne legyen a mai dátumnál korábbi. 2. Számítsa ki: a.) az ÁR mezőt az Anyagár + Bérköltség + Amortizáció képlettel. b). Hogy a gyártás nyereséges legyen, növelje meg az ÁR előbb kiszámított értékét 20%-kal. c). Azon termékeknél, ahol a Bérköltség az 500Ft-ot, vagy az Anyagár az 1000Ftot meghaladja, növeljük az ÁR mezőt még 10%-kal. 3. Tegyük át egy NEMGYÁRTOTT táblába azokat a termékeket (minden adat), amelyekből a készlet 0. Töröljük a Termék táblából ezeket a sorokat! 4. Állapítsa meg, mely megrendeléseket nem tudjuk a jelenlegi készlet alapján teljesíteni, mennyi és milyen terméket kell a közeljövőben gyártani. (Segédfeladat: Állapítsa meg, mely termékből mennyit rendeltek.) 5. Az Amortizációt módosítsa termékcsoportonként: Legyen 300 a szék típusú termékekre, Legyen 500 az asztalokra, Legyen 800 a szekrényekre! 6. Hozzon létre egy Teljesített táblát azon megrendelésekből, melyek kiszállítását már 1998 előtt el kellett végezni! Törölje e sorokat a Megrendelés táblából! 7. Állítsa be a Megrendelés tábla Kiszállítandó mezejének alapértelmezési értékét a mai napnál 120 nappal nagyobbra, és írjon néhány új megrendelést! 8. Állapítsa meg melyik évben hány db terméket kell kiszállítani a megrendelések alapján! 9. Állapítsa meg termékcsoportonként mennyi a készlet, ill. a megrendelés! Készítsen egy kimutatást, hogy mely termékcsoportból mennyi lesz a maradvány kiszállítás után (A termékcsoportokat a TERMÉKnév első 4 betűje határozza meg. Válassza ezt le egy külön oszlopban a LEFT$ függvénnyel!) 10. Növelje a Kiszállítandó mező értékeit 4 hónappal ( lekérdezéssel)! 11. Számítsa ki a megrendelések számlaértékét! 12. A vevő3 és vevő6 megrendelők (törzsvásárlók) vagy az 1000-nél nagyobb mennyiségű rendelésekre 30% kedvezményt kapnak. Számítsa ki a kedvezmények értékét!
-13-
3. Feladat Hozza létre a MÉRÉS táblát egy erdészeti üzem számára az erdő összes fájának adatairól: Mező:
SORSZ FAFAJ
ÁTMÉRŐ MAGASSÁG KOR MIDŐ
Leírás:
azonosító
cm-ben
Típus
számláló Szöveg(10) Egész szám
m-ben Egész szám
Alapértelmezett érték:
mérési idő Egész Rövid szám dátum Aktuális gépi idő
1
Bükk
60
30
120
2
Bükk
50
28
80
3
Bükk
70
32
90
4
Bükk
30
15
50
5
Fenyő
40
25
80
6
Fenyő
30
20
50
7
Fenyő
20
10
40
8
Cser
30
15
30
9
Cser
40
25
50
10
Cser
40
20
40
KÖRLAP TÉRF alapterület Egyszeres szám Lekérdezéssel töltjük ki!
(A több ezer sor leírását itt most mellőzzük.)
1.
Számítsuk ki soronként, és tároljuk is a MÉRÉS táblában a KÖRLAP-ot (alapterület)!. (Majd térfogatszámításhoz felhasználjuk, és az erdészek más fontos mutatókat is számolnak belőle.)
2.
Számítsuk
ki,
és
tároljuk a MÉRÉS
táblában az
egyes
fák
fatérfogatát
a
MAGASSÁG*KÖRLAP*ALAKTÉNYEZŐ képlettel. (Az alaktényező elméletileg meghatározott érték, a fafajra jellemző állandó.) a)
Hozzunk létre egy új táblát a fafajra jellemző elméleti adatoknak! Az erdő minden fafaja szerepeljen, pontosan egyszer! Vigyük át táblakészítő lekérdezéssel a FAFAJ oszlopot, majd tervező nézetben egészítsük ki az ALAK(tényező), VÁGÁS(érettségi kor) oszlopokkal! Az ALAK oszlop típusa egyszeres szám, érvényességi szabálya: (0;1) intervallumba eső szám, a VÁGÁSKOR egész szám.
Megjegyzések: E táblában szerepelhetnek olyan fajok is, amelyek a konkrét erdőben nem. A vágásérettség azt jelenti, hogy az ennél idősebb fák állapota inkább romlik, tehát ebben a korban célszerű kivágni, helyette újat ültetni. A fenti térfogat megközelítően kúptérfogat.
-14-
Az új tábla neve: FAJOK. FAFAJ Bükk Fenyő Cser Gyertyán
VÁGÁSKOR 110 60 40 70
ALAK 0,3 0,4 0,35 0,25
b) Ezt a táblát felhasználva már a térfogat kiszámítható. 3.
Határozzuk meg, mennyi az erdő összes fatérfogata, mennyi fafajonként a fatérfogat.
4.
Állapítsuk meg a a) legnagyobb fatérfogatot, b) fafajonként az átlagos fatérfogatot, c)fafajonként a fák (sorok) számát!
5.
Írassuk ki a MÉRÉS tábla azon sorait, amelyek meghaladják az azonos faj átlagos térfogatát!
6.
Írassuk ki a MÉRÉS tábla azon sorait, amelyek a legnagyobb fa térfogatát legalább 75%ban megközelítik.
7.
Gyűjtsük ki a vágásérett fákat! Számítsuk ki ezek térfogatösszegét!
8.
Módosítsuk az átmérőt 10%-kal növelve, a magasságot 20%-kal, kort-t 5 évvel, a mérési időt 5 évvel növeljük!
9.
Tegyük külön táblába a kis fákat, amelyeknek magassága <=15, vagy a térfogata <1m3! (KISFA tábla) Töröljük a MÉRÉS táblából ezeket a sorokat!
10.
Úgy döntöttünk mégsem lesz gyérítés, fűzzük össze a MÉRÉS és KISFÁK táblát!
11.
Az összefűző lekérdezés gyakorlásaként fűzzük az alábbi(FAJ) táblát a FAJOK táblához!
(Hozzáfűzés előtt létre kell hozni, szándékosan csak a rövidítéseket tartalmazza.) FAFAJ B F Cs Gy
ALAKTÉNYEZŐ 0,3 0,4 0,35 0,25
-15-
VÁGÁSÉRETTSÉGI_KOR 110 60 40 70
4. Feladat: Készítsen két táblát egy használt autókereskedés számára: 1. AUTÓK tábla: Típus Évjárat Megtett út Fogyasztás Ár Szöveg Szám Szám Szám Szám (10) (egész) (dupla) (egyszeres) (dupla) 1. A Fogyasztás oszlopot az alapértelmezés kitöltésével
Tulaj-Tel Üzemanyagár Szöveg Szám (6) (egyszeres) egységesen 6,1-re töltse
Előleg Szám (dupla) ki;
Üzemanyagárat 140-re. 2.
Az érvényességi szabály előírásával biztosítsa, hogy az Évjárat (1985;2002) közé essen.
A körülbelül 8 sor adat begépelésekor néhány Típusnév végére illesszen „-D”-t (2 karakter!), ezzel jelölve a Diesel kocsikat, a Tulaj-Telefon számok között legyenek azonos értékek (Kisvállalkozások több kocsit is el akarnak adni). Az Előleg oszlopot nem kell még kitöltenie. 3. Gyűjtse ki a táblából a Tulajdonosok telefonszámait, mindegyik egyszer szerepeljen a listában. Töltse át ezen telefonszámokat egy Tulajdonosok (2.) táblába, majd egészítse ki a Tulajdonosok táblát a következő oszlopokkal, és töltse is ki adatokkal az alábbiak szerint. Név szöveg(20)
Lakhely szöveg(10)
Körzetszám szöveg (2)
A Lakhely rovat csak a „Győr” ill. „Sopron” helységeket tartalmazza. A Név oszlopban szerepeljen „ÁLOM KFT”. A Körzetszám oszlopot lekérdezéssel is kitöltheti. 4. Módosítsa 153-ra az Üzemanyagár oszlop azon sorait, amelyekben a típus „-D”-re végződik! 5. Töltse ki az Előleg oszlopot az Ár mező 60%-ával az 1990 előtti típusokra, az ÁR 50%ával a többire! 6. Gyűjtse ki az AUTÓK táblából a soproni tulajdonosok kocsijainak adatait! 7. Rendezze az AUTÓK táblát évjárat szerint csökkenő sorrendben! Évjáratonként csoportosítva határozza meg az Ár minimumát! 8. Gyűjtse ki az AUTÓK táblából azokat a kocsikat, amelyeknek vagy a megtett útja kisebb 80000-nél, vagy az életkora kisebb 5 évnél! Egy külön oszlopban számítsa ki az életkort, Használja a YEAR és NOW függvényeket! 9. Keresse ki a legkisebb árat! Vajon melyik kocsik tartoznak a legkisebb árat legfeljebb 30%ban meghaladó árú kocsik közé? 10. Listázza ki azon tulajdonosok telefonszámait KÖRZET+TEL alakban, akiknek nevében nem szerepel a KFT megjelölés! 11. Csoportosítsuk a megtett utat 100000-es csoportokba! Készítsünk kereszttáblás lekérdezést, amely megmutatja évjáratonként és megtett út szerinti csoportosításban az átlagárat!
-16-
MEGOLDÁSOK 1. FELADAT 1/1. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Megjelenítés Feltétel 1/2A. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Megjelenítés Feltétel 1/2B Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Megjelenítés Feltétel
ELADÁS Számlaszám Termék Mennyiség Termék
Mennyiség
Átutalás-e
Vevőkód
ELADÁS
Számlaszám Termék Növekvő
Mennyiség Csökkenő
Év-hó-nap
Átutalás-e
Vevőkód
Év-hó-nap
Átutalás-e
Vevőkód
ELADÁS
Számlaszám Mennyiség Termék Csökkenő Növekvő
-17-
1/3A Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Megjelenítés Feltétel 1/3B Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Megjelenítés Feltétel VAGY 1/4f. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Megjelenítés Feltétel
ELADÁS
Számlaszám Termék
Mennyiség
Év-hó-nap
>1
Átutalás-e
Vevőkód
FALSE
ELADÁS
Számlaszám Termék
Mennyiség
Év-hó-nap
Átutalás-e
Vevőkód
>1 FALSE
ELADÁS
Számlaszám Termék
Mennyiség
Év-hó-nap
Átutalás-e
Vevőkód „
>10
Not xy”
„
Megjegyzés: Not xy” helyett írható: <>”xy”. 1/5A. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Megjelenítés Feltétel
ELADÁS
Számlaszám Termék
Mennyiség Év-hó-nap Átutalás-e
„
Like *TV” -18-
Vevőkód
1/5B. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Megjelenítés Feltétel
ELADÁS
Számlaszám Termék
Mennyiség
Év-hó-nap
Átutalás-e
Vevőkód
„
Like W*”
1/5C. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Megjelenítés Feltétel Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Megjelenítés Feltétel
ELADÁS
Számlaszám Termék
Mennyiség Átutalás- Vevőkód
„
„
Like W*” AND Like *TV”
ELADÁS
Számlaszám Termék
Mennyiség Átutalás- Vevőkód
„
Like W*” „
Vagy
Like *TV”
1/6. Táblák felsorolása
a „Tábla
ELADÁS
hozzáadása
” ablakkal Mező
Szá
Ter
Men
Év-hó-nap
Á
Rendezés Megjeleníté
Feltétel
„
„
Datevalue( 2000.01.15”) A feltételben leírt képlet magasabb verzióban egyszerűsíthető, kihagyható a konverziós függvény és a mezőnév.
-19-
1/7. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező
ELADÁS
Számla Termék Menn Év-hó- Év:Year([Év-hó-nap]) Hó:Month([Év-hó-nap]) szám yiség nap
Rendezés Megjelenítés Feltétel 1/8. Táblák felsorolása a „Tábla hozzáadása ” ablakkal
ELADÁS
Mező Rendezés Összesítés Feltétel 1/9. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Összesítés Feltétel
Mennyiség
Átutalás-e
Sum
Group by
ELADÁS
Számlaszám Termék Count
Átutalás-e
Count
Group by
Megjegyzés: Elegendő egy oszlopra a Count függvény használata.
1/10. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Összesítés Feltétel
ELADÁS
Számlaszám
Év-hó-nap
Count
Count
Megjegyzés: Nem szükséges az Év-hó-nap oszlopot is beválasztani a lekérdezésbe.
-20-
ÉV:Year([Év-hó-nap]) Group by
1/11. Táblák felsorolása a „Tábla hozzáadása ” ablakkal
ELADÁS
Mező Rendezés Összesítés Feltétel 1/12. Táblák felsorolása a „Tábla hozzáadása ” ablakkal
Termék Group by
Mennyiség Sum
ELADÁS
Mező Rendezés Összesítés Feltétel
Termék Where
Mennyiség Sum
„
Like *TV” A WHERE összesítő függvényt akkor használjuk, ha egy oszlopra feltételt kell írnunk, de sorbeli értékétől független az összesítés. Jelentése: ahol a feltétel teljesül. 1/13. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Összesítés Feltétel
ELADÁS
Termék Group by
A fenti választó lekérdezést (ellenőrző futtatás után) alakítsuk TÁBLAKÉSZÍTŐ lekérdezéssé (lekérdezés menü) az új tábla neve: TERMÉKEK. A táblakészítő lekérdezés lefuttatása után a TERMÉKEK táblát tervező nézetben egészítjük ki.
-21-
1/14. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Megjelenítés Feltétel
ELADÁS Termék Mennyiség Számlaszám Termék
TERMÉKEK Termék Egységár Mennyiség
Számlaérték:[Mennyiség]*[Egységár]
Megjegyzés: A Mennyiség mezőt éppúgy nem szükséges a mező sorban felsorolni a képlet kiszámításához, ahogy az Egységárat sem soroltuk fel a képleten kívül. A két Termék mezőt összekötő vonal azt a feltételt jelenti, hogy az ELADÁS tábla Termék mezőjének értéke egyezzen meg a TERMÉKEK tábla Termék mezőjének értékével. Enélkül az ELADÁS tábla minden sorához hozzáillesztené a TERMÉKEK tábla minden sorát, s így minden lehetséges Mennyiség*Egységár szorzatot megkapnánk.
1/15. Táblák felsorolása a „Tábla hozzáadása ” ablakkal
ELADÁS Termék Mennyiség
Mező Év:YEAR([Év_hó_nap] Rendezés Összesítés: Group by Kereszttábla Oszlopfejléc Feltétel
TERMÉKEK Termék Egységár Termék Group by Sorfejléc
-22-
Számlaérték:[Mennyiség]*[Egységár] Sum Érték
1/16. 1/16A. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Módosítás Feltétel
TERMÉKEK
Egységár [Egységár]*1,2
1/16B. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező Rendezés Módosítás Feltétel
TERMÉKEK
Egységár [Egységár]*1,1 >50000
1/17. Táblák felsorolása a „Tábla hozzáadása ” ablakkal
TERMÉKEK
Mező Egységár Rendezés „ Módosítás iif([Termék] like *.TV”;[Egységár]*1,1;[Egységár]*1,15) Feltétel Megjegyzés: Az iif függvény feltételében alkalmazható az alábbi kifejezés is: Right$([Termék];2)=”TV”
2. FELADAT
2/1. A táblák tervező nézetében kell az adott mezőre a MEZŐTULAJDONSÁGokat beállítani. Például a Kiszállítandó mezőre kattintás után a hozzátartozó érvényességi szabály rublikájába a >=NOW( ) függvényt kell írni. A NOW( ) függvény paraméter nélküli, az aktuális gépi időt jelenti. Mindazonáltal a ( ) zárójel függvény volta miatt szükséges (nyelvtani=szintaktikai szabály).
-23-
2/2A. Táblák felsorolása TERMÉK a „Tábla Név hozzáadása készlet ” ablakkal Mező Ár Rendezés Módosítás
[Anyagár]+[Bérköltség]+[Amortizáció]
Feltétel 2/2B. Táblák felsorolása TERMÉK a „Tábla Név hozzáadása készlet ” ablakkal Mező Ár Rendezés Módosítás
[Ár]*1,2
Feltétel 2/2C. Táblák felsorolása TERMÉK a „Tábla Név hozzáadása készlet ” ablakkal Mező Ár
Bérköltség
Anyagár
Rendezés Módosítás Feltétel
[Ár]*1,1 >500
VAGY
>1000
-24-
2/3. Táblák felsorolása TERMÉK a „Tábla Név hozzáadása készlet ” ablakkal Mező Név készlet
Anyagár
Bérköltség
Amortizáció
Ár
Rendezés Feltétel
=0
Az így elkészített választó lekérdezést futtatás-ellenőrzés után TÁBLAKÉSZTŐ-vé alakítjuk (Lekérdezés menü), az új tábla neve NEMGYÁRTOTT. Működtetéskor a táblák listájába bekerül az új tábla. Ezután TÖRLŐ lekérdezéssé alakíthatjuk, majd azt is működtetjük. Mindhárom lekérdezés mentésére (mentés másként) akkor van szükség, ha a lekérdezések meglétét ellenőrzik (pl. dolgozatban).
2/4A. Segédfeladat, a lekérdezést ÖMET néven menthetjük. Táblák felsorolása MEGRENDELÉS a „Tábla Áru hozzáadása Mennyi ” ablakkal Mező Áru Mennyi Rendezés Összesítés
Group by
Sum
Feltétel 2/4B. Táblák felsorolása ÖMET a „Tábla Áru hozzáadása SUMofMennyi ” ablakkal Mező Név készlet
TERMÉK Név készlet Kell: [SUMofMennyi]-[készlet]
Rendezés Feltétel
<[SUMofMennyi]
VAGY
-25-
Az Áru és Név mezőket összekötő vonal biztosítja, hogy a TERMÉK tábla készlete és az ÖMET lekérdezés termékenkénti Mennyiségösszege ugyanarra a termékre vonatkozzék. Jelentése: [ÖMET].[Áru]=[TERMÉK].[Név] feltétel minden rekordban (sorban) teljesül. 2/5. Csak a szekrény típusú termékekre oldjuk meg a feladatot, a többire is hasonló a megoldás. Táblák felsorolása TERMÉK a „Tábla Név hozzáadása készlet ” ablakkal Mező Név
Amortizáció
Rendezés
Módosítás Feltétel
800 „
Like szek*”
Másik megoldásként próbálkozhat egymásba ágyazott iif függvényekkel. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező
TERMÉK Név készlet Amortizáció
Rendezés
Módosítás
iif(left$([Név];4)=”szek”;800;iif(left$([Név];4)=”aszt”;500;300))
Feltétel 2/6. Táblák felsorolása MEGRENDELÉS a „Tábla Áru hozzáadása Mennyi ” ablakkal Mező Áru Mennyi
Kiszállítandó
Megrendelő
Rendezés „
Feltétel
-26-
2/7. A MEGRENDELÉS tábla tervező nézetében kattintsunk a Kiszállítandó mezőre, majd a mezőtulajdonságok közt az alapértelmezett értékre! A beírandó képlet: NOW( )+120.
2/8. Táblák felsorolása MEGRENDELÉS a „Tábla Áru hozzáadása Mennyi ” ablakkal Mező év:YEAR([Kiszállítandó])
Mennyi
Rendezés Összesítés: Group by
Sum
Feltétel
2/9. Termékcsoportonkénti megrendelés mennyisége (MTM): Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező
MEGRENDELÉS Áru Mennyi Mennyi
Tcs:left$([Áru];4)
Sum
Group by
Rendezés Összesítés: Feltétel
Termékcsoportonkénti készlet (TKM): Táblák felsorolása TERMÉK a „Tábla Név hozzáadása készlet ” ablakkal Mező Tcs2:left$([Név];4)
készlet
Rendezés
Összesítés
Group by
Sum
Feltétel
-27-
Feltételezzük, hogy a megrendelt termékekből elegendő mennyiség áll rendelkezésre a készletben, bár az eredmény lehet negatív. Ebben az esetben külön vizsgálódás tárgya, hogy az adott termékcsoporton belül mely termékből van hiány. Így: Táblák felsorolása TKM MTM a „Tábla Tcs2 Tcs hozzáadása SUMof készlet SUMofMennyi ” ablakkal Mező Tcs Marad:[Sumofkészlet]-[SUMofMennyi] Rendezés Feltétel VAGY A Tcs2 és Tcs közti összekötő vonal biztosítja, hogy ugyanazon termékcsoport készletösszege illetve megrendelt mennyiségösszege szerepel egy sor képletében.
2/10. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező
MEGRENDELÉS Áru Mennyi Kiszállítandó
Rendezés Módosítás:
[Kiszállítandó]+4*30
Feltétel Megjegyzés: Két dátum különbsége napokban értendő. 2/11. Az 1. feladatsor 14. feladatához hasonlóan. 2/12. Táblák felsorolása MEGRENDELÉS a „Tábla Áru hozzáadása Mennyi ” ablakkal Mező Áru Mennyi Megrendelő
TERMÉK Név készlet KEDVEZMÉNY:[Mennyi]*[Ár]*0,3
Rendezés Feltétel VAGY
>1000 Vevő3 Vevő6
-28-
3. FELADAT 3/1. Táblák felsorolása MÉRÉS a „Tábla SORSZ hozzáadása FAFAJ ” ablakkal Mező: KÖRLAP Módosítás ([ÁTMÉRŐ]/2)^2*3,14/10000 Feltétel (A körlapot m2-ben fogjuk a térfogatképletben felhasználni.) 3/2. Táblák felsorolása MÉRÉS a „Tábla hozzáadása FAFAJ SORSZ ” ablakkal Mező TÉRF
FAJOK FAFAJ ALAK
Rendezés Módosítás
[KÖRLAP]*[MAGASSÁG]*[ALAK]
Feltétel A két FAFAJ mező közti vonal biztosítja, hogy arra a fafajra vonatkozó alaktényezővel szorozzunk, amelyre az adott körlap és magasság is vonatkozik a másik táblában. 3/3.A. Táblák felsorolása a MÉRÉS „Tábla SORSZ hozzáadása ” FAFAJ ablakkal Mező: TÉRF Rendezés Összesítés SUM Feltétel .
3/3B. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező: Rendezés Összesítés Feltétel
3/4A. Táblák felsorolása a MÉRÉS „Tábla SORSZ hozzáadása ” FAFAJ ablakkal TÉRF Mező: Rendezés MAX Összesítés
3/4B. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező: Rendezés Összesítés -29-
MÉRÉS SORSZ FAFAJ TÉRF
FAFAJ
SUM
GROUP BY
MÉRÉS SORSZ FAFAJ TÉRF
FAFAJ
AVG
GROUP BY
3/4C. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező: Rendezés Összesítés
MÉRÉS SORSZ FAFAJ TÉRF
FAFAJ
COUNT
GROUP BY
3/5. Táblák felsorolása MÉRÉS a „Tábla FAFAJ hozzáadása SORSZ ” ablakkal Mező TÉRF
3/4C FAFAJ AVGofTÉRF SORSZ
FAFAJ
KOR
MAGASSÁG
ÁTMÉRŐ
Rendezés Feltétel
>[AVGofTÉRF]
3/6. Táblák felsorolása MÉRÉS a „Tábla hozzáadása FAFAJ SORSZ ” ablakkal Mező TÉRF
3/4A MAXofTÉRF SORSZ
FAFAJ KOR
Rendezés Feltétel
ÁG
RŐ
>[MAXofTÉRF]*0,75
3/7A. Táblák felsorolása MÉRÉS a „Tábla FAFAJ hozzáadása SORSZ ” ablakkal Mező SORSZ FAFAJ
FAJOK FAFAJ ALAK KOR
Rendezés Feltétel
MAGASS ÁTMÉ
>[VÁGÁSKOR]
-30-
ÁTMÉ MAGASS TÉRF
3/7B. Táblák felsorolása a „Tábla hozzáadása ” ablakkal Mező
MÉRÉS FAFAJ SORSZ
FAJOK FAFAJ ALAK KOR
TÉRF
WHERE >[VÁGÁSKOR]
SUM
Rendezés Összesítés Feltétel
3/8. Táblák felsorolása MÉRÉS a „Tábla SORSZ hozzáadása FAFAJ ” ablakkal Mező: ÁTMÉRŐ Rendezés [ÁTMÉRŐ]*1,2 Módosítás Feltétel
MAGASSÁG
KOR
[MAGASSÁG]*1,1 [KOR]+5
MIDŐ [MIDŐ]+5*365
3/9A. Táblák felsorolása MÉRÉS a „Tábla SORSZ hozzáadása FAFAJ ” ablakkal Mező: SORSZ FAFAJ ÁTMÉRŐ MAGASSÁG KOR MIDŐ KÖRLAP TÉRF Rendezés Feltétel <=15 VAGY <1 3/9B. Az előző lekérdezés táblakészítővé alakítása, futtatása. 3/9C. Az előző lekérdezést törlővé alakítjuk (lekérdezés menü), futtatjuk, miután ellenőriztük, hogy a táblák listájában létrejött a KISFÁK tábla.(3/9B) 3/10. Táblák felsorolása KISFÁK a „Tábla SORSZ hozzáadása FAFAJ ” ablakkal Mező: SORSZ FAFAJ ÁTMÉRŐ MAGASSÁG KOR MIDŐ Rendezés Feltétel
-31-
KÖRLAP TÉRF
Az így elkészített választó lekérdezést alakítjuk HOZZÁFŰZŐ lekérdezéssé a lekérdezés menüből. Ennek párbeszédablakában lehet a listából kiválasztani, hogy melyik táblához (MÉRÉS) kívánjuk hozzátenni az előbb lekérdezésként elkészítettet. Amennyiben a mezőnevek nem egyeznek meg, a hozzáfűzés sorban meg kell adnunk a párosítást. A hozzáfűzendő tábla valamennyi mezőjéhez kell lennie befogadó oszlopnak. A fogadó tábla azon oszlopai 0 vagy üres értéket vesznek fel az új sorokban, amelyekhez nincs adat a hozzáillesztendőben.
3/11. HOZZÁFŰZŐ lekérdezés Táblák felsorolása a FAJ „Tábla FAFAJ hozzáadása ” ALAKTÉNYEZŐ ablakkal Mező: FAFAJ Rendezés Hozzáfűzés FAFAJ Feltétel
ALAKTÉNYEZŐ VÁGÁSÉRETTSÉGI_KOR ALAK
VÁGÁSKOR
A fogadó tábla nevét (FAJOK) az erre a célra szolgáló párbeszédablakban választjuk ki.
4. FELADAT 4/1. Az AUTÓK tábla tervező nézetében az adott mezőhöz tartozó Mezőtulajdonságok rublikáit kell kitölteni a konkrét adatok bevitele előtt. 4/2. Érvényességi szabály (az Évjárat mezőre!): >1985 AND [Évjárat]<2002 Az AUTÓK tábla adatlap nézetét használhatjuk a rekordok kitöltéséhez. 4/3. Táblák felsorolása „Tábla hozzáadása ablakkal Mező:
a ”
AUTÓK Típus Tulaj_tel
Rendezés
Összesítés Group by Feltétel A fenti választó lekérdezést alakítsuk táblakészítővé! Futtassuk, majd a létrejött TULAJDONOSOK táblát tervező nézetben egészítsük ki! -32-
4/4. Táblák felsorolása a AUTÓK „Tábla Típus hozzáadása ” ablakkal Mező: Üzemanyagár Rendezés Módosítás 153 Feltétel
DIESEL:RIGHT$([Típus];2)
=”-D”
4/5. Táblák felsorolása a AUTÓK „Tábla Típus hozzáadása ” ablakkal Mező: Előleg Rendezés Módosítás iif([Évjárat]<1990;[Ár]*0,5;[Ár]*0,6) Feltétel 4/6. Táblák felsorolása AUTÓK a „Tábla Típus hozzáadása Tulaj_tel ” ablakkal Mező Típus Évjárat
TULAJDONOSOK Név Tulaj_tel Megtett út
Ár
Lakhely
Rendezés Feltétel
„Sopron”
A tulajdonosok lakhelyét a TULADONOSOK tábla tartalmazza, az autók adatait az AUTÓK tábla. Az autók adatai közt a tulajdonos telefonszáma utal a tulajdonos másik táblabeli rekordjára. Az összekötő vonal biztosítja, hogy összetartozó sorok kerüljenek kilistázásra.
-33-
4/7A. Táblák felsorolása „Tábla hozzáadása ablakkal Mező: Rendezés Feltétel
a ”
AUTÓK Típus Típus
Évjárat Csökkenő
Ár
Évjárat Csökkenő
Ár
Megtett_út
4/7B. Táblák felsorolása „Tábla hozzáadása ablakkal Mező: Rendezés Összesítés: Feltétel
a ”
AUTÓK Típus
MIN
4/8. Táblák felsorolása „Tábla hozzáadása ablakkal Mező: Rendezés Feltétel VAGY
a ”
AUTÓK Típus Típus
Évjárat Ár
Megtett_út Kor:YEAR(NOW())-[Évjárat] <80000 <5
4/9A. Táblák felsorolása „Tábla hozzáadása ablakkal Mező: Rendezés Összesítés: Feltétel
a ”
AUTÓK Típus Ár MIN
-34-
4/9B. Táblák felsorolása AUTÓK a „Tábla Típus hozzáadása Évjárat ” ablakkal Mező Típus Évjárat
4/9A MINofÁr Megtett út
Ár
Előleg
Rendezés Feltétel
<[MINofÁr]*1,3
4/10. Táblák felsorolása a „Tábla hozzáadása ” Tulajdonosok ablakkal Név Mező: Rendezés Feltétel VAGY
Név
T:[Körzet]&[Tulaj_tel] „
Not like *KFT”
Ez a feladat csupán a szövegösszefűzés gyakorlására példa.
4/11. Táblák felsorolása a „Tábla hozzáadása ” AUTÓK ablakkal Típus Mező: Rendezés Összesítés: Kereszttábla: Feltétel
Évjárat
Ár
Útcsop:Int([Megtett_út]/100000)
Group by Sorfejléc
AVG Érték
Group by Oszlopfejléc
-35-
