Taller 3

PARTE UNO
JOINS
1.     Realiza los Joins para relacionar las tablas de los siguientes modelos:

Reserva
Join 1: cliente.id_clie=reserva.id_cli;
Join 2: reserva.id_res=vehiculo.id_res;
Join 3: vehiculo.placa=garaje.placa;


Academia de Clases
Join 1: empresa.nitemp=alumno.nitemp;
Join 2: profesor.idpro=grupo.idpro;
Join 3: diplomado.coddip=grupo.coddip;
Join 4: alumno.idalu=asistir.idalu
Join 5: grupo.numgru=asistir.numgru

Almacén
Join 1: almacen.numalm=estanteria.numalm;
Join 2: pieza.tippie=estanteria.tippie;
Join 3: pieza.molpie=estanteria.molpie;
Join 4: pieza.tippie= componerse.tippie1
Join 5: pieza.molpie= componerse.molpie1
Join 6: pieza.tippie=componerse.tippie2;
Join 7: pieza.molpie= componerse.molpie2;

Porque siete joins, porque la llave primaria de pieza es compuesta.

Compañía
Join 1: departamento.iddpto=empleado.iddpto;
Join 2: departamento.iddpto=proyecto.iddpto;
Join 3: empleado.rutemp=trabaja.rutemp;
Join 4: proyecto.idpro=trabaja.rutemp;


Diario
Join 1: edicion.numedi=seccion.numedi;
Join 2: seccion.idsec=pagina.idsec;
Join 3: pagina.idpag=articulo.idpag;
Join 4: autor.idaut=articulo.idaut;


Revista Hemisferio
Join 1: usuario.cod_usu=anuncio.cod_usu;
Join 2: usuario.cod_usu=articulo.cod_usu;
Join 3: articulo.cod_art=clasifica.cod_art;
Join 4: categoria.cod_cat=clasifica.cod_cat;
Join 5: usuario.cod_usu=ficha.cod_usu


--Script “Pedidos”    
--https://app.box.com/s/9714y7zjuv4tq93s5v9b
--2. Utilizando el script de pedidos responder las siguientes
--consultas utilizando joins para relacionar las tablas del modelo.


--a. Muestre las órdenes generadas que contengan productos de
--la categoría de cárnicos.

select ordenes.*
from ordenes,detalleordenes,productos,categorias
where ordenes.ordenid=detalleordenes.ordenid and
productos.productoid=detalleordenes.productoid and
categorias.categoriaid=productos.categoriaid and
categorias.nombrecat='Carnicos';



--b. Muestre las ordenes solicitadas por el implado Mario Sanchez
--que fueron realizadas en la primera quincena de junio de 2007

select ordenes.*
from empleados,ordenes
where empleados.empleadoid=ordenes.empleadoid and
empleados.nombre='Mario' and
empleados.apellido='Sanchez' and
extract(day from fechaorden) between 1 and 15 and
extract(month from fechaorden)=6 and
extract(year from fechaorden)=2007;

select ordenes.*
from empleados,ordenes
where empleados.empleadoid=ordenes.empleadoid and
empleados.nombre='Mario' and
empleados.apellido='Sanchez' and
fechaorden between '01-06-2007' and '15-06-2007' ;

--c. Liste el nombre y las existencias de los productos suministrados
--por el proveedor Don Diego y que la cantidad en el detalle de la orden
--sea superior a 10.

select productos.descripcion,productos.existencia
from proveedores,productos,detalleordenes,ordenes
where proveedores.proveedorid=productos.proveedorid and
productos.productoid=detalleordenes.productoid and
ordenes.ordenid=detalleordenes.ordenid and
proveedores.nombreprov='Don Diego' and
detalleordenes.cantidad>10;



--d. Obtenga la identificación de la orden, la fecha de la orden,
--el nombre del empleado que realizo la orden, el cliente quien solicito
--la orden y que en su detalle contiene el producto con la descripción de
 --Rimmel.

select ordenes.ordenid,
ordenes.fechaorden,
empleados.nombre,
clientes.nombrecontacto,
productos.descripcion as "Producto Buscado"
from empleados,ordenes,clientes,detalleordenes,productos
where empleados.empleadoid=ordenes.empleadoid and
clientes.clienteid=ordenes.ordenid and
ordenes.ordenid=detalleordenes.ordenid and
productos.productoid=detalleordenes.productoid and
productos.descripcion='Rimmel';



--e. Muestra los nombres de los clientes que hayan solicitado
--productos suministrados por el proveedor Almay y que pertenezcan a la
--categoría de Cosmeticos.

select clientes.nombrecontacto,productos.descripcion
from clientes,ordenes,detalleordenes,productos,proveedores,categorias
where clientes.clienteid=ordenes.ordenid and
ordenes.ordenid=detalleordenes.ordenid and
productos.productoid=detalleordenes.productoid and
categorias.categoriaid=productos.categoriaid and
proveedores.proveedorid=productos.proveedorid and
proveedores.nombreprov='Almay' and
categorias.nombrecat='Cosmeticos';



--Script “Tienda Informática”  
--https://app.box.com/s/htt6jpmr4d6azftn2hxr
--3. Utilizando el script de tienda informática responder las siguientes
--consultas utilizando joins para relacionar las tablas del modelo.

--f. Obtener la clave y precio del articulo donde su nombre tiene USB
--y fue suministrada por el fabricante Kingston.

select articulos.clavearticulo,articulos.precio
from articulos,fabricantes
where fabricantes.clavefabricante=articulos.clavefabricante and
articulos.nombrearticulo like '%USB%' and
fabricantes.nombre='Kingston';



--g. Obtener todos los datos de los fabricantes donde el precio
--artículos  esté entre $100 y $350

select distinct fabricantes.clavefabricante,fabricantes.nombre
from fabricantes,articulos
where fabricantes.clavefabricante=articulos.clavefabricante and
articulos.precio between 100 and 350;



--h. Obtener un listado de artículos, incluyendo el nombre del
--artículo, su precio, y el nombre de su fabricante.

select articulos.nombrearticulo,articulos.precio,fabricantes.nombre
from fabricantes,articulos
where fabricantes.clavefabricante=articulos.clavefabricante;



--i. Obtener un listado completo de artículos, incluyendo los
--de su fabricante.

select *
from articulos,fabricantes
where fabricantes.clavefabricante=articulos.clavefabricante;



--j. Muestre la información de los artículos y fabricantes,
--ya sea el precio de los productos sean menores a 200 o que comienzan por M
--y el fabricante sea Kingston.

select *
from articulos,fabricantes
where fabricantes.clavefabricante=articulos.clavefabricante and
(articulos.precio<200 or (nombrearticulo like 'M%' and fabricantes.nombre='Kingston'));



--PARTE DOS
--FUNCIONES DE AGREGADO
--AVG – SUM – COUNT – MAX – MIN
--Script “Pedidos”
--https://app.box.com/s/9714y7zjuv4tq93s5v9b

--4. Utilizando el script de pedidos responder las siguientes consultas
--utilizando joins para relacionar las tablas del modelo, funciones de agregado
--y subconsultas para responder los ítems.

--k. Muestre el nombre de la categoría y cantidad de productos que pertenecen a ella.

select categorias.nombrecat,count(productoid)
from categorias,productos
where categorias.categoriaid=productos.categoriaid
group by categorias.nombrecat;



--l. Muestre el precio promedio de las distintas categorías.

select categorias.nombrecat,trunc(avg(productos.preciounit),2) as "AVG x Categoria"
from categorias,productos
where categorias.categoriaid=productos.categoriaid
group by categorias.nombrecat;


--m. Obtenga la cantidad de clientes que hayan solicitado productos
--suministrados por el proveedor Almay y que pertenezcan a la categoría de Cosmeticos.


select count(clientes.clienteid)
from clientes,ordenes,detalleordenes,productos,categorias,proveedores
where clientes.clienteid=ordenes.clienteid and
ordenes.ordenid=detalleordenes.ordenid and
productos.productoid=detalleordenes.productoid and
categorias.categoriaid=productos.categoriaid and
proveedores.proveedorid=productos.proveedorid and
proveedores.nombreprov='Almay' and
categorias.nombrecat='Cosmeticos';

--n. Muestre las existencias totales según el proveedor que surtió el producto.
select count(productos.existencia)
from proveedores,productos
where proveedores.proveedorid=productos.proveedorid
group by proveedores.nombreprov;


--o. Muestre el nombre del proveedor y el precio del producto más costoso.
select proveedores.nombreprov,max(productos.preciounit)
from proveedores,productos
where proveedores.proveedorid=productos.proveedorid
group by proveedores.nombreprov;


--Script “Tienda Informática”
--https://app.box.com/s/htt6jpmr4d6azftn2hxr
--5. Utilizando el script de tienda informática responder las siguientes
--consultas utilizando joins para relacionar las tablas del modelo, funciones de agregado
--y subconsultas para responder los ítems.

--p. Obtener el precio medio de los productos por cada fabricante, mostrando
--el nombre del fabricante.

select fabricantes.nombre, trunc(avg(articulos.precio),2)
from articulos,fabricantes
where fabricantes.clavefabricante=articulos.clavefabricante
group by fabricantes.nombre;


--q. Obtener los nombres de los fabricantes de los productos cuyo
--precio medio sea superior o igual a 60. (Sub)

select fabricantes.nombre
from articulos, fabricantes
where (select avg(articulos.precio) from articulos)>=60
group by fabricantes.nombre;


--r. Muestre el nombre del fabricante y la cantidad de productos que provee.

select fabricantes.nombre,
count(articulos.clavearticulo) as "CantidadProductos"
from articulos,fabricantes
where fabricantes.clavefabricante=articulos.clavefabricante
group by fabricantes.nombre;


--s. Obtener el nombre y precio del articulo más barato. (Sub)

select articulos.nombrearticulo,articulos.precio
from articulos
where precio=(select min(articulos.precio) from articulos);


--t. Obtener una lista con el nombre y precio de los artículos más caros de cada proveedor,
--incluyendo el nombre del fabricante.

select fabricantes.nombre,articulos.nombrearticulo,precio
from articulos,fabricantes
where precio=(select max(articulos.precio)
     from articulos
     where fabricantes.clavefabricante=articulos.clavefabricante);


--Script “Cliclista”
--https://app.box.com/s/rclpz26ndhli9svvq4so
--6. Utilizando el script de ciclista responder las siguientes consultas utilizando joins
-- para relacionar las tablas del modelo, funciones de agregado y subconsultas para responder los ítems.

--u. Obtener el nombre del ciclista más joven

select nombre
from ciclista
where edad=(select min(edad) from ciclista);


--v. Obtener el número de ciclistas de cada equipo

select count(dorsal)
from ciclista
group by nomeq;


--w. Obtener el nombre de los equipos que tengan más de 5 ciclistas.
select nomeq
from ciclista
group by nomeq
having count(dorsal)>5;


--x. Obtener el nombre de los ciclistas que han ganado más de un puerto

select ciclista.nombre, count(puerto.dorsal)
from ciclista,puerto
where ciclista.dorsal=puerto.dorsal
group by puerto.dorsal,ciclista.nombre
having count(puerto.dorsal)>1;


--y. Nombre de los ciclistas que no han ganado ninguna etapa.

select nombre
from ciclista
where dorsal not in(select dorsal from etapa);


--Script “Empleado & Departamento”
--https://app.box.com/s/aet7i391g07ydnlz0ovt
--7. Utilizando el script de Empleado y Departamento responder las siguientes consultas
--utilizando joins para relacionar las tablas del modelo, funciones de agregado y subconsultas
--para responder los ítems.

--z. Para los empleados que tengan como jefe a un empleado con código mayor que el suyo,
--obtén los que reciben de salario más de 1000 y menos de 2000, o que están en el departamento 30.

select *
from empleado
where empsup>empnro and ((empsal>1000 and empsal<2000)or (dptonro=30));


--aa. Obtén el salario más alto de la empresa, el total destinado a comisiones
--y el número de empleados.

select max(empsal) as "Salario Maximo",
sum(comm) as "Sumatorial de Comisiones" ,
count(empnro) as "Cuantos Empleados"
from empleado;


--bb. Halla el salario más alto, el más bajo, y la diferencia entre ellos.
select max(empsal) as "Salario Maximo",
min(empsal) as "Salario Minimo",
(max(empsal)-min(empsal)) as "Resta entre el Max y Min"
from empleado;


--cc. Halla los empleados cuyo salario supera o coincide con la media del salario de la empresa.

select *
from empleado
where empsal>=(select avg(empsal) from empleado);


--dd. ¿Cuántos empleos diferentes, cuántos empleados, y cuantos salarios diferentes
--encontramos en el departamento 30, y a cuánto asciende la suma de salarios de dicho departamento?

select count(distinct empcar) as "Cuantos Empleos",
count(empnom)"Cuantos Empleados",
count(distinct empsal)"Salarios Diferentes",
sum(empsal)"Sumatorias Salarios"
from empleado
where dptonro=30;


--ee. ¿Cuántos empleados tienen comisión?

select count(comm)
from empleado
where comm>0;