Agregaatfunktsioonid

CREATE TABLE osalejad(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  eesnimi VARCHAR(30),
  asula   VARCHAR(30),
  synniaasta INT
);

mysql> EXPLAIN osalejad;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| eesnimi    | varchar(30) | YES  |     | NULL    |                |
| asula      | varchar(30) | YES  |     | NULL    |                |
| synniaasta | int(11)     | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


INSERT INTO osalejad (eesnimi, asula, synniaasta) VALUES ('Märt', 'Märjamaa', 1993);
INSERT INTO osalejad (eesnimi, asula, synniaasta) VALUES ('Mari', 'Märjamaa', 1995);
INSERT INTO osalejad (eesnimi, asula, synniaasta) VALUES ('Johannes', 'Valga', 1935);
INSERT INTO osalejad (eesnimi, asula, synniaasta) VALUES ('Leida', 'Valga', 1938);
INSERT INTO osalejad (eesnimi, asula, synniaasta) VALUES ('Vaike', 'Valga', 1959);
INSERT INTO osalejad (eesnimi, asula, synniaasta) VALUES ('Siim', 'Haapsalu', 1995);





mysql> SELECT * FROM osalejad;
+----+----------+----------+------------+
| id | eesnimi  | asula    | synniaasta |
+----+----------+----------+------------+
|  1 | Märt     | Märjamaa |       1993 |
|  2 | Mari     | Märjamaa |       1995 |
|  3 | Johannes | Valga    |       1935 |
|  4 | Leida    | Valga    |       1938 |
|  5 | Vaike    | Valga    |       1959 |
|  6 | Siim     | Haapsalu |       1995 |
+----+----------+----------+------------+


mysql> SELECT COUNT(*) FROM osalejad;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+


mysql> SELECT COUNT(*) as kokku FROM osalejad;
+-------+
| kokku |
+-------+
|     6 |
+-------+


mysql> SELECT COUNT(*) as kokku FROM osalejad WHERE asula='Valga';
+-------+
| kokku |
+-------+
|     3 |
+-------+


mysql> SELECT DISTINCT(asula) FROM osalejad;
+----------+
| asula    |
+----------+
| Märjamaa |
| Valga    |
| Haapsalu |
+----------+



mysql> SELECT MIN(synniaasta) FROM osalejad;
+-----------------+
| MIN(synniaasta) |
+-----------------+
|            1935 |
+-----------------+


mysql> SELECT MAX(synniaasta) FROM osalejad;
+-----------------+
| MAX(synniaasta) |
+-----------------+
|            1995 |
+-----------------+
1 row in set (0.00 sec)


mysql> SELECT AVG(synniaasta) FROM osalejad;
+-----------------+
| AVG(synniaasta) |
+-----------------+
|       1969.1667 |
+-----------------+
1 row in set (0.05 sec)


mysql> SELECT GROUP_CONCAT(eesnimi) FROM osalejad;
+-------------------------------------+
| GROUP_CONCAT(eesnimi)               |
+-------------------------------------+
| Märt,Mari,Johannes,Leida,Vaike,Siim |
+-------------------------------------+



mysql> SELECT asula, COUNT(*) FROM osalejad GROUP BY asula;
+----------+----------+
| asula    | COUNT(*) |
+----------+----------+
| Haapsalu |        1 |
| Märjamaa |        2 |
| Valga    |        3 |
+----------+----------+


-- leidke iga asula kohta vähim sünniaasta (neist, kes tabelis on)

-- leidke iga asula kohta seal olevate inimeste loetelu

-- pärast 1980. aastat sündinud inimeste loetelu asulate kaupa

SELECT asula, GROUP_CONCAT(eesnimi) FROM osalejad
  WHERE synniaasta>1980
  GROUP BY asula;

  mysql> SELECT asula, GROUP_CONCAT(eesnimi) FROM osalejad
    ->   WHERE synniaasta>1980
    ->   GROUP BY asula;
+----------+-----------------------+
| asula    | GROUP_CONCAT(eesnimi) |
+----------+-----------------------+
| Haapsalu | Siim                  |
| Märjamaa | Märt,Mari             |
+----------+-----------------------+
2 rows in set (0.00 sec)


-- võrreldes eelmisega eesnimed koos sünniaastatega

SELECT asula, GROUP_CONCAT(CONCAT(eesnimi, ' ', synniaasta)) FROM osalejad
  WHERE synniaasta>1980
  GROUP BY asula;

+----------+------------------------------------------------+
| asula    | GROUP_CONCAT(CONCAT(eesnimi, ' ', synniaasta)) |
+----------+------------------------------------------------+
| Haapsalu | Siim 1995                                      |
| Märjamaa | Märt 1993,Mari 1995                            |
+----------+------------------------------------------------+


SELECT asula, GROUP_CONCAT(CONCAT(eesnimi, ' ', synniaasta) SEPARATOR '; ') 
  FROM osalejad
  WHERE synniaasta>1980
  GROUP BY asula;
  
+----------+---------------------------------------------------------------+
| asula    | GROUP_CONCAT(CONCAT(eesnimi, ' ', synniaasta) SEPARATOR '; ') |
+----------+---------------------------------------------------------------+
| Haapsalu | Siim 1995                                                     |
| Märjamaa | Märt 1993; Mari 1995                                          |
+----------+---------------------------------------------------------------+



Alampäringud


Alampäringud väärtuse asendajana


mysql> SELECT AVG(synniaasta) FROM osalejad;
+-----------------+
| AVG(synniaasta) |
+-----------------+
|       1969.1667 |
+-----------------+


-- keskmisest nooremad

SELECT eesnimi, synniaasta FROM osalejad WHERE synniaasta > 1969;

+---------+------------+
| eesnimi | synniaasta |
+---------+------------+
| Märt    |       1993 |
| Mari    |       1995 |
| Siim    |       1995 |
+---------+------------+


-- sama ühe päringuga

SELECT eesnimi, synniaasta FROM osalejad WHERE synniaasta > 
  (SELECT AVG(synniaasta) FROM osalejad);


+---------+------------+
| eesnimi | synniaasta |
+---------+------------+
| Märt    |       1993 |
| Mari    |       1995 |
| Siim    |       1995 |
+---------+------------+


--Väljasta osalejad, kelle sünniaasta on võrdne vanima osaleja sünniaastaga

SELECT MIN(synniaasta) FROM osalejad;

SELECT eesnimi, synniaasta FROM osalejad WHERE synniaasta=
  (SELECT MIN(synniaasta) FROM osalejad);
  

--Väljasta osalejad, kelle sünniaasta on võrdne noorima osaleja sünniaastaga
  

--Väljasta osalejad, kes on vanimad või vanimast kuni kümme aastat nooremad.
SELECT eesnimi, synniaasta FROM osalejad WHERE synniaasta<=
  (SELECT MIN(synniaasta)+10 FROM osalejad);

+----------+------------+
| eesnimi  | synniaasta |
+----------+------------+
| Johannes |       1935 |
| Leida    |       1938 |
+----------+------------+

-- Väljasta osalejad, kes jäävad sünniaastate keskmisesse kolmandikku
-- nt. kui vanim on sündinud 1950 ja noorim sündinud 2010, siis väljastage 
-- 1970-1990 sündinud

SELECT eesnimi, synniaasta FROM osalejad WHERE 
  synniaasta>=
    (SELECT MIN(synniaasta)+(MAX(synniaasta)-MIN(synniaasta))/3 FROM osalejad) 
  AND synniaasta<=
    (SELECT MAX(synniaasta)-(MAX(synniaasta)-MIN(synniaasta))/3 FROM osalejad);

    
+---------+------------+
| eesnimi | synniaasta |
+---------+------------+
| Vaike   |       1959 |
+---------+------------+



Alampäringud loetelu asendajana

SELECT * FROM osalejad WHERE synniaasta IN (1935, 1955, 1975, 1995);

+----+----------+----------+------------+
| id | eesnimi  | asula    | synniaasta |
+----+----------+----------+------------+
|  2 | Mari     | Märjamaa |       1995 |
|  3 | Johannes | Valga    |       1935 |
|  6 | Siim     | Haapsalu |       1995 |
+----+----------+----------+------------+


-- paarituarvulised sünniaastad osalejate tabelist

SELECT DISTINCT synniaasta FROM osalejad WHERE synniaasta % 2 =1;

+------------+
| synniaasta |
+------------+
|       1993 |
|       1995 |
|       1935 |
|       1959 |
+------------+


-- Asulate erinevad algustähed

SELECT DISTINCT LEFT(asula, 1) FROM osalejad;
+----------------+
| LEFT(asula, 1) |
+----------------+
| M              |
| V              |
| H              |
+----------------+


-- Kõik eesnimed, mis algavad ükskõik millise loetelus oleva asula algustähega

SELECT eesnimi FROM osalejad WHERE LEFT(eesnimi, 1) IN
  (SELECT DISTINCT LEFT(asula, 1) FROM osalejad);

  
+---------+
| eesnimi |
+---------+
| Märt    |
| Mari    |
| Vaike   |
+---------+
  

  
Alampäringud tabeli asendajana


SELECT asula, MIN(synniaasta) as vanimaasta FROM osalejad GROUP BY asula;


+----------+------------+
| asula    | vanimaasta |
+----------+------------+
| Haapsalu |       1995 |
| Märjamaa |       1993 |
| Valga    |       1935 |
+----------+------------+

 
SELECT asula, vanimaasta-1900 as kahekohaline_vanima_synniaasta FROM 
  (SELECT asula, MIN(synniaasta) as vanimaasta FROM osalejad GROUP BY asula) 
  AS tabel1;

+----------+--------------------------------+
| asula    | kahekohaline_vanima_synniaasta |
+----------+--------------------------------+
| Haapsalu |                             95 |
| Märjamaa |                             93 |
| Valga    |                             35 |
+----------+--------------------------------+



Rekursiivsed alampäringud

-- Leia iga asula kohta inimesed, kelle sünniaasta on suurem või võrdne
-- kui sealt asulast tulevate osalejate keskmine sünniaasta.



SELECT eesnimi, asula, synniaasta FROM osalejad AS tabel1 WHERE synniaasta <=
  (SELECT AVG(synniaasta) FROM osalejad AS tabel2 
    WHERE tabel2.asula=tabel1.asula);


+----------+----------+------------+
| eesnimi  | asula    | synniaasta |
+----------+----------+------------+
| Märt     | Märjamaa |       1993 |
| Johannes | Valga    |       1935 |
| Leida    | Valga    |       1938 |
| Siim     | Haapsalu |       1995 |
+----------+----------+------------+