• Author / Uploaded
• Jean Carlos Obada Delgado

• Categories
• SQL
• Tabla (Base de datos)
• Diseño de software
• Modelado científico
• Modelo de datos

Citation preview

Ejercicios tema 2. Las consultas simples Antes de empezar deberías crear la base de datos datos.mdb con las tablas descritas en el tema 1 clic aquí para verlas. Nota: Debes crear una consulta por cada ejercicio, no se pueden escribir varias sentencias SQL en una misma consulta. Si quieres puedes guardar cada consulta con un nombre que permita identificarla por ejemplo: consulta_2_1 siendo 2 el número del tema y 1 el número del ejercicio dentro del tema. Si la consulta contiene errores sintácticos no se podrá guardar. Ahora puedes empezar a redactar las sentencias SQL para obtener lo que se pide en cada ejercicio. La lista de selección 1 Obtener una lista de todos los productos indicando para cada uno su idfab, idproducto, descripción, precio y precio con I.V.A. incluido (es el precio anterior aumentado en un 16%). 2 De cada pedido queremos saber su número de pedido, fab, producto, cantidad, precio unitario e importe. 3 Listar de cada empleado su nombre, nº de días que lleva trabajando en la empresa y su año de nacimiento (suponiendo que este año ya ha cumplido años). Ordenación de filas. 4 Obtener la lista de los clientes agrupados por código de representante asignado, visualizar todas las columnas de la tabla. 5 Obtener las oficinas ordenadas por orden alfabético de región y dentro de cada región por ciudad, si hay más de una oficina en la misma ciudad, aparecerá primero la que tenga el número de oficina mayor. 6 Obtener los pedidos ordenados por fecha de pedido. Selección de filas. 7 Listar las cuatro líneas de pedido más caras (las de mayor importe).

8 Obtener las mismas columnas que en el ejercicio 2 pero sacando unicamente las 5 líneas de pedido de menor precio unitario. 9 Listar toda la información de los pedidos de marzo. 10 Listar los números de los empleados que tienen una oficina asignada. 11 Listar los números de las oficinas que no tienen director. 12 Listar los datos de las oficinas de las regiones del norte y del este (tienen que aparecer primero las del norte y después las del este). 13 Listar los empleados de nombre Julia. 14 Listar los productos cuyo idproducto acabe en x.

Solución ejercicios tema 2. Las consultas simples Ejercicio 1 SELECT idfab,idproducto,descripcion,precio, (precio * 1.16) AS iva_incluido FROM productos Los paréntesis son opcionales, también se puede poner como fórmula de cálculo: precio + precio * 16 /100. Ejercicio 2 SELECT numpedido, fab, producto, cant, importe / cant AS precio_unitario, importe FROM pedidos Ejercicio 3 SELECT nombre, date() - contrato AS dias_trabajados, year(date()) - edad AS año_nacimiento FROM empleados Aquí hemos utilizado la función date() que devuelve el día actual y hemos utilizado la diferencia de fechas para saber cuántos días han transcurrido entre las dos fechas. Para saber el año de nacimiento restamos al año actual la edad del empleado. Para obtener el año actual aplicamos la función year() (que devuelve el año de una fecha) sobre la fecha actual (date()) Ejercicio 4

SELECT * FROM clientes ORDER BY repclie Ejercicio 5 SELECT * FROM oficinas ORDER BY region, ciudad, oficina DESC Ejercicio 6 SELECT * FROM pedidos ORDER BY fechapedido Ejercicio 7 SELECT TOP 4 * FROM pedidos ORDER importe DESC Para obtener las más caras tenemos que ordenar por importe y en orden descendente para que aparezcan las más caras primero. Además como sólo queremos las cuatro primeras utilizamos la cláusula TOP 4. Ejercicio 8 SELECT TOP 5 numpedido, fab, producto, cant, importe / cant AS precio_unitario, importe FROM pedidos ORDER BY 5 Ordenamos los pedidos por precio unitario utilizando el nº de columna, el precio unitario es la quinta columna dentro de la lista de selección. En este caso la ordenación debe ser ascendente. Ejercicio 9 SELECT * FROM pedidos WHERE MONTH(fechapedido) = 3 MONTH(fecha) devuelve el número de mes de la fecha. Ejercicio 10

SELECT numemp FROM empleados WHERE oficina IS NOT NULL Los empleados que tienen asignada una oficina son los que tienen un valor en el campo oficina. Ejercicio 11 SELECT oficina FROM oficinas WHERE dir IS NULL El campo dir es el que nos dice quien es el director de la oficina. Ejercicio 12 SELECT * FROM oficinas WHERE region IN ('norte','este') ORDER BY region DESC Los valores se ponen entre comillas simples o dobles ya que son valores alfanuméricos. También se puede poner WHERE region = 'norte' OR region = 'este'. Ordenamos desc para que primero aparezcan las del norte. Ejercicio 13 SELECT * FROM empleados WHERE nombre LIKE 'Julia *' Los empleados cuyo nombre empiece por Julia, observar que antes del * hay un espacio en blanco para forzar a que el siguiente carácter después de la a sea un blanco y no coja por ejemplo Julian. Ejercicio 14 SELECT * FROM productos WHERE idproducto LIKE '*x' Ejercicios tema 3. Las consultas multitabla 1 Listar las oficinas del este indicando para cada una de ellas su número, ciudad, números y nombres de sus empleados. Hacer una versión en la que aparecen sólo

las que tienen empleados, y hacer otra en las que aparezcan las oficinas del este que no tienen empleados. 2 Listar los pedidos mostrando su número, importe, nombre del cliente, y el límite de crédito del cliente correspondiente (todos los pedidos tienen cliente y representante). 3 Listar los datos de cada uno de los empleados, la ciudad y región en donde trabaja. 4 Listar las oficinas con objetivo superior a 600.000 pts indicando para cada una de ellas el nombre de su director. 5 Listar los pedidos superiores a 25.000 pts, incluyendo el nombre del empleado que tomó el pedido y el nombre del cliente que lo solicitó. 6 Hallar los empleados que realizaron su primer pedido el mismo día en que fueron contratados. 7 Listar los empleados con una cuota superior a la de su jefe; para cada empleado sacar sus datos y el número, nombre y cuota de su jefe. 8 Listar los códigos de los empleados que tienen una línea de pedido superior a 10.000 ptas o que tengan una cuota inferior a 10.000 pts.

Solución ejercicios tema 3. Las consultas multitabla Ejercicio 1 SELECT oficinas.oficina, ciudad, numemp, nombre FROM oficinas INNER JOIN empleados ON oficinas.oficina = empleados.oficina WHERE region = 'este'

Como la columna de emparejamiento oficinas.oficina es clave principal en la tabla oficinas, es mejor utilizar el JOIN que un producto cartesiano. Emparejamos las dos tablas por el campo oficina. Las oficinas que no tengan empleados no salen (es un INNER). Como queremos sólo las oficinas del este añadimos la cláusula WHERE con la condicion. El valor este debe ir entre comillas (es un valor alfanumérico). Observar que en la lista de selección la columna oficina está cualificada (su nombre está precedido del nombre de la tabla), es necesario cualificarla porque en las dos tablas existe una columna llamada oficina y el sistema no sabría cuál de las dos escoger.

SELECT oficinas.oficina, ciudad, numemp, nombre FROM oficinas LEFT JOIN empleados ON oficinas.oficina = empleados.oficina WHERE region = 'este'

Si queremos que también aparezcan las oficinas que no tienen empleados cambiamos INNER por LEFT (queremos todas las oficinas y la tabla oficinas está a la izquierda de la palabra JOIN). Ojo, si en la lista de selección ponemos empleados.oficina en vez de oficinas.oficina, en las filas de oficinas que no tienen empleados el número de oficina aparece nulo. SELECT oficinas.oficina, ciudad, numemp, nombre FROM empleados RIGHT JOIN oficinas ON oficinas.oficina = empleados.oficina WHERE region = 'este'

Esta SELECT es equivalente a la anterior pero hemos cambiado LEFT por RIGHT porque ahora la tabla oficinas está a la derecha de la palabra JOIN. Ejercicio 2 SELECT numpedido, importe, clientes.nombre AS cliente, limitecredito FROM pedidos INNER JOIN clientes ON pedidos.clie = clientes.numclie

En este ejercicio no pueden haber pedidos sin cliente, y lo que nos interesa son los pedidos, luego tampoco tienen que aparecer los clientes que no tienen pedidos, por lo tanto utilizamos un INNER JOIN. Ejercicio 3 SELECT empleados.*, ciudad, region FROM empleados LEFT JOIN oficinas ON empleados.oficina = oficinas.oficina

Aquí hemos utilizado LEFT JOIN para que también salgan los empleados que no tienen oficina asignada. Como queremos todos los datos del empleado utilizamos empleados.* para acortar. Ejercicio 4 SELECT oficinas.*, nombre AS director FROM empleados RIGHT JOIN oficinas ON empleados.oficina = oficinas.oficina WHERE objetivo > 600000

Nos interesan las oficinas con objetivo superior a 600.000pts. luego nos tenemos que asegurar que salgan todas incluso si no tienen director asignado por eso utilizamos RIGHT JOIN. En los valores numéricos no utilizar el punto para separar los miles (lo consideraría coma decimal y entendería 600 en vez de 600000). Ejercicio 5

SELECT numpedido, importe, empleados.nombre AS representante, clientes.nombre AS cliente FROM (pedidos INNER JOIN clientes ON pedidos.clie = clientes.numclie) INNER JOIN empleados ON pedidos.rep = empleados.numemp WHERE importe > 25000

En este ejercicio no pueden haber pedidos sin representante ni cliente, y lo que nos interesa son los pedidos, luego tampoco tienen que aparecer los representantes que no tienen pedidos ni los clientes que no tienen pedidos, por lo tanto utilizamos un INNER JOIN. Primero añadimos a cada línea de pedido los datos del cliente corespondiente (con el primer INNER) y a cada fila resultante añadimos los datos del representante correspondiente. Nota: el representante que nos interesa es el que ha realizado el pedido y ese dato lo tenemos en el campo rep de pedidos por eso la condición de emparejamiento es pedidos.rep = empleados.rep. Si hubiesemos querido el nombre del representante asignado al cliente, la condición hubiera sido clientes.repclie = empleados.numemp. Ejercicio 6 SELECT empleados.* FROM empleados INNER JOIN pedidos ON pedidos.rep = empleados.numemp WHERE fechapedido = contrato

Los representantes que buscamos tienen un pedido con la misma fecha que la de su contrato, tenemos que añadir a los pedidos los datos del representante correspondiente para poder comparar los dos campos. Ejercicio 7 SELECT empleados.*, jefes.numemp AS num_jefe, jefes.nombre AS nombre_jefe, jefes.cuota AS cuota_jefe FROM empleados INNER JOIN empleados jefes ON empleados.jefe = jefes.numemp WHERE empleados.cuota > jefes.cuota

En una misma línea necesito los datos del empleado y los datos de su jefe, luego tengo que combinar empleados con empleados. No interesan los empleados que no tienen jefe luego utilizo INNER. El alias de tabla es obligatorio ya que combino empleados con la misma. Ejercicio 8

SELECT numemp FROM empleados LEFT JOIN pedidos ON pedidos.rep = empleados.numemp WHERE importe > 10000 OR cuota < 10000

Una posible solución es combinar pedidos con empleados para poder seleccionar las líneas de importe > 10000 o cuota < 10000. Hay que utilizar LEFT para que puedan aparecer empleados con cuota < 10000 que no tengan pedidos. SELECT rep FROM pedidos WHERE importe > 10000 UNION SELECT numemp FROM empleados WHERE cuota < 10000

Esta es otra solución, obtener por una parte los códigos de los empleados con una línea de pedido > 10000, por otra parte los códigos de los empleados con cuota < 10000 y finalmente unir las dos listas con una UNION. Ejercicios tema 4. Las consultas de resumen 1 ¿Cuál es la cuota media y las ventas medias de todos los empleados? 2 Hallar el importe medio de pedidos, el importe total de pedidos y el precio medio de venta (el precio de venta es el precio unitario en cada pedido). 3 Hallar el precio medio de los productos del fabricante ACI. 4 ¿Cuál es el importe total de los pedidos realizados por el empleado Vicente Pantalla? 5 Hallar en qué fecha se realizó el primer pedido (suponiendo que en la tabla de pedidos tenemos todos los pedidos realizados hasta la fecha). 6 Hallar cuántos pedidos hay de más de 25000 ptas. 7 Listar cuántos empleados están asignados a cada oficina, indicar el número de oficina y cuántos hay asignados. 8 Para cada empleado, obtener su número, nombre, e importe vendido por ese empleado a cada cliente indicando el número de cliente. 9 Para cada empleado cuyos pedidos suman más de 30.000 ptas, hallar su importe medio de pedidos. En el resultado indicar el número de empleado y su importe medio de pedidos.

10 Listar de cada producto, su descripción, precio y cantidad total pedida, incluyendo sólo los productos cuya cantidad total pedida sea superior al 75% del stock; y ordenado por cantidad total pedida. 11 Saber cuántas oficinas tienen empleados con ventas superiores a su cuota, no queremos saber cuales sino cuántas hay. Solución ejercicios tema 4. Las consultas de resumen Ejercicio 1 SELECT AVG(cuota) AS cuota_media, AVG(ventas) AS ventas_media FROM empleados

Sale una única fila con el resultado deseado. Siempre que se utilicen expresiones o funciones en la lista de selección, queda mejor utilizar un alias de columna para que ese aparezca en el encabezado del resultado. Ejercicio 2 SELECT AVG(importe) AS importe_medio, SUM(importe) AS importe_total, AVG(importe/cant) AS precio_venta_medio FROM pedidos

El precio medio de venta es la media aritmética de los precios unitarios de cada pedido. El precio unitario se calcula dividiendo el importe del pedido por la cantidad del pedido: importe/cant, por lo que ponemos AVG(importe/cant). Ejercicio 3 SELECT AVG(precio) AS p_medio_ACI FROM productos WHERE idfab = 'ACI'

Ahora no nos interesan todos los productos sino unicamente los del fabricante ACI, por lo que añadimos la cláusula WHERE para que antes de calcular la media, elimine del origen de datos los registros que no cumplan la condición. Ejercicio 4 SELECT SUM(importe) AS total_pedidos_V_Pantalla FROM empleados INNER JOIN pedidos ON empleados.numemp = pedidos.rep WHERE nombre = 'Vicente Pantalla'

El importe total lo tenemos que sacar de la tabla de pedidos, y además sólo nos interesan los de Vicente Pantalla. Como nos dan el nombre del representante en vez de su número y en el pedido sólo tenemos el número de representante tenemos que añadir a las líneas de cada pedido, los datos del representante correspondiente, por lo que el origen de datos debe ser el que aparece en la FROM.

Ejercicio 5 SELECT MIN(fechapedido) AS primer_pedido FROM pedidos

La fecha del primer pedido es la fecha más antigua de la tabla de pedidos. Ejercicio 6 SELECT COUNT(*) AS cuantos_pedidos_mayores FROM pedidos WHERE importe > 25000

Se podía haber utilizado también COUNT(numpedido) o cualquier nombre de columna que no pueda contener valores nulos, pero COUNT(*) es mejor por ser más rápido (la diferencia se nota con tablas muy voluminosas). Ejercicio 7 SELECT oficina, COUNT(*) AS cuantos_empleados FROM empleados GROUP BY oficina

Con esta solución obtenemos el listado pedido pero no aparecen las oficinas que no tienen empleados asignados ya que sacamos la información de la tabla empleados y aparece una fila con valor nulo en oficina que contiene el número de empleados que no tienen oficina. Si quisieramos listar incluso las que no tengan empleados habría que recurrir a la solución 2 Solución 2 SELECT oficinas.oficina, COUNT(numemp) AS cuantos_empleados FROM empleados RIGHT JOIN oficinas ON empleados.oficina = oficinas.oficina GROUP BY oficinas.oficina

Utilizamos un RIGHT JOIN para que el origen de datos incluya también una fila por cada oficina que no tenga empleados. En el GROUP BY y en la lista de selección hay que indicar el campo oficina de la tabla oficinas, si ponemos el de la tabla empleados, agrupará todas las oficinas que no tienen empleados en una fila (la columna empleados.oficina contiene valor nulo para esas filas). Aquí no podemos utilizar COUNT(*) por que las oficinas sin empleados aparecerían con 1 en la columna cuantos_empleados ya que para esa oficina hay una fila. Ejercicio 8 SELECT numemp, nombre, clie AS cliente, SUM(importe) AS total_vendido FROM empleados INNER JOIN pedidos ON pedidos.rep = empleados.numemp GROUP BY numemp, nombre, clie

Necesitamos la tabla de pedidos para el importe vendido a qué cliente, necesitamos la tabla empleados para el nombre del representante, la de clientes no la necesitamos ya que nos piden el número de cliente y este está en pedidos. La agrupación básica que debemos realizar es por numemp y después por clie, pero como aparece el nombre del empleado en la lista de selección, hay que incluirlo también en el GROUP BY. Después de determinar la agrupación básica que nos hace falta, siempre que se incluye una columna adicional en el GROUP BY hay que comprobar que esa nueva columna no cambia la agrupación básica. Por ejemplo no podríamos añadir al GROUP BY la columna fechapedido ya que se formarían más grupos. Solución 2 SELECT numemp, nombre, clie AS cliente, SUM(importe) AS total_vendido FROM empleados LEFT JOIN pedidos ON pedidos.rep = empleados.numemp GROUP BY numemp, nombre, clie

Si queremos que salgan todos los empleados incluso los que no aparezcan en los pedidos habría que sustituir el INNER por un LEFT. Ejercicio 9 SELECT rep, AVG(importe) AS importe_medio FROM pedidos GROUP BY rep HAVING SUM(importe) > 30000

No queremos todos los empleados, unicamente los que tengan un importe total pedido superior a 30.000, luego tenemos que poner la condición SUM(importe) > 30000. Como esta condición contiene una función de columna (SUM()) se tiene que poner en la cláusula HAVING ya que selecciona filas de la tabla resultante no del origen de datos. Ejercicio 10 SELECT descripcion, precio, SUM(importe) AS total_pedido FROM productos INNER JOIN pedidos ON pedidos.fab = productos.idfab AND pedidos.producto = productos.idproducto GROUP BY idfab, idproducto, descripcion, precio, existencias HAVING SUM(importe) > existencias * 0.75 ORDER BY 3

La agrupación básica es por idfab e idproducto ya que son los dos campos que conjuntamente identifican un producto. Como descripción y precio aparecen en la lista de selección y no modifican la agrupación básica los incluimos en el GROUP BY.

Como existencias aparece en el HAVING y no modifica la agrupación básica lo incluimos también el el GROUP BY. Para calcular el 75% de las existencias multiplicamos existencias por 0,75; observar que en la sentencia SQL hay que utilizar el punto para indicar los decimales. Para indicar la columna de ordenación no podemos utilizar el alias campo, utilizamos el número de orden de la columna dentro de la lista de selección. En este caso la suma de importes es la tercera columna. Ejercicio 11 Consulta: distintas_oficinas SELECT DISTINCT oficina FROM empleados WHERE ventas > cuota Consulta: sumaria11 SELECT COUNT(*) AS cuantas_oficinas FROM distintas_oficinas

Si contamos las oficinas directamente de la tabla empleados nos salen 9 oficinas ya que la función COUNT(nb columna) cuenta los valores no nulos pero los valores repetidos los cuenta tantas veces como se repiten, como tenemos oficinas de se repiten en la columna oficina de la tabla oficinas, esas oficinas son contadas varias veces, hay que contar los valores distintos. En otros SQL la función COUNT puede llevar delante del nombre de la columna la cláusula DISTINCT que indica que sólo se tienen que tener en cuenta valores distintos (no cuenta los repetidos), por ejemplo COUNT(DISTINCT oficina), es una opción muy útil que desgraciadamente no incluye el SQL de Microsoft JET. Para solucionar el problema se resuelve con dos consultas, una con la cual nos quedamos con los valores distintos (en la solución la consulta se llama distintas_oficinas), y la otra que nos cuenta esos valores. Ejercicios tema 5. Las subconsultas Los ejercicios que te proponemos a continuación se pueden resolver de varias maneras, intenta resolverlos utilizando subconsultas ya que de eso trata el tema, además un mismo ejercicio lo puedes intentar resolver de diferentes maneras utilizandos distintos tipos de condiciones, así un ejercicio se puede convertir en dos o tres ejercicios. 1 Listar los nombres de los clientes que tienen asignado el representante Alvaro Jaumes (suponiendo que no pueden haber representantes con el mismo nombre). 2 Listar los vendedores (numemp, nombre, y nº de oficina) que trabajan en oficinas "buenas" (las que tienen ventas superiores a su objetivo).

3 Listar los vendedores que no trabajan en oficinas dirigidas por el empleado 108. 4 Listar los productos (idfab, idproducto y descripción) para los cuales no se ha recibido ningún pedido de 25000 o más. 5 Listar los clientes asignados a Ana Bustamante que no han remitido un pedido superior a 3000 pts. 6 Listar las oficinas en donde haya un vendedor cuyas ventas representen más del 55% del objetivo de su oficina. 7 Listar las oficinas en donde todos los vendedores tienen ventas que superan al 50% del objetivo de la oficina. 8 Listar las oficinas que tengan un objetivo mayor que la suma de las cuotas de sus vendedores. Solución ejercicios tema 5. Las subconsultas Ejercicio 1 SELECT nombre FROM clientes WHERE repclie = (SELECT numemp FROM empleados WHERE nombre = 'Alvaro Jaumes' );

Hemos supuesto que no pueden haber dos empleados con el mismo nombre, de lo contrario habría que añadir ANY antes de la subconsulta. Ejercicio 2 Solución 1 SELECT numemp, nombre, oficina FROM empleados WHERE oficina IN ( SELECT oficina FROM oficinas WHERE ventas > objetivo );

Con esta solución buscamos que la oficina del empleado esté en la lista de oficinas que tienen ventas superiores a su objetivo.

Solución 2 SELECT numemp, nombre, oficina FROM empleados WHERE EXISTS ( SELECT * FROM oficinas WHERE empleados.oficina = oficinas.oficina AND ventas > objetivo );

Con esta solución buscamos que exista una oficina igual al del empleado y que tenga ventas superiores a su objetivo. El resultado será el mismo que con la solución 1.

Solución 3 SELECT numemp, nombre, oficina FROM empleados WHERE oficina = ANY ( SELECT oficina FROM oficinas WHERE ventas > objetivo );

Con esta otra comparamos la oficina del empleado con cada una de las oficinas que tengan ventas superiores a su objetivo, si la oficina del empleado es igual a alguna de esas oficinas aparece el empleado en el resultado. El resultado será el mismo que con la solución 1. Ejercicio 3 Solución 1 SELECT numemp, nombre, oficina FROM empleados WHERE NOT EXISTS ( SELECT * FROM oficinas WHERE empleados.oficina = oficinas.oficina AND dir = 108);

Obtenemos los empleados tales que no exista una oficina igual a la suya que además esté dirigida por el empleado 108, con esta solución sí aparecen los empleados que no tienen oficina. SELECT numemp, nombre, oficina FROM empleados WHERE oficina NOT IN ( SELECT oficina FROM oficinas WHERE dir = 108);

Con la subconsulta obtenemos la lista de las oficinas dirigidas por el empleado 108. Al final se obtienen los empleados cuya oficina no esté en esa lista. Pero no salen los empleados que no tienen oficina asignada ya que su campo oficina es nulo por lo que el resultado de la comparación es nulo, no es verdadero y no se seleccionan. El problema se puede arreglar indicando que también se tienen que seleccionar los empleados con oficina nula: Solución 2 SELECT numemp, nombre, oficina FROM empleados WHERE ( oficina NOT IN ( SELECT oficina FROM oficinas WHERE dir = 108) ) OR ( oficina IS NULL);

Con la subconsulta obtenemos la lista de las oficinas dirigidas por el empleado 108. Al final se obtienen los empleados cuya oficina no esté en esa lista. Pero no salen los empleados que no tienen oficina asignada ya que su campo oficina es nulo por lo que el resultado de la comparación es nulo, no es verdadero y no se seleccionan. SELECT numemp, nombre, oficina FROM empleados WHERE oficina ALL ( SELECT oficina FROM oficinas WHERE dir = 108);

Con esta solución tenemos el mismo problema que con NOT IN , cuando la oficina del empleado es nula todos los resultados de las comparaciones individuales son nulos por los que el test ALL da nulo y no se seleccionan los empleados con oficina nula. Ejercicio 4 SELECT idfab, idproducto, descripcion FROM productos WHERE NOT EXISTS (SELECT * FROM pedidos WHERE fab = idfab AND producto = idproducto AND importe >= 25000);

En este caso es más cómodo utilizar NOT EXISTS ya que hay que preguntar por el idfab e idproducto a la vez. Ejercicio 5 SELECT numclie, nombre FROM clientes WHERE repclie IN ( SELECT numemp FROM empleados WHERE nombre = 'Ana Bustamante' ) AND numclie NOT IN ( SELECT clie FROM pedidos WHERE importe > 3000);

Ejercicio 6 SELECT * FROM oficinas WHERE EXISTS ( SELECT * FROM empleados WHERE ventas > objetivo * 0.55);

En una subconsulta todos los campos no cualificados se presuponen de la tabla origen de la subconsulta y sólo si no existe ninguna columna con ese nombre, la considera como referencia externa, por eso no es necesario cualificar ventas porque interpreta que es el campo ventas de la tabla empleados. Ejercicio 7 SELECT * FROM oficinas WHERE (objetivo * 0.5) (select avg(pre_unit*cant) from detalle)

Se quiere saber el importe promedio y el importe de ventas por fecha y cliente, para los casos en que los números de factura que oscilen entre 20 y 35 y que ese importe de ventas sea superior o igual al promedio global. Rotule como FECHA VENTA, CLIENTE, IMPORTE, PROMEDIO.

SELECT fecha, nom_clie, sum(pre_unit*cant), avg(pre_unit*cant) from facturas f, clientes c, detalle d where f.nro_factu= d.nro_factu and c.cod_clie = f.cod_clie and f.nro_factu between 20 and 35 group by fecha, nom_clie, d.cod_arti having sum(pre_unit*cant) > (select avg(pre_unit*cant) from detalle )

Se quiere saber el importe vendido, la cantidad vendida por artículo, para los casos en que los números de factura no sean uno de los siguientes:2, 10, 7, 13, 22 y que ese importe promedio sea inferior al

importe promedio de ese artículo.

SELECT descripcion, sum(d.pre_unit*cant), avg(d.pre_unit*cant) from articulos a, detalle d where a.cod_arti = d.cod_arti and nro_factu not in (10,2,7,13,22) group by descripcion, d.cod_arti having avg(d.pre_unit*cant) < (select avg(d1.pre_unit*cant) from detalle d1 where d1.cod_arti= d.cod_arti)

Se quiere saber la cantidad vendida, el importe vendido y el promedio vendido por fecha, siempre que esa cantidad vendida sea superior al promedio de la cantidad global. Rotule como FECHA VENTA, CANTIDAD, IMPORTE, PROMEDIO. Ordene por fecha en forma descendente.

select fecha, sum(cant), sum(pre_unit*cant), avg(pre_unit*cant) from facturas f, detalle d where f.nro_factu =d.nro_factu group by fecha having sum(cant) > (select avg(cant) from detalle)

Se quiere saber el promedio vendido por fecha y artículo para los casos en que las cantidades vendidas oscilen entre 5 y 20 y que ese importe sea superior al importe promedio de ese artículo. Rotule como FECHA VENTA, ARTICULO, FEC PRIMER VENTA, PROMEDIO.

select fecha, descripcion, avg(d.pre_unit*cant) from facturas f, articulos a, detalle d where f.nro_factu = d.nro_factu and a.cod_arti = d.cod_arti and cant between 5 and 20 group by fecha, descripcion, d.cod_arti having sum(d.pre_unit*cant) > (select avg(d1.pre_unit*cant) from detalle d1 where d1.cod_arti =d.cod_arti)

Se quiere saber el importe vendido por fecha para los casos en que ese promedio vendido sea inferior al importe promedio global. Rotule como FECHA, IMPORTE.

SELECT fecha, sum(pre_unit*cant) from detalle d,facturas f where f.nro_factu = d.nro_factu group by fecha having avg(pre_unit*cant) < (select avg(pre_unit*cant) from detalle)

-------------------------------------------------------------

SUMARIAS

EJERCICIOS:

Se quiere saber la cantidad de clientes que hay en la empresa.

select count (cod_clie) as cantidad From dbo.clientes

Se quiere saber la cantidad de artículos que hay en la empresa.

select count (cod_arti) as cantidad from dbo.articulos

Se quiere saber que cantidad de vendedores hay en la empresa.

select count (cod_vend) as cantidad from dbo.vendedores

Se quiere saber la cantidad de ventas que hizo el vendedor de código 3.

select count (nro_factu) as cantidad from dbo.facturas where cod_vend = 3

Se quiere saber la cantidad vendida, la cantidad de ventas y el importe para la factura 10.

select sum(cant) as 'Cantidad vendida',count (nro_factu) as 'Cantidad de ventas',sum(pre_unit*cant) as Importe from dbo.detalle where nro_factu=10

Se quiere saber cual fue la fecha de la primera y última venta. Rotule como PRIMERVENTA, ULTIMA VENTA.

select max (fecha) as 'ultima venta' , min (fecha) as 'primera venta' from dbo.facturas

Se quiere saber cual fue la máxima y la mínima cantidad que se vendió para el artículo 10.

select max(cant) as 'Maxima venta', min (cant) as 'Minima venta' from dbo.detalle where cod_arti = 10

Se quiere saber la cantidad total vendida, el monto y el importe promedio total; para vendedores cuyo nombres comienzan con letras

que van de la “d” a la “l”.

select sum (d.cant) as 'Cantidad total vendida' , sum (d.pre_unit) as 'Monto', avg (d.pre_unit*d.cant) as 'Importe promedio total' from dbo.detalle d, dbo.vendedores v where v.nom_vend like '[d-l]%'

Se quiere saber el promedio del importe vendido, el total del importe vendido, el promedio de la cantidad vendida y el total de la cantidad vendida.

select avg(pre_unit*cant) as 'Promedio importe', sum(pre_unit*cant) as 'Importe total', avg(cant) as 'Promedio cantidad vendida', sum(cant) as 'Total cantidad vendida' from dbo.detalle

Se quiere saber el total vendido, el promedio vendido y la cantidad total vendida para el cliente de código 3.

select sum(d.cant) as 'Total vendido', avg(d.cant) as 'Promedio vendido' from dbo.detalle d, dbo.clientes c where c.cod_clie = 3

Se quiere saber la fecha de la primera venta, la cantidad total vendida y el monto total vendido para los artículos que empiecen con “c”.

select min(f.fecha) as 'Fecha primer venta', sum(d.cant) as 'Cant total vendida',

sum(d.pre_unit) as 'Monto total' from dbo.facturas f, dbo.detalle d, dbo.articulos a where a.descripcion like 'c%'

Se quiere saber la cantidad total vendida y el monto total vendido para el periodo del 15/06/2005 al 15/03/2007.

select sum(d.cant) as 'Cantidad total ', sum(d.pre_unit*d.cant) as 'Monto total' from dbo.detalle d, dbo.facturas f where f.fecha between '15/06/2005' and '15/03/2007'

Se quiere saber la cantidad de veces y la última vez que vino el cliente de apellido Abarca.

select count(f.nro_factu) as 'Cantidad de veces' , max(f.fecha) as 'Ultima visita' from dbo.facturas f , dbo.clientes c where c.nom_clie like 'Abarca%'

Se quiere saber el importe total vendido y el importe promedio vendido para números de factura que no sean los siguientes: 13, 5, 17, 33, 24.

select sum(d.cant*pre_unit)as 'Importe total' , avg(d.cant*d.pre_unit) 'Importe promedio' from dbo.detalle d, dbo.facturas f where d.nro_factu= f.nro_factu and f.nro_factu not in (13,5,17,33,29)

----------------------------------------------------

VISTAS

EJERCICIOS:

Cree las siguientes vistas:

Detalle_Ventas_Vendedor: Liste la fecha, la factura, el codigo y nombre del vendedor, el articulo, la cantidad e importe, para lo que va del año. Rotule como FECHA, NRO_FACTURA, CODIGO_VENDEDOR, OMBRE_VENDEDOR, ARTICULO, CANTIDAD, IMPORTE.

create view [Detalle_ventas_vendedor] as (

select f.fecha as FECHA, f.nro_factu as NRO_FACTURA, v.cod_vend as CODIGO_VENDEDOR, v.nom_vend as NOMBRE_VENDEDOR, a.descripcion as ARTICULO, d.cant as CANTIDAD, (d.pre_unit*d.cant) as IMPORTE from facturas f, vendedores v, articulos a, detalle d where year (fecha) = 2009)

select * from [Detalle_ventas_vendedor]

Subtotales_Ventas_Vendedor: Se quiere saber el importe vendido y la cantidad de ventas por vendedor. Rotule como VENDEDOR, IMPORTE_VENDIDO, CANTIDAD_VENDIDA.

create view [subtotales_ventas_vendedor] as ( select count (v.cod_vend) as CANTIDAD_VENTAS, v.nom_vend , sum(d.pre_unit*d.cant) as IMPORTE from facturas f, detalle d, vendedores v where f.cod_vend = v.cod_vend and f.nro_factu = d.nro_factu group by v.nom_vend )

select * from [subtotales_ventas_vendedor]

Modifique las vistas según el siguientes detalle: La vista creada en el punto b, agréguele la condición de que solo tome lo del año en curso y que también muestre el promedio vendido y el código del vendedor.

alter view [subtotales_ventas_vendedor] as ( select count (v.cod_vend) as CANTIDAD_VENTAS, v.nom_vend , sum(d.pre_unit*d.cant) as IMPORTE, avg(d.pre_unit*d.cant) as PROMEDIO_VENDIDO, v.cod_vend from facturas f, detalle d, vendedores v where f.cod_vend = v.cod_vend and f.nro_factu = d.nro_factu and year (fecha) = 2009 group by v.nom_vend, v.cod_vend)

select * from [subtotales_ventas_vendedor]

Consulta las vistas según el siguiente detalle: Llame a la vista creada en el punto 1.a pero filtrando por importes inferiores a $20.

select * from [Detalle_ventas_vendedor]

where IMPORTE 40

Elimine las vistas creadas en el punto 1 (no se olvide de colocar el nombre como corresponde)

drop view [Detalle_ventas_vendedor] drop view [subtotales_ventas_vendedor]

----------------------------------------------------

PROCEDIMIENTOS ALMACENADOS

EJERCICIOS:

Cree los siguientes SP: LEGAJO-Detalle_Ventas: liste la fecha, la factura, el vendedor, el cliente, el artículo, cantidad e importe. Este SP recibirá como parámetros de E un rango de fechas. create procedure detalle_venta @fecha1 as smalldatetime, @fecha2 as smalldatetime as select f.fecha, f.nro_factu, v.nom_vend, c.nom_clie, a.descripcion, d.cant, (d.cant*d.pre_unit) from clientes c, facturas f, vendedores v, articulos a, detalle d where f.nro_factu = d.nro_factu and c.cod_clie = f.cod_clie and a.cod_arti = d.cod_arti and v.cod_vend = f.cod_vend and fecha between @fecha1 and @fecha2 execute detalle_venta '20/03/2008','13/11/2009' CantidadArt_Cli : este SP me debe devolver la cantidad de artículos o clientes (según se pida) que existen en la empresa. create procedure CantidadArt_Clie @opcion1 int as begin if (@opcion1 = 1) select count (*) from Articulos as Articulos else select count (*) from Clientes as Clientes end

execute CantidadArt_Clie '1'

INS_Vendedor: Cree un SP que le permita insertar registros en la tabla vendedores. create procedure INS_Vendedores @cod_vend as int output, @nom_vend as nvarchar (50), @dir as nvarchar (50), @nro_tel as int, @e_mail as nvarchar (50), @fec_nac as smalldatetime as begin insert into vendedores (nom_vend, direccion, nro_tel, fec_nac) values (@nom_vend, @dir, @nro_tel, @fec_nac) set @cod_vend = @@identity end declare @a as int execute INS_Vendedores @a output, 'Pedro Perez', 'Lavalleja 250', 4205889, null, '20/03/2001' UPD_Vendedor: cree un SP que le permita modificar un vendedor cargado. create procedure UPD_Vendedor @cod_vend int, @nom_vend nvarchar (50), @dir nvarchar (50), @nro_tel int, @correo nvarchar (50), @fec_nac smalldatetime as update vendedores set nom_vend = @nom_vend, direccion = @dir, nro_tel = @nro_tel, = @correo, fec_nac = @fec_nac where cod_vend = @cod_vend

execute UPD_Vendedor 12, 'Pedro Perez', 'Lavalleja 250', 4205889, null, '20/03/2001'

DEL_Vendedor: cree un SP que le permita eliminar un vendedor ingresado. create procedure DEL_Vendedores @cod_vend as int as delete from vendedores where cod_vend = @cod_vend exec DEL_Vendedores '13'