1HaLhqJ6

1DROP DATABASE IF EXISTS erlent_av31;
2CREATE DATABASE IF NOT EXISTS erlent_av31;
3USE erlent_av31;
4
5
6CREATE TABLE `tooted` (
7  `tootedID`    int(20) unsigned  NOT NULL AUTO_INCREMENT,
8  `tootekood`  varchar(3)           NOT NULL DEFAULT 'XXX',
9  `nimi`         varchar(30)       NOT NULL DEFAULT 'Tundmatu',
10  `kogus`     int(10) NOT NULL DEFAULT '0',
11  `kaal`        int(20)      NOT NULL DEFAULT '0',
12  `hind`        decimal(7,2)      NOT NULL DEFAULT '0',
13  `kuupaev`        DATE      NOT NULL,
14  PRIMARY KEY (`tootedID`)
15);
16INSERT INTO tooted (tootedID,tootekood,nimi,kogus,kaal,hind,kuupaev) VALUE
17
18(1,'BBB','Liha',3,55,1.77, '2011-01-05'),
19(2,'BBB','Peekon',5,52, 0.88, '2012-09-15'),
20(3,'BBB','Peet',9,11, 0.88, '2025-05-21'),
21
22(4,'CCC','Coca Cola',2,2, 0.88, '2009-07-19'),
23(5,'CCC','Monster Energy',2,2, 0.88, '2010-11-05'),
24(6,'CCC','Red Bull',1,2, 0.88, '2011-03-09'),
25
26(7,'DDD','Nokamüts',5,1, 0.88, '2013-03-11'),
27
28(8,'AAA','Niked',5,21, 0.88, '2014-02-04'),
29(9,'AAA','Sandaalid',1,11, 1.11, '1996-02-15'),
30
31(10,'AAA','Kummikud',1,75, 5000.99, '1998-03-04');
32
33
34CREATE TABLE tootjad (
35  `tootjaID`    int(20) unsigned  NOT NULL AUTO_INCREMENT,
36  `tootjanimi`  varchar(50)           NOT NULL DEFAULT 'XXX',
37  `aadress`         varchar(30)       NOT NULL DEFAULT 'Tundmatu',
38  `telefon`     int(10) NOT NULL DEFAULT '0',
39    PRIMARY KEY (`tootjaID`)
40);
41
42INSERT INTO tootjad (tootjaID, tootjanimi, aadress, telefon) VALUE
43          (1001, 'Winston KK','Suur-Aia 27', '88881111'), 
44          (1002, 'MAN United','Tokyo 23', '88882533'), 
45		  (1003, 'ABC Company','Shinghai 24', '88882233'), 
46		  (1004, 'XYZ Master','Tallinn 11', '88882222'), 
47		  (1005, 'Root Place','Kartulimaa 99', '89982112'), 
48		  (1006, 'Yu Gi Oh','SQLmaa 77', '76882222'), 
49		  (1007, 'Avengers','Kitsetänav 23', '88882222'), 
50		  (1008, 'Erlent Firma','Moskva 7', '88123522'), 
51		  (1009, 'FFX ACX','Yirma  12', '88555622'), 
52          (1010, 'YoLo Shoes','Imedemaa', '86969633');
53        
54	   
55	   CREATE TABLE `uhendus` (
56    ID_tooted     UNSIGNED        INT NOT NULL,
57    ID_tootjad    UNSIGNED        INT NOT NULL,
58    CONSTRAINT fk_tooted          FOREIGN KEY (ID_tooted)   REFERENCES tooted(ID),
59    CONSTRAINT fk_tootjad         FOREIGN KEY (ID_tootjad)  REFERENCES tootjad(ID)
60);