rvyPPGYh

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 "PEC",
8    Tootenimetus varchar(64) NOT NULL DEFAULT "ASD",
9    Kogus INT NOT NULL DEFAULT -1,
10    Kaal INT NOT NULL DEFAULT -1,
11    Hind decimal(7,2) NOT NULL DEFAULT -1,
12    Kuupäev DATE NOT NULL DEFAULT "2005-05-05",
13    PRIMARY KEY (ID)
14);
15 
16CREATE TABLE `Tootjad` (
17    ID INT NOT NULL AUTO_INCREMENT,
18    Tootjanimi varchar(32) NOT NULL DEFAULT "ASD",
19    Aadress varchar(64) NOT NULL DEFAULT "ASD",
20    Telefon INT NOT NULL DEFAULT 51234567
21    PRIMARY KEY (ID)
22);
23 
24CREATE TABLE `Tabel` (
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, "Tootja1", "Aia mnt 5,Paide", 51123456),
45(NULL, "Tootja2", "Kodu pst 6,Tallinn", 52123456),
46(NULL, "Tootja3", "Koidu mnt 7,Tallinn,", 53123456),
47(NULL, "Tootja4", "Paide mnt 5,Türi", 54123456),
48(NULL, "Tootja5", "Türi 8,Paide", 55123456),
49(NULL, "Tootja6", "Tartu 6,Pärnu", 56123456),
50(NULL, "Tootja7", "Tallina 6,Tartu", 57123456),
51(NULL, "Tootja8", "Rapla 7,Paide", 58123456),
52(NULL, "Tootja9", "Tallina 17,Paide", 59123456),
53(NULL, "Tootja10", "Paide 16,Tallinn", 50123456);
54
55SELECT Tooted.Tootekood,DATE_FORMAT(Tooted.Kuupäev,"%y,%m,%d"),sum(Tooted.Kaal+Tooted.Kogus) FROM `Tooted` GROUP BY Tooted.Tootekood;
56SELECT Tootjad.ID,Tootjad.Tootjanimi,Tootjad.Aadress,Tootjad.Telefon,Tooted.Tootenimetus,Tooted.Tootekood,AVG(Tooted.Kaal)
57FROM `Tootjad`
58JOIN `Tooted`
59ON Tooted.ID=Tootjad.ID
60GROUP BY Tooted.Tootenimetus ASC;