Mitu mitmele seos

kool_opilased(id, eesnimi, telefon)
kool_kursused(id, nimetus, hindamisvorm)  ENUM('A', 'E')
kool_opilane_kursusel(id, opilase_id, kursuse_id, aasta)

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema jaagup_2016
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema jaagup_2016
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `jaagup_2016` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `jaagup_2016` ;

-- -----------------------------------------------------
-- Table `jaagup_2016`.`kool_opilased`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2016`.`kool_opilased` ;

CREATE TABLE IF NOT EXISTS `jaagup_2016`.`kool_opilased` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `eesnimi` VARCHAR(45) NOT NULL,
  `telefon` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `jaagup_2016`.`kool_kursused`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2016`.`kool_kursused` ;

CREATE TABLE IF NOT EXISTS `jaagup_2016`.`kool_kursused` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nimetus` VARCHAR(45) NOT NULL,
  `hindamisvorm` ENUM('A', 'E') NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `jaagup_2016`.`kool_opilane_kursusel`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2016`.`kool_opilane_kursusel` ;

CREATE TABLE IF NOT EXISTS `jaagup_2016`.`kool_opilane_kursusel` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `opilase_id` INT NOT NULL,
  `kursuse_id` INT NOT NULL,
  `aasta` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_kool_opilane_kursusel_kool_opilased_idx` (`opilase_id` ASC),
  INDEX `fk_kool_opilane_kursusel_kool_kursused1_idx` (`kursuse_id` ASC),
  CONSTRAINT `fk_kool_opilane_kursusel_kool_opilased`
    FOREIGN KEY (`opilase_id`)
    REFERENCES `jaagup_2016`.`kool_opilased` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_kool_opilane_kursusel_kool_kursused1`
    FOREIGN KEY (`kursuse_id`)
    REFERENCES `jaagup_2016`.`kool_kursused` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;


INSERT INTO kool_opilased VALUES (DEFAULT, 'Juku', 4734223);
INSERT INTO kool_opilased VALUES (DEFAULT, 'Kati', 4734221);
INSERT INTO kool_opilased VALUES (DEFAULT, 'Mati', 4734222);

mysql> SELECT * FROM kool_opilased;
+----+---------+---------+
| id | eesnimi | telefon |
+----+---------+---------+
|  1 | Juku    | 4734223 |
|  2 | Kati    | 4734221 |
|  3 | Mati    | 4734222 |
+----+---------+---------+

INSERT INTO kool_kursused VALUES (DEFAULT, 'Matemaatika 9. klass', 'E');
INSERT INTO kool_kursused VALUES (DEFAULT, 'Loodusreis Lapimaale', 'A');


+----+----------------------+--------------+
| id | nimetus              | hindamisvorm |
+----+----------------------+--------------+
|  1 | Loodusreis Lapimaale | A            |
|  2 | Matemaatika 9. klass | E            |
+----+----------------------+--------------+

INSERT INTO kool_opilane_kursusel VALUES (DEFAULT, 3, 2, 2015);
INSERT INTO kool_opilane_kursusel VALUES (DEFAULT, 3, 1, 2015);
INSERT INTO kool_opilane_kursusel VALUES (DEFAULT, 2, 1, 2015);
INSERT INTO kool_opilane_kursusel VALUES (DEFAULT, 2, 7, 2015); -- ei saa

SELECT * FROM kool_opilased
  JOIN kool_opilane_kursusel ON kool_opilane_kursusel.opilase_id=kool_opilased.id
  JOIN kool_kursused ON kool_opilane_kursusel.kursuse_id=kool_kursused.id;
  

+----+---------+---------+----+------------+------------+-------+----+----------------------+--------------+
| id | eesnimi | telefon | id | opilase_id | kursuse_id | aasta | id | nimetus              | hindamisvorm |
+----+---------+---------+----+------------+------------+-------+----+----------------------+--------------+
|  3 | Mati    | 4734222 |  1 |          3 |          2 |  2015 |  2 | Matemaatika 9. klass | E            |
|  3 | Mati    | 4734222 |  2 |          3 |          1 |  2015 |  1 | Loodusreis Lapimaale | A            |
|  2 | Kati    | 4734221 |  3 |          2 |          1 |  2015 |  1 | Loodusreis Lapimaale | A            |
+----+---------+---------+----+------------+------------+-------+----+----------------------+--------------+

SELECT * FROM kool_opilased
  JOIN kool_opilane_kursusel ON kool_opilane_kursusel.opilase_id=kool_opilased.id
  JOIN kool_kursused ON kool_opilane_kursusel.kursuse_id=kool_kursused.id
   WHERE kool_opilased.id=3; 
   

+----+---------+---------+----+------------+------------+-------+----+----------------------+--------------+
| id | eesnimi | telefon | id | opilase_id | kursuse_id | aasta | id | nimetus              | hindamisvorm |
+----+---------+---------+----+------------+------------+-------+----+----------------------+--------------+
|  3 | Mati    | 4734222 |  2 |          3 |          1 |  2015 |  1 | Loodusreis Lapimaale | A            |
|  3 | Mati    | 4734222 |  1 |          3 |          2 |  2015 |  2 | Matemaatika 9. klass | E            |
+----+---------+---------+----+------------+------------+-------+----+----------------------+--------------+


SELECT kool_kursused.nimetus FROM kool_opilased
  JOIN kool_opilane_kursusel ON kool_opilane_kursusel.opilase_id=kool_opilased.id
  JOIN kool_kursused ON kool_opilane_kursusel.kursuse_id=kool_kursused.id
   WHERE kool_opilased.id=3; 
   
+----------------------+
| nimetus              |
+----------------------+
| Loodusreis Lapimaale |
| Matemaatika 9. klass |
+----------------------+

-- Väljastage, mitmel kursusel on  kirjas õpilane nr. 3

SELECT COUNT(kool_kursused.nimetus) FROM kool_opilased
  JOIN kool_opilane_kursusel ON kool_opilane_kursusel.opilase_id=kool_opilased.id
  JOIN kool_kursused ON kool_opilane_kursusel.kursuse_id=kool_kursused.id
   WHERE kool_opilased.id=3; 
+------------------------------+
| COUNT(kool_kursused.nimetus) |
+------------------------------+
|                            2 |
+------------------------------+

SELECT COUNT(*) FROM kool_opilane_kursusel WHERE opilase_id=3;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+


-- Väljastage iga kursuse kohta, mitu õpilast seal kirjas on

SELECT kursuse_id, COUNT(opilase_id) FROM kool_opilane_kursusel 
  GROUP BY kursuse_id;
+------------+-------------------+
| kursuse_id | COUNT(opilase_id) |
+------------+-------------------+
|          1 |                 2 |
|          2 |                 1 |
+------------+-------------------+

SELECT kool_kursused.nimetus, COUNT(opilase_id) FROM kool_opilane_kursusel 
  JOIN kool_kursused ON kool_opilane_kursusel.kursuse_id=kool_kursused.id
  GROUP BY kursuse_id;
+----------------------+-------------------+
| nimetus              | COUNT(opilase_id) |
+----------------------+-------------------+
| Loodusreis Lapimaale |                 2 |
| Matemaatika 9. klass |                 1 |
+----------------------+-------------------+

SELECT kool_kursused.nimetus, COUNT(opilase_id), GROUP_CONCAT(eesnimi) 
    FROM kool_opilane_kursusel 
  JOIN kool_kursused ON kool_opilane_kursusel.kursuse_id=kool_kursused.id
  JOIN kool_opilased ON kool_opilane_kursusel.opilase_id=kool_opilased.id
  GROUP BY kursuse_id;


+----------------------+-------------------+-----------------------+
| nimetus              | COUNT(opilase_id) | GROUP_CONCAT(eesnimi) |
+----------------------+-------------------+-----------------------+
| Loodusreis Lapimaale |                 2 | Mati,Kati             |
| Matemaatika 9. klass |                 1 | Mati                  |
+----------------------+-------------------+-----------------------+
  
-- Väljastage iga kursuse ja aasta kohta, mitu õpilast seal kirjas on (olnud)
SELECT aasta, kursuse_id, COUNT(*) FROM kool_opilane_kursusel GROUP BY kursuse_id, aasta;

+-------+------------+----------+
| aasta | kursuse_id | COUNT(*) |
+-------+------------+----------+
|  2015 |          1 |        2 |
|  2015 |          2 |        1 |
|  2016 |          2 |        1 |
+-------+------------+----------+

-- lisage kursuste nimed
-- lisage õpilaste nimed