SUBCONSULTAS
Devuelve el producto que más unidades tiene en stock.
select *
from producto
where cantidad_en_stock >= all
(select cantidad_en_stock
from producto);
ó
select *
from producto
where cantidad_en_stock =
(select max(cantidad_en_stock)
from producto);
Obtén un listado con el nombre de cada empleado y el nombre y apellido de su jefe.
select e.nombre, je.nombre
from empleado e join empleado je on e.codigo_jefe = je.codigo_empleado;
Obtener el nombre del cliente y el código del pedido, mostrando también los clientes que no han hecho ningún pedido.
SELECT cliente.nombre_cliente, pedido.codigo_pedido
FROM cliente
LEFT JOIN pedido
ON cliente.codigo_cliente = pedido.codigo_cliente;
Realiza una vista "gamas_y_productos" con el nombre de cada gama y la cantidad de productos pertenecientes a ellas.
Ahora realiza una consulta sobre esa vista que muestre la gama de la que se tienen más productos.
create or replace view gamas_y_productos as
select p.gama, count(*) as "productos"
from producto p
group by p.gama;
select gama, productos
from gamas_y_productos
where productos = (select max(productos) from gamas_y_productos);
Devuelve el nombre, apellido1 y cargo de los empleados que no representen a ningún cliente.
select e.nombre, e.apellido1, e.puesto
from empleado e
where e.codigo_empleado not in
(select c.codigo_empleado_rep_ventas
from cliente c);
Devuelve el nombre de los clientes que no hayan hecho pagos y el nombre de sus representantes junto con la ciudad de la oficina a la que pertenece el representante.
select cl.nombre_cliente, e.nombre, o.ciudad
from cliente cl join empleado e on cl.codigo_empleado_rep_ventas = e.codigo_empleado
join oficina o on o.codigo_oficina = e.codigo_oficina
where cl.codigo_cliente not in ( select codigo_cliente
from pago);
Obtener el nombre del empleado y el nombre del producto que ha vendido, mostrando también los empleados que no han vendido ningún producto.
SELECT e.nombre, pro.nombre
FROM empleado e
left JOIN cliente c ON e.codigo_empleado = c.codigo_empleado_rep_ventas
left JOIN pedido p ON c.codigo_cliente = p.codigo_cliente
left JOIN detalle_pedido dp ON p.codigo_pedido = dp.codigo_pedido
LEFT JOIN producto pro ON dp.codigo_producto = pro.codigo_producto;
Realize una vista "pagos_por_cliente" con el nombre, los importes de los pagos que ha hecho, y la fecha de cada pago.
Ahora realize una consulta sobre la vista donde saque el nombre de los clientes y la cantidad total que pagaron cada año.
create or replace view pagos_por_cliente as
select c.nombre_cliente "nombre", p.total "importe", p.fecha_pago "fecha"
from cliente c join pago p using (codigo_cliente);
select nombre, sum(importe), year(fecha)
from pagos_por_cliente
group by year(fecha), nombre;
Devuelve un listado de los nombres de los productos que han aparecido en un pedido alguna vez.
Debe aparecer una vez cada nombre.
select distinct(p.nombre)
from producto p
where exists (select dp.codigo_pedido
from detalle_pedido dp
where dp.codigo_producto = p.codigo_producto);
Lista la dirección de las oficinas que tengan clientes en Fuenlabrada
select o.linea_direccion1, o.linea_direccion2, o.ciudad
from oficina o join empleado e on e.codigo_oficina = o.codigo_oficina
join cliente c on e.codigo_empleado = c.codigo_empleado_rep_ventas
where c.ciudad like "%Fuenlabrada%";
Obtener el nombre del cliente y la gama del producto que ha comprado, de los clientes que han comprado Frutales mostrando también los clientes que no han comprado ningún producto de gama Frutales.
SELECT cliente.nombre_cliente, producto.gama
FROM cliente
LEFT JOIN pedido
ON cliente.codigo_cliente = pedido.codigo_cliente
LEFT JOIN detalle_pedido
ON pedido.codigo_pedido = detalle_pedido.codigo_pedido
LEFT JOIN producto
ON detalle_pedido.codigo_producto = producto.codigo_producto
AND producto.gama = 'Frutales';
Realiza una vista donde aparezca el nombre y apellidos de cada jefe, con los de sus empleados, y la oficina a la que pertenecen. Los nombres y apellidos tienen que estar en una sola columna
Ahora realiza una consulta sobre la vista donde se vea la ciudad de la oficina tiene mas empleados.
create or replace view jefes_y_empleados as
select concat (j.nombre," ",j.apellido1," ", j.apellido2) "jefe",
concat (e.nombre," ",e.apellido1," ", e.apellido2) "empleado", e.codigo_oficina "oficina"
from empleado e join empleado j on e.codigo_jefe = j.codigo_empleado;
select o.ciudad
from oficina o join jefes_y_empleados je on je.oficina = o.codigo_oficina
group by o.codigo_oficina
order by count(je.empleado) desc
limit 1;
Devuelve un listado indicando todas las ciudades donde hay oficinas y el número de empleados que tiene.
select distinct(o.ciudad), (select count(*)
from empleado e
where e.codigo_oficina = o.codigo_oficina) as "Numero empleados"
from oficina o;
Devuelve el nombre de los clientes a los que no se les ha entregado a tiempo un pedido.
select distinct(c.nombre_cliente)
from cliente c join pedido p on c.codigo_cliente = p.codigo_cliente
where p.fecha_entrega > p.fecha_esperada;
Obtener el nombre de los clientes que hayan hecho un pedido de algún producto de la gama "Ornamentales", y tambien alguno de la gama "Frutales", incluídos aquellos que no cumplan esta condición.
SELECT distinct(c.nombre_cliente)
FROM cliente c
LEFT JOIN pedido p ON c.codigo_cliente = p.codigo_cliente
LEFT JOIN detalle_pedido dp ON p.codigo_pedido = dp.codigo_pedido
LEFT JOIN producto pr ON dp.codigo_producto = pr.codigo_producto
WHERE pr.gama = 'Ornamentales'
AND c.codigo_cliente IN (
SELECT c2.codigo_cliente
FROM cliente c2
LEFT JOIN pedido p2 ON c2.codigo_cliente = p2.codigo_cliente
LEFT JOIN detalle_pedido dp2 ON p2.codigo_pedido = dp2.codigo_pedido
LEFT JOIN producto pr2 ON dp2.codigo_producto = pr2.codigo_producto
WHERE pr2.gama = 'Frutales'
);
Realiza una vista donde aparezca el nombre de cada proveedor, y la cantidad de productos que se ha vendido del mismo en cada numero_linea.
Ahora realiza una consulta sobre dicha vista que devuelva la cantidad de proveedores distintos de los productos que se han cobrado en cada linea.
create or replace view proveedores_en_linea as
select p.proveedor `proveedor`, sum(dp.cantidad) `cantidad`, dp.numero_linea `linea`
from producto p join detalle_pedido dp using (codigo_producto)
group by dp.numero_linea, p.proveedor;
select distinct(count(proveedor)), linea
from proveedores_en_linea
group by linea
order by linea;
Devuelve el nombre de los clientes que hayan hecho pedidos en 2021 ordenados alfabéticamente de menor a mayor.
select c.nombre_cliente
from cliente c
where c.codigo_cliente in
(select p.codigo_cliente
from pedido p
where year(p.fecha_pedido) = 2021)
order by c.nombre_cliente;
Obtener la gama de producto que tiene el mayor precio promedio y su descripción
SELECT gama_producto.gama, gama_producto.descripcion_texto, AVG(producto.precio_venta) AS precio_promedio
FROM gama_producto INNER JOIN producto ON gama_producto.gama = producto.gama
GROUP BY gama_producto.gama
ORDER BY precio_promedio DESC LIMIT 1;
Obtener la cantidad total de productos que ha vendido cada empleado, incluyendo aquellos empleados que no han vendido ningún producto:
SELECT e.nombre, COUNT(dp.codigo_producto) as cantidad_vendida
FROM empleado e
left JOIN cliente c ON e.codigo_empleado = c.codigo_empleado_rep_ventas
left JOIN pedido p ON c.codigo_cliente = p.codigo_cliente
left JOIN detalle_pedido dp ON p.codigo_pedido = dp.codigo_pedido
group by e.nombre ;
Realiza una vista con los nombres de los clientes, el limite de crédito que tienen, la cantidad de pedidos que ha hecho y la cantidad total de pagos que ha hecho.
Ahora realiza una consulta sobre dicha vista que muestre los clientes cuya cantidad de pedidos supere la media total. Ordénalos por límite de crédito de mayor a menor.
create or replace view datos_clientes as
select c.nombre_cliente "nombre", c.limite_credito "credito", count(pe.codigo_pedido) "num_pedidos", sum(pa.total) "total_pagos"
from cliente c join pago pa using (codigo_cliente) join pedido pe using (codigo_cliente)
group by c.codigo_cliente;
select dc.nombre, dc.credito
from datos_clientes dc
where dc.num_pedidos > (select avg(num_pedidos)
from datos_clientes)
order by dc.credito desc ;