[[oktatas:adatbázis-kezelés|< Adatbázis-kezelés]]

====== Feladatok és megoldások ======

  * **Szerző:** Sallai András
  * Copyright (c) 2019, Sallai András
  * Szerkesztve: 2019, 2021
  * Licenc: [[https://creativecommons.org/licenses/by-sa/4.0/|CC BY-SA 4.0]]
  * Web: https://szit.hu

===== Egytáblás lekérdezések =====

==== Feladat 001 ====

=== Adatok ===

  * Adatbázis: tanker

<code mysql>
create database tanker
character set utf8
collate utf8_hungarian_ci;

use tanker;

create table szemelyek (
    az int not null primary key auto_increment,
    nev varchar(50),
    telepules varchar(50),
    cim varchar(50),
    belepes date
);

insert into szemelyek
(nev, telepules, cim, belepes)
values
('Nagy József', 'Szolnok', 'Nyár u. 3.', '2010-03-17'),
('Pár Elek', 'Debrecen', 'Tél u. 3.', '2005-01-01'),
('Tér Ferenc', 'Szolnok', 'Kossuth u. 5.', '2000-02-01'),
('Dolog Károly', 'Szeged', 'Almás tér 2', '2007-05-01'),
('Tronf Mihály', 'Debrecen', 'Lát u. 45.', '2005-02-01'),
('Ab Béla', 'Szolnok', 'Bach u. 91', '2000-07-01');
</code>



Egy cég a dolgozóit a "szemelyek" nevű táblában tartja nyilván.
A táblában minden dolgozónak van egy azonosítója, nyilván van tartva a neve, a település és cím ahol lakik, és mikor lépett be a céghez.

^  szemelyek  ^^^^^
^ az  ^  nev  ^  telepules  ^ cim  ^ belepes  ^
|  1  | Nagy József  | Szolnok  | Nyár u. 3.  |  2010.03.17  |
|  2  | Pár Elek  | Debrecen  | Tél u. 3  |  2005.01.01  |
|  3  | Tér Ferenc  | Szolnok  | Kossuth u. 5.  |  2000.02.01  |
|  4  | Dolog Károly  | Szeged  | Almás tér 2.  |  2007.05.01  |
|  5  | Tronf Mihály  | Debrecen   | Lát u. 45.  |  2005.02.01  |
|  6  | Ab Béla  | Szolnok  | Bach u. 91. |  2000.07.01  |


=== Feladatok ===


Jelenítse meg azokat a dolgozókat, amelyek Szolnokon laknak.

--> Megoldás #

<code sql>
SELECT nev
FROM szemelyek
WHERE telepules="Szolnok";
</code>

<--


Számolja meg, összesen, hány személy van a szemelyek táblában.

--> Megoldás #

<code sql>
SELECT count(az) AS Összesen
FROM szemelyek;
</code>

<--






Számolja meg, hány szolnoki személy van.

--> Megoldás #
<code sql>
SELECT Count(az)
FROM szemelyek
WHERE telepules="Szolnok";
</code>
<--



Számolja meg, Pár Elek hány napja van a cégnél.

--> Megoldás #
<code sql>
SELECT now() - belepes AS "Napok száma"
FROM szemelyek
WHERE nev="Pár Elek";
</code>
<--



Jelenítse meg, azoknak a nevét, akik februárban léptek be.

--> Megoldás #
<code sql>
SELECT nev
FROM szemelyek
WHERE month(belepes)=2;
</code>
<--


Jelenítse meg, milyen települések vannak. Egy település csak egyszer szerepeljen.

--> Megoldás #
<code sql>
SELECT telepules
FROM szemelyek
GROUP BY telepules;
</code>
<--

Jelenítse meg, azok nevét, akik 2003 előtt léptek be.


--> Megoldás #

MySQL és MariaDB:
<code mysql>
SELECT nev
FROM szemelyek
WHERE belepes<"2003-1-1";
</code>

MS Access:
<code sql>
SELECT nev
FROM szemelyek
WHERE belepes<#1/1/2003#;
</code>


<--


Jelenítse meg, a szegedi és szolnoki dolgozók közül azokat, akik 2005 után jöttek a céghez.

--> Megoldás #

MySQL és Mariadb:
<code sql>
SELECT nev, telepules, belepes
FROM szemelyek
WHERE (telepules = "Szeged" OR 
telepules = "Szolnok")
AND belepes > "2005-12-31";
</code>

MS Access
<code sql>
SELECT nev, telepules, belepes
FROM szemelyek
WHERE (telepules = "Szeged" OR 
telepules = "Szolnok")
AND belepes > #31/12/2005#;
</code>

<--


A MS Access megoldás az MS Access 2007-es szintaktikája.


==== Feladat 002 ====

Adott a következő pácienseket tartalmazó "Személy" nevű tábla:

^  szemelyek  ^^^^^^
^ id ^ nev ^ telepules ^ szuletes  ^ ar ^ tuszuras ^
| 1 | Nagy Emese | Szolnok | 1978.05.11 | 6500 | igen |
| 2 | Kis Géza | Nyíregyháza | 1982.07.01 | 10000 | nem |
| 3 | Danka Béla | Szolnok | 1984.02.05 | 12000 | nem |


<code mysql>
create table szemelyek (
    id int not null primary key auto_increment,
    nev varchar(50),
    telepules varchar(50),
    szuletes date,
    ar double,
    tuszuras boolean
);


insert into szemelyek 
(nev, telepules, szuletes, ar, tuszuras)
values
('Nagy Emese', 'Szolnok','1978-05-11', 6500,1),
('Kis Géza', 'Nyíregyháza','1982-07-01', 10000,1),
('Danka Béla', 'Szolnok','1984-02-05', 12000,1);
</code>



A táblázatban tároljuk a páciens azonosítóját (id), a nevét (név,
a települést ahol lakik (telepules), az árat, amit legutóbb fizetett (ar),
és lehet-e tűszúrással kezelni (tuszuras)

Készítse el, a következő lekérdezéseket:

Jelenítse meg, az összes mezőt, az összes rekorddal:

--> Megoldás #

<code sql>
SELECT * FROM szemelyek;
</code>

<--


Jelenítse meg, csak a nevek és a település mezőket:


--> Megoldás #

<code sql>
SELECT nev, telepules
FROM szemelyek;
</code>

<--


Jelenítse meg, csak a település és az ár mezőket:

--> Megoldás #

<code sql>
SELECT telepules, ar
FROM szemelyek;
</code>

<--


Jelenítse meg, a szolnokiak neveit:

--> Megoldás #

<code sql>
SELECT nev
FROM szemelyek
WHERE telepules="Szolnok";
</code>

<--


Jelenítse meg, azok neveit és települését, akik többet fizetnek mint 10000:

--> Megoldás #

<code sql>
SELECT nev, telepules
FROM szemelyek
WHERE ar>10000;
</code>

<--


Jelenítse meg, ki hány éves?

--> Megoldás #

<code sql>
SELECT nev, year(now()) - year(szuletes)
FROM szemelyek;
</code>

<--


Jelenítse meg, a mai dátumot:

--> Megoldás #

<code sql>  
SELECT now();
</code>

<--


Jelenítse meg, a mai dátum év részét:

--> Megoldás #

<code sql>
SELECT year(now());
</code>

<--


Mai dátum hónap része:

--> Megoldás #

<code sql>
SELECT month(now());
</code>

<--


A mai dátum nap része: 

--> Megoldás #

<code sql>
SELECT day(now());
</code>

<--


Jelenítése meg, mindenki nevét és életkorát:

--> Megoldás #

<code sql>
SELECT nev, year(now()) - year(szuletes)
FROM szemelyek;
</code>

<--


Számolja meg, hány páciens van:

--> Megoldás #

<code sql>
SELECT count(az)
FROM szemelyek;
</code>

<--


Számolja meg, hány páciens van Szolnokról:

--> Megoldás #

<code sql>
SELECT count(az)
FROM szemelyek
WHERE telepules="Szolnok";
</code>

<--


Hány embert lehet tűszúrással gyógyítani?

--> Megoldás #

<code sql>
SELECT count(az)
FROM szemelyek
WHERE tuszuras=true;
</code>

<--


Mennyi volt a bevétel, a páciensektől eddig összesen?

--> Megoldás #

<code sql>
SELECT sum(ar)
FROM szemelyek;
</code>

<--


Mennyi volt a bevétel, a páciensektől eddig összesen? A fejléc "Összbevétel" legyen!

--> Megoldás #

<code sql>
SELECT sum(ar) as Összbevétel
FROM szemelyek;
</code>

<--


Mennyi, az átlagbevétel Budapestről?

--> Megoldás #

<code sql>
SELECT avg(ar)
FROM szemelyek
WHERE telepules="Budapest";
</code>

<--


Mennyi, az átlagbevétel Budapestről? A fejléc "Budapesti átlagbevétel" legyen!

--> Megoldás #

<code sql>
SELECT avg(ar) as "Budapesti átlagbevétel"
FROM szemelyek
WHERE telepules="Budapest";
</code>

<--


Jelenítse meg, azok neveit, akik Szolnokról valók és tűszúrással gyógyíthatók!

--> Megoldás #

<code sql>
SELECT nev 
FROM szemelyek
WHERE telepules="Szolnok" and
          tuszuras=true;
</code>

<--


Jelenítse meg, milyen településekről vannak páciensek. Egy név csak egyszer szerepeljen.

--> Megoldás #

<code sql>
SELECT telepules
FROM szemelyek
GROUP BY telepules;
</code>

Csoportosítjuk település szerint az eredményt.

<--






Jelenítse meg, a 1970 előtt születettek neveit és településüket. 

--> Megoldás #
MySQL, Mariadb:
<code sql>
SELECT nev, telepules
FROM szemelyek
WHERE személy.szuletes< "1970-01-01";
</code>

MS Access:
<code sql>
SELECT nev, telepules
FROM szemelyek
WHERE személy.szuletes<#1/1/1970#;
</code>

<--

==== Feladat 003 ====

Adott az alábbi adatbázis. 
<code mysql>
create database surubt;
use surubt;
create table dolgozok (
	az int not null primary key auto_increment,
	nev varchar(30),
	telepules varchar(30),
	cim varchar(30),
	szuletes date,
	belepes date,
	fizetes double,
	diploma boolean
);

insert into dolgozok
(nev, telepules, cim, szuletes, belepes, fizetes, diploma)
values
("Nagy József", "Szolnok", "Tél u. 23.", "1975-08-25", "2004-03-01", 570000, 1),
("Pék Réka", "Miskolc", "Árny u. 29", "1977-02-03", "2006-04-01", 389000, 1),
("Kis Mária", "Debrecen", "Vértes u. 20.", "1981-04-01", "2005-03-27", 680000, 1),
("Érdek Júlia", "Szeged", "Bíró u. 25.", "1982-03-08", "2008-09-01", 190000, 0),
("Boros Evelin", "Szolnok", "Barát u. 78.", "1988-02-22", "2001-01-01", 850000, 0),
("Lórem Kinga", "Szeged", "Pilisi út 17.", "1973-08-02", "2000-01-01", 878000, 1),
("Fix Béla", "Szolnok", "Burkus u. 80.", "1991-11-05", "2008-01-01", 351000, 1);

</code>

Hozza létre az adatbázist.


^  dolgozok  ^^^^^^^^
^  az ^  nev  ^  telepules  ^  cim  ^  szuletes  ^  belepes  ^  fizetes  ^  diploma  ^
|  1  | Nagy József | Szolnok | Tél u. 23. | 1975-08-25 | 2004-03-01 | 570000 |  1  |
|  2  | Pék Réka | Miskolc | Árny u. 29. | 1977-02-03 | 2006-04-01 |	389000 |  1  |
|  3  | Kis Mária | Debrecen | Vértes u. 20. |	1981-04-01 | 2005-03-27 | 680000 |  1  |
|  4  | Érdek Júlia | Szeged | Bíró u. 25. | 1982-03-08 | 2008-09-01 |	190000 |  0  |
|  5  | Boros Evelin | Szolnok | Barát u. 78. |	1988-02-22 | 2001-01-01 | 850000 |  0  |
|  6  | Lórem Kinga | Szeged | Pilisi út 17. |	1973-08-02 | 2000-01-01 | 878000 |  1  |
|  7  | Fix Béla | Szolnok | Burkus u. 80. | 1991-11-05 | 2008-01-01 | 351000 |  1  |

Írassa ki, a legkésőbb belépett, szolnoki dolgozó nevét. 

--> Megoldás #

<code mysql>
select nev from dolgozok
where belepes =
(
select max(belepes) from dolgozok
where telepules="Szolnok")
</code>

<code mysql>
select nev
from dolgozok
where telepules = "Szolnok"
order by belepes desc
limit 1;
</code>

<--

==== Feladat 004 ====

Adott a következő dolgozók nevű tábla.

^  Dolgozók  ^^^^^^^^^
^  Az ^  Név  ^  Anyja neve  ^  Település  ^  Lakcím  ^  Születés  ^  Belépés  ^  Fizetés  ^  Jutalom  ^
|  1  | Nagy József | Marsh Eszter | Szolnok | Tél u. 23. | 1975-08-25 | 2004-03-01 | 570000 |  5000  |
|  2  | Pék Réka | Sargéj Tímea | Miskolc | Árny u. 29. | 1977-02-03 | 2006-04-01 |	389000 |  10000  |
|  3  | Kis Mária | Immell Márta | Debrecen | Vértes u. 20. |	1981-04-01 | 2005-03-27 | 680000 |  5000  |
|  4  | Érdek Júlia | Haller Fruzsina | Szeged | Bíró u. 25. | 1982-03-08 | 2008-09-01 |	190000 |  15000  |
|  5  | Boros Evelin | Csömöri Nikolett | Szolnok | Barát u. 78. |	1988-02-22 | 2001-01-01 | 850000 |  5000  |
|  6  | Lórem Kinga | Gory Nikolett | Szeged | Pilisi út 17. |	1973-08-02 | 2000-01-01 | 878000 |  15000  |
|  7  | Fix Béla | Pritchard Lili | Szolnok | Burkus u. 80. | 1991-11-05 | 2008-01-01 | 351000 |  5000  |


<code mysql>
create table Dolgozók (
    Az int not null primary key auto_increment,
    Név varchar(50),
    Anyja_neve varchar(50),
    Település varchar(50),
    Lakcím varchar(50),
    Születés date,
    Belépés date,
    Fizetés double,
    Jutalom double
);


insert into Dolgozók 
(Név, Anyja_neve, Település, Lakcím, Születés, Belépés, Fizetés, Jutalom)
values
('Nagy József', 'Marsh Eszter', 'Szolnok', 'Tél u. 23.', '1975-08-25', '2004-03-01', 570000, 5000),
('Pék Réka', 'Sargéj Tímea', 'Miskolc', 'Árny u. 29.', '1977-02-03', '2006-04-01', 389000, 10000),
('Kis Mária', 'Immell Márta', 'Debrecen', 'Vértes u. 20', '1981-04-01', '2005-03-27', 680000, 5000),
('Érdek Júlia', 'Haller Fruzsina', 'Szeged', 'Bíró u. 25.', '1982-03-08', '2008-09-01', 190000, 15000),
('Boros Evelin', 'Csömöri Nikolett', 'Szolnok', 'Barát u. 78.', '1988-02-22', '2001-01-01', 850000, 5000),
('Lórem Kinga', 'Gory Nikolett', 'Szeged', 'Pilisi út 17.', '1973-08-02', '2000-01-01', 878000, 15000),
('Fix Béla', 'Pritchard Lili', 'Szolnok', 'Burkus u. 80.', '1991-11-05', '2008-01-01', 351000, 5000);


</code>




Kérdezzük le a települések neveit:

--> Megoldás #

<code sql>
SELECT  Település
FROM Dolgozók
GROUP BY Település
</code>
vagy
<code sql>
SELECT  DISTINCT Település
FROM Dolgozók
</code>

<--


Számoljuk meg hány település van.

--> Megoldás #

<code sql>
SELECT count(*)
AS "Települések száma"
FROM
(SELECT  DISTINCT Település
FROM Dolgozók) AS alap
</code>

vagy:

<code sql>
SELECT count(*)
AS "Települések száma"
FROM
(SELECT  Település
FROM Dolgozók
GROUP BY Település) AS alap
</code>

<--


Hány éves az első dolgozó?

--> Megoldás #

<code sql>
select year(now()) - year(születés)
from Dolgozók
where Az = 1
</code>

<--


Hány éves a legfiatalabb dolgozó?

--> Megoldás #

<code sql>
select year(now()) - year(max(születés))
from Dolgozók
</code>

<--


==== Feladat 005 ====

Adott a következő dolgozók nevű tábla.

^  Szemelyek  ^^^^^^^^
^  az ^  nev  ^  telepules  ^  lakcim  ^  szuletes  ^  belepes  ^  fizetes  ^  jutalom  ^
|  1  |  Perkel Ibolya |  Miskolc  |  Tornyosi út 75. | 1989-07-12  |  2013-02-01  |  1800000  |  560000  |
|  2  |  Talon Ferenc |  Hatvan  |  Árkos utca 43. | 1995-03-02  |  2015-01-01  |  1540000  |  560000  |


Válaszoljon az alábbi kérdésekre.

1.) Mennyi jutalmat kapnak az adonyi 
és keceli dolgozók? Összesen.


--> Megoldás #

<code sql>
select sum(jutalom)
from Szemelyek
where telepules="Adony"
or telepules="Kecel"
</code>

<--

2.) Mennyi jutalmat és fizetést kapnak battonyai,
mátészalkai és csorvási dolgozok, összesen?
A jutalom és fizetés is legyen összesítve, egyetlen
lekérdezésben.

--> Megoldás #

<code sql>
select sum(fizetes+jutalom) 
from Szemelyek
where telepules="Battonya"
or telepules="Mátészalka"
or telepules="Csorvás"
</code>

<--


3.) Mennyi jutalmat és fizetést kapnak nagyatádi,
nyírábrányi és kenderesi dolgozok, 
településenként összesen?


--> Megoldás #

<code sql>
select telepules, sum(jutalom+fizetes) as "Összes LÓVÉ"
from Szemelyek
where telepules="Nagyatád"
or telepules="Nyírábrány"
or telepuels="Kenderes"
group by telepules
</code>

<--


4.) Mi az életkorok átlaga az összes dolgozóknak?
Évre?

--> Megoldás #

<code sql>
select now()
</code>

<code sql>
select year(now())
</code>

<code sql>
select year(szuletes)
from Szemelyek
</code>

<code sql>
select year(now())-year(szuletes)
from Szemelyek
</code>

<code sql>
select avg(year(now())-year(szuletes)) as "Születések átlag"
from Szemelyek
</code>

<--


5.) 
Írass ki azoknak a hatvani dolgozóknak a nevét
fizetését és jutalmát, akik idősebbek 60 évnél.

--> Megoldás #

<code sql>
select nev, fizetes, jutalom
from Szemelyek 
where telepules = "Hatvan"
and year(now())-year(szuletes) > 60
</code>

<--


===== Kéttáblás lekérdezés =====

==== 101 feladat ====

{{:oktatas:adatbazis-kezeles:zeneszamokeloadok.png|}}

Jelenítse meg az összes zeneszámot és előadót.

--> Megoldás #

<code mysql>
select zeneszamok.cim
from zeneszamok inner join eloadok
on zeneszamok.eloadoAz=eloadok.az;
</code>

A művelethez a két táblát össze kell kapcsolni. 
A "**from zeneszamok inner join eloadok**" rész azt mondja, hogy a
**zeneszamok** táblát összekapcsolom az "eloadok" táblával. 
Az "**on zeneszamok.eloadoAz=eloadok.az;**" rész azt mondja meg, hogy
melyik két mezőt kapcsolom össze a két táblában. A **zeneszamok**
táblában az "**eloadoAz**" idegen kulcs. Az "**eloadok**" táblában
az "**az**" mező elsődleges kulcs. Ezeket kapcsoljuk össze. 



<--



==== 102 feladat ====

{{:oktatas:adatbazis-kezeles:arpakiado.png|}}


Kérdezze le a "Lovas" című könyv szerzőjének telefonszámát.

--> Megoldás #

<code mysql>
select telefon
from konyvek inner join szerzok
on konyvek.szerzoAz = szerzok.az
where cim="Lovas"
</code>

<--

Jelenítse meg azon könyvek szerzőinek
nevét, akiknek a könyvcímükben 
szerepel a "lovas" szó.

--> Megoldás #

<code mysql>
select szerzo.nev 
from szerzok inner join konyvek
on konyvek.szerzoAz = szerzok.az
where konyvek.cim like "%lovas%";
</code>

<--


Jelenítse meg a 2000 Ft-nál 
drágább könyvek címeit, és 
szerzőjük nevét.

--> Megoldás #

<code mysql>
select konyvek.cim, szerzo.nev
from szerzok inner join konyvek
on konyvek.szerzoAz = szerzok.az
where konyvek.ar > 2000;
</code>

<--

Jelenítse meg hány darab 2000 Ft-nál 
drágább könyvek van. Az eredmény
felirata "Drága könyvek" legyen.


--> Megoldás #

<code mysql>
select count(konyvek.cim) 
as "Drága könyvek"
from konyvek
where ar > 2000;
</code>

<--

Jelenítse meg, mennyi a könyvek átlagára.
Az eredményben a 'Átlag' szerepeljen.

--> Megoldás #

<code mysql>
select avg(ar)
as "Átlag"
from konyvek;
</code>

<--



Szúrjon be egy új szerzőt a következő
adatokkal:
Pere János, 30-384124, pere@vhol.hu


--> Megoldás #

<code mysql>
insert into szerzok
(nev, telefon, email)
values
("Pere János", "30-384124", "pere@vhol.hu");
</code>

<--


Szúrjon be egy új könyvet
a következő adatokkal:
cím: Valami
ár: 3000
szerző: 3
Az ISBN-t nem kell megadni.


--> Megoldás #

<code mysql>
insert into konyvek
(cim, ar, szerzoAz)
values
("Valami", 3000, 3);
</code>

<--


Szúrjon be egy új könyvet
a következő adatokkal:
  * cím: Valami
  * ár: 3000
  * szerző: 3
  * ISBN: ismeretlen


--> Megoldás #

<code mysql>
insert into konyvek
(cim, ar, szerzoAz, isbn)
values
("Valami", 3000, 3, null);
</code>

<--
==== 103 feladat ====

{{:oktatas:adatbazis-kezeles:ingatlannyilvantartas.png|}}


Kérdezze le "Barna Ferenc" nevű tulajdonosnak, hány darab ingatlana van.

--> Megoldás #

<code mysql>
select count(*)
from ingatlanok inner join szemelyek
on ingatlanok.szemelyAz = szemelyek.az
where nev="Barna Ferenc"
</code>

<--




===== Három táblás =====
==== 201 feladat ====

Adott a következő adatbázis:

{{:oktatas:adatbázis-kezelés:kektrt.png?|}}


Kérdezze le Szabó János milyen projektekben vesz részt.

--> Megoldás #

<code mysql>
select projektek.nev, projektek.leiras

from (dolgozok inner join dolgozokProjektek
on dologozok.az = dolgozokProjektek.dolgozoAz) 
inner join projektek
on dolgozokProjektek.projektekAz = projektek.az

where dolgozok.nev = "Szabó József"
</code>

Az on direktívában a táblanevek rövidíthetők,
ha a inner join előtt és után megadunk egy 
álnevet:
<code mysql>
select projektek.nev, projektek.leiras

from (dolgozok a inner join dolgozokProjektek b
on a.az = b.dolgozoAz) 
inner join projektek c
on b.projektekAz = c.az

where dolgozok.nev = "Szabó József"
</code>

<--


==== 202 feladat ====

{{:oktatas:adatbazis-kezeles:rendelorecepttel.png|}}

Milyen beteghez tartozik a 35 számú recept?

--> Megoldás #

<code mysql>
select nev
from paciensek a 
inner join kezelesek b on a.az = b.paciensekAz
inner join receptek c on b.az = c.kezelesAz
where receptek.az = 35;
</code>

<--