create table S (
	SNUM char(5) not null,
	SNAME char(20),
	STATUS int,
	CITY char(15)
)

create table P (
	PNUM char(6) not null,
	PNAME char(20),
	COLOR char(6),
	WEIGHT int,
	CITY char(15)
)

create table J (
	JNUM char(4) not null,
	JNAME char(10),
	CITY char(15)
)

create table SP (
	SNUM char(5) not null,
	PNUM char(6) not null,
	QTY int
)

create table SPJ (
	SNUM char(5) not null,
	PNUM char(6) not null,
	JNUM char(4) not null,
	QTY int
)

create unique index SX on S (SNUM)

create unique index PX on P (PNUM)

create unique index JX on J (JNUM)

create unique index SPX on SP (SNUM, PNUM)

create unique index SPJX on SPJ (SNUM, PNUM, JNUM)

relshow SuppliersAndParts S

insert into S values ('S1', 'Smith', 20, 'London')
insert into S values ('S2', 'Jones', 10, 'Paris')
insert into S values ('S3', 'Blake', 30, 'Paris')
insert into S values ('S4', 'Clark', 20, 'London')
insert into S values ('S5', 'Adams', 30, 'Athens')

insert into P values ('P1', 'Nut', 'Red', 12, 'London')
insert into P values ('P2', 'Bolt', 'Green', 17, 'Paris')
insert into P values ('P3', 'Screw', 'Blue', 17, 'Rome')
insert into P values ('P4', 'Screw', 'Red', 14, 'London')
insert into P values ('P5', 'Cam', 'Blue', 12, 'Paris')
insert into P values ('P6', 'Cog', 'Red', 19, 'London')

insert into J values ('J1', 'Sorter', 'Paris')
insert into J values ('J2', 'Punch', 'Rome')
insert into J values ('J3', 'Reader', 'Athens')
insert into J values ('J4', 'Console', 'Athens')
insert into J values ('J5', 'Collator', 'London')
insert into J values ('J6', 'Terminal', 'Oslo')
insert into J values ('J7', 'Tape', 'London')

insert into SP values ('S1', 'P1', 300)
insert into SP values ('S1', 'P2', 200)
insert into SP values ('S1', 'P3', 400)
insert into SP values ('S1', 'P4', 200)
insert into SP values ('S1', 'P5', 100)
insert into SP values ('S1', 'P6', 100)
insert into SP values ('S2', 'P1', 300)
insert into SP values ('S2', 'P2', 400)
insert into SP values ('S3', 'P2', 200)
insert into SP values ('S4', 'P2', 200)
insert into SP values ('S4', 'P4', 300)
insert into SP values ('S4', 'P5', 400)

insert into SPJ values ('S1', 'P1', 'J1', 200)
insert into SPJ values ('S1', 'P1', 'J4', 700)
insert into SPJ values ('S2', 'P3', 'J1', 400)
insert into SPJ values ('S2', 'P3', 'J2', 200)
insert into SPJ values ('S2', 'P3', 'J3', 200)
insert into SPJ values ('S2', 'P3', 'J4', 500)
insert into SPJ values ('S2', 'P3', 'J5', 600)
insert into SPJ values ('S2', 'P3', 'J6', 400)
insert into SPJ values ('S2', 'P3', 'J7', 800)
insert into SPJ values ('S2', 'P5', 'J2', 100)
insert into SPJ values ('S3', 'P3', 'J1', 200)
insert into SPJ values ('S3', 'P4', 'J2', 500)
insert into SPJ values ('S4', 'P6', 'J3', 300)
insert into SPJ values ('S4', 'P6', 'J7', 300)
insert into SPJ values ('S5', 'P2', 'J2', 200)
insert into SPJ values ('S5', 'P2', 'J4', 100)
insert into SPJ values ('S5', 'P5', 'J5', 500)
insert into SPJ values ('S5', 'P5', 'J7', 100)
insert into SPJ values ('S5', 'P6', 'J2', 200)
insert into SPJ values ('S5', 'P1', 'J4', 100)
insert into SPJ values ('S5', 'P3', 'J4', 200)
insert into SPJ values ('S5', 'P4', 'J4', 800)
insert into SPJ values ('S5', 'P5', 'J4', 400)
insert into SPJ values ('S5', 'P6', 'J4', 500)

select SNUM, STATUS from S where CITY = 'Paris'

select PNUM from SP

select distinct PNUM from SP

select * from S

select S.*, P.* from S, P where S.CITY = P.CITY

select S.SNUM, S.SNAME, S.STATUS, S.CITY,
	P.PNUM, P.PNAME, P.COLOR, P.WEIGHT, P.CITY
	from S, P
	where S.CITY = P.CITY