wHutk6fw

1DROP DATABASE IF EXISTS `Toomas-av31`;
2CREATE DATABASE `Toomas-av31`;
3USE `Toomas-av31`;
4 
5CREATE TABLE `tooted` (
6    id INT NOT NULL AUTO_INCREMENT,
7    tootekood varchar(16) NOT NULL DEFAULT "AA1",
8    tootenimetus varchar(64) NOT NULL DEFAULT "Tundmatu",
9    kogus INT UNSIGNED NOT NULL DEFAULT 0,
10    kaal INT UNSIGNED NOT NULL DEFAULT 0,
11    hind decimal(7,2) UNSIGNED 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` VALUES
32(NULL, "AA1", "Joonlaud A", 800, 2, 1.30, "2016-08-24"),
33(NULL, "AA2", "Joonlaud B", 87, 2, 1.40, "2005-07-17"),
34(NULL, "AA3", "Joonlaud C", 85, 2, 1.20, "2009-12-1"),
35(NULL, "BB1", "Sirkel A", 52, 5, 2.50, "2008-08-04"),
36(NULL, "BB2", "Sirkel B", 45, 5, 2.50, "2012-04-09"),
37(NULL, "BB3", "Pliiats A", 89, 2, 1.45, "2001-07-12"),
38(NULL, "CC1", "Pliiats B", 73, 2, 1.45, "2004-01-10"),
39(NULL, "CC2", "Vihik A", 48, 3, 0.75, "2014-08-24"),
40(NULL, "CC3", "Pastakas A", 58, 2, 2.30, "2012-04-15"),
41(NULL, "CC4", "Pastakas B", 89, 2, 2.50, "2013-11-18");
42 
43INSERT INTO `tootjad` VALUES
44(NULL, "Firma HJK", "Kuperjanovi 8, Tartu", 50055005),
45(NULL, "Firma RTY", "Viljandi 14, Türi", 51155115),
46(NULL, "Firma LKJ", "Tallinna 20, Paide", 52255225),
47(NULL, "Firma ÖLI", "Riia 35, Tartu", 53355335),
48(NULL, "Firma QWE", "Ranna 3, Pärnu", 54455445),
49(NULL, "Firma TYH", "Linnuse 23, Rakvere", 55555555),
50(NULL, "Firma BVW", "Kaare 9, Tartu ", 56655665),
51(NULL, "Firma KZE", "Raatuse 14, Tartu", 57755775),
52(NULL, "Firma KHR", "Pärnu 19, Tallinn", 58855885),
53(NULL, "Firma AOR", "Raekoja 29. Tartu", 59955995);
54 
55SELECT tooted.tootekood,DATE_FORMAT(tooted.kuupäev,"%d.%m.%Y"),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;