1. Introducir datos de otra base de datos

INSERT INTO ganaderia.piensos
SELECT * FROM ganaderia_antigua.piensos;

2. Actualizar las existencias de los piensos

Añadir 100 unidades a cada una.

UPDATE piensos
SET existencias = existencias + 100;

2(bis). Actualizar las existencias de la medicación

Poner las existencias a 0 de aquellos medicamentos cuyas existencias sean mayores que 100.

UPDATE medicacion
SET existencias = 0 WHERE existencias > 100;

3. Visualizar el nombre del medicamento y el número de tratamientos

Mostrar el nombre del medicamento y el número de tratamientos en los que se utiliza, ordenados de mayor a menor.

SELECT descripcion, COUNT(cod_med) FROM tratamientos, medicacion
WHERE cod = cod_med
GROUP BY descripcion
ORDER BY COUNT(cod_med) DESC;

4. Visualizar el peso medio de las vacas nacidas en el mismo año

Mostrar el año de nacimiento y el peso medio de las vacas nacidas en ese año.

SELECT YEAR(fecha_nac) AS 'año nacimiento', AVG(peso) AS 'peso medio' FROM vacas
GROUP BY YEAR(fecha_nac);

5. Visualizar la cantidad media de días que ha estado a dieta cada vaca

Mostrar el ID de la vaca y la media de días que ha estado a dieta.

SELECT id_vaca, AVG(DATEDIFF(dd, fecha_fin, fecha_inicio)) AS 'dias medios' FROM dietas
GROUP BY id_vaca;

6. Crear una función para obtener la fecha del último tratamiento de una vaca

Crear una función a la que se le pasa como parámetro el nombre de una vaca y devuelve la fecha de la aplicación del último tratamiento que se le ha aplicado.

CREATE FUNCTION ultimo_tratamiento (
    @vaca VARCHAR(30)
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @fecha DATETIME;
    SELECT @fecha = MAX(fecha_aplicacion) FROM tratamientos
    WHERE id_vaca = (SELECT id FROM vacas WHERE nombre = @vaca);
    RETURN @fecha;
END;

7. Crear un procedimiento almacenado para actualizar existencias de pienso

Crear un procedimiento almacenado que recibe como parámetro el código de un pienso y la cantidad de unidades que se ha gastado, actualizando las existencias. Debe controlar si hay suficientes existencias y si el código del pienso existe.

CREATE PROC pa_pienso_actualizar_existencias
    @pienso INT, @restar INT
AS
    IF EXISTS (SELECT * FROM piensos WHERE cod = @pienso)
        IF @restar <= (SELECT existencias FROM piensos WHERE cod = @pienso)
            UPDATE piensos SET existencias = existencias - @restar WHERE cod = @pienso
        ELSE
            SELECT 'No hay suficientes existencias';
    ELSE
        SELECT 'No existe el pienso indicado';

8. Crear un procedimiento almacenado para obtener el pienso más consumido por una vaca

Crear un procedimiento almacenado que recibe el nombre de una vaca y proporciona como parámetro de salida el nombre del pienso que más consume dicha vaca. Si la vaca no existe, debe dar un mensaje de error.

CREATE PROC pa_vaca_pienso
    @vaca VARCHAR(40), @pienso VARCHAR(40) OUTPUT
AS
    DECLARE @cod INT;
    DECLARE @pi INT;
    IF EXISTS (SELECT id FROM vacas WHERE nombre = @vaca)
    BEGIN
        SELECT @cod = id FROM vacas WHERE nombre = @vaca;
        SELECT TOP 1 @pi = cod_pienso FROM dietas WHERE id_vaca = @cod
        GROUP BY cod_pienso ORDER BY COUNT(*) DESC;
        SELECT @pienso = descripcion FROM piensos WHERE cod = @pi;
    END
    ELSE
        SELECT 'No existe una vaca con tal código';

Operaciones en la Tabla de Ventas

9. Añadir un nuevo vendedor

Añadir a Enrique Pérez como nuevo vendedor en la tabla REPVENTAS.

INSERT INTO REPVENTAS (NUM_EMPL, NOMBRE, EDAD, OFICINA, TITULO, CONTRATO, DIRECTOR, CUOTA, VENTAS)
VALUES (12, 'ENRIQUE PÉREZ', 36, 13, 'Director ventas', '1999-07-27', 108, 0, 0);

10. Insertar un nuevo cliente

Insertar la empresa INTERCORP como nuevo cliente del Sr. Enrique Pérez.

INSERT INTO CLIENTES (NUM_CLIE, EMPRESA, REP_CLIE, LIMITE_CREDITO)
SELECT 2127, 'INTERCORP', NUM_EMPL, 15000 FROM REPVENTAS WHERE NOMBRE = 'ENRIQUE PÉREZ';

11. Insertar un nuevo pedido

Insertar un nuevo pedido para Enrique Pérez del fabricante ACI.

INSERT INTO PEDIDOS (NUM_PEDIDO, FECHA_PEDIDO, CLIE, REP, FAB, PRODUCTO, CANT, IMPORTE)
SELECT 113070, GETDATE(), 2127, NUM_EMPL, 'ACI', 41004, 20, 2340 FROM REPVENTAS WHERE NOMBRE = 'ENRIQUE PÉREZ';

12. Suprimir clientes con ventas bajas

Suprimir los clientes atendidos por vendedores cuyas ventas son inferiores al 80% de su cuota.

DELETE FROM PEDIDOS WHERE CLIE IN (SELECT NUM_CLIE FROM CLIENTES WHERE REP_CLIE IN (SELECT NUM_EMPL FROM REPVENTAS WHERE VENTAS < CUOTA * 0.8));
DELETE FROM CLIENTES WHERE REP_CLIE IN (SELECT NUM_EMPL FROM REPVENTAS WHERE VENTAS < CUOTA * 0.8));

13. Dar de alta tiendas y pedidos en Sevilla

Dar de alta dos tiendas en la provincia de Sevilla y hacer pedidos para 30 unidades de los artículos del fabricante Gallo.

INSERT INTO TIENDAS (PROVINCIA, NOMBRE) VALUES ('SEVILLA', 'GALLO');
INSERT INTO PEDIDOS (TIENDA, FABRICANTE, FECHA_PEDIDO, CANTIDAD)
SELECT T.NOMBRE, F.NOMBRE, GETDATE(), 30
FROM TIENDAS T, FABRICANTES F, ARTICULOS A
WHERE T.PROVINCIA = 'SEVILLA'
AND F.NOMBRE = 'GALLO'
AND F.COD_FABRICANTE = A.COD_FABRICANTE;

Procedimientos Almacenados para Gestión de Empleados

a) Procedimiento almacenado para seleccionar empleados y sueldos

CREATE PROCEDURE PA_EMPLEADOS_SUELDOS
AS
SELECT NOMBRE, APELLIDOS, SUELDO FROM EMPLEADOS;

b) Procedimiento almacenado para seleccionar empleados con sueldo superior a un parámetro

CREATE PROCEDURE PA_EMPLEADOS_SUELDO2 @SUELDO DECIMAL(6, 2)
AS
SELECT NOMBRE, APELLIDOS, SUELDO FROM EMPLEADOS WHERE SUELDO >= @SUELDO;