i3XFTX7u

1DROP DATABASE IF EXISTS shitLife;
2CREATE DATABASE shitLife;
3
4USE shitLife;
5
6CREATE TABLE Camera(
7	Id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
8	Brand ENUM ('Canon', 'Nikon') NOT NULL,
9    Model VARCHAR(150) NOT NULL
10);
11
12CREATE TABLE Lens(
13	Id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
14	Brand VARCHAR(150) NOT NULL,
15    Model VARCHAR(150) NOT NULL,
16    forBrand VARCHAR(150) NOT NULL
17);
18
19CREATE TABLE Person(
20	Id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
21	Name VARCHAR(150) NOT NULL
22);
23
24CREATE TABLE Kit(
25	Id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
26	Name VARCHAR(150) NOT NULL,
27    Id_person INTEGER NOT NULL,
28    
29    FOREIGN KEY	(Id_person) REFERENCES Person(Id)
30);
31
32CREATE TABLE KitCamera(
33	Id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
34	Id_kit INTEGER NOT NULL,
35    Id_camera INTEGER NOT NULL,
36    
37    FOREIGN KEY	(Id_kit) REFERENCES Kit(Id),
38	FOREIGN KEY	(Id_camera) REFERENCES Camera(Id)
39);
40
41CREATE TABLE KitLens(
42	Id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
43	Id_kit INTEGER NOT NULL,
44    Id_lens INTEGER NOT NULL,
45    
46    FOREIGN KEY	(Id_kit) REFERENCES Kit(Id),
47	FOREIGN KEY	(Id_lens) REFERENCES Lens(Id)
48);
49
50INSERT INTO Camera(Brand, Model) VALUES ('Nikon', 'ETUD'),
51	('Nikon', 'YTRH'),
52    ('Canon', 'TJFI'),
53    ('Nikon', 'ETSD'),
54    ('Canon', 'EHPP');
55    
56INSERT INTO	Lens(Brand, Model, forBrand) VALUES ('Plaj', 'TUCG', 'Nikon'),
57	('Planina', 'DRTH', 'Canon'),
58    ('Ten', 'EWHB', 'Canon'),
59    ('Andreshko', 'ETVA', 'Nikon'),
60    ('Svetilnik', 'PDGM', 'Nikon');
61
62INSERT INTO	Person(Name) VALUES ('Mlukni'),
63	('Tishina'),
64    ('Mulchi'),
65    ('Spoko'),
66    ('Harmoniq');
67    
68INSERT INTO	Kit(Name, Id_person) VALUES ('Skup', 2),
69	('Evtin', 1),
70    ('Evtin', 3),
71    ('Skup', 5),
72    ('Skup', 4);
73    
74INSERT INTO	KitCamera(Id_kit, Id_camera) VALUES (3, 2),
75	(5, 1),
76    (4, 3),
77    (1, 5),
78    (2, 4);
79
80INSERT INTO	KitLens(Id_lens, Id_kit) VALUES (4, 1),
81	(3, 2),
82    (5, 5),
83    (2, 3),
84    (1, 4);
85
86SELECT p.Name, l.Model  FROM Person as p
87LEFT JOIN Kit as k ON p.Id = k.Id_person
88LEFT JOIN KitLens as kl ON k.Id = kl.Id_kit
89LEFT JOIN Lens as l ON kl.Id_lens = l.Id;
90
91SELECT p.Name, c.Brand FROM Person as p
92LEFT JOIN Kit as k ON p.Id= k.Id_person
93LEFT JOIN KitCamera as kc ON k.Id= kc.Id_kit
94LEFT JOIN Camera as c ON kc.Id_camera= c.Id;