iereeSj9

1DROP DATABASE IF EXISTS kaspar_av31;
2CREATE DATABASE kaspar_av31;
3USE kaspar_av31;
4
5DROP TABLE IF EXISTS tooted;
6DROP TABLE IF EXISTS tootjad;
7CREATE TABLE tooted (
8	ID		int(10) unsigned	NOT NULL AUTO_INCREMENT,
9	kood		char(3)			NOT NULL DEFAULT 'AAA',
10	nimetus		varchar(32)		NOT NULL DEFAULT '',
11	kogus		int(10)			NOT NULL DEFAULT '0',
12	kaal		varchar(10)		NOT NULL DEFAULT '0kg',
13	hind		decimal(7,2)		NOT NULL DEFAULT '0.00',
14	kuupäev		DATE			NOT NULL DEFAULT '2000-01-01',
15	PRIMARY KEY (ID)
16	);
17
18CREATE TABLE tootjad (
19	ID		int(10) unsigned	NOT NULL AUTO_INCREMENT,
20	nimi		varchar(32)		NOT NULL DEFAULT '',
21	aadress		varchar(50)		NOT NULL DEFAULT '',
22	telefon		int(12)			NOT NULL DEFAULT '123456789',
23	PRIMARY KEY (ID)
24	);
25
26CREATE TABLE `välisvõtmed` (
27	id_tooted 	INT NOT NULL,
28	id_tootjad	INT NOT NULL,
29	CONSTRAINT fk_tooted FOREIGN KEY (id_tooted) REFERENCES tooted(ID),
30	CONSTRAINT fk_tootjad FOREIGN KEY (id_tootjad) REFERENCES tootjad(ID)
31	);
32
33INSERT INTO tooted VALUES
34	(101,'FUR','TOOL','110','2.5kg','11.11','2000-01-01'),
35	(102,'FUR','TUMBA','56','3.2kg','7.59','2000-01-01'),
36	(103,'FUR','LAUD','247','4kg','22.65','2000-01-01'),
37	(104,'KON','PASTAKAS','7532','20g','0.25','2000-01-01'),
38	(105,'KON','KLAMMERDAJA','402','150g','4.15','2000-01-01'),
39	(106,'KON','HIIREMATT','56','160g','1.76','2000-01-01'),
40	(107,'MÄN','PALL','279','500g','5.55','2000-01-01'),
41	(108,'MÄN','TEINE PALL','68','700g','15.69','2000-01-01'),
42	(109,'MÄN','LEGOD','184','1kg','15.69','2000-01-01'),
43	(110,'MÄN','KAISUKARU','17','500g','22.59','2000-01-01');
44	
45INSERT INTO tootjad VALUES
46	(101,'Tõnis Tõuks','Tallinn, Suur mnt 7','5123456'),
47	(102,'Laura Soe','Tallinn, Liivalaia 4','5637632'),
48	(103,'Joonatan Siu','Paide, Lai 13','5236203'),
49	(104,'Kert Hobune','Tartu, Pikk 5','51994352'),
50	(105,'Heli Kopter','Tallinn, Vabaduse pst 14','5284825'),
51	(106,'Liina Leeni','Tartu, Ehituse 2,'5978324'),
52	(107,'Tarvo Võsa','Paide, Pärnu 2','5589781'),
53	(108,'Priit Toobal','Narva, Kooli 7','5294386'),
54	(109,'Teet Margna','Tallinn, Pikk 31','595391'),
55	(110,'Mann Lusti','Haapsalu, Lai 13','5947632');
56
57SELECT tooted.kood,DATE_FORMAT(tooted.kuupäev,"%d/%m/%y"),sum(tooted.kaal+tooted.kogus) FROM tooted GROUP BY tooted.kood;
58SELECT tootjad.id,tootjad.nimi,tootjad.aadress,tootjad.telefon,tooted.nimetus,tooted.kood,AVG(tooted.kaal);
59FROM tootjad
60JOIN tooted
61ON tooted.id=tootjad.id
62GROUP BY tooted.nimetus ASC;