[estas son las tablas que usamos]
UNION
UNION: (OTRO DIA M PONGO A COMPLETAR TODO)
EJERCICIOS: Se quiere saber que vendedores y clientes hay en la empresa; para los casos en que su teléfono y dirección de e-mail sean conocidos. Se deberá visualizar el código, nombre y si se trata de un cliente o de un vendedor. Ordene por la tercer columna y la segunda. selectcod_vend, nom_vend, 'vend' Tipo from vendedores wherenro_telisnotnull and [e-mail] isnotnull union selectcod_clie, nom_clie, 'cliente' from clientes wherenro_telisnotnull and [e-mail] isnotnull orderby 3,2
Se quiere saber que artículos, clientes y vendedores hay en la empresa. Determine Determine los campos a
mostrar y su ordenamiento selectcod_arti 'Código', descripcion 'Nombre', 'Articulo'Tipo fromarticulos union selectcod_clie, nom_clie, 'Cliente' from clientes union selectcod_vend, nom_vend, 'Vendedor' from vendedores orderby 3
Se quiere saber que artículos hay en la empresa y cuáles han sido vendidos. Determine Ud. las columnas a mostrar. selectcod_arti Código, descripcion Articulo, 'En Stock' Tipo fromarticulos union selectD.cod_arti, A.descripcion, 'Vendido' from detalle D, Articulos A whereD.cod_arti = A.cod_arti orderby 3
Se quiere saber las direcciones tanto de clientes como de vendedores. Para el caso de los vendedores, códigos entre 3 y 12. En ambos casos la direcciones deberán ser conocidas. Rotule como NOMBRE, DIRECCION, INTEGRANTE (en donde indicará si es cliente o vendedor). Ordenado por la primera columna y la última.
selectnom_clie Nombre, direccionDireccion, 'Cliente' Integrante from clientes wheredireccionisnotnull union selectnom_vend, direccion, 'Vendedor' from vendedores wheredireccionisnotnull and cod_vendbetween 3 and 12 orderby 1,3
Se quiere saber que clientes hay en la empresa y quienes han comprado entre el 11/12/2002 y el 27/02/2003. Muestre el código, sin duplicarlos.
selectcod_clieCodigo from clientes union selectcod_clie from facturas where fecha between 2002-11-12 and 2003-27-02
Idem al ejercicio anterior, sólo que además del código, identifique de donde obtiene la información.
selectcod_clie Código, 'Clientes' Desde from clientes unionall selectcod_clie, 'Facturas' from facturas F where fecha between '2002-11-12' and '2003-27-02'
Se quiere saber que clientes hay en la empresa y quiénes han comprado; para el primer caso para nombres que empiecen con letras que van de la c a la l y para el segundo para facturas que oscilen entre 10 y 23. Muestre el código (no elimine los qu e se repiten). selectcod_clie Código, 'Pasivo' Tipo from clientes wherenom_clielike '[c-l]%' unionall selectcod_clie, 'Activo' from facturas F whereF.nro_factubetween 10 and 23
-------------------------------------------------------------------------------------
INNER JOIN
El INNER JOIN: Es otro tipo de composición de tablas. En lugar de hacer el producto cartesiano completo, para cada una de las tablas busca directamente e n la otra tabla las filas que cumplen la condición.
LEFT OUTER JOIN: Toma todos los registros de la tabla izquier da, aunque no tengan ningún registro coincidente (clave), en la tabla de la derecha.
RIGHT OVER JOIN: Realiza la selección de todos los reg istros de la tabla de la derecha, au nque NO tenga ningún registros de la tabla de izquierda.
EJERCICIOS:
Liste factura, fecha, vendedor y cliente para las ventas del año 2006 y 2007. selectf.nro_factu, f.fecha,v.nom_vend, c.nom_clie from facturas f innerjoin vendedores v onf.cod_vend=v.cod_vend innerjoin clientes c onf.cod_clie=c.cod_clie whereyear (f.fecha) in (2006, 2007)
Liste código de vendedor, nombre, fecha y factura; para las ventas en lo que va del año. selectv.cod_vend, v.nom_vend, f.fecha, f.nro_factu from facturas f innerjoin vendedores v onf.cod_vend=v.cod_vend whereyear(f.fecha)=2007
Liste código de vendedor, nombre, fecha y factura; para las ventas en lo que va del año. selectv.nom_vend, v.cod_vend, f.fecha, f.nro_factu from facturas f rightjoin vendedores v onf.cod_vend=v.cod_vend whereyear(f.fecha)=2007 orf.fe chaisnullorf.nro_factuisnull
Liste descripción, cantidad e importe; aun para aquellos artículos que no registran ventas.
selecta.descripcion, d.cant, d.pre_unit, d.pre_unit*d.cant as 'total' from detalle d full joinarticulos a ond.cod_arti=a.cod_arti full join facturas f ond.nro_factu=f.nro_factu orderbya.descripcion from nombre de talbajoin otra tableon igualamos campos para listar todas las facturas, y los clientes a los que he facturado para casos en que l as facturas no tengan campo cliente, . leftjoin es una unión a la izquierda muerto todos los campos de la izquierda y solo los coicidentes con la derecha. Con el reightMuesta todos los campos de la derecha y solo los coicidentes con la derecha Con el full join muestra todos los campos
Liste factura, fecha, vendedor, cliente, articulo, cantidad e importe; para las ventas de febrero y marzo de los años 2006 y 2007 y siempre que el articulo empiece con letras que van de la a a la m. Ordene por fecha, cliente y articulo. selectf.nro_factu, f.fecha, v.nom_vend, c.nom_clie, a.descripcion, d.cant, d.pre_unit, d.cant*d.pre_unit from facturas f full join vendedores v onf.cod_vend=v.cod_vend innerjoin detalle d onf.nro_factu=d.nro_factu innerjoinarticulos a ona.cod_arti=d.cod_arti innerjoin clientes c onf.cod_clie=c.cod_clie wheremonth(f.fecha)in (2, 3) and year(f.fecha) in (2006, 2007)
Liste código de cliente,nombre, fecha y factura para las ventas del año 2007. Muestre los clientes hayan comprado o no en ese año. selectc.cod_clie,c.nom_clie, f.fecha, f.nro_factu from facturas f full join clientes c onf.cod_clie=c.cod_clie whereyear (f.fecha)=2007
Se quiere saber los artículos que compro el cliente 7 en lo que va del año. Liste articulo,
observaciones e importe. selecta.descripcion, c.nom_clie from facturas f innerjoin clientes c onf.cod_clie=c.cod_clie innerjoin detalle d onf.nro_factu=f.nro_factu innerjoinarticulos a ond.cod_arti=a.cod_arti wherec.cod_clie=7 and year(f.fecha)=2007
Se quiere saber los artículos que compraron los clientes que empiezan con p. Liste cliente, articulo, cantidad e importe. Ordene por cliente y articulo, este en forma descendente. Rotule como CLIENTE, ARTICULO, CANTIDAD, IMPORTE. select c.nom_clie as 'cliente', a.descripcion as 'articulo' , d.cant as 'cantidad', d.pre_unit as 'precio', d.cant*d.pre_unit as 'total' from facturas f inner join detalle d on f.nro_factu=d.nro_factu inner join articulos a on d.cod_arti=a.cod_arti inner join clientes c on f.cod_clie=c.cod_clie where c.nom_clie like '[p]%' order by c.nom_clie desc, a.descripcion desc
----------------------------------------------------------------------------
SUB-CONSULTAS
Subconsultas: Es una consulta que aparece dentro de la cláusula WHERE ó HAVING de otra sentencia SQL. Subconsultas en la cláusula WHERE
Test De Comparación: (= ,< , > ) Compara el valor de una expresión con un único valor producido por una subconsulta
Test de Pertenencia A Conjunto (IN): Compara un valor de datos con una columna de valores producida por una subconsulta. NOT IN: esta expresión significa que no está incluida en la lista de valores generada por la subconsulta.
Test de Existencia (EXISTS): Comprueba si una subconsulta produce alguna fila de resultados. Este test sólo se utiliza en subconsultas. NOT EXISTS: esta expresión invierte la lógica.
Test de Cuantificados: ANY y ALL: Ambos tests comparan un valor de datos con la columna de valores producidos por una subconsulta.
EJERCICIOS:
Se quiere saber que artículos no fueron vendidos el año pasado y que el precio unitario del artículo oscile entre 2 y 6. Muestre el nombre del artículo, observaciones y precio unitario. selectcod_arti, descripcion, observaciones, pre_unit fromarticulos wherepre_unitbetween 2 and 6 and cod_artinot in (sel ectdistinctcod_arti from facturas f, detalle d whered.nro_factu=f.nro_factu and year(fecha)=2008)
Se quiere saber que clientes vinieron más de 5 veces el año pasado. Muestre el nombre del cliente, número de factura y fecha. Rotule como CLIENTE, FACTURA, FECHA VENTA. Ordene por nombre de cliente y fecha selectc.cod_clie as COD_CLIENTe, c.nom_clie as C LIENTES ,f.nro_factu as FACTURAS, f.fecha as FECHA from clientes c, facturas f wherec.cod_clie=f.cod_clie and year (fecha)=2008 and 5<(selectcount (*) from facturas f1 where f1.cod_clie=c.cod_clie and year (fecha)=2008 ) orderbyc.nom_clie, f.fechaasc
Se quiere saber que clientes vinieron entre el 12/12/2004 y el 13/7/2007. Muestre el nombre del cliente y su dirección de mail. Ordene por nombre de cliente. selectc.cod_clie, c.nom_clie, [e-mail] from clientes c wherecod_clie in (selectcod_cliefrom facturas where fecha between '12/12/2004' and '13/7/2007')
Liste número de factura, fecha y cliente para los casos en que todas las veces que vino a comprar haya sido en el mes de febrero. Ordene por cliente y f echa. selectdistinctc.cod_clie, c.nom_clie, [e-mail] from clientes c, facturas f wherec.cod_clie=f.cod_clie and fecha between '12/12/2004' and '13/7/2007'
Muestre el número de factura y la fecha de venta para los casos en que por año se hayan hecho menos de 9 ventas. Rotule como FACTURA, FECHA VENTA. selectf.nro_factu as FACTURA , f.fecha as FECHA_VENTA from facturas f where 9 > ( selectcount(*) from facturas f1 whereyear (f.fecha) = year (f1.fecha) )
Muestre el número de factura, la fecha de venta, el artículo y el importe para los casos en que para esa factura su importe total sea superior o igual a 150. selectf.nro_factu , f.fecha , a.descripcion , d.pre_unit*d.cant from facturas f , articulos a , de talle d wherea.cod_arti = d.cod_arti and f.nro_factu = d.nro_factu and 150 < = ( select sum (d1.pre_unit*d1.cant) from detalle d1 where d1.nro_factu = d.nro_factu)
Se quiere saber que vendedores nunca atendieron a estos clientes:6, 1. Muestre solamente el nombre del vendedor. Rotule como VENDEDOR.
select * from vendedores v wherev.cod_vendnot in ( selectf.cod_vend from facturas f wherecod_clie in (1,6) )
Se quiere saber de que artículos superó el promedio de ventas de 80. Muestre el nombre del artículo y sus observaciones. Rotule como ARTICULO, OBSERVACIONES. selectdescripcion as ARTICULOS , observaciones as OBSERVACIONES fromarticulos a where 80 < ( selectavg (pre_unit*cant) from detalle d wherea.cod_arti = d.cod_arti)
Que artículos nunca se vendieron?. Tenga además en cuenta que su nombre comience con letras que van de la d a la p. Muestre solamente la descripción de artículo. selectdescripcion fromarticulos a wherecod_artinot in ( selectdistinctd.cod_arti from detalle d) and a.descripcionlike '[d-p]%' orderbydescripcion´ -------------------------------------------------
SUB-CONSULTAS CON HAVING
EJERCICIOS:
Se quiere saber la fecha de la primer venta y el importe de ventas por vendedor, para los casos en que su promedio de ventas sea superior al importe promedio global. Rotule como
VENDEDOR, TOTAL VENTAS. selectnom_vend, min(fecha), sum(pre_unit*cant), avg(pre_unit*cant) from facturas f, detalle d, vendedores v wheref.nro_factu=d.nro_factu and v.cod_vend=f.cod_vend groupbynom_vend havingavg(pre_unit*cant) > (selectavg(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 wheref.nro_factu= d.nro_factu and c.cod_clie = f.cod_clie and f.nro_factubetween 20 and 35 groupby fecha, nom_clie, d.cod_arti having sum(pre_unit*cant) > (selectavg(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) fromarticulos a, detalle d wherea.cod_arti = d.cod_arti and nro_factunot in (10,2,7,13,22) groupbydescripcion, d.cod_arti havingavg(d.pre_unit*cant) < (selectavg(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 wheref.nro_factu =d.nro_factu groupby fecha having sum(cant) > (selectavg(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 wheref.nro_factu = d.nro_factu and a.cod_arti = d.cod_arti and cantbetween 5 and 20 groupby fecha, descripcion, d.cod_arti having sum(d.pre_unit*cant) > (selectavg(d1.pre_unit*cant) from detalle d1 wher e 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. selectcount (cod_clie) as cantidad Fromdbo.clientes
Se quiere saber la cantidad de artículos que hay en la empresa.
selectcount (cod_arti) as cantidad fromdbo.articulos
Se quiere saber que cantidad de vendedores hay en la empresa. selectcount (cod_vend) as cantidad fromdbo.vendedores
Se quiere saber la cantidad de ventas que hizo el vendedor de código 3. selectcount (nro_factu) as cantidad fromdbo.facturas wherecod_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 fromdbo.detalle wherenro_factu=10
Se quiere saber cual fue la fecha de la primera y última venta. Rotule como PRIMERVENTA, ULTIMA VENTA. selectmax (fecha) as 'ultima venta' , min (fecha) as 'primera venta' fromdbo.facturas
Se quiere saber cual fue la máxima y la mínima cantidad que se vendió para el artículo 10. selectmax(cant) as 'Maxima venta', min (cant) as 'Minima venta' fromdbo.detalle wherecod_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' fromdbo.detalle d, dbo.vendedores v wherev.nom_vendlike '[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. selectavg(pre_unit*cant) as 'Promedio importe', sum(pre_unit*cant) as 'Importe total', avg(cant) as 'Promedio cantidad vendida', sum(cant) as 'Total ca ntidad vendida' fromdbo.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' fromdbo.detalle d, dbo.clientes c wherec.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' fromdbo.facturas f, dbo.detalle d, dbo.articulos a wherea.descripcionlike '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' fromdbo.detalle d, dbo.facturas f wheref.fechabetween '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. selectcount(f.nro_factu) as 'Cantidad de veces' , max(f.fecha) as 'Ultima visita' fromdbo.facturas f , dbo.clientes c wherec.nom_clielike '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. createview [Detalle_ventas_vendedor] as ( selectf.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 I MPORTE from facturas f, vendedores v, articulos a, detalle d whereyear (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. createview [subtotales_ventas_vendedor] as ( selectcount (v.cod_vend) as CANTIDAD_VENTAS, v.nom_vend , sum(d.pre_unit*d.cant) as IMPORTE
from facturas f, detalle d, vendedores v wheref.cod_vend = v.cod_vend and f.nro_factu = d.nro_factu groupbyv.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 ( selectcount (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 wheref.cod_vend = v.cod_vend and f.nro_factu = d.nro_factu and year (fecha) = 2009 groupbyv.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 <= 20
Llame a la vista creada en el punto 1.b filtrando para el vendedor Miranda. select * from [subtotales_ventas_vendedor] wherenom_vendlike 'Miranda%'
Llama a la vista creada en el punto 1.b filtrando para promedios superiores a 100.
select * from [subtotales_ventas_vendedor] where PROMEDIO_VENDIDO > 40
Elimine las vistas creadas en el punto 1 (no se olvide de colocar el nombre como corresponde) dropview [Detalle_ventas_vendedor] dropview [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. createproceduredetalle_venta @fecha1 as smalldatetime, @fecha2 as smalldatetime as selectf.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 wheref.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 executedetalle_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. createprocedureCantidadArt_Clie @opcion1 int as begin if (@opcion1 = 1) selectcount (*) fromArticulos as Articulos else selectcount (*) from Clientes as Clientes end executeCantidadArt_Clie '1'
INS_Vendedor: Cree un SP que le permita insertar registros en la tabla vendedores. createprocedureINS_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 insertinto 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 executeINS_Vendedores @a output, 'Pedro Perez', 'Lavalleja 250', 42058 89, null, '20/03/2001'
UPD_Vendedor: cree un SP que le permita modificar un vendedor cargado. createprocedureUPD_Vendedor @cod_vendint, @nom_vendnvarchar (50), @dirnvarchar (50), @nro_telint,
@correo nvarchar (50), @fec_nacsmalldatetime as update vendedores set nom_vend = @nom_vend, direccion = @dir, nro_tel = @nro_tel, [e-mail]= @correo, fec_nac = @fec_nac wherecod_vend = @cod_vend executeUPD_Vendedor 12, 'Pedro Perez', 'Lavalleja 250', 4205889, null, '20/03/2001'
DEL_Vendedor: cree un SP que le permita eliminar un vendedor ingresado. createprocedureDEL_Vendedores @cod_vend as int as deletefrom vendedores wherecod_vend = @cod_vend execDEL_Vendedores '13'
