i4q4DgcH

1DROP DATABASE IF EXISTS `rain-av31`;
2CREATE DATABASE `rain-av31`;
3USE DATABASE `rain-av31`;
4 
5CREATE TABLE `Tooted` (
6    ID INT NOT NULL AUTO_INCREMENT,
7    Tootekood varchar(16) NOT NULL DEFAULT "AAA",
8    Tootenimetus varchar(64) NOT NULL DEFAULT "Tundmatu",
9    Kogus INT NOT NULL DEFAULT 0,
10    Kaal INT NOT NULL DEFAULT 0,
11    Hind decimal(7,2) NOT NULL DEFAULT 0,
12    Kuupäev DATE NOT NULL DEFAULT "2016-01-01",
13    PRIMARY KEY (ID)
14);
15 
16CREATE TABLE `Tootjad` (
17    ID INT NOT NULL AUTO_INCREMENT,
18    Tootjanimi varchar(32) NOT NULL DEFAULT "Tundmatu",
19    Aadress varchar(64) NOT NULL DEFAULT "Tundmatu",
20    Telefon INT NOT NULL DEFAULT 53000000
21    PRIMARY KEY (ID)
22);
23 
24CREATE TABLE `Võtmed` (
25    ID_Tooted INT NOT NULL,
26    ID_Tootjad INT NOT NULL,
27    CONSTRAINT fk_Tooted FOREIGN KEY (ID_Tooted) REFERENCES Tooted(ID),
28    CONSTRAINT fk_Tootjad FOREIGN KEY (ID_Tootjad) REFERENCES Tootjad(ID)
29);
30
31INSERT INTO `Tooted` VALUE
32(NULL, "PEC", "Pliiats 3B", 500, 8, 0.52, "1998-07-12"),
33(NULL, "PEC", "Pliiats 4B", 200, 7, 0.62, "1996-08-12"),
34(NULL, "PEC", "Pliiats 5B", 100, 4, 0.73, "1995-09-12"),
35(NULL, "PEC", "Pliiats 6B", 500, 5, 0.47, "1992-06-12"),
36(NULL, "REC", "Pastapliiats 3C", 300, 8, 1.43, "2001-03-08"),
37(NULL, "REC", "Pastapliiats 4C", 600, 7, 1.21, "2003-02-09"),
38(NULL, "REC", "Pastapliiats 5C", 200, 6, 1.78, "2004-01-11"),
39(NULL, "BEC", "Viltpliiats 6U", 100, 8, 1.00, "2009-06-25"),
40(NULL, "BEC", "Viltpliiats 5U", 100, 7, 1.12, "2008-07-14"),
41(NULL, "BEC", "Viltpliiats 4U", 100, 6, 1.23, "2007-06-21"),
42
43INSERT INTO `Tootjad` VALUES
44(NULL, "Firma A", "Joonase pst 5,Tallinn", 53606060),
45(NULL, "Firma B", "Joonase mnt 6,Tallinn", 53639606),
46(NULL, "Firma C", "Joonase 5,Tallinn,", 53603636),
47(NULL, "Firma D", "Mihkli mnt 6,Tallinn", 53607777),
48(NULL, "Firma E", "Antoni 7,Tartu", 53603533),
49(NULL, "Firma F", "Tartu pst 7,Pärnu", 53603536),
50(NULL, "Firma G", "Paide 6,Türi", 53609755),
51(NULL, "Firma H", "Tallinna 32,Paide", 53605469),
52(NULL, "Firma I", "Mihkli 33,Tallinn", 53607953),
53(NULL, "Firma J", "Pavela 55", 53606049);