# Cikkfajták és gyártók adatait tároljuk.

DROP TABLE IF EXISTS cikkfajtak_gyartok;
DROP TABLE IF EXISTS cikkfajtak;
DROP TABLE IF EXISTS gyartok;

CREATE TABLE cikkfajtak(
  cikk_id  INT PRIMARY KEY,
  cikk_nev VARCHAR(30) NOT NULL
  #...és egyéb adatok
)Engine=InnoDB;

CREATE TABLE gyartok(
  gy_id  INT PRIMARY KEY,
  gy_nev VARCHAR(30) NOT NULL
  #...és egyéb adatok
)Engine=InnoDB;

CREATE TABLE cikkfajtak_gyartok(
  cikk_id   INT,
  gy_id     INT,
  kapacitas INT NOT NULL,
  # napi gyártási kapacitás

  CONSTRAINT u_cikk_gyarto UNIQUE (cikk_id,gy_id),
  FOREIGN KEY (cikk_id) REFERENCES cikkfajtak(cikk_id),
  FOREIGN KEY (gy_id) REFERENCES gyartok(gy_id)
)Engine=InnoDB;

INSERT INTO cikkfajtak (cikk_id,cikk_nev) VALUES
  (1,'cikk_1'),
  (2,'cikk_2'),
  (3,'cikk_3'),
  (4,'cikk_4'),
  (5,'cikk_5');

INSERT INTO gyartok (gy_id,gy_nev) VALUES
  (9011,'gyártó_9011'),
  (9012,'gyártó_9012'),
  (9013,'gyártó_9013'),
  (9014,'gyártó_9014'),
  (9015,'gyártó_9015'),
  (9016,'gyártó_9016');

INSERT INTO cikkfajtak_gyartok (cikk_id,gy_id,kapacitas) VALUES
  (1,9011,5),
  (1,9012,10),
  (1,9013,20),
  (2,9011,8),
  (2,9012,12),
  (3,9013,70),
  (3,9014,20),
  (4,9015,50);

1.

# Listázzuk ki a gyártók azonosítóit és az összkapacitásukat
# az összkapacitás szerint csökkenő sorrendben.

# Várt eredmény:

#   +-------+---------------+
#   | gy_id | összkapacitás |
#   +-------+---------------+
#   |  9013 |            90 |
#   |  9015 |            50 |
#   |  9012 |            22 |
#   |  9014 |            20 |
#   |  9011 |            13 |
#   +-------+---------------+

select gy_id,sum(kapacitas) as 'összkapacitás'
from cikkfajtak_gyartok 
group by gy_id
order by sum(kapacitas) desc;

######################################################

2.

# Listázzuk ki a gyártók azonosítóit, NEVÉT és az összkapacitásukat
# az összkapacitás szerint csökkenő sorrendben.

# Várt eredmény:

#   +-------+-------------+---------------+
#   | gy_id | gy_nev      | összkapacitás |
#   +-------+-------------+---------------+
#   |  9013 | gyártó_9013 |            90 |
#   |  9015 | gyártó_9015 |            50 |
#   |  9012 | gyártó_9012 |            22 |
#   |  9014 | gyártó_9014 |            20 |
#   |  9011 | gyártó_9011 |            13 |
#   +-------+-------------+---------------+


select gyartok.gy_id,gy_nev, sum(kapacitas) as 'összkapacitás'
from cikkfajtak_gyartok 
inner join gyartok on gyartok.gy_id = cikkfajtak_gyartok.gy_id
group by gy_id
order by sum(kapacitas) desc;


######################################################

3.

# Listázzuk ki azon gyártók azonosítóit és az összkapacitásukat az összkapacitás
# szerint csökkenő sorrendben, akiknek az összkapacitása nagyobb 20-nál
# az összkapacitás szerint csökkenő sorrendben.

# Várt eredmény:

#   +-------+---------------+
#   | gy_id | összkapacitás |
#   +-------+---------------+
#   |  9013 |            90 |
#   |  9015 |            50 |
#   |  9012 |            22 |
#   +-------+---------------+


select gy_id,sum(kapacitas) as 'összkapacitás'
from cikkfajtak_gyartok 
group by gy_id
having sum(kapacitas) > 20
order by sum(kapacitas) desc;

######################################################

4.

# Listázzuk ki a gyártók és cikkfajták azonosítóit és az összkapacitást
# gyártó és cikkfajta szerint csoportosítva az összkapacitás szerint
# csökkenő sorrendben.

# Várt eredmény:

#   +-------+---------+---------------+
#   | gy_id | cikk_id | összkapacitás |
#   +-------+---------+---------------+
#   |  9011 |       2 |             8 |
#   |  9011 |       1 |             5 |
#   |  9012 |       2 |            12 |
#   |  9012 |       1 |            10 |
#   |  9013 |       3 |            70 |
#   |  9013 |       1 |            20 |
#   |  9014 |       3 |            20 |
#   |  9015 |       4 |            50 |
#   +-------+---------+---------------+



select gy_id,cikk_id,kapacitas as 'összkapacitás'
from cikkfajtak_gyartok
order by gy_id,cikk_id desc, kapacitas desc;



######################################################

5.

# Listázzuk ki azon cikkfajták azonosítóját és nevét, melyeket egyetlen
# gyártó sem gyárt.

# Várt eredmény:

#   +---------+----------+
#   | cikk_id | cikk_nev |
#   +---------+----------+
#   |       5 | cikk_5   |
#   +---------+----------+

SELECT cikkfajtak.cikk_id, cikk_nev
FROM cikkfajtak
LEFT JOIN cikkfajtak_gyartok ON  cikkfajtak_gyartok.cikk_id = cikkfajtak.cikk_id
WHERE gy_id IS NULL;

######################################################