Algus

CREATE TABLE malelaud(
  malend CHAR(1),
  veerg CHAR(1),
  rida INT,
  toon CHAR(1),
  PRIMARY KEY(veerg, rida)
);

EXPLAIN malelaud;
INSERT INTO malelaud VALUES('v', 'a', 1, 'v');
INSERT INTO malelaud VALUES('r', 'b', 1, 'v');
INSERT INTO malelaud VALUES('o', 'c', 1, 'v');
INSERT INTO malelaud VALUES('l', 'd', 1, 'v');
INSERT INTO malelaud VALUES('k', 'e', 1, 'v');
INSERT INTO malelaud VALUES('o', 'f', 1, 'v');
INSERT INTO malelaud VALUES('r', 'g', 1, 'v');
INSERT INTO malelaud VALUES('v', 'h', 1, 'v');

 SELECT * FROM malelaud;
 SELECT malend, veerg FROM malelaud;
DELETE FROM malelaud WHERE veerg='h' AND rida=1;
INSERT INTO malelaud VALUES('v', 'h', 3, 'v');
UPDATE malelaud SET rida=4 WHERE rida=1 AND veerg='a';

--Liigutage valge oda kaks kohta diagonaalis vasakule
UPDATE malelaud SET rida=3, veerg='d' WHERE rida=1 AND veerg='f';

ALTER TABLE malelaud ADD kommentaar VARCHAR(30);
--Märkige kommentaaris, milline on valge oda
UPDATE malelaud SET kommentaar='valgel ruudul oda' WHERE rida=3 AND veerg='d';

ALTER TABLE malelaud CHANGE kommentaar kommentaarid VARCHAR(40);
ALTER TABLE malelaud CHANGE kommentaarid kommentaarid VARCHAR(4);
ALTER TABLE malelaud DROP kommentaarid;
DROP TABLE malelaud;

SELECT COUNT(*) FROM malelaud;
SELECT COUNT(*) FROM malelaud WHERE malend='v';
SELECT COUNT(*) FROM malelaud GROUP BY malend;
SELECT malend, COUNT(*) FROM malelaud GROUP BY malend;

+--------+----------+
| malend | COUNT(*) |
+--------+----------+
| k      |        1 |
| l      |        1 |
| o      |        2 |
| r      |        2 |
| v      |        2 |
+--------+----------+

SELECT malend, COUNT(*) FROM malelaud GROUP BY malend HAVING COUNT(*)>1; 

+--------+----------+
| malend | COUNT(*) |
+--------+----------+
| o      |        2 |
| r      |        2 |
| v      |        2 |
+--------+----------+

Ülesanne:

Koosta tabel lammaste tarbeks. 
Igal lambal on lambanimi (primaarvõti), mass (kg, DOUBLE) ja toon
Lisage kümmekond lammast
Kuvage mustad lambad
Lugege kokku mustad lambad
Suurendage konkreetse lamba massi 1 kg võrra
Suurendage kõikide lammaste massi 0.5 kg võrra
Näidake iga tooni kohta, mitu lammast sellest toonist on
Näidake neid toone, millest on igaühest vaid üks lammas

Kontrollaeg 16.15


CREATE TABLE lambad(
   lambanimi VARCHAR(15),
   masskg DOUBLE,
   toon VARCHAR(15)
);

INSERT INTO lambad VALUES ('Jossif', 35, 'valge');
INSERT INTO lambad VALUES ('Nikita', 30, 'valge');
INSERT INTO lambad VALUES ('Adolf', 40, 'must');
INSERT INTO lambad VALUES ('Mao', 45, 'must');
INSERT INTO lambad VALUES ('Winston', 45, 'must');

SELECT AVG(masskg) FROM lambad;
+-------------+
| AVG(masskg) |
+-------------+
|          39 |
+-------------+

SELECT * FROM lambad WHERE masskg<39;
+-----------+--------+-------+
| lambanimi | masskg | toon  |
+-----------+--------+-------+
| Jossif    |     35 | valge |
| Nikita    |     30 | valge |
+-----------+--------+-------+

SELECT * FROM lambad WHERE masskg<(SELECT AVG(masskg) FROM lambad);

SELECT SUM(masskg) FROM lambad;

--milline iga lamba osakaal viie lamba kogumassist
SELECT lambanimi, masskg, masskg/(SELECT SUM(masskg) FROM lambad) FROM lambad;

+-----------+--------------------+-----------------------------------------+
| lambanimi | masskg             | masskg/(SELECT SUM(masskg) FROM lambad) |
+-----------+--------------------+-----------------------------------------+
| Jossif    | 35.848405408531804 |                     0.18068215328805134 |
| Nikita    |  30.95791502199873 |                      0.1560332372873689 |
| Adolf     |  40.24435894586552 |                      0.2028385181759202 |
| Mao       |   45.3480497247987 |                     0.22856200096811577 |
| Winston   | 46.007171847864214 |                     0.23188409028054377 |
+-----------+--------------------+-----------------------------------------+

--Väljastage suhteline mass protsentides
SELECT lambanimi,  ROUND(100*masskg/(SELECT SUM(masskg) FROM lambad)) AS protsent FROM lambad;
SELECT lambanimi, CONCAT(masskg, ' kg') FROM lambad;

SELECT lambanimi, IF(masskg<40, CONCAT(masskg * 1000, ' g'), CONCAT(masskg, ' kg')) FROM lambad;

+-----------+-------------------------------------------------------------------+
| lambanimi | IF(masskg<40, CONCAT(masskg * 1000, ' g'), CONCAT(masskg, ' kg')) |
+-----------+-------------------------------------------------------------------+
| Jossif    | 35848.405408531806 g                                              |
| Nikita    | 30957.91502199873 g                                               |
| Adolf     | 40.24435894586552 kg                                              |
| Mao       | 45.3480497247987 kg                                               |
| Winston   | 46.007171847864214 kg                                             |
+-----------+-------------------------------------------------------------------+

mysql> SELECT AVG(masskg), SUM(masskg), MIN(masskg), MAX(masskg), COUNT(*), toon FROM lambad GROUP BY toon;
+--------------------+--------------------+-------------------+--------------------+----------+-------+
| AVG(masskg)        | SUM(masskg)        | MIN(masskg)       | MAX(masskg)        | COUNT(*) | toon  |
+--------------------+--------------------+-------------------+--------------------+----------+-------+
| 43.866526839509476 | 131.59958051852843 | 40.24435894586552 | 46.007171847864214 |        3 | must  |
|  33.40316021526527 |  66.80632043053053 | 30.95791502199873 | 35.848405408531804 |        2 | valge |
+--------------------+--------------------+-------------------+--------------------+----------+-------+


SELECT ROUND(masskg, 2) AS ymass, toon FROM lambad;


+-------+-------+
| ymass | toon  |
+-------+-------+
| 35.85 | valge |
| 30.96 | valge |
| 40.24 | must  |
| 45.35 | must  |
| 46.01 | must  |
+-------+-------+

SELECT * FROM (SELECT ROUND(masskg, 2) AS ymass, toon FROM lambad) AS tabel1;

+-------+-------+
| ymass | toon  |
+-------+-------+
| 35.85 | valge |
| 30.96 | valge |
| 40.24 | must  |
| 45.35 | must  |
| 46.01 | must  |
+-------+-------+

SELECT SUM(ymass), AVG(ymass), MIN(ymass), MAX(ymass), COUNT(*), toon FROM 
   (SELECT ROUND(masskg, 2) AS ymass, toon FROM lambad) AS tabel1 
  GROUP BY toon;


+-------------------+------------+------------+------------+----------+-------+
| SUM(tabel1.ymass) | AVG(ymass) | MIN(ymass) | MAX(ymass) | COUNT(*) | toon  |
+-------------------+------------+------------+------------+----------+-------+
|            131.60 |  43.866667 |      40.24 |      46.01 |        3 | must  |
|             66.81 |  33.405000 |      30.96 |      35.85 |        2 | valge |
+-------------------+------------+------------+------------+----------+-------+


SELECT lambanimi, masskg FROM lambad;

SELECT lambanimi, masskg/(SELECT SUM(masskg) FROM lambad), toon FROM lambad;

SELECT lambanimi, masskg,
           masskg/(
             SELECT SUM(masskg) FROM lambad AS tabel2 WHERE tabel2.toon=tabel1.toon               
             ) AS osagrupist, toon FROM lambad AS tabel1;

             
SELECT lambanimi, masskg,
           masskg - (
             SELECT AVG(masskg) FROM lambad AS tabel2 WHERE tabel2.toon=tabel1.toon               
             ) AS vahekeskmisega, toon FROM lambad AS tabel1;
             
             
             
<!doctype html>
<html>
  <head>
    <title>Andmed baasist</title>
  </head>
  <body>
    <table>
    <?php
       $yhendus=new mysqli("localhost", "jaagup", "xxxxxx", "jaagup_2015");
       $kask=$yhendus->prepare("SELECT lambanimi, masskg,
           masskg - (
             SELECT AVG(masskg) FROM lambad AS tabel2 WHERE tabel2.toon=tabel1.toon               
             ) AS vahekeskmisega, toon FROM lambad AS tabel1;");
       $kask->bind_result($lambanimi, $mass, $vahe, $toon);
       $kask->execute();
       while($kask->fetch()){
          echo "
             <tr>
               <td>$lambanimi</td>
               <td>$mass</td>
               <td>$vahe</td>
               <td>$toon</td>
             </tr>
          ";           
       }
       $yhendus->close();
    ?>
    </table>
  </body>
</html>