CREATE TABLE koerad(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	koeranimi VARCHAR(30),
	synniaasta INT
 );
 
 INSERT INTO koerad (koeranimi, synniaasta) VALUES ('Pontu', 2008); 
 INSERT INTO koerad (koeranimi, synniaasta) VALUES ('Muki',  2009); 
 INSERT INTO koerad (koeranimi, synniaasta) VALUES ('Muri',  2007); 
 INSERT INTO koerad (koeranimi, synniaasta) VALUES ('Polla', 2009); 
 INSERT INTO koerad (koeranimi, synniaasta) VALUES ('Juula', 2008);
 
 SELECT * FROM koerad;
 
 mysql> SELECT * FROM koerad;
+----+-----------+------------+
| id | koeranimi | synniaasta |
+----+-----------+------------+
|  1 | Pontu     |       2008 |
|  2 | Muki      |       2009 |
|  3 | Muri      |       2007 |
|  4 | Polla     |       2009 |
|  5 | Juula     |       2008 |
+----+-----------+------------+
5 rows in set (0.00 sec)

 
 UPDATE koerad SET synniaasta=2008 WHERE id=4;
 
 mysql>  UPDATE koerad SET synniaasta=2008 WHERE id=4;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM koerad;
+----+-----------+------------+
| id | koeranimi | synniaasta |
+----+-----------+------------+
|  1 | Pontu     |       2008 |
|  2 | Muki      |       2009 |
|  3 | Muri      |       2007 |
|  4 | Polla     |       2008 |
|  5 | Juula     |       2008 |
+----+-----------+------------+
5 rows in set (0.00 sec)

DELETE FROM koerad WHERE id=4;

mysql> DELETE FROM koerad WHERE id=4;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM koerad;
+----+-----------+------------+
| id | koeranimi | synniaasta |
+----+-----------+------------+
|  1 | Pontu     |       2008 |
|  2 | Muki      |       2009 |
|  3 | Muri      |       2007 |
|  5 | Juula     |       2008 |
+----+-----------+------------+
4 rows in set (0.01 sec)

ALTER TABLE koerad ADD mass_grammides INT DEFAULT 1000;

mysql> UPDATE koerad SET mass_grammides=1000+rand()*2000;
Query OK, 4 rows affected (0.03 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.5294006844324998 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM koerad;
+----+-----------+------------+----------------+
| id | koeranimi | synniaasta | mass_grammides |
+----+-----------+------------+----------------+
|  1 | Pontu     |       2008 |           1818 |
|  2 | Muki      |       2009 |           2653 |
|  3 | Muri      |       2007 |           2812 |
|  5 | Juula     |       2008 |           1099 |
+----+-----------+------------+----------------+
4 rows in set (0.02 sec)

mysql> SELECT COUNT(*) FROM koerad;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT MAX(mass_grammides) FROM koerad;
+---------------------+
| MAX(mass_grammides) |
+---------------------+
|                2812 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MIN(mass_grammides), AVG(mass_grammides) FROM koerad;
+---------------------+---------------------+
| MIN(mass_grammides) | AVG(mass_grammides) |
+---------------------+---------------------+
|                1099 |           2095.5000 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT synniaasta, COUNT(*) FROM koerad GROUP BY synniaasta;
+------------+----------+
| synniaasta | COUNT(*) |
+------------+----------+
|       2007 |        1 |
|       2008 |        2 |
|       2009 |        1 |
+------------+----------+
3 rows in set (0.00 sec)

mysql> SELECT synniaasta, COUNT(*) FROM koerad 
  GROUP BY synniaasta 
    HAVING COUNT(*) > 1;
+------------+----------+
| synniaasta | COUNT(*) |
+------------+----------+
|       2008 |        2 |
+------------+----------+
1 row in set (0.00 sec)

-- Näidake iga aasta kohta suurim koera mass grammides
mysql> SELECT synniaasta, COUNT(*) FROM koerad GROUP BY synniaasta HAVING COUNT(
*) > 1;
+------------+----------+
| synniaasta | COUNT(*) |
+------------+----------+
|       2008 |        2 |
+------------+----------+
1 row in set (0.00 sec)

mysql> INSERT INTO koerad(koeranimi, synniaasta, mass_grammides) VALUES ('Karu',
 2007, 1900);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO koerad(koeranimi, synniaasta, mass_grammides) VALUES ('Kardin
al', 2008, 1870);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT MAX(mass_grammides),  synniaasta FROM koerad GROUP BY synniaasta;
+---------------------+------------+
| MAX(mass_grammides) | synniaasta |
+---------------------+------------+
|                2812 |       2007 |
|                1870 |       2008 |
|                2653 |       2009 |
+---------------------+------------+
3 rows in set (0.00 sec)


Alampäringud

* Alampäringud väärtuse asendajana
* Alampäringud loetelu asendajana 
* Alampäringud tabeli asendajana

mysql> SELECT AVG(mass_grammides) FROM koerad;
+---------------------+
| AVG(mass_grammides) |
+---------------------+
|           2025.3333 |
+---------------------+
1 row in set (0.00 sec)


SELECT * FROM koerad WHERE mass_grammides>2025;

SELECT * FROM koerad WHERE mass_grammides>
               (SELECT AVG(mass_grammides) FROM koerad);

Koerad, kelle mass ületab keskmist massi
			   
mysql> SELECT * FROM koerad WHERE mass_grammides>
    ->                (SELECT AVG(mass_grammides) FROM koerad);
+----+-----------+------------+----------------+
| id | koeranimi | synniaasta | mass_grammides |
+----+-----------+------------+----------------+
|  2 | Muki      |       2009 |           2653 |
|  3 | Muri      |       2007 |           2812 |
+----+-----------+------------+----------------+
2 rows in set (0.01 sec)

-- Leidke koerad, kes on sünniaasta järgi kõige vanemad
   (kelle sünniaasta on vähim, neid võib olla mitu)
   
SELECT * FROM koerad WHERE synniaasta=(SELECT MIN(synniaasta) FROM koerad);  
			   
mysql> SELECT * FROM koerad WHERE synniaasta=(SELECT MIN(synniaasta) FROM koerad);
+----+-----------+------------+----------------+
| id | koeranimi | synniaasta | mass_grammides |
+----+-----------+------------+----------------+
|  3 | Muri      |       2007 |           2812 |
|  6 | Karu      |       2007 |           1900 |
+----+-----------+------------+----------------+
2 rows in set (0.00 sec)

* Alampäringud loetelu asendajana 

-- leian koerad sünniaastatel, kus vähemasti ühe koera mass on üle kahe kilo

mysql> SELECT synniaasta FROM koerad WHERE mass_grammides>2000;
+------------+
| synniaasta |
+------------+
|       2009 |
|       2007 |
+------------+

SELECT * FROM koerad WHERE synniaasta IN 
   (SELECT synniaasta FROM koerad WHERE mass_grammides>2000);

mysql> SELECT * FROM koerad WHERE synniaasta IN
    ->    (SELECT synniaasta FROM koerad WHERE mass_grammides>2000);
+----+-----------+------------+----------------+
| id | koeranimi | synniaasta | mass_grammides |
+----+-----------+------------+----------------+
|  2 | Muki      |       2009 |           2653 |
|  3 | Muri      |       2007 |           2812 |
|  6 | Karu      |       2007 |           1900 |
+----+-----------+------------+----------------+
3 rows in set (0.00 sec)

* Koerad aastatest, kus keegi pole üle kahe kilo

SELECT * FROM koerad WHERE synniaasta NOT IN 
   (SELECT synniaasta FROM koerad WHERE mass_grammides>2000);
   
mysql> SELECT * FROM koerad WHERE synniaasta NOT IN
    ->    (SELECT synniaasta FROM koerad WHERE mass_grammides>2000);
+----+-----------+------------+----------------+
| id | koeranimi | synniaasta | mass_grammides |
+----+-----------+------------+----------------+
|  1 | Pontu     |       2008 |           1818 |
|  5 | Juula     |       2008 |           1099 |
|  7 | Kardinal  |       2008 |           1870 |
+----+-----------+------------+----------------+


* Päringud tabeli asendajana

SELECT koeranimi FROM
 (SELECT * FROM koerad) as tabel1;
   
   
mysql> SELECT koeranimi FROM
    ->  (SELECT * FROM koerad) as tabel1;
+-----------+
| koeranimi |
+-----------+
| Pontu     |
| Muki      |
| Muri      |
| Juula     |
| Karu      |
| Kardinal  |
+-----------+

SELECT synniaasta, COUNT(*) as kogus FROM koerad GROUP BY synniaasta;


mysql> SELECT YEAR(NOW())-synniaasta as vanus, COUNT(*) as kogus, synniaasta FROM koerad 
GROUP BY synniaasta;
+-------+-------+
| vanus | kogus |
+-------+-------+
|     5 |     2 |
|     4 |     3 |
|     3 |     1 |
+-------+-------+

mysql> SELECT YEAR(NOW())-synniaasta as vanus, COUNT(*) as kogus, synniaasta FRO
M koerad
    -> GROUP BY synniaasta;
+-------+-------+------------+
| vanus | kogus | synniaasta |
+-------+-------+------------+
|     5 |     2 |       2007 |
|     4 |     3 |       2008 |
|     3 |     1 |       2009 |
+-------+-------+------------+
3 rows in set (0.00 sec)


SELECT vanus*kogus, synniaasta FROM 
  (SELECT YEAR(NOW())-synniaasta as vanus, COUNT(*) as kogus, synniaasta 
     FROM koerad GROUP BY synniaasta) as tabel1

+-------------+------------+
| vanus*kogus | synniaasta |
+-------------+------------+
|          10 |       2007 |
|          12 |       2008 |
|           3 |       2009 |
+-------------+------------+


* Rekursiivsed alampäringud

(alampäring, mis käivitatakse uuesti iga tabeli rea korral). 

Näiteks: Väljasta kõik koerad, kelle mass ületab temaga samal aastal 
sündinud koerte keskmist massi.

SELECT * FROM koerad as tabel1 WHERE  tabel1.mass_grammides>
  (SELECT AVG(tabel2.mass_grammides) FROM koerad as tabel2
     WHERE tabel1.synniaasta=tabel2.synniaasta);
+----+-----------+------------+----------------+
| id | koeranimi | synniaasta | mass_grammides |
+----+-----------+------------+----------------+
|  1 | Pontu     |       2008 |           1818 |
|  3 | Muri      |       2007 |           2812 |
|  7 | Kardinal  |       2008 |           1870 |
+----+-----------+------------+----------------+
3 rows in set (0.00 sec)



Kaks seotud tabelit

CREATE TABLE peremehed(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  eesnimi VARCHAR(30),
  sugu ENUM('M','N')  
);


INSERT INTO peremehed(eesnimi, sugu) VALUES ('Juku', 'M');
INSERT INTO peremehed(eesnimi, sugu) VALUES ('Kati', 'N');
INSERT INTO peremehed(eesnimi, sugu) VALUES ('Mati', 'M');

ALTER TABLE koerad ADD peremehe_id INT DEFAULT 1;	 
ALTER TABLE koerad ADD FOREIGN KEY(peremehe_id) REFERENCES peremehed(id);
mysql> explain koerad;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| id             | int(11)     | NO   | PRI | NULL    | auto_increment |
| koeranimi      | varchar(30) | YES  |     | NULL    |                |
| synniaasta     | int(11)     | YES  |     | NULL    |                |
| mass_grammides | int(11)     | YES  |     | 1000    |                |
| peremehe_id    | int(11)     | YES  | MUL | 1       |                |
+----------------+-------------+------+-----+---------+----------------+

UPDATE koerad SET peremehe_id=2 WHERE id IN (2, 3);

mysql> SELECT * FROM koerad;
+----+-----------+------------+----------------+-------------+
| id | koeranimi | synniaasta | mass_grammides | peremehe_id |
+----+-----------+------------+----------------+-------------+
|  1 | Pontu     |       2008 |           1818 |           1 |
|  2 | Muki      |       2009 |           2653 |           2 |
|  3 | Muri      |       2007 |           2812 |           2 |
|  5 | Juula     |       2008 |           1099 |           1 |
|  6 | Karu      |       2007 |           1900 |           1 |
|  7 | Kardinal  |       2008 |           1870 |           1 |
+----+-----------+------------+----------------+-------------+

mysql> SELECT * FROM peremehed;
+----+---------+------+
| id | eesnimi | sugu |
+----+---------+------+
|  1 | Juku    | M    |
|  2 | Kati    | N    |
|  3 | Mati    | M    |
+----+---------+------+

SELECT koeranimi, eesnimi
  FROM koerad, peremehed
  WHERE koerad.peremehe_id=peremehed.id;
  
  
mysql> SELECT koeranimi, eesnimi
    ->   FROM koerad, peremehed
    ->   WHERE koerad.peremehe_id=peremehed.id;
+-----------+---------+
| koeranimi | eesnimi |
+-----------+---------+
| Pontu     | Juku    |
| Muki      | Kati    |
| Muri      | Kati    |
| Juula     | Juku    |
| Karu      | Juku    |
| Kardinal  | Juku    |
+-----------+---------+


SELECT koeranimi, eesnimi
  FROM koerad INNER JOIN peremehed
   ON koerad.peremehe_id=peremehed.id

+-----------+---------+
| koeranimi | eesnimi |
+-----------+---------+
| Pontu     | Juku    |
| Muki      | Kati    |
| Muri      | Kati    |
| Juula     | Juku    |
| Karu      | Juku    |
| Kardinal  | Juku    |
+-----------+---------+

SELECT koeranimi, eesnimi
  FROM koerad RIGHT JOIN peremehed
   ON koerad.peremehe_id=peremehed.id  
+-----------+---------+
| koeranimi | eesnimi |
+-----------+---------+
| Pontu     | Juku    |
| Juula     | Juku    |
| Karu      | Juku    |
| Kardinal  | Juku    |
| Muki      | Kati    |
| Muri      | Kati    |
| NULL      | Mati    |
+-----------+---------+
7 rows in set (0.00 sec)


Väljasta koerte ja peremeeste nimed, kus peremehe juures on rohkem koeri 
kui keskmiselt ühe peremehe juures.

select b.eesnimi,
count(*) as koerade_arv,
(select count(*) from koerad) / (select count(*) from peremehed) as keskmine
from koerad a
inner join peremehed b on a.peremehe_id=b.id
group by b.eesnimi having koerade_arv>
(select count(*) from koerad) / (select count(*) from peremehed)


+---------+-------------+----------+
| eesnimi | koerade_arv | keskmine |
+---------+-------------+----------+
| Juku    |           4 |   2.0000 |
+---------+-------------+----------+

CREATE USER kursuslane@localhost IDENTFIED BY kalake1;
mysql> CREATE USER kursuslane@localhost IDENTIFIED BY 'kalake1';
Query OK, 0 rows affected (0.06 sec)
mysql> GRANT ALL PRIVILEGES ON kursusebaas.* TO kursuslane@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)
mysql> quit
Bye

C:\Users\opetaja\Desktop\xampp\mysql\bin>mysql -ukursuslane -pkalake1 kursusebaas