Last active
August 29, 2015 14:17
-
-
Save baquiax/606e351e9f92df151637 to your computer and use it in GitHub Desktop.
Lab10 CC5
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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'); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment