Consultas SQL sobre las tablas emp y dept: ejemplos prácticos

A continuación, se presentan una serie de consultas SQL con el objetivo de ilustrar diferentes funcionalidades y casos de uso. Cada consulta está acompañada de una breve descripción de su propósito.

Consultas Básicas

Consulta 1:

SELECT ename, sal FROM emp WHERE sal > 1000 AND sal <>

1. Ordenado alfabéticamente en descendente.

Consulta 2:

SELECT ename, sal FROM emp WHERE sal > 1000 AND sal <>

1. Nº empleado, nombre, profesión y salario de aquellos empleados con salario igual o superior a 1100€ o que su puesto sea clerk.

Consulta 3:

SELECT empno, ename, job, sal FROM emp WHERE sal > 1100 OR job = 'CLERK'

1. Nombre y oficio de todos los empleados que no tienen jefe.

Consulta 4:

SELECT ename, job FROM emp WHERE MGR IS NULL

1. El nombre de todos los empleados que tienen comisión, incluidos los que tienen comisión cero, ordenado por salario descendente y, si el salario coincide, en ascendente por comisión.

Consulta 5:

SELECT ename, sal, comm FROM emp WHERE comm IS NOT NULL ORDER BY sal DESC, comm ASC

1. Nombre de los empleados que tengan una 'A' en la tercera posición del nombre.

Consulta 6:

SELECT ename FROM emp WHERE ename LIKE '__A%'

Nombre, salario y comisión de los empleados que tienen una comisión mayor que el salario incrementado en un 40%.

Consulta 7:

SELECT ename, sal, comm FROM emp WHERE comm > sal * 1.4

Cuando se incrementa es 1,(el incremento) -> 1.10

1. Obtener nombre de los empleados con la primera letra en mayúscula y el resto en minúscula.

Consulta 8:

SELECT initcap(ename) FROM emp

1. Obtener todos los empleados que tienen como oficio ‘salesman’ que tengan sensibilidad a mayúsculas. (Qué función hay que utilizar para que me lo coja como mayúsculas).

Consulta 9:

SELECT ENAME FROM EMP WHERE JOB LIKE UPPER('salesman')

1. Mostrar las 2 últimas letras de los nombres de los empleados.

Consulta 10:

SELECT SUBSTRING(Nombre, 0, LEN(Nombre)-2) AS SubCadena FROM Usuario

1. Obtener nombre, longitud del nombre de los empleados y en qué posición está el carácter 'A' en el nombre.

Consulta 11:

SELECT ename, LENGTH(ename), INSTR(ename,'A') FROM emp

1. Concatenación de dos columnas:

Consulta 12:

SELECT ename || job FROM emp

1. Concatenación:

Consulta 13:

SELECT ename ||'-'|| job FROM emp

1. Visualizar los nombres de los empleados en un campo de 10 y por la izquierda rellenado por asteriscos.

Consulta 14:

SELECT LPAD(ename,10,'*') FROM emp

1. Una consulta para visualizar el salario de los empleados, el promedio, el salario mínimo, máximo, la suma de todos los salarios y el nº de empleados que hay.

Consulta 15:

SELECT SUM(sal), MIN(sal), MAX(sal), Round(AVG(sal),2) , COUNT(ename) FROM emp

1. ¿Cuántos empleados trabajan en el departamento técnico?

Consulta 16:

SELECT count(*) FROM emp WHERE DEPTNO = 30

1. Consulta para que devuelva el nº de departamentos distintos que hay en la tabla emp.

Consulta 17:

SELECT count(DISTINCT(DEPTNO)) FROM emp WHERE DEPTNO = 30

1. Consulta de todos los trabajadores que reciben comisión agrupados por el código de departamento.

Consulta 18:

SELECT DEPTNO, SUM(SAL) FROM EMP WHERE COMM IS NOT NULL GROUP BY DEPTNO

1. Consulta de los números de departamento agrupadas por el salario.

Consulta 19:

SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO

Obtener el salario máximo por departamento.

Consulta 20:

SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO

1. Obtener el salario máximo por departamento, pero solo de aquellos cuyo máximo sea superior a 2900.

Consulta 21:

SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO HAVING MAX(SAL) > 2900

1. Suma de salarios para cada oficio.

Consulta 22a:

SELECT SUM(SAL) FROM EMP GROUP BY JOB

22b. Suma de salarios para cada oficio excluyendo los que empiecen por 'SA'.

Consulta 22b:

SELECT JOB, SUM(SAL) FROM EMP WHERE SUBSTR(JOB,1,2)<>'sa' GROUP BY JOB

22c. Suma de salarios para cada oficio excluyendo los que empiecen por 'SA' y aquellos cuya suma sea inferior a 5000.

Consulta 22c:

SELECT job, SUM(SAL) FROM EMP WHERE SUBSTR(JOB,1,2)<> 'SA' GROUP BY JOB HAVING SUM(SAL) >= 5000

1. Visualizar nombre de los oficios junto a la suma de salarios, media de salarios, salario máximo y mínimo.

Consulta 23:

SELECT JOB, SUM(SAL), AVG(SAL), MAX(SAL), MIN(SAL) FROM EMP GROUP BY JOB

1. Nombre de empleados cuyo departamento contenga una 's' en el nombre.

Consulta 24:

SELECT DNAME, COUNT(EMPNO) FROM DEPT, EMP WHERE DEPT.DEPTNO=EMP.DEPTNO AND DNAME LIKE '%S%' GROUP BY DNAME

1. Consulta 25:

SELECT DNAME, ENAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO

1. Me saca aquellos que estén emparejados y los que no en un nuevo departamento.

Consulta 26:

SELECT DNAME, ENAME FROM DEPT D, EMP E WHERE D.DEPTNO = E.DEPTNO (+)

Ejemplo seleccionar columnas de tablas (REPASO), estamos haciendo una proyección y luego una restricción.

SELECT ENAME, SAL FROM EMP WHERE COMM IS NULL

O varias tablas, hay que definir una como clave ajena y otra como clave primaria

SELECT ENAME, SAL, DNAME FROM EMP, DEPT WHERE COMM IS NULL

Te da todas aquellas columnas que coinciden en ambas tablas.

SELECT ENAME, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO

Lo podemos ordenar por la segunda columna, bien poniendo el nombre la columna o el nº de la columna.

SELECT ENAME, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO ORDER BY 2

También podemos utilizar ALIAS.

SELECT ENAME, DNAME FROM EMP A, DEPT B WHERE A.DEPTNO = B.DEPTNO ORDER BY 2

CLAUSULAS:

GROUP BY: FILAS AGRUPADAS EN BASE A UN CRITERIO, UN VALOR QUE SE REPITA.

HAVING: EQUIVALENTE AL WHERE, PARA QUITAR FILAS DEL AGRUPAMIENTO.

Ej: Quiero saber la suma de salarios para cada uno de los oficios, nombre y suma de los salarios.

SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB

Mismo enunciado excluyendo aquellas que sean mayores de 5000.

SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL) <=>=>

El where se utiliza para excluir tablas, y el having columnas una vez agrupadas.

Suma de salario para cada uno de los departamentos,

SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO

Suma de los salarios para cada uno de los departamentos visualizando el nombre de los departamentos.

SELECT DNAME, sum(SAL) FROM EMP, dept WHERE EMP.DEPTNO = DEPT.DEPTNO group by DNAME

SELECT ENAME ||’*’|| FROM EMP

Las dos barras concatenan filas, esta por ejemplo junta el nombre de los empleados con su puesto.

Podemos trabajar con la misma tabla desdoblada tantas veces como quiera, simplemente usando alias diferentes. Tienen que tener alguna columna en común y hay que establecer ahí la relación. Entonces la clave ajena de “jefes” apunta a la clave primaria “código de empleados” porque ambos son empleados al fin y al cabo.

SELECT A.ENAME||' TRABAJA PARA '||B.ENAME FROM EMP A, EMP B WHERE A.MGR = B.EMPNO

Hacer una consulta del menor salario de los empleados que dependan de esos jefes.

1. Consulta 27:

select mgr,min(sal) from emp GROUP BY mgr

b) (Solo para los empleados que tengan jefe) Para quitar la fila con un guion :

Consulta 28:

select mgr,min(sal) from emp where mgr is not null GROUP BY mgr

c) solo nos interesa ver los mínimos superiores a mil.

Consulta 29:

select mgr,min(sal) from emp where mgr is not null GROUP BY mgr having min(SAL) > 1000

d) que nos de también el nombre del jefe:

Consulta 30:

select A.ENAME, min(B.SAL) from emp A, emp B where A.EMPNO=B.MGR AND B.MGR IS NOT NULL GROUP BY A.ENAME having min(B.SAL) > 1000

36. NOMBRE DE LOS EMPLEADOS, EL OFICIO, EL NOMBRE DEL DEPARTAMENTO DE TODOS LOS EMPLEADOS QUE TRABAJAN EN DALLAS

Consulta 31:

SELECT ENAME, JOB, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND LOC LIKE 'DALLAS'

37. Obtener nombre de los empleados que tienen un salario igual a cualquiera de los salarios mínimos de cada uno de los departamentos.

Consulta 32:

SELECT ENAME FROM EMP WHERE SAL = ANY (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);

38. Obtener el nombre de los empleados que tienen el mismo oficio que el empleado 7369 y cuyo salario sea superior al del empleado ADAMS.

Consulta 33:

SELECT ENAME, JOB FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7369 ) AND SAL > (SELECT SAL FROM EMP WHERE ENAME ='ADAMS')

39. Obtener nombre y oficio de los empleados con un salario igual al salario mínimo de la empresa.

Consulta 34:

SELECT ENAME, JOB FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP)

40. Obtener el nombre y el oficio de los empleados con un salario igual al mínimo de los salarios de cada departamento

Consulta 35:

SELECT ENAME, JOB FROM EMP WHERE SAL = IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO)

41. Vamos a obtener el código de departamento que tienen un salario mínimo mayor que el mínimo del departamento 20

Consulta 36:

SELECT DEPTNO, MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING MIN(SAL) > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 20)

41b. Para consultar el nombre de los departamentos a partir del anterior.

Consulta 37:

SELECT DNAME, MIN(SAL) FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO GROUP BY DNAME HAVING MIN(SAL) > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 20)

42. El oficio con la media de los salarios más bajos

Consulta 38:

SELECT JOB, avg(SAL) FROM EMP GROUP BY job HAVING AVG(SAL) = (SELECT min(AVG(SAL)) from emp group by job)

43. Vamos a visualizar qué empleados ganan igual a cualquiera de los mínimos de cada departamento.

Consulta 39:

SELECT ename FROM EMP WHERE sal in (SELECT min(SAL) from emp group by deptno)

Esquema de varias tablas.

44. Nombre de los responsables de los departamento con sus departamentos.

Consulta 40:

select first_name,department_name from employees e, departments d where e.employee_id=d.manager_id

45. Nombre de los empleados con los nombres de sus oficios.

Consulta 41:

select first_name,JOB_TITLE from employees e, JOBS d where e.job_id=d.job_id

Para el Nuevo usuario RASTY2 y tras importar las tablas.

Insertar Un empleado con todos los datos asociados al departamento 10.

Consulta 42:

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE,SAL,COMM,DEPTNO) VALUES (7777,'JORGE','CLERK',7902,'20/12/12',80,20,10);

Insertar un departamento con el código y el nombre del departamento exclusivamente.

Consulta 43:

Insert into dept (deptno,dname)values(56,’matematicas’);

Borrar todas las filas de la tabla emp cuyos empleados pertenecen al departamento que se encuentra ubicado en la localidad de New York.

Consulta 44:

Delete from emp where deptno = (select deptno from dept where loc =’New York’)

Borrar las filas de la tabla de los departamentos que no tengan localidad.

Consulta 45:

Delete from DEPT where LOC is null;

46. Nombre de la región con los nombres de los países y los nombres de las localidades.

Consulta 46:

select region_name,country_name,city from regions r,countries c, locations l where c.region_id=r.region_id and l.country_id=c.country_id

1. Obtener todos los empleados que tienen como oficio ‘salesman’ que tengan sensibilidad a mayúsculas. (Qué función hay que utilizar para que me lo coja como mayúsculas).

Consulta 47:

SELECT ENAME FROM EMP WHERE JOB LIKE UPPER('salesman')

1. Mostrar las 2 últimas letras de los nombres de los empleados.

Consulta 48:

SELECT SUBSTRING(Nombre, 0, LEN(Nombre)-2) AS SubCadena FROM Usuario

1. Obtener nombre, longitud del nombre de los empleados y en qué posición está el carácter 'A' en el nombre.

Consulta 49:

SELECT ename, LENGTH(ename), INSTR(ename,'A') from emp

1. Concatenación de dos columnas:

Consulta 50:

select ename || job from emp

1. Concatenación:

Consulta 51:

select ename ||'-'|| job from emp

1. Visualizar los nombres de los empleados en un campo de 10 y por la izquierda rellenado por asteriscos.

Consulta 52:

select LPAD(ename,10,'*') from emp

1. Una consulta para visualizar el salario de los empleados, el promedio, el salario mínimo, máximo, la suma de todos los salarios y el nº de empleados que hay.

Consulta 53:

select SUM(sal), MIN(sal), MAX(sal), Round(AVG(sal),2) , COUNT(ename) from emp

1. ¿Cuántos empleados trabajan en el departamento técnico?

Consulta 54:

select count(*)from emp where DEPTNO = 30

1. Consulta para que devuelva el nº de departamentos distintos que hay en la tabla emp.

Consulta 55:

select count(DISTINCT(DEPTNO))from emp where DEPTNO = 30

1. Consulta de todos los trabajadores que reciben comisión agrupados por el código de departamento.