Zenekarok_2021_okt_minta

1. Adatbázis létrehozása és adatok importálása

1.1 Adatbázis létrehozása

CREATE DATABASE zenekarok;

USE zenekarok;

1.2 Táblák létrehozása

CREATE TABLE egyuttesek (

ekod INT PRIMARY KEY, -- Együttes egyedi kódja (kulcs)

nev VARCHAR(100), -- Együttes neve

ev INT, -- Alakulás éve

mufaj VARCHAR(255), -- Műfaj(ok)

emerton BOOLEAN -- eMeRTon-díj (logikai)

);

CREATE TABLE zeneszek (

azon INT PRIMARY KEY AUTO_INCREMENT, -- Egyedi azonosító

nev VARCHAR(100), -- Zenész neve

ekod INT, -- Együttes kódja (kapcsolat az együttesek táblával)

szuldt DATE, -- Születési dátum

haldt DATE NULL, -- Halálozási dátum (lehet NULL)

szerep VARCHAR(100), -- Zenész szerepe az együttesben

FOREIGN KEY (ekod) REFERENCES egyuttesek(ekod)

);

1.3 Adatok importálása fájlból

LOAD DATA INFILE 'egyuttesek.txt'

INTO TABLE egyuttesek

FIELDS TERMINATED BY ';'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS;

LOAD DATA INFILE 'zeneszek.txt'

INTO TABLE zeneszek

FIELDS TERMINATED BY ';'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS;

Ha a fájl a helyi gépen van, akkor LOCAL kulcsszót kell használni:

LOAD DATA LOCAL INFILE 'egyuttesek.txt' INTO TABLE egyuttesek ...

2. Gitáros zenészek listája (2gitar)

SELECT nev

FROM zeneszek

WHERE szeriep LIKE '%gitár%'

ORDER BY nev;

Mit csinál?

• Megkeresi azokat a zenészeket, akiknek a szerepében szerepel a "gitár" szó.
• A találatokat név szerint rendezi.

3. Együttesek tagjainak száma (3letszam)

SELECT egyuttesek.nev, COUNT(zeneszek.azon) AS letszam

FROM egyuttesek

LEFT JOIN zeneszek ON egyuttesek.ekod = zeneszek.ekod

GROUP BY egyuttesek.nev

ORDER BY letszam DESC;

Mit csinál?

• Összeszámolja az együttesek tagjait.
• A legtöbb taggal rendelkező együttes kerül előre.

4. Együttes(ek) a legtöbb énekessel (4enekes)

SELECT egyuttesek.nev, COUNT(zeneszek.azon) AS enekesek_szama

FROM egyuttesek

JOIN zeneszek ON egyuttesek.ekod = zeneszek.ekod

WHERE zeneszek.szerep LIKE '%ének%'

GROUP BY egyuttesek.nev

HAVING enekesek_szama = (

SELECT MAX(enekesek_count)

FROM (

SELECT COUNT(zeneszek.azon) AS enekesek_count

FROM zeneszek

WHERE szerep LIKE '%ének%'

GROUP BY ekod

) AS max_enekesek

);

Mit csinál?

• Megszámolja az együttesek énekeseit.
• Csak azt az együttest mutatja, amelyikben a legtöbb énekes van.

5. A legkorábban alakult együttes tagjai és szerepük (5tagok)

SELECT zeneszek.nev, zeneszek.szerep

FROM zeneszek

JOIN egyuttesek ON zeneszek.ekod = egyuttesek.ekod

WHERE egyuttesek.ev = (

SELECT MIN(ev) FROM egyuttesek

)

ORDER BY zeneszek.nev;

Mit csinál?

• Kikeresi a legrégebbi együttest.
• Annak tagjait és szerepüket név szerint rendezi.

6. Együttesek, amelyek már biztosan nem zenélhetnek eredeti felállásban (6egyuttes)

SELECT DISTINCT egyuttesek.nev

FROM egyuttesek

JOIN zeneszek ON egyuttesek.ekod = zeneszek.ekod

WHERE zeneszek.haldt IS NOT NULL;