Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE DATABASE artist_db;
- GO
- USE artist_db;
- GO
- CREATE TABLE artists
- (
- id int NOT NULL IDENTITY(1,1),
- firstName varchar(40) NOT NULL,
- lastName varchar(40) NOT NULL,
- db int NOT NULL,
- PRIMARY KEY (id)
- );
- CREATE TABLE artMovements
- (
- id int NOT NULL IDENTITY(1,1),
- name varchar(40) NOT NULL,
- PRIMARY KEY (id),
- );
- CREATE TABLE paintings
- (
- id int NOT NULL IDENTITY(1,1),
- authorID int NOT NULL FOREIGN KEY REFERENCES artists(id),
- name varchar(40) NOT NULL,
- dt int NOT NULL,
- idMovArt int NOT NULL FOREIGN KEY REFERENCES artMovements(id),
- PRIMARY KEY (id)
- );
- CREATE TABLE exhibitions
- (
- exId int NOT NULL IDENTITY(1,1),
- name varchar(40) NOT NULL,
- MovId int NOT NULL FOREIGN KEY REFERENCES artMovements(id),
- city varchar(20) NOT NULL,
- PRIMARY KEY (exId),
- );
- INSERT INTO artMovements(name)
- VAlUES
- ('экспрессионизм'),
- ('импрессионизм'),
- ('абстракционизм'),
- ('супрематизм'),
- ('сюрреализм');
- INSERT INTO artists(firstName,lastName, db) VALUES
- ('Алексей','Явленский', 1864),
- ('Август','Маке', 1853),
- ('Огюст','Ренуар', 1841),
- ('Эдгар','Дега', 1834),
- ('Василий','Кандинский', 1866),
- ('Казимир','Малевич', 1879),
- ('Сальвадор','Дали', 1931);
- INSERT INTO paintings(authorID,name, dt, idMovArt) VALUES
- (1, 'Девочка с зелёным лицом', 1910, 1),
- (1,'Хелен', 1911,1),
- (2,'Индейцы', 1911,1),
- (2,'Прощание', 1914,1),
- (3,'Танец в Буживале', 1883,2),
- (3,'Весенний букет', 1866,2),
- (4,'Голубые танцовщицы', 1897,2),
- (5,'Синий круг', 1922,3),
- (5,'Смутное', 1917,3),
- (6,'Супрематизм', 1915,4),
- (6,'Спортсмены', 1932,3),
- (7,'Постоянство памяти', 1931,5),
- (7,'Предчувствие гражданской войны', 1936,5);
- SELECT artists.firstName, artists.lastName, paintings.name AS "Title", paintings.dt as "Date", artMovements.name AS "Art Movements"
- FROM
- paintings
- JOIN artMovements ON paintings.idMovArt = artMovements.id
- JOIN artists ON paintings.authorID = artists.id
- ORDER BY painting
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement