gistfile1.sql

### Structure

CREATE  TABLE IF NOT EXISTS `tube`.`film` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE  TABLE IF NOT EXISTS `tube`.`aktor` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `aktor` VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE  TABLE IF NOT EXISTS `tube`.`aktor_has_film` (
  `aktor_id` INT(10) UNSIGNED NOT NULL ,
  `film_id` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`aktor_id`, `film_id`) ,
  INDEX `fk_aktor_has_film_film1_idx` (`film_id` ASC) ,
  INDEX `fk_aktor_has_film_aktor_idx` (`aktor_id` ASC) ,
  CONSTRAINT `fk_aktor_has_film_aktor`
    FOREIGN KEY (`aktor_id` )
    REFERENCES `tube`.`aktor` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_aktor_has_film_film1`
    FOREIGN KEY (`film_id` )
    REFERENCES `tube`.`film` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

### Get aktor num

SELECT
  name,
  (SELECT COUNT(aktor_id) FROM aktor_has_film WHERE film_id = film.id) AS aktor_num
FROM
  film

### Filter by aktor num >= 5

SELECT
  film.id, name, aktor_num
FROM
  film
INNER JOIN (
	SELECT
	  film_id, COUNT(aktor_id) AS aktor_num
	FROM
	  aktor_has_film
	GROUP BY
	  film_id
	HAVING
	  aktor_num >= 5
) AS aktor_num_tbl ON film_id = film.id