Lab10 CC5

Lab10

CREATE DATABASE IF NOT EXISTS lab10;
USE lab10;
create table if not EXISTS suppliers (
	sid int,
	sname varchar(100),
	address varchar(200),
	primary key(sid)
);

create table if not EXISTS parts (
	pid int,
	pname varchar(100),
	color varchar(40),
	primary key(pid)
);

create table if not EXISTS catalog(
	sid int,
	pid int,
	cost numeric(6,2),
	primary key(sid,pid),
	foreign key(sid) references suppliers(sid),
	foreign key(pid) references parts(pid)
);

insert into suppliers(sid,sname,address) values(1 ,'GHISA','4 calle');
insert into suppliers(sid,sname,address) values(2 ,'Antonio Perez','4 calle');
insert into suppliers(sid,sname,address) values(3 ,'Juan Lopez','5 avenida');
insert into suppliers(sid,sname,address) values(4 ,'Luis Andrade','7 calle');
insert into suppliers(sid,sname,address) values(5 ,'Maria Perez','9 avenida');
insert into suppliers(sid,sname,address) values(6 ,'Francisco Castro','7 avenida');
insert into suppliers(sid,sname,address) values(7 ,'Pedro Ruiz','5 avenida');
insert into suppliers(sid,sname,address) values(8 ,'Antonieta Perez','4 calle');
insert into suppliers(sid,sname,address) values(9 ,'Maria Hernandez','14 calle');
insert into suppliers(sid,sname,address) values(10 ,'Luis Valdez','14 calle');

insert into parts(pid,pname,color) values (100,'Tuerca','Gris');
insert into parts(pid,pname,color) values (200,'Puerta','Rojo');
insert into parts(pid,pname,color) values (300,'Puerta','Verde');
insert into parts(pid,pname,color) values (400,'Llanta','Negro');
insert into parts(pid,pname,color) values (500,'Timon','Rojo');
insert into parts(pid,pname,color) values (600,'Llanta','Blanca');

insert into catalog(sid,pid,cost) values(1,100,10.5);
insert into catalog(sid,pid,cost) values(1,200,3500);
insert into catalog(sid,pid,cost) values(1,300,3000);
insert into catalog(sid,pid,cost) values(1,400,400);
insert into catalog(sid,pid,cost) values(1,500,720);
insert into catalog(sid,pid,cost) values(1,600,720);
insert into catalog(sid,pid,cost) values(2,100,10);
insert into catalog(sid,pid,cost) values(2,400,310);
insert into catalog(sid,pid,cost) values(3,200,3800);
insert into catalog(sid,pid,cost) values(3,500,850);
insert into catalog(sid,pid,cost) values(5,100,8);
insert into catalog(sid,pid,cost) values(5,200,3610);
insert into catalog(sid,pid,cost) values(5,400,400);
insert into catalog(sid,pid,cost) values(5,500,850);
insert into catalog(sid,pid,cost) values(6,200,3800);
insert into catalog(sid,pid,cost) values(6,400,405);
insert into catalog(sid,pid,cost) values(8,400,410);
insert into catalog(sid,pid,cost) values(8,500,620);
insert into catalog(sid,pid,cost) values(9,100,10.5);
insert into catalog(sid,pid,cost) values(9,200,3500);
insert into catalog(sid,pid,cost) values(9,300,3000);
insert into catalog(sid,pid,cost) values(9,400,400);
insert into catalog(sid,pid,cost) values(9,500,820);


## SUPER QUERIES ##
#1
select distinct pname from parts p where exists(select sid from catalog c where c.pid = p.pid);

#2
select s.sid, s.sname from suppliers s where not exists (
	select p.pid from parts p where not exists (
		select c.pid from catalog c
		where c.pid = p.pid and c.sid = s.sid
	)
);

#3
select s.sid, s.sname from suppliers s where not exists (
	select p.pid from parts p where color = 'rojo' and not exists (
		select c.pid from catalog c
		where c.pid = p.pid and c.sid = s.sid
	)
);

#4
select  p.pname from parts p , catalog c, suppliers s where p.pid = c.pid and  c.sid = s.sid and s.sname = 'GHISA' 
and not exists(select c2.sid from catalog c2 where p.pid = c2.pid and c.sid != c2.sid);

#5
select c.sid,c.pid,c.cost from catalog c where c.cost > (select avg(cost) as cost from catalog c2 where c2.pid = c.pid group by pid); 

#6
select s.sname, c.pid, c.cost from catalog c , suppliers s where c.sid = s.sid and c.cost = (select max(c2.cost) from catalog c2 where c2.pid = c.pid);

#7
select distinct s.sid from suppliers s where not exists(select p.color from parts p, catalog c where p.pid = c.pid and c.sid = s.sid and p.color != 'Rojo');

#8
select distinct s.sid from suppliers s where exists(select p.color from parts p, catalog c, parts p2, catalog c2 where p.pid = c.pid and c.sid = s.sid and p2.pid = c2.pid and c2.sid = s.sid and (p.color = 'Verde' and p2.color = 'Rojo'));

#9
select distinct s.sid from suppliers s where exists(select p.color from parts p, catalog c where p.pid = c.pid and c.sid = s.sid and p.color = 'Rojo') union select distinct s.sid from suppliers s where exists(select p.color from parts p, catalog c where p.pid = c.pid and c.sid = s.sid and p.color = 'Verde');