Download - consultas sql server
-
7/26/2019 consultas sql server
1/53
Ejercicios tema 2. Las consultas simples
Antes de empezar deberas crear la base de datos datos.mdb con las tablasdescritas en el tema 1 clic aqupara verlas.
Nota: Debes crear una consulta por cada ejercicio, no se pueden escribir variassentencias SQL en una misma consulta.
Si quieres puedes uardar cada consulta con un nombre que permita identi!icarlapor ejemplo: consulta"#"1 siendo # el n$mero del tema % 1 el n$mero delejercicio dentro del tema.
Si la consulta contiene errores sint&cticos no se podr& uardar.
A'ora puedes empezar a redactar las sentencias SQL para obtener lo que se pide
en cada ejercicio.
La lista de selecci(n
1)btener una lista de todos los productos indicando para cada uno su id!ab,idproducto, descripci(n, precio % precio con *.+.A. incluido es el precio anterioraumentado en un 1-/.
2De cada pedido queremos saber su n$mero de pedido, !ab, producto, cantidad,precio unitario e importe.
3Listar de cada empleado su nombre, n0 de das que lleva trabajando en laempresa % su ao de nacimiento suponiendo que este ao %a 'a cumplido aos/.
)rdenaci(n de !ilas.
4)btener la lista de los clientes arupados por c(dio de representanteasinado, visualizar todas las columnas de la tabla.
5)btener las o!icinas ordenadas por orden al!ab2tico de rei(n % dentro de cadarei(n por ciudad, si 'a% m&s de una o!icina en la misma ciudad, aparecer&primero la que tena el n$mero de o!icina ma%or.
6)btener los pedidos ordenados por !ec'a de pedido.
Selecci(n de !ilas.
7Listar las cuatro lneas de pedido m&s caras las de ma%or importe/.
-
7/26/2019 consultas sql server
2/53
8)btener las mismas columnas que en el ejercicio # pero sacando unicamentelas 3 lneas de pedido de menor precio unitario.
9Listar toda la in!ormaci(n de los pedidos de marzo.
10Listar los n$meros de los empleados que tienen una o!icina asinada.
11Listar los n$meros de las o!icinas que no tienen director.
12Listar los datos de las o!icinas de las reiones del norte % del este tienen queaparecer primero las del norte % despu2s las del este/.
13Listar los empleados de nombre 4ulia.
14Listar los productos cu%o idproducto acabe en 5.
Solucin ejercicios tema 2. Las consultas simples
6jercicio 1
S6L678 id!ab,idproducto,descripcion,precio, precio 9 1.1-/ AS iva"incluido;)< productos
Los par2ntesis son opcionales, tambi2n se puede poner como !(rmula de c&lculo:precio = precio 9 1- >1??.
6jercicio #
S6L678 numpedido, !ab, producto, cant, importe > cant AS precio"unitario,importe;)< pedidos
6jercicio @
S6L678 nombre, date/ contrato AS dias"trabajados, %eardate// edad ASao"nacimiento
;)< empleadosAqu 'emos utilizado la !unci(n date/ que devuelve el da actual % 'emosutilizado la di!erencia de !ec'as para saber cu&ntos das 'an transcurrido entrelas dos !ec'as. Bara saber el ao de nacimiento restamos al ao actual la edaddel empleado. Bara obtener el ao actual aplicamos la !unci(n %ear/ quedevuelve el ao de una !ec'a/ sobre la !ec'a actual date//
6jercicio C
-
7/26/2019 consultas sql server
3/53
S6L678 9;)< clientes);D6; E repclie
6jercicio 3
S6L678 9;)< o!icinas);D6; E reion, ciudad, o!icina D6S7
6jercicio -
S6L678 9;)< pedidos);D6; E !ec'apedido
6jercicio F
S6L678 8)B C 9;)< pedidos);D6; importe D6S7
Bara obtener las m&s caras tenemos que ordenar por importe % en ordendescendente para que aparezcan las m&s caras primero. Adem&s como s(loqueremos las cuatro primeras utilizamos la cl&usula 8)B C.
6jercicio G
S6L678 8)B 3 numpedido, !ab, producto, cant, importe > cant ASprecio"unitario, importe;)< pedidos);D6; E 3
)rdenamos los pedidos por precio unitario utilizando el n0 de columna, el preciounitario es la quinta columna dentro de la lista de selecci(n. 6n este caso laordenaci(n debe ser ascendente.
6jercicio H
S6L678 9;)< pedidosIJ6;6
-
7/26/2019 consultas sql server
4/53
S6L678 numemp;)< empleadosIJ6;6 o!icina *S N)8 NLL
Los empleados que tienen asinada una o!icina son los que tienen un valor en el
campo o!icina.
6jercicio 11
S6L678 o!icina;)< o!icinasIJ6;6 dir *S NLL
6l campo dir es el que nos dice quien es el director de la o!icina.
6jercicio 1#
S6L678 9;)< o!icinasIJ6;6 reion *N MnorteM,MesteM/);D6; E reion D6S7
Los valores se ponen entre comillas simples o dobles %a que son valoresal!anum2ricos. 8ambi2n se puede poner IJ6;6 reion K MnorteM ); reion K MesteM.)rdenamos desc para que primero aparezcan las del norte.
6jercicio 1@
S6L678 9;)< empleadosIJ6;6 nombre L*6 M4ulia 9M
Los empleados cu%o nombre empiece por 4ulia, observar que antes del 9 'a% unespacio en blanco para !orzar a que el siuiente car&cter despu2s de la a sea unblanco % no coja por ejemplo 4ulian.
6jercicio 1C
S6L678 9;)< productosIJ6;6 idproducto L*6 M95M
Ejercicios tema 3. Las consultas multitabla
1Listar las o!icinas del este indicando para cada una de ellas su n$mero, ciudad,n$meros % nombres de sus empleados. Jacer una versi(n en la que aparecen s(lo
-
7/26/2019 consultas sql server
5/53
las que tienen empleados, % 'acer otra en las que aparezcan las o!icinas del esteque no tienen empleados.
2Listar los pedidos mostrando su n$mero, importe, nombre del cliente, % ellmite de cr2dito del cliente correspondiente todos los pedidos tienen cliente %
representante/.
3Listar los datos de cada uno de los empleados, la ciudad % rei(n en dondetrabaja.
4Listar las o!icinas con objetivo superior a -??.??? pts indicando para cada unade ellas el nombre de su director.
5Listar los pedidos superiores a #3.??? pts, inclu%endo el nombre del empleadoque tom( el pedido % el nombre del cliente que lo solicit(.
6Jallar los empleados que realizaron su primer pedido el mismo da en que!ueron contratados.
7Listar los empleados con una cuota superior a la de su je!eO para cadaempleado sacar sus datos % el n$mero, nombre % cuota de su je!e.
8Listar los c(dios de los empleados que tienen una lnea de pedido superior a1?.??? ptas o que tenan una cuota in!erior a 1?.??? pts.
Solucin ejercicios tema 3. Las consultas multitabla
6jercicio 1
SELE! o"icinas.o"icina# ciu$a$# numemp# nombre%&'( o"icinas )**E& +')* emplea$os '* o"icinas.o"icina , emplea$os.o"icina-E&E re/ion , este
7omo la columna de emparejamiento o!icinas.o!icina es clave principal en latabla o!icinas, es mejor utilizar el 4)*N que un producto cartesiano.6mparejamos las dos tablas por el campo o!icina. Las o!icinas que no tenanempleados no salen es un *NN6;/.
7omo queremos s(lo las o!icinas del este aadimos la cl&usula IJ6;6 con lacondicion. 6l valor estedebe ir entre comillas es un valor al!anum2rico/.
)bservar que en la lista de selecci(n la columna o!icina est& cuali!icada sunombre est& precedido del nombre de la tabla/, es necesario cuali!icarla porqueen las dos tablas e5iste una columna llamada o!icina % el sistema no sabra cu&lde las dos escoer.
-
7/26/2019 consultas sql server
6/53
SELE! o"icinas.o"icina# ciu$a$# numemp# nombre%&'( o"icinas LE%! +')* emplea$os '* o"icinas.o"icina , emplea$os.o"icina-E&E re/ion , este
Si queremos que tambi2n aparezcan las o!icinas que no tienen empleadoscambiamos *NN6; por L68 queremos todas las o!icinas % la tabla o!icinas est& ala izquierda de la palabra 4)*N/.
)jo, si en la lista de selecci(n ponemos empleados.o!icina en vez deo!icinas.o!icina, en las !ilas de o!icinas que no tienen empleados el n$mero deo!icina aparece nulo.
SELE! o"icinas.o"icina# ciu$a$# numemp# nombre%&'( emplea$os &)! +')* o"icinas '* o"icinas.o"icina , emplea$os.o"icina-E&E re/ion , este
6sta S6L678 es equivalente a la anterior pero 'emos cambiado L68 por ;*PJ8porque a'ora la tabla o!icinas est& a la derec'a de la palabra 4)*N.
6jercicio #
SELE! numpe$i$o# importe# clientes.nombre S cliente# limitecre$ito%&'( pe$i$os )**E& +')* clientes '* pe$i$os.clie , clientes.numclie
6n este ejercicio no pueden 'aber pedidos sin cliente, % lo que nos interesa sonlos pedidos, lueo tampoco tienen que aparecer los clientes que no tienenpedidos, por lo tanto utilizamos un *NN6; 4)*N.
6jercicio @
SELE! emplea$os.# ciu$a$# re/ion
%&'( emplea$os LE%! +')* o"icinas '* emplea$os.o"icina , o"icinas.o"icinaAqu 'emos utilizado L68 4)*N para que tambi2n salan los empleados que notienen o!icina asinada.7omo queremos todos los datos del empleado utilizamos empleados.9 paraacortar.
6jercicio C
SELE! o"icinas.# nombre S $irector%&'( emplea$os &)! +')* o"icinas '* emplea$os.o"icina , o"icinas.o"icina-E&E objetio 600000
Nos interesan las o!icinas con objetivo superior a -??.???pts. lueo nos tenemosque aseurar que salan todas incluso si no tienen director asinado por esoutilizamos ;*PJ8 4)*N.6n los valores num2ricos no utilizar el punto para separar los miles loconsiderara coma decimal % entendera -?? en vez de -?????/.
6jercicio 3
-
7/26/2019 consultas sql server
7/53
SELE! numpe$i$o# importe# emplea$os.nombre S representante# clientes.nombre Scliente%&'( pe$i$os )**E& +')* clientes '* pe$i$os.clie , clientes.numclie )**E& +')*emplea$os '* pe$i$os.rep , emplea$os.numemp-E&E importe 25000
6n este ejercicio no pueden 'aber pedidos sin representante ni cliente, % lo quenos interesa son los pedidos, lueo tampoco tienen que aparecer losrepresentantes que no tienen pedidos ni los clientes que no tienen pedidos, porlo tanto utilizamos un *NN6; 4)*N.
Brimero aadimos a cada lnea de pedido los datos del cliente corespondientecon el primer *NN6;/ % a cada !ila resultante aadimos los datos delrepresentante correspondiente.
Nota: el representante que nos interesa es el que 'a realizado el pedido % esedato lo tenemos en el campo rep de pedidos por eso la condici(n de
emparejamiento es pedidos.rep K empleados.rep.
Si 'ubiesemos querido el nombre del representante asinado al cliente, lacondici(n 'ubiera sido clientes.repclie K empleados.numemp.
6jercicio -
SELE! emplea$os.%&'( emplea$os )**E& +')* pe$i$os '* pe$i$os.rep , emplea$os.numemp-E&E "ecape$i$o , contrato
Los representantes que buscamos tienen un pedido con la misma !ec'a que la desu contrato, tenemos que aadir a los pedidos los datos del representantecorrespondiente para poder comparar los dos campos.
6jercicio F
SELE! emplea$os.# je"es.numemp S numje"e# je"es.nombre S nombreje"e# je"es.cuotaS cuotaje"e%&'( emplea$os )**E& +')* emplea$os je"es '* emplea$os.je"e , je"es.numemp-E&E emplea$os.cuota je"es.cuota
6n una misma lnea necesito los datos del empleado % los datos de su je!e, lueo
teno que combinar empleados con empleados. No interesan los empleados queno tienen je!e lueo utilizo *NN6;. 6l alias de tabla es obliatorio %a que combinoempleados con la misma.
6jercicio G
-
7/26/2019 consultas sql server
8/53
SELE! numemp%&'( emplea$os LE%! +')* pe$i$os '* pe$i$os.rep , emplea$os.numemp
-E&E importe 10000 '& cuota : 10000
na posible soluci(n es combinar pedidos con empleados para poder seleccionarlas lneas de importe 1???? o cuota R 1????. Ja% que utilizar L68 para quepuedan aparecer empleados con cuota R 1???? que no tenan pedidos.
SELE! rep%&'( pe$i$os-E&E importe 10000;*)'*SELE! numemp%&'( emplea$os-E&E cuota : 10000
6sta es otra soluci(n, obtener por una parte los c(dios de los empleados con unalnea de pedido 1????, por otra parte los c(dios de los empleados con cuota R
1???? % !inalmente unir las dos listas con una N*)N.
Ejercicios tema 4. Las consultas $e resumen
17u&l es la cuota media % las ventas medias de todos los empleadosT
2Jallar el importe medio de pedidos, el importe total de pedidos % el preciomedio de venta el precio de venta es el precio unitario en cada pedido/.
3Jallar el precio medio de los productos del !abricante A7*.
47u&l es el importe total de los pedidos realizados por el empleado +icenteBantallaT
5Jallar en qu2 !ec'a se realiz( el primer pedido suponiendo que en la tabla depedidos tenemos todos los pedidos realizados 'asta la !ec'a/.
6Jallar cu&ntos pedidos 'a% de m&s de #3??? ptas.
7Listar cu&ntos empleados est&n asinados a cada o!icina, indicar el n$mero deo!icina % cu&ntos 'a% asinados.
8Bara cada empleado, obtener su n$mero, nombre, e importe vendido por eseempleado a cada cliente indicando el n$mero de cliente.
9Bara cada empleado cu%os pedidos suman m&s de @?.??? ptas, 'allar suimporte medio de pedidos. 6n el resultado indicar el n$mero de empleado % suimporte medio de pedidos.
-
7/26/2019 consultas sql server
9/53
10Listar de cada producto, su descripci(n, precio % cantidad total pedida,inclu%endo s(lo los productos cu%a cantidad total pedida sea superior al F3 delstocUO % ordenado por cantidad total pedida.
11Saber cu&ntas o!icinas tienen empleados con ventas superiores a su cuota, no
queremos saber cuales sino cu&ntas 'a%.
Solucin ejercicios tema 4. Las consultas $e resumen
6jercicio 1
SELE! cant, por lo que ponemos A+Pimporte>cant/.
6jercicio @
SELE!
-
7/26/2019 consultas sql server
10/53
6jercicio 3
SELE! ()*"ecape$i$o S primerpe$i$o%&'( pe$i$os
La !ec'a del primer pedido es la !ec'a m&s antiua de la tabla de pedidos.
6jercicio -
SELE! ';*! S cuantospe$i$osma?ores%&'( pe$i$os-E&E importe 25000
Se poda 'aber utilizado tambi2n 7)N8numpedido/ o cualquier nombre decolumna que no pueda contener valores nulos, pero 7)N89/ es mejor por serm&s r&pido la di!erencia se nota con tablas mu% voluminosas/.
6jercicio F
SELE! o"icina# ';*! S cuantosemplea$os%&'( emplea$os&';> @A o"icina
7on esta soluci(n obtenemos el listado pedido pero no aparecen las o!icinas queno tienen empleados asinados %a que sacamos la in!ormaci(n de la tablaempleados % aparece una !ila con valor nulo en o!icina que contiene el n$merode empleados que no tienen o!icina. Si quisieramos listar incluso las que notenan empleados 'abra que recurrir a la soluci(n #
Solucin 2
SELE! o"icinas.o"icina# ';*!numemp S cuantosemplea$os%&'( emplea$os &)! +')* o"icinas '* emplea$os.o"icina , o"icinas.o"icina&';> @A o"icinas.o"icina
tilizamos un ;*PJ8 4)*N para que el orien de datos inclu%a tambi2n una !ilapor cada o!icina que no tena empleados.6n el P;)B E % en la lista de selecci(n 'a% que indicar el campo o!icina de latabla o!icinas, si ponemos el de la tabla empleados, arupar& todas las o!icinasque no tienen empleados en una !ila la columna empleados.o!icina contienevalor nulo para esas !ilas/.
Aqu no podemos utilizar 7)N89/ por que las o!icinas sin empleadosapareceran con 1 en la columna cuantos"empleados %a que para esa o!icina 'a%una !ila.
6jercicio G
SELE! numemp# nombre# clie S cliente# S;(importe S totalen$i$o%&'( emplea$os )**E& +')* pe$i$os '* pe$i$os.rep , emplea$os.numemp&';> @A numemp# nombre# clie
-
7/26/2019 consultas sql server
11/53
Necesitamos la tabla de pedidos para el importe vendido a qu2 cliente,necesitamos la tabla empleados para el nombre del representante, la de clientesno la necesitamos %a que nos piden el n$mero de cliente % este est& en pedidos.La arupaci(n b&sica que debemos realizar es por numemp % despu2s por clie,pero como aparece el nombre del empleado en la lista de selecci(n, 'a% que
incluirlo tambi2n en el P;)B E.Despu2s de determinar la arupaci(n b&sica que nos 'ace !alta, siempre que seinclu%e una columna adicional en el P;)B E 'a% que comprobar que esa nuevacolumna no cambia la arupaci(n b&sica.Bor ejemplo no podramos aadir al P;)B E la columna !ec'apedido %a que se!ormaran m&s rupos.
Solucin 2SELE! numemp# nombre# clie S cliente# S;(importe S totalen$i$o%&'( emplea$os LE%! +')* pe$i$os '* pe$i$os.rep , emplea$os.numemp&';> @A numemp# nombre# clie
Si queremos que salan todos los empleados incluso los que no aparezcan en lospedidos 'abra que sustituir el *NN6; por un L68.
6jercicio H
SELE! rep# @A rep
-
7/26/2019 consultas sql server
12/53
7omo e5istencias aparece en el JA+*NP % no modi!ica la arupaci(n b&sica loincluimos tambi2n el el P;)B E.
Bara calcular el F3 de las e5istencias multiplicamos e5istencias por ?,F3Oobservar que en la sentencia SQL 'a% que utilizar el punto para indicar los
decimales.
Bara 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. 6neste caso la suma de importes es la tercera columna.
6jercicio 11
onsultaD $istintaso"icinasSELE! B)S!)*! o"icina%&'( emplea$os-E&E entas cuota
onsultaD sumaria11SELE! ';*! S cuantaso"icinas%&'( $istintaso"icinas
Si contamos las o!icinas directamente de la tabla empleados nos salen H o!icinas%a que la !unci(n 7)N8nb columna/ cuenta los valores no nulos pero losvalores repetidos los cuenta tantas veces como se repiten, como tenemoso!icinas de se repiten en la columna o!icina de la tabla o!icinas, esas o!icinas soncontadas varias veces, 'a% que contar los valores distintos.6n otros SQL la !unci(n 7)N8 puede llevar delante del nombre de la columna lacl&usula D*S8*N78 que indica que s(lo se tienen que tener en cuenta valores
distintos no cuenta los repetidos/, por ejemplo 7)N8D*S8*N78 o!icina/, es unaopci(n mu% $til que desraciadamente no inclu%e el SQL de
-
7/26/2019 consultas sql server
13/53
3Listar los vendedores que no trabajan en o!icinas diriidas por el empleado1?G.
4Listar los productos id!ab, idproducto % descripci(n/ para los cuales no se 'arecibido nin$n pedido de #3??? o m&s.
5Listar los clientes asinados a Ana ustamante que no 'an remitido un pedidosuperior a @??? pts.
6Listar las o!icinas en donde 'a%a un vendedor cu%as ventas representen m&sdel 33 del objetivo de su o!icina.7Listar las o!icinas en donde todos los vendedores tienen ventas que superan al3? del objetivo de la o!icina.
8Listar las o!icinas que tenan un objetivo ma%or que la suma de las cuotas desus vendedores.
Solucin ejercicios tema 5. Las subconsultas
6jercicio 1
SELE! nombre%&'( clientes-E&E repclie , SELE! numemp %&'( emplea$os -E&E nombre , laro +aumes
Jemos supuesto que no pueden 'aber dos empleados con el mismo nombre, de locontrario 'abra que aadir *Aantes de la subconsulta.
6jercicio #
Solucin 1SELE! numemp# nombre# o"icina%&'( emplea$os-E&E o"icina )* SELE! o"icina %&'( o"icinas -E&E entas objetio
7on esta soluci(n buscamos que la o!icina del empleado est2 en la lista deo!icinas que tienen ventas superiores a su objetivo.
Solucin 2SELE! numemp# nombre# o"icina
%&'( emplea$os-E&E EF)S!S SELE! %&'( o"icinas -E&E emplea$os.o"icina , o"icinas.o"icina *Bentas objetio
7on esta soluci(n buscamos que e5ista una o!icina iual al del empleado % quetena ventas superiores a su objetivo. 6l resultado ser& el mismo que con lasoluci(n 1.
-
7/26/2019 consultas sql server
14/53
Solucin 3SELE! numemp# nombre# o"icina%&'( emplea$os-E&E o"icina , *A SELE! o"icina %&'( o"icinas -E&E entas objetio
7on esta otra comparamos la o!icina del empleado con cada una de las o!icinas
que tenan ventas superiores a su objetivo, si la o!icina del empleado es iual aaluna de esas o!icinas aparece el empleado en el resultado. 6l resultado ser& elmismo que con la soluci(n 1.
6jercicio @
Solucin 1SELE! numemp# nombre# o"icina%&'( emplea$os-E&E *'! EF)S!S SELE! %&'( o"icinas -E&E emplea$os.o"icina , o"icinas.o"icina *B$ir , 108
)btenemos los empleados tales que no e5ista una o!icina iual a la su%a que
adem&s est2 diriida por el empleado 1?G, con esta soluci(n s aparecen losempleados que no tienen o!icina.
SELE! numemp# nombre# o"icina%&'( emplea$os-E&E o"icina *'! )* SELE! o"icina %&'( o"icinas -E&E $ir , 108
7on la subconsulta obtenemos la lista de las o!icinas diriidas por el empleado1?G. Al !inal se obtienen los empleados cu%a o!icina no est2 en esa lista. Bero nosalen los empleados que no tienen o!icina asinada %a que su campo o!icina esnulo por lo que el resultado de la comparaci(n es nulo, no es verdadero % no se
seleccionan. 6l problema se puede arrelar indicando que tambi2n se tienen queseleccionar los empleados con o!icina nula:
Solucin 2SELE! numemp# nombre# o"icina%&'( emplea$os-E&E o"icina *'! )* SELE! o"icina %&'( o"icinas -E&E $ir , 108 '& o"icina )S*;LL
7on la subconsulta obtenemos la lista de las o!icinas diriidas por el empleado1?G. Al !inal se obtienen los empleados cu%a o!icina no est2 en esa lista. Bero nosalen los empleados que no tienen o!icina asinada %a que su campo o!icina es
nulo por lo que el resultado de la comparaci(n es nulo, no es verdadero % no seseleccionan.
SELE! numemp# nombre# o"icina%&'( emplea$os-E&E o"icina : LL SELE! o"icina %&'( o"icinas -E&E $ir , 108
-
7/26/2019 consultas sql server
15/53
7on esta soluci(n tenemos el mismo problema que con *'! )*, cuando lao!icina del empleado es nula todos los resultados de las comparacionesindividuales son nulos por los que el test LLda nulo % no se seleccionan losempleados con o!icina nula.
6jercicio C
SELE! i$"ab# i$pro$ucto# $escripcion%&'( pro$uctos-E&E *'! EF)S!S SELE! %&'( pe$i$os -E&E "ab , i$"ab *B pro$ucto , i$pro$ucto*B importe , 25000
6n este caso es m&s c(modo utilizar *'! EF)S!S%a que 'a% que preuntar por elidfabe idproductoa la vez.
6jercicio 3
SELE! numclie# nombre
%&'( clientes-E&E repclie )* SELE! numemp %&'( emplea$os -E&E nombre , na @ustamante *B numclie *'! )* SELE! clie %&'( pe$i$os -E&E importe 3000
6jercicio -
SELE! %&'( o"icinas-E&E EF)S!S SELE! %&'( emplea$os -E&E entas objetio 0.55
6n una subconsulta todos los campos no cuali!icados se presuponen de la tablaorien de la subconsulta % s(lo si no e5iste ninuna columna con ese nombre, laconsidera como re!erencia e5terna, por eso no es necesario cuali!icar ventas
porque interpreta que es el campo ventas de la tabla empleados.
6jercicio F
SELE! %&'( o"icinas-E&E objetio 0.5 :, LL SELE! entas %&'( emplea$os -E&E emplea$os.o"icina ,o"icinas.o"icina
6sta soluci(n no vale porque salen las o!icinas que no tienen empleados.Ja% que aadir una condici(n para que se consideren s(lo las o!icinas conempleados como muestra la soluci(n 1.
Solucin 1SELE! %&'( o"icinas-E&E objetio 0.5 :, LL SELE! entas %&'( emplea$os -E&E emplea$os.o"icina ,o"icinas.o"icina *B EF)S!S SELE! %&'( emplea$os -E&E emplea$os.o"icina , o"icinas.o"icina
-
7/26/2019 consultas sql server
16/53
Solucin 2SELE! %&'( o"icinas-E&E objetio .5 :, SELE! ()*entas %&'( emplea$os -E&E emplea$os.o"icina ,o"icinas.o"icina
6sta es otra posible soluci(n, calculamos la menor venta de los empleados de lao!icina % si esta es ma%or que el 3? del ojetivo de la o!icina quiere decir quetodos los empleados de esa o!icina tienen ventas iuales o superiores. Si lao!icina no tiene empleados, la subconsulta no devuelve ninuna !ila % comoestamos utilizando una comparaci(n simple el resultado es nulo, lueo no salenlas o!icinas que no tienen empleados.
6jercicio G
SELE! %&'( o"icinas
-E&E objetio SELE! S;(cuota %&'( emplea$os -E&E emplea$os.o"icina ,o"icinas.o"icina
Ejercicios tema 6. ctualiGacin $e $atos
7omo en estos ejercicios vamos a modi!icar los valores almacenados en la basede datos, es conveniente uardar antes una copia de las tablas, en los cuatroprimeros ejercicios crearemos una copia de los datos almacenados para lueopoder recuperar los valores oriinales.17rear una tabla llamarla nuevaempleados/ que contena las !ilas de la tablaempleados.
27rear una tabla llamarla nuevaoficinas/ que contena las !ilas de la tablaoficinas.
37rear una tabla llamarla nuevaproductos/ que contena las !ilas de la tablaproductos.
47rear una tabla llamarla nuevapedidos/ que contena las !ilas de la tablapedidos.
5Subir un 3 el precio de todos los productos del !abricante ACI.
6Aadir una nueva o!icina para la ciudad de Madrid, con el n$mero de o!icina30, con un objetivo de 100000% rei(n Centro.
77ambiar los empleados de la o!icina 21a la o!icina 30.
-
7/26/2019 consultas sql server
17/53
86liminar los pedidos del empleado 105.
96liminar las o!icinas que no tenan empleados.
10;ecuperar los precios oriinales de los productos a partir de la tabla
nuevosproductos.
11;ecuperar las o!icinas borradas a partir de la tabla nuevaoficinas.
12;ecuperar los pedidos borrados en el ejercicio G a partir de la tablanuevapedidos.
13A los empleados de la o!icina 30asinarles la o!icina 21.
Solucin ejercicios tema 6. ctualiGacin
6jercicio 1
SELE! )*!' nueaemplea$os%&'( emplea$os
6jercicio #
SELE! )*!' nueao"icinas%&'( o"icinas
6jercicio @
SELE! )*!' nueapro$uctos%&'( pro$uctos
6jercicio C
SELE! )*!' nueape$i$os%&'( pe$i$os
6jercicio 3
;>B!E pro$uctosSE! precio , precio 1.05 -E&E i$"ab , )
8ambi2n se puede poner precio K precio = precio9?.?3
6jercicio -
-
7/26/2019 consultas sql server
18/53
Solucin 1
)*SE&! )*!' o"icinas o"icina# re/ion# ciu$a$# objetio
-
7/26/2019 consultas sql server
19/53
Solucin 1BELE!E %&'( o"icinas -E&E *'! EF)S!S SELE! %&'( emplea$os -E&E emplea$os.o"icina , o"icinas.o"icina
Si la o!icina no tiene empleados asinados, no e5iste nin$n empleado con eln$mero de esa o!icina.
Solucin 2BELE!E %&'( o"icinas -E&E o"icina *'! )* SELE! o"icina%&'( emplea$os
8ambi2n se puede ver como las o!icinas cu%o n$mero no se encuentra entre laso!icinas asinados a los empleados.
Solucin 3BELE!E o"icinas.%&'( o"icinas LE%! +')* emplea$os'* o"icinas.o"icina, emplea$os.o"icina
-E&E emplea$os.numemp )S *;LL
)tro planteamiento sera unir los empleados con sus o!icinas % que tambi2nsalan las o!icinas que no tienen empleados por eso L68 en vez de *NN6;/ apartir de a' seleccionamos las !ilas que no tienen valor en el campo numemp,estas son las no tienen nin$n empleado relacionado. 7omo adem&s el orienest& basado en dos tablas es obliatorio poner oficinas.9 para indicar que setienen que borrar las !ilas de la tabla oficinas% no de empleados.
6jercicio 1?
;>B!E pro$uctos )**E& +')* nueapro$uctos'* pro$uctos.i$"ab , nueapro$uctos.i$"ab *B pro$uctos.i$pro$ucto ,nueapro$uctos.i$pro$uctoSE! pro$uctos.precio , nueapro$uctos.precio
nimos la tabla de productos con la tabla nuevaproductospara tener en unamisma !ila el precio que queremos cambiar % el precio antiuo el valor quequeremos dejar/.
6jercicio 11
)*SE&! )*!' o"icinas
SELE! %&'( nueao"icinas-E&E o"icina *'! )* SELE! o"icina %&'( o"icinas
6n este caso insertamos en o!icinas las o!icinas de nuevaoficinascu%o n$mero deo!icina no est& en oficinases decir las que se 'an borrado/.
6jercicio 1#
-
7/26/2019 consultas sql server
20/53
)*SE&! )*!' pe$i$osSELE! "rom nueape$i$os -E&E rep , 105
*nsertamos en pedidoslos pedidos del empleados 105que se encuentran en latabla nuevapedidos.
6jercicio 1@
;>B!E emplea$osSE! o"icina , 21 -E&E o"icina , 30
Si no 'emos recuperado las o!icinas borradas, no permitir& cambiar el campooficinaa #1 %a que la o!icina #1 es de las que se 'an borrado en el ejercicio H.
Ejercicios tema 7. &e"erencias cruGa$as
1Queremos saber de cada empleado sus ventas mensuales del ao 1HH?.
2
-
7/26/2019 consultas sql server
21/53
pedidos de 1HH?, como columna !ija queremos el n$mero de empleado lueo lalista de selecci(n ser& rep con un alias para que el resultado sala m&s aseado.
6jercicio #
!&*S%'&( S;(importe
SELE! numemp S emplea$o# nombre%&'( pe$i$os )**E& +')* emplea$os '* pe$i$os.rep,emplea$os.numemp-E&E ?ear"ecape$i$o,1990
&';> @A numemp# nombre
>) @A o"icina
>) @A o"icina
>)
-
7/26/2019 consultas sql server
22/53
6n este caso eleimos como pivote el ao % como encabezado de !ila la o!icina %aque normalmente abr&n m&s o!icinas que aos.
6jercicio 3
!&*S%'&( S;(importe
SELE! AE&"ecape$i$o S an?o%&'( pe$i$os )**E& +')* emplea$os '* pe$i$os.rep,emplea$os.numemp )**E& +')*o"icinas '* emplea$os.o"icina,o"icinas.o"icina
&';> @A AE&"ecape$i$o
>)
-
7/26/2019 consultas sql server
23/53
8Jacer que no puedan 'aber dos empleados con el mismo nombre.
9Aadir a la tabla de pedidos la de!inici(n de clave principal.
10De!inir un ndice sobre la columna reion de la tabla de o!icinas.
106liminar el ndice creado.
Solucin ejercicios tema 8. El BBL
6jercicio 1
&E!E !@LE emplea$os numemp )*!EE& >&)(&A JEA#nombre !EF!30 *'! *;LL#e$a$ )*!EEo"icina )*!EE
titulo &20#contrato B!E!)(E *'! *;LL#$irector )*!EEcuota ;&&E*A#entas ('*EA
La soluci(n propuesta es una de la muc'as posibles %a que cada uno puede eleirel tipo de datos que pre!iera siempre % cuando ese tipo permita introducir losdatos que tenemos en las tablas de ejemplo. 8ambi2n la cl&usula N)8 NLL sepuede poner en m&s campos de los que tiene la soluci(n e5cepto en las columnasque tenan !ilas sin valor en las tablas de ejemplo. Bor ejemplo en o!icina elempleado 11? no tiene o!icina/, en director el empleado 1?- no tiene director/ %
en cuota el empleado 11? no tiene cuota asinada. Bero las columnas edad,titulo % ventas s las podemos de!inir con la restricci(n N)8 NLL. Bara de!inir lascolumnas cuota % ventas 'emos eleido el tipo moneda 7;;6N7E % &)(&A JEA#ciu$a$ !EF!30#re/ion !EF!20#$ir )*! '*S!&)*! c"$ir &E%E&E*ES emplea$os#
objetio ;&&E*A#entas ;&&E*A
Bara de!inir la columna dir como clave !or&nea 'emos eleido una restricci(n1poner la de!inici(n dentro de la de!inici(n de la columna/.
6jercicio @
-
7/26/2019 consultas sql server
24/53
&E!E !@LE pro$uctos i$"ab !EF!10#i$pro$ucto !EF!20#$escripcion !EF!30 *'! *;LL#precio ;&&E*A *'! *;LL#eCistencias )*! #'*S!&)*! cp >&)(&A JEA i$"ab#i$pro$ucto
6n este caso la clave principal est& !ormada por dos columnas id!ab e idproductolueo para de!inirla tenemos que utilizar necesariamente una restricci(n#.
6jercicio C
&E!E !@LE clientes numclie )*! #nombre !EF!30 *'! *;LL#repclie )*! '*S!&)*! c"repclie &E%E&E*ES emplea$os#'*S!&)*! cp >&)(&A JEA numclie
La clave principal se puede de!inir en una restricci(n# aunque est2 compuestapor una sola columna.
6jercicio 3
&E!E !@LE pe$i$os co$i/o ';*!Enumpe$i$o )*! >&)(&A JEA#"ecape$i$o B!E!)(E *'! *;LL#clie )*! *'! *;LL#rep )*! *'! *;LL#
"ab !EF!10 *'! *;LL#pro$ucto !EF!20 *'! *;LL#cant )*! *'! *;LL#importe ;&&E*A *'! *;LL#'*S!&)*! c"clie %'&E)* JEA clie &E%E&E*ES clientes#'*S!&)*! c"rep %'&E)* JEA rep &E%E&E*ES emplea$os#'*S!&)*! c"pro$ %'&E)* JEA "ab#pro$ucto &E%E&E*ES pro$uctos
Bara e5poner m&s !ormas de de!inir una tabla aqu te 'emos de!inido todas lasclaves como restricci(n# la $nica que es obliatoria en una restricci(n# es lac!"prod %a que est& compuesta por varias columnas.
6jercicio -L!E& !@LE clientesBB 'L;(* limitecre$ito ('*EA
Bara aadir una nueva columna a una tabla que %a e5iste debemos emplear lasentencia AL86; 8AL6, % en nuestro caso la cl&usula ADD 7)L
-
7/26/2019 consultas sql server
25/53
6jercicio F
L!E& !@LE emplea$osBB '*S!&)*! c"o"icina %'&E)* JEA o"icina &E%E&E*ES o"icinas#'*S!&)*! c"$irector %'&E)* JEA $irector &E%E&E*ES emplea$os
Bara aadir una de!inici(n de clave !or&nea 'a% que aadir una restricci(n#, sepueden aadir varias restricciones en la misma sentencia AL86; 8AL6.
6jercicio G
Solucin 1L!E& !@LE emplea$osBB '*S!&)*! unombre ;*)K;E nombre
Solucin 2&E!E ;*)K;E )*BEF unombre '* emplea$os nombre
Bara que no se puedan repetir los valores en la columna nombre 'a% que de!inirun ndice $nico, o bien de!iniendo una restricci(n sobre la columna como teindicamos en la soluci(n 1 o bien creando el ndice $nico como te indicamos en lasoluci(n #.
6jercicio H
Solucin 1L!E& !@LE pe$i$osBB '*S!&)*! cp >&)(&A JEA numpe$i$o
Bara aadir una de!inici(n de clave primaria 'a% que aadir una restricci(n#.
Solucin 2&E!E )*BEF cp '* pe$i$os numpe$i$o -)! >&)(&A
)tra soluci(n es crear un ndice con la cl&usula I*8J B;* )*BEF ire/ion '* o"icinas
UNION
-
7/26/2019 consultas sql server
26/53
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 telfono y direccin de e-mail sean conocidos Se
deber! visuali"ar el cdi#o$ nombre y si se trata de un cliente o de un
vendedor Ordene por la tercer columna y la se#unda
select cod_vend, nom_vend, 'vend' Tipo
!om vendedo!es
"#e!e n!o_tel is not n$ll %nd is not n$ll
$nion
select cod_clie, nom_clie, 'cliente'
!om clientes
"#e!e n!o_tel is not n$ll %nd is not n$ll
o!de! & ,
Se quiere saber que art%culos$ clientes y vendedores hay en la
empresa &etermine los campos a mostrar y su ordenamiento
select cod_%!ti 'C*di+o', desc!ipcion 'Nom&!e', 'A!tic$lo'Tipo
!om %!tic$los
-
7/26/2019 consultas sql server
27/53
$nion
select cod_clie, nom_clie, 'Cliente'
!om clientes
$nion
select cod_vend, nom_vend, 'endedo!'
!om vendedo!es
o!de! &
Se quiere saber que art%culos hay en la empresa y cu!les han sido
vendidos &etermine Ud las columnas a mostrar
select cod_%!ti C*di+o, desc!ipcion A!tic$lo, 'En -toc.' Tipo
!om %!tic$los
$nion
select D/cod_%!ti, A/desc!ipcion, 'endido'
!om det%lle D, A!tic$los A
"#e!e D/cod_%!ti 0 A/cod_%!ti
o!de! &
Se quiere saber las direcciones tanto de clientes como de vendedores
'ara el caso de los vendedores$ cdi#os entre ( y )* +n ambos casos
la direcciones deber!n ser conocidas ,otule como NO.,+$
&I,+//ION$ IN0+1,2N0+ 3en donde indicar! si es cliente o vendedor4
Ordenado por la primera columna y la 5ltima
-
7/26/2019 consultas sql server
28/53
select nom_clie Nom&!e, di!eccion Di!eccion, 'Cliente' Inte+!%nte
!om clientes
"#e!e di!eccion is not n$ll
$nion
select nom_vend, di!eccion, 'endedo!'
!om vendedo!es
"#e!e di!eccion is not n$ll %nd cod_vend &et"een %nd 1
o!de! & 1,
Se quiere saber que clientes hay en la empresa y quienes han
comprado entre el ))6)*6*77* y el *867*6*77( uestre el cdi#o$ sin
duplicarlos
select cod_clie Codi+o
!om clientes
$nion
select cod_clie
!om %ct$!%s
"#e!e ec#% &et"een 2231131 %nd 223432
-
7/26/2019 consultas sql server
29/53
Idem al e9ercicio anterior$ slo que adem!s del cdi#o$ identique de
donde obtiene la informacin
select cod_clie C*di+o, 'Clientes' Desde
!om clientes
$nion %ll
select cod_clie, '5%ct$!%s'
!om %ct$!%s 5
"#e!e ec#% &et"een '2231131' %nd '223432'
Se quiere saber que clientes hay en la empresa y quines han
comprado; para el primer caso para nombres que empiecen con letras
que van de la c< a la l< y para el se#undo para facturas que oscilen
entre )7 y *( uestre el cdi#o 3no elimine los que se repiten4
select cod_clie C*di+o, 'P%sivo' Tipo
!om clientes
"#e!e nom_clie li.e '6c3l78'
$nion %ll
select cod_clie, 'Activo'
!om %ct$!%s 5
"#e!e 5/n!o_%ct$ &et"een 12 %nd
3333333333333333333333333333333333333333333333333333333333333333333333333333333333333
-
7/26/2019 consultas sql server
30/53
INN+, =OIN
+l INN+, =OIN:Es ot!o tipo de composici*n de t%&l%s/ En l$+%! de #%ce! el
p!od$cto c%!tesi%no completo, p%!% c%d% $n% de l%s t%&l%s &$sc% di!ect%mente
en l% ot!% t%&l% l%s 9l%s :$e c$mplen l% condici*n/
>+?0 OU0+, =OIN:Tom% todos los !e+ist!os de l% t%&l% i;:$ie!d%, %$n:$e no
ten+%n nin+
-
7/26/2019 consultas sql server
31/53
>iste cdi#o de vendedor$ nombre$ fecha y factura; para las ventas en
lo que va del aBo
select v/cod_vend, v/nom_vend, /ec#%, /n!o_%ct$
!om %ct$!%s
inne! =oin vendedo!es v
on /cod_vend0v/cod_vend
"#e!e e%!(/ec#%)0224
>iste cdi#o de vendedor$ nombre$ fecha y factura; para las ventas en
lo que va del aBo
select v/nom_vend, v/cod_vend, /ec#%, /n!o_%ct$
!om %ct$!%s
!i+#t =oin vendedo!es v
on /cod_vend0v/cod_vend
"#e!e e%!(/ec#%)0224 o! /ec#% is n$ll o! /n!o_%ct$ is n$ll
>iste descripcin$ cantidad e importe; aun para aquellos art%culos que
no re#istran ventas
select %/desc!ipcion, d/c%nt, d/p!e_$nit, d/p!e_$nit?d/c%nt %s 'tot%l'
!om det%lle d
$ll =oin %!tic$los %
on d/cod_%!ti0%/cod_%!ti
$ll =oin %ct$!%s
-
7/26/2019 consultas sql server
32/53
on d/n!o_%ct$0/n!o_%ct$
o!de! & %/desc!ipcion
!om nom&!e de t%l&% =oin ot!% t%&le on i+$%l%mos c%mpos p%!% list%! tod%s l%s
%ct$!%s, los clientes % los :$e #e %ct$!%do p%!% c%sos en :$e l%s %ct$!%s no
ten+%n c%mpo cliente, /
let =oin es $n% $ni*n % l% i;:$ie!d% m$e!to todos los c%mpos de l% i;:$ie!d%
solo los coicidentes
con l% de!ec#%/ Con el !ei+#t M$est% todos los c%mpos de l% de!ec#% solo los
coicidentes con l% de!ec#% Con el $ll =oin m$est!% todos los c%mpos
>iste factura$ fecha$ vendedor$ cliente$ articulo$ cantidad e importe;
para las ventas de febrero y mar"o de los aBos *77C y *778 y siempre
que el articulo empiece con letras que van de la a< a la m
-
7/26/2019 consultas sql server
33/53
%nd e%!(/ec#%) in (22>, 224)
>iste cdi#o de cliente$nombre$ fecha y factura para las ventas del
aBo *778 uestre los clientes hayan comprado o no en ese aBo
select c/cod_clie,c/nom_clie, /ec#%, /n!o_%ct$
!om %ct$!%s $ll =oin clientes c
on /cod_clie0c/cod_clie
"#e!e e%! (/ec#%)0224
Se quiere saber los art%culos que compro el cliente 8 en lo que va del
aBo >iste articulo$ observaciones e importe
select %/desc!ipcion, c/nom_clie
!om %ct$!%s inne! =oin clientes c
on /cod_clie0c/cod_clie
inne! =oin det%lle d
on /n!o_%ct$0/n!o_%ct$
inne! =oin %!tic$los %
on d/cod_%!ti0%/cod_%!ti
"#e!e c/cod_clie04
%nd e%!(/ec#%)0224
Se quiere saber los art%culos que compraron los clientes que empie"an
con piste cliente$ articulo$ cantidad e importe Ordene por cliente
-
7/26/2019 consultas sql server
34/53
y articulo$ este en forma descendente ,otule como />I+N0+$
2,0I/U>O$ /2N0I&2&$ I'O,0+
select c/nom_clie %s 'cliente',
%/desc!ipcion %s '%!tic$lo' ,
d/c%nt %s 'c%ntid%d',
d/p!e_$nit %s 'p!ecio',
d/c%nt?d/p!e_$nit %s 'tot%l'
!om %ct$!%s inne! =oin det%lle d
on /n!o_%ct$0d/n!o_%ct$
inne! =oin %!tic$los %
on d/cod_%!ti0%/cod_%!ti
inne! =oin clientes c
on /cod_clie0c/cod_clie
"#e!e c/nom_clie li.e '6p78'
o!de! & c/nom_clie desc, %/desc!ipcion desc
3333333333333333333333333333333333333333333333333333333333333333333333333333
SU.-/ONSU>02S
Subconsultas:Es $n% cons$lt% :$e %p%!ece dent!o de l% cl@$s$l% BERE *
BAING de ot!% sentenci% -L/
-$&cons$lt%s en l% cl@$s$l% BERE
-
7/26/2019 consultas sql server
35/53
0est &e /omparacin: 3D $ E $ F 4 Comp%!% el v%lo! de $n% ep!esi*n con $n
-
7/26/2019 consultas sql server
36/53
select cod_%!ti, desc!ipcion, o&se!v%ciones, p!e_$nit
!om %!tic$los
"#e!e p!e_$nit &et"een %nd > %nd cod_%!ti not in (select distinct cod_%!ti
!om %ct$!%s , det%lle d
"#e!e d/n!o_%ct$0/n!o_%ct$ %nd e%!(ec#%)022)
Se quiere saber que clientes vinieron m!s de J veces el aBo pasado
uestre el nombre del cliente$ n5mero de factura y fecha ,otule
como />I+N0+$ ?2/0U,2$ ?+/@2 A+N02 Ordene por nombre de cliente
y fecha
select c/cod_clie %s COD_CLIENTe, c/nom_clie %s CLIENTE- ,/n!o_%ct$ %s
5ACTFRA-, /ec#% %s 5ECBA
!om clientes c, %ct$!%s
"#e!e c/cod_clie0/cod_clie %nd e%! (ec#%)022 %nd
H(select co$nt (?) !om %ct$!%s 1 "#e!e 1/cod_clie0c/cod_clie %nd
e%! (ec#%)022 )
o!de! & c/nom_clie, /ec#% %sc
Se quiere saber que clientes vinieron entre el )*6)*6*77K y el
)(686*778 uestre el nombre del cliente y su direccin de mail
Ordene por nombre de cliente
select c/cod_clie, c/nom_clie,
!om clientes c
"#e!e cod_clie in (select cod_clie !om %ct$!%s
-
7/26/2019 consultas sql server
37/53
"#e!e ec#% &et"een '1122J' %nd '14224')
>iste n5mero 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 fecha
select distinct c/cod_clie, c/nom_clie,
!om clientes c, %ct$!%s
"#e!e c/cod_clie0/cod_clie %nd ec#% &et"een '1122J' %nd '14224'
uestre el n5mero de factura y la fecha de venta para los casos en
que por aBo se hayan hecho menos de L ventas ,otule como
?2/0U,2$ ?+/@2 A+N02
select /n!o_%ct$ %s 5ACTFRA , /ec#% %s 5ECBA_ENTA
!om %ct$!%s
"#e!e K (
select co$nt(?)
!om %ct$!%s 1
"#e!e e%! (/ec#%) 0 e%! (1/ec#%) )
uestre el n5mero 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 i#ual a )J7
-
7/26/2019 consultas sql server
38/53
select /n!o_%ct$ , /ec#% , %/desc!ipcion , d/p!e_$nit?d/c%nt
!om %ct$!%s , %!tic$los % , det%lle d
"#e!e %/cod_%!ti 0 d/cod_%!ti %nd /n!o_%ct$ 0 d/n!o_%ct$ %nd 12 H 0 (
select s$m (d1/p!e_$nit?d1/c%nt)
!om det%lle d1
"#e!e d1/n!o_%ct$ 0 d/n!o_%ct$)
Se quiere saber que vendedores nunca atendieron a estos clientes:C$
) uestre solamente el nombre del vendedor ,otule como
A+N&+&O,
select ?
!om vendedo!es v
"#e!e v/cod_vend not in (
select /cod_vend
!om %ct$!%s
"#e!e cod_clie in (1,>) )
Se quiere saber de que art%culos super el promedio de ventas de M7
uestre el nombre del art%culo y sus observaciones ,otule como
2,0I/U>O$ O.S+,A2/ION+S
select desc!ipcion %s ARTICFLO- , o&se!v%ciones %s O-ERACIONE-
!om %!tic$los %
-
7/26/2019 consultas sql server
39/53
"#e!e 2 H (
select %v+ (p!e_$nit?c%nt)
!om det%lle d
"#e!e %/cod_%!ti 0 d/cod_%!ti)
ue art%culos nunca se vendieron 0en#a adem!s en cuenta que su
nombre comience con letras que van de la d< a la p
-
7/26/2019 consultas sql server
40/53
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 #lobal ,otule como A+N&+&O,$ 0O02> A+N02S
select nom_vend, min(ec#%), s$m(p!e_$nit?c%nt), %v+(p!e_$nit?c%nt)
!om %ct$!%s , det%lle d, vendedo!es v
"#e!e /n!o_%ct$0d/n!o_%ct$ %nd v/cod_vend0/cod_vend
+!o$p & nom_vend
#%vin+ %v+(p!e_$nit?c%nt) (select %v+(p!e_$nit?c%nt) !om det%lle)
Se quiere saber el importe promedio y el importe de ventas por fecha
y cliente$ para los casos en que los n5meros de factura que oscilen
entre *7 y (J y que ese importe de ventas sea superior o i#ual al
promedio #lobal ,otule como ?+/@2 A+N02$ />I+N0+$ I'O,0+$
',O+&IO
-ELECT ec#%, nom_clie, s$m(p!e_$nit?c%nt), %v+(p!e_$nit?c%nt)
!om %ct$!%s , clientes c, det%lle d
"#e!e /n!o_%ct$0 d/n!o_%ct$ %nd c/cod_clie 0 /cod_clie %nd /n!o_%ct$
&et"een 2 %nd
+!o$p & ec#%, nom_clie, d/cod_%!ti
#%vin+ s$m(p!e_$nit?c%nt) (select %v+(p!e_$nit?c%nt) !om det%lle )
Se quiere saber el importe vendido$ la cantidad vendida por art%culo$
para los casos en que los n5meros de factura no sean uno de los
si#uientes:*$ )7$ 8$ )($ ** y que ese importe promedio sea inferior al
-
7/26/2019 consultas sql server
41/53
importe promedio de ese art%culo
-ELECT desc!ipcion, s$m(d/p!e_$nit?c%nt), %v+(d/p!e_$nit?c%nt)
!om %!tic$los %, det%lle d
"#e!e %/cod_%!ti 0 d/cod_%!ti %nd n!o_%ct$ not in (12,,4,1,)
+!o$p & desc!ipcion, d/cod_%!ti
#%vin+ %v+(d/p!e_$nit?c%nt) H (select %v+(d1/p!e_$nit?c%nt) !om det%lle d1
"#e!e d1/cod_%!ti0 d/cod_%!ti)
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 #lobal ,otule como ?+/@2 A+N02$
/2N0I&2&$ I'O,0+$ ',O+&IO Ordene por fecha en forma
descendente
select ec#%, s$m(c%nt), s$m(p!e_$nit?c%nt), %v+(p!e_$nit?c%nt)
!om %ct$!%s , det%lle d
"#e!e /n!o_%ct$ 0d/n!o_%ct$
+!o$p & ec#%
#%vin+ s$m(c%nt) (select %v+(c%nt) !om det%lle)
Se quiere saber el promedio vendido por fecha y art%culo para los
casos en que las cantidades vendidas oscilen entre J y *7 y que ese
importe sea superior al importe promedio de ese art%culo ,otule como
?+/@2 A+N02$ 2,0I/U>O$ ?+/ ',I+, A+N02$ ',O+&IO
-
7/26/2019 consultas sql server
42/53
select ec#%, desc!ipcion, %v+(d/p!e_$nit?c%nt)
!om %ct$!%s , %!tic$los %, det%lle d
"#e!e /n!o_%ct$ 0 d/n!o_%ct$ %nd %/cod_%!ti 0 d/cod_%!ti %nd c%nt &et"een
%nd 2
+!o$p & ec#%, desc!ipcion, d/cod_%!ti
#%vin+ s$m(d/p!e_$nit?c%nt) (select %v+(d1/p!e_$nit?c%nt) !om det%lle d1
"#e!e d1/cod_%!ti 0d/cod_%!ti)
Se quiere saber el importe vendido por fecha para los casos en que
ese promedio vendido sea inferior al importe promedio #lobal ,otule
como ?+/@2$ I'O,0+
-ELECT ec#%, s$m(p!e_$nit?c%nt)
!om det%lle d,%ct$!%s
"#e!e /n!o_%ct$ 0 d/n!o_%ct$
+!o$p & ec#%
#%vin+ %v+(p!e_$nit?c%nt) H (select %v+(p!e_$nit?c%nt) !om det%lle)
3333333333333333333333333333333333333333333333333333333333333
SU2,I2S
-
7/26/2019 consultas sql server
43/53
EJERCICIOS:
Se quiere saber la cantidad de clientes que hay en la empresa
select co$nt (cod_clie) %s c%ntid%d
5!om d&o/clientes
Se quiere saber la cantidad de art%culos que hay en la empresa
select co$nt (cod_%!ti) %s c%ntid%d
!om d&o/%!tic$los
Se quiere saber que cantidad de vendedores hay en la empresa
select co$nt (cod_vend) %s c%ntid%d
!om d&o/vendedo!es
Se quiere saber la cantidad de ventas que hi"o el vendedor de cdi#o
(
-
7/26/2019 consultas sql server
44/53
select co$nt (n!o_%ct$) %s c%ntid%d
!om d&o/%ct$!%s
"#e!e cod_vend 0
Se quiere saber la cantidad vendida$ la cantidad de ventas y el
importe para la factura )7
select s$m(c%nt) %s 'C%ntid%d vendid%',co$nt (n!o_%ct$) %s 'C%ntid%d de
vent%s',s$m(p!e_$nit?c%nt) %s Impo!te
!om d&o/det%lle
"#e!e n!o_%ct$012
Se quiere saber cual fue la fecha de la primera y 5ltima venta ,otule
como ',I+,A+N02$ U>0I2 A+N02
select m% (ec#%) %s '$ltim% vent%' , min (ec#%) %s 'p!ime!% vent%'
!om d&o/%ct$!%s
Se quiere saber cual fue la m!Gima y la m%nima cantidad que se
vendi para el art%culo )7
select m%(c%nt) %s 'M%im% vent%', min (c%nt) %s 'Minim% vent%'
!om d&o/det%lle
"#e!e cod_%!ti 0 12
Se quiere saber la cantidad total vendida$ el monto y el importe
promedio total; para vendedores cuyo nombres comien"an con letras
-
7/26/2019 consultas sql server
45/53
que van de la d< a la l
-
7/26/2019 consultas sql server
46/53
s$m(d/p!e_$nit) %s 'Monto tot%l'
!om d&o/%ct$!%s , d&o/det%lle d, d&o/%!tic$los %
"#e!e %/desc!ipcion li.e 'c8'
Se quiere saber la cantidad total vendida y el monto total vendido
para el periodo del )J67C6*77J al )J67(6*778
select s$m(d/c%nt) %s 'C%ntid%d tot%l ', s$m(d/p!e_$nit?d/c%nt) %s 'Monto tot%l'
!om d&o/det%lle d, d&o/%ct$!%s
"#e!e /ec#% &et"een '12>22' %nd '12224'
Se quiere saber la cantidad de veces y la 5ltima ve" que vino el cliente
de apellido 2barca
select co$nt(/n!o_%ct$) %s 'C%ntid%d de veces' , m%(/ec#%) %s 'Fltim% visit%'
!om d&o/%ct$!%s , d&o/clientes c
"#e!e c/nom_clie li.e 'A&%!c%8'
Se quiere saber el importe total vendido y el importe promedio
vendido para n5meros de factura que no sean los si#uientes: )($ J$
)8$ (($ *K
select s$m(d/c%nt?p!e_$nit)%s 'Impo!te tot%l' , %v+(d/c%nt?d/p!e_$nit) 'Impo!te
p!omedio'
!om d&o/det%lle d, d&o/%ct$!%s
"#e!e d/n!o_%ct$0 /n!o_%ct$ %nd /n!o_%ct$ not in (1,,14,,K)
-
7/26/2019 consultas sql server
47/53
3333333333333333333333333333333333333333333333333333
AIS02S
EJERCICIOS:
/ree las si#uientes vistas:
&etallePAentasPAendedor: >iste la fecha$ la factura$ el codi#o y
nombre del vendedor$ el articulo$ la cantidad e importe$ para lo que va
del aBo ,otule como ?+/@2$ N,OP?2/0U,2$ /O&I1OPA+N&+&O,$
O.,+PA+N&+&O,$ 2,0I/U>O$ /2N0I&2&$ I'O,0+
c!e%te vie" 6Det%lle_vent%s_vendedo!7
%s (
-
7/26/2019 consultas sql server
48/53
select /ec#% %s 5ECBA, /n!o_%ct$ %s NRO_5ACTFRA, v/cod_vend %s
CODIGO_ENDEDOR,
v/nom_vend %s NOMRE_ENDEDOR, %/desc!ipcion %s ARTICFLO,
d/c%nt %s CANTIDAD, (d/p!e_$nit?d/c%nt) %s IMPORTE
!om %ct$!%s , vendedo!es v, %!tic$los %, det%lle d
"#e!e e%! (ec#%) 0 22K)
select ?
!om 6Det%lle_vent%s_vendedo!7
SubtotalesPAentasPAendedor: Se quiere saber el importe vendido y la
cantidad de ventas por vendedor ,otule como A+N&+&O,$
I'O,0+PA+N&I&O$ /2N0I&2&PA+N&I&2
c!e%te vie" 6s$&tot%les_vent%s_vendedo!7
%s (
select co$nt (v/cod_vend) %s CANTIDAD_ENTA-, v/nom_vend ,
s$m(d/p!e_$nit?d/c%nt) %s IMPORTE
!om %ct$!%s , det%lle d, vendedo!es v
"#e!e /cod_vend 0 v/cod_vend %nd /n!o_%ct$ 0 d/n!o_%ct$
+!o$p & v/nom_vend )
select ?
!om 6s$&tot%les_vent%s_vendedo!7
-
7/26/2019 consultas sql server
49/53
odique las vistas se#5n el si#uientes detalle:
>a vista creada en el punto b$ a#r#uele la condicin de que solo tome
lo del aBo en curso y que tambin muestre el promedio vendido y el
cdi#o del vendedor
%lte! vie" 6s$&tot%les_vent%s_vendedo!7
%s (
select co$nt (v/cod_vend) %s CANTIDAD_ENTA-, v/nom_vend ,
s$m(d/p!e_$nit?d/c%nt) %s IMPORTE,
%v+(d/p!e_$nit?d/c%nt) %s PROMEDIO_ENDIDO,
v/cod_vend
!om %ct$!%s , det%lle d, vendedo!es v
"#e!e /cod_vend 0 v/cod_vend %nd /n!o_%ct$ 0 d/n!o_%ct$ %nd e%! (ec#%)
0 22K
+!o$p & v/nom_vend, v/cod_vend)
select ?
!om 6s$&tot%les_vent%s_vendedo!7
/onsulta las vistas se#5n el si#uiente detalle:
>lame a la vista creada en el punto )a pero ltrando por importes
inferiores a Q*7
select ?
!om 6Det%lle_vent%s_vendedo!7
-
7/26/2019 consultas sql server
50/53
"#e!e IMPORTE H0 2
>lame a la vista creada en el punto )b ltrando para el vendedor
iranda
select ?
!om 6s$&tot%les_vent%s_vendedo!7
"#e!e nom_vend li.e 'Mi!%nd%8'
>lama a la vista creada en el punto )b ltrando para promedios
superiores a )77
select ?
!om 6s$&tot%les_vent%s_vendedo!7
"#e!e PROMEDIO_ENDIDO J2
+limine las vistas creadas en el punto ) 3no se olvide de colocar el
nombre como corresponde4
d!op vie" 6Det%lle_vent%s_vendedo!7
d!op vie" 6s$&tot%les_vent%s_vendedo!7
3333333333333333333333333333333333333333333333333333
-
7/26/2019 consultas sql server
51/53
',O/+&II+N0OS2>2/+N2&OS
EJERCICIOS:
/ree los si#uientes S':
>+12=O-&etallePAentas: liste la fecha$ la factura$ el vendedor$ el
cliente$ el art%culo$ cantidad e importe +ste S' recibir! comopar!metros de + un ran#o de fechas
c!e%te p!oced$!e det%lle_vent%
ec#%1%s sm%lld%tetime,
ec#% %s sm%lld%tetime
%s
select /ec#%, /n!o_%ct$, v/nom_vend, c/nom_clie, %/desc!ipcion, d/c%nt,
(d/c%nt?d/p!e_$nit)
!om clientes c, %ct$!%s , vendedo!es v, %!tic$los %, det%lle d
"#e!e /n!o_%ct$ 0 d/n!o_%ct$ %nd c/cod_clie 0 /cod_clie %nd%/cod_%!ti 0 d/cod_%!ti %nd v/cod_vend 0 /cod_vend %nd
ec#% &et"een ec#%1%nd ec#%
eec$te det%lle_vent% '2222','11122K'
/antidad2rtP/li : este S' me debe devolver la cantidad de art%culos o
clientes 3se#5n se pida4 que eGisten en la empresa
c!e%te p!oced$!e C%ntid%dA!t_Clie
opcion1int
%s
&e+in
i (opcion10 1)
select co$nt (?) !om A!tic$los %s A!tic$los
else
select co$nt (?) !om Clientes %s Clientes
end
http://www.taringa.net/fecha1http://www.taringa.net/fecha1http://www.taringa.net/opcion1http://www.taringa.net/opcion1http://www.taringa.net/fecha1http://www.taringa.net/fecha1http://www.taringa.net/opcion1http://www.taringa.net/opcion1 -
7/26/2019 consultas sql server
52/53
eec$te C%ntid%dA!t_Clie '1'
INSPAendedor: /ree un S' que le permita insertar re#istros en la tabla
vendedores
c!e%te p!oced$!e IN-_endedo!es
cod_vend %s int o$tp$t,
nom_vend %s nv%!c#%! (2),
di! %s nv%!c#%! (2),
n!o_tel %s int,
e_m%il%s nv%!c#%! (2),
ec_n%c %s sm%lld%tetime
%s
&e+in
inse!t into vendedo!es (nom_vend, di!eccion, n!o_tel, ec_n%c) v%l$es
(nom_vend, di!, n!o_tel, ec_n%c)
set cod_vend 0 identit
end
decl%!e %%s int
eec$te IN-_endedo!es
%o$tp$t, 'Ped!o Pe!e;', 'L%v%lle=% 2', J2K, n$ll, '22221'
U'&PAendedor: cree un S' que le permita modicar un vendedor
car#ado
c!e%te p!oced$!e FPD_endedo!
cod_vend int,
nom_vend nv%!c#%! (2),
di! nv%!c#%! (2),
n!o_tel int,
co!!eonv%!c#%! (2),
ec_n%c sm%lld%tetime
%s
$pd%te vendedo!esset nom_vend 0 nom_vend,
di!eccion 0 di!,
n!o_tel 0 n!o_tel,
0 co!!eo,
ec_n%c 0 ec_n%c
"#e!e cod_vend 0 cod_vend
http://www.taringa.net/e_mailhttp://www.taringa.net/ahttp://www.taringa.net/ahttp://www.taringa.net/correohttp://www.taringa.net/correohttp://www.taringa.net/e_mailhttp://www.taringa.net/ahttp://www.taringa.net/ahttp://www.taringa.net/correohttp://www.taringa.net/correo -
7/26/2019 consultas sql server
53/53
eec$te FPD_endedo!
1, 'Ped!o Pe!e;', 'L%v%lle=% 2', J2K, n$ll, '22221'
&+>PAendedor: cree un S' que le permita eliminar un vendedor
in#resado
c!e%te p!oced$!e DEL_endedo!es
cod_vend %s int
%s
delete !om vendedo!es
"#e!e cod_vend 0 cod_vend
eec DEL_endedo!es
'1'