Geograafiaga seotud tabelid

geo_maailmajaod (nimetus, pindala)
geo_riigid (riiginimi, pealinn, maailmajagu, riigipindala)

CREATE TABLE geo_maailmajaod(
  nimetus VARCHAR(50) NOT NULL PRIMARY KEY,
  pindala INT
);

CREATE TABLE geo_riigid(
   riiginimi VARCHAR(50) NOT NULL PRIMARY KEY,
   pealinn VARCHAR(50),
   maailmajagu VARCHAR(50),
   riigipindala INT,
   FOREIGN KEY(maailmajagu) REFERENCES geo_maailmajaod(nimetus)
);

SELECT geo_maailmajaod.nimetus, geo_maailmajaod.pindala, riiginimi, pealinn, riigipindala
  FROM geo_riigid
  JOIN geo_maailmajaod ON geo_riigid.maailmajagu=geo_maailmajaod.nimetus;
  
SELECT riiginimi, 100*riigipindala/geo_maailmajaod.pindala AS protsent_maailmajaost
  FROM geo_riigid
  JOIN geo_maailmajaod ON geo_riigid.maailmajagu=geo_maailmajaod.nimetus;
  
-- Kuvage, mitu korda on riik väiksem maailmajaost, kus ta paikneb. 

SELECT geo_maailmajaod.pindala, geo_riigid.riigipindala
  FROM geo_riigid
  JOIN geo_maailmajaod ON geo_riigid.maailmajagu=geo_maailmajaod.nimetus
  
  
CREATE TABLE geo_maakonnad(
   maakonnanimi VARCHAR(50) NOT NULL PRIMARY KEY,
   maakonnakeskus VARCHAR(50),
   maakonnapindala INT,
   riik VARCHAR(50),
   FOREIGN KEY(riik) REFERENCES geo_riigid(riiginimi)
);

SELECT * FROM geo_maakonnad 
  JOIN geo_riigid ON geo_maakonnad.riik=geo_riigid.riiginimi
  JOIN geo_maailmajaod ON geo_riigid.maailmajagu=geo_maailmajaod.nimetus;
  
pood_kaubad(id, nimetus, hind)
pood_kliendid(id, perekonnanimi)
pood_ostud(id, kogus, kauba_id, kliendi_id)

Koostage tabelid koos seostega
Vaadake joonist
Lisage mõned andmed
Näidake kõik ostud (koos kauba nimetuse ja kliendi perekonnanimega)

Näidake ostude summa kliendi kaupa

CREATE TABLE pood_kaubad(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nimetus VARCHAR(50),
  hind DECIMAL(8, 2)
);

ALTER TABLE pood_kaubad CHANGE hind hind DECIMAL(8, 2)


CREATE TABLE pood_kliendid(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  perekonnanimi VARCHAR(50)
);

CREATE TABLE pood_ostud(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  kogus INT,
  kauba_id INT,
  kliendi_id INT,
  FOREIGN KEY(kauba_id) REFERENCES pood_kaubad(id),
  FOREIGN KEY(kliendi_id) REFERENCES pood_kliendid(id)  
);

SELECT nimetus, kogus, perekonnanimi, kogus*hind AS summa
  FROM pood_ostud 
  JOIN pood_kaubad ON pood_ostud.kauba_id=pood_kaubad.id
  JOIN pood_kliendid ON pood_ostud.kliendi_id=pood_kliendid.id;


+---------------+-------+---------------+-------+
| nimetus       | kogus | perekonnanimi | summa |
+---------------+-------+---------------+-------+
| Riia peenleib |     2 | Kask          |  1.34 |
| Riia peenleib |     2 | Tamm          |  1.34 |
| Isa sai       |     1 | Tamm          |  0.45 |
+---------------+-------+---------------+-------+
  
  
-- Näidake ostude summa kliendi kaupa
SELECT sum(kogus) AS toodete_arv, perekonnanimi, sum(kogus*hind) AS kogusumma
  FROM pood_ostud 
  JOIN pood_kaubad ON pood_ostud.kauba_id=pood_kaubad.id
  JOIN pood_kliendid ON pood_ostud.kliendi_id=pood_kliendid.id
  GROUP BY perekonnanimi;
  

+-------------+---------------+-----------+
| toodete_arv | perekonnanimi | kogusumma |
+-------------+---------------+-----------+
|           2 | Kask          |      1.34 |
|           3 | Tamm          |      1.79 |
+-------------+---------------+-----------+


-- Näidake ostude arv ja summa toote kaupa
SELECT pood_kaubad.nimetus, sum(kogus) AS toodete_arv, COUNT(kogus) AS ostude_arv, 
       GROUP_CONCAT(perekonnanimi), sum(kogus*hind) AS kogusumma
  FROM pood_ostud 
  JOIN pood_kaubad ON pood_ostud.kauba_id=pood_kaubad.id
  JOIN pood_kliendid ON pood_ostud.kliendi_id=pood_kliendid.id
  GROUP BY pood_kaubad.id;
  

+---------------+-------------+------------+-----------------------------+-----------+
| nimetus       | toodete_arv | ostude_arv | GROUP_CONCAT(perekonnanimi) | kogusumma |
+---------------+-------------+------------+-----------------------------+-----------+
| Riia peenleib |           4 |          2 | Kask,Tamm                   |      2.68 |
| Isa sai       |           1 |          1 | Tamm                        |      0.45 |
+---------------+-------------+------------+-----------------------------+-----------+



SELECT sum(kogus) AS toodete_arv, CONCAT(perekonnanimi, '-', pood_kliendid.id), sum(kogus*hind) AS kogusumma
  FROM pood_ostud 
  JOIN pood_kaubad ON pood_ostud.kauba_id=pood_kaubad.id
  JOIN pood_kliendid ON pood_ostud.kliendi_id=pood_kliendid.id
  GROUP BY pood_kliendid.id;