Prueba SQL

EVALUACIÓN PARA CANDIDATOS

1. Dada la siguiente Base de Datos relacional:

[pic 1]

Escriba las sentencias SQL necesarias para obtener los siguientes informes:

CREATE DATABASE EMPRESA;

USE EMPRESA;

CREATE TABLE EMPLEADO (

codigo_c VARCHAR(40) NOT NULL PRIMARY KEY,

nombre VARCHAR(30),

edad INT,

oficio VARCHAR(20),

dir VARCHAR(20),

fecha_alt DATE,

salario INT,

comision INT,

depto_no INT NOT NULL,

FOREIGN KEY (depto_no) REFERENCES DEPARTAMENTO(depto_no)

) ENGINE = InnoDB;

CREATE TABLE DEPARTAMENTO (

depto_no INT NOT NULL PRIMARY KEY,

nombre_depto VARCHAR(20),

localizacion VARCHAR(20)

) ENGINE = InnoDB;

1. Inserte un nuevo registro en la tabla “empleado”

INSERT INTO EMPLEADO VALUES ( "281-160483-0005G", 'Claudio Pizarro', 37, "Futbolista", "Callao", "1973-05-12" , 12000, 0, 20);

1. Mostrar los nombres de departamentos con más de 3 empleados.

SELECT nombre_depto, COUNT(codigo_c) FROM EMPLEADO GROUP BY depto_no HAVING COUNT(codigo_c)>3;

1. Mostrar el nombre, salario y localización donde trabajan los empleados que tengan un salario entre 13,000 y 15,000.

SELECT nombre, salario, dir FROM EMPLEADO WHERE salario>=13000 AND salario<=15000;

1. Mostrar el salario promedio de los empleados por Departamento:

SELECT nombre_depto, AVG(salario) as Promedio FROM EMPLEADO a INNER JOIN DEPARTAMENTO c ON a.depto_no = c.depto_no ;

1. Mostrar el nombre y el departamento al que pertenece el empleado más antiguo en la empresa:

SELECT nombre, depto_no FROM EMPLEADO WHERE fecha_alt = (SELECT MIN(fecha_alt) FROM EMPLEADO)

1. Considerando la Base de Datos del punto 1, crear una función que retorne el salario promedio de todos los empleados

CREATE FUNCTION SALARIO_PROMEDIO() RETURNS FLOAT

BEGIN

  DECLARE promedio FLOAT;

  DECLARE sum FLOAT;

  SET sum=0;

  DECLARE num_trab INT;

  SET num_trab= SELECT COUNT(*) FROM EMPLEADO;

  DECLARE i INT;

  SET i=0;

  my_loop: LOOP

    SET suma=suma+EMPLEADO.salario;

        IF i>num_trab THEN

          SET promedio=suma/num_trab;

      LEAVE my_loop;

    END IF;

  RETURN promedio;

END

1. Diseñar un algoritmo que decida si se le aprueba o no un crédito a un cliente XYZ. Suponga que el razonamiento para la aprobación es la siguiente:

“Para que se le apruebe el crédito a un cliente es necesario que su deuda total con el banco y en el resto de los bancos del país no supere el 20% de sus ingresos brutos además su edad no supere los 45 años ni sea menor a 20 años.  Se otorgan excepciones para personas mayores a 45 años, se les puede aprobar el crédito siempre que presenten un aval y sea un trabajado dependiente.”

...