Consultas SQL: Ejercicios Prácticos y Soluciones

Ejercicio 1: Media de Salarios por Sexo

Realice una consulta que devuelva la media de salarios de la tabla empleados agrupado por sexo. Redondee la media de salarios a un solo decimal y decodifique la columna SEXO para que aparezca el literal HOMBRES y MUJERES en lugar de H y M. No olvide rebautizar las columnas con un alias apropiado.

SELECT IF(SEXO='M', 'MUJERES', 'HOMBRES') AS SEXO, ROUND(AVG(salario), 1) AS MEDIA_SALARIO
FROM EMPLEADOS
GROUP BY IF(SEXO='M', 'MUJERES', 'HOMBRES');

Ejercicio 2: Edad de Empleados

Realice una consulta sobre la tabla EMPLEADOS que devuelva el nombre, los apellidos, la fecha de nacimiento y la edad actual en años de cada empleado, para aquellos empleados con 18 años o más.

Nota: La edad de un empleado en años es el número de días transcurridos desde el nacimiento dividido entre los 365 días que tiene un año.

SELECT NOMBRE, APELLIDOS, F_NACIMIENTO, TRUNCATE(DATEDIFF(CURRENT_DATE, F_NACIMIENTO) / 365, 0) AS EDAD
FROM EMPLEADOS
WHERE TRUNCATE(DATEDIFF(CURRENT_DATE, F_NACIMIENTO) / 365, 0) >= 18;

Ejercicio 3: Revisión de Vehículos por Año

Realice una consulta sobre la tabla vehículos que devuelva el número de vehículos que deben pasar la revisión agrupado por el año en que deben pasarla.

SELECT DATE_FORMAT(PROX_ITV, '%Y') AS AÑO_ITV, COUNT(1) AS VEHICULOS
FROM VEHICULOS
GROUP BY DATE_FORMAT(PROX_ITV, '%Y');

Ejercicio 4: Porcentaje de Mascotas por Ubicación

Resuelva en una sola consulta usando una subconsulta en la cláusula SELECT, el porcentaje de individuos que contiene cada ubicación del centro de mascotas versus el total de mascotas en el centro. No considere mascotas dadas de baja.

SELECT UBICACION, COUNT(1) / (SELECT COUNT(1) FROM MASCOTAS WHERE ESTADO = 'A') * 100 AS PORCENTAJE
FROM MASCOTAS
WHERE ESTADO = 'A'
GROUP BY UBICACION;

Ejercicio 5: Porcentaje de Mascotas por Ubicación y Especie

Realice el mismo ejercicio pero discriminando por especie, es decir, el porcentaje de gatos o perros que contiene cada ubicación versus el total de gatos o de perros que hay en el centro.

SELECT UBICACION, COUNT(1) / (SELECT COUNT(1) FROM MASCOTAS S WHERE S.ESTADO = 'A' AND S.ESPECIE = M.ESPECIE) * 100 AS PORCENTAJE
FROM MASCOTAS M
WHERE M.ESTADO = 'A'
GROUP BY UBICACION, ESPECIE;

Ejercicio 6: Porcentaje de Mascotas por Ubicación (Subconsulta en FROM)

Resuelva en una sola consulta usando una subconsulta en la cláusula FROM, el porcentaje de individuos que contiene cada ubicación del centro de mascotas versus el total de mascotas en el centro. No considere mascotas dadas de baja.

SELECT M.UBICACION, COUNT(1) / T.TOTAL_MASCOTAS * 100 AS PORCENTAJE
FROM MASCOTAS AS M, (SELECT COUNT(1) AS TOTAL_MASCOTAS FROM MASCOTAS WHERE ESTADO = 'A') AS T
WHERE M.ESTADO = 'A'
GROUP BY M.UBICACION;

Ejercicio 7: Porcentaje de Mascotas por Ubicación y Especie (Subconsulta en FROM)

Realice el mismo ejercicio discriminando por especie, es decir, el porcentaje de gatos o perros que contiene cada ubicación versus el total de gatos o de perros que hay en el centro.

SELECT M.UBICACION, M.ESPECIE, COUNT(1) / T.TOTAL_ESPECIE * 100 AS PORCENTAJE
FROM MASCOTAS M, (SELECT ESPECIE, COUNT(1) AS TOTAL_ESPECIE FROM MASCOTAS WHERE ESTADO = 'A' GROUP BY ESPECIE) AS T
WHERE M.ESTADO = 'A' AND M.ESPECIE = T.ESPECIE
GROUP BY M.UBICACION, M.ESPECIE;

Ejercicio 8: Empleados Excepto el Más Joven y el Más Mayor

Construya una consulta que devuelva todos los impuestos salvo el más joven y el más mayor.

SELECT *
FROM EMPLEADOS
WHERE F_NACIMIENTO != (SELECT MAX(F_NACIMIENTO) FROM EMPLEADOS) AND F_NACIMIENTO != (SELECT MIN(F_NACIMIENTO) FROM EMPLEADOS);

Ejercicio 9: Profesores que No Imparten Cursos

Construya una consulta que devuelva los profesores que no imparten ningún curso. Resuelva el problema de dos modos: use el operador IN en uno de ellos y el operador EXISTS en el otro.

Usando IN:

SELECT *
FROM PROFESORES
WHERE ID_PROFE NOT IN (SELECT DISTINCT ID_PROFE FROM CURSOS WHERE ID_PROFE IS NOT NULL);

Usando EXISTS:

SELECT *
FROM PROFESORES P
WHERE NOT EXISTS (SELECT 1 FROM CURSOS C WHERE P.ID_PROFE = C.ID_PROFE);

Ejercicio 10: Ubicación con Mayor Número de Mascotas

Construya una consulta que devuelva la ubicación del centro de mascotas con mayor número de ejemplares. En el resultado debe aparecer además de la ubicación, la especie y el número de ejemplares de la ubicación. No considere mascotas dadas de baja.

SELECT UBICACION, ESPECIE, COUNT(1) AS EJEMPLARES
FROM MASCOTAS
WHERE ESTADO = 'A'
GROUP BY UBICACION, ESPECIE
HAVING COUNT(1) = (SELECT MAX(EJEMPLARES) FROM (SELECT UBICACION, COUNT(1) AS EJEMPLARES FROM MASCOTAS WHERE ESTADO = 'A' GROUP BY UBICACION) AS E);

Ejercicio 11: Vista de Mascotas con Campos Decodificados

Construya una vista que decodifique el campo ESPECIE, SEXO y ESTADO de la tabla MASCOTAS con ayuda de la función IF. El resto de campos no se deben tratar, pero deben formar parte de la vista. Ponga un nombre a la vista para que cuando se reutilice en las consultas, denote que se trata de una vista y no de una tabla.

CREATE VIEW MASCOTAS_V AS
SELECT ID_MASCOTAS, NOMBRE, IF(ESPECIE = 'P', 'Perro', 'Gato') AS ESPECIE, IF(SEXO = 'M', 'MACHO', 'HEMBRA') AS SEXO, UBICACION, IF(ESTADO = 'A', 'Alta', 'Baja') AS ESTADO
FROM MASCOTAS;

SELECT *
FROM MASCOTAS_V
WHERE ESTADO = 'Baja';

Ejercicios Adicionales de Consultas SQL

1. Listar los datos de los autores

   SELECT * FROM autor;

2. Listar nombre y edad de los estudiantes

   SELECT nombre, edad FROM estudiante;

3. ¿Qué estudiantes pertenecen a la carrera de Informática?

   SELECT nombre FROM estudiante WHERE carrera = "Informatica";

4. Listar los nombres de los estudiantes cuyo apellido comience con la letra G

   SELECT nombre FROM estudiante WHERE nombre LIKE '% G%';

5. ¿Quiénes son los autores del libro “Visual Studio Net”, listar solamente los nombres?

   SELECT nombre FROM autor WHERE idautor IN (SELECT idautor FROM libaut WHERE idlibro IN (SELECT idlibro FROM libro WHERE titulo = 'Visual Studio Net'));

6. ¿Qué autores son de nacionalidad USA o Francia?

   SELECT * FROM autor WHERE nacionalidad IN ('USA', 'Francia');

7. ¿Qué libros No Son del Area de Internet?

   SELECT * FROM libro WHERE area <> 'Internet';

8. ¿Qué libros se prestó el Lector “Raul Valdez Alanes”?

   SELECT * FROM libro WHERE idlibro IN (SELECT idlibro FROM prestamo WHERE idlector IN (SELECT idlector FROM estudiante WHERE nombre = 'Raul Valdez Alanes'));

9. Listar el nombre del estudiante de menor edad

   SELECT nombre FROM estudiante WHERE edad IN (SELECT MIN(edad) FROM estudiante);

10. Listar los nombres de los estudiante que se prestaron Libros de Base de Datos

    SELECT * FROM estudiante WHERE idlector IN (SELECT idlector FROM prestamo WHERE idlibro IN (SELECT idlibro FROM libro WHERE area = 'Base de Datos'));

11. Listar los libros de editorial AlfayOmega

    SELECT * FROM libro WHERE editorial = 'AlfaOmega';

12. Listar los libros que pertenecen al autor Mario Benedetti

    SELECT * FROM libro WHERE idlibro IN (SELECT idlibro FROM libaut WHERE idautor IN (SELECT idautor FROM autor WHERE nombre = 'Benedetti Mario'));

13. Listar los títulos de los libros que debían devolverse el 10/04/07

    SELECT * FROM libro WHERE idlibro IN (SELECT idlibro FROM prestamo WHERE fechadevolucion = #04/10/07# AND devuelto = 'No');

14. Hallar la suma de las edades de los estudiantes

    SELECT SUM(edad) AS [La suma de las edades es: ] FROM estudiante;

15. Listar los datos de los estudiantes cuya edad es mayor al promedio

    SELECT * FROM estudiante WHERE edad > (SELECT AVG(edad) FROM estudiante);