CREATE TABLE k_klassid (
	klassinimi VARCHAR(5) PRIMARY KEY NOT NULL,
	ruumi_nr VARCHAR(10)
	);
	
CREATE TABLE k_ainekursused (
	kursusenimi VARCHAR(40) PRIMARY KEY NOT NULL,
	tundidearv INT
	);
	
CREATE TABLE k_klass_kursusel (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	klass VARCHAR(5) NOT NULL,
	ainekursus VARCHAR(40) NOT NULL,
	FOREIGN KEY(klass) REFERENCES k_klassid(klassinimi),
	FOREIGN KEY(ainekursus) REFERENCES k_ainekursused(kursusenimi)
	);
	
	
INSERT INTO k_klassid VALUES("6A", "A406");
INSERT INTO k_klassid VALUES("7A", "S303");
INSERT INTO k_klassid VALUES("6B", "A524");
INSERT INTO k_klassid VALUES("12D", "T302");
INSERT INTO k_klassid VALUES("3C", "M522");
INSERT INTO k_klassid VALUES("3A", "M523");
INSERT INTO k_klassid VALUES("3B", "M522");
INSERT INTO k_klassid VALUES("3D", "M523");

INSERT INTO k_ainekursused VALUES("Matemaatika 3. klass", 105);
INSERT INTO k_ainekursused VALUES("Eesti keel 6. klass", 70);
INSERT INTO k_ainekursused VALUES("Füüsika 12. klass", 70);
INSERT INTO k_ainekursused VALUES("Muusika ?petus 7. klass", 35);
INSERT INTO k_ainekursused VALUES("Muusika ?petus 3. klass", 35);

INSERT INTO k_klass_kursusel VALUES(default, "6A", "Eesti keel 6. klass");
INSERT INTO k_klass_kursusel VALUES(default, "6B", "Eesti keel 6. klass");
INSERT INTO k_klass_kursusel VALUES(default, "12D", "Füüsika 12. klass");
INSERT INTO k_klass_kursusel VALUES(default, "12D", "Inglise keel 12. klass");
INSERT INTO k_klass_kursusel VALUES(default, "3C", "Matemaatika 3. klass");
INSERT INTO k_klass_kursusel VALUES(default, "3D", "Matemaatika 3. klass");
INSERT INTO k_klass_kursusel VALUES(default, "3B", "Matemaatika 3. klass");
INSERT INTO k_klass_kursusel VALUES(default, "3A", "Matemaatika 3. klass");
INSERT INTO k_klass_kursusel VALUES(default, "7A", "Muusika ?petus 7. klass");
INSERT INTO k_klass_kursusel VALUES(default, "3A", "Muusika ?petus 3. klass");
INSERT INTO k_klass_kursusel VALUES(default, "3B", "Muusika ?petus 3. klass");



SELECT * FROM k_klass_kursusel
	JOIN k_ainekursused ON k_klass_kursusel.ainekursus = k_ainekursused.kursusenimi
	JOIN k_klassid ON k_klass_kursusel.klass = k_klassid.klassinimi;
	
+----+-------+--------------------------+--------------------------+------------+------------+----------+
| id | klass | ainekursus               | kursusenimi              | tundidearv | klassinimi | ruumi_nr |
+----+-------+--------------------------+--------------------------+------------+------------+----------+
|  1 | 6A    | Eesti keel 6. klass      | Eesti keel 6. klass      |         70 | 6A         | A406     |
|  2 | 6B    | Eesti keel 6. klass      | Eesti keel 6. klass      |         70 | 6B         | A524     |
|  3 | 12D   | Füüsika 12. klass        | Füüsika 12. klass        |         70 | 12D        | T302     |
|  4 | 3C    | Matemaatika 3. klass     | Matemaatika 3. klass     |        105 | 3C         | M522     |
|  5 | 3D    | Matemaatika 3. klass     | Matemaatika 3. klass     |        105 | 3D         | M525     |
|  6 | 3B    | Matemaatika 3. klass     | Matemaatika 3. klass     |        105 | 3B         | M524     |
|  7 | 3A    | Matemaatika 3. klass     | Matemaatika 3. klass     |        105 | 3A         | M523     |
| 12 | 3A    | Muusika ?petus 3. klass   | Muusika ?petus 3. klass   |         35 | 3A         | M523     |
| 13 | 3B    | Muusika ?petus 3. klass   | Muusika ?petus 3. klass   |         35 | 3B         | M524     |
|  9 | 7A    | Muusika ?petus 7. klass   | Muusika ?petus 7. klass   |         35 | 7A         | S303     |
+----+-------+--------------------------+--------------------------+------------+------------+----------+



CREATE TABLE k_opetajad(
  perekonnanimi VARCHAR(50) NOT NULL PRIMARY KEY,
  synniaasta INT
);

ALTER TABLE k_klassid ADD klassijuhataja VARCHAR(50),
 ADD CONSTRAINT klassijuhataja_voti 
   FOREIGN KEY(klassijuhataja) REFERENCES k_opetajad(perekonnanimi);
   
INSERT INTO k_opetajad VALUES ('Tamm', 1956);
INSERT INTO k_opetajad VALUES ('Kask', 1958);
INSERT INTO k_opetajad VALUES ('Saar', 1959);

UPDATE k_klassid SET klassijuhataja='Saar' WHERE klassinimi='3A';


-- Näidake, mis aastal sündis(id) 
-- 3. klassi matemaatikakursust õpetavate klasside klassijuhatajad
   
SELECT * FROM k_ainekursused;   

mysql> SELECT * FROM k_ainekursused;
+-------------------------+------------+
| kursusenimi             | tundidearv |
+-------------------------+------------+
| Eesti keel 6. klass     |         70 |
| Füüsika 12. klass       |         70 |
| Matemaatika 3. klass    |        105 |
| Muusika ?petus 3. klass |         35 |
| Muusika ?petus 7. klass |         35 |
+-------------------------+------------+

SELECT * FROM k_ainekursused 
  WHERE kursusenimi='Matemaatika 3. klass';
  
+----------------------+------------+
| kursusenimi          | tundidearv |
+----------------------+------------+
| Matemaatika 3. klass |        105 |
+----------------------+------------

SELECT * FROM k_ainekursused 
  JOIN k_klass_kursusel ON k_klass_kursusel.ainekursus=k_ainekursused.kursusenimi
  WHERE kursusenimi='Matemaatika 3. klass';
  

+----------------------+------------+----+-------+----------------------+
| kursusenimi          | tundidearv | id | klass | ainekursus           |
+----------------------+------------+----+-------+----------------------+
| Matemaatika 3. klass |        105 |  5 | 3C    | Matemaatika 3. klass |
| Matemaatika 3. klass |        105 |  6 | 3D    | Matemaatika 3. klass |
| Matemaatika 3. klass |        105 |  7 | 3B    | Matemaatika 3. klass |
| Matemaatika 3. klass |        105 |  8 | 3A    | Matemaatika 3. klass |
+----------------------+------------+----+-------+----------------------+

SELECT klass FROM k_ainekursused 
  JOIN k_klass_kursusel ON k_klass_kursusel.ainekursus=k_ainekursused.kursusenimi
  WHERE kursusenimi='Matemaatika 3. klass';
  
+-------+
| klass |
+-------+
| 3C    |
| 3D    |
| 3B    |
| 3A    |
+-------+

SELECT klass, klassijuhataja FROM k_ainekursused 
  JOIN k_klass_kursusel ON k_klass_kursusel.ainekursus=k_ainekursused.kursusenimi
  JOIN k_klassid ON k_klass_kursusel.klass=k_klassid.klassinimi
  WHERE kursusenimi='Matemaatika 3. klass';

+-------+----------------+
| klass | klassijuhataja |
+-------+----------------+
| 3C    | Tamm           |
| 3D    | NULL           |
| 3B    | Saar           |
| 3A    | NULL           |
+-------+----------------+

-- Juurde sünniaasta õpetajate tabelist

SELECT klass, klassijuhataja, synniaasta FROM k_ainekursused 
  JOIN k_klass_kursusel ON k_klass_kursusel.ainekursus=k_ainekursused.kursusenimi
  JOIN k_klassid ON k_klass_kursusel.klass=k_klassid.klassinimi
  JOIN k_opetajad ON k_klassid.klassijuhataja=k_opetajad.perekonnanimi
  WHERE kursusenimi='Matemaatika 3. klass';
  
+-------+----------------+------------+
| klass | klassijuhataja | synniaasta |
+-------+----------------+------------+
| 3B    | Saar           |       1959 |
| 3C    | Tamm           |       1956 |
+-------+----------------+------------+

SELECT klass, klassijuhataja, synniaasta FROM k_ainekursused 
  JOIN k_klass_kursusel ON k_klass_kursusel.ainekursus=k_ainekursused.kursusenimi
  JOIN k_klassid ON k_klass_kursusel.klass=k_klassid.klassinimi
  LEFT JOIN k_opetajad ON k_klassid.klassijuhataja=k_opetajad.perekonnanimi
  WHERE kursusenimi='Matemaatika 3. klass';

+-------+----------------+------------+
| klass | klassijuhataja | synniaasta |
+-------+----------------+------------+
| 3C    | Tamm           |       1956 |
| 3D    | NULL           |       NULL |
| 3B    | Saar           |       1959 |
| 3A    | NULL           |       NULL |
+-------+----------------+------------+


-- Lisa tabelisse k_klass_kursusel veerg opetaja viitega õpetajate tabeli perekonnanimele
-- Näita, millised õpetajad õpetavad 3. klassi matemaatikakursust
   Näita ka nende sünniaastad
-- Näita, millistel klassidel õpetab 3. klassi matemaatikakursust nende oma klassijuhataja.

ALTER TABLE k_klass_kursusel ADD opetaja VARCHAR(50),
 ADD CONSTRAINT opetaja_voti 
   FOREIGN KEY(opetaja) REFERENCES k_opetajad(perekonnanimi);
   

Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

SELECT opetaja, klass FROM k_ainekursused 
  JOIN k_klass_kursusel ON k_klass_kursusel.ainekursus=k_ainekursused.kursusenimi
  WHERE kursusenimi='Matemaatika 3. klass';
  

+---------+-------+
| opetaja | klass |
+---------+-------+
| Tamm    | 3C    |
| Kask    | 3D    |
| Saar    | 3B    |
| Saar    | 3A    |
+---------+-------+

-- Lisage õpetaja sünniaasta

SELECT opetaja, klass, synniaasta FROM k_ainekursused 
  JOIN k_klass_kursusel ON k_klass_kursusel.ainekursus=k_ainekursused.kursusenimi
  JOIN k_opetajad ON k_klass_kursusel.opetaja=k_opetajad.perekonnanimi
  WHERE kursusenimi='Matemaatika 3. klass';
  
+---------+-------+------------+
| opetaja | klass | synniaasta |
+---------+-------+------------+
| Kask    | 3D    |       1958 |
| Saar    | 3B    |       1959 |
| Saar    | 3A    |       1959 |
| Tamm    | 3C    |       1956 |
+---------+-------+------------+

-- Kellel õpetab oma klassijuhataja
SELECT opetaja, klass FROM k_ainekursused 
  JOIN k_klass_kursusel ON k_klass_kursusel.ainekursus=k_ainekursused.kursusenimi
  JOIN k_klassid ON k_klass_kursusel.klass=k_klassid.klassinimi
  WHERE kursusenimi='Matemaatika 3. klass'
    AND k_klassid.klassijuhataja=k_klass_kursusel.opetaja;

+---------+-------+
| opetaja | klass |
+---------+-------+
| Tamm    | 3C    |
| Saar    | 3B    |
+---------+-------+