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

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

-- -----------------------------------------------------
-- Table `jaagup_2015`.`rollid`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2015`.`rollid` ;

CREATE TABLE IF NOT EXISTS `jaagup_2015`.`rollid` (
  `roll` VARCHAR(20) NOT NULL,
  `kirjeldus` VARCHAR(45) NULL,
  PRIMARY KEY (`roll`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `jaagup_2015`.`riigid`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2015`.`riigid` ;

CREATE TABLE IF NOT EXISTS `jaagup_2015`.`riigid` (
  `kood` CHAR(3) NOT NULL,
  `riiginimi` VARCHAR(45) NULL,
  `rahvaarv` INT NULL,
  PRIMARY KEY (`kood`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `jaagup_2015`.`isikud`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2015`.`isikud` ;

CREATE TABLE IF NOT EXISTS `jaagup_2015`.`isikud` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `eesnimi` VARCHAR(45) NULL,
  `perekonnanimi` VARCHAR(45) NULL,
  `roll` VARCHAR(20) NULL,
  `kodakondsus` CHAR(3) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_isikud_rollid_idx` (`roll` ASC),
  INDEX `fk_isikud_riigid1_idx` (`kodakondsus` ASC),
  CONSTRAINT `fk_isikud_rollid`
    FOREIGN KEY (`roll`)
    REFERENCES `jaagup_2015`.`rollid` (`roll`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_isikud_riigid1`
    FOREIGN KEY (`kodakondsus`)
    REFERENCES `jaagup_2015`.`riigid` (`kood`)
    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 rollid (roll, kirjeldus) VALUES ('opetaja', 'Kursuste haldur');
INSERT INTO rollid (roll, kirjeldus) VALUES ('opilane', 'Kursuste kasutaja');
INSERT INTO rollid (roll, kirjeldus) VALUES ('admin', 'Kasutajate haldur');


INSERT INTO riigid (kood, riiginimi, rahvaarv) VALUES ('GER', 'Saksamaa', 80000000);
INSERT INTO riigid (kood, riiginimi, rahvaarv) VALUES ('GBR', 'Inglismaa',49000000);
INSERT INTO riigid (kood, riiginimi, rahvaarv) VALUES ('FRA', 'Prantsusmaa', 49000000);
INSERT INTO riigid (kood, riiginimi, rahvaarv) VALUES ('EST', 'Eesti', 1300000);

INSERT INTO isikud (eesnimi, perekonnanimi, roll, kodakondsus) VALUES 
  ('Juku', 'Tamm', 'opilane', 'EST');
INSERT INTO isikud (eesnimi, perekonnanimi, roll, kodakondsus) VALUES 
  ('Kati', 'Tamm', 'opetaja', 'EST');
INSERT INTO isikud (eesnimi, perekonnanimi, roll, kodakondsus) VALUES 
  ('Klaus', 'Berlin', 'opilane', 'GER');
  
SELECT * FROM isikud;  
  1	Juku	Tamm	opilane	EST
2	Kati	Tamm	opetaja	EST
3	Klaus	Berlin	opilane	GER


SELECT eesnimi, kirjeldus, riiginimi 
  FROM isikud, rollid, riigid
  WHERE
    isikud.roll=rollid.roll
   AND
	isikud.kodakondsus=riigid.kood;
	
Kati	Kursuste haldur	Eesti
Juku	Kursuste kasutaja	Eesti
Klaus	Kursuste kasutaja	Saksamaa

SELECT eesnimi FROM
  isikud, riigid
 WHERE 
  isikud.kodakondsus=riigid.kood
 AND
  riigid.rahvaarv>10000000;
  
Klaus


SELECT riiginimi, COUNT(*)
  FROM riigid, isikud, rollid
   WHERE 
     isikud.roll=rollid.roll
	AND 
     isikud.kodakondsus=riigid.kood
   GROUP BY riiginimi;
   
Eesti	2
Saksamaa	1