Ayuda ejercicios unidad 9: Programación en TRANSACT SQL (I)

Consejo: Es recomedable copiar el código en la consulta del SSMS y tratar de entenderlo desde ahí, porque son consultas extensas y se ven más claras con las palabras coloreadas.

1. Añadir a la tabla de productos un campo StockMinimo, y rellenarlo de tal forma que el stock mínimo valorado de cada artículo sea de 100€. El stock mínimo valorado es el resultado de multiplicar el stock mínimo por el precio del producto. No hace falta incluir estas sentencias en un procedimiento ya que sólo nos sirven para preparar la tabla productos pero redactarlas en Transact-SQL para repasar temas anteriores.

PRINT 'Empieza el ejercicio 1'

USE Gestion10;

Alter TABLE Productos ADD  StockMinimo INTEGER DEFAULT 0;

GO

UPDATE Productos SET StockMinimo= 100/(CASE WHEN Precio IS NULL OR Precio=0 THEN 100 ELSE Precio END);

-- Utilizamos CASE para  que si el producto no tiene precio o un precio igual a cero que asuma  StockMinimo 1.

Seguidamente, escribir un procedimiento que se pueda ejecutar en cualquier momento y que actualice una lista de productos bajo stock. Esta lista se guarda en una tabla ProductosAPedir y contiene el código completo del producto, su descripción, existencias y cantidad a pedir (para que el producto supere en 5 unidades su stock mínimo). Si un producto bajo mínimo (cuyas existencias no llegan al stock mínimo) no está en esa tabla, insertarlo, si está actualizar el campo CantidadAPedir, y si  ya no está bajo mínimo y está en la tabla, eliminar el registro de ProductosAPedir.

PRINT 'Empieza el ejercicio 1 bis'

   USE Gestion10

   IF OBJECT_ID('ActualizaProductosAPedir','P') IS NOT NULL DROP PROC  ActualizaProductosAPedir

   GO

   CREATE PROC ActualizaProductosAPedir

   AS

   BEGIN

        IF OBJECT_ID('ProductosAPedir','U') IS NULL 

          CREATE  TABLE ProductosAPedir (

                   Afab  CHAR(5),

                   Aproducto CHAR(10),

                   Adescripcion CHAR(20),

                   Aexistencias INT, 

                   Apedir SMALLINT, 

                   PRIMARY KEY (Afab,Aproducto)

                   )

        

        DELETE FROM ProductosAPedir WHERE  EXISTS (SELECT * FROM Productos WHERE  idfab=Afab AND  idproducto=Aproducto AND  existencias >= StockMinimo)

        --Borramos los ProductosAPedir de los  productos que rebasan el stock mínimo, si rebasa el stock el producto ya no  tiene que estar en la tabla

        UPDATE ProductosAPedir SET  Apedir=StockMinimo+5-existencias

        FROM ProductosAPedir INNER JOIN Productos ON idfab=Afab AND idproducto=Aproducto

        WHERE existencias <  Stockminimo

        -- Actualizamos los ProductosAPedir de productos que no  llegan al stock mínimo

        INSERT INTO  ProductosAPedir        

                    SELECT Idfab,Idproducto,Descripcion,existencias,StockMinimo+5-existencias 

                    FROM Productos

                    WHERE  existencias < Stockminimo 

                          AND  NOT EXISTS (SELECT * FROM  ProductosAPedir WHERE idfab=Afab AND idproducto=Aproducto);

        -- Insertamos nuevos ProductosAPedir  de productos que no llegan al stock mínimo y que no están ya en ProductosAPedir  (para no repetirlos).

   END;

   GO

   USE Gestion10

   SELECT * FROM  Productos WHERE existencias < Stockminimo 

   EXEC ActualizaProductosAPedir

  SELECT * FROM ProductosAPedir

2. Obtener un listado de las oficinas de una determinada región con ventas superiores a un determinado importe.

PRINT 'Empieza el ejercicio 2'

USE Gestion10

IF OBJECT_ID('Listado_Oficinas1','P') IS NOT NULL DROP PROCEDURE Listado_Oficinas1

GO

CREATE PROCEDURE Listado_Oficinas1 @region CHAR(20),@ventas MONEY

AS

      SELECT * FROM oficinas WHERE region= @region  AND ventas >  @ventas

GO

EXEC Listado_Oficinas1 Este, 1000 

EXEC Listado_Oficinas1 Este, 100000 

EXEC Listado_Oficinas1 Norte, 0 

EXEC Listado_Oficinas1 Norte,  10000 

3. Crear un nuevo procedimiento parecido al anterior pero que nos devuelva también el número de oficinas recuperadas. Utilizar un parámetro de salida.

PRINT 'Empieza el ejercicio 3'

  USE Gestion10

  IF OBJECT_ID('Listado_Oficinas2','P') IS NOT NULL DROP PROCEDURE Listado_Oficinas2

  GO

  CREATE PROCEDURE Listado_Oficinas2 @region CHAR(20),@ventas MONEY, @cuantas INT OUTPUT

  AS

        SELECT * FROM oficinas WHERE region= @region  AND ventas >  @ventas

        SET  @cuantas=(SELECT  COUNT(*) FROM oficinas WHERE  region= @region AND  ventas > @ventas)

  GO

  DECLARE @resultado INT

  EXEC Listado_Oficinas2 Este, 1000 ,@resultado OUTPUT

  SELECT @resultado

  EXEC Listado_Oficinas2 Este, 100000 ,@resultado OUTPUT

  PRINT @resultado

  EXEC Listado_Oficinas2 Norte, 0 ,@resultado OUTPUT

  PRINT @resultado

  EXEC Listado_Oficinas2 Norte, 10000 ,@resultado OUTPUT

  PRINT @resultado

 Cursos Informática Gratuitos