SQL gyakorló feladatok 6. Adatbázis gyakorlat 2011. április 5.
SQL alapparancsai • • -
DDL: create: táblák létrehozása alter: táblák (séma) módosítása drop: táblák törlése DML: select: adatok lekérdezése insert: adatok bevitele update: adatok felülírása, frissítése delete: adatok (sorok) törlése táblákból
Egyszerű adatbázis létrehozása Csak egy tábla van: Hallgatok, 4 attribútummal CREATE table Hallgatok ( nev VARCHAR2(25) PRIMARY KEY, szul NUMBER not null, lakhely VARCHAR2(20) not null, jegy NUMBER ); vagy
Primary key CREATE table Hallgatok ( nev VARCHAR2(25), szul NUMBER not null, lakhely VARCHAR2(20) not null, jegy NUMBER, primary key(nev) );
Primary key CREATE table Hallgatok ( nev VARCHAR2(25), szul NUMBER not null, lakhely VARCHAR2(20) not null, jegy NUMBER ); alter table Hallgatok add constraint pk_Hallgatok primary key(nev);
Foreign key CREATE table Catalog ( sid NUMBER not null, pid NUMBER not null, cost NUMBER not null primary key(pid,sid) foreign key pid references Parts(pid) ); vagy
Foreign key CREATE table Catalog ( sid NUMBER not null, pid NUMBER not null, cost NUMBER not null primary key(pid,sid) ); alter table Catalog add constraint fk_Catalog foreign key(pid) references Parts(pid);
Töltsük fel az adatbázist! Adatok bevitele: INSERT INTO INSERT INTO Hallgatok(nev,szul,lakhely,jegy) VALUES(„Thomas Mann‟,1973,‟Siofok‟,5);
vagy INSERT INTO Hallgatok VALUES(„Thomas Mann‟,1973,‟Siofok‟,5);
Kérdezzünk le adatokat!
Lekérdezés: SELECT…FROM…WHERE…
SELECT alapstruktúrája SELECT oszloplista (amit látni akarunk) FROM táblalista (amiből lekérdezni akarunk) WHERE log. kif. (a “szelekció” művelete) GROUP BY csoportosítókif. (projekció oszlopa szerint csoportosít) HAVING log. kif. (aggregált kifejezéseknél, ahol WHERE nincs) ORDER BY log. kif. (alfabetikus rendezés)
Egyszerű lekérdezések SELECT * FROM Hallgatok;
mindent kilistáz
Szelekció: SELECT * FROM Hallgatok WHERE szul=1973 AND lakhely=„Siofok‟; Projekció: SELECT nev,jegy FROM Hallgatok; Átnevezés: SELECT lakhely AS varos FROM Hallgatok; Descartes szorzat: ha két táblánk van pl. SELECT * FROM Hallgatok,Hobbik;
Aggregátor függvények • avg(), first(), last(), max(), min(), sum(), ucase(), lcase(),… stb. • Példák: SELECT avg(jegy) FROM Hallgatok; – jegyek átlaga a táblázatban SELECT count(*) FROM Hallgatok; – összes hallgatók létszáma SELECT min(jegy) FROM Hallgatok; – legrosszabb jegy
Csoportosító függvények (GROUP BY) Milyen életkorban milyen a tanulmányi átlag?
SELECT szul, avg(jegy) FROM Hallgatok GROUP BY szul; Azok a települések, ahonnan 3-nál többen jöttek:
SELECT lakhely, count(*) AS db FROM Hallgatok GROUP BY lakhely HAVING count(*) > 3;
ORDER BY A hallgató lakhelye és jegye a jegyek szerinti növekvő [csökkenő] sorrendben:
SELECT lakhely, jegy FROM Hallgatok ORDER BY jegy; Csökkenő: SELECT lakhely, jegy FROM Hallgatok ORDER BY jegy DESC;
LIKE használata SELECT Név FROM Hallgatók WHERE Név LIKE '%János%';
like '%János%' <- bárhol like 'János%' <- elején like '%J_nos' <- végén A nem egyenlő: NOT LIKE
Metszet, kivonás, unió
(QUERY1) MINUS (QUERY2); (QUERY1) INTERSECT (QUERY2); (QUERY1) UNION (QUERY2);
Adatbázis letöltése Cím: http://digitus.itk.ppke.hu/~fulta/oracle load_gyak.sql unload_gyak.sql
Bemásolni valamelyik digitusos könyvtárotokba. Futtatás SQL-ben (sqlplus): @/home/…/load_gyak.sql illetve start /home/…/unload_gyak.sql
Adatbázis sémája Suppliers(sid int, sname str, adress str) Parts(pid int, pname str, color str) Catalog(sid int, pid int, cost real)
Egyszerűbb lekérdezések – 1 • Listázzuk ki a SUPPLIERS tábla tartalmát!
SELECT * FROM suppliers; • Listázzuk ki a PARTS tábla tartalmát!
SELECT * FROM parts; • Listázzuk ki a Piros alkatrészeket tartalmazó sorokat! σ(Color = Piros) (Parts)
SELECT * FROM Parts WHERE color='piros';
Egyszerűbb lekérdezések – 2 • Keressük a Sárga alkatrészeket azonosítóit! π(Pid) (σ(Color = Sárga) (Parts)) SELECT PID FROM Parts WHERE color='s_rga'; • Keressük az 's' betüt tartalmazó színű alkatrészek nevét! SELECT PNAME FROM PNAME WHERE color LIKE '%s%';
Egyszerűbb lekérdezések – 3 • Szükségünk van azoknak alkatrészeknek az áraira amit a 2 -es azonosítójú gyártó gyárt! π(Cost) (σ(Sid = 2) (Catalog)) SELECT cost FROM catalog WHERE sid=2; • Milyen azonosítójú gyár készít piros alkatrészt? π(Sid) (σ(Color = 'Piros') (Parts >
Egyszerűbb lekérdezések – 4 • Három db termék nevét nézzünk (mindegy melyik) amit 2 -es azonosítójú gyártó készít! SELECT PNAME FROM CATALOG c, PARTS p WHERE c.PID=p.PID AND s.SID=2 LIMIT 3; -- Limit 3 maximum három sort ad vissza - MySQL
• Kik gyártanak 'Markolo fogantyu' -t? π(Sid) (σ(Pname = 'Markolo fogantyu') (Parts >< Catalog >< Supplier)) SELECT SID, SNAME FROM CATALOG c, Parts p, SUPPLIER s WHERE c.PID=p.PID AND c.SID=s.SID AND p.PNAME='Markolo fogantyu';
1. Listázza ki a piros cikkeket forgalmazó szolgáltatókat! SELECT sname FROM SUPPLIERS s, CATALOG c, PARTS p WHERE s.sid=c.sid and c.pid=p.pid and p.color=„piros‟; Result: 1. Egyesult Szolgaltatok Szovetsege 2. Markolo alkatreszek 3. Mezoalkat Resz Kft
2. Listázza ki a piros vagy zöld cikkek forgalmazóit! SQL: SELECT (DISTINCT, UNIQUE) sname FROM SUPPLIERS s, CATALOG c, PARTS p WHERE s.sid=c.sid AND c.pid=p.pid AND ( p.color=„piros' OR p.color=„zold' ) ; Result: Egyesult Szolgaltatok Szovetsege (Egyesult Szolgaltatok Szovetsege) KicsiMisi Hovamesz Bt Markolo alkatreszek Mezoalkat Resz Kft Was machst du den da Ku Wu Hijasti Chui
3. Listázza ki a piros és sárga cikkeket is forgalmazókat! SELECT sname FROM SUPPLIERS s WHERE sid IN ( ( SELECT c.sid FROM CATALOG c, PARTS p WHERE c.pid=p.pid AND p.color='piros' ) INTERSECT ( SELECT c.sid FROM CATALOG c, PARTS p WHERE c.pid=p.pid AND p.color='sarga' ) ); Result: Egyesult Szolgaltatok Szovetsege Markolo alkatreszek Mezoalkat Resz Kft
4 .Listázza ki a kék cikk forgalmazókat a forgalmazott kék cikkek számával! SELECT sname, COUNT(p.pid) FROM SUPPLIERS s, PARTS p, CATALOG c WHERE s.sid=c.sid AND p.pid=c.pid AND p.color='kek' GROUP BY s.sname; Result: Egyesult Szolgaltatok Szovetsege 2 KicsiMisi Hovamesz Bt 2 Ku Wu Hijasti Chui 2 Was machst du den da 2
5. Listázza ki a kék cikk forgalmazókat a kék cikkek árának összegének sorrendjében! SELECT sname, sum(c.cost) as arakosszege FROM SUPPLIERS s, PARTS p, CATALOG c WHERE s.sid=c.sid AND p.pid=c.pid AND p.color='kek' GROUP BY s.sname ORDER BY arakosszege DESC; Result: Was machst du den da 32099 Egyesult Szolgaltatok Szovetsege 6000 KicsiMisi Hovamesz Bt 4868 Ku Wu Hijasti Chui 798
6. Listázza ki a szolgáltatókat a forgalmazott cikkek átlagos árának függvényében! SELECT sname, avg(c.cost) as atlagosar FROM SUPPLIERS s, PARTS p, CATALOG c WHERE s.sid=c.sid AND p.pid=c.pid GROUP BY s.sname ORDER BY atlagosar DESC; Result: Was machst du den da 15241,1818181818 Markolo alkatreszek 10280 Mezoalkat Resz Kft 6513,85714285714 Egyesult Szolgaltatok Szovetsege 5323,33333333333 KicsiMisi Hovamesz Bt 3174 Paprika Janos 2349,33333333333 Ku Wu Hijasti Chui 486,818181818182
7. Listázza ki hol és kinél lehet ‘Markolo fogantyu’-t vásárolni, elsőként azt a helyet ahol a legolcsóbban megkaphatjuk! SELECT s.*, c.cost, p.* FROM suppliers s, catalog c, parts p WHERE s.sid=c.sid and c.pid=p.pid and p.pname='Markolo fogantyu' ORDER BY c.cost (ASC);
8. Listázza ki a ‘cipo’ forgalmazókat és a forgalmazott cipők számát, aszerint sorrendezve, hogy ki árul a legtöbbet! SELECT sname, count(*) AS arultcipokszama FROM suppliers s, catalog c, parts p WHERE s.sid=c.sid AND c.pid=p.pid AND p.pname like('%cipo%') GROUP BY sname order by arultcipokszama; Result: Egyesult Szolgaltatok Szovetsege 4 KicsiMisi Hovamesz Bt 4 Ku Wu Hijasti Chui 4 Was machst du den da 4
9. Listázza ki a ‘cipo’ forgalmazókat és a forgalmazott cipők átlagos árát, aszerint, hogy ki árulja átlagosan a legolcsóbban őket! SELECT sname, AVG(c.cost) AS atlagosar FROM suppliers s, catalog c, parts p WHERE s.sid=c.sid and c.pid=p.pid AND p.pname like('%cipo%') GROUP BY sname ORDER BY atlagosar asc; Result: Ku Wu Hijasti Chui 489,5 KicsiMisi Hovamesz Bt 4524 Egyesult Szolgaltatok Szovetsege 5500 Was machst du den da 27475
10.’Puspokladanyban’-ban mennyibe kerül a legolcsóbb papucs? Megkeressük a legolcsóbb papucs árát, majd kiválasztjuk az ehhez az árhoz tartozó adatokat.. SELECT sname, adress ,pname, cost FROM parts p,catalog c,suppliers s WHERE s.sid=c.sid AND c.pid=p.pid and p.pname='Papucs' AND s.adress like '%Puspokladany%' AND c.cost in ( SELECT min( c.cost) FROM suppliers s, catalog c, parts p WHERE s.sid=c.sid and c.pid=p.pid AND p.pname='Papucs' AND s.adress like '%Puspokladany%' );
11. Listázza ki az összes cikk forgalmazóit! SELECT sname FROM suppliers s WHERE not exists ( (SELECT pid FROM parts ) MINUS (SELECT p.pid FROM parts p, catalog c WHERE c.pid=p.pid AND c.sid=s.sid) );
