Tekstidokumentide veahaldus

CREATE TABLE d_doktyybid(
  tyybinimi VARCHAR(50) NOT NULL PRIMARY KEY,
  tyybikirjeldus VARCHAR(255)
);

CREATE TABLE d_vealiigid(
  vealiik VARCHAR(50) NOT NULL PRIMARY KEY,
  ylemliik VARCHAR(50), 
  FOREIGN KEY(ylemliik) REFERENCES d_vealiigid(vealiik)
);

CREATE TABLE d_dokumendid(
  doknr INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  doktyyp VARCHAR(50),
  tekstiurl VARCHAR(255),
  FOREIGN KEY(doktyyp) REFERENCES d_doktyybid(tyybinimi)
);

CREATE TABLE d_viga_dokumendis(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  dokument INT,
  viga VARCHAR(50),
  FOREIGN KEY(dokument) REFERENCES d_dokumendid(doknr),
  FOREIGN KEY(viga) REFERENCES d_vealiigid(vealiik)
);

INSERT INTO d_doktyybid VALUES('uudis', 'ajalehe uudislugu');
INSERT INTO d_doktyybid VALUES('kommentaar', 'kommentaar uudisele');
INSERT INTO d_doktyybid VALUES('loengumaterjal', 'õppimiseks mõeldud tekst');

INSERT INTO d_dokumendid VALUES (DEFAULT, 'uudis', 'http://www.delfi.ee/news/paevauudised/valismaa/amaris-paikneva-saksa-lennugrupi-ulem-raakis-hullust-oopaevast-soome-lahel-venemaa-saatis-lennukeid-pideva-vooluna?id=75969763')
INSERT INTO d_dokumendid VALUES (DEFAULT, 'uudis', 'http://www.delfi.ee/news/paevauudised/eesti/video-lufthansa-lennuk-tegi-kanadas-hadamaandumise-pardal-olid-ka-eestlased?id=75967145');
INSERT INTO d_dokumendid VALUES (DEFAULT, 'loengumaterjal', 'http://minitorn.tlu.ee/~jaagup/kool/java/loeng/pohikursus03/tutvus.txt');

INSERT INTO d_vealiigid VALUES ('salvestusvormingu viga', NULL);
INSERT INTO d_vealiigid VALUES ('kooditabeli viga', 'salvestusvormingu viga');
INSERT INTO d_vealiigid VALUES ('trükiviga', NULL);
INSERT INTO d_vealiigid VALUES ('puuduv täht', 'trükiviga');
INSERT INTO d_vealiigid VALUES ('vahetusse läinud täht', 'trükiviga');

INSERT INTO d_viga_dokumendis VALUES(DEFAULT, 3, 'kooditabeli viga');
INSERT INTO d_viga_dokumendis VALUES(DEFAULT, 3, 'trükiviga');


-- Näidake iga vigu sisaldava dokumendi kohta, mitu viga seal on
SELECT dokument, COUNT(*) FROM d_viga_dokumendis GROUP BY dokument;
+----------+----------+
| dokument | COUNT(*) |
+----------+----------+
|        3 |        2 |
+----------+----------+

SELECT viga, COUNT(*) FROM d_viga_dokumendis GROUP BY viga;

+------------------+----------+
| viga             | COUNT(*) |
+------------------+----------+
| kooditabeli viga |        1 |
| trükiviga        |        1 |
+------------------+----------+


-- Näidake iga olemasoleva dokumendi kohta, mitu viga seal on
SELECT doknr FROM d_dokumendid;

SELECT doknr FROM d_dokumendid;
SELECT COUNT(*) FROM d_viga_dokumendis WHERE dokument=2;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

SELECT doknr, (SELECT COUNT(*) FROM d_viga_dokumendis) FROM d_dokumendid;
;
+-------+------------------------------------------+
| doknr | (SELECT COUNT(*) FROM d_viga_dokumendis) |
+-------+------------------------------------------+
|     3 |                                        2 |
|     1 |                                        2 |
|     2 |                                        2 |
+-------+------------------------------------------+

SELECT doknr, 
  (SELECT COUNT(*) FROM d_viga_dokumendis 
    WHERE d_dokumendid.doknr=d_viga_dokumendis.dokument) 
  AS vigadearv 
 FROM d_dokumendid;
 
+-------+-----------+
| doknr | vigadearv |
+-------+-----------+
|     3 |         2 |
|     1 |         0 |
|     2 |         0 |
+-------+-----------+

SELECT doknr FROM d_dokumendid;
SELECT dokument FROM d_viga_dokumendis;
SELECT DISTINCT dokument FROM d_viga_dokumendis;
SELECT doknr FROM d_dokumendid WHERE doknr NOT IN (SELECT DISTINCT dokument FROM d_viga_dokumendis);
+-------+
| doknr |
+-------+
|     1 |
|     2 |
+-------+

SELECT dokument, COUNT(*) FROM d_viga_dokumendis GROUP BY dokument;

SELECT 7, "pole";

SELECT dokument, COUNT(*) FROM d_viga_dokumendis GROUP BY dokument
UNION ALL
SELECT 7, "pole";

+----------+----------+
| dokument | COUNT(*) |
+----------+----------+
|        3 | 2        |
|        7 | pole     |
+----------+----------+

SELECT doknr, 0 FROM d_dokumendid WHERE doknr NOT IN 
   (SELECT DISTINCT dokument FROM d_viga_dokumendis);
+-------+---+
| doknr | 0 |
+-------+---+
|     1 | 0 |
|     2 | 0 |
+-------+---+

--ühendage päringute tulemused kokku
SELECT dokument, COUNT(*) FROM d_viga_dokumendis GROUP BY dokument;
SELECT doknr, 0 FROM d_dokumendid WHERE doknr NOT IN 
   (SELECT DISTINCT dokument FROM d_viga_dokumendis);
   
SELECT dokument, COUNT(*) FROM d_viga_dokumendis GROUP BY dokument
UNION ALL
SELECT doknr, 0 FROM d_dokumendid WHERE doknr NOT IN 
   (SELECT DISTINCT dokument FROM d_viga_dokumendis);
+----------+----------+
| dokument | COUNT(*) |
+----------+----------+
|        3 |        2 |
|        1 |        0 |
|        2 |        0 |
+----------+----------+


SELECT doknr, viga FROM d_dokumendid 
  JOIN d_viga_dokumendis ON d_viga_dokumendis.dokument=d_dokumendid.doknr;
 

+-------+------------------+
| doknr | viga             |
+-------+------------------+
|     3 | kooditabeli viga |
|     3 | trükiviga        |
+-------+------------------+

 
SELECT doknr, viga FROM d_dokumendid 
  LEFT JOIN d_viga_dokumendis ON d_viga_dokumendis.dokument=d_dokumendid.doknr;
+-------+------------------+
| doknr | viga             |
+-------+------------------+
|     3 | kooditabeli viga |
|     3 | trükiviga        |
|     1 | NULL             |
|     2 | NULL             |
+-------+------------------+

SELECT doknr, COUNT(*) FROM d_dokumendid 
  LEFT JOIN d_viga_dokumendis ON d_viga_dokumendis.dokument=d_dokumendid.doknr
  GROUP BY doknr;

+-------+----------+
| doknr | COUNT(*) |
+-------+----------+
|     1 |        1 |
|     2 |        1 |
|     3 |        2 |
+-------+----------+

SELECT doknr, COUNT(viga) FROM d_dokumendid 
  LEFT JOIN d_viga_dokumendis ON d_viga_dokumendis.dokument=d_dokumendid.doknr
  GROUP BY doknr;

+-------+-------------+
| doknr | COUNT(viga) |
+-------+-------------+
|     1 |           0 |
|     2 |           0 |
|     3 |           2 |
+-------+-------------+


-- Leidke iga vigadega dokumenditüübi kohta mitu viga on tehtud
SELECT doktyyp, dokument, viga FROM d_dokumendid
  JOIN d_viga_dokumendis ON d_viga_dokumendis.dokument=d_dokumendid.doknr
  
SELECT doktyyp, COUNT(*) FROM d_dokumendid
  JOIN d_viga_dokumendis ON d_viga_dokumendis.dokument=d_dokumendid.doknr
  GROUP BY doktyyp;
  
 
+----------------+----------+
| doktyyp        | COUNT(*) |
+----------------+----------+
| loengumaterjal |        2 |
| uudis          |        1 |
+----------------+----------+

SELECT viga, COUNT(*) FROM d_dokumendid
  JOIN d_viga_dokumendis ON d_viga_dokumendis.dokument=d_dokumendid.doknr
  GROUP BY viga;

+------------------+----------+
| viga             | COUNT(*) |
+------------------+----------+
| kooditabeli viga |        1 |
| trükiviga        |        2 |
+------------------+----------+


SELECT viga, doktyyp, COUNT(*) FROM d_dokumendid
  JOIN d_viga_dokumendis ON d_viga_dokumendis.dokument=d_dokumendid.doknr
  GROUP BY viga, doktyyp;

+------------------+----------------+----------+
| viga             | doktyyp        | COUNT(*) |
+------------------+----------------+----------+
| kooditabeli viga | loengumaterjal |        1 |
| trükiviga        | loengumaterjal |        1 |
| trükiviga        | uudis          |        1 |
+------------------+----------------+----------+
 
-- Leidke iga olemasoleva dokumenditüübi kohta mitu viga on tehtud

-- Leidke iga dokumenditüübi ja veatüübi kombinatsiooni kohta, mitu viga on tehtud