Teemade ideid

Kümnevõistlus             2
Autoesindus               1
Automüük (auto24)         5    5
Tuunimine                 1
E-kool                    5    3
Brauseripõhine mäng       0
Mängude keskkond/skoorid  6    4
Telekava - kanalid, sarjad 4
Kaubanduskeskus           2


Auto24 automüük

Eeldatavad tabelid


kytusetyybid
kaigukastityybid
sillatyybid
varvused
asukohad

a24_soidukid
a24_pildid
a24_myyjad
a24_soidukityybid
a24_keretyybid
a24_margid
a24_mudelid
a24_soiduk_varustus
a24_varustus
a24_varustusgrupp



-- 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`.`a24_soidukityybid`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2015`.`a24_soidukityybid` ;

CREATE TABLE IF NOT EXISTS `jaagup_2015`.`a24_soidukityybid` (
  `soidukityyp` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`soidukityyp`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `jaagup_2015`.`a24_keretyybid` (
  `id` INT NOT NULL,
  `tyybinimi` VARCHAR(45) NOT NULL,
  `soidukityyp` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_a24_keretyybid_a24_soidukityybid_idx` (`soidukityyp` ASC),
  CONSTRAINT `fk_a24_keretyybid_a24_soidukityybid`
    FOREIGN KEY (`soidukityyp`)
    REFERENCES `jaagup_2015`.`a24_soidukityybid` (`soidukityyp`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `jaagup_2015`.`a24_margid` (
  `mark` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`mark`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `jaagup_2015`.`a24_mudelid` (
  `id` INT NOT NULL,
  `mudel` VARCHAR(45) NULL,
  `mark` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_a24_mudelid_a24_margid1_idx` (`mark` ASC),
  CONSTRAINT `fk_a24_mudelid_a24_margid1`
    FOREIGN KEY (`mark`)
    REFERENCES `jaagup_2015`.`a24_margid` (`mark`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `jaagup_2015`.`a24_myyjad` (
  `knimi` VARCHAR(45) NOT NULL,
  `paroorir2si` CHAR(41) NULL,
  `telefon` VARCHAR(45) NULL,
  `epost` VARCHAR(45) NULL,
  PRIMARY KEY (`knimi`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `jaagup_2015`.`a24_soidukid` (
  `id` INT NOT NULL,
  `keretyybi_id` INT NOT NULL,
  `mudeli_id` INT NOT NULL,
  `myyja` VARCHAR(45) NOT NULL,
  `hind` INT NOT NULL,
  `esmanereg` DATE NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_a24_soidukid_a24_keretyybid1_idx` (`keretyybi_id` ASC),
  INDEX `fk_a24_soidukid_a24_mudelid1_idx` (`mudeli_id` ASC),
  INDEX `fk_a24_soidukid_a24_myyjad1_idx` (`myyja` ASC),
  CONSTRAINT `fk_a24_soidukid_a24_keretyybid1`
    FOREIGN KEY (`keretyybi_id`)
    REFERENCES `jaagup_2015`.`a24_keretyybid` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_a24_soidukid_a24_mudelid1`
    FOREIGN KEY (`mudeli_id`)
    REFERENCES `jaagup_2015`.`a24_mudelid` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_a24_soidukid_a24_myyjad1`
    FOREIGN KEY (`myyja`)
    REFERENCES `jaagup_2015`.`a24_myyjad` (`knimi`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `jaagup_2015`.`a24_pildid` (
  `id` INT NOT NULL,
  `soiduki_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_a24_pildid_a24_soidukid1_idx` (`soiduki_id` ASC),
  CONSTRAINT `fk_a24_pildid_a24_soidukid1`
    FOREIGN KEY (`soiduki_id`)
    REFERENCES `jaagup_2015`.`a24_soidukid` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `jaagup_2015`.`a24_varustusgrupp` (
  `grupinimi` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`grupinimi`))
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `jaagup_2015`.`a24_varustus` (
  `id` INT NOT NULL,
  `varustusgrupp` VARCHAR(45) NOT NULL,
  `varustus` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_a24_varustus_a24_varustusgrupp1_idx` (`varustusgrupp` ASC),
  CONSTRAINT `fk_a24_varustus_a24_varustusgrupp1`
    FOREIGN KEY (`varustusgrupp`)
    REFERENCES `jaagup_2015`.`a24_varustusgrupp` (`grupinimi`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

CREATE TABLE IF NOT EXISTS `jaagup_2015`.`a24_soiduk_varustus` (
  `id` INT NOT NULL,
  `soiduki_id` INT NOT NULL,
  `varustuse_id` INT NOT NULL,
  `kommentaar` VARCHAR(255) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_a24_soiduk_varustus_a24_soidukid1_idx` (`soiduki_id` ASC),
  INDEX `fk_a24_soiduk_varustus_a24_varustus1_idx` (`varustuse_id` ASC),
  CONSTRAINT `fk_a24_soiduk_varustus_a24_soidukid1`
    FOREIGN KEY (`soiduki_id`)
    REFERENCES `jaagup_2015`.`a24_soidukid` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_a24_soiduk_varustus_a24_varustus1`
    FOREIGN KEY (`varustuse_id`)
    REFERENCES `jaagup_2015`.`a24_varustus` (`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 a24_soidukityybid (soidukityyp) VALUES ('kaubik');
INSERT INTO a24_soidukityybid (soidukityyp) VALUES ('maastur');
INSERT INTO a24_soidukityybid (soidukityyp) VALUES ('veoauto');
INSERT INTO a24_soidukityybid (soidukityyp) VALUES ('');


Päringud

Andmed sõiduki kohta:

SELECT myyja, hind 
  FROM a24_soidukid
  WHERE a24_soidukid.id=1;
+-------+------+
| myyja | hind |
+-------+------+
| juku  |  700 |
+-------+------+

SELECT myyja, hind, soidukityyp, tyybinimi
  FROM a24_soidukid
    JOIN a24_keretyybid ON keretyybi_id=a24_keretyybid.id
  WHERE a24_soidukid.id=1;
+-------+------+-------------+------------+
| myyja | hind | soidukityyp | tyybinimi  |
+-------+------+-------------+------------+
| juku  |  700 | s?iduauto   | universaal |
+-------+------+-------------+------------+

  
-- Lisage mark ja mudel

SELECT myyja, hind, soidukityyp, tyybinimi, mudel, mark
  FROM a24_soidukid
    JOIN a24_keretyybid ON keretyybi_id=a24_keretyybid.id
	JOIN a24_mudelid ON mudeli_id=a24_mudelid.id
  WHERE a24_soidukid.id=1;

+-------+------+-------------+------------+--------+------+
| myyja | hind | soidukityyp | tyybinimi  | mudel  | mark |
+-------+------+-------------+------------+--------+------+
| juku  |  700 | s?iduauto   | universaal | Meriva | Opel |
+-------+------+-------------+------------+--------+------+

--Lisage elektronpost ja telefon`
SELECT myyja, hind, soidukityyp, tyybinimi, mudel, mark, epost, telefon
  FROM a24_soidukid
    JOIN a24_keretyybid ON keretyybi_id=a24_keretyybid.id
	JOIN a24_mudelid ON mudeli_id=a24_mudelid.id
	JOIN a24_myyjad ON a24_soidukid.myyja=a24_myyjad.knimi
  WHERE a24_soidukid.id=1;
+-------+------+-------------+------------+--------+------+--------------+---------+
| myyja | hind | soidukityyp | tyybinimi  | mudel  | mark | epost        | telefon |
+-------+------+-------------+------------+--------+------+--------------+---------+
| juku  |  700 | s?iduauto   | universaal | Meriva | Opel | juku@mail.ee | 123456  |
+-------+------+-------------+------------+--------+------+--------------+---------+

SELECT varustusgrupp, varustus FROM
  a24_varustus 
    JOIN a24_soiduk_varustus ON a24_soiduk_varustus.varustuse_id=a24_varustus.id 
  WHERE a24_soiduk_varustus.soiduki_id=1;
 
+---------------+------------------+
| varustusgrupp | varustus         |
+---------------+------------------+
| Tuled         | Tugev pidurituli |
+---------------+------------------+


Universaalkerega sõidukite andmed:

SELECT * FROM a24_keretyybid
  JOIN a24_soidukid ON a24_soidukid.keretyybi_id=a24_keretyybid.id
  WHERE a24_keretyybid.tyybinimi='universaal';
  

SELECT soidukityyp, mark FROM a24_keretyybid  
   JOIN a24_soidukid ON a24_soidukid.keretyybi_id=a24_keretyybid.id
   JOIN a24_mudelid ON a24_soidukid.mudeli_id=a24_mudelid.id;

+-------------+------------+
| soidukityyp | mark       |
+-------------+------------+
| s?iduauto   | Opel       |
| s?iduauto   | Volkswagen |
| kaubik      | Opel       |
+-------------+------------+

  
   
SELECT COUNT(*), soidukityyp, mark FROM a24_keretyybid  
   JOIN a24_soidukid ON a24_soidukid.keretyybi_id=a24_keretyybid.id
   JOIN a24_mudelid ON a24_soidukid.mudeli_id=a24_mudelid.id
   GROUP BY soidukityyp, mark;


+----------+-------------+------------+
| COUNT(*) | soidukityyp | mark       |
+----------+-------------+------------+
|        1 | kaubik      | Opel       |
|        1 | s?iduauto   | Opel       |
|        1 | s?iduauto   | Volkswagen |
+----------+-------------+------------+