Vamos a realizar ejercicios de consultas SQL. Para poder realizarlas, usa el siguiente código SQL para cargar las tablas necesarias:
DROP TABLE IF EXISTS PEDIDOS;
DROP TABLE IF EXISTS PRODUCTOS;
DROP TABLE IF EXISTS CLIENTES;
DROP TABLE IF EXISTS EMPLEADOS;
DROP TABLE IF EXISTS DEPARTAMENTOS;
CREATE TABLE DEPARTAMENTOS
( DEP_NO INT(2),
DNOMBRE VARCHAR(14),
LOCALIDAD VARCHAR(10),
CONSTRAINT PK_DEPARTAMENTOS_DEP_NO PRIMARY KEY (DEP_NO))
ENGINE = INNODB;
CREATE TABLE EMPLEADOS
( EMP_NO INT(4),
APELLIDO VARCHAR(8),
OFICIO VARCHAR(10),
DIRECTOR INT(4),
FECHA_ALTA DATE,
SALARIO FLOAT(6,2),
COMISION FLOAT(6,2),
DEP_NO INT (2),
CONSTRAINT PK_EMPLEADOS_EMP_NO PRIMARY KEY (EMP_NO),
CONSTRAINT FK_EMP_DIRECTOR FOREIGN KEY (DIRECTOR)
REFERENCES EMPLEADOS(EMP_NO),
CONSTRAINT FK_EMP_DEP_NO FOREIGN KEY (DEP_NO)
REFERENCES DEPARTAMENTOS(DEP_NO)
)ENGINE = INNODB;
CREATE TABLE CLIENTES
(CLIENTE_NO INT(4),
NOMBRE VARCHAR(25),
LOCALIDAD VARCHAR(14),
VENDEDOR_NO INT(4),
DEBE FLOAT(9,2),
HABER FLOAT(9,2),
LIMITE_CREDITO FLOAT(9,2),
CONSTRAINT PK_CLIENTE_NO PRIMARY KEY(CLIENTE_NO),
CONSTRAINT FK_CLI_EMP_NO FOREIGN KEY (VENDEDOR_NO)
REFERENCES EMPLEADOS(EMP_NO) ON DELETE CASCADE
)ENGINE = INNODB;
CREATE TABLE PRODUCTOS
(PRODUCTO_NO INT(4),
DESCRIPCION VARCHAR(30),
PRECIO_ACTUAL FLOAT(8,2),
STOCK_DISPONIBLE INT(9),
CONSTRAINT PK_PRODUCTO_NO PRIMARY KEY (PRODUCTO_NO)
)ENGINE = INNODB;
CREATE TABLE PEDIDOS
(PEDIDO_NO INT(4),
PRODUCTO_NO INT(4),
CLIENTE_NO INT(4),
UNIDADES INT(4),
FECHA_PEDIDO DATE,
CONSTRAINT PK_PEDIDO_NO PRIMARY KEY (PEDIDO_NO),
CONSTRAINT FK_PEDIDOS_PRODUCTO_NO FOREIGN KEY (PRODUCTO_NO)
REFERENCES PRODUCTOS(PRODUCTO_NO) ON DELETE CASCADE,
CONSTRAINT FK_PEDIDOS_CLIENTE_NO FOREIGN KEY (CLIENTE_NO)
REFERENCES CLIENTES(CLIENTE_NO) ON DELETE CASCADE
)ENGINE = INNODB;
INSERT INTO DEPARTAMENTOS VALUES(10, 'CONTABILIDAD', 'BARCELONA');
INSERT INTO DEPARTAMENTOS VALUES(20, 'INVESTIGACION', 'VALENCIA');
INSERT INTO DEPARTAMENTOS VALUES(30, 'VENTAS', 'MADRID');
INSERT INTO DEPARTAMENTOS VALUES(40, 'PRODUCCION', 'SEVILLA');
INSERT INTO EMPLEADOS VALUES (7839,'REY', 'PRESIDENTE',NULL,'1981-11-17',6000, NULL, 10);
INSERT INTO EMPLEADOS VALUES (7698,'GARRIDO', 'DIRECTOR', 7839,'1981-05-01',3850.12,NULL, 30);
INSERT INTO EMPLEADOS VALUES (7782,'MARTINEZ','DIRECTOR', 7839,'1981-06-09',2450, NULL, 10);
INSERT INTO EMPLEADOS VALUES (7499,'ALONSO', 'VENDEDOR', 7698,'1981-02-23',1400, 400, 30);
INSERT INTO EMPLEADOS VALUES (7521,'LOPEZ', 'EMPLEADO', 7782,'1981-05-08',1350.50,NULL, 10);
INSERT INTO EMPLEADOS VALUES (7654,'MARTIN', 'VENDEDOR', 7698,'1981-09-28',1500, 1600, 30);
INSERT INTO EMPLEADOS VALUES (7844,'CALVO', 'VENDEDOR', 7698,'1981-09-08',1800, 0, 30);
INSERT INTO EMPLEADOS VALUES (7876,'GIL', 'ANALISTA', 7782,'1982-05-06',3350, NULL, 20);
INSERT INTO EMPLEADOS VALUES (7900,'JIMENEZ', 'EMPLEADO', 7782,'1983-03-24',1400, NULL, 20);
INSERT INTO CLIENTES VALUES (101, 'DISTRIBUCIONES GOMEZ', 'MADRID', 7499, 0, 0, 5000);
INSERT INTO CLIENTES VALUES (102, 'LOGITRONICA S.L', 'BARCELONA', 7654, 0, 0, 5000);
INSERT INTO CLIENTES VALUES (103, 'INDUSTRIAS LACTEAS S.A.', 'LAS ROZAS', 7844, 0, 0, 10000);
INSERT INTO CLIENTES VALUES (104, 'TALLERES ESTESO S.A.', 'SEVILLA', 7654, 0, 0, 5000);
INSERT INTO CLIENTES VALUES (105, 'EDICIONES SANZ', 'BARCELONA', 7499, 0, 0, 5000);
INSERT INTO CLIENTES VALUES (106, 'SIGNOLOGIC S.A.', 'MADRID', 7654, 0, 0, 5000);
INSERT INTO CLIENTES VALUES (107, 'MARTIN Y ASOCIADOS S.L.', 'ARAVACA', 7844, 0, 0, 10000);
INSERT INTO CLIENTES VALUES (108, 'MANUFACTURAS ALI S.A.', 'SEVILLA', 7654, 0, 0, 5000);
INSERT INTO PRODUCTOS VALUES (10, 'MESA DESPACHO MOD. GAVIOTA', 550, 50);
INSERT INTO PRODUCTOS VALUES (20, 'SILLA DIRECTOR MOD. BUFALO', 670, 25);
INSERT INTO PRODUCTOS VALUES (30, 'ARMARIO NOGAL DOS PUERTAS', 460, 20);
INSERT INTO PRODUCTOS VALUES (40, 'MESA MODELO UNION', 340, 15);
INSERT INTO PRODUCTOS VALUES (50, 'ARCHIVADOR CEREZO', 1050, 20);
INSERT INTO PRODUCTOS VALUES (60, 'CAJA SEGURIDAD MOD B222', 280, 15);
INSERT INTO PRODUCTOS VALUES (70, 'DESTRUCTORA DE PAPEL A3', 450, 25);
INSERT INTO PRODUCTOS VALUES (80, 'MODULO ORDENADOR MOD. ERGOS', 550, 25);
INSERT INTO PEDIDOS VALUES (1000, 20, 103, 3, '1999-10-06');
INSERT INTO PEDIDOS VALUES (1001, 50, 106, 2, '1999-10-06');
INSERT INTO PEDIDOS VALUES (1002, 10, 101, 4, '1999-10-07');
INSERT INTO PEDIDOS VALUES (1003, 20, 105, 4, '1999-10-16');
INSERT INTO PEDIDOS VALUES (1004, 40, 106, 8, '1999-10-20');
INSERT INTO PEDIDOS VALUES (1005, 30, 105, 2, '1999-10-20');
INSERT INTO PEDIDOS VALUES (1006, 70, 103, 3, '1999-11-03');
INSERT INTO PEDIDOS VALUES (1007, 50, 101, 2, '1999-11-06');
INSERT INTO PEDIDOS VALUES (1008, 10, 106, 6, '1999-11-16');
INSERT INTO PEDIDOS VALUES (1009, 20, 105, 2, '1999-11-26');
INSERT INTO PEDIDOS VALUES (1010, 40, 102, 3, '1999-12-08');
INSERT INTO PEDIDOS VALUES (1011, 30, 106, 2, '1999-12-15');
INSERT INTO PEDIDOS VALUES (1012, 10, 105, 3, '1999-12-06');
INSERT INTO PEDIDOS VALUES (1013, 30, 106, 2, '1999-12-06');
INSERT INTO PEDIDOS VALUES (1014, 20, 101, 4, '2000-01-07');
INSERT INTO PEDIDOS VALUES (1015, 70, 105, 4, '2000-01-16');
INSERT INTO PEDIDOS VALUES (1017, 20, 105, 6, '2000-01-20');
COMMIT;
Ejercicios. Consultas sencillas
Tablas utilizadas: EMPLEADOS y DEPARTAMENTOS.
1. Escribir los apellidos de los empleados junto con sus fechas de alta en formato “día del mes de año”.
SELECT APELLIDO, CONCAT(DAY(FECHA_ALTA)," del ",MONTH(FECHA_ALTA)," de ",YEAR(FECHA_ALTA)) AS 'Fecha de alta'
FROM EMPLEADOS;
2. Hallar por orden alfabético los primeros tres caracteres del apellido de los empleados con apellido de más de 6 letras.
SELECT SUBSTR(APELLIDO, 1, 3) AS APELLIDOS
FROM EMPLEADOS
WHERE LENGTH(APELLIDO) > 6;
3. Se desea hacer un regalo de un 3% del salario a los empleados que no tienen comisión. Mostrar apellido y regalo.
SELECT APELLIDO, SALARIO * 0.03 AS 'Regalo de salario'
FROM EMPLEADOS
WHERE COMISION IS NULL;
4. Obtener los datos de los empleados cuyo salario total (salario+comision) supere los 2.000 euros y (salario>1800 o comision>500).
SELECT *
FROM EMPLEADOS
WHERE SALARIO + IFNULL(COMISION, 0) > 2000
AND (SALARIO > 1800 OR COMISION > 500);
5. Visualizar los datos del empleado de oficio DIRECTOR más antiguo en la empresa.
SELECT *
FROM EMPLEADOS
WHERE OFICIO LIKE 'DIRECTOR'
ORDER BY FECHA_ALTA
LIMIT 1;
6. Visualizar los datos de los dos empleados que ganan más salario entre los empleados de los departamentos 20 y 30.
SELECT *
FROM EMPLEADOS
WHERE DEP_NO = 20 OR DEP_NO = 30
ORDER BY SALARIO DESC
LIMIT 2;
7. Empleados dados de alta después del 01/05/1981.
SELECT *
FROM EMPLEADOS
WHERE FECHA_ALTA > '1981-05-01';
8. Apellido y días trabajados hasta el 31-12-2004.
SELECT APELLIDO, DATEDIFF('2004-12-31', FECHA_ALTA) AS 'DIAS TRABAJADOS HASTA 31-12-2004'
FROM EMPLEADOS;
9. Apellido, fecha de alta y nombre del día de la semana de la fecha de alta.
SELECT APELLIDO, FECHA_ALTA, DAYNAME(FECHA_ALTA)
FROM EMPLEADOS;
10. Apellido e importe total (salario+comisión) de los vendedores.
SELECT APELLIDO, SALARIO + IFNULL(COMISION, 0) AS 'IMPORTE TOTAL'
FROM EMPLEADOS
WHERE OFICIO LIKE 'VENDEDOR';
11. Número de empleado, apellido y oficio si el apellido termina en O o el oficio empieza por V.
SELECT EMP_NO, APELLIDO, OFICIO
FROM EMPLEADOS
WHERE APELLIDO LIKE '%O' OR OFICIO LIKE 'V%';
12. Nombre del departamento rellenado con asteriscos hasta 36 caracteres.
SELECT RPAD(DNOMBRE, 36, '*') AS DEPARTAMENTO
FROM DEPARTAMENTOS
WHERE LENGTH(DNOMBRE) <= 36;
13. Apellidos sustituyendo la A por *.
SELECT REPLACE(APELLIDO, 'A', '*')
FROM EMPLEADOS;
14. Primera mitad del nombre del departamento.
SELECT SUBSTR(DNOMBRE, 1, LENGTH(DNOMBRE) / 2)
FROM DEPARTAMENTOS;
15. Datos de empleados ordenados por departamento, oficio y apellido.
SELECT DEP_NO, OFICIO, APELLIDO, FECHA_ALTA, SALARIO, COMISION, DIRECTOR
FROM EMPLEADOS
ORDER BY DEP_NO, OFICIO, APELLIDO;
16. Número de departamento, apellido y salario total, ordenados por departamento y salario total.
SELECT DEP_NO, APELLIDO, SALARIO + IFNULL(COMISION, 0) AS 'SALARIO TOTAL'
FROM EMPLEADOS
ORDER BY DEP_NO, SALARIO + IFNULL(COMISION, 0);
17. Apellido y salario total de empleados del departamento 30, ordenados por salario total descendente y apellido ascendente.
SELECT APELLIDO, SALARIO + IFNULL(COMISION, 0) AS 'SALARIO TOTAL'
FROM EMPLEADOS
WHERE DEP_NO = 30
ORDER BY SALARIO + IFNULL(COMISION, 0) DESC, APELLIDO ASC;
18. Apellidos de empleados sin comisión ordenados por longitud del apellido.
SELECT APELLIDO
FROM EMPLEADOS
WHERE COMISION IS NULL
ORDER BY LENGTH(APELLIDO);
19. El departamento con nombre más largo.
SELECT DNOMBRE, LENGTH(DNOMBRE) AS LONGITUD
FROM DEPARTAMENTOS
ORDER BY LENGTH(DNOMBRE) DESC
LIMIT 1;
20. Los 4 últimos empleados incorporados junto con su departamento.
SELECT EMPLEADOS.APELLIDO, EMPLEADOS.FECHA_ALTA, DEPARTAMENTOS.DNOMBRE AS DEPARTAMENTO
FROM EMPLEADOS, DEPARTAMENTOS
ORDER BY FECHA_ALTA DESC
LIMIT 4;
¡Salud y coding!