Re5MMrQC

1DROP DATABASE IF EXISTS TR;
2CREATE DATABASE TR;
3USE TR;
4
5CREATE TABLE person (
6    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
7    name VARCHAR(256) NOT NULL
8); 
9
10CREATE TABLE camera (
11    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
12    model VARCHAR(256) NOT NULL,
13    brand VARCHAR(256) NOT NULL
14);
15
16CREATE TABLE lens (
17    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
18    model VARCHAR(256) NOT NULL,
19    brand VARCHAR(256) NOT NULL,
20	forBrand VARCHAR(256) NOT NULL
21);
22
23CREATE TABLE kit (
24    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
25    name VARCHAR(256) NOT NULL,
26    idPerson INT NOT NULL,
27    FOREIGN KEY (idPerson) REFERENCES person(Id)
28);
29
30CREATE TABLE kitCamera (
31    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
32    idKit INT NOT NULL,
33    idCamera INT NOT NULL,
34    FOREIGN KEY (idKit) REFERENCES kit(Id),
35    FOREIGN KEY (idCamera) REFERENCES camera(Id)
36);
37
38CREATE TABLE kitLens (
39    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
40	idKit INT NOT NULL,
41    idLens INT NOT NULL,
42    FOREIGN KEY (idKit) REFERENCES kit(Id),
43    FOREIGN KEY (idLens) REFERENCES lens(Id)
44);
45
46INSERT INTO person (name)
47VALUES ('Tosho1'),('Misho2'),('Reti3');
48SELECT * FROM person;
49INSERT INTO camera (model,brand)
50VALUES ('camera1','cannon'),('camera2','nikon'),('camera3','laika');
51INSERT INTO lens(model,brand,forBrand )
52VALUES ('lens1','cannon','cannon'),('lens2','cannon','nikon'),('lens3','cannon','laika');
53INSERT INTO kit(name,idPerson)
54VALUES ('fancy',1),('rich',1),('family',3);
55INSERT INTO kitCamera(idKit,idCamera)
56VALUES (1,3),(1,2),(2,3),(3,2);
57INSERT INTO kitLens(idKit,idLens)
58VALUES (1,3),(2,3),(3,2),(1,2);
59
60SELECT p.name, l.model, l.brand FROM person AS p
61LEFT JOIN kit AS k ON p.Id = k.idPerson 
62LEFT JOIN kitLens AS kl ON kl.idKit = k.Id
63LEFT JOIN lens AS l ON kl.idLens = l.Id ;
64
65SELECT p.name, c.model, c.brand FROM person AS p
66LEFT JOIN kit AS k ON p.Id = k.idPerson 
67LEFT JOIN kitCamera AS kc ON kc.idKit = k.Id
68LEFT JOIN camera AS c ON kc.idCamera = c.Id ;