Create artDB.sql Script

artDB.sql

CREATE DATABASE artDB;
USE artDB;

CREATE TABLE Paintings(
ID int PRIMARY KEY NOT NULL,
Painting VARCHAR(50) NOT NULL,
ArtistID VARCHAR(5),
ArtType VARCHAR(10),
PDate int
);

--Equivalent to Describe Table
exec sp_columns Paintings;


CREATE TABLE Artists(
ArtistID VARCHAR(5) PRIMARY KEY NOT NULL, 
AName VARCHAR(50)
);

CREATE TABLE ArtTypes(
ArtType VARCHAR(10) PRIMARY KEY NOT NULL,
ArtDesc VARCHAR(50)
);

--Foreign Key
ALTER TABLE Paintings
ADD FOREIGN KEY (ArtistID)
REFERENCES Artists(ArtistID);

ALTER TABLE Paintings
ADD FOREIGN KEY (ArtType)
REFERENCES ArtTypes(ArtType);

--Insertions
INSERT INTO Artists (ArtistID, AName)
VALUES ('A01', 'Vincent van Gogh'),
		('A02', 'Salvador Dali'),
		('A03', 'Clyfford Still');

INSERT INTO ArtTypes (ArtType, ArtDesc)
VALUES ('T01', 'Post-Impressionist'),
	   ('T02', 'Surrealist'),
	   ('T03', 'Abstract');

INSERT INTO Paintings (ID, Painting, ArtistID, ArtType, PDate)
VALUES (1, 'Starry Nights Over the Rhone', 'A01', 'T01', 1888),
		(2, 'Sunflowers', 'A01', 'T01', 1888),
		(3, 'The Persistence of Memory', 'A02', 'T02', 1931),
		(4, 'The Elephant', 'A02', 'T02', 1948),
		(5, '1957-D ', 'A03', 'T03', 1957),
		(6, '1948-E', 'A03', 'T03', 1948);

SELECT * FROM Paintings;
SELECT * FROM Artists;
SELECT * FROM ArtTypes;