Gyakorlati alapok III.

SQL (adatbázis)

 

SQL - több tábla feltöltése és lekérdezések

 

Emlékezzünk vissza: az előző fejezetekben létrehoztunk 1 auto adatbázist és benne 1 auto_csop táblát, amelyek segítségével alapszintű SQL-függvények, általában véve a mySQL-környezet működését vizsgálhattuk. Most már sejthetjük, hogy az auto adatbázis egy teljes, átfogó adatszerkezet egy autókereskedés és szervíz komplex, adatbázisos modellezésére. Az auto adatbázis a következő további komponenesekből áll, ezen komponenseket táblák és a bennük definiált attribútumok (oszlopok) reprezentálják:

Először létre kell hoznunk a táblákat, majd fel is kell azokat töltenünk értékekkel. Az alábbi SQL-alapú jegyzet egy az egyben megmutatja ezt a lehetőséget.


auto_csop tábla létrehozása

CREATE TABLE `AUTO`.`AUTO_CSOP` ( `auto_csop_nev` VARCHAR(6) NOT NULL , `km_dij` INT(4) NOT NULL , `napi_dij` INT(8) NOT NULL ) ENGINE = InnoDB;

auto_csop tábla feltöltése

insert into AUTO_CSOP values ('NORMAL','80','5000');
insert into AUTO_CSOP values ('EXTRA','120','7500');
insert into AUTO_CSOP values ('LUXUS','300','15000');
insert into AUTO_CSOP values ('BEYOND','500','20000');

tipusok tábla létrehozása

 

CREATE TABLE `AUTO`.`TIPUSOK` ( `tipus_nev` VARCHAR(15) NOT NULL , `auto_csop_nev` VARCHAR(6) NOT NULL , `leiras` VARCHAR(30) NOT NULL , `szerviz_km` INT(8) NOT NULL ) ENGINE = InnoDB

tipusok tábla feltöltése (a rekordokat egyszerre, egyetlen másolással  is feltölthetjük!)

insert into TIPUSOK values ('FORD ESCORT','EXTRA','CL 1.8 DSL 5-A','15000');
insert into TIPUSOK values ('RENAULT 19','EXTRA','RL 1.4 L KAT 3-A','5000');
insert into TIPUSOK values ('RENAULT NEVADA','EXTRA','RTD 2.1 L KAT 5-','75000');
insert into TIPUSOK values ('RENAULT LAGUNA','EXTRA','RT 2.0 L KAT 5-A','15000');
insert into TIPUSOK values ('SKODA LX','NORMAL','FAVORIT 1.3 L KAT','5000');
insert into TIPUSOK values ('SKODA CL','NORMAL','FAVORIT 1.3 L KAT COMFORT LINE','5000');
insert into TIPUSOK values ('OPEL ASTRA','EXTRA','CL 1.6 I 5-A','10000');
insert into TIPUSOK values ('OPEL VECTRA','LUXUS','TURBO 2.0 I 4-A','10000');
insert into TIPUSOK values ('W VENTO CL','EXTRA','TD 1.9 L KAT','15000');
insert into TIPUSOK values ('OPEL CORSA','NORMAL','CITY 1.2 I 3-A','10000');
insert into TIPUSOK values ('RENAULT TWINGO','NORMAL','1.2 L KAT 3-A','75000');
insert into TIPUSOK values ('RENAULT ESPACE','LUXUS','RN DT 2.1 L KAT','15000');
 

autok tábla létrehozása (az utasítást rendezhetjük függőlegesen is!)

 

CREATE TABLE `AUTO`.`AUTOK`

(

`rendszam` VARCHAR(7) NOT NULL ,

`tipus_nev` VARCHAR(15) NOT NULL ,

`auto_csop_nev` VARCHAR(6) NOT NULL ,

`vasarlas_datuma` DATE ,

`ar` FLOAT(8),

`futott_km` INT(8) NOT NULL ,

`ut_szerviz` INT(8) ,

`allapot` VARCHAR(1) NOT NULL ,

`reszleg_kod` INT(4) NOT NULL ,

`alk_kod` INT(8) NOT NULL

)

ENGINE = InnoDB

autok tábla feltöltése (a sorokat lekicsinyítettem, hogy beférjenek 1 sorba, ez az inzertálásra nincs hatással!)
 

insert into AUTOK values ('ABR-047','FORD ESCORT','EXTRA','19930102','1300000','151003','141410','F',30,1236);
insert into AUTOK values ('ABR-115','RENAULT 19','EXTRA','19930102','1200000','145097','139600','X',20,1234);
insert into AUTOK values ('CDE-324','RENAULT NEVADA','EXTRA','19930601','2473000','42040','15901','F',20,1234);
insert into AUTOK values ('DHV-123','RENAULT LAGUNA','EXTRA','19930605','2346000','73621','53600','A',20,1245);
insert into AUTOK values ('PHD-100','RENAULT 19','EXTRA','19930630','1329000','151441','140507','X',20,1249);
insert into AUTOK values ('PHD-610','RENAULT NEVADA','EXTRA','19930720','2473000','39066','30009','F',20,1249);
insert into AUTOK values ('ABC-021','SKODA LX','NORMAL','19931001','827000','123212','120509','F',30,1236);
insert into AUTOK values ('ABC-022','SKODA CL','NORMAL','19931001','917000','97081','95310','S',30,1236);
insert into AUTOK values ('ABC-123','OPEL ASTRA','EXTRA','19931111','1239000','101978','93710','A',30,1236);
insert into AUTOK values ('BAH-115','OPEL VECTRA','LUXUS','19931011','3977000','51007','49620','A',50,1246);
insert into AUTOK values ('CIC-312','W VENTO CL','EXTRA','19931201','1650000','100021','83701','F',60,1241);
insert into AUTOK values ('COD-705','W VENTO CL','EXTRA','19931201','1650000','91970','79600','S',60,1241);
insert into AUTOK values ('CBN-301','OPEL CORSA','NORMAL','19931201','917000','28001','25444','A',60,1241);
insert into AUTOK values ('CBN-302','OPEL ASTRA','EXTRA','19931201','1239000','87111','80001','S',60,1241);
insert into AUTOK values ('CDD-115','FORD ESCORT','EXTRA','19940102','1400000','15901','15501','A',30,1236);
insert into AUTOK values ('DCD-324','RENAULT LAGUNA','EXTRA','19940102','2346000','41061','28861','A',20,1245);
insert into AUTOK values ('PDP-101','RENAULT ESPACE','LUXUS','19940102','3530000','7911',NULL,'S',20,1249);
insert into AUTOK values ('CBN-001','W VENTO CL','EXTRA','19940102','1999000','152311','150600','F',60,1241);
insert into AUTOK values ('CDD-115','RENAULT TWINGO','NORMAL','19940103','955000','8307',NULL,'S',20,1234);
 

rendeles tábla létrehozása

 

CREATE TABLE `AUTO`.`RENDELES`

(

`rendeles_szam` VARCHAR(5) NOT NULL ,

`ugyfel_szam` VARCHAR(3) NOT NULL ,

`rendeles_datum` DATE ,

`rendelo_szemely` VARCHAR(12) NOT NULL ,

`kolcson_kezdete` DATE ,

`napok` INT(8) NOT NULL ,

`rendszam` VARCHAR(7) NOT NULL ,

`tipus_nev` INT(15) NOT NULL ,

`km_kezdet` INT(8) ,

`km_veg` INT(8) ,

`kolcson_dij` INT(8) ,

`fizetes` VARCHAR(1) NOT NULL

)

ENGINE = InnoDB

rendeles tábla feltöltése
 

insert into RENDELES values ('12334','348','19931201','NAGY TIBORNE','19940401','5','ABC-123','OPEL ASTRA','95308','101978','558600','Y');
insert into RENDELES values ('12335','342','19940202','HOTEL CIVIS','19940305','2','ABR-115','RENAULT 19','144102','145097','134400','Y');
insert into RENDELES values ('12336','342','19940202','HOTEL CIVIS','19940202','21','PHD-100','RENAULT 19','150871','151441','225900','Y');
insert into RENDELES values ('12337','344','19940330','HILTON','19940405','30','ABR-047','FORD ESCORT','141413','151003','1375800','N');
insert into RENDELES values ('12338','343','19940330','MATAV IG.','19940405','5','ABC-123','OPEL ASTRA','101978','104965','395940','N');
insert into RENDELES values ('12339','348','19940402','APEH IG.','19940410','1','BAH-115','OPEL VECTRA','50609','51007','55260','N');
insert into RENDELES values ('12340','345','19940203','HOTEL CIVIS','19940501','5','ABR-047','FORD ESCORT','151003',NULL,NULL,'Y');
insert into RENDELES values ('12341','349','19940404','IBUSZ IG.','19940501','10','ABC-123','W VENTO CL','152311',NULL,NULL,'Y');
insert into RENDELES values ('12342','344','19940404','HILTON','19940502','5','PHD-610','RENAULT NEVADA','39066',NULL,NULL,'Y');
insert into RENDELES values ('12343','348','19940504','NAGY TIBORNE','19940410','7','CDE-324','RENAULT NEVADA','42040',NULL,NULL,'N');
insert into RENDELES values ('12344','343','19940420','KOOS TIBOR','19940503','10','ABC-123','SKODA LX','123212',NULL,NULL,'N');
insert into RENDELES values ('12345','342','19940421','HILTON','19940503','3','CIC-312','W VENTO CL','100021',NULL,NULL,'Y');
 

ugyfelek tábla létrehozása

 

CREATE TABLE `AUTO`.`UGYFELEK`

(

`ugyfel_szam` VARCHAR(3) NOT NULL ,

`ugyfel_nev` VARCHAR(20) ,

`cim` VARCHAR(15) ,

`varos` VARCHAR(10) ,

`orszag` VARCHAR(10) ,

`iranyito_szam` VARCHAR(6) ,

`megbizott` VARCHAR(15) ,

`fizetesi_mod` VARCHAR(1)

)

ENGINE = InnoDB

ugyfelek tábla feltöltése


insert into UGYFELEK values ('342','MOLNÁR PETER','HILTON PRK. 1.','NEW YORK','USA','49928','HILTON','K');
insert into UGYFELEK values ('343','PAPP ROBERT','PIAC U. 10.','DEBRECEN','MAGYAR','4027','PINTER ATTILA','K');
insert into UGYFELEK values ('344','ABEL ANETT','WIESZ STR 5','BECS','AUSZTRIA','20800','HILTON','K');
insert into UGYFELEK values ('345','TIBOR SMITH','LIGNOUN 15.','PARIZS','FRANCIA','140010','HOTEL CIVIS','A');
insert into UGYFELEK values ('346','PAPP AGNES','VEZER U. 19.','DEBRECEN','MAGYAR','4032',NULL,'A');
insert into UGYFELEK values ('347','BIRO KATALIN','TESSEDIK U. 6.','DEBRECEN','MAGYAR','4032',NULL,'A');
insert into UGYFELEK values ('348','KISS VIKTÓRIA','FO U. 8.','BUDAPEST','MAGYAR','1221','NAGY TIBORNE','K');
insert into UGYFELEK values ('349','LIGETI ZOLTAN','KIRALY U. 88.','BUDAPEST','MAGYAR','1239',NULL,'A');
insert into UGYFELEK values ('350','TOTH KAROLYNE','DOMB U. 6.','BUDAPEST','MAGYAR','1260',NULL,'A');
 

reszleg tábla létrehozása

 

CREATE TABLE `AUTO`.`RESZLEG`

(

`reszleg_kod` INT(8) NOT NULL ,

`reszleg_nev` VARCHAR(20) ,

`reszleg_cim` VARCHAR(15)

)

ENGINE = InnoDB

reszleg tábla feltöltése
 

insert into RESZLEG values ('10','KOZPONT','BUDAPEST');
insert into RESZLEG values ('20','AUTO-RENAULT','BUDAPEST');
insert into RESZLEG values ('30','AUTO','GYOR');
insert into RESZLEG values ('50','LUXUS-AUTO','BUDAPEST');
insert into RESZLEG values ('60','AUTO','DEBRECEN');
insert into RESZLEG values ('70','TEHERAUTO','BUDAPEST');
 

alkalmazott tábla létrehozása

 

CREATE TABLE `AUTO`.`ALKALMAZOTT`

(

`alk_kod` INT(8) NOT NULL ,

`alk_nev` VARCHAR(20) ,

`beosztas` VARCHAR(16) ,

`belepes` DATE ,

`fizetes` INT(8) ,

`premium` INT(8) ,

`reszleg_kod` INT(8) NOT NULL

)

ENGINE = InnoDB

alkalmazott tábla feltöltése
 

insert into ALKALMAZOTT values ('1234','KOVACS','ELADO','19921210',13000,4000,'20');
insert into ALKALMAZOTT values ('1235','MOLNAR','SZERELO','19921218',18000, NULL,'30');
insert into ALKALMAZOTT values ('1236','CSIKOS','ELADO','19930312',13250,5000,'30');
insert into ALKALMAZOTT values ('1237','TOTH','VIZSGABIZTOS','19930422',20000, NULL,'20');
insert into ALKALMAZOTT values ('1238','NEMETH','TELEPHELYVEZETO','19931128',29000,14000,'30');
insert into ALKALMAZOTT values ('1239','SZABO','IGAZGATO','19920501',54000,75000,'10');
insert into ALKALMAZOTT values ('1240','BALOGH','SZERELO','19920602',22000,5000,'50');
insert into ALKALMAZOTT values ('1241','TRENCSENI','ELADO','19930620',12100,7200,'60');
insert into ALKALMAZOTT values ('1244','KIRALY','VIZSGABIZTOS','19930804',21000,8000,'20');
insert into ALKALMAZOTT values ('1245','HERCEG','ELADO','19931112',11000,6000,'20');
insert into ALKALMAZOTT values ('1246','BOGNAR','ELADO','19921008',13000,6000,'50');
insert into ALKALMAZOTT values ('1247','HALASZ','SZERELO','19930523',21000,7000,'60');
insert into ALKALMAZOTT values ('1248','HORVAT','TELEPHELYVEZETO','19931209',25000,12000,'60');
insert into ALKALMAZOTT values ('1249','KISS','ELADO','19931223',12500, 9500,'20');
 

Nézzük meg a phpMyAdmin-ban keletkezett táblákat és rekordjaikat:

 

auto_csop tábla

www.informatika-programozas.hu - SQL-műveletek

 

tipusok tábla

www.informatika-programozas.hu - SQL-műveletek

 

autok tábla

www.informatika-programozas.hu - SQL-műveletek

 

rendeles tábla

www.informatika-programozas.hu - SQL-műveletek

 

ugyfelek tábla

www.informatika-programozas.hu - SQL-műveletek

 

reszleg tábla

www.informatika-programozas.hu - SQL-műveletek

 

alkalmazott tábla

www.informatika-programozas.hu - SQL-műveletek

 

Kezdjük egyszerű lekérdezésekkel, amelynek alapelemeit az előző fejezetben ismertettünk!

 

www.informatika-programozas.hu - Ezt most meg kell tanulni!

 

Amint az a szöveges matematikai feladatoknál már lenni szokott, a szöveg megértése már fél siker!

 

Példaként vessünk egy pillantást az 1. feladatunkra:

 

1 - Irassa ki az AUTOK táblából az egyes autók típusát, a vásárlás idejét és a beszerzési árat!

 

A feladat megoldásához először nagyon alaposan át kell tanulmányoznunk az auto adatbázis összes táblájának attribútumát, sőt néha a rekordok egyedi jellegzetességeit is. Ezután próbáljuk az utasítást megfogalmazni SQL-nyelven, majd futtassuk a lekérdezést a phpMyAdmin SQL-fülén az Indítás gomb megnyomásával.

 

Az 1. feladat megoldása:

 

SELECT tipus_nev,vasarlas_datuma,ar FROM autok

 

Végeredmény:

 

www.informatika-programozas.hu - SQL-műveletek

 

Így persze a megoldás túl egyszerű volna, ezért az SQL-szöveget a továbbiakban elrejtem egy gomb mögé. A feldobott üzenetdobozból a helyes SQL-utasítás kimásolható lesz. Ellenőrizzük ezt le a 2 feladatban:

 

2 - Irassa ki SELECT utasítással az AUTOK táblából az összes autó rendszámát, típusát és árát! A kiírás az ár szerinti csökkenő sorrendben történjen!

3 - Irassa ki az AUTOK táblából az autók rendszámát és árát!

4 - Irassa ki az 1350000 Ft-nál drágábban vásárolt autók típusát és árát!

5 - Irassa ki az összes olyan autó rendszámát és típusát, amelyek nem az EXTRA autócsoporthoz tartoznak!

6 - Irassa ki azon autók adatait az AUTOK táblából, amelyek 9000 km nél többet futottak és áruk 1000000 Ft alatt van!

7 - Irassa ki az összes kifizetett rendelésre vonatkozó adatot!

8 - Irassa ki az 1994. március 1. előtti megrendelésekben szereplő adatokat!

9 - Irassa ki az 1994. március 1. után esedékes, nem kifizetett megrendelés adatokat!

10 - Irassa ki az összes olyan autó rendszámát, típusát és a futott kilométert, amelyek a 20-as részleghez tartoznak!

11 - Irassa ki az 1350000 Ft-nál drágábban vásárolt autók típusát és árát!

12 - Irassa ki az összes olyan autó adatait, amelyet 1994. január 1. előtt vásároltak!

13 - Irassa ki az ALKALMAZOTT táblából a munkatársak nevét, beosztását és fizetését, fizetés szerint csökkenő (növekvő) sorrendben!

14 - Irassa ki az ALKALMAZOTT tábla tartalmát beosztás szerint növekvő, ezen belül fizetés szerint csökkenő sorrendben!

15 - Irassa ki az 1350000 Ft-nál drágábban vásárolt autók típusát, árát és autócsoport nevét a csoport szerint növekvő, az ár szerint csökkenő sorrendben!

16 - Irassa ki az ALKALMAZOTT táblából az összes különböző beosztás nevét!

17 - Irassa ki az ELADÓ és SZERELŐ foglalkozású dolgozókat fizetés szerint csökkenő sorrendben!

18 - Irassa ki az ALKALMAZOTT táblából azokat, akiknek a fizetése nagyobb 50000-nél vagy a beosztásuk SZERELŐ a belépés dátuma szerinti sorrendben!

19 - Irassa ki azokat, akiknek a fizetése 60000-nél kisebb vagy 100000-nél nagyobb!

20 - Irassa ki az ALKALMAZOTT táblából azokat a dolgozókat, akik a 20-as vagy 50-es kódú részlegben dolgoznak!

21 - Irassa ki a 30-nál nagyobb kódú munkahelyen dolgozók adatait!

22 - Irassa ki a vidéki (nem budapesti) részlegek adatait!

23 - Irassa ki városnév szerint rendezve az ügyfelek és városuk nevét!

24 - Irassa ki azon ügyfelek adatait, akik készpénzzel (átutalással) fizetnek!

25 - Irassa ki azon rendelések adatait, amelyeknél a kölcsönzési idő 1 hétnél hosszabb!

26 - Irassa ki azon rendelésekre vonatkozó adatokat, amelyeket már kifizettek!

27 - Irassa ki az AUTO_CSOP tábla tartalmát kilométer díj szerint csökkenő sorrendben!

28 - Irassa ki az egyes autótípusoknál a tipus név mellé a kötelező karbantartás (szervízbevitel) kilométer intervallumát! A kiírás a típus neve szerint legyen rendezve!

29 - Irassa ki azon autók adatait, amelyek 100000 kilométernél többet futottak!

30 - Készítsen listát a kiadható autók adatairól!

31 - Készítsen listát azokról az autókról, amelyek szervízben vannak, vagy karbantartásra várnak és ezért nem adhatók ki kölcsönzésre!

32 - Készítsen név szerint rendezett listát az ügyfelekről tárolt adatokról!

33 - Irassa ki a LUXUS autócsoporthoz tartozó autók rendszámát és típusát!

34 - Irassa ki azokat az autó típusokat, melyeket 15000 kilométerenként kell szervízbe vinni!

35 - Készítsen listát azokról a városokról, ahol az autókölcsönzőnek részlege van!

36 - Irassa ki a debreceni ügyfelek adatait!

37 - Irassa ki a rendeléseknél kapcsolatot tartó személyek nevét névsor szerint rendezve!