Ejercicios SQL – Empresa
La siguiente base de datos almacena información sobre los empleados, clientes, productos, pedidos y departamentos de una empresa:
CLIENTES (cliente_no, nombre, localidad, vendedor_no*, debe, haber, limite_credito) EMPLEADOS (emp_no, apellido, oficio, director*, fecha_alta, salario, comision*, dep_no) PRODUCTOS (producto_no, descripción, precio_actual, stock_disponible)
PEDIDOS (pedido_no, producto_no, cliente_no, unidades, fecha_pedido) DEPARTAMENTOS (dep_no, dnombre, localidad)
La clave primaria de cada relación está formada por los atributos que aparecen subrayados. La información que contienen las relaciones anteriores se describe a continuación:
La relación EMPLEADOS almacena la información referente a los empleados de la empresa: número de empleado (emp_no), apellido (apellido), función que desempeñan en la empresa (oficio), código de su director(director), fecha de alta en la empresa (fecha_alta), sueldo (salario), comisiones (comisión) y número del departamento al que pertencen (dep_no). Esta tabla está relacionada con DEPARTAMENTOS a través del atributo dep_no (número de departamento), y también está relacionada consigo misma mediante los atributos emp_no y director.
La relación DEPARTAMENTO almacena los datos sobre los departamentos de que consta la empresa: número de departamento (dep_no), nombre (nombre) y localidad en que se encuentra (localidad).
La relación CLIENTES almacena los datos de los clientes: número de cliente (cliente_no), nombre (nombre), localidad (localidad), vendedor asignado al cliente (vendedor_no), el debe, el haber y el límite de crédito para ese cliente (debe, haber, limite_credito). Esta tabla se relaciona con EMPLEADOS por medio del atributo vendedor_no que hace referencia al atributo emp_no en EMPLEADOS.
La relación PRODUCTOS almacena los datos de los clientes: número de producto (producto_no), descripción del producto (descripción), precio actual (precio_actual), y stock disponible del producto (stock_disponible).
La relación PEDIDOS almacena información referente a los pedidos realizados por los clientes: número de pedido (pedido_no), número de producto (producto_no), código del cliente que ha realizado el pedido (cliente_no), unidades (unidades), y fecha en que se efectuó el pedido (fecha_pedido). Esta tabla está relacionada con PRODUCTOS mediante el atributo producto_no y con CLIENTES mediante el atributo cliente_no.
Las claves ajenas se muestran a continuación:
CLIENTES EMPLEADOS EMPLEADOS EMPLEADOS
EMPLEADOS DEPARTAMENTO PEDIDOS PRODUCTOS
PEDIDOS CLIENTES vendedor_no director dep_no producto_no cliente_no B : N ; M : C B : N ; M : C B : C ; M : C B : C ; M : C
CLIENTES
cliente_no nombre localidad vendedor_no debe haber limite_credito
101 DISTRIBUCIONES GOMEZ MADRID 7499 0 0 5000
102 LOGITRONICA S.L. BARCELONA 7654 0 0 5000
103 INDUSTRIAS LACTEAS S.A. LAS ROZAS 7844 0 0 10000
104 TALLERES ESTESO S.A. SEVILLA 7654 0 0 5000
105 EDICIONES SANZ BARCELONA 7499 0 0 5000
106 SIGNOLOGIC S.A. MADRID 7654 0 0 5000
107 MARTIN Y ASOCIADOS S.L. ARAVACA 7844 0 0 10000
108 MANUFACTURAS ALI S.A. SEVILLA 7654 0 0 5000
DEPARTAMENTOS
dep_no dnombre localidad
10 CONTABILIDAD BARCELONA 20 INVESTIGACION VALENCIA
30 VENTAS MADRID
40 PRODUCCION SEVILLA
PRODUCTOS
producto_no descripción precio_actual stock_disponible
10 MESA DESPACHO MOD. GAVIOTA 550 50
20 SILLA DIRECTOR MOD. BUFALO 670 25
30 ARMARIO NOGAL DOS PUERTAS 460 20
40 MESA MODELO UNIÓN 340 15
50 ARCHIVADOR CEREZO 1050 20
60 CAJA SEGURIDAD MOD. B222 280 15
70 DESTRUCTORA PAPEL A3 450 25
80 MODULO ORDENADOR MOD. ERGOS 550 25
EMPLEADOS
emp_no apellido oficio director fecha_alta salario comision dep_no
7499 ALONSO VENDEDOR 7698 20/02/1991 1400 400 30 7521 LOPEZ EMPLEADO 7782 08/05/1991 1350 10 7654 MARTIN VENDEDOR 7698 30/12/1991 1500 1600 30 7698 GARRIDO DIRECTOR 7839 01/05/1991 3850 30 7782 MARTINEZ DIRECTOR 7839 09/06/1991 2450 10 7839 REY PRESIDENTE 17/11/1991 6000 10 7844 CALVO VENDEDOR 7698 08/09/1991 1800 0 30 7876 GIL ANALISTA 7782 06/05/1992 3350 20 7900 JIMENEZ EMPLEADO 7782 24/03/1993 1400 20 8998 CORTES VENDEDOR 7698 20/02/2009 1800 30 PEDIDOS
pedido_no producto_no cliente_no unidades fecha_pedido
1000 20 103 3 06/10/2009 1001 50 106 2 06/10/2009 1002 10 101 4 07/10/2009 1003 20 105 4 16/10/2009 1004 40 106 8 20/10/2009 1005 30 105 2 20/10/2009 1006 70 103 3 03/11/2009 1007 50 101 2 06/11/2009 1008 10 106 6 16/11/2009 1009 20 105 2 26/11/2009 1010 40 102 3 08/12/2009 1011 30 106 2 15/12/2009 1012 10 105 3 06/12/2009 1013 30 106 2 06/12/2009 1014 20 101 4 07/01/2010 1015 70 105 4 16/01/2010
Resuelve las siguientes consultas en SQL:
1. Apellido y sueldo incrementado un 10% de los empleados con oficio de vendedor.
2. Apellido, salario, sueldo, comisión y total a cobrar por cada empleado, suponiendo que se trata de sumar su salario a la correspondiente comisión, si la tuviera.
3. Datos de todos aquellos empleados cuya comisión sea nula.
4. Datos de los empleados con oficio de vendedor y salario entre 1750 y 2000 €.
5. Datos de los empleados cuyo oficio no sea el de vendedor, cuyo apellido comience por ‘G’ y que tengan un sueldo superior a 2500 €.
6. Seleccionar los empleados existentes en los departamentos 10 y 30. Mostrar su número de empleado, su apellido, y el número del departamento.
7. Listar los empleados por departamentos en orden descendente de salarios. Mostrar el número de departamento, el apellido y el salario.
8. Obtener los salarios máximo, mínimo y la diferencia existente entre ambos. 9. Calcular el salario medio de los empleados.
10. Obtener los salarios medios por departamento.
11. Obtener cuántos empleados hay en cada departamento.
12. Seleccionar el mayor salario dentro de cada oficio, excluyendo el del presidente. 13. Seleccionar los oficios que tengan dos o más empleados.
14. Seleccionar los oficios que tengan dos o más empleados, cuyo salario supere los 1400 €.
15. Obtener todos los empleados (indicando nº empleado y apellido) con su nombre de departamento y su localidad. 16. Obtener la lista de los empleados con los nombres de sus directores.
17. Obtener los jefes de los empleados cuyo oficio sea el de ‘VENDEDOR’. 18. Listar los empleados de los departamentos diferentes al de ‘VENTAS’.
19. Listar los empleados cuyo salario supere el salario medio. Mostrar su número de departamento, apellido y salario. 20. Mostrar, por orden alfabético, el apellido y número de todos los empleado que tienen el mismo oficio que ‘Alonso’. 21. Obtener información de los empleados que ganan más que cualquier empleado del departamento 30.
22. Visualizar el número de vendedores del departamento de VENTAS.
23. Visualizar la suma de los salarios para cada oficio de los empleados del departamento de VENTAS. 24. Listar, en orden alfabético, aquellos empleados que no trabajen ni en Madrid ni en Barcelona.
25. Listar los nombres de los departamentos que tengan algún empleado con fecha de alta anterior a 1992. 26. Obtener los departamentos y sus nombres, siempre que haya más de un empleado trabajando en ellos. 27. Listar las localidades donde existan departamentos con empleados cuya comisión supere el 10% del salario. 28. Seleccionar aquellos departamentos en los que al menos exista un empleado con comisión.
29. Listar aquellos departamentos en los que todos sus empleados carezcan de información sobre su comisión. 30. Obtener apellido y oficio de los empleados que tienen el mismo oficio y mismo número de departamento que el de
‘INVESTIGACIÓN’.
31. Visualizar el número de departamento con más empleados.
32. Visualizar los números de departamentos en los que el salario medio de sus empleados sea mayor o igual que la media de todos los salarios.
33. Visualizar el departamento con más presupuesto asignado para pagar el salario y la comisión de sus empleados. 34. Visualizar el número de departamento, el oficio y el salario de los oficios con mayor salario de cada departamento.
Enunciados de las consultas SQL y resultados:
1. Apellido y sueldo incrementado un 10% de los empleados con oficio de vendedor.
apellido Salario_incrementado
ALONSO 1540
MARTIN 1650
CALVO 1980
CORTES 1980
2. Apellido, salario, sueldo, comisión y total a cobrar por cada empleado, suponiendo que se trata de sumar su salario a la correspondiente comisión, si la tuviera. apellido salario comision Importe Total
ALONSO 1400 400 1800 LOPEZ 1350 1350 MARTIN 1500 1600 3100 GARRIDO 3850 3850 MARTINEZ 2450 2450 REY 6000 6000 CALVO 1800 0 1800 GIL 3350 3350 JIMENEZ 1400 1400 CORTES 1800 1800
3. Datos de todos aquellos empleados cuya comisión sea nula.
emp_no apellido oficio director fecha_alta salario comision dep_no
7521 LOPEZ EMPLEADO 7782 08/05/91 1350 10 7698 GARRIDO DIRECTOR 7839 01/05/91 3850 30 7782 MARTINEZ DIRECTOR 7839 09/06/91 2450 10 7839 REY PRESIDENTE 17/11/91 6000 10 7876 GIL ANALISTA 7782 06/05/92 3350 20 7900 JIMENEZ EMPLEADO 7782 24/03/93 1400 20 8998 CORTES VENDEDOR 7698 20/02/09 1800 30
4. Datos de los empleados con oficio de vendedor y salario entre 1750 y 2000 €.
emp_no apellido oficio director fecha_alta salario comision dep_no
7844 CALVO VENDEDOR 7698 08/09/91 1800 0 30
8998 CORTES VENDEDOR 7698 20/02/09 1800 30
5. Datos de los empleados cuyo oficio no sea el de vendedor, cuyo apellido comience por ‘G’ y que tengan un sueldo superior a 2500 €.
emp_no apellido oficio director fecha_alta salario comision dep_no
7698 GARRIDO DIRECTOR 7839 01/05/91 3850 30
7876 GIL ANALISTA 7782 06/05/92 3350 20
6. Seleccionar los empleados existentes en los departamentos 10 y 30. Mostrar su número de empleado, su apellido, y el número del departamento.
NºEmpleado apellido departamento
7499 ALONSO 30 7521 LOPEZ 10 7654 MARTIN 30 7698 GARRIDO 30 7782 MARTINEZ 10 7839 REY 10
7. Listar los empleados por departamentos en orden descendente de salarios. Mostrar el número de departamento, el apellido y el salario.
dpto apellido salario
10 REY 6000 10 MARTINEZ 2450 10 LOPEZ 1350 20 GIL 3350 20 JIMENEZ 1400 30 GARRIDO 3850 30 CORTES 1800
8. Obtener los salarios máximo, mínimo y la diferencia existente entre ambos.
maximo minimo diferencia
6000 1350 4650
9. Calcular el salario medio de los empleados.
Salario Medio 2490
10. Obtener los salarios medios por departamento. Dpto SalarioDpto
30 2070
10 3266,67
20 2375
11. Obtener cuántos empleados hay en cada departamento. Dpto Empleados
30 5
10 3
20 2
12. Seleccionar el mayor salario dentro de cada oficio, excluyendo el del presidente.
oficio Mayor Salario Oficio
VENDEDOR 1800
EMPLEADO 1400
DIRECTOR 3850
ANALISTA 3350
13. Seleccionar los oficios que tengan dos o más empleados.
oficio empleados
VENDEDOR 4
EMPLEADO 2
DIRECTOR 2
14. Seleccionar los oficios que tengan dos o más empleados, cuyo salario supere los 1400 €. oficio empleados
VENDEDOR 3
DIRECTOR 2
15. Obtener todos los empleados (indicando nº empleado y apellido) con su nombre de departamento y su localidad. emp_no apellido dnombre localidad
7499 ALONSO VENTAS MADRID
7521 LOPEZ CONTABILIDAD BARCELONA
7654 MARTIN VENTAS MADRID
7698 GARRIDO VENTAS MADRID
7782 MARTINEZ CONTABILIDAD BARCELONA
7839 REY CONTABILIDAD BARCELONA
7844 CALVO VENTAS MADRID
7876 GIL INVESTIGACION VALENCIA
7900 JIMENEZ INVESTIGACION VALENCIA
8998 CORTES VENTAS MADRID
16. Obtener la lista de los empleados con los nombres de sus directores.
NºEmpleado apellido NºDirector NombreDirector
7499 ALONSO 7698 GARRIDO 7521 LOPEZ 7782 MARTINEZ 7654 MARTIN 7698 GARRIDO 7698 GARRIDO 7839 REY 7782 MARTINEZ 7839 REY 7844 CALVO 7698 GARRIDO 7876 GIL 7782 MARTINEZ 7900 JIMENEZ 7782 MARTINEZ 8998 CORTES 7698 GARRIDO
NºEmpleado apellido NºDirector NombreDirector
7499 ALONSO 7698 GARRIDO
7654 MARTIN 7698 GARRIDO
7844 CALVO 7698 GARRIDO
8998 CORTES 7698 GARRIDO
18. Listar los empleados de los departamentos diferentes al de ‘VENTAS’. NºEmpleado apellido 7521 LOPEZ 7782 MARTINEZ 7839 REY 7876 GIL 7900 JIMENEZ
19. Listar los empleados cuyo salario supere el salario medio. Mostrar su número de departamento, apellido y salario.
Nºdpto apellido salario
30 GARRIDO 3850
10 REY 6000
20 GIL 3350
20. Mostrar, por orden alfabético, el apellido y número de todos los empleado que tienen el mismo oficio que ‘Alonso’.
Nºempleado apellido oficio
7499 ALONSO VENDEDOR
7654 MARTIN VENDEDOR
7844 CALVO VENDEDOR
8998 CORTES VENDEDOR
21. Obtener información de los empleados que ganan más que cualquier empleado del departamento 30. Nºempleado apellido oficio salario
7839 REY PRESIDENTE 6000
22. Visualizar el número de vendedores del departamento de VENTAS.
Nº Vendedores 4
23. Visualizar la suma de los salarios para cada oficio de los empleados del departamento de VENTAS.
oficio TotalSalarios
VENDEDOR 6500
DIRECTOR 3850
24. Listar, en orden alfabético, aquellos empleados que no trabajen ni en Madrid ni en Barcelona.
Nºempleado apellido NºDpto oficio
7876 GIL 20 ANALISTA
7900 JIMENEZ 20 EMPLEADO
25. Listar los nombres de los departamentos que tengan algún empleado con fecha de alta anterior a 1992.
dnombre CONTABILIDAD
VENTAS
26. Obtener los departamentos y sus nombres, siempre que haya más de un empleado trabajando en ellos.
dep_no dnombre
10 CONTABILIDAD
20 INVESTIGACION
27. Listar las localidades donde existan departamentos con empleados cuya comisión supere el 10% del salario.
localidad MADRID
28. Seleccionar aquellos departamentos en los que al menos exista un empleado con comisión.
dep_no dnombre localidad
30 VENTAS MADRID
29. Listar aquellos departamentos en los que todos sus empleados carezcan de información sobre su comisión.
NºDpto Departamento
10 CONTABILIDAD
20 INVESTIGACION
40 PRODUCCION
30. Obtener apellido y oficio de los empleados que tienen el mismo oficio y mismo número de departamento que el de
‘INVESTIGACIÓN’.
apellido oficio LOPEZ EMPLEADO
GIL ANALISTA
JIMENEZ EMPLEADO
31. Visualizar el número de departamento con más empleados.
NºDpto TotalEmpleados
30 5
32. Visualizar los números de departamentos en los que el salario medio de sus empleados sea mayor o igual que la media de todos los salarios.
NºDpto 10
33. Visualizar el departamento con más presupuesto asignado para pagar el salario y la comisión de sus empleados. NºDpto
30
34. Visualizar el número de departamento, el oficio y el salario de los oficios con mayor salario de cada departamento. dep_no oficio salario
30 DIRECTOR 3850
10 PRESIDENTE 6000