Utolsó óra
(gyakorlás)

Feladatok:

1) Sorold fel az állatkák nevét akit Dina idizett

2) melyik gyepiről jött a legtöbb állatka?

3) Ki a legrégebb óta nem gazdisodott állatkánk és mennyi ideje van itt?

4) Hány kan és hány szuka baba kutyánk volt?

5) Hány szív és bőrférges kutyánk van?
6) Ki az a szívférges aki legkésőbb gazdisdodott?

7) Hány cica gazdisodott vidékre?

8) Hány bebukós idizésünk volt?

9) Évenként hány állatka gazdisítottunk?

10) Ki idizett a leghosszabb ideig és mi az állatka neve?

11) Helyes-e a következő Query? (csináljunk meg elötte a táblákat)
SELECT MULAN FROM MFÖEK INNER JOIN WATER,BALL,LOVE

Feladatok:

12) Ki a legöregebb mentvényünk (mostani év szerint azaz bekerüléskori kora + az azóta eltelt év)?

13) Ki a legöregebb gazdisodott mentvényünk?
14) melyik álltakának utalták a legtöbb örökbefogadási díjat?

15) hány olyan állatka van akinek többet fizettek mint az "alap" ár? (ivaros kutya: 30ezer, ivartalan 35ezer, ivaros cica 15ezer, ivartalan cica 20ezer)

16) átlagosan mennyi örökbe adási díjat fizettek a gazdik?

17) kik azok akiknek elengedtük a díjat?

18) 11. kerületi állatkák közül kinek volt a legmagasabb befizetett díja?

19) kerületenként kinek volt a legmagasabb befizetett díjja? (help: SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '')); )

20) melyik Noémi legelső és legutolsó idizett állatkája?

Egy kis extra elmélet A 20as kérdés miatt
(mert ma "csak" gyakorlunk 😃)

Mi is az a Common Table Expression? (CTE)

A CTE egy nevesített ideiglenes lekérdezés halmaz, mely a lekérdezés ideéig él

Gyakorlatban így nézz ki (mindig With-el kezdjük)

WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;

Megoldások:

1) SELECT mentveny_nev from idik where idi='Dörflinger Edina';

2) SELECT gyepi,count(1) FROM Mentesek GROUP BY 1 ORDER BY 2 DESC LIMIT 1;

3) SELECT mfoek_nev,nalunk_toltot_nap FROM Mentesek WHERE gazdis_datum like '0000-00-00%' order by 2 desc LIMIT 1;

4) SELECT neme,count(1) FROM mentes_stat WHERE baba=1 AND neme in('kan','szuka') GROUP BY 1;

5) SELECT count(1) FROM mentes_stat WHERE szivfereg=1 AND borfereg=1;
6) SELECT m.mfoek_nev FROM Mentesek as m INNER JOIN mentes_stat as ms on m.pupID=ms.pupID WHERE ms.szivfereg=1 AND gazdihoz_koltozes not like '0000-00-00%' ORDER BY nalunk_toltot_nap DESC LIMIT 1;

Megoldások:

7) SELECT count(1) FROM gazdik as g INNER JOIN mentes_stat as ms ON g.mentveny_id=ms.pupID WHERE ms.neme IN ('kandúr','nöstény') AND g.varos<>'Budapest';

8) SELECT count(1) FROM mentes_stat WHERE erkezes=gazdihoz_koltozes;

9) SELECT YEAR(gazdis_datum),count(1) FROM Mentesek WHERE gazdis_datum not like '0000-00-00%' group by 1;

10) SELECT mfoek_nev,idi FROM Mentesek ORDER BY nalunk_toltot_nap desc LIMIT 1;

11) CREATE TABLE MFÖEK AS SELECT 'Mulan original name was Husika' AS MULAN; CREATE TABLE WATER SELECT '' AS Water; CREATE TABLE BALL SELECT '' AS Ball; CREATE TABLE LOVE SELECT '' AS LOVE;
SELECT MULAN FROM MFÖEK INNER JOIN WATER,BALL,LOVE

Megoldások:

12) SELECT IF(SUBSTRING_INDEX(kora,' ',-1)='év',SUBSTRING_INDEX(kora,' ',1),0) + ROUND(DATEDIFF(NOW(), erkezes)/365) AS kor,mentveny_name FROM mentes_stat ORDER BY kor DESC LIMIT 1;

13) SELECT IF(SUBSTRING_INDEX(kora,' ',-1)='év',SUBSTRING_INDEX(kora,' ',1),0) + ROUND(DATEDIFF(NOW(), erkezes)/365) AS kor,mentveny_name FROM mentes_stat WHERE gazdihoz_koltozes not like '0000-00-00%' ORDER BY kor DESC LIMIT 1;
14) SELECT mentveny_name,CAST(REPLACE(SUBSTRING_INDEX(orokbefogadasi_dij,' ',1),'.','') AS DECIMAL) AS dij FROM mentes_stat WHERE orokbefogadasi_dij<>'' AND orokbefogadasi_dij LIKE '%.%' ORDER BY 2 DESC LIMIT 1;

Megoldások:

15) SELECT count(1) FROM mentes_stat WHERE orokbefogadasi_dij<>'' AND orokbefogadasi_dij LIKE '%.%' AND ((CAST(REPLACE(SUBSTRING_INDEX(orokbefogadasi_dij,' ',1),'.','') AS DECIMAL)>30000 AND Ivartalanitas=0 and neme in('kan','szuka')) OR (CAST(REPLACE(SUBSTRING_INDEX(orokbefogadasi_dij,' ',1),'.','') AS DECIMAL)>35000 AND Ivartalanitas=1 AND neme in('kan','szuka')) OR (CAST(REPLACE(SUBSTRING_INDEX(orokbefogadasi_dij,' ',1),'.','') AS DECIMAL)>15000 AND Ivartalanitas=0 and neme in('kandúr','nőstény')) OR (CAST(REPLACE(SUBSTRING_INDEX(orokbefogadasi_dij,' ',1),'.','') AS DECIMAL)>20000 AND Ivartalanitas=1 AND neme in('kandúr','nőstény')));

Megoldások:

16) SELECT AVG(CAST(REPLACE(SUBSTRING_INDEX(orokbefogadasi_dij,' ',1),'.','') AS DECIMAL)) AS atlag_dij FROM mentes_stat WHERE orokbefogadasi_dij<>'' AND orokbefogadasi_dij LIKE '%.%';

17) SELECT mentveny_name,orokbefogadasi_dij FROM mentes_stat WHERE orokbefogadasi_dij LIKE 'elenged%' OR orokbefogadasi_dij LIKE 'Hangya%';

18) SELECT ms.mentveny_name, CAST(REPLACE(SUBSTRING_INDEX(ms.orokbefogadasi_dij,' ',1),'.','') AS DECIMAL) AS dij FROM mentes_stat AS ms INNER JOIN gazdik as g ON ms.pupID=g.mentveny_id WHERE ms.orokbefogadasi_dij<>'' AND ms.orokbefogadasi_dij LIKE '%.%' AND MID(g.ZIP,2,2)='11' AND g.varos='Budapest' ORDER BY 2 DESC LIMIT 1;

Megoldások:

19) SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));SELECT MID(g.ZIP,2,2) as kerulet,MAX(CAST(REPLACE(SUBSTRING_INDEX(ms.orokbefogadasi_dij,' ',1),'.','') AS DECIMAL)) AS dij FROM mentes_stat AS ms INNER JOIN gazdik as g ON ms.pupID=g.mentveny_id WHERE ms.orokbefogadasi_dij<>'' and ms.orokbefogadasi_dij like '%.%' and g.varos='Budapest' GROUP BY 1 order by 2 desc;

20) WITH t1 AS (SELECT ms.erkezes,ms.mentveny_name FROM idik as i INNER JOIN mentes_stat as ms ON i.pupID=ms.pupID WHERE i.idi='Roszkopf Noémi' order by ms.erkezes limit 1), t2 AS (SELECT ms.erkezes,ms.mentveny_name FROM idik as i INNER JOIN mentes_stat as ms ON i.pupID=ms.pupID WHERE i.idi='Roszkopf Noémi' order by ms.erkezes DESC limit 1) SELECT * from t1,t2;

Feladatok:

Kicsit ismerjük meg a form_responses táblát:

21) listázzd ki az első 5 sort

22) Hány bejegyzés került Zorbáról?

23) sorold fel az összes adminos mentvényeit (mármint párban melyik adminosnak melyik mentvénye volt)

24) kinek lett a legtöbb bejegyzése?

25) hány Update-t csinált Gábor? :D

26) kérd le minden állatka cicakkal utolsó bejgyzésést
(help: SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '')); )

27) kérd le Zorba utolsó bejegyzését

28) kérd le Zorba utolsó státuszát

29) kérd le Zorba teljes idő szerint utolsó pupdate-jét (minden kitöltött mező látszódjon)

Megoldások:

21) SELECT * FROM form_responses LIMIT 5;

22) SELECT Count(1) FROM form_responses WHERE mfoek_nev='Zorba';

23) SELECT adminos,mfoek_nev FROM form_responses WHERE adminos<>'' GROUP BY 1,2 ORDER BY 1;

24) SELECT mfoek_nev,count(1) FROM form_responses GROUP BY 1 ORDER BY 2 DESC LIMIT 1;

25) SELECT count(1) FROM form_responses WHERE updated_by='Gábor';

26) SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '')); SELECT mfoek_nev,cicakkal,max(Timstamp) FROM form_responses WHERE cicakkal<>'' GROUP BY 1;

27) SELECT * FROM form_responses WHERE mfoek_nev='Zorba' ORDER BY Timstamp DESC LIMIT 1;

Megoldások:

28) SELECT * FROM form_responses WHERE mfoek_nev='Zorba' AND status<>'' ORDER BY Timstamp DESC LIMIT 1;

29) WITH t1 AS (SELECT max(Timstamp) as idopont,mfoek_nev FROM form_responses where mfoek_nev='Zorba' group by 2), t2 AS (SELECT mfoek_nev,koordi FROM form_responses where mfoek_nev='Zorba' and koordi<>'' order by Timstamp desc limit 1), t3 AS (SELECT mfoek_nev,adminos FROM form_responses where mfoek_nev='Zorba' and adminos<>'' order by Timstamp desc limit 1), t4 AS (SELECT mfoek_nev,status FROM form_responses where mfoek_nev='Zorba' and status<>'' order by Timstamp desc limit 1), t5 AS (SELECT mfoek_nev,kell_jelolt FROM form_responses where mfoek_nev='Zorba' and kell_jelolt<>'' order by Timstamp desc limit 1), t6 AS (SELECT mfoek_nev,helyzet FROM form_responses where mfoek_nev='Zorba' and helyzet<>'' order by Timstamp desc limit 1), t7 AS (SELECT mfoek_nev,updated_by FROM form_responses where mfoek_nev='Zorba' and updated_by<>'' order by Timstamp desc limit 1), t8 AS (SELECT mfoek_nev,meret FROM form_responses where mfoek_nev='Zorba' and meret<>'' order by Timstamp desc limit 1), t9 AS (SELECT mfoek_nev,kutyakkal FROM form_responses where mfoek_nev='Zorba' and kutyakkal<>'' order by Timstamp desc limit 1), t10 AS (SELECT mfoek_nev,cicakkal FROM form_responses where mfoek_nev='Zorba' and cicakkal<>'' order by Timstamp desc limit 1), t11 AS (SELECT mfoek_nev,gyerekkel FROM form_responses where mfoek_nev='Zorba' and gyerekkel<>'' order by Timstamp desc limit 1), t12 AS (SELECT mfoek_nev,egyedi_igeny FROM form_responses where mfoek_nev='Remus' and egyedi_igeny<>'' order by Timstamp desc limit 1) SELECT t1.mfoek_nev, t2.koordi, t3.adminos, t4.status, t5.kell_jelolt, t6.helyzet, t1.idopont, t7.updated_by, t8.meret, t9.kutyakkal, t10.cicakkal, t11.gyerekkel, t12.egyedi_igeny FROM t1 LEFT JOIN t2 ON true LEFT JOIN t3 ON true LEFT JOIN t4 ON true LEFT JOIN t5 ON true LEFT JOIN t6 ON true LEFT JOIN t7 ON true LEFT JOIN t8 ON true LEFT JOIN t9 ON true LEFT JOIN t10 ON true LEFT JOIN t11 ON true LEFT JOIN t12 on true;

Mi is az a View?
(még egy kis elmélet 😃 )

A view egy virtuális tábla, mely egy Query az adott időben lekért eredmény halmazát "tartalmazza"

CREATE TABLE myTestTable SELECT * FROM Mentesek WHERE mfoek_nev='Lencsi';
vs
CREATE VIEW myTestView SELECT * FROM Mentesek WHERE mfoek_nev='Lencsi';

Idáig teljesen ugyanaz, de ....

Jön egy Update, hogy Lencsi már bemutatva

UPDATE Mentesek set status='bemutatva' where mfoek_nev='Lencsi';

most mi lesz az eredménye

SELECT * FROM myTestTable;

SELECT * FROM myTestView;

Ezek alapján, hogy is raktam össze a pupdate táblát?

VÉGE

Köszönöm mindenkinek a részvételt és remélem hasznos tudást is sikerült átadnom vagy legalább kicsit rávilágítottam, hogy mi fán terem az adatabázis, meg hogy is nézz ki egy SQL Query

És az összes eddig előadás:
https://slides.com/gaboropitzer/adatbazis-kezeles-alapjai/fullscreen

Utolsó óra (gyakorlás)

Utolsó óra

More from Gábor Opitzer

Utolsó óra
(gyakorlás)