Bases de Datos

Bases de Datos
Ejercicios de SQL

1. Descripción del modelo de datos

El modelo de datos que se usará para las consultas está reflejado en el siguiente modelo E-R.

Transformado a relacional, la base de datos almacena cuatro tablas: DEPT (Departamento), EMP
(Empleado), PRO (Proyecto) y EMPPRO (Empleados que trabajan en proyectos) cuyos campos se des-
criben a continuación.
DEPT
Campo Tipo Descripción
DEPTNO NUMBER(2) NOT NULL Número o código del departamento.
Es la clave primaria de la tabla.
DNAME VARCHAR2(14) Nombre del departamento.
LOC VARCHAR2(13) Localidad (o ciudad) donde el departamento está ubicado.

EMP
Campo Tipo Descripción
EMPNO: NUMBER(4) NOT NULL Número o código del empleado.
Es la clave primaria de la tabla.
ENAME VARCHAR2(10) Nombre del empleado
JOB VARCHAR2(9) Trabajo del empleado
MGR NUMBER(4) Código del jefe del empleado.
Clave foránea que referencia (cı́clicamente) la tabla EMP
HIREDATE DATE Fecha de contratación.
SAL NUMBER(7, 2) Salario mensual del empleado
COMM NUMBER(7, 2) Comisión
DEPTNO NUMBER(2) Código del departamento al que el empleado está adscrito.
Clave foránea que referencia la tabla DEPT

Pág. 1/13
 Bases de Datos

PRO
Campo Tipo Descripción
PRONO NUMBER(4) NOT NULL Número o código del Proyecto.
Es la clave primaria de la tabla.
PNAME VARCHAR2(10) Nombre del proyecto.
LOC VARCHAR2(13) Ciudad donde se realiza el proyecto.
DEPTNO NUMBER(2) Número del departamento controlador del proyecto.
Clave foránea que referencia la tabla DEPT
EMPPRO
Campo Tipo Descripción
EMPNO NUMBER(4) NOT NULL Número o código del empleado.
Clave foránea que referencia la tabla EMP
PRONO NUMBER(4) NOT NULL Número o código del proyecto.
Clave foránea que referencia la tabla PRO
Estos dos atributos son la clave de la tabla.
HOURS NUMBER(2) Horas que ha trabajado un empleado en un proyecto.

Notas
La base de datos viene en parte de un ejemplo que Oracle incorpora en su instalación, por lo
que tanto los nombres de los campos como sus valores están en inglés. Ası́, un vendedor tendrá en el
atributo JOB el valor SALESMAN, o el departamento de investigación es RESEARCH. Además, todos los
nombres (de empleado, departamento, trabajo) están almacenados en mayúsculas.
En cuanto a la definición de las tablas, aunque todos los campos excepto las claves primarias
admiten valores nulos, consideraremos especialmente los siguientes casos:
COMM: Si tiene un valor nulo, indica que el empleado no tiene comisión.
MGR: Si tiene un nulo, indica que el empleado no tiene jefe.

2. Datos almacenados en las tablas

Tabla DEPT:
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Tabla EMP:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 <Nulo> 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 <Nulo> 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 <Nulo> 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 <Nulo> 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 <Nulo> 20
7839 KING PRESIDENT <Nulo> 17-NOV-81 5000 <Nulo> 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 <Nulo> 20
7900 JAMES CLERK 7698 03-DEC-81 950 <Nulo> 30
7902 FORD ANALYST 7566 03-DEC-81 3000 <Nulo> 20
7934 MILLER CLERK 7782 23-JAN-82 1300 <Nulo> 10

Pág. 2
 Bases de Datos

Tabla PRO:

PRONO PNAME LOC DEPTNO

---------- ---------- ------------- ------
1001 P1 BOSTON 20
1004 P4 CHICAGO 30
1005 P5 CHICAGO 30
1006 P6 LOS ANGELES 30
1008 P8 NEW YORK 30

Tabla EMPPRO:

EMPNO PRONO HOURS

----- ---------- ----------
7499 1004 15
7499 1005 12
7521 1004 10
7521 1008 8
7654 1001 16
7654 1006 15
7654 1008 5
7844 1005 6
7934 1001 4

Pág. 3
 Bases de Datos

1. Halla los empleados que tienen una comisión superior a la mitad de su salario.

select ename
from emp
where comm > sal/2

2. Halla los empleados que no tienen comisión, o que la tengan menor o igual que el 25 % de su
salario.

select ename
from emp
where comm is null or comm <= sal*0.25

3. Obtén los empleados que no son supervisados por ningún otro.

select ename
from emp
where mgr is null

4. Para los empleados que tengan comisión, obtén sus nombres y el cociente entre su salario y su
comisión (excepto cuando la comisión sea cero), ordenando el resultado por nombre.

select ename, sal/comm

from emp
where comm is not null and comm<>0
order by ename

5. Para los empleados que tengan como jefe a un empleado con código mayor que el suyo, obtén
los que reciben de salario más de 1000 y menos de 2000, o que están en el departamento 30.

select ename
from emp
where mgr>empno and (sal between 1001 and 1999 or deptno=30)

6. Obtén el nombre, salario, comisión y salario total (salario+comisión, si tiene comisión) de los
empleados con salario total superior a 2300.

select ename, sal, comm, coalesce(sal+comm,sal) salario_total

from emp
where coalesce(sal+comm,sal)>2300

7. Obtén los puestos de trabajo que hay en cada departamento, de forma que no se repitan filas.

select distinct job, deptno

from emp

8. Obtén el salario más alto de la empresa, el total destinado a comisiones y el número de empleados.

select max(sal),coalesce(sum(comm), 0), count(*)

from emp

Pág. 4
 Bases de Datos

9. Halla el nombre del último empleado por orden alfabético.

select max(ename) from emp

10. Halla el salario más alto, el más bajo, y la diferencia entre ellos.

select max(sal), min(sal), max(sal)-min(sal) from emp

11. ¿Cuántos empleos diferentes, cuántos empleados, y cuántos salarios diferentes encontramos en
el departamento 30, y a cuánto asciende la suma de salarios de dicho departamento?

select count(distinct job),count(*), count(distinct sal), sum(sal)

from emp
where deptno=30

12. ¿Cuántos empleados tiene el departamento 20?

select count(*)
from emp
where deptno=20

13. ¿Cuántos empleados tienen comisión?

select count(comm)
from emp

14. ¿Qué empleos distintos encontramos en la empresa, y cuántos empleados desempeñan cada uno
de ellos?

select job, count(*) from emp

group by job

15. Halla la suma de salarios de cada departamento, junto con el código del departamento.

select deptno, sum(sal) from emp

group by deptno

16. Para cada departamento muestra cuántos proyectos controla, junto con el código del departa-
mento.

select deptno, count(*)

from pro
group by deptno

17. Muestra los proyectos en los que trabajan al menos tres empleados y cuántas horas trabajan en
dicho proyectos.

select prono, count(*), sum(hours)

from emppro
group by prono
having count(*)>=3

Pág. 5
 Bases de Datos

18. Para cada departamento muestra cuántos proyectos controla en cada ciudad.

select deptno, loc, count(*)

from pro
group by deptno, loc

19. Para cada departamento muestra cuántos empleados tiene que ganen más de 1500.

select deptno, count(*)

from emp
where sal > 1500
group by deptno

20. Muestra los departamentos que tienen un salario mı́nimo mayor o igual a 1000. Muestra sus
códigos y cuántos empleados tiene.

select deptno, count(*)

from emp
group by deptno
having min(sal)>=1000

21. Muestra los departamentos y los trabajos donde hay por lo menos dos trabajadores con ese
puesto de trabajo.

select deptno, job

from emp
group by job, deptno
having count(*)>=2

22. Halla los datos de los empleados cuyo salario es mayor que el del empleado de código 7934,
ordenando por el salario.

select * from emp

where sal>(select sal from emp where empno=7934)
order by sal

23. Obtén los empleados que trabajan en Dallas o New York.

select ename from emp

where deptno in (select deptno
from dept
where loc in (’DALLAS’,’NEW YORK’))

24. Halla los empleados cuyo salario supera o coincide con la media del salario de la empresa.

select ename from emp

where sal >= (select avg(sal)
from emp)

25. Obtén los empleados del departamento 10 que tienen el mismo empleo que alguien del departa-
mento de Ventas. Desconocemos el código de dicho departamento.

Pág. 6
 Bases de Datos

select ename from emp

where deptno=10 and job in (select job
from emp
where deptno=(select deptno from dept where dname=’SALES’))

26. Halla los empleados que tienen por lo menos un empleado a su mando, ordenados inversamente
por nombre.

select ename from emp

where empno in (select mgr
from emp)
order by ename desc

27. Halla los empleados que no tienen ningún empleado a su mando

select ename from emp

where empno not in (select mgr
from emp
where mgr is not null)

Si hay un nulo en el conjunto, el not in devuelve ”desconocido”

28. Obtén todos los departamentos sin empleados.

select dname from dept

where deptno not in (select deptno
from emp)

29. Muestra el código del empleado o empleados que más horas trabaja(n) en cada proyecto.

select empno, prono from emppro a

where hours >= (select max(hours)
from emppro
where prono=[Link])

30. Obtén el empleado que tiene el mayor salario de cada departamento, pero considerando que si en
algún departamento hay varios empleados con el mismo salario máximo, no se mostrará ningún
empleado

select ename, deptno from emp a

where sal > (select max(sal)
from emp
where deptno=[Link]
and empno<>[Link])

31. Para cada puesto de trabajo el/los empleados que más ganan.

select ename, job from emp a

where sal = (select max(sal)
from emp
where job=[Link])

Pág. 7
 Bases de Datos

32. ¿Qué empleados trabajan en ciudades de más de seis letras? Ordena el resultado inversamente
por ciudades y normalmente por los nombres de los empleados.

select ename, loc

from emp e join dept d on [Link]=[Link]
where length(loc)>6
order by loc desc,ename

33. Para cada empleado muestra los proyectos en los que trabaja. Muestra el nombre del empleado
y el nombre del proyecto.

select ename, pname

from pro p join emppro ep on [Link]=[Link] join emp e on [Link]=[Link]

34. Muestra los proyectos controlados por cada departamento. Muestra el nombre del departamento
y el nombre del proyecto. Deben aparecer todos los departamentos, incluso si alguno no controla
ningún proyecto.

select dname, pname

from dept d left join pro p on [Link]=[Link]

35. Obtén un listado en el que se reflejen los empleados y los nombres de sus jefes. En el listado
deben aparecer todos los empleados, aunque no tengan jefe.

select [Link], [Link]

from emp e left join emp j on [Link]=[Link]

36. Los nombres de empleados contratados antes que su jefe.

select [Link]
from emp e join emp j on [Link]=[Link]
where [Link]<[Link]

37. Para cada departamento, muestra los empleados que trabajan en proyectos controlados por él.
Muestra el nombre del departamento y el código de los empleados.

select [Link], [Link]

from dept d join pro p on [Link]=[Link] join emppro ep on [Link]=[Link]

38. Obtén el código de empleado, el nombre, el salario, el código del proyecto y las horas que le
dedica cada empleado vinculado a algún proyecto, ordenado por el código del empleado.

select [Link], [Link], sal, prono, hours

from emp e join emppro ep on [Link]=[Link]
order by [Link]

39. ¿Cuántos empleados hay en cada departamento, y cuál es la media del salario de cada uno?
Indique el nombre del departamento para clarificar el resultado.

select dname, count(*), avg(sal)

from emp e join dept d on [Link]=[Link]
group by [Link], dname

Pág. 8
 Bases de Datos

40. Muestra la suma de salarios de los empleados de cada departamento que tienen un salario superior
al salario medio de la empresa. Muestra el nombre del departamento.

select dname, sum(sal)

from emp e join dept d on [Link]=[Link]
where sal > (select avg(sal) from emp)
group by [Link], dname

41. Para cada proyecto controlado por el departamento 30, indica su número, ciudad, número de em-
pleados participantes en el proyecto, las horas dedicadas por el empleado que más ha trabajado,
y las horas dedicadas por el que menos ha trabajado, y la diferencia entre ellas

select [Link], loc, count(*), max(hours), min(hours), max(hours)-min(hours)

from pro p join emppro ep on [Link]=[Link]
where deptno=30
group by [Link], loc

42. Por cada departamento muestra el total de horas trabajadas en proyectos por los empleados de
cada puesto de trabajo. Muestra el código de departamento y el nombre del puesto de trabajo

select [Link], job, sum(hours)

from emp e join emppro ep on [Link]=[Link]
group by [Link],job

43. Para cada empleado muestra su nombre y cuántos empleados supervisa de cada puesto de tra-
bajo.

select [Link], [Link], count([Link])

from emp e join emp j on [Link]=[Link]
group by [Link],[Link], [Link]

44. Muestra para cada proyecto cuántas horas trabajan en total todos los empleados que tienen un
salario superior al salario medio de la empresa. Muestra el nombre del proyecto.

select pname, sum(hours)

from pro p join emppro ep on [Link]=[Link] join emp e on [Link]=[Link]
where sal>(select avg(sal) from emp)
group by [Link], pname

45. Para cada departamento, muestra su código, su nombre, el salario más alto y más bajo que
cobran sus empleados, la diferencia entre estos dos salarios, y el número de proyectos a cargo
del departamento.

select dname, [Link], count(distinct prono), max(sal), min(sal), max(sal)-min(sal)

from pro p join dept d on [Link]=[Link] join emp e on [Link]=[Link]
group by dname, [Link]

46. Considerando empleados con salario menor de 5000, halla la media de los salarios de los de-
partamentos cuyo salario mı́nimo supera a 900. Muestra también el código y el nombre de los
departamentos.

Pág. 9
 Bases de Datos

select [Link], dname, avg(sal)

from emp e join dept d on [Link]=[Link]
where sal>5000
group by [Link], dname
having min(sal)>900

47. Lista los empleados que tengan el mayor salario de su departamento, mostrando el nombre del
empleado, su salario y el nombre del departamento.

select ename, sal, dname

from emp e join dept d on [Link]=[Link]
where [Link] >= (select max(sal)
from emp
where deptno=[Link])

48. El puesto de trabajo con el salario medio más alto.

select job
from emp a
group by job
having avg(sal)>=ALL(select avg(sal)
from emp
group by job)

49. Para cada supervisor, muestra su subordinado(s) que más gana.

select [Link] jefe, [Link] subordinado, [Link]

from emp e join emp j on [Link]=[Link]
where [Link] >= (select max(sal)
from emp
where mgr=[Link])

50. Deseamos saber cuántos empleados supervisa cada jefe. Para ello, obtén un listado en el que
se reflejen el código y el nombre de cada jefe, junto al número de empleados que supervisa
directamente. Como puede haber empleados sin jefe, para estos se indicará sólo el número de
ellos, y los valores restantes (código y nombre del jefe) se dejarán como nulos.

select [Link], [Link], count(*)

from emp e left join emp j on [Link]=[Link]
group by [Link], [Link]

51. Hallar el/los departamento(s) cuya suma de salarios sea la más alta, mostrando esta suma de
salarios y el nombre del departamento

select [Link], [Link], sum(sal)

from emp e join dept d on [Link]=[Link]
group by [Link], [Link]
having sum(sal)>=ALL(select sum(sal)
from emp
group by deptno)

52. Obtén un listado de todos los empleados (código, nombre) donde aparezca el total de horas
dedicado a proyectos. Deben aparecer todos los empleados aunque no trabajen en proyectos.

Pág. 10
 Bases de Datos

select [Link], ename, sum(hours)

from emp e left join emppro ep on [Link]=[Link]
group by [Link], ename

53. Nombre del departamento(s) que tiene(n) el mayor número de supervisores.

select dname
from emp e join dept d on [Link]=[Link]
where [Link] in (select mgr from emp)
group by [Link], dname
having count(*) >=ALL (select count(*)
from emp
where empno in (select mgr from emp)
group by deptno)

54. Nombres de empleados que trabajan solos en algún proyecto

select ename
from emp e join emppro ep on [Link]=[Link]
where prono in (select prono from emppro
group by prono
having count(empno)=1)

55. Para cada empleado, número, nombre y contar cuantos ganan menos que él (si no hay ninguno,
debe aparecer un 0)

select [Link], [Link], count([Link])

from emp a left join emp b on [Link] > [Link]
group by [Link], [Link]

56. Para cada empleado, número, nombre y contar cuantos (descontando a él mismo) ganan lo mismo
o menos que él (si no hay ninguno, debe aparecer un 0)

select [Link], [Link], count([Link])

from emp a left join emp b on [Link] >= [Link] and [Link]<>[Link]
group by [Link], [Link]

57. Para cada jefe mostrar cuántos empleados supervisa en cada departamento (los empleados su-
pervisados no tienen porque ser del mismo departamento que el supervisor). Mostrar nombre de
empleado y código departamento (de los empleados supervisados).

select [Link], [Link], count([Link])

from emp e join emp j on [Link]=[Link]
group by [Link], [Link], [Link]

58. Idem mostrando todos los empleados, y en aquellos que no son jefes, mostrando un cero en el
número de empleados supervisados.

select [Link], [Link], count([Link])

from emp j left join emp e on [Link]=[Link]
group by [Link], [Link], [Link]

Pág. 11
 Bases de Datos

59. Para cada departamento que tenga, por lo menos, dos empleados sin comisión, muestra el nombre
del departamento, y cuántos empleados tiene en total (con y sin comisión).

select dname, count(*), count(*)-count(comm)

from emp e join dept d on [Link]=[Link]
group by [Link], dname
having count(*)-count(comm)>1

60. ¿Cuál es la ciudad (o ciudades, si hay más de una) en la que hasta el momento se han trabajado
más horas en proyectos? Indica el nombre de la ciudad, y el número de horas.

select loc, sum(hours)

from emppro ep join pro p on [Link]=[Link]
group by loc
having sum(hours)>=ALL(select sum(hours)
from emppro ep join pro p on [Link]=[Link]
group by loc)

61. Muestra para cada proyecto su código y, de los empleados que trabajan en dicho proyecto, el
nombre del empleado que más gana de cada puesto de trabajo.

select prono, ename, job

from emp e join emppro t on [Link]=[Link]
where sal= (select max(sal) from emp e1 join emppro t1 on [Link]=[Link]
where [Link]=[Link] and [Link]=[Link])

62. Para cada empleado, obtén su código y su nombre, y el código y nombre del proyecto al que
dedica más horas ese empleado. Muestra también las horas, y ordena el resultado por nombre
de empleado.

select [Link], ename, [Link], pname, hours

from emp e
join emppro t on [Link]=[Link]
join pro p on [Link]=[Link]
where hours=(select max(hours) from emppro where empno=[Link])
order by ename

63. Considerando sólo los empleados que tienen el máximo salario de cada puesto de trabajo, muestra
para cada departamento su nombre y cuántos de dichos empleados trabajan en ese departamento.

select dname, count(*)

from emp e join dept d on [Link]=[Link]
where sal=(select max(sal) from emp where job=[Link])
group by [Link], dname

64. Para cada empleado muestra su nombre, cuántos empleados supervisa en total, cuántos con
comisión y cuántos sin comisión. Si el empleado no supervisa a nadie, o no supervisa empleados
sin/con comisión, se debe mostrar un cero en el número correspondiente.

select [Link], count ([Link]) total, count([Link]) comisión,

count([Link])-count([Link]) sin
from emp e right join emp j on [Link]=[Link]
group by [Link], [Link]

Pág. 12
 Bases de Datos

65. Muestra el nombre y trabajo de los empleados que son supervisores y que tienen el mismo trabajo
que todos sus subordinados

select ename,job
from emp e
where empno in (select mgr from emp)
and not exists (select * from emp where mgr=[Link] and job<>[Link])

66. Para cada departamento muestra cuántos jefes tiene y cuántos subordinados tienen esos jefes
(los subordinados no tienen porque ser del mismo departamento que el jefe). Deben aparecer
todos los departamentos.

select dname, count(distinct [Link]), count([Link])

from (emp e join emp x on [Link]=[Link]) right join dept d on [Link]=[Link]
group by [Link], dname

67. Muestra el nombre de los departamentos con más de tres empleados de los cuales al menos dos
son jefes

select dname
from emp e join dept d on [Link]=[Link]
where (select count(*)
from emp
where empno in (select mgr from emp)
and deptno=[Link])>=2
group by [Link], dname
having count(*)>3

68. Para cada jefe, muestra su nombre y cuántos empleados supervisa en departamentos diferentes
al suyo. Si un jefe no supervisa a ningún empleado de otro departamento, muestra un cero.

select [Link],count([Link])
from emp e right join emp j on [Link]=[Link] and [Link]<>[Link]
where [Link] in (select distinct mgr from emp)
group by [Link],[Link]

Pág. 13