-- 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 if15_jaagup_3
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema if15_jaagup_3
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `if15_jaagup_3` DEFAULT CHARACTER SET utf8 COLLATE utf8_estonian_ci ;
USE `if15_jaagup_3` ;

-- -----------------------------------------------------
-- Table `if15_jaagup_3`.`turva_kaardid`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `if15_jaagup_3`.`turva_kaardid` ;

CREATE TABLE IF NOT EXISTS `if15_jaagup_3`.`turva_kaardid` (
  `kaardinr` INT NOT NULL COMMENT '',
  `eesnimi` VARCHAR(45) NULL COMMENT '',
  PRIMARY KEY (`kaardinr`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `if15_jaagup_3`.`turva_uksed`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `if15_jaagup_3`.`turva_uksed` ;

CREATE TABLE IF NOT EXISTS `if15_jaagup_3`.`turva_uksed` (
  `uksenr` INT NOT NULL COMMENT '',
  `kirjeldus` VARCHAR(45) NULL COMMENT '',
  PRIMARY KEY (`uksenr`)  COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `if15_jaagup_3`.`turva_load`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `if15_jaagup_3`.`turva_load` ;

CREATE TABLE IF NOT EXISTS `if15_jaagup_3`.`turva_load` (
  `kaardinr` INT NOT NULL COMMENT '',
  `uksenr` INT NOT NULL COMMENT '',
  PRIMARY KEY (`kaardinr`, `uksenr`)  COMMENT '',
  INDEX `fk_turva_load_turva_uksed1_idx` (`uksenr` ASC)  COMMENT '',
  CONSTRAINT `fk_turva_load_turva_kaardid`
    FOREIGN KEY (`kaardinr`)
    REFERENCES `if15_jaagup_3`.`turva_kaardid` (`kaardinr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_turva_load_turva_uksed1`
    FOREIGN KEY (`uksenr`)
    REFERENCES `if15_jaagup_3`.`turva_uksed` (`uksenr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `if15_jaagup_3`.`turva_logi`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `if15_jaagup_3`.`turva_logi` ;

CREATE TABLE IF NOT EXISTS `if15_jaagup_3`.`turva_logi` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `kaardinr` INT NOT NULL COMMENT '',
  `uksenr` INT NOT NULL COMMENT '',
  `aeg` DATETIME NOT NULL COMMENT '',
  `suund` CHAR(1) NOT NULL COMMENT 's - sisse\nv - valja',
  PRIMARY KEY (`id`)  COMMENT '',
  INDEX `fk_turva_logi_turva_kaardid1_idx` (`kaardinr` ASC)  COMMENT '',
  INDEX `fk_turva_logi_turva_uksed1_idx` (`uksenr` ASC)  COMMENT '',
  CONSTRAINT `fk_turva_logi_turva_kaardid1`
    FOREIGN KEY (`kaardinr`)
    REFERENCES `if15_jaagup_3`.`turva_kaardid` (`kaardinr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_turva_logi_turva_uksed1`
    FOREIGN KEY (`uksenr`)
    REFERENCES `if15_jaagup_3`.`turva_uksed` (`uksenr`)
    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;

-- -----------------------------------------------------
-- Data for table `if15_jaagup_3`.`turva_kaardid`
-- -----------------------------------------------------
START TRANSACTION;
USE `if15_jaagup_3`;
INSERT INTO `if15_jaagup_3`.`turva_kaardid` (`kaardinr`, `eesnimi`) VALUES (1, 'Juhan');
INSERT INTO `if15_jaagup_3`.`turva_kaardid` (`kaardinr`, `eesnimi`) VALUES (2, 'Juku');

COMMIT;


-- -----------------------------------------------------
-- Data for table `if15_jaagup_3`.`turva_uksed`
-- -----------------------------------------------------
START TRANSACTION;
USE `if15_jaagup_3`;
INSERT INTO `if15_jaagup_3`.`turva_uksed` (`uksenr`, `kirjeldus`) VALUES (1, 'välisuks');
INSERT INTO `if15_jaagup_3`.`turva_uksed` (`uksenr`, `kirjeldus`) VALUES (2, 'keemiaklass');
INSERT INTO `if15_jaagup_3`.`turva_uksed` (`uksenr`, `kirjeldus`) VALUES (3, 'õpetajate tuba');

COMMIT;


-- -----------------------------------------------------
-- Data for table `if15_jaagup_3`.`turva_load`
-- -----------------------------------------------------
START TRANSACTION;
USE `if15_jaagup_3`;
INSERT INTO `if15_jaagup_3`.`turva_load` (`kaardinr`, `uksenr`) VALUES (1, 1);
INSERT INTO `if15_jaagup_3`.`turva_load` (`kaardinr`, `uksenr`) VALUES (1, 2);
INSERT INTO `if15_jaagup_3`.`turva_load` (`kaardinr`, `uksenr`) VALUES (1, 3);
INSERT INTO `if15_jaagup_3`.`turva_load` (`kaardinr`, `uksenr`) VALUES (2, 1);
INSERT INTO `if15_jaagup_3`.`turva_load` (`kaardinr`, `uksenr`) VALUES (2, 2);

COMMIT;

mysql> select * from turva_kaardid;
+----------+---------+
| kaardinr | eesnimi |
+----------+---------+
|        1 | Juhan   |
|        2 | Juku    |
+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from turva_uksed;
+--------+-----------------+
| uksenr | kirjeldus       |
+--------+-----------------+
|      1 | välisuks        |
|      2 | keemiaklass     |
|      3 | õpetajate tuba  |
+--------+-----------------+
3 rows in set (0.00 sec)

mysql> select * from turva_load;
+----------+--------+
| kaardinr | uksenr |
+----------+--------+
|        1 |      1 |
|        2 |      1 |
|        1 |      2 |
|        2 |      2 |
|        1 |      3 |
+----------+--------+
5 rows in set (0.00 sec)


--Millise eesnimega inimesed tohivad minna keemiaklassi:

SELECT eesnimi FROM turva_kaardid
   JOIN turva_load ON turva_kaardid.kaardinr=turva_load.kaardinr
   JOIN turva_uksed ON turva_load.uksenr=turva_uksed.uksenr
   WHERE turva_uksed.kirjeldus='keemiaklass';
   

+---------+
| eesnimi |
+---------+
| Juhan   |
| Juku    |
+---------+

-- Näidake iga inimese kohta, mitmest uksest ta tohib sisse minna
 COUNT(*) ja piisab kahest tablist
 
SELECT eesnimi, COUNT(*) FROM turva_kaardid
  JOIN turva_load ON turva_load.kaardinr=turva_kaardid.kaardinr
  GROUP BY turva_kaardid.kaardinr;
  
INSERT INTO turva_kaardid (kaardinr, eesnimi) VALUES (101, 'Juku');

SELECT eesnimi, COUNT(turva_load.uksenr) FROM turva_kaardid
  LEFT JOIN turva_load ON turva_load.kaardinr=turva_kaardid.kaardinr
  GROUP BY turva_kaardid.kaardinr;
  
+---------+--------------------------+
| eesnimi | COUNT(turva_load.uksenr) |
+---------+--------------------------+
| Juhan   |                        3 |
| Juku    |                        2 |
| Juku    |                        0 |
+---------+--------------------------+

 
-- Näidake iga inimese kohta millistest (kirjeldus) ustest ta tohib sisse minna
 GROUP_CONCAT ja kolm tabelit
 
SELECT eesnimi, turva_kaardid.kaardinr, GROUP_CONCAT(turva_uksed.kirjeldus) FROM turva_kaardid
  LEFT JOIN turva_load ON turva_load.kaardinr=turva_kaardid.kaardinr
  LEFT JOIN turva_uksed ON turva_load.uksenr=turva_uksed.uksenr
  GROUP BY turva_kaardid.kaardinr;
  
+---------+----------+---------------------------------------+
| eesnimi | kaardinr | GROUP_CONCAT(turva_uksed.kirjeldus)   |
+---------+----------+---------------------------------------+
| Juhan   |        1 | välisuks,keemiaklass,õpetajate tuba   |
| Juku    |        2 | välisuks,keemiaklass                  |
| Juku    |      101 | NULL                                  |
+---------+----------+---------------------------------------+



-- Juhan tuleb välisuksest sisse

INSERT INTO turva_logi (kaardinr, uksenr, aeg, suund) VALUES 
  (1, 1, NOW(), 's');
  
mysql> SELECT * FROM turva_logi;
+----+----------+--------+---------------------+-------+
| id | kaardinr | uksenr | aeg                 | suund |
+----+----------+--------+---------------------+-------+
|  1 |        1 |      1 | 2015-10-30 11:28:57 | s     |
+----+----------+--------+---------------------+-------+

INSERT INTO turva_logi (kaardinr, uksenr, aeg, suund) VALUES  (2, 1, NOW(), 's');

INSERT INTO turva_logi (kaardinr, uksenr, aeg, suund) VALUES  (2, 1, NOW(), 'v');


mysql> SELECT * FROM turva_logi;                                                +----+----------+--------+---------------------+-------+
| id | kaardinr | uksenr | aeg                 | suund |
+----+----------+--------+---------------------+-------+
|  1 |        1 |      1 | 2015-10-30 11:28:57 | s     |
|  2 |        2 |      1 | 2015-10-30 11:30:30 | s     |
|  3 |        2 |      1 | 2015-10-30 11:30:45 | v     |
+----+----------+--------+---------------------+-------+


-- Kas Juku tohib keemiaklassi minna:

SELECT COUNT(*) FROM turva_load WHERE kaardinr=2 AND uksenr=2;

+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

-- kas Juku tohib õpetajate tuppa minna

SELECT COUNT(*) FROM turva_load WHERE kaardinr=2 AND uksenr=3;

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

Kas Juku on majas:

SELECT (SELECT COUNT(*) FROM turva_logi WHERE kaardinr=2 AND uksenr=1 AND suund='s') - 
  (SELECT COUNT(*) FROM turva_logi WHERE kaardinr=2 AND uksenr=1 AND suund='v') AS sees;
  
 
+------+
| sees |
+------+
|    0 |
+------+

SET @knr=1;
SELECT (SELECT COUNT(*) FROM turva_logi WHERE kaardinr=@knr AND uksenr=1 AND suund='s') - 
  (SELECT COUNT(*) FROM turva_logi WHERE kaardinr=@knr AND uksenr=1 AND suund='v') AS sees;

+------+
| sees |
+------+
|    1 |
+------+