# Gyártmányfajták és alkatrészfajták adatait tároljuk.

# Mivel nem példányokról, hanem fajtákról van szó, egy gyártmány több alkatrészt
# tartalmazhat és egy alkatrész több gyártmányba is beépülhet. Konkrét alkatrész- és
# gyártmánypéldányoknál nyilván nem ez lenne a helyzet: Egy alkatrész egy helyre tud
# csak beépülni.

# Itt tehát N:M-es kapcsolatról van szó, ezért szükség van kapcsolótáblára.

DROP TABLE IF EXISTS gyartmanyok_alkatreszek;
DROP TABLE IF EXISTS gyartmanyfajtak;
DROP TABLE IF EXISTS alkatreszfajtak;

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

CREATE TABLE alkatreszfajtak(
  alk_id  INT PRIMARY KEY,
  alk_nev VARCHAR(30) NOT NULL
  #...és egyéb adatok
)Engine=InnoDB;

CREATE TABLE gyartmanyok_alkatreszek(
  gy_id     INT,
  alk_id    INT,
  darabszam INT NOT NULL,

  CONSTRAINT u_gep_alk UNIQUE (gy_id,alk_id),
  FOREIGN KEY (gy_id) REFERENCES gyartmanyfajtak(gy_id),
  FOREIGN KEY (alk_id) REFERENCES alkatreszfajtak(alk_id)
)Engine=InnoDB;

INSERT INTO gyartmanyfajtak (gy_id,gy_nev) VALUES
  (1,'gyártmány_1'),
  (2,'gyártmány_2'),
  (3,'gyártmány_3'),
  (4,'gyártmány_4'),
  (5,'gyártmány_5');

INSERT INTO alkatreszfajtak (alk_id,alk_nev) VALUES
  (1001,'alk_1001'),
  (1002,'alk_1002'),
  (1003,'alk_1003'),
  (1004,'alk_1004'),
  (1005,'alk_1005'),
  (1006,'alk_1006');

INSERT INTO gyartmanyok_alkatreszek (gy_id,alk_id,darabszam) VALUES
  (1,1001,5),
  (1,1002,10),
  (1,1003,20),
  (2,1001,8),
  (2,1002,12),
  (3,1003,70),
  (3,1004,20),
  (4,1005,50);

1.

# Listázzuk ki az összes összetartozó gyártmány és alkatrész azonosítóját és nevét
# és a beépülési darabszámot alkatrészazonosító és azon belül gyártmányazonosító szerint rendezve.

# Várt eredmény:

#   +----------+-----------+----------+-------------+-----------+
#   | alk.azon | alkatrész | gy.azon. | gyártmány   | darabszám |
#   +----------+-----------+----------+-------------+-----------+
#   |     1001 | alk_1001  |        1 | gyártmány_1 |         5 |
#   |     1001 | alk_1001  |        2 | gyártmány_2 |         8 |
#   |     1002 | alk_1002  |        1 | gyártmány_1 |        10 |
#   |     1002 | alk_1002  |        2 | gyártmány_2 |        12 |
#   |     1003 | alk_1003  |        1 | gyártmány_1 |        20 |
#   |     1003 | alk_1003  |        3 | gyártmány_3 |        70 |
#   |     1004 | alk_1004  |        3 | gyártmány_3 |        20 |
#   |     1005 | alk_1005  |        4 | gyártmány_4 |        50 |
#   +----------+-----------+----------+-------------+-----------+

select alkatreszfajtak.alk_id as `alk.azon`, alkatreszfajtak.alk_nev as `alkatresz`, gyartmanyfajtak.gy_id as `gy.azon`, gyartmanyfajtak.gy_nev as `gyartmany`,
gyartmanyok_alkatreszek.darabszám as `darabszam`
#NM                     darabszam
from alkatreszfajtak
inner join gyartmanyok_alkatreszek on alkatreszfajtak.alk_id = gyartmanyok_alkatreszek.alk_id
inner join gyartmanyfajtak on gyartmanyfajtak.gy_id = gyartmanyok_alkatreszek.gy_id
order by alk.azon;
#NM      `alk.azon` -- lemaradt az aposztrof (meg a másik mező).Amúgy jó az elgondolás.

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

2.

# Listázzuk ki az összes olyan alkatrész azonosítóját és össz-darabszámát, melynek van
# legalább egy feljegyzett gyártmánya.

# Várt eredmény:

#   +--------+-----------+
#   | alk_id | darabszám |
#   +--------+-----------+
#   |   1001 |        13 |
#   |   1002 |        22 |
#   |   1003 |        90 |
#   |   1004 |        20 |
#   |   1005 |        50 |
#   +--------+-----------+

select alk_id, sum(darabszam) as `darabszám`
from gyartmanyok_alkatreszek
group by alk_id;

OK

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

3.1.

# A fenti lekérdezést módosítsuk úgy, hogy csak azon alkatrészek
# azonosítója jelenjen meg, melyeknek össz-darabszáma 15-nél nagyobb
# és 75-nél kisebb.

# Várt eredmény:

#   +--------+-----------+
#   | alk_id | darabszám |
#   +--------+-----------+
#   |   1002 |        22 |
#   |   1004 |        20 |
#   |   1005 |        50 |
#   +--------+-----------+


select alk_id, sum(darabszam) as `darabszám`
from gyartmanyok_alkatreszek
group by alk_id
having (`darabszám` > 15 and `darabszám` < 75);

OK

3.2.

# A listában jelenjenek meg az alkatrész-nevek is.

# Várt eredmény:

#   +--------+-----------+-----------+
#   | alk_id | alkatrész | darabszám |
#   +--------+-----------+-----------+
#   |   1002 | alk_1002  |        22 |
#   |   1004 | alk_1004  |        20 |
#   |   1005 | alk_1005  |        50 |
#   +--------+-----------+-----------+

select alk_id, sum(darabszam) as `darabszám`
from gyartmanyok_alkatreszek
group by alk_id
having (`darabszám` > 15 and `darabszám` < 75);
#NM A zárójel felesleges (de nem baj).

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

4.

# Listázzuk ki az összes olyan alkatrész minden adatát, melyekhez nincs feljegyezve
# egyetlen gyártmány sem.

# Várt eredmény:

#   +--------+----------+
#   | alk_id | alk_nev  |
#   +--------+----------+
#   |   1006 | alk_1006 |
#   +--------+----------+

# 4.1. LEFT JOIN segítségével.
SELECT alkatreszfajtak.*
FROM alkatreszfajtak
LEFT JOIN gyartmanyok_alkatreszek ON alkatreszfajtak.alk_id = alkatreszfajtak.alk_id
#NM                                                           +++++++++++++++ gyartmanyok_alkatreszek kellett volna :)
WHERE gy_id IS NULL;

nem ok
# 4.2. EXISTS művelettel és korrelált allekérdezéssel.


SELECT alkatreszfajtak.*
FROM alkatreszfajtak
WHERE NOT EXISTS(
  SELECT *
  FROM alkatreszfajtak
#NM    gyartmanyok_alkatreszek kellett volna
  WHERE ... = ...
);


# 4.3. IN művelettel és korrelálatlan allekérdezéssel.

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

5.

# Írassuk ki a legnagyobb és a legkisebb alkatrész össz-darabszámot. (Anélkül persze,
# hogy felhasználnánk, hogy most éppen az 1001-es és az 1003-as azonosítójú
# alkatrészről van szó, ld. a 2. feladat eredményét)

# Tipp: származtatott tábla.
select max(
# Várt eredmény:

#   +------------+-----------+
#   | legnagyobb | legkisebb |
#   +------------+-----------+
#   |         90 |        13 |
#   +------------+-----------+

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