VbDibDYa

1DROP DATABASE IF EXISTS `germo-av31`;
2CREATE DATABASE `germo-av31`;
3USE `germo-av31`;
4
5DROP TABLE IF EXISTS Tooted;
6DROP TABLE IF EXISTS Tootjad;
7CREATE TABLE `Tooted` (
8	ID				  INT NOT NULL AUTO_INCREMENT,
9	Tootekood	varchar(16)	  INT NOT NULL DEFAULT "AAA",
10	Tootenimetus	varchar(64)	  NOT NULL DEFAULT "Puudub",
11	Kogus				  INT UNSIGNED NOT NULL DEFAULT 0,
12	Kaal				  INT UNSIGNED NOT NULL DEFAULT 0,
13	Hind		decimal(7,2)      UNISGED NOT NULL DEFAULT 0,
14        Kuupäev				  DATE NOT NULL DEFAULT "2016-01-01",
15	PRIMARY KEY (ID)
16);
17
18CREATE TABLE 'Tootjad' (
19	ID				  INT NOT NULL AUTO_INCREMENT,
20	Tootjanimi	varchar(32)	  NOT NULL DEFAULT "Puudub",
21	Aadress 	varchar(64)	  NOT NULL DEFAULT "Puudub",
22	Telefon				  INT NOT NULL DEFAULT '55000000
23);
24
25CREATE TABLE 'Välisvõtmed' (
26	ID_Tooted			  INT NOT NULL,
27	ID_Tootjad			  INT NOT NULL,
28	CONSTRAINT FK_Tooted		  FOREIGN KEY (ID_Tooted)   REFERENCES Tooted(ID),
29	CONSTRAINT FK_Tootjad		  FOREIGN KEY (ID_Tootjad)  REFERENCES Tootjad(ID)
30);
31
32INSERT INTO 'Tooted' VALUES
33(NULL, AAA, 'ARVUTI_TOOL', 1500, '5KG', '100€', '2015-10-04'),
34(NULL, AAA, 'ARVUTI_LAUD', 2500, '25KG', '250€', '2015-08-26'),
35(NULL, AAA, 'ARVUTI_LAUALAMP', 500, '2.5KG', '25€', '2015-10-09'),
36(NULL, AAA, 'ARVUTI_LAUASAHTEL', 5000, '2KG', '5€', '2015-10-08'),
37(NULL, BBB, 'VOODIRAAM_PUIT', 2000, '30KG', '250€', '2016-06-02'),
38(NULL, BBB, 'VOODIRAAM_METALL', 3000, '60KG', '350€', '2016-10-04'),
39(NULL, BBB, 'VOODI_MADRATS', 10500, '10KG', '100€', '2016-01-21'),
40(NULL, BBB, 'VOODI_PADI', 20000, '1KG', '15€', '2016-01-29'),
41(NULL, CCC, 'KÖÖK_PLIIT', 500, '100KG', '550€', '2016-03-12'),
42(NULL, CCC, 'KÖÖK_NÕUDEPESUMASIN', 1500, '95KG', '700€', '2016-08-12'),
43(NULL, CCC, 'KÖÖK_KÜLMKAPP', 250, '150KG', '850€', '2016-10-04'),
44(NULL, CCC, 'KÖÖK_NÕUDEKOMPLEKT', 350, '10KG', '85€', '2016-04-02');
45
46INSERT INTO 'Toojad' VALUES
47(NULL, 'TOOTJA NR1', 'Vabaduse pst 4-2', 55681732),
48(NULL, 'TOOTJA NR2', 'Markenio pst 33-13', 55686937),
49(NULL, 'TOOTJA NR3', 'Vabaduse väljak 1-1', 55391739),
50(NULL, 'TOOTJA NR4', 'Bendover tnv 51-3', 55681128),
51(NULL, 'TOOTJA NR5', 'Deesnots mnt 3-1', 55681759),
52(NULL, 'TOOTJA NR6', 'Kiruoja 3-1', 55689135),
53(NULL, 'TOOTJA NR7', 'Kiriku tee 3-94', 55682389),
54(NULL, 'TOOTJA NR8', 'Kirnalo mnt 231-1', 55683871),
55(NULL, 'TOOTJA NR9', 'Odaniku 59-23', 55685819),
56(NULL, 'TOOTJA NR10', 'Sikuti pst 1-7', 55683902);
57
58SELECT Tooted.Tootekood,DATE_FORMAT(Tooted.Kuupäev,"%d-%m-%Y"),SUM(Tooted.Kaal+Tooted.Kogus) FROM 'Tooted' GROUP BY Tooted.Tootekood;
59SELECT Tootjad.ID,Tootjad.Tootjanimi,Tootjad.Aadress,Tootjad.Telefon,Tooted.Tootenimetus,Tooted.Tootekood,AVG(Tooted.Kaal)
60FROM 'Tootjad'
61JOIN 'Tooted'
62ON Tooted.ID=Tootjad.ID
63GROUP BY Tooted.Tootenimetus ASC;