Keeleandmed

CREATE TABLE d_keeled(
  keel VARCHAR(50) NOT NULL PRIMARY KEY,
  keelkond ENUM('soomeugri', 'slaavi')
);

CREATE TABLE d_autorid(
  email VARCHAR(50) NOT NULL PRIMARY KEY,
  emakeel VARCHAR(50),
  vanus INT,
  FOREIGN KEY(emakeel) REFERENCES d_keeled(keel)
);

CREATE TABLE d_dokumendid(
  doknr INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  autori_email VARCHAR(50),
  dokumendikeel VARCHAR(50),
  FOREIGN KEY(autori_email) REFERENCES d_autorid(email),
  FOREIGN KEY(dokumendikeel) REFERENCES d_keeled(keel)
);

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

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

CREATE TABLE d_dokumendid_sonad(
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   dokument INT,
   sona VARCHAR(50),
   FOREIGN KEY(dokument) REFERENCES d_dokumendid(doknr),
   FOREIGN KEY(sona) REFERENCES d_sonad(sona)
);


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


INSERT INTO d_sonad VALUES ('tass', 'nimisõna');
INSERT INTO d_sonad VALUES ('et', 'sidesõna');

INSERT INTO d_keeled VALUES ('eesti', 'soomeugri');
INSERT INTO d_keeled VALUES ('vene', 'slaavi');
INSERT INTO d_keeled VALUES ('soome', 'soomeugri');
INSERT INTO d_keeled VALUES ('ukraina', 'slaavi');
INSERT INTO d_keeled VALUES ('rootsi', 'skandinaavia');

INSERT INTO d_autorid VALUES ('juku@tlu.ee', 'eesti', 18);
INSERT INTO d_autorid VALUES ('kati@tlu.ee', 'eesti', 18);
INSERT INTO d_autorid VALUES ('jelena@tlu.ee', 'vene', 18);

INSERT INTO d_dokumendid VALUES (DEFAULT, 'juku@tlu.ee', 'eesti');
INSERT INTO d_dokumendid VALUES (DEFAULT, 'jelena@tlu.ee', 'eesti');
INSERT INTO d_dokumendid VALUES (DEFAULT, 'jelena@tlu.ee', 'vene');


SELECT * FROM d_dokumendid;

+-------+---------------+---------------+
| doknr | autori_email  | dokumendikeel |
+-------+---------------+---------------+
|     1 | juku@tlu.ee   | eesti         |
|     2 | jelena@tlu.ee | eesti         |
|     3 | jelena@tlu.ee | vene          |
+-------+---------------+---------------+

INSERT INTO d_dokumendid_sonad VALUES (DEFAULT, 1, 'tass');
INSERT INTO d_dokumendid_sonad VALUES (DEFAULT, 1, 'et');

INSERT INTO d_sonad VALUES ('kass', 'nimisõna');
INSERT INTO d_sonad VALUES ('kollane', 'omadussõna');

INSERT INTO d_dokumendid_sonad VALUES (DEFAULT, 2, 'tass');
INSERT INTO d_dokumendid_sonad VALUES (DEFAULT, 2, 'kass');
INSERT INTO d_dokumendid_sonad VALUES (DEFAULT, 2, 'kollane');

-- näita, millises kasutatud keeles on tehtud mitu dokumenti

SELECT dokumendikeel, COUNT(doknr) FROM d_dokumendid GROUP BY dokumendikeel;
+---------------+--------------+
| dokumendikeel | COUNT(doknr) |
+---------------+--------------+
| eesti         |            2 |
| vene          |            1 |
+---------------+--------------+

-- näita, millistes keelte tabelites loetletud keeltes on tehtud mitu dokumenti
-- (kaasaarvatud keeled, milles siin baasis dokumente pole)

LEFT JOINi abil - näita välja kõik keeled ning olemasolu korral leiduvad dokumendid

SELECT keel FROM d_keeled;

+---------+
| keel    |
+---------+
| eesti   |
| rootsi  |
| soome   |
| ukraina |
| vene    |
+---------+

SELECT keel, doknr FROM d_keeled
  LEFT JOIN d_dokumendid ON d_dokumendid.dokumendikeel=d_keeled.keel;
  
+---------+-------+
| keel    | doknr |
+---------+-------+
| eesti   |     1 |
| eesti   |     2 |
| rootsi  |  NULL |
| soome   |  NULL |
| ukraina |  NULL |
| vene    |     3 |
+---------+-------+

SELECT keel, COUNT(doknr) FROM d_keeled
  LEFT JOIN d_dokumendid ON d_dokumendid.dokumendikeel=d_keeled.keel
  GROUP BY d_keeled.keel;
+---------+--------------+
| keel    | COUNT(doknr) |
+---------+--------------+
| eesti   |            2 |
| rootsi  |            0 |
| soome   |            0 |
| ukraina |            0 |
| vene    |            1 |
+---------+--------------+

-- Mitu dokumenti on rootsi keeles?

SELECT COUNT(*) FROM d_dokumendid WHERE dokumendikeel='rootsi';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

SELECT keel FROM d_keeled;

SELECT keel, 
 (SELECT COUNT(*) FROM d_dokumendid WHERE dokumendikeel=d_keeled.keel) AS kogus 
 FROM d_keeled;
 
+---------+-------+
| keel    | kogus |
+---------+-------+
| eesti   |     2 |
| rootsi  |     0 |
| soome   |     0 |
| ukraina |     0 |
| vene    |     1 |
+---------+-------+

-- Näidake iga sõnaliigi kohta, mitu sellest liigist sõna on tabelis olemas (d_sonad)
SELECT sonaliik, COUNT(*) FROM d_sonad GROUP BY sonaliik;
+-------------+----------+
| sonaliik    | COUNT(*) |
+-------------+----------+
| nimisõna    |        2 |
| omadussõna  |        1 |
| sidesõna    |        1 |
+-------------+----------+


-- Näidake, millise sõnaliigi sõnu on mitu korda kasutatud (d_dokumendid_sonad)

SELECT d_dokumendid_sonad.sona, d_sonad.sonaliik FROM d_dokumendid_sonad 
  JOIN d_sonad ON d_dokumendid_sonad.sona=d_sonad.sona;
  
SELECT * FROM d_dokumendid_sonad;
INSERT INTO d_dokumendid_sonad VALUES (DEFAULT, 1, 'tass');

SELECT COUNT(*), d_sonad.sonaliik FROM d_dokumendid_sonad 
  JOIN d_sonad ON d_dokumendid_sonad.sona=d_sonad.sona
  GROUP BY d_sonad.sonaliik;	
  

+----------+-------------+
| COUNT(*) | sonaliik    |
+----------+-------------+
|        3 | nimisõna    |
|        1 | omadussõna  |
+----------+-------------+

SELECT d_dokumendid_sonad.sona, COUNT(*) FROM d_dokumendid_sonad 
  JOIN d_sonad ON d_dokumendid_sonad.sona=d_sonad.sona
  GROUP BY d_sonad.sona;

+---------+----------+
| sona    | COUNT(*) |
+---------+----------+
| kass    |        1 |
| kollane |        1 |
| tass    |        2 |
+---------+----------+