Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Marosvásárhely
ABR ( Adatbázisrendszerek) 12. Előadás: 0. Egyes érdekesebb lekérdezésekről 1. NULL értékek használata alkérdésekben 2. Számlanyilvántartási feladat megoldása
Illyés László 1
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) 2
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda e) Keressük meg a legolcsóbb színes nyomtatók gyártóit?
e1 SELECT gyarto FROM Termek WHERE modell IN
(SELECT modell FROM Nyomtato WHERE szines AND ar=(SELECT MIN(ar) FROM Nyomtato));
Három variáns Melyik a jó
e2 SELECT gyarto FROM Termek WHERE modell IN
És miért
(SELECT modell FROM Nyomtato WHERE ar=(SELECT MIN(ar) FROM Nyomtato WHERE szines));
e3 SELECT gyarto FROM Termek WHERE modell IN
(SELECT modell FROM Nyomtato WHERE szines AND ar=(SELECT MIN(ar) FROM Nyomtato WHERE szines)); 3
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda f) Keressük meg annak a PC-nek 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, sebesseg FROM Termek INNER JOIN PC ON Termek.modell=PC.modell WHERE sebesseg IN (SELECT sebesseg FROM PC WHERE memoria=(SELECT MIN(memoria) FROM PC)); SELECT gyarto FROM Q2 WHERE sebesseg =(SELECT MAX(sebesseg) FROM Q2)
4
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 5 Termek.modell=laptop.modell GROUP BY (gyarto);
NULL érték használata alkérdésekben (Celko J., SQL felsőfokon, Kiskapu, 117-119) CREATE TABLE table1(KOD INTEGER PRIMARY KEY, ertek INTEGER); CREATE TABLE table2(KOD INTEGER PRIMARY KEY, ertek INTEGER); INSERT INTO table1 VALUES (1,1),(2,2); INSERT INTO table2 VALUES (1,1),(2,2),(3,3),(4,4),(5,5); SELECT ertek FROM table2 WHERE ertek NOT IN (SELECT ertek FROM table1); Eredmény: 3,4,5 INSERT INTO table1 VALUES (6,null); Eredmény: NULL ugyanazzal a lekérdezéssel Egy megoldás: SELECT ertek FROM table2 WHERE ertek NOT IN (SELECT ertek FROM table1 WHERE ertek IS6 NOT null);
Az IN predikátumot a következő képpen határozzuk meg: SELECT ertek FROM table2 WHERE NOT (ertek= ANY(SELECT ertek FROM table1)); Ebből következik, hogy: SELECT ertek FROM table2 WHERE NOT((ertek=1) OR (ertek=2) OR (ertek=3) OR (ertek=4) OR (ertek=5) OR (ertek=NULL)); DeMorgan azonosságokat alkalmazva SELECT ertek FROM table2 WHERE((ertek<>1) AND (ertek<>2) AND (ertek<>3) AND (ertek<>4) AND (ertek<>5) AND UNKNOWN) Eredmény mindíg UNKNOWN lesz, ami a WHERE záradékban nem lehet. 7
NULL érték kiküszöbölése a gyakorlatban 0 ismeretlen 1 férfi 2 nő 9 N/A
8
hossz
cím
szerep
év
szalagFajta
Filmek Hangok
azegy
azegy fegyver
Rajzfilmek
Bűnűgyi filmek
azegy
azegy
Bűnűgyi Rajzfilmek 9
Film(cím, év, hossz, szalagfajta) BűnűgyiFilm(cím, év, hossz, szalagfajta, fegyver) Rajzfilm(cím, év, hossz, szalagfajta) Hang(filmCím, gyártÉv, színészNév, szerep) BűnűgyiRajzfilm(cím, év, hossz, szalagfajta, fegyver)
Egyedhalmazok összevonása nullérték használatával Film(cím, év, hossz, szalagfajta, fegyver, hang) Hang(filmCím, gyártÉv, színészNév, szerep)
10
Sapientia - Erdélyi Magyar Tudományegyetem (EMTE) Marosvásárhely Egy lehetséges megkülönböztetés NULL érték használatával Hang
Fegyver
NULL -1 NULL
Filmtipús “”
NULL -1 Érték 0
0 NULL
0
0 Érték
1
1 NULL
1
1 Érték
Film Bűnűgyi film
“”
Néma rajzfilm Néma bűnűgyi rajzfilm
“”
Hangos rajzfilm Hangos bűnűgyi rajzfilm 11
Feladat: Egy cégnél adatbázisban szeretnénk tárolni a kimenő és bejövő számlák információit. Szeretnénk tudni, hogy mindenik számlán milyen terméket adtunk el, hogy a kimenő számlák melyik cégnek voltak kibocsájtva, a cégek milyen kifizetéseket eszközöltek ezen számlákra és hogy mennyivel tartoznak, vagy késésben vannak-e a megadott határidő szerint. A határidő a számlához tartozik, nem a céghez. Ugyanakkor nyilvántartjuk a bejövő számláinkat is, de csak összeg szerint és a saját kifizetéseink szerint. Az ügyfeleink, a beszállítók és a vevők egy táblában tárolódnak. A mi kifizetéseinket is nyilvántartjuk. Egy számlára történhetnek részleges kifizetések is. 12
Zölddel írva a számla általános adatait láthatjuk Pirossal a számlasorok attribútumai vannak Kékkel írva láthatóak a számlasorok. 13
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Marosvásárhely
számlaszám
ÁFA
határidő
sorai
Számlák Dátum
Sorszám Számlasorok egységár
számlája
azonosító
terméke Ügyfelek
név
mennyiség
bank
termékID
név
bszámla Termékek
adószám
mértékegység
bejegyzés 14
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Marosvásárhely Az ábrához tartozó relációk: Ugyfel(azonosito, nev, adoszam, cim, bejegyzes, bank, bszamla) Termek(termekID, nev, mertekegyseg) Szamla(szamlaszam, AFA, datum, hatarido, klienskod) Szamlasor(szamlaszam, sorszam, egysegar, mennyiseg, termekID) Mivel a Szamla a bejövő és kimenő számlákat is jelenti, még egy attribútumot adunk hozzá: összeg, amelyik az össz-számlaösszeget jelenti. Ez a kimenő számlák esetében lehet NULL, ezzel meg tudjuk különböztetni a bejövő és kimenő számlákat egymástól. Szamla(szamlaszam, AFA, datum, hatarido, klienskod, osszeg)
15
A megvalósítás ACCESS-ban
16
számlaszám
ÁFA
határidő Számlák Dátum
dokSzam
Sorszám Kifizetései
Kifizetések összeg
Dátum tipús
A számlák kifizetései ugyancsak hasonló a számlasorok struktúrához, mert a kifizetések is egy számlára vonatkoznak. Ezért gyenge egyedhalmaz. Kifizetes(szamlaszam, sorszam, dokszam, Datum, tipus, összeg) A tipus lehet pl. {OP, cash} vagy más (kompenzáció) stb. Ebben nyilvantarthatjuk a mi kifizeteseinket és a nekünk fizetett összegeket is. A különbséget az adja meg, hogy milyen SZÁMLÁHOZ tartozik, a bejövőhöz-e, vagy a kimenőhöz. 17
A kifizetések nyilvántartásával kiegészült ACCESS megvalósítás 18
A kimenő és bejövő számláknak nyilvántartása jobb, ha külön történik: A kimenő számláknál az összeg attribútumot kivettük, mert kiszámítható a számlasorokból. A kifizetéseket is külön vettük.
A beviteli sorrend a következő: 1.
Termek, Ugyfel
2.
BeSzamla, KiSzamla
3.
Szamlasor, bejovoPenz, Kifizetés
19
Ugyanaz OpenBaseban
20
CREATE TABLE UGYFEL( azonosito integer PRIMARY KEY, nev VARCHAR(40), cim VARCHAR(40), ADOSZAM VARCHAR(20), BEJEGYZES VARCHAR(20), BANK VARCHAR(50), BANKSZAMLA VARCHAR(40) ); CREATE TABLE TERMEK( TERMEKID INTEGER PRIMARY KEY, NEV VARCHAR(50), MERTEKEGYSEG VARCHAR(30) ); 21
CREATE TABLE BESZAMLA( SZAMLASZAM INTEGER PRIMARY KEY, AFA INTEGER, DATUM DATE, HATARIDO DATE, UGYFELID INTEGER, OSSZEG NUMERIC(16,2), FOREIGN KEY (UGYFELID) REFERENCES UGYFEL(AZONOSITO) );
22
CREATE TABLE KISZAMLA( SZAMLASZAM INTEGER PRIMARY KEY, AFA INTEGER, DATUM DATE, HATARIDO DATE, UGYFELID INTEGER, FOREIGN KEY (UGYFELID) REFERENCES UGYFEL(AZONOSITO) ); CREATE TABLE KIFIZETES( SZAMLASZAM INTEGER, SORSZAM INTEGER, DATUM DATE, OSSZEG NUMERIC(16,2), TIPUS VARCHAR(30), PRIMARY KEY(SZAMLASZAM,SORSZAM), FOREIGN KEY (SZAMLASZAM) REFERENCES BESZAMLA(SZAMLASZAM) ); 23
CREATE TABLE BEJOVOPENZ( SZAMLASZAM INTEGER, SORSZAM INTEGER, DATUM DATE, OSSZEG NUMERIC(16,2), TIPUS VARCHAR(30), PRIMARY KEY(SZAMLASZAM,SORSZAM), FOREIGN KEY (SZAMLASZAM) REFERENCES KISZAMLA(SZAMLASZAM) ); CREATE TABLE SZAMLASOR( SZAMLASZAM INTEGER, SORSZAM INTEGER, EGYSEGAR NUMERIC(16,2), MENNYISEG NUMERIC(16,2), TERMEKID INTEGER, PRIMARY KEY(SZAMLASZAM,SORSZAM), FOREIGN KEY (SZAMLASZAM) REFERENCES KISZAMLA(SZAMLASZAM), FOREIGN KEY (TERMEKID) REFERENCES TERMEK(TERMEKID) ); 24
Beviteli sorrend és függőség Ügyfél
1
Termék
1
1
BeSzámla
KiSzámla
1
1
Kifizetés
BejövőPénz
1
Számlasor
25
1. Melyik ügyfélnek a neve kezdődik ‘A’ betűvel? SELECT Ugyfel.nev FROM Ugyfel WHERE Ugyfel.nev LIKE ‘A%’; 2. Melyik ügyfél nevében van benne az ‘ama’ betűkombináció? SELECT Ugyfel.nev FROM Ugyfel WHERE Ugyfel.nev LIKE ‘%ama%’ % mindent helyettesít (mint Windowsban a *) _ helyettesít egy karaktert (mint Windowsban a ?) 26
Melyik ügyfélhez nem tartozik számla SELECT Ugyfel.nev FROM Ugyfel WHERE Ugyfel.azonosito Not In (SELECT ugyfelID FROM KiSzamla) And (Ugyfel.azonosito) Not In (SELECT ugyfelID FROM BeSzamla); Melyik számlához nem tartozik számlasor (ez hiba, vagy még nem vittük be) SELECT szamlaszam FROM KiSzamla WHERE szamlaszam NOT IN (SELECT szamlaszam FROM Szamlasor); Kik a beszállítók (akikhez bejövő számla tartozik) SELECT DISTINCT Ugyfel.nev FROM Ugyfel INNER JOIN BeSzamla ON 27 Ugyfel.azonosito=BeSzamla.ugyfelID;
Melyik ügyfelek a kliensek (akikhez kimeno szamla tartozik (nem ures)) SELECT DISTINCT Ugyfel.nev FROM Ugyfel INNER JOIN KiSzamla ON Ugyfel.azonosito = KiSzamla.ugyfelID WHERE KiSzamla.szamlaszam IN (SELECT szamlaszam FROM Szamlasor); Melyek azok az ügyfelek, akik egyben beszállítók és vásárlók is SELECT DISTINCT Ugyfel.nev FROM Ugyfel INNER JOIN KiSzamla ON Ugyfel.azonosito=KiSzamla.ugyfelID WHERE szamlaszam IN (SELECT szamlaszam FROM Szamlasor) AND azonosito IN (SELECT ugyfelID FROM BeSzamla); 28
SELECT KiSzamla.szamlaszam, Szamlasor.sorszam, Termek.nev, Termek.mertekegyseg, Szamlasor.egysegar, Szamlasor.darabszam, (egysegar)*(mennyiseg) AS sorossz FROM Termek INNER JOIN (KiSzamla INNER JOIN Szamlasor ON KiSzamla.szamlaszam = Szamlasor.szamlaszam) ON Termek.termekID = Szamlasor.termekID; Számlák a soraikkal együtt. 29
SELECT KiSzamla.szamlaszam, Szamlasor.sorszam, Termek.nev, Termek.mertekegyseg, Szamlasor.egysegar, Szamlasor.darabszam, (egysegar)*(mennyiseg) AS sorossz FROM Termek INNER JOIN (KiSzamla INNER JOIN Szamlasor ON KiSzamla.szamlaszam = Szamlasor.szamlaszam) ON Termek.termekID = Szamlasor.termekID WHERE (((KiSzamla.szamlaszam)=[Hanyas szamla?])); Ez a kiegészítés ACCESSben paraméterezett lekérdezést ad. A felhasználótól 30 bekéri a számlaszámot, s csak azt az egy számlát adja meg.
SELECT Ugyfel.nev, KiSzamla.szamlaszam, Sum((egysegar)*(mennyiseg)*(1+KiSzamla.AFA/100)) AS osszSzamlaErtek, (Datum)+(hatarido) AS hatardatum FROM Ugyfel INNER JOIN (KiSzamla INNER JOIN Szamlasor ON KiSzamla.szamlaszam=Szamlasor.szamlaszam) ON Ugyfel.azonosito=KiSzamla.ugyfelID GROUP BY Ugyfel.nev, KiSzamla.szamlaszam, (Datum)+(hatarido); Megadja minden ügyfélhezt tartozó minden számla összértékét és fizetési 31 határidejét.
32
Az előző lekérdezés részleges eredménye (jelentés formájában).
Bejövő számláink az ügyfelekkel, összegekkel és határidőkkel SELECT Ugyfel.nev, BeSzamla.szamlaszam, BeSzamla.osszeg, (Datum)+ (hatarido) AS hatardatum FROM Ugyfel INNER JOIN BeSzamla ON Ugyfel.azonosito=BeSzamla.ugyfelID; Az ügyfelek által vásárolt termékek, mennyiségre:
33
Ha az ügyfeleink által vásárolt termékekről szeretnénk információt fel kell vennünk a lekérdezésbe az összes táblát, amelyen keresztül a kapcsolat megoldható : SELECT Ugyfel.nev, Termek.nev, Sum(Szamlasor.darabszam) AS SumOfdarabszam FROM Ugyfel INNER JOIN (Termek INNER JOIN (KiSzamla INNER JOIN Szamlasor ON KiSzamla.szamlaszam = Szamlasor.szamlaszam) ON Termek.termekID = Szamlasor.termekID) ON Ugyfel.azonosito = KiSzamla.ugyfelID GROUP BY Ugyfel.nev, Termek.nev;
Az összekötések zárójelezéssel történnek, amint fentebb a színes kiemelések vannak.
34
Teljesen kifizetetlen számlák SELECT Ugyfel.nev, KiSzamla.szamlaszam, Sum((egysegar)*(mennyiseg)*KiSzamla.AFA/100) AS osszSzamlaErtek FROM Ugyfel INNER JOIN (KiSzamla INNER JOIN Szamlasor ON KiSzamla.szamlaszam=Szamlasor.szamlaszam) ON Ugyfel.azonosito=KiSzamla.ugyfelID WHERE KiSzamla.szamlaszam NOT IN (SELECT bejovoPenz.szamlaszam FROM bejovoPenz) GROUP BY Ugyfel.nev, KiSzamla.szamlaszam; Az alkérdés megadja azokat a számlákat, amelyekre kifizetések történtek. Ezeknek a kivonása eredményezi azon számlákat és összegeket, amelyek teljesen kifizetetlenek voltak. 35
Meg szeretnénk tudni, hogyan állnak a részlegesen fizetett számláink is. Reszlegesen fizetett számlák és a rajtuk levő összeg SELECT Ugyfel.nev, KiSzamla.szamlaszam, Sum((egysegar)*(mennyiseg)*KiSzamla.AFA/100) AS osszSzamlaErtek FROM Ugyfel INNER JOIN (KiSzamla INNER JOIN Szamlasor ON KiSzamla.szamlaszam=Szamlasor.szamlaszam) ON Ugyfel.azonosito=KiSzamla.ugyfelID WHERE (((KiSzamla.szamlaszam) In (SELECT bejovoPenz.szamlaszam FROM bejovoPenz))) GROUP BY Ugyfel.nev, KiSzamla.szamlaszam; Minden számlára az összkifizetés SELECT bejovoPenz.szamlaszam, Sum(bejovoPenz.osszeg) AS SumOfosszeg FROM bejovoPenz GROUP BY bejovoPenz.szamlaszam; 36
A két lekérdezést kombinálva kapjuk meg a tényleges állást SELECT ReszlegesenFizetettSzamlak.nev, ReszlegesenFizetettSzamlak.szamlaszam, ReszlegesenFizetettSzamlak.osszSzamlaErtek, SzamlaraFizetes.SumOfosszeg, (osszSzamlaErtek)-(SumOfosszeg) AS kulonbseg FROM ReszlegesenFizetettSzamlak INNER JOIN SzamlaraFizetes ON ReszlegesenFizetettSzamlak.szamlaszam = SzamlaraFizetes.szamlaszam;
37
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Marosvásárhely Csak a kifizetetlen számlák, s rajtuk levő összegek (SELECT * FROM TeljesenKifizetetlenSzamlak) UNION (SELECT nev,szamlaszam,kulonbseg FROM ReszlegesAllas WHERE kulonbseg<>0); Kik vannak késésben a számla-kifizetéssel SELECT KiSzamla.szamlaszam, CsakTartozasok.osszSzamlaErtek, (Datum)+(hatarido)AS Expr1 FROM KiSzamla INNER JOIN CsakTartozasok ON KiSzamla.szamlaszam=CsakTartozasok.szamlaszam WHERE Datum+hatarido>Date(); 38
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Marosvásárhely Kik fizettek egyáltalán késve SELECT Ugyfel.nev, KiSzamla.szamlaszam, bejovoPenz.datumKiSzamla.datum-[hatarido] AS kesesnap, bejovoPenz.osszeg FROM Ugyfel INNER JOIN (KiSzamla INNER JOIN bejovoPenz ON KiSzamla.szamlaszam=bejovoPenz.szamlaszam) ON Ugyfel.azonosito=KiSzamla.ugyfelID WHERE (((bejovoPenz.datum-KiSzamla.datum-(hatarido))>0));
39
