SQL Assignment: Database Design, SQL Queries and Join Statements

This SQL assignment solution demonstrates the creation of three tables: Artist, Art, and Location. The solution includes the SQL code for creating the tables with specified data types and constraints, such as primary keys and foreign key relationships. The assignment also provides the SQL INSERT statements used to populate these tables with sample data. Furthermore, the document contains SQL SELECT statements to retrieve data from the tables and SQL queries to filter data based on specific criteria, such as selecting data from a specific section or selecting art IDs starting with a specific letter. Finally, the assignment showcases the use of SQL JOIN statements to combine data from multiple tables, retrieving related information from the Location and Art tables, and the Artist and Art tables.

SQL ASSIGNMENT
STUDENT
16 JULY 2016

Contents
CREATE TABLES......................................................................................................................................2
INSERT TABLES.......................................................................................................................................3
SELECT STATEMENTS.............................................................................................................................5
SQL QUERIES..........................................................................................................................................5
SQL JOIN STATEMENTS..........................................................................................................................6
1 | P a g e

CREATE TABLES
TABLE ARTIST
Create table Artist
(
Artist_Id varchar(5) primary key,
Name varchar(10),
Address varchar(50),
Telephone number(10,0),
Amount_of_Art number(5,0)
);
TABLE ART
Create table Art
(
Art_Id varchar(20) primary key,
Val Number(10,2),
Sizes varchar(10),
Genre Varchar(20),
Date_created Date,
Artist_Id varchar(5),
foreign key(Artist_Id) references Artist(Artist_Id)
);
TABLE LOCATION
Create table Location
(
Location_Id varchar(5) primary key,
Start_Date date,
End_date date,
Section char check (Section IN ('A', 'B', 'C', 'D')),
2 | P a g e

Locations varchar(7) check (Locations IN ('Side', 'side', 'Front', 'front')),
Art_Id varchar(4),
foreign key(Art_Id) references Art(Art_Id)
);
Alter table Art ADD constraint chkgen check (Genre IN ('Paintings', 'Prints', 'Photos'));
Alter table Art Add constraint chk_size check (sizes IN ('Small', 'Medium' , 'Large'));
INSERT TABLES
TABLE ARTIST
insert all
into Artist (Artist_Id , Name, Address, Telephone, Amount_of_Art) values ('A1', 'John Mil', '21 park
street', 9999876543, 2)
into Artist (Artist_Id , Name, Address, Telephone, Amount_of_Art) values ('A2', 'Alex S', '212 james
road' , 8887654321, 1)
into Artist (Artist_Id , Name, Address, Telephone, Amount_of_Art) values ('A3', 'Dia GS', '45 thames
road', 9854321234, 2)
into Artist (Artist_Id , Name, Address, Telephone, Amount_of_Art) values ('A4', 'Max Zam' , '87/90
thames street', 4456543234, 1)
into Artist (Artist_Id , Name, Address, Telephone, Amount_of_Art) values ('A5', 'Steve S', '90 avenue
road', 9999990909, 1)
into Artist (Artist_Id , Name, Address, Telephone, Amount_of_Art) values ('A6', 'Tiller', '23 park
street', 8761112321, 1)
into Artist (Artist_Id , Name, Address, Telephone, Amount_of_Art) values ('A7', 'David S', '34 green
park', 3333787654, 1)
into Artist (Artist_Id , Name, Address, Telephone, Amount_of_Art) values ('A8', 'Stevenson', '67
thames view park', 7777654765, 1 )
into Artist (Artist_Id , Name, Address, Telephone, Amount_of_Art) values ('A9', 'Jones', '78 yuvian
street', 8888110098, 1)
into Artist (Artist_Id , Name, Address, Telephone, Amount_of_Art) values ('A10', 'Julia', '345 Drone
street', 8833201987, 1)
select * from dual;
3 | P a g e

TABLE ART
insert all
into Art (Art_Id, Val , Sizes, Genre, Date_created , Artist_Id) values ('John1', 1230.00, 'Small',
'Paintings', '01/01/2016', 'A1')
into Art (Art_Id, Val , Sizes, Genre, Date_created , Artist_Id) values ('John2', 456.90, 'Medium',
'Paintings', '01/03/2016', 'A1')
into Art (Art_Id, Val , Sizes, Genre, Date_created , Artist_Id) values ('Alex1', 67.90, 'Large', 'Prints',
'01/19/2016', 'A2')
into Art (Art_Id, Val , Sizes, Genre, Date_created , Artist_Id) values ('Dia1', 340.00, 'Small', 'Paintings',
'02/01/2016', 'A3')
into Art (Art_Id, Val , Sizes, Genre, Date_created , Artist_Id) values ('Dia2', 151.90, 'Medium',
'Photos', '02/11/2016', 'A3')
into Art (Art_Id, Val , Sizes, Genre, Date_created , Artist_Id) values ('Max1', 600.00, 'Large',
'Paintings', '05/12/2016', 'A4')
into Art (Art_Id, Val , Sizes, Genre, Date_created , Artist_Id) values ('Steve1', 100.91, 'Large', 'Photos',
'06/01/2016', 'A5')
into Art (Art_Id, Val , Sizes, Genre, Date_created , Artist_Id) values ('Tiller1', 85.88, 'Small', 'Prints',
'06/11/2016', 'A6')
into Art (Art_Id, Val , Sizes, Genre, Date_created , Artist_Id) values ('David1', 700.90, 'Large',
'Paintings', '06/12/2016', 'A7')
into Art (Art_Id, Val , Sizes, Genre, Date_created , Artist_Id) values ('Stevenson1', 190.83, 'Medium',
'Photos', '07/01/2016', 'A8')
into Art (Art_Id, Val , Sizes, Genre, Date_created , Artist_Id) values ('Jones1', 89.00, 'Small', 'Prints',
'07/01/2016', 'A9')
into Art (Art_Id, Val , Sizes, Genre, Date_created , Artist_Id) values ('Julia1', 78.00, 'Small', 'Prints',
'07/10/2016', 'A10')
select * from dual;
TABLE LOCATION
insert all
into Location (Location_Id, Start_Date, End_date, Section, Locations, Art_Id) values ('L1',
'02/02/2016', '02/10/2016', 'A', 'Side', 'John1')
into Location (Location_Id, Start_Date, End_date, Section, Locations, Art_Id) values ('L2',
'02/05/2016', '02/10/2016', 'B', 'Side', 'John2')
into Location (Location_Id, Start_Date, End_date, Section, Locations, Art_Id) values ('L3',
'02/09/2016', '02/21/2016', 'C', 'Front', 'Alex1')
4 | P a g e

into Location (Location_Id, Start_Date, End_date, Section, Locations, Art_Id) values ('L4',
'03/02/2016', '03/10/2016', 'D', 'Side', 'Dia1')
into Location (Location_Id, Start_Date, End_date, Section, Locations, Art_Id) values ('L5',
'04/02/2016', '04/10/2016', 'A', 'Front', 'Dia2')
into Location (Location_Id, Start_Date, End_date, Section, Locations, Art_Id) values ('L6',
'06/02/2016', '06/10/2016', 'B', 'Front', 'Max1')
into Location (Location_Id, Start_Date, End_date, Section, Locations, Art_Id) values ('L7',
'07/02/2016', '07/10/2016', 'B', 'Side', 'Steve1')
into Location (Location_Id, Start_Date, End_date, Section, Locations, Art_Id) values ('L8',
'07/02/2016', '07/10/2016', 'C', 'Front', 'Tiller1')
into Location (Location_Id, Start_Date, End_date, Section, Locations, Art_Id) values ('L9',
'07/11/2016', '07/22/2016', 'D', 'Side', 'David1')
into Location (Location_Id, Start_Date, End_date, Section, Locations, Art_Id) values ('L10',
'07/20/2016', '07/30/2016','D', 'Front', 'Jones1')
into Location (Location_Id, Start_Date, End_date, Section, Locations, Art_Id) values ('L11',
'07/21/2016', '07/30/2016', 'B', 'Side', 'Julia1')
select * from dual;
SELECT STATEMENTS
select * from artist;
select * from Art;
select * from location;
SQL QUERIES
1. SELECT LOCATION ID, SECTION AND ART ID FROM LOCATION IN SECTION A
select Location_Id, Section, Locations, Art_Id from location where Section = 'A';
2. SELECT ART ID, VALUE, SIZES, GENRE FROM TABLE ART WHERE ART ID BEGINS WITH LETTER
‘J’
select Art_Id, Val , Sizes, Genre from Art where Art_Id like 'J%';
5 | P a g e

SQL JOIN STATEMENTS
1. SELECT DETAILS FROM LOCATION AND ART TABLE USING INNER JOIN
select L.Location_Id, L.Start_Date, L.End_date, L.Section, L.Locations, A.Genre, A.Date_created ,
A.Artist_Id FROM Art A INNER JOIN Location L ON A.Art_Id = L.Art_Id;
2. SELECT ARTIST NAME, ART ID AND THE GENRE OF THE ART FROM THE TABLES
select A1.Name , A2.Art_Id , A2.Genre from Artist A1 INNER JOIN Art A2 ON A1.Artist_Id =
A2.Artist_Id;
6 | P a g e