--  I. 610.
--  Schmidt Marcell
--  Pécs, Koch Valéria Gimn., Ált. Isk., 9. o. t.

-- 1. Készítsünk új adatbázist ebed néven.

CREATE DATABASE ebed;

USE ebed;

CREATE TABLE ebed (
  nev varchar(50),
  azon varchar(50),
  fizA int(11),
  fizB int(11),
  megA int(11),
  megB int(11),
  PRIMARY KEY (azon)
);

-- 2. Melyik diákok fizettek elő legalább egy napra B menüt a 2014-ben induló C osztályból?
-- Adjuk meg a tanulók nevét ABC sorrendben. (2bmenuc)
-- 2bmenuc

SELECT nev
  FROM ebed
 WHERE LEFT(azon, 5) = '2014C'
   AND fizB > 0
ORDER BY nev

-- 3. Lekérdezéssel adjuk meg azoknak a tanulóknak a nevét és azonosítóját,
-- akiknek befizettek A menüs ebédet, és ők azt mind elfogyasztották. (3amegette)
-- 3amegette

SELECT nev, azon
  FROM ebed
 WHERE fizA > 0
   AND fizA = megA

-- 4. Készítsünk lekérdezést, amely megadja a nevüket azoknak,
-- akik a 2015-ben indult C osztályba járnak és legalább egy napra be vannak fizetve ebédelni. (4ebed15C)
-- 4ebed15C

SELECT nev
  FROM ebed
 WHERE LEFT(azon, 5) = '2015C'
   AND (fizA > 0 OR fizB > 0)

-- 5. Lekérdezés segítségével listázzuk ki, hogy melyik osztályban hány olyan tanuló van,
-- aki legalább öt napi ebédet elfogyasztott. (5otebed)
-- 5otebed
SELECT osztaly, tanulok
FROM
(
SELECT LEFT(azon, 5) AS osztaly,
       COUNT(azon) AS tanulok,
       megA, megB
  FROM ebed
GROUP BY LEFT(azon, 5)
HAVING megA + megB >= 5
) AS otebed

-- 6. Készítsünk lekérdezést, amely megadja azokat a tanulókat,
-- akik azonos névvel szerepelnek az adatbázisban. (6nevazon)
-- 6nevazon

SELECT nev, azon
  FROM ebed
WHERE nev IN
(
SELECT nev
FROM
(
SELECT nev, COUNT(azon)
  FROM ebed
GROUP BY nev
HAVING COUNT(azon) > 1
) AS nevazon
)

-- 7. Lekérdezés segítségével listázzuk ki, hogy a C osztálytípusba járó tanulóknál
-- mennyi a menü fogyasztásának és befizetésének aránya.
-- A listában azok szerepeljenek az arány szerint növekvő sorrendben, 
-- akiket legalább egy napra befizettek. (7arany)
-- 7arany

SELECT nev, (megA + megB)/(fizA + fizB) AS arany
  FROM ebed
 WHERE MID(azon, 5, 1) = 'C'
   AND fizA + fizB >= 1
ORDER BY (megA + megB)/(fizA + fizB)
