Consultas SQL: Ejemplos Prácticos

Comandos SELECT y sus variaciones

• Obtener datos de todos los empleados:

SELECT * FROM emp;

Obtener comisiones de empleados de forma que no se repitan:

SELECT DISTINCT comm FROM emp;

Obtener nuevos salarios de empleados del departamento 30, resultado de sumar su salario más 1000 y el nombre:

SELECT sal+1000 AS 'nuevo salario', ename FROM emp WHERE deptno=30;

Empleados que no tienen comisión o que la tengan menor o igual al 25% del salario:

SELECT * FROM emp WHERE comm <= (0.25*sal) OR comm IS NULL;

Empleados con comisión entre 50% y 75% que no se llamen Clark:

SELECT * FROM emp WHERE comm BETWEEN (sal*0.5) AND (sal*0.75) AND ename NOT LIKE 'clark';

Obtener salario, comisión y salario total de empleados con comisión, ordenado por número de empleado:

SELECT sal, comm, sal+comm AS 'salario total' FROM emp WHERE comm IS NOT NULL ORDER BY empno;

Indicar para cada empleado el porcentaje de su comisión sobre su salario:

SELECT empno, (100*comm)/sal AS 'porcentaje' FROM emp;

Información sobre empleados cuyo nombre tenga 5 caracteres:

SELECT * FROM emp WHERE ename LIKE '_____';

Hallar nombre, salario y el sueldo total de empleados, ordenando el resultado por salario y luego por sueldo total. Si no tiene comisión, el sueldo debe reflejar el salario:

SELECT ename, sal, IFNULL(sal+comm, sal) AS 'sueldo total' FROM emp ORDER BY sal, comm;

Empleados cuyo empleo acaba en ‘man’ y su nombre empieza por ‘a’, sin usar AND ni OR:

SELECT ename, job FROM emp WHERE CONCAT(ename, job) LIKE 'a%man';

Para empleados que tengan como jefe a un empleado con código mayor que el suyo, mostrar los que reciben de salario más de 1000 y menos de 2000 o los del departamento 30:

SELECT * FROM emp WHERE mgr > empno AND ((sal > 1000 AND sal < 2000) OR deptno = 30);

Obtener el salario más alto de la empresa, el total destinado a comisiones y el número de empleados:

SELECT MAX(sal) AS 'salario max', SUM(IFNULL(comm, 0)) AS 'total comisiones', COUNT(empno) AS 'numero de empleados' FROM emp;

Si queremos agrupar resultados por departamentos, es igual que antes y al final GROUP BY deptno. Y si queremos poner condiciones, por ejemplo: GROUP BY deptno HAVING sal > 1000

Consultas con Condiciones Específicas

• Hallar los datos de los empleados cuyo salario es mayor que el del empleado de código 7934, ordenando por salario:

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE empno=7934) ORDER BY sal;

Hallar el salario más alto, el más bajo y su diferencia:

SELECT MAX(sal) AS 'salario mayor', MIN(sal) AS 'salario menor', (MAX(sal)-MIN(sal)) AS 'diferencia' FROM emp;

Considerando empleados con salario menor a 5000, hallar la media de salarios de departamentos cuyo salario mínimo supera a 900. Mostrar también el código y el nombre de los departamentos:

SELECT deptno, AVG(sal) FROM emp WHERE sal < 5000 GROUP BY deptno HAVING MIN(sal) > 900;

Hallar los departamentos que tienen más de 3 empleados y el número de empleados de los mismos:

SELECT deptno, COUNT(empno) FROM emp GROUP BY deptno HAVING COUNT(empno) > 3;

Cuántos empleos distintos hay en la empresa y cuántos empleados los desempeñan:

SELECT job, COUNT(sal) FROM emp GROUP BY deptno;

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

SELECT ename, sal FROM emp e2, dept WHERE e2.deptno = dept.deptno AND sal IN (SELECT MAX(sal) FROM emp e1 WHERE e2.deptno = e1.deptno GROUP BY deptno);

Vistas en SQL

Una vista es el resultado de una consulta de varias tablas que se presenta como una sola tabla.

Ejemplo de creación de vista:

CREATE VIEW vista1 AS SELECT * FROM emp WHERE empno > 1000 AND sal > 1000;

(Bajo vista1 se guardará esta consulta y solo habrá que poner: SELECT * FROM vista1;)

Procedimientos, Funciones y Triggers en PL/SQL

Los procedimientos PL/SQL son subprogramas compuestos por un conjunto de sentencias SQL. Siempre debemos usar una base de datos.

Diferencia entre procedure y función: la función devuelve un valor, mientras que el procedimiento hay que llamarlo.

Los Triggers son oyentes que se mantienen a la escucha de eventos que puedan producirse en una tabla (INSERT, UPDATE, DELETE) y ejecutan un código antes (BEFORE) o después (AFTER). Podemos hacer cosas como mantener un log de usuarios que hacen modificaciones en una tabla.

Sintaxis de un Trigger

CREATE TRIGGER nombre_trigger

{BEFORE | AFTER}

{INSERT | UPDATE | DELETE}

ON nombre_tabla

FOR EACH ROW

BEGIN

-- Código del trigger

END;

Ejemplo de alterar una tabla:

ALTER TABLE 'tablaexistente' RENAME TO 'nuevonombre';

Cursores en PL/SQL

Un cursor es un puntero que apunta a los registros de una tabla. Un puntero es un tipo especial de variable que permite guardar direcciones de memoria de otras variables. Permite recorrer diferentes registros.

Ejemplo de Declaración de un Procedimiento con Cursor

CREATE PROCEDURE nombre_procedimiento()

BEGIN

DECLARE variables tipo;

DECLARE done INT DEFAULT 0;

-- Declaramos el cursor

DECLARE nombre_cursor CURSOR FOR SELECT campos FROM tabla;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

-- Lo que dice es que cuando se llegue al final de la tabla se cambie el valor de la variable done a 1

-- Abrimos el cursor ... OPEN nombre_cursor; ... REPEAT

END;