Clases Repaso 13_14

I.E.S. Puerto de la Cruz

1º ASIR - GTB

EJERCICIOS PRÁCTICOS SQL. - SOLUCIONES Utilizando el siguiente esquema de la base de datos “jardinería”

Codif ica ica en en SQL (My (MyS SQ L) L) se sen nte ten ncias para obt obte ener la la si sigui uie ente inf orm rmac ación: ión:

Curso 2013/14


1º ASIR - GTB

Curso 2013/14

Mostrar el el nombre (NombreCliente) de los clientes españoles. ¿Qué mostrar? NombreCliente

¿Dónde buscar? Clientes

Filtro Pais = „Espa??a‟ Pais = „Spain‟

SELECT clientes.NombreCliente SELECT clientes.NombreCliente FROM clientes FROM clientes WHERE clientes.pais WHERE clientes.pais IN („Espa??a‟,‟Spain‟);

(o También) SELECT clientes.NombreCliente SELECT clientes.NombreCliente FROM clientes FROM clientes WHERE (clientes.pais WHERE (clientes.pais = „Espa??a‟) OR (clients.pais = ‟Spain‟);

Agrupar


1º ASIR - GTB

Curso 2013/14

Mostrar cuántos clientes tienen las ciudades cuyo Mostrar cuántos cuyo nombre nombre (Ciudad) contiene la palabra “del”. ¿Qué mostrar? ¿Dónde buscar? Cuántos clientes Clientes COUNT(CodigoCliente) las ciudades: Ciudad

Filtro

Agrupar

Ciudad contiene „ del „ Ciudad Like „% del %„

Ciudad

SELECT clientes.Ciudad SELECT clientes.Ciudad , COUNT(clientes.CodigoCliente) COUNT(clientes.CodigoCliente) AS NumClientes FROM clientes FROM clientes WHERE clientes.Ciudad WHERE clientes.Ciudad LIKE „% del %‟ GROUP BY clientes.Ciudad; BY clientes.Ciudad;

ciudades cuyo Mostrar cuántos clientes tienen las ciudades Mostrar cuántos cuyo nombre nombre (Ciudad) empieza por M. M.

¿Qué mostrar? ¿Dónde buscar? Cuántos clientes Clientes COUNT(CodigoCliente) las ciudades: Ciudad

Filtro

Agrupar

Ciudad empieza por „M„ Ciudad Like „M%„

SELECT clientes.Ciudad SELECT clientes.Ciudad , COUNT(clientes.CodigoCliente) COUNT(clientes.CodigoCliente) AS NumClientes FROM clientes FROM clientes WHERE clientes.Ciudad WHERE clientes.Ciudad LIKE „M%‟ GROUP BY clientes.Ciudad; BY clientes.Ciudad;

Ciudad


1º ASIR - GTB

Curso 2013/14

Mostrar el código de empleado (CodigoEmpleadoRepVentas) y el número de clientes al que atiende cada empleado. ¿Qué mostrar? ¿Dónde buscar? CodigoEmpleadoRepVentas Clientes Número de clientes COUNT(CodigoCliente)

Filtro

SELECT clientes.CodigoEmpleadoRepVentas, COUNT(CodigoCliente) AS NumClientes FROM clientes GROUP BY clientes.CodigoEmpleadoRepVentas;

Agrupar CodigoEmpleadoRepVentas


1º ASIR - GTB

Curso 2013/14

Mostrar cuál fue el mayor pago (Cantidad) efectuado por los clientes en el año 2008. ¿Qué mostrar? Mayor pago (Cantidad) MAX(Cantidad)

¿Dónde buscar? Pagos

Filtro Año 2008 FechaPago = ??/??/2008 YEAR(FechaPago)=2008

Agrupar

Filtro Año 2006 FechaPago = ??/??/2006 YEAR(FechaPago)=2006

Agrupar

SELECT MAX(Cantidad) FROM pagos WHERE Year(FechaPago)=‟2008‟;

Mostrar cuál fue el pago medio en 2006. ¿Qué mostrar? Pago medio (Cantidad) AVG(Cantidad)


SELECT AVG(Cantidad) FROM pagos WHERE Year(FechaPago)=‟2006‟;


1º ASIR - GTB

Curso 2013/14

Mostrar cuántos clientes tiene cada país. ¿Qué mostrar? Cuántos clientes COUNT(CodigoCliente) Cada país: País

¿Dónde buscar? Clientes

Filtro

SELECT clientes.Pais, COUNT(clientes.CodigoCliente) AS NumClientes FROM clientes GROUP BY Pais;

Agrupar Pais


1º ASIR - GTB

Curso 2013/14

Mostrar el nombre (Nombre), apellidos (Apellido1, Apellido2) y puesto (Puesto) de aquellos empleados que no ocupen el puesto de “R epresentante de ventas”. ¿Qué mostrar? Nombre

¿Dónde buscar? Empleados

Filtro No ocupen

Agrupar puesto

„Representante Ventas‟

Apellido1

NOT

Puesto

„Representante Ventass‟

Apellido2 Puesto

SELECT empleados.Nombre, empleados.Apellido1, empleados.Apellido2, empleados.Puesto FROM empleados WHERE NOT empleados.Puesto=‟Representante Ventas‟;

=


1º ASIR - GTB

Curso 2013/14

Mostrar cuántas oficinas tiene la ciudad de Madrid. ¿Qué mostrar? ¿Dónde buscar? Cuántas oficinas Oficinas COUNT(CodigoOficina) (opcional) Ciudad

Filtro Ciudad de Madrid

Agrupar

Ciudad = „Madrid‟

(opcional) Ciudad

SELECT COUNT(oficinas.CodigoOficina) AS NumOficinas FROM oficinas WHERE oficinas.Ciudad=‟Madrid‟;

Mostrar cuántos clientes tiene la ciudad de Madrid. ¿Qué mostrar? ¿Dónde buscar? Cuántas clientes Clientes COUNT(CodigoCliente) (opcional) Ciudad

Filtro Ciudad de Madrid

Agrupar

Ciudad = „Madrid‟

SELECT COUNT(clientes.CodigoCliente) AS NumClientes FROM clientes WHERE clientes.Ciudad=‟Madrid‟;

(opcional) Ciudad


1º ASIR - GTB

Curso 2013/14

Mostrar los distintos estados por los que puede pasar un pedido. ¿Qué mostrar? Distintos estados Estado

¿Dónde buscar? Pedidos

SELECT DISTINCT pedidos.Estado FROM pedidos;

O también SELECT pedidos.Estado FROM pedidos GROUP BY pedidos.Estado;

Filtro

Agrupar Estado


1º ASIR - GTB

Curso 2013/14

Mostrar el nombre completo del empleado (nombre y apellidos en una sola columna), el puesto (Puesto), el código de la oficina ( CodigoOficina) en la que trabaja y la ciudad (Ciudad) y país (Pais) de dicha oficina. ¿Qué mostrar? Nombre completo del empleado una columna CONCAT(Nombre, Apellido1, Apellido2) Puesto CodigoOficina Ciudad Pais

¿Dónde buscar? Empleados

Filtro

Agrupar

Empleados Empleados Oficinas Oficinas

SELECT CONCAT(empleados.Nombre, “ “, empleados.Apellido1, “ “, empleados.Apellido2) AS NombreCompleto, empleados.Puesto, empleados.CodigoOficina, oficinas.Ciudad, oficinas.Pais FROM empleados INNER JOIN oficinas ON empleados.CodigoOficina=oficinas.CodigoOficina;

……


1º ASIR - GTB

Curso 2013/14

Mostrar cuál fue el primer y último pago (FechaPago) que hizo cada cliente. Debe mostrar también el código del cliente (CodigoCliente), nombre del cliente (NombreCliente). ¿Qué mostrar? Primer pago MIN(FechaPago) Último pago MAX(FechaPago) CodigoCliente NombreCliente


Filtro

Agrupar

Pagos Pagos Clientes

SELECT pagos.CodigoCliente, clientes.NombreCliente, MIN(pagos.FechaPago) AS PrimerPago, MAX(pagos.FechaPago) AS UltimoPago FROM pagos INNER JOIN clientes ON pagos.CodigoCliente=clientes.CodigoCliente GROUP BY pagos.CodigoCliente;

CodigoCliente (opcional) NombreCliente


1º ASIR - GTB

Curso 2013/14

Mostrar el número de pedido (CodigoPedido), fecha del pedido (FechaPedido), fecha esperada (FechaEsperada), fecha de entrega (FechaEntrega), código de cliente (CodigoCliente) y nombre del cliente (NombreCliente) de los pedidos en estado “Pendiente” que han sido entregados (tienen fecha de entrega). ¿Qué mostrar? CodigoPedido FechaPedido FechaEsperada FechaEntrega

¿Dónde buscar? Pedidos Pedidos Pedidos Pedidos

CodigoCliente NombreCliente

Pedidos Clientes

Filtro Estado=„Pendiente‟

Tienen algo en FechaEntrega NOT FechaEntrega IS NULL

SELECT pedidos.CodigoPedido, pedidos.FechaPedido, pedidos.FechaEsperada, pedidos.FechaEntrega, pedidos.CodigoCliente, clientes.NombreCliente FROM pedidos INNER JOIN clientes ON pedidos.CodigoCliente = clientes.CodigoCliente WHERE (pedidos.Estado = „Pendiente‟) AND (NOT Pedidos.FechaEntrega IS NULL);

Agrupar


1º ASIR - GTB

Curso 2013/14

Mostrar el número de pedido (CodigoPedido), fecha del pedido (FechaPedido), fecha esperada (FechaEsperada), fecha de entrega (FechaEntrega), código de cliente (CodigoCliente) y nombre del cliente (NombreCliente) de los pedidos en estado “Rechazado” que han sido entregados (tienen fecha de entrega). ¿Qué mostrar? CodigoPedido FechaPedido FechaEsperada FechaEntrega

¿Dónde buscar? Pedidos Pedidos Pedidos Pedidos

CodigoCliente NombreCliente

Pedidos Clientes

Filtro Estado=„Rechazado‟

Tienen algo en FechaEntrega NOT FechaEntrega IS NULL

SELECT pedidos.CodigoPedido, pedidos.FechaPedido, pedidos.FechaEsperada, pedidos.FechaEntrega, pedidos.CodigoCliente, clientes.NombreCliente FROM pedidos INNER JOIN clientes ON pedidos.CodigoCliente = clientes.CodigoCliente WHERE (pedidos.Estado = „Rechazado‟) AND (NOT Pedidos.FechaEntrega IS NULL);

Agrupar


1º ASIR - GTB

Curso 2013/14


1º ASIR - GTB

Curso 2013/14

Mostrar un listado de los 5 productos que más beneficios producen a la empresa en cada pedido. Deben mostrar el código del producto (CodigoProducto), nombre del producto (Nombre) y el beneficio sobre cada unidad de producto pedido. Beneficio = (detallepedidos.PrecioUnidad – productos.PrecioProveedor) (pista: Usar el filtro LIMIT de MySQL) ¿Qué mostrar? CodigoProducto Nombre Beneficio = PrecioUnidad PrecioProveedor

¿Dónde buscar? Detallepedidos Productos

Filtro

Agrupar CodigoProducto Nombre Beneficio

Detallepedidos Productos

AGRUPAR para evitar que el mismo producto ocupe los 5 primeros puestos ORDENAR DESCENDENTE para que aparezcan primero los mayores valores de beneficio. LIMITAR a los 5 primeros SELECT detallepedidos.CodigoProducto, productos.Nombre, (detallepedidos.PrecioUnidad – productos.PrecioProveedor) AS Benef FROM detallepedidos INNER JOIN productos ON detallepedidos.CodigoProducto = productos.CodigoProducto GROUP BY detallepedidos.CodigoProducto, productos.Nombre, Benef ORDER BY Benef DESC LIMIT 5;

Probar a quitar el agrupamiento o a agrupar sólo por CodigoProducto


1º ASIR - GTB

Curso 2013/14

Mostrar el número de pedido (CodigoPedido), código de cliente (CodigoCliente), nombre de cliente (NombreCliente), teléfono del cliente (Telefono), estado del pedido (Estado), fecha esperada (FechaEsperada) y fecha de entrega (FechaEntrega) de los pedidos cuya fecha de entrega ha sido al menos tres días después de la fecha esperada. (pista: Usar la función addDate() de MySQL) ¿Qué mostrar? CodigoPedido CodigoCliente NombreCliente Telefono Estado FechaEsperada FechaEntrega

¿Dónde buscar? Pedidos Pedidos Clientes Clientes Pedidos Pedidos Pedidos

Filtro

FechaEntrega FechaEsperada + 3

Agrupar

>=

SELECT pedidos.CodigoPedido, pedidos.CodigoCliente, clientes.NombreCliente, clientes.Telefono, pedidos.Estado, pedidos.FechaEsperada, pedidos.FechaEntrega FROM pedidos INNER JOIN clientes ON pedidos.CodigoCliente = clientes.CodigoCliente WHERE pedidos.FechaEntrega>=addDate(pedidos.FechaEsperada,3);

……………


1º ASIR - GTB

Curso 2013/14


1º ASIR - GTB

Curso 2013/14

Mostrar las gamas de los productos (DescripcionTexto) y el importe total que suman los productos que tenemos en stock (CantidadEnStock * PrecioVenta) de cada gama de producto. ¿Qué mostrar? DescripcionTexto ImporteTotal = SUM( CantidadEnStock * PrecioVenta)

¿Dónde buscar? GamasProductos

Filtro

Agrupar DescripcionTexto

Productos Productos

SELECT gamasproductos.DescripcionTexto, SUM(productos.CantidadEnStock*productos.PrecioVenta) AS ImporteTotal FROM productos INNER JOIN gamasproductos ON productos.Gama=gamasproductos.Gama GROUP BY gamasproductos.DescripcionTexto;


1º ASIR - GTB

Curso 2013/14

Mostrar las gamas de los productos ( Gama y DescripcionTexto) y el importe total que suman los productos que tenemos en stock de cada gama de producto. (Importe = (CantidadEnStock * PrecioVenta)). Mostrar sólo las gamas de productos en las que el importe total supere el valor de 10000. ¿Qué mostrar? Gama DescripcionTexto ImporteTotal = SUM( CantidadEnStock * PrecioVenta)

¿Dónde buscar? GamasProductos GamasProductos

Filtro

Agrupar Gama

Productos Productos ImporteTotal > 1000

SELECT gamasproductos.Gama, gamasproductos.DescripcionTexto, SUM(productos.CantidadEnStock*productos.PrecioVenta) AS ImporteTotal FROM productos INNER JOIN gamasproductos ON productos.Gama=gamasproductos.Gama GROUP BY gamasproductos.Gama HAVING ImporteTotal>10000;


1º ASIR - GTB

Curso 2013/14

Mostrar el código del producto (CodigoProducto), nombre del producto (Nombre), gama del producto (DescripcionTexto) y la cantidad total (suma) pedida de cada producto (detallepedidos.Cantidad), de los productos de la gama “Frutales”. ¿Qué mostrar? CodigoProducto Nombre DescripcionTexto CantidadTotal = SUM( Cantidad)

¿Dónde buscar? Productos Productos GamasProductos

Filtro

Agrupar CodigoProducto

DetallePedidos Gama = „Frutales‟

Tenemos dos posibilidades: 

Todos los productos de „Frutales‟ aunque no se hayan pedido nunca

SELECT productos.CodigoProducto, productos.Nombre, gamasproductos.DescripcionTexto, SUM(detallepedidos.Cantidad) AS CantidadTotal FROM productos INNER JOIN gamasproductos ON productos.Gama=gamasproductos.Gama LEFT JOIN detallepedidos ON productos.CodigoProducto=detallepedidos.CodigoProducto WHERE productos.Gama=‟Frutales‟ GROUP BY productos.CodigoProducto, productos.Nombre, gamasproductos.DescripcionTexto;


1º ASIR - GTB

Curso 2013/14

…………

Con LEFT JOIN a la tabla detallepedidos muestra todos los productos de la gama, aunque no se hayan pedido nunca. 

Todos los productos de „Frutales‟ que se han pedido alguna vez

SELECT productos.CodigoProducto, productos.Nombre, gamasproductos.DescripcionTexto, SUM(detallepedidos.Cantidad) AS CantidadTotal FROM productos INNER JOIN gamasproductos ON productos.Gama=gamasproductos.Gama INNER JOIN detallepedidos ON productos.CodigoProducto=detallepedidos.CodigoProducto WHERE productos.Gama=‟Frutales‟ GROUP BY productos.CodigoProducto, productos.Nombre, gamasproductos.DescripcionTexto;

…………..


1º ASIR - GTB

Curso 2013/14

Mostrar el nombre completo (Nombre, Apellido1 y Apellido2) de los empleados del puesto (Puesto) “Representante de ventas”, el nombre de los clientes a los que representa (NombreCliente) y el teléfono del cliente (Telefono) de todos los clientes de la ciudad de Madrid. Ordenado por nombre y apellidos del empleado representante de ventas y después por nombre del cliente. ¿Qué mostrar? Nombre Apellido1 Apellido2

¿Dónde buscar? Empleados Empleados Empleados

Filtro

Agrupar

Puesto = „Representante Ventas‟

NombreCliente Telefono

Clientes Clientes Ciudad = „Madrid‟

ORDENAR POR Nombre, Apellido1, Apellido2, NombreCliente SELECT empleados.Nombre, empleados.Apellido1, empleados.Apellido2, clientes.NombreCliente, clientes.Telefono FROM clientes INNER JOIN empleados ON clientes.CodigoEmpleadoRepVentas = empleados.CodigoEmpleado WHERE empleados.Puesto=‟Representante Ventas‟ AND clientes.Ciudad='Madrid' ORDER BY empleados.Nombre, empleados.Apellido1, empleados.Apellido2, clientes.NombreCliente;


1º ASIR - GTB

Curso 2013/14


1º ASIR - GTB

Curso 2013/14

Mostrar el importe total de los pagos efectuados por cada cliente de España (Espa??a o Spain). Debe mostrar el código del cliente (CodigoCliente), nombre del cliente (NombreCliente) y el importe total pagado ( suma de las cantidades). ¿Qué mostrar? ImporteTotal CodigoCliente NombreCliente ImporteTotal = SUM( Cantidad)

¿Dónde buscar? ?????????? Pagos Clientes

Filtro

Agrupar

Pais = „Espa??a‟ o Pais = „Spain‟

Clientes Clientes Pagos

SELECT pagos.CodigoCliente, clientes.NombreCliente, SUM(pagos.Cantidad) AS TotalPagado FROM pagos INNER JOIN clientes ON pagos.CodigoCliente = clientes.CodigoCliente WHERE clientes.Pais IN ('Espa??a','Spain') GROUP BY pagos.CodigoCliente, clientes.NombreCliente;

CodigoCliente


1º ASIR - GTB

Curso 2013/14

Mostrar el importe total de los pagos efectuados por cada cliente de España (Espa??a o Spain). Debe mostrar el código del cliente (CodigoCliente), nombre del cliente (NombreCliente) y el importe total pagado ( suma de las cantidades). Mostrar sólo cuando el importe total supere el valor de 10000. ) ¿Qué mostrar? ImporteTotal CodigoCliente NombreCliente ImporteTotal = SUM( Cantidad)

¿Dónde buscar? ?????????? Pagos Clientes

Filtro

Agrupar

Pais = „Espa??a‟ o Pais = „Spain‟

Clientes Clientes

CodigoCliente

Pagos ImporteTotal > 10000

SELECT pagos.CodigoCliente, clientes.NombreCliente, SUM(pagos.Cantidad) AS TotalPagado FROM pagos INNER JOIN clientes ON pagos.CodigoCliente = clientes.CodigoCliente WHERE clientes.Pais IN ('Espa??a','Spain') GROUP BY pagos.CodigoCliente, clientes.NombreCliente HAVING TotalPagado > 10000;


1º ASIR - GTB

Curso 2013/14

Mostrar los nombres completos (nombre y apellidos en una sola columna) de los empleados acompañados de los nombres completos (nombre y apellidos en una sola columna) de sus jefes de aquellos empleados cuyo puesto (Puesto) sea „Representante Ventas‟. Se debe mostrar también el puesto que ocupa el jefe (Puesto). ¿Qué mostrar? NombreCompleto = CONCAT( Nombre, Apellido1, Apellido2) NombreCompleto = CONCAT( Nombre, Apellido1, Apellido2)

¿Dónde buscar?

Filtro

Agrupar

Empleados Empleados Empleados Empleados AS Jefes Empleados AS Jefes Empleados AS Jefes Empleados.Puesto

=

„Representante Ventas‟

Puesto

Empleados AS Jefes

SELECT CONCAT(empleados.Nombre, “ “, empleados.Apellido1, “ “, empleados.Apellido2) AS NombreEmpleado, CONCAT(jefes.Nombre, “ “, jefes.Apellido1, “ “, jefes.Apellido2) AS NombreJefe, jefes.Puesto AS PuestoJefe FROM empleados INNER JOIN empleados jefes ON empleados.CodigoJefe = jefes.CodigoEmpleado WHERE empleados.Puesto='Representante Ventas';


1º ASIR - GTB

Curso 2013/14


1º ASIR - GTB

Curso 2013/14

Mostrar el número de pedido (pedidos. CodigoPedido), código de cliente (pedidos.CodigoCliente), nombre del cliente (clientes.NombreCliente), fecha requerida (pedidos.FechaEsperada) y fecha de entrega (pedidos.FechaEntrega) de los pedidos que no

han sido entregados a tiempo Entrega a tiempo: (pedidos.FechaEntrega<=pedidos.FechaEsperada) Entrega fuera de tiempo: (pedidos.FechaEntrega>pedidos.FechaEsperada)

pedidos .CodigoPedido, pedidos.CodigoCliente, clientes.NombreCliente, pedidos.FechaEsperada, pedidos.FechaEntrega FROM pedidos INNER JOIN clientes ON pedidos.CodigoCliente = clientes.CodigoCliente WHERE FechaEntrega > FechaEsperada; SELECT


1º ASIR - GTB

Curso 2013/14

Mostrar el número de pedido (pedidos. CodigoPedido), código de cliente (pedidos.CodigoCliente), nombre de cliente (clientes.NombreCliente), estado del pedido (pedidos.Estado), fecha esperada (pedidos.FechaEsperada) y fecha de entrega (pedidos. FechaEntrega) de los pedidos cuya fecha de entrega ha sido al menos tres días antes de la

fecha esperada. (pista: Usar la función addDate de MySQL) FechaEntrega anterior a FechaEsperada implica: pedidos.FechaEntrega < pedidos.FechaEsperada 3 días antes de la FechaEsperada es restar 3 días a la FechaEsperada: addDate(pedidos.FechaEsperada , -3) Al menos 3 días antes <=: pedidos.FechaEntrega<=addDate(pedidos.FechaEsperada,-3)

SELECT pedidos.CodigoPedido,

pedidos.CodigoCliente, clientes.NombreCliente, pedidos.Estado, pedidos.FechaEsperada, pedidos.FechaEntrega FROM pedidos INNER JOIN clientes ON pedidos.CodigoCliente = clientes.CodigoCliente WHERE pedidos.FechaEntrega<=addDate(pedidos.FechaEsperada,-3);


1º ASIR - GTB

Curso 2013/14

Mostrar la facturación que ha tenido la empresa en toda la historia (TODOS LOS

REGISTROS), indicando la base imponible, el IGIC y el total facturado. NOTA: La base imponible se calcula sumando el coste del producto (detallepedidos. PrecioUnidad) por el número de unidades vendidas (detallepedidos.Cantidad) El IGIC es el 5 % de la base imponible, y el total facturado, la suma de los dos valores calculados anteriormente (Base imponible + IGIC). El número de unidades vendidas de cada producto sólo puede estar en la tabla detallespedidos: Detallepedidos.Cantidad

SELECT SUM(detallepedidos.Cantidad

* detallepedidos.PrecioUnidad) AS

BaseImponible, SUM((((detallepedidos.Cantidad * detallepedidos.PrecioUnidad)*5)/100)) AS TotalIVA, SUM((detallepedidos.Cantidad * detallepedidos.PrecioUnidad) + ((( detallepedidos.Cantidad * detallepedidos.PrecioUnidad)*5)/100)) AS TotalVentas FROM detallepedidos; Se podía entender que el coste del producto es el campo: productos.PrecioVenta o también productos.PrecioProveedor Ambos eran válidos aunque la consulta sale un poco más compleja.

Clases Repaso 13_14

Recommend Documents