Desarrollo y Depuración de Consultas SQL y Funciones PL/pgSQL

Consultas SQL: Ejercicios Resueltos y Optimizados

A continuación, se presentan una serie de consultas SQL, corregidas y optimizadas para una mejor comprensión y ejecución. Cada consulta aborda un escenario específico de manipulación y recuperación de datos en una base de datos relacional.

• 1. Número de facturas por empleado en la misma provincia y con más de 20 facturas

  Esta consulta identifica el número de facturas emitidas por cada empleado, filtrando aquellos que pertenecen a la misma provincia que el empleado con código '100' y que han generado más de 20 facturas.

  SELECT COUNT(*)
  FROM facturas f
  JOIN clientes c ON f.codcli = c.codcli
  JOIN municipios m ON c.codmun = m.codmun
  WHERE m.codpro = (SELECT m2.codpro FROM municipios m2 JOIN clientes c2 ON c2.codmun = m2.codmun WHERE c2.codcli = '100')
  GROUP BY f.codcli
  HAVING COUNT(*) > 20;

• 2. Vendedor de Segovia: Nombre y Nombre de su Jefe

  Consulta para obtener el nombre de un vendedor ubicado en la provincia de Segovia y el nombre de su jefe directo.

  SELECT v.nombre AS nombre_vendedor, j.nombre AS nombre_jefe
  FROM vendedores v
  JOIN municipios m ON v.codmun = m.codmun
  JOIN provincias p ON m.codpro = p.codpro
  JOIN vendedores j ON v.codjefe = j.codven -- Corrección: v.codjefe es el código del jefe de v
  WHERE p.nombre = 'Segovia';

• 3. Municipios de Soria con Nombres Duplicados en Otras Provincias

  Identifica los nombres de municipios de la provincia de Soria que coinciden con nombres de municipios en cualquier otra provincia.

  SELECT DISTINCT m.nombre
  FROM municipios m
  JOIN provincias p ON p.codpro = m.codpro
  WHERE p.nombre = 'Soria'
  AND m.nombre = ANY (SELECT m2.nombre FROM municipios m2 JOIN provincias p2 ON p2.codpro = m2.codpro WHERE p2.nombre != 'Soria');

• 4. Artículos con Precio Superior a la Media y Múltiples Compras

  Muestra el código y la descripción de artículos cuyo precio es superior al precio medio de todos los artículos y que han sido registrados en más de 3 líneas de factura.

  SELECT a.codart, a.descrip
  FROM articulos a
  JOIN lineas_fac lf ON a.codart = lf.codart
  WHERE a.precio > (SELECT AVG(precio) FROM articulos)
  GROUP BY a.codart, a.descrip
  HAVING COUNT(lf.codart) > 3;

• 5. Vendedores que son Jefes: Nombre y Número de Facturas

  Obtiene el nombre y el número de facturas realizadas por aquellos vendedores que, a su vez, ejercen como jefes de otros vendedores.

  SELECT f.codven, v.nombre, COUNT(*) AS numero_facturas
  FROM vendedores v
  JOIN facturas f ON v.codven = f.codven
  WHERE v.codven IN (SELECT DISTINCT codjefe FROM vendedores WHERE codjefe IS NOT NULL)
  GROUP BY f.codven, v.nombre;

• 6. Artículos Facturados por Antonio Blanco con Alto Valor

  Lista los códigos de los artículos que han sido facturados por el vendedor 'Antonio Blanco' y cuyo valor total en las líneas de factura supera los 6000€.

  SELECT lf.codart
  FROM lineas_fac lf
  JOIN facturas f ON f.codfac = lf.codfac
  JOIN vendedores v ON v.codven = f.codven
  WHERE v.nombre = 'Antonio Blanco'
  GROUP BY lf.codart
  HAVING SUM(lf.precio * lf.cantidad) > 6000;

• 7. Vendedores de Valladolid sin Facturas: Nombre y Nombre de su Jefe

  Muestra el nombre del vendedor y el nombre de su jefe para aquellos vendedores ubicados en Valladolid que no han registrado ninguna factura.

  SELECT v1.nombre AS nombre_vendedor, v2.nombre AS nombre_jefe
  FROM vendedores v1
  JOIN vendedores v2 ON v1.codjefe = v2.codven -- Corrección: v1.codjefe es el código del jefe de v1
  JOIN municipios m ON v1.codmun = m.codmun
  JOIN provincias p ON m.codpro = p.codpro
  WHERE p.nombre = 'Valladolid'
  AND v1.codven NOT IN (SELECT codven FROM facturas);

• 8. Vendedor con el Mayor Número de Facturas

  Identifica el nombre del vendedor que ha emitido el mayor número de facturas en el sistema.

  SELECT v.nombre
  FROM vendedores v
  JOIN facturas f ON v.codven = f.codven
  GROUP BY v.codven, v.nombre
  HAVING COUNT(f.codfac) >= ALL (SELECT COUNT(f2.codfac) FROM facturas f2 GROUP BY f2.codven);

• 9. Relación Completa: Municipios, Vendedores y Jefes

  Esta consulta presenta una relación exhaustiva entre municipios, vendedores y sus respectivos jefes, incluyendo aquellos municipios que no tienen vendedores asignados y aquellos vendedores que no están asociados a ningún municipio, así como la información del jefe cuando esté disponible.

  SELECT m.nombre AS nombre_municipio,
         t.nombre AS nombre_trabajador,
         j.nombre AS nombre_jefe
  FROM municipios m
  FULL OUTER JOIN vendedores t ON m.codmun = t.codmun
  LEFT JOIN vendedores j ON t.codjefe = j.codven;

Función PL/pgSQL: Asignación de Calificaciones

A continuación, se presenta una función PL/pgSQL diseñada para asignar calificaciones a los alumnos basándose en sus notas y rangos predefinidos, además de identificar y registrar a los alumnos suspensos.

CREATE OR REPLACE FUNCTION asign_calificacion(cod VARCHAR(3)) RETURNS INT AS $$
DECLARE
    calif listado_calificaciones%ROWTYPE;
    alum  alumnos%ROWTYPE;
    contador INT := 0;
BEGIN
    FOR calif IN SELECT * FROM listado_calificaciones LOOP
        UPDATE alumnos
        SET calificacion = calif.calificacion
        WHERE cod_asignatura = cod
          AND nota >= calif.limite_inferior -- Se cambió a >= para incluir el límite inferior
          AND nota < calif.limite_superior;

        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE 'Error en la actualización de calificaciones para el rango de nota %s a %s.', calif.limite_inferior, calif.limite_superior;
                EXIT; -- Sale del bucle si ocurre un error en la actualización
    END LOOP;

    FOR alum IN SELECT * FROM alumnos LOOP
        IF alum.nota < 5 THEN
            INSERT INTO alumnos_suspensos (cod_alumno, cod_asignatura) -- Se especifican las columnas
            VALUES (alum.cod_alumno, alum.cod_asignatura);
            contador := contador + 1;
        END IF;
    END LOOP;

    RETURN contador;
END;
$$ LANGUAGE plpgsql;