Consultas avanzadas de base de datos

NO

ENUNCIADO

SENTENCIA

 SQL CON PRODUCTO

TIEMPO DE EJECUCIÓN

SENTENCIA SQL CON JOIN

TIEMPO DE EJECUCIÓN

SENTENCIA SQL CON SUBCONSULTA

TIEMPO DE EJECUCIÓN

DATOS PROPORCIONADOS POR LA CONSULTA

1

Nombre de los artículos suministrados por proveedores de categoría 10 y componentes de peso menor

select a.nom_art from articulos as a, envios as e, proveedores as p, componentes as c

 where a.no_art=e.no_art and e.no_prov=p.no_prov and e.no_com=c.no_com and p.categoria=10 and c.peso<14;

0.000 sec / 0.000 sec

select a.nom_art from articulos as a inner join envios as e inner join proveedores as p inner join componentes as c

 on (a.no_art=e.no_art and e.no_prov=p.no_prov and e.no_com=c.no_com) where p.categoria=10 and c.peso<14;

0.000 sec / 0.000 sec

select nom_art from articulos where no_art in(select no_art from envios where no_prov in(select no_prov from proveedores where categoria=10)and no_com in(select no_com from componentes where peso<14));

0.000 sec / 0.000 sec

[pic 2]

2

Número de proveedores que envían componentes de Morelos.

select distinct(e.no_prov) from envios as e,componentes as c where e.no_com=c.no_com and c.ciudad="morelos";

0.000 sec / 0.000 sec

select distinct(e.no_prov) from envios as e inner join componentes as c on(e.no_com=c.no_com) where c.ciudad="morelos";

0.000 sec / 0.000 sec

select distinct(no_prov) from envios where no_com in(select no_com from componentes where ciudad="morelos");

0.000 sec / 0.000 sec

[pic 3]

3

Número de proveedores que reparten artículos de Querétaro y componentes de color verde

select e.no_prov from envios as e, articulos as a, componentes as c where e.no_art=a.no_art and e.no_com=c.no_com and a.ciudad="queretaro" and c.color="verde";

0.000 sec / 0.000 sec

select e.no_prov from envios as e inner join articulos as a inner join componentes as c on( e.no_art=a.no_art and e.no_com=c.no_com) where a.ciudad="queretaro" and c.color="verde";

0.000 sec / 0.000 sec

select no_prov from envios where no_art in(select no_art from articulos where ciudad="queretaro") and no_com in (select no_com from componentes where color="verde");

0.000 sec / 0.000 sec

[pic 4]

4

Nombre y color de los componentes distribuidos por Eva Monroy Ortiz

select distinct(c.nom_com), c.color from componentes as c, envios as e, proveedores as p where e.no_com=c.no_com and e.no_prov=p.no_prov and p.nom_prov="eva monrroy ortiz";

0.000 sec / 0.000 sec

select distinct(c.nom_com), c.color from componentes as c inner join envios as e inner join proveedores as p on(e.no_com=c.no_com and e.no_prov=p.no_prov) where p.nom_prov="eva monrroy ortiz";

0.000 sec / 0.000 sec

select distinct(nom_com), color from componentes where no_com in(select no_com from envios where no_prov in( select no_prov from proveedores where nom_prov="eva monrroy ortiz"));

0.000 sec / 0.000 sec

[pic 5]

5

Número de artículos donde la categoría del proveedor sea igual a 20

select distinct(e.no_art) from envios as e, proveedores as p where e.no_prov=p.no_prov and p.categoria=20;

0.000 sec / 0.000 sec

select distinct(e.no_art) from envios as e inner join proveedores as p on( e.no_prov=p.no_prov) where p.categoria=20;

0.000 sec / 0.000 sec

select distinct(no_art) from envios where no_prov in(select no_prov from proveedores where categoria=20);

0.000 sec / 0.000 sec

[pic 6]

6

Componentes azules de Morelos repartidos por proveedores que residen en Querétaro.

select c.nom_com from envios as e, proveedores as p, componentes as c where e.no_prov=p.no_prov and e.no_com=c.no_com and p.ciudad="queretaro" and c.color="azul"and c.ciudad="morelos";

0.000 sec / 0.000 sec

select c.nom_com from envios as e inner join proveedores as p inner join componentes as c on(e.no_prov=p.no_prov and e.no_com=c.no_com) where p.ciudad="queretaro" and c.color="azul"and c.ciudad="morelos";

0.000 sec / 0.000 sec

select no_com from envios where no_prov in(select no_prov from proveedores where ciudad="queretaro") and no_com in(select no_com from componentes where color="azul"and ciudad="morelos");

0.000 sec / 0.000 sec

[pic 7]

7

Ciudad de los artículos que distribuyen los proveedores Juan Herrera Ortiz y Eva Monroy Ortiz

select distinct(a.ciudad) from articulos as a, envios as e, proveedores as p where a.no_art=e.no_art and e.no_prov=p.no_prov and (p.nom_prov="juan herrera cruz" or p.nom_prov="eva monrroy ortiz");

0.000 sec / 0.000 sec

select distinct(a.ciudad) from articulos as a inner join envios as e inner join proveedores as p on( a.no_art=e.no_art and e.no_prov=p.no_prov) where (p.nom_prov="juan herrera cruz" or p.nom_prov="eva monrroy ortiz");

0.000 sec / 0.000 sec

select ciudad from articulos where no_art in(select no_art from envios where no_prov in(select no_prov from proveedores where (nom_prov="juan herrera cruz" or nom_prov="eva monrroy ortiz")));

0.000 sec / 0.000 sec

[pic 8]

8

Nombre de los artículos con componentes de color rojo o verde

select distinct(a.nom_art) from articulos as a, envios as e, componentes as c where a.no_art=e.no_art and c.no_com=e.no_com and (c.color="verde"or c.color="rojo");

0.000 sec / 0.000 sec

select distinct(a.nom_art) from articulos as a inner join envios as e inner join componentes as c on (a.no_art=e.no_art and c.no_com=e.no_com) where (c.color="verde"or c.color="rojo");

0.000 sec / 0.000 sec

select nom_art from articulos where no_art in(select no_art from envios where no_com in(select no_com from componentes where (color="verde"or color="rojo")));

0.000 sec / 0.000 sec

[pic 9]

9

Proveedores que viven en Querétaro y envían el artículo “perforadora”.

select distinct(p.nom_prov) from proveedores as p,articulos as a, envios as e where e.no_prov=p.no_prov and a.no_art=e.no_art and p.ciudad="queretaro" and a.nom_art="clasificadora";

0.000 sec / 0.000 sec

select distinct(p.nom_prov) from proveedores as p inner join articulos as a inner join envios as e on(e.no_prov=p.no_prov and a.no_art=e.no_art) where p.ciudad="queretaro" and a.nom_art="clasificadora";

0.000 sec / 0.000 sec

select distinct(nom_prov) from proveedores where ciudad="queretaro" and no_prov in(select no_prov from envios where no_art in(select no_art from articulos where nom_art="clasificadora"));

0.000 sec / 0.000 sec

[pic 10]

10

Colores de los componentes que reparten el proveedor “Eva Monroy Ortiz”.

select distinct(c.color) from componentes as c, envios as e, proveedores as p where c.no_com=e.no_com and e.no_prov=p.no_prov and p.nom_prov="eva monrroy ortiz";

0.000 sec / 0.000 sec

select distinct(c.color) from componentes as c inner join envios as e inner join proveedores as p on( c.no_com=e.no_com and e.no_prov=p.no_prov) where p.nom_prov="eva monrroy ortiz";

0.000 sec / 0.000 sec

select distinct(color) from componentes where no_com in(select no_com from envios where no_prov in (select no_prov from proveedores where nom_prov="eva monrroy ortiz"));

[pic 11]