wzLHyeLN

1DROP DATABASE IF EXISTS Studio;
2CREATE DATABASE Studio;
3
4USE Studio;
5
6-- ----------CREATE TABLES ------------
7
8CREATE TABLE photographers(
9	id SERIAL,
10    name VARCHAR(300) NOT NULL,
11    PRIMARY KEY(id)
12);
13
14CREATE TABLE cameras(
15	id SERIAL,
16	brand VARCHAR(300) NOT NULL,
17    model VARCHAR(300) NOT NULL,
18    price DECIMAL(11, 2),
19    PRIMARY KEY(id)
20);
21
22CREATE TABLE lens(
23	id SERIAL,
24    brand VARCHAR(300) NOT NULL,
25    model VARCHAR(300) NOT NULL,
26    forCameraBrand VARCHAR(300) DEFAULT null,
27    PRIMARY KEY(id)
28);
29
30CREATE TABLE kits(
31	id SERIAl,
32    name VARCHAR(300) NOT NULL,
33    p_id BIGINT UNSIGNED,
34    PRIMARY KEY(id)
35);
36
37CREATE TABLE kitCameras(
38	id SERIAL,
39    kit_id BIGINT UNSIGNED,
40    camera_id BIGINT UNSIGNED,
41    PRIMARY KEY(id),
42    FOREIGN KEY(kit_id) REFERENCES kits(id),
43    FOREIGN KEY(camera_id) REFERENCES cameras(id)
44);
45
46CREATE TABLE kitLens(
47	id SERIAL,
48    kit_id BIGINT UNSIGNED,
49    lens_id BIGINT UNSIGNED,
50    PRIMARY KEY(id),
51    FOREIGN KEY(kit_id) REFERENCES kits(id),
52    FOREIGN KEY(lens_id) REFERENCES lens(id)
53);
54
55-- ---------- END  -------------------------
56
57INSERT INTO cameras(brand, model, price)
58	VALUES
59		('Canon', 'Cpro', 52),
60        ('Nikon', 'Npro', 25),
61        ('Custom', 'CMpro', 5225);
62
63INSERT INTO lens(brand, model, forCameraBrand)
64	VALUES
65		('Canon', 'CLpro', 'Canon'),
66        ('Nikon', 'NLpro', 'Nikon'),
67        ('Custom', 'CMLpro', '');
68        
69INSERT INTO photographers(name)
70	VALUES
71		('Jesko'),
72        ('Calgon'),
73        ('Arnold');
74
75INSERT INTO kits(name, p_id)
76	VALUES
77		('Diving', 1),
78        ('HighSpeedRacing', 2),
79        ('Lifting', 3),
80        ('Paragliding', 1),
81        ('Showroom', 2),
82        ('BodyBuilding', 3);
83
84INSERT INTO kitCameras(kit_id, camera_id)
85	VALUES
86		(1, 1),
87        (2, 2),
88		(3, 3),
89        (4, 1),
90        (5, 2),
91        (6, 3);
92        
93INSERT INTO kitLens(kit_id, lens_id)
94	VALUES
95		(1, 1),
96        (2, 2),
97        (3, 3),
98        (4, 1),
99        (5, 2),
100        (6, 3);
101        
102-- ------- END ------------------------
103
104SELECT p.name, l.brand AS lensBrand FROM photographers p
105LEFT JOIN kits k
106ON p.id = k.p_id
107LEFT JOIN kitLens kl
108ON k.id = kl.kit_id
109LEFT JOIN lens l
110ON kl.lens_id = l.id;
111
112
113SELECT p.name, c.brand AS CameraBrand FROM photographers p
114LEFT JOIN kits k
115ON p.id = k.p_id
116LEFT JOIN kitCameras kc
117ON k.id = kc.kit_id
118LEFT JOIN cameras c
119ON kc.camera_id = c.id;