pealaod (id, aadress, telefon)

poed(id, juhataja, pealao_id)

poed.lao_id -> pealaod.id

CREATE TABLE pealaod(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  aadress VARCHAR(50),
  telefon VARCHAR(20)
);


CREATE TABLE poed(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  juhataja VARCHAR(20),
  pealao_id INT,
  FOREIGN KEY(pealao_id) REFERENCES pealaod(id)
);


INSERT INTO pealaod VALUES(DEFAULT, 'Valga, Turu tn. 2', '5534776');
INSERT INTO pealaod VALUES(DEFAULT, 'Võru, Jaama tn. 6', '5534774');
INSERT INTO pealaod VALUES(DEFAULT, 'Viljandi, Jaama tn. 8', '5534772');

+----+-----------------------+---------+
| id | aadress               | telefon |
+----+-----------------------+---------+
|  1 | Valga, Turu tn. 2     | 5534776 |
|  2 | Võru, Jaama tn. 6     | 5534774 |
|  3 | Viljandi, Jaama tn. 8 | 5534772 |
+----+-----------------------+---------+

INSERT INTO poed VALUES(DEFAULT, 'Tamm', 1);
INSERT INTO poed VALUES(DEFAULT, 'Kask', 1);
INSERT INTO poed VALUES(DEFAULT, 'Saar', 2);


+----+----------+-----------+
| id | juhataja | pealao_id |
+----+----------+-----------+
|  1 | Tamm     |         1 |
|  2 | Kask     |         1 |
|  3 | Saar     |         2 |
+----+----------+-----------+

INSERT INTO poed VALUES(DEFAULT, 'Saar', 7);


ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`if17_jaagup_1`.`poed`, CONSTRAINT `poed_ibfk_1` FOREIGN KEY (`pealao_id`) REFERENCES `pealaod` (`id`))


DELETE FROM pealaod WHERE id=1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai   nt fails (`if17_jaagup_1`.`poed`, CONSTRAINT `poed_ibfk_1` FOREIGN KEY (`pealao_   id`) REFERENCES `pealaod` (`id`))


SELECT * FROM poed
  INNER JOIN pealaod ON poed.pealao_id=pealaod.id;
  
+----+----------+-----------+----+--------------------+---------+
| id | juhataja | pealao_id | id | aadress            | telefon |
+----+----------+-----------+----+--------------------+---------+
|  1 | Tamm     |         1 |  1 | Valga, Turu tn. 2  | 5534776 |
|  2 | Kask     |         1 |  1 | Valga, Turu tn. 2  | 5534776 |
|  3 | Saar     |         2 |  2 | Võru, Jaama tn. 6  | 5534774 |
+----+----------+-----------+----+--------------------+---------+

SELECT juhataja, aadress, telefon FROM poed
  INNER JOIN pealaod ON poed.pealao_id=pealaod.id;


+----------+--------------------+---------+
| juhataja | aadress            | telefon |
+----------+--------------------+---------+
| Tamm     | Valga, Turu tn. 2  | 5534776 |
| Kask     | Valga, Turu tn. 2  | 5534776 |
| Saar     | Võru, Jaama tn. 6  | 5534774 |
+----------+--------------------+---------+

SELECT id, juhataja, aadress, telefon FROM poed
  INNER JOIN pealaod ON poed.pealao_id=pealaod.id;

ERROR 1052 (23000): Column 'id' in field list is ambiguous

SELECT poed.id, juhataja, aadress, telefon FROM poed
  INNER JOIN pealaod ON poed.pealao_id=pealaod.id;
  

+----+----------+--------------------+---------+
| id | juhataja | aadress            | telefon |
+----+----------+--------------------+---------+
|  1 | Tamm     | Valga, Turu tn. 2  | 5534776 |
|  2 | Kask     | Valga, Turu tn. 2  | 5534776 |
|  3 | Saar     | Võru, Jaama tn. 6  | 5534774 |
+----+----------+--------------------+---------+

SELECT poed.id AS poeid, juhataja, pealaod.id AS laoid, aadress, telefon FROM poed
  INNER JOIN pealaod ON poed.pealao_id=pealaod.id;
  

+-------+----------+-------+--------------------+---------+
| poeid | juhataja | laoid | aadress            | telefon |
+-------+----------+-------+--------------------+---------+
|     1 | Tamm     |     1 | Valga, Turu tn. 2  | 5534776 |
|     2 | Kask     |     1 | Valga, Turu tn. 2  | 5534776 |
|     3 | Saar     |     2 | Võru, Jaama tn. 6  | 5534774 |
+-------+----------+-------+--------------------+---------+


SELECT * FROM pealaod
  INNER JOIN poed ON poed.pealao_id=pealaod.id;
  
+----+--------------------+---------+----+----------+-----------+
| id | aadress            | telefon | id | juhataja | pealao_id |
+----+--------------------+---------+----+----------+-----------+
|  1 | Valga, Turu tn. 2  | 5534776 |  1 | Tamm     |         1 |
|  1 | Valga, Turu tn. 2  | 5534776 |  2 | Kask     |         1 |
|  2 | Võru, Jaama tn. 6  | 5534774 |  3 | Saar     |         2 |
+----+--------------------+---------+----+----------+-----------+

SELECT * FROM pealaod
  LEFT JOIN poed ON poed.pealao_id=pealaod.id;

+----+-----------------------+---------+------+----------+-----------+
| id | aadress               | telefon | id   | juhataja | pealao_id |
+----+-----------------------+---------+------+----------+-----------+
|  1 | Valga, Turu tn. 2     | 5534776 |    1 | Tamm     |         1 |
|  1 | Valga, Turu tn. 2     | 5534776 |    2 | Kask     |         1 |
|  2 | Võru, Jaama tn. 6     | 5534774 |    3 | Saar     |         2 |
|  3 | Viljandi, Jaama tn. 8 | 5534772 | NULL | NULL     |      NULL |
+----+-----------------------+---------+------+----------+-----------+
  
SELECT * FROM pealaod, poed WHERE poed.pealao_id=pealaod.id;
 
+----+--------------------+---------+----+----------+-----------+
| id | aadress            | telefon | id | juhataja | pealao_id |
+----+--------------------+---------+----+----------+-----------+
|  1 | Valga, Turu tn. 2  | 5534776 |  1 | Tamm     |         1 |
|  1 | Valga, Turu tn. 2  | 5534776 |  2 | Kask     |         1 |
|  2 | Võru, Jaama tn. 6  | 5534774 |  3 | Saar     |         2 |
+----+--------------------+---------+----+----------+-----------+


SELECT * FROM pealaod, poed;
+----+-----------------------+---------+----+----------+-----------+
| id | aadress               | telefon | id | juhataja | pealao_id |
+----+-----------------------+---------+----+----------+-----------+
|  1 | Valga, Turu tn. 2     | 5534776 |  1 | Tamm     |         1 |
|  2 | Võru, Jaama tn. 6     | 5534774 |  1 | Tamm     |         1 |
|  3 | Viljandi, Jaama tn. 8 | 5534772 |  1 | Tamm     |         1 |
|  1 | Valga, Turu tn. 2     | 5534776 |  2 | Kask     |         1 |
|  2 | Võru, Jaama tn. 6     | 5534774 |  2 | Kask     |         1 |
|  3 | Viljandi, Jaama tn. 8 | 5534772 |  2 | Kask     |         1 |
|  1 | Valga, Turu tn. 2     | 5534776 |  3 | Saar     |         2 |
|  2 | Võru, Jaama tn. 6     | 5534774 |  3 | Saar     |         2 |
|  3 | Viljandi, Jaama tn. 8 | 5534772 |  3 | Saar     |         2 |
+----+-----------------------+---------+----+----------+-----------+


CREATE TABLE tarned(
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   lao_id INT,
   poe_id INT,
   summa DECIMAL(8, 2),
   aeg DATE,
   FOREIGN KEY(lao_id) REFERENCES pealaod(id),
   FOREIGN KEY(poe_id) REFERENCES poed(id)
);


INSERT INTO tarned VALUES (DEFAULT, 1, 1, 300, NOW());
INSERT INTO tarned VALUES (DEFAULT, 1, 1, 200, DATE(NOW()));
INSERT INTO tarned VALUES (DEFAULT, 1, 2, 150, DATE(NOW()));
INSERT INTO tarned VALUES (DEFAULT, 1, 3, 150, DATE(NOW()));
--INSERT INTO tarned VALUES (DEFAULT, 1, 4, 150, DATE(NOW()));
INSERT INTO tarned VALUES (DEFAULT, 2, 2, 150, DATE(NOW()));
INSERT INTO tarned VALUES (DEFAULT, 2, 2, 150, DATE(NOW()));
INSERT INTO tarned VALUES (DEFAULT, 3, 2, 150, DATE(NOW()));

SELECT * FROM tarned;

SELECT * FROM tarned
  INNER JOIN pealaod ON tarned.lao_id=pealaod.id;
  

+----+--------+--------+--------+------------+----+-----------------------+---------+
| id | lao_id | poe_id | summa  | aeg        | id | aadress               | telefon |
+----+--------+--------+--------+------------+----+-----------------------+---------+
|  1 |      1 |      1 | 300.00 | 2017-10-17 |  1 | Valga, Turu tn. 2     | 5534776 |
|  2 |      1 |      1 | 200.00 | 2017-10-17 |  1 | Valga, Turu tn. 2     | 5534776 |
|  3 |      1 |      2 | 150.00 | 2017-10-17 |  1 | Valga, Turu tn. 2     | 5534776 |
|  4 |      1 |      3 | 150.00 | 2017-10-17 |  1 | Valga, Turu tn. 2     | 5534776 |
|  6 |      2 |      2 | 150.00 | 2017-10-17 |  2 | Võru, Jaama tn. 6     | 5534774 |
|  7 |      2 |      2 | 150.00 | 2017-10-17 |  2 | Võru, Jaama tn. 6     | 5534774 |
|  8 |      3 |      2 | 150.00 | 2017-10-17 |  3 | Viljandi, Jaama tn. 8 | 5534772 |
+----+--------+--------+--------+------------+----+-----------------------+---------+
 
SELECT aadress, juhataja, summa FROM tarned
  INNER JOIN pealaod ON tarned.lao_id=pealaod.id
  INNER JOIN poed ON tarned.poe_id=poed.id;

+-----------------------+----------+--------+
| aadress               | juhataja | summa  |
+-----------------------+----------+--------+
| Valga, Turu tn. 2     | Tamm     | 300.00 |
| Valga, Turu tn. 2     | Tamm     | 200.00 |
| Valga, Turu tn. 2     | Kask     | 150.00 |
| Valga, Turu tn. 2     | Saar     | 150.00 |
| Võru, Jaama tn. 6     | Kask     | 150.00 |
| Võru, Jaama tn. 6     | Kask     | 150.00 |
| Viljandi, Jaama tn. 8 | Kask     | 150.00 |
+-----------------------+----------+--------+

-- Näidake iga poe kohta kogusumma, palju sinna on tarnitud
-- Näidake iga lao kohta kogusumma, palju sinna on tarnitud

SELECT SUM(summa) FROM tarned WHERE poe_id=1;
+------------+
| SUM(summa) |
+------------+
| 500.00     |
+------------+

SELECT poe_id, SUM(summa) FROM tarned GROUP BY poe_id;

+--------+------------+
| poe_id | SUM(summa) |
+--------+------------+
|      1 | 500.00     |
|      2 | 600.00     |
|      3 | 150.00     |
+--------+------------+

SELECT poe_id, juhataja, SUM(summa) 
  FROM tarned 
  INNER JOIN poed ON tarned.poe_id=poed.id
  GROUP BY poe_id;

+--------+----------+------------+
| poe_id | juhataja | SUM(summa) |
+--------+----------+------------+
|      1 | Tamm     | 500.00     |
|      2 | Kask     | 600.00     |
|      3 | Saar     | 150.00     |
+--------+----------+------------+

SELECT poe_id, lao_id, summa FROM tarned;

SELECT poe_id, lao_id, SUM(summa), COUNT(*) 
  FROM tarned 
  GROUP BY poe_id, lao_id;
+--------+--------+------------+----------+
| poe_id | lao_id | SUM(summa) | COUNT(*) |
+--------+--------+------------+----------+
|      1 |      1 | 500.00     |        2 |
|      2 |      1 | 150.00     |        1 |
|      2 |      2 | 300.00     |        2 |
|      2 |      3 | 150.00     |        1 |
|      3 |      1 | 150.00     |        1 |
+--------+--------+------------+----------+

Lisage ka lao aadress ja poe juhataja

SELECT juhataja, aadress, SUM(summa), COUNT(*) 
  FROM tarned 
  INNER JOIN poed ON tarned.poe_id=poed.id
  INNER JOIN pealaod ON tarned.lao_id=pealaod.id
  GROUP BY poe_id, lao_id;  

+----------+-----------------------+------------+----------+
| juhataja | aadress               | SUM(summa) | COUNT(*) |
+----------+-----------------------+------------+----------+
| Tamm     | Valga, Turu tn. 2     | 500.00     |        2 |
| Kask     | Valga, Turu tn. 2     | 150.00     |        1 |
| Kask     | Võru, Jaama tn. 6     | 300.00     |        2 |
| Kask     | Viljandi, Jaama tn. 8 | 150.00     |        1 |
| Saar     | Valga, Turu tn. 2     | 150.00     |        1 |
+----------+-----------------------+------------+----------+

SET @loendur=0;
SELECT @loendur;
INSERT INTO tarned VALUES (DEFAULT, 3, 2, 175, DATE(NOW()));
SELECT * FROM tarned ORDER BY summa;
+----+--------+--------+--------+------------+
| id | lao_id | poe_id | summa  | aeg        |
+----+--------+--------+--------+------------+
|  3 |      1 |      2 | 150.00 | 2017-10-17 |
|  4 |      1 |      3 | 150.00 | 2017-10-17 |
|  6 |      2 |      2 | 150.00 | 2017-10-17 |
|  7 |      2 |      2 | 150.00 | 2017-10-17 |
|  8 |      3 |      2 | 150.00 | 2017-10-17 |
|  9 |      3 |      2 | 175.00 | 2017-10-17 |
|  2 |      1 |      1 | 200.00 | 2017-10-17 |
|  1 |      1 |      1 | 300.00 | 2017-10-17 |
+----+--------+--------+--------+------------+

SET @loendur=0;
SELECT @loendur:=@loendur+1 AS nr, lao_id, poe_id, summa FROM tarned ORDER BY summa;

+------+--------+--------+--------+
| nr   | lao_id | poe_id | summa  |
+------+--------+--------+--------+
|    1 |      1 |      2 | 150.00 |
|    2 |      1 |      3 | 150.00 |
|    3 |      2 |      2 | 150.00 |
|    4 |      2 |      2 | 150.00 |
|    5 |      3 |      2 | 150.00 |
|    6 |      3 |      2 | 175.00 |
|    7 |      1 |      1 | 200.00 |
|    8 |      1 |      1 | 300.00 |
+------+--------+--------+--------+

--mediaansumma

SET @loendur=0;
SELECT summa FROM
(SELECT @loendur:=@loendur+1 AS nr, lao_id, poe_id, summa FROM tarned 
  ORDER BY summa) AS vahetabel
  WHERE nr=(SELECT ROUND(COUNT(*)/2) FROM tarned);

+--------+
| summa  |
+--------+
| 150.00 |
+--------+

SET @eelminepood=-1; -- olematu pood
SELECT @loendur:=IF(poe_id=@eelminepood, @loendur+1, 1), @eelminepood:=poe_id,
  summa FROM tarned
  ORDER BY poe_id, summa;

+--------------------------------------------------+----------------------+--------+
| @loendur:=IF(poe_id=@eelminepood, @loendur+1, 1) | @eelminepood:=poe_id | summa  |
+--------------------------------------------------+----------------------+--------+
|                                                1 |                    1 | 200.00 |
|                                                2 |                    1 | 300.00 |
|                                                1 |                    2 | 150.00 |
|                                                2 |                    2 | 150.00 |
|                                                3 |                    2 | 150.00 |
|                                                4 |                    2 | 150.00 |
|                                                5 |                    2 | 175.00 |
|                                                1 |                    3 | 150.00 |
+--------------------------------------------------+----------------------+--------+

SET @eelminepood=-1; -- olematu pood
SELECT * FROM 
(SELECT @loendur:=IF(poe_id=@eelminepood, @loendur+1, 1) AS nr, @eelminepood:=poe_id,
  summa FROM tarned
  ORDER BY poe_id, summa) AS vahetabel
  WHERE vahetabel.nr<=2;

+------+----------------------+--------+
| nr   | @eelminepood:=poe_id | summa  |
+------+----------------------+--------+
|    1 |                    1 | 200.00 |
|    2 |                    1 | 300.00 |
|    1 |                    2 | 150.00 |
|    2 |                    2 | 150.00 |
|    1 |                    3 | 150.00 |
+------+----------------------+--------+