Sõnade andmed

CREATE TABLE sonaliigid(
   sonaliik VARCHAR(30) NOT NULL PRIMARY KEY,
   kirjeldus VARCHAR(100)
);

INSERT INTO sonaliigid VALUES ('nimisõna', 'esemete ja isikute nimetused');
INSERT INTO sonaliigid VALUES ('tegusõna', 'tegevusega seotud pöördsõnad');
INSERT INTO sonaliigid VALUES ('omadussõna', 'omaduse kirjeldamine');
INSERT INTO sonaliigid VALUES ('määrsõna', 'tegevuse või omaduse laad');


+-------------+---------------------------------+
| sonaliik    | kirjeldus                       |
+-------------+---------------------------------+
| määrsõna    | tegevuse või omaduse laad       |
| nimisõna    | esemete ja isikute nimetused    |
| omadussõna  | omaduse kirjeldamine            |
| tegusõna    | tegevusega seotud pöördsõnad    |
+-------------+---------------------------------+

CREATE TABLE sonad(
   sona VARCHAR(50) NOT NULL PRIMARY KEY,
   sonaliik VARCHAR(30),
   FOREIGN KEY(sonaliik) REFERENCES sonaliigid(sonaliik)
);

INSERT INTO sonad VALUES ('tass', 'nimisõna');
INSERT INTO sonad VALUES ('et', 'sidesõna');
mysql> INSERT INTO sonad VALUES ('et', 'sidesõna');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`if16_jaagup_4`.`sonad`, CONSTRAINT `sonad_ibfk_1` FOREIGN KEY (`sonaliik`) REFERENCES `sonaliigid` (`sonaliik`))
INSERT INTO sonaliigid VALUES ('sidesõna', 'sõnade ja lauseosade siduja');
INSERT INTO sonad VALUES ('et', 'sidesõna');

-- proovige lisada arv (näiteks seitse) arvsõnana, veenduge, et seda ei saa lisada
-- lisage sõnaliikide tabelisse liigiks arvsõna koos kirjeldusega
-- pange seitse uuesti sõnade alla

INSERT INTO sonad VALUES ('seitse', 'arvsõna');
INSERT INTO sonaliigid VALUES ('arvsõna', 'kvantitatiivne kirjeldaja')

SELECT * FROM sonad
  JOIN sonaliigid ON sonad.sonaliik=sonaliigid.sonaliik;


+---------+-----------+-----------+------------------------------+
| sona    | sonaliik  | sonaliik  | kirjeldus                    |
+---------+-----------+-----------+------------------------------+
| kaheksa | arvsõna   | arvsõna   | kvantitatiivne kirjeldaja    |
| kolm    | arvsõna   | arvsõna   | kvantitatiivne kirjeldaja    |
| seitse  | arvsõna   | arvsõna   | kvantitatiivne kirjeldaja    |
| kapsas  | nimisõna  | nimisõna  | esemete ja isikute nimetused |
| kartul  | nimisõna  | nimisõna  | esemete ja isikute nimetused |
| tass    | nimisõna  | nimisõna  | esemete ja isikute nimetused |
| et      | sidesõna  | sidesõna  | sõnade ja lauseosade siduja  |
+---------+-----------+-----------+------------------------------+

  
SELECT sona, sonad.sonaliik, kirjeldus FROM sonad
   JOIN sonaliigid ON sonad.sonaliik=sonaliigid.sonaliik;
   
+---------+-----------+------------------------------+
| sona    | sonaliik  | kirjeldus                    |
+---------+-----------+------------------------------+
| kaheksa | arvsõna   | kvantitatiivne kirjeldaja    |
| kolm    | arvsõna   | kvantitatiivne kirjeldaja    |
| seitse  | arvsõna   | kvantitatiivne kirjeldaja    |
| kapsas  | nimisõna  | esemete ja isikute nimetused |
| kartul  | nimisõna  | esemete ja isikute nimetused |
| tass    | nimisõna  | esemete ja isikute nimetused |
| et      | sidesõna  | sõnade ja lauseosade siduja  |
+---------+-----------+------------------------------+

Näidake iga sõnaliigi kohta, mitu sõna sellest on tabelis
SELECT COUNT(*), sonaliik FROM sonad GROUP BY sonaliik;

Näidake iga sõnaliigi kohta kirjeldus ning mitu sõna sellest liigist on tabelis
SELECT COUNT(*), sonad.sonaliik, kirjeldus FROM sonad 
  JOIN sonaliigid ON sonad.sonaliik=sonaliigid.sonaliik
  GROUP BY sonad.sonaliik;
+----------+-----------+------------------------------+
| COUNT(*) | sonaliik  | kirjeldus                    |
+----------+-----------+------------------------------+
|        3 | arvsõna   | kvantitatiivne kirjeldaja    |
|        3 | nimisõna  | esemete ja isikute nimetused |
|        1 | sidesõna  | sõnade ja lauseosade siduja  |
+----------+-----------+------------------------------+


SELECT sonaliik FROM sonaliigid;

-- näitab kõikide sõnade arvu
SELECT sonaliik, 
    (SELECT COUNT(*) FROM sonad) 
  FROM sonaliigid;

-- Näitab vastavat liiki sõnade arvu
SELECT sonaliik, 
    (SELECT COUNT(*) FROM sonad WHERE sonad.sonaliik=sonaliigid.sonaliik) AS kogus
  FROM sonaliigid
  ORDER BY kogus DESC;

INSERT INTO sonad VALUES ('kr1aws2', NULL);
SELECT * FROM sonad;

+---------+-----------+
| sona    | sonaliik  |
+---------+-----------+
| kr1aws2 | NULL      |
| kaheksa | arvsõna   |
| kolm    | arvsõna   |
| seitse  | arvsõna   |
| kapsas  | nimisõna  |
| kartul  | nimisõna  |
| tass    | nimisõna  |
| et      | sidesõna  |
+---------+-----------+

-- puuduva sõnaliigiga sõna läheb tavalise (inner) joini puhul kaduma

SELECT sona, sonad.sonaliik, kirjeldus FROM sonad
   JOIN sonaliigid ON sonad.sonaliik=sonaliigid.sonaliik;
   
+---------+-----------+------------------------------+
| sona    | sonaliik  | kirjeldus                    |
+---------+-----------+------------------------------+
| kaheksa | arvsõna   | kvantitatiivne kirjeldaja    |
| kolm    | arvsõna   | kvantitatiivne kirjeldaja    |
| seitse  | arvsõna   | kvantitatiivne kirjeldaja    |
| kapsas  | nimisõna  | esemete ja isikute nimetused |
| kartul  | nimisõna  | esemete ja isikute nimetused |
| tass    | nimisõna  | esemete ja isikute nimetused |
| et      | sidesõna  | sõnade ja lauseosade siduja  |
+---------+-----------+------------------------------+

SELECT sona, sonad.sonaliik, kirjeldus FROM sonad
   LEFT JOIN sonaliigid ON sonad.sonaliik=sonaliigid.sonaliik;
   
+---------+-----------+------------------------------+
| sona    | sonaliik  | kirjeldus                    |
+---------+-----------+------------------------------+
| kr1aws2 | NULL      | NULL                         |
| kaheksa | arvsõna   | kvantitatiivne kirjeldaja    |
| kolm    | arvsõna   | kvantitatiivne kirjeldaja    |
| seitse  | arvsõna   | kvantitatiivne kirjeldaja    |
| kapsas  | nimisõna  | esemete ja isikute nimetused |
| kartul  | nimisõna  | esemete ja isikute nimetused |
| tass    | nimisõna  | esemete ja isikute nimetused |
| et      | sidesõna  | sõnade ja lauseosade siduja  |
+---------+-----------+------------------------------+

SELECT sona, sonaliigid.sonaliik, kirjeldus FROM sonad
   RIGHT JOIN sonaliigid ON sonad.sonaliik=sonaliigid.sonaliik;
   
+---------+-------------+---------------------------------+
| sona    | sonaliik    | kirjeldus                       |
+---------+-------------+---------------------------------+
| kaheksa | arvsõna     | kvantitatiivne kirjeldaja       |
| kolm    | arvsõna     | kvantitatiivne kirjeldaja       |
| seitse  | arvsõna     | kvantitatiivne kirjeldaja       |
| NULL    | määrsõna    | tegevuse või omaduse laad       |
| kapsas  | nimisõna    | esemete ja isikute nimetused    |
| kartul  | nimisõna    | esemete ja isikute nimetused    |
| tass    | nimisõna    | esemete ja isikute nimetused    |
| NULL    | omadussõna  | omaduse kirjeldamine            |
| et      | sidesõna    | sõnade ja lauseosade siduja     |
| NULL    | tegusõna    | tegevusega seotud pöördsõnad    |
+---------+-------------+---------------------------------+

SELECT sona, sonaliigid.sonaliik, kirjeldus FROM sonaliigid
   LEFT JOIN sonad ON sonad.sonaliik=sonaliigid.sonaliik;
   
+---------+-------------+---------------------------------+
| sona    | sonaliik    | kirjeldus                       |
+---------+-------------+---------------------------------+
| kaheksa | arvsõna     | kvantitatiivne kirjeldaja       |
| kolm    | arvsõna     | kvantitatiivne kirjeldaja       |
| seitse  | arvsõna     | kvantitatiivne kirjeldaja       |
| NULL    | määrsõna    | tegevuse või omaduse laad       |
| kapsas  | nimisõna    | esemete ja isikute nimetused    |
| kartul  | nimisõna    | esemete ja isikute nimetused    |
| tass    | nimisõna    | esemete ja isikute nimetused    |
| NULL    | omadussõna  | omaduse kirjeldamine            |
| et      | sidesõna    | sõnade ja lauseosade siduja     |
| NULL    | tegusõna    | tegevusega seotud pöördsõnad    |
+---------+-------------+---------------------------------+


SELECT sonaliigid.sonaliik, COUNT(*), GROUP_CONCAT(sona) FROM sonaliigid
   LEFT JOIN sonad ON sonad.sonaliik=sonaliigid.sonaliik
   GROUP BY sonaliigid.sonaliik;
   
+-------------+----------+---------------------+
| sonaliik    | COUNT(*) | GROUP_CONCAT(sona)  |
+-------------+----------+---------------------+
| arvsõna     |        3 | kaheksa,kolm,seitse |
| määrsõna    |        1 | NULL                |
| nimisõna    |        3 | kapsas,kartul,tass  |
| omadussõna  |        1 | NULL                |
| sidesõna    |        1 | et                  |
| tegusõna    |        1 | NULL                |
+-------------+----------+---------------------+

SELECT 'teadmata';

+----------+
| teadmata |
+----------+
| teadmata |
+----------+


SELECT sonaliik FROM sonaliigid
UNION ALL
SELECT 'teadmata';

+-------------+
| sonaliik    |
+-------------+
| arvsõna     |
| määrsõna    |
| nimisõna    |
| omadussõna  |
| sidesõna    |
| tegusõna    |
| teadmata    |
+-------------+

SELECT 'teadmata', COUNT(*), GROUP_CONCAT(sona) FROM sonad WHERE sonaliik IS NULL;
+----------+----------+--------------------+
| teadmata | COUNT(*) | GROUP_CONCAT(sona) |
+----------+----------+--------------------+
| teadmata |        1 | kr1aws2            |
+----------+----------+--------------------+


SELECT sonaliigid.sonaliik, COUNT(sona), GROUP_CONCAT(sona) FROM sonaliigid
   LEFT JOIN sonad ON sonad.sonaliik=sonaliigid.sonaliik
   GROUP BY sonaliigid.sonaliik
UNION ALL
SELECT 'teadmata', COUNT(*), GROUP_CONCAT(sona) FROM sonad WHERE sonaliik IS NULL;

+-------------+----------+---------------------+
| sonaliik    | COUNT(*) | GROUP_CONCAT(sona)  |
+-------------+----------+---------------------+
| arvsõna     |        3 | kaheksa,kolm,seitse |
| määrsõna    |        1 | NULL                |
| nimisõna    |        3 | kapsas,kartul,tass  |
| omadussõna  |        1 | NULL                |
| sidesõna    |        1 | et                  |
| tegusõna    |        1 | NULL                |
| teadmata    |        1 | kr1aws2             |
+-------------+----------+---------------------+

-- et ei loendaks puuduva sõnaga sõnaliike, siis COUNT(*) asemele COUNT(sona)

mysql> SELECT sonaliigid.sonaliik, COUNT(sona), GROUP_CONCAT(sona) FROM sonaliigid
    ->    LEFT JOIN sonad ON sonad.sonaliik=sonaliigid.sonaliik
    ->    GROUP BY sonaliigid.sonaliik
    -> UNION ALL
    -> SELECT 'teadmata', COUNT(*), GROUP_CONCAT(sona) FROM sonad WHERE sonaliik IS NULL;
+-------------+-------------+---------------------+
| sonaliik    | COUNT(sona) | GROUP_CONCAT(sona)  |
+-------------+-------------+---------------------+
| arvsõna     |           3 | kaheksa,kolm,seitse |
| määrsõna    |           0 | NULL                |
| nimisõna    |           3 | kapsas,kartul,tass  |
| omadussõna  |           0 | NULL                |
| sidesõna    |           1 | et                  |
| tegusõna    |           0 | NULL                |
| teadmata    |           1 | kr1aws2             |
+-------------+-------------+---------------------+