Mitme seotud tabeliga andmebaas

Abivahend tähejärjestuste andmetega töötamiseks

CREATE TABLE t_sonad(
  sona VARCHAR(50) NOT NULL PRIMARY KEY
);

CREATE TABLE t_tahepaarid(
  tahepaar CHAR(2) NOT NULL PRIMARY KEY
);

CREATE TABLE t_tahepaarid_sonad(
  tahepaar CHAR(2),
  sona VARCHAR(50),
  koht INT,
  PRIMARY KEY(tahepaar, sona, koht),
  FOREIGN KEY(tahepaar) REFERENCES t_tahepaarid(tahepaar),
  FOREIGN KEY(sona) REFERENCES t_sonad(sona)
);

CREATE TABLE t_paarijargnevused(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  paar1 CHAR(2),
  paar2 CHAR(2),
  sona VARCHAR(50),
  koht1 INT,
  koht2 INT,
  FOREIGN KEY(paar1, sona, koht1) REFERENCES t_tahepaarid_sonad(tahepaar, sona, koht),
  FOREIGN KEY(paar2, sona, koht2) REFERENCES t_tahepaarid_sonad(tahepaar, sona, koht)  
);

Täitke tabelid andmetega lausest:
asjaajaja, habemeajaja ja jaamaülem

INSERT INTO t_sonad VALUES('asjaajaja');
INSERT INTO t_sonad VALUES('habemeajaja');
INSERT INTO t_sonad VALUES('ja');
INSERT INTO t_sonad VALUES('jaamaülem');

INSERT INTO t_tahepaarid VALUES('as');
INSERT INTO t_tahepaarid VALUES('sj');
INSERT INTO t_tahepaarid VALUES('ja');
INSERT INTO t_tahepaarid VALUES('aa');
INSERT INTO t_tahepaarid VALUES('aj');
INSERT INTO t_tahepaarid VALUES('ha');
INSERT INTO t_tahepaarid VALUES('ab');
INSERT INTO t_tahepaarid VALUES('be');
INSERT INTO t_tahepaarid VALUES('em');
INSERT INTO t_tahepaarid VALUES('me');
INSERT INTO t_tahepaarid VALUES('ea');
INSERT INTO t_tahepaarid VALUES('am');
INSERT INTO t_tahepaarid VALUES('ma');
INSERT INTO t_tahepaarid VALUES('aü');
INSERT INTO t_tahepaarid VALUES('ül');
INSERT INTO t_tahepaarid VALUES('le');


INSERT INTO t_tahepaarid_sonad VALUES('as', 'asjaajaja', 1);
INSERT INTO t_tahepaarid_sonad VALUES('sj', 'asjaajaja', 2);
INSERT INTO t_tahepaarid_sonad VALUES('ja', 'asjaajaja', 3);
INSERT INTO t_tahepaarid_sonad VALUES('aa', 'asjaajaja', 4);
INSERT INTO t_tahepaarid_sonad VALUES('aj', 'asjaajaja', 5);
INSERT INTO t_tahepaarid_sonad VALUES('ja', 'asjaajaja', 6);
INSERT INTO t_tahepaarid_sonad VALUES('aj', 'asjaajaja', 7);
INSERT INTO t_tahepaarid_sonad VALUES('ja', 'asjaajaja', 8);
INSERT INTO t_tahepaarid_sonad VALUES('ha', 'habemeajaja', 1);
INSERT INTO t_tahepaarid_sonad VALUES('ab', 'habemeajaja', 2);
INSERT INTO t_tahepaarid_sonad VALUES('be', 'habemeajaja', 3);
INSERT INTO t_tahepaarid_sonad VALUES('em', 'habemeajaja', 4);
INSERT INTO t_tahepaarid_sonad VALUES('me', 'habemeajaja', 5);
INSERT INTO t_tahepaarid_sonad VALUES('ea', 'habemeajaja', 6);
INSERT INTO t_tahepaarid_sonad VALUES('aj', 'habemeajaja', 7);
INSERT INTO t_tahepaarid_sonad VALUES('ja', 'habemeajaja', 8);
INSERT INTO t_tahepaarid_sonad VALUES('aj', 'habemeajaja', 9);
INSERT INTO t_tahepaarid_sonad VALUES('ja', 'habemeajaja', 10);
INSERT INTO t_tahepaarid_sonad VALUES('ja', 'ja', 1);
INSERT INTO t_tahepaarid_sonad VALUES('ja', 'jaamaülem', 1);
INSERT INTO t_tahepaarid_sonad VALUES('aa', 'jaamaülem', 2);
INSERT INTO t_tahepaarid_sonad VALUES('am', 'jaamaülem', 3);
INSERT INTO t_tahepaarid_sonad VALUES('ma', 'jaamaülem', 4);
INSERT INTO t_tahepaarid_sonad VALUES('aü', 'jaamaülem', 5);
INSERT INTO t_tahepaarid_sonad VALUES('ül', 'jaamaülem', 6);
INSERT INTO t_tahepaarid_sonad VALUES('le', 'jaamaülem', 7);
INSERT INTO t_tahepaarid_sonad VALUES('em', 'jaamaülem', 8);


Kuvage, millist tähepaari mitu korda esineb
MariaDB [if17_jaagup_4]> SELECT tahepaar, COUNT(*) FROM t_tahepaarid_sonad GROUP BY tahepaar;
+----------+----------+
| tahepaar | COUNT(*) |
+----------+----------+
| aa       |        2 |
| ab       |        1 |
| aj       |        4 |
| am       |        1 |
| as       |        1 |


Kuvage tähepaarid, mida esineb rohkem kui korra

SELECT tahepaar, COUNT(*) FROM t_tahepaarid_sonad GROUP BY tahepaar HAVING COUNT(*)>1;

+----------+----------+
| tahepaar | COUNT(*) |
+----------+----------+
| aa       |        2 |
| aj       |        4 |
| em       |        2 |
| ja       |        7 |
+----------+----------+
4 rows in set (0.00 sec)


Näita iga sõna juures (GROUP_CONCAT) tähepaarid, mida esineb kogu tekstis rohkem kui korra

SELECT sona, GROUP_CONCAT(tahepaar) FROM t_tahepaarid_sonad WHERE tahepaar IN
  (SELECT tahepaar FROM t_tahepaarid_sonad GROUP BY tahepaar HAVING COUNT(*)>1)
 GROUP BY sona;

+-------------+------------------------+
| sona        | GROUP_CONCAT(tahepaar) |
+-------------+------------------------+
| asjaajaja   | aa,aj,aj,ja,ja,ja      |
| habemeajaja | aj,aj,em,ja,ja         |
| ja          | ja                     |
| jaamaülem   | aa,em,ja               |
+-------------+------------------------+