Instructions: This assignment requires you to write SQL code. The SQL code must
ID: 3699451 • Letter: I
Question
Instructions: This assignment requires you to write SQL code. The SQL code must be written in a file(s).
Exercise-1 (25 points)
Write the SQL code to create a table to store following information about Painters
First Name
Last Name
Address
Be sure to create a field for the primary key that has auto-increment feature. The first name and the last name can each be a maximum size of 45 characters. The address can be a maximum size of 100 characters
Exercise-2 (15 points)
Write the SQL statements to insert data into the table created in Exercise-1 above, about the following Painters:
John Doe 123 Market St Manassas VA 20021
Jane Smith 342 Water St. Centreville VA 20121
Mike Williams 390 Bank Ave Fairfax VA 20201
Exercise-3 (30 points)
Write the SQL code to create a table to store following information about Paintings
Title of the painting
Date of the painting
Description of the painting
A painter who painted the painting
Be sure to create a field for the primary key that has auto-increment feature. Be sure to create a foreign key column that connects this table to the table created in Exercise-1 previously. Assume suitable data types and sizes for all fields.
Exercise-4 (30 points)
Write the SQL code to insert data into the table created in Exercise-3, about the following paintings:
Title Date Description Painter
Morning Mist 10/10/2015 Beautiful painting about mist John Doe
Evening Shadows 10/12/2015 Beautiful painting about shadows John Doe
Afternoon Thoughts 10/11/2015 Beautiful painting about memories Jane Smith
?
Explanation / Answer
Part A:
Create a table for painter details
CREATE TABLE painters
(painter_id number(10) PRIMARY KEY,
First_Name varchar(45),
Last_Name varchar(45),
Address varchar(45));
Create a sequence with starting value 1 and gets incremented by 1:
CREATE sequence painter_seq start with 1
increment by 1
minvalue 1
maxvalue 10000;
Part B:
INSERT into painters(painter_id, First_Name, Last_Name, Address)
values(painter_seq.nextval, "John", ''Doe", "123 Market St Manassas VA 20021");
INSERT into painters(painter_id, First_Name, Last_Name, Address)
values(painter_seq.nextval, "Jane", ''Smith", "342 Water St. Centreville VA 20121");
INSERT into painters(painter_id, First_Name, Last_Name, Address)
values(painter_seq.nextval, "Mike", ''Williams", "390 Bank Ave Fairfax VA 20201");
Part C:
Create the table for paintings:
CREATE TABLE paintings
(painting_id number(10) PRIMARY KEY,
Title varchar(45),
Date date,
Description varchar(100)
painter_id number(10)
FOREIGN KEY (painter_id) REFERENCES painters(painter_id));
Create a sequence for painting_id:
CREATE sequence painting_seq start with 1
increment by 1
minvalue 1
maxvalue 10000;
Part D:
Foreign key of a table should be the primary key of the referened table. Hence, instead of taking painter's name, we insert painter_id corresponding to the painter, i.e., ("John Doe", 1), ("Jane Smith", 2) and ("Mike Williams", 3).
INSERT into paintings(painting_id, Title, Date, Description, painter_id)
values(painting_seq.nextval, "Morning Mist", "2015-10-10", "Beautiful painting about mist", 1);
INSERT into paintings(painting_id, Title, Date, Description, painter_id)
values(painting_seq.nextval, "Evening Shadows", "2015-12-10", "Beautiful painting about shadows", 1);
INSERT into paintings(painting_id, Title, Date, Description, painter_id)
values(painting_seq.nextval, "Afternoon Thoughts", "2015-11-10", "Beautiful painting about memories", 2);