y86bWA22

1DROP DATABASE IF EXISTS Art;
2CREATE DATABASE Art;
3USE Art;
4
5CREATE TABLE painter(
6	id INT AUTO_INCREMENT PRIMARY KEY ,
7	name VARCHAR(255) NOT NULL,
8    nationality VARCHAR(255) NOT NULL,
9    birthDay INT NOT NULL
10);
11
12CREATE TABLE picture(
13	id INT AUTO_INCREMENT PRIMARY KEY ,
14	name VARCHAR(255) NOT NULL,
15    createDate INT,
16    painterId INT,
17    CONSTRAINT FOREIGN KEY(painterID) REFERENCES painter(id) 
18);
19
20INSERT INTO painter
21VALUES 	(NULL, 'Караваджо', 'Италия', 1571) ,
22		(NULL, 'Салвадор Дали', 'Испания', 1904) ,
23		(NULL, 'Ван Гог', 'Холандия', 1853),
24        (NULL, 'Да Винчи', 'Италия', 1452);
25        
26        
27INSERT INTO picture
28VALUES 	(NULL, 'Обезглавяването на Св. Иван Кръстител', 1608, 1),
29		(NULL, 'Бакхус', 1595, 1),
30        (NULL, 'Слоновете', 1948, 2),
31        (NULL, 'Ана Мария', 1924, 2),
32        (NULL, 'Слънчогледите', 1888, 3),
33        (NULL, 'Звездна нощ', 1889, 3),
34        (NULL, 'Мадоната с каранфила', 1480, NULL);
35
36SELECT * FROM painter;
37SELECT * FROM picture;
38
39SELECT * FROM picture INNER JOIN painter WHERE painterId = painter.id;
40SELECT * FROM picture LEFT JOIN painter ON painterId = painter.id;
41SELECT * FROM picture RIGHT JOIN painter ON painterId = painter.id;
42
43
44SELECT * FROM picture
45LEFT JOIN painter ON painterId = painter.id
46UNION all
47SELECT * FROM picture
48RIGHT JOIN painter ON painterId = painter.id;
49
50SELECT * FROM painter ORDER BY id DESC limit 2;
51SELECT painter.name, MIN(painter.birthDay) FROM painter 
52UNION
53SELECT painter.name, MAX(painter.birthDay) FROM painter;