create database ex10d3;
use ex10d3
create table color(
    colorcode char(6) not null primary key
);
create table brick(
    type int not null primary key,
    colorcode char(6) not null,
    foreign key (colorcode) references color(colorcode)
);
create table coloredpaper(
    type int not null primary key,
    colorcode char(6) not null,
    foreign key (colorcode) references color(colorcode)
);
insert into color values('000000');
insert into color values('333333');
insert into color values('666666');
insert into color values('999999');
insert into color values('cccccc');
insert into color values('ffffff');
insert into coloredpaper values(1,'000000');
insert into coloredpaper values(2,'333333');
insert into coloredpaper values(5,'999999');
insert into coloredpaper values(9,'cccccc');
insert into brick values(1,'000000');
insert into brick values(2,'333333');
insert into brick values(3,'666666');

-- solutions #3

-- ideal, refer to diagram, not feasible in mysql
select colorcode from coloredpaper
intersect
select colorcode from brick;

-- or
select brick.colorcode 
from brick, coloredpaper 
where brick.colorcode = coloredpaper.colorcode;

-- or
select colorcode 
from coloredpaper 
where colorcode in (select colorcode from brick);

-- or
select coloredpaper.colorcode 
from coloredpaper 
inner join brick 
on brick.colorcode=coloredpaper.colorcode;

-- solutions #4

-- ideal, refer to diagram, not feasible in mysql
select colorcode from coloredpaper
except    
select colorcode from brick;

-- or
select colorcode 
from coloredpaper
where colorcode not in (select colorcode from brick);

-- or
select colorcode from coloredpaper 
left join brick using (colorcode) 
where brick.colorcode is null;