Archivo del Autor: cynversb

Consultas Básicas SQL

Publicado por cynversb

Durante el aprendizaje de SQL, siempre encontraremos unos Queries que usamos cotidianamente.

Comando	Utilidad
SELECT	Utilizado para seleccionar datos de la tabla que deseen ser mostrados en la consulta.
UPDATE	Permite actualizar los datos insertados en la tabla.
CREATE	Permite la creación de una base de datos o una tabla.
DELETE	Permite borrar filas de una tabla.

Comandos Básicos SQL

Publicado en Temas de Informática

Deja un comentario

Etiquetas: consultas, queries, sql

VENTAS

1.- Crear un trigger que actualice el monto de la venta total [S_VentaTotal] de la tabla Ventas cuando el vendedor modifique el pedido de cliente.

Solución:

ALTER TRIGGER ActualizarMonto ON VENTA_DETALLE FOR UPDATE
AS
BEGIN
    DECLARE @ANT_Q INT               DECLARE @NUEVA_Q INT
    DECLARE @C_VENTA INT           DECLARE @S_VENTA INT
    DECLARE @PRECIO_UNITARIO MONEY DECLARE @C_PRODUCTO INT

    IF UPDATE(Q_CANTIDAD)
    BEGIN
        SELECT @NUEVA_Q = Q_CANTIDAD FROM INSERTED
        SELECT @C_PRODUCTO = C_PRODUCTO FROM INSERTED
        SELECT @C_VENTA = C_VENTA,@ANT_Q = Q_CANTIDAD FROM DELETED
        SELECT @S_VENTA = V.S_VENTA_TOTAL FROM VENTA V WHERE V.C_VENTA = @C_VENTA
        SELECT @PRECIO_UNITARIO = P.S_PRECIO_UNITARIO FROM PRODUCTO P WHERE P.C_PRODUCTO = @C_PRODUCTO

       IF @NUEVA_Q > @ANT_Q BEGIN
            UPDATE VENTA
            SET
                S_VENTA_TOTAL = @S_VENTA + (@NUEVA_Q – @ANT_Q)*@PRECIO_UNITARIO
            WHERE
                C_VENTA = @C_VENTA
        END

        IF @NUEVA_Q < @ANT_Q BEGIN
            UPDATE VENTA
            SET
                S_VENTA_TOTAL = @S_VENTA – (@ANT_Q – @NUEVA_Q)*@PRECIO_UNITARIO
            WHERE
                C_VENTA = @C_VENTA
        END
    END
END

Comprobación:

SELECT * FROM VENTA
SELECT * FROM VENTA_DETALLE

UPDATE VENTA_DETALLE

SET Q_CANTIDAD = 25
WHERE C_PRODUCTO = 12

SELECT * FROM VENTA
SELECT * FROM VENTA_DETALLE

2.- Crear un trigger que actualice la cantidad de unidades que se tiene en stock [Q_Stock] de la tabla Productos cuando el vendedor anule o elimine toda la venta de un cliente.

Solución:

CREATE TRIGGER trgActualizar_Stock ON VENTA_DETALLE FOR DELETE
AS
BEGIN
    DECLARE @ANT_Q INT
    DECLARE @C_PROD INT
    DECLARE @Q_STOCK INT

SELECT @ANT_Q = Q_CANTIDAD,@C_PROD = C_PRODUCTO FROM DELETED
SELECT @Q_STOCK = P.Q_STOCK FROM PRODUCTO P WHERE P.C_PRODUCTO = @C_PROD

    UPDATE PRODUCTO
    SET
       Q_STOCK = Q_STOCK + @ANT_Q
   WHERE
        C_PRODUCTO = @C_PROD
END

Comprobación:

SELECT * FROM VENTA_DETALLE
SELECT * FROM PRODUCTO

DELETE FROM VENTA_DETALLE WHERE C_PRODUCTO = 12

SELECT * FROM VENTA_DETALLE
SELECT * FROM PRODUCTO

3.- Crear un trigger que actualice el monto de la venta total [S_VentaTotal] de la tabla Ventas cuando el vendedor anule o elimine la compra de uno de los productos en venta a un cliente.

Solución:

CREATE TRIGGER ActualizarMonto ON VENTA_DETALLE FOR DELETE
AS
BEGIN
   DECLARE @PRECIO INT           DECLARE @C_VENTA INT
   DECLARE @C_PRODUCTO INT       DECLARE @Q_CANTIDAD INT
   DECLARE @S_DESCUENTO REAL   DECLARE @PRECIO_UNITARIO MONEY
   DECLARE @DESC REAL

   SELECT
       @C_VENTA = C_VENTA,
       @C_PRODUCTO = C_PRODUCTO,
       @Q_CANTIDAD = Q_CANTIDAD,
       @S_DESCUENTO = S_DESCUENTO
   FROM DELETED

   SELECT @PRECIO_UNITARIO = P.S_PRECIO_UNITARIO
   FROM PRODUCTO P
   WHERE P.C_PRODUCTO = @C_PRODUCTO

   UPDATE VENTA
       SET
           S_VENTA_TOTAL = S_VENTA_TOTAL – @PRECIO_UNITARIO*@Q_CANTIDAD
       WHERE
           C_VENTA = @C_VENTA
END

Comprobación:

SELECT * FROM VENTA

DELETE FROM VENTA_DETALLE
WHERE C_PRODUCTO = 2 AND C_VENTA = 2 AND Q_CANTIDAD = 3

SELECT * FROM VENTA

AHORROS

4.- Crear un procedimiento que permita realizar operaciones de depósito de un determinado monto en nuevos soles o en dólares a una cuenta de ahorros en soles perteneciente a un cliente.

Solución:

CREATE PROCEDURE DEPOSITA_MONTO @CUENTA CHAR(5), @MONTO MONEY
AS
BEGIN TRANSACTION
        UPDATE CUENTA
        SET S_SALDO = S_SALDO + @MONTO
        WHERE C_CUENTA = @CUENTA
        COMMIT

Comprobación:

SELECT * FROM CUENTAexec DEPOSITA_MONTO ‘C0001’,1000.00SELECT * FROM CUENTA

5.- Crear un procedimiento que permita realizar operaciones de pagos de servicios en nuevos soles o en dólares de una cuenta de ahorros en soles perteneciente a un cliente.

Solución:

CREATE PROCEDURE PAGO_SERVICIOS @CUENTA CHAR(5), @MONTO MONEY
AS
BEGIN TRANSACTION
    DECLARE @SALDO MONEY
    SELECT @SALDO = C.S_SALDO
    FROM CUENTA C
    WHERE C.C_CUENTA = @CUENTA
    IF @MONTO > @SALDO
        ROLLBACK
    ELSE
    BEGIN
        DECLARE @NRO_MOV INT
        SELECT @NRO_MOV = MAX(C_MOV) + 1
        FROM MOVIMIENTO
        WHERE C_CUENTA = @CUENTA        INSERT INTO MOVIMIENTO VALUES(@CUENTA,@NRO_MOV,’50’,@MONTO,GETDATE())            UPDATE CUENTA
        SET S_SALDO = S_SALDO – @MONTO
        WHERE C_CUENTA = @CUENTA
        COMMIT
    END

Comprobación:

SELECT * FROM CUENTA
SELECT * FROM MOVIMIENTOEXEC PAGO_SERVICIOS ‘C0001’,100.00SELECT * FROM CUENTA
SELECT * FROM MOVIMIENTO

6.- Crear un procedimiento que permita realizar la transferencia de un monto en nuevos soles de una cuenta de ahorros origen a otra cuenta de ahorros destino ambas en soles.

Solución:

CREATE PROCEDURE TRANSFIERE_MONTOS @CUENTA_ORI CHAR(5),@CUENTA_DEST CHAR(5), @MONTO MONEY
AS
BEGIN TRANSACTION
    DECLARE @SALDO MONEY
   – LEER SALDO DE LA CUENTA
   SELECT @SALDO = C.S_SALDO
   FROM CUENTA C
   WHERE C.C_CUENTA = @CUENTA_ORI
   – VALIDA EL MONTO A RETIRAR
   IF @MONTO > @SALDO
       ROLLBACK — SOLO SI SE REALIZARON CAMBIOS
   ELSE
   BEGIN
       DECLARE @NRO_MOV INT
       – CALCULAR EL NRO_MOV SECUENCIAL
       SELECT @NRO_MOV = MAX(C_MOV) + 1
       FROM MOVIMIENTO
       WHERE C_CUENTA = @CUENTA_ORI

– INSERTA EL NUEVO MOVIMIENTO
INSERT INTO MOVIMIENTO VALUES(@CUENTA_ORI,@NRO_MOV,’40′,@MONTO,GETDATE())

       – ACTUALIZA EL SALDO
       UPDATE CUENTA
       SET S_SALDO = S_SALDO + @MONTO
       WHERE C_CUENTA = @CUENTA_DEST

       UPDATE CUENTA
       SET S_SALDO = S_SALDO – @MONTO
       WHERE C_CUENTA = @CUENTA_ORI
       COMMIT — ACTUALIZA CAMBIOS “LA BD DEBE SER CONSISTENTE”
   END

Comprobación:

SELECT * FROM CUENTA
SELECT * FROM MOVIMIENTO

EXEC TRANSFIERE_MONTOS ‘C0001′,’C0002′,100.00

SELECT * FROM CUENTA
SELECT * FROM MOVIMIENTO

NORTHWIND

7.- Los proveedores de productos quieren conocer a los “n” mejores vendedores que más ventas han realizado de sus productos que ofertan mediante Northwind evaluado dentro de un mes y año, para lo cual se requiere una función que devuelva el nombre del proveedor, nombre de contacto, ciudad, región, código postal, país, y del empleado sus nombres ciudad, país, y monto de venta, monto descontado.

Solución:

CREATE FUNCTION fnListarMejoresVendedores (@Mes int,@Anno int,@Top int)
RETURNS @Resultado TABLE (CompanyName nvarchar(40),ContactName nvarchar(30),
                          CompanyCity nvarchar(15),Region nvarchar(15),
                          PostalCode nvarchar(10),CompanyCountry nvarchar(15),
                          LastName nvarchar(20),FirstName nvarchar(10),
                          City nvarchar(15),Country nvarchar(15),MontoVentas float,
                          MontoDescontado float)
AS
BEGIN
   declare @promedio as float
   INSERT INTO @Resultado
   SELECT
       TOP (@Top) S.CompanyName,S.ContactName,S.City AS CompanyCity,
        S.Region, S.PostalCode, S.Country AS CompanyCountry,
        E.LastName, E.FirstName, E.City, E.Country,
        sum(d.Quantity * d.unitprice) as MontoVentas,
       convert(decimal(12,2),sum(d.Discount)) as MontoDescontado
   FROM
        EMPLOYEES E INNER JOIN ORDERS O ON E.EmployeeID=O.EmployeeID INNER JOIN [Order Details] D
       INNER JOIN PRODUCTS P ON D.ProductID=P.ProductID INNER JOIN SUPPLIERS S
       ON P.supplierID=S.supplierID ON O.orderid=D.orderid
   WHERE
       month(orderdate)=@Mes AND year(orderdate)=@Anno
   GROUP BY
       S.CompanyName,S.ContactName,S.City,S.Region,
       S.PostalCode,S.Country,E.LastName,E.FirstName,
       E.City,E.Country
   ORDER BY
       sum(d.Quantity * d.unitprice) DESC

RETURN
END
GO

8.- Crear un Store Procedure que liste los productos y cantidad comprada en unidades y monto a pagar en la compra por cada cliente, obtener el listado para el nombre de una categoría del producto que es ingresado como parámetro al procedimiento.

Solución:

CREATE PROCEDURE uspListarProductos @CategoryName nvarchar(15)
AS
   SELECT
       C.CompanyName,C.City,C.Country,P.ProductName,P.UnitPrice,
        Ct.CategoryName,CONVERT(varchar(300),Ct.[Description]) as Descripcion,
       sum(d.Quantity) as Unidades,sum(d.Quantity * d.unitprice) as Monto
   FROM
       CUSTOMERS C INNER JOIN ORDERS O ON C.CustomerID=O.CustomerID INNER JOIN [Order Details] D
        INNER JOIN PRODUCTS P ON D.ProductID=P.ProductID INNER JOIN CATEGORIES Ct
        ON P.CategoryID=Ct.CategoryID ON O.orderid=D.orderid
   WHERE
       Ct.CategoryName=@CategoryName
   GROUP BY
       C.CompanyName,C.City,C.Country,P.ProductName,P.UnitPrice,
       Ct.CategoryName,convert(varchar(300),Ct.[Description])

Comprobación:

EXEC uspListarProductos ‘Beverages’

Publicado en Prácticas

Deja un comentario

PC3 2010-2 A

Jun 29

Publicado por cynversb

VENTAS

1.- Crear un trigger que actualice la cantidad de unidades que se tiene en stock [Q_Stock] de la tabla Productos cuando el vendedor modifique el pedido de un cliente.

Solución:

ALTER TRIGGER ActualizarStock ON VENTA_DETALLE FOR UPDATE
AS
BEGIN
DECLARE @ANT_Q INT DECLARE @NUEVA_Q INT
DECLARE @C_PROD INT DECLARE @Q_STOCK INT

   IF UPDATE(Q_CANTIDAD)
   BEGIN
       SELECT @NUEVA_Q = Q_CANTIDAD,@C_PROD = C_PRODUCTO FROM INSERTED
       SELECT @ANT_Q = Q_CANTIDAD FROM DELETED
       SELECT @Q_STOCK = P.Q_STOCK FROM PRODUCTO P WHERE P.C_PRODUCTO = @C_PROD

       IF(@Q_STOCK + @ANT_Q – @NUEVA_Q < 0)
           BEGIN
               RAISERROR(‘El Stock es menor a 0’,16,1)
               ROLLBACK TRANSACTION
               RETURN
           END
       ELSE
           BEGIN
               UPDATE PRODUCTO
               SET
                   Q_STOCK = (Q_STOCK + @ANT_Q) – @NUEVA_Q
               WHERE
                   C_PRODUCTO = @C_PROD
           END
   END
END

Comprobación:

UPDATE VENTA_DETALLE

SET Q_CANTIDAD = 4000
WHERE C_PRODUCTO = 12

2.- Crear un trigger que actualice el monto de la venta total [S_VentaTotal] de la tabla Ventas cuando el vendedor anule o elimine la compra de uno de los productos en venta a un cliente.

Solución:

   SELECT
       @C_VENTA = C_VENTA,
       @C_PRODUCTO = C_PRODUCTO,
       @Q_CANTIDAD = Q_CANTIDAD,
       @S_DESCUENTO = S_DESCUENTO
   FROM DELETED

   SELECT @PRECIO_UNITARIO = P.S_PRECIO_UNITARIO
   FROM PRODUCTO P
   WHERE P.C_PRODUCTO = @C_PRODUCTO

   UPDATE VENTA
       SET
           S_VENTA_TOTAL = S_VENTA_TOTAL – @PRECIO_UNITARIO*@Q_CANTIDAD
       WHERE
           C_VENTA = @C_VENTA
END

Comprobación:

SELECT * FROM VENTA

DELETE FROM VENTA_DETALLE
WHERE C_PRODUCTO = 2 AND C_VENTA = 2 AND Q_CANTIDAD = 3

SELECT * FROM VENTA

3.- Crear un trigger que actualice la cantidad de unidades que se tiene en stock [Q_Stock] de la tabla Productos cuando el vendedor anule o elimine la compra de uno de los productos en venta a un cliente.

Solución:

CREATE TRIGGER ActualizarStockEliminado ON VENTA_DETALLE FOR DELETE
AS
BEGIN
    DECLARE @ANT_Q INT
    DECLARE @NUEVA_Q INT
    DECLARE @C_PROD INT

SELECT @ANT_Q = Q_CANTIDAD,@C_PROD = C_PRODUCTO FROM DELETED

    UPDATE PRODUCTO
    SET
       Q_STOCK = Q_STOCK + @ANT_Q
    WHERE
        C_PRODUCTO = @C_PROD
END

Comprobación:

SELECT * FROM PRODUCTO

DELETE FROM VENTA_DETALLE WHERE C_PRODUCTO = 10 AND C_VENTA = 1 AND Q_CANTIDAD = 2

SELECT * FROM PRODUCTO

AHORROS

4.- Crear un procedimiento que permita abrir una cuenta de ahorros para un cliente con un saldo inicial.

Solución:

CREATE PROCEDURE uspApertura_Cuenta @COD_CLIENTE CHAR(5), @MONTO MONEY
AS
BEGIN
TRANSACTION
DECLARE @CUENTA CHAR(5) DECLARE @AUX INT

   SELECT @CUENTA = MAX(C_CUENTA) FROM CUENTA
   IF @CUENTA is NULL
       BEGIN
           SET @CUENTA = ‘C0001’
           INSERT INTO CUENTA VALUES(@CUENTA, @COD_CLIENTE, @MONTO)
           INSERT INTO MOVIMIENTO VALUES(@CUENTA,1,’10’,@MONTO,GETDATE())
       END
   ELSE
       BEGIN
           SET @AUX = CONVERT(INT,RIGHT(@CUENTA,4)) + 1
           SET @CUENTA = ‘C’ + RIGHT(‘0000’ + CONVERT(VARCHAR,@AUX),4)

           INSERT INTO CUENTA VALUES(@CUENTA, @COD_CLIENTE, @MONTO)
           INSERT INTO MOVIMIENTO VALUES(@CUENTA,1,’10’,@MONTO,GETDATE())
       END
COMMIT

Comprobación:

exec uspApertura_Cuenta ‘C0010’, 200.00

SELECT * FROM CUENTA

SELECT * FROM MOVIMIENTO

5.- Crear un procedimiento que permita abrir una cuenta de ahorros para un cliente con un saldo inicial.

Solución:

CREATE PROCEDURE RETIRA_MONTO @CUENTA CHAR(5), @MONTO MONEY
AS
BEGIN TRANSACTION
    DECLARE @SALDO MONEY
   — LEER SALDO DE LA CUENTA
   SELECT @SALDO = C.S_SALDO
   FROM CUENTA C
   WHERE C.C_CUENTA = @CUENTA
   — VALIDA EL MONTO A RETIRAR
   IF @MONTO > @SALDO
       ROLLBACK — SOLO SI SE REALIZARON CAMBIOS
   ELSE
   BEGIN
       DECLARE @NRO_MOV INT
       — CALCULAR EL NRO_MOV SECUENCIAL
       SELECT @NRO_MOV = MAX(C_MOV) + 1
       FROM MOVIMIENTO
       WHERE C_CUENTA = @CUENTA

— INSERTA EL NUEVO MOVIMIENTO
INSERT INTO MOVIMIENTO VALUES(@CUENTA,@NRO_MOV,’30’,@MONTO,GETDATE())

       — ACTUALIZA EL SALDO
       UPDATE CUENTA
       SET S_SALDO = S_SALDO – @MONTO
       WHERE C_CUENTA = @CUENTA
       COMMIT — ACTUALIZA CAMBIOS «LA BD DEBE SER CONSISTENTE»
   END

Comprobación:

SELECT * FROM CUENTA

EXEC RETIRA_MONTO ‘C0001’, 50.0

SELECT * FROM CUENTA

6.- Crear un procedimiento que permita realizar la transferencia de un monto en nuevos soles de una cuenta de ahorros origen a otra cuenta de ahorros destino ambas en soles.

Solución:

CREATE PROCEDURE TRANSFIERE_MONTOS @CUENTA_ORI CHAR(5),@CUENTA_DEST CHAR(5), @MONTO MONEY
AS
BEGIN TRANSACTION
    DECLARE @SALDO MONEY
   — LEER SALDO DE LA CUENTA
   SELECT @SALDO = C.S_SALDO
   FROM CUENTA C
   WHERE C.C_CUENTA = @CUENTA_ORI
   — VALIDA EL MONTO A RETIRAR
   IF @MONTO > @SALDO
       ROLLBACK — SOLO SI SE REALIZARON CAMBIOS
   ELSE
   BEGIN
       DECLARE @NRO_MOV INT
       — CALCULAR EL NRO_MOV SECUENCIAL
       SELECT @NRO_MOV = MAX(C_MOV) + 1
       FROM MOVIMIENTO
       WHERE C_CUENTA = @CUENTA_ORI

— INSERTA EL NUEVO MOVIMIENTO
INSERT INTO MOVIMIENTO VALUES(@CUENTA_ORI,@NRO_MOV,’40’,@MONTO,GETDATE())

       — ACTUALIZA EL SALDO
       UPDATE CUENTA
       SET S_SALDO = S_SALDO + @MONTO
       WHERE C_CUENTA = @CUENTA_DEST

       UPDATE CUENTA
       SET S_SALDO = S_SALDO – @MONTO
       WHERE C_CUENTA = @CUENTA_ORI
       COMMIT — ACTUALIZA CAMBIOS «LA BD DEBE SER CONSISTENTE»
   END

Comprobación:

SELECT * FROM CUENTA
SELECT * FROM MOVIMIENTO

EXEC TRANSFIERE_MONTOS ‘C0001′,’C0002’,100.00

SELECT * FROM CUENTA
SELECT * FROM MOVIMIENTO

NORTHWIND

7.- Listar mediante una función los empleados que han realizado ventas superiores al promedio durante el año 1997 junto a los clientes que han realizado compras inferiores al promedio en el mismo año, la función debe retornar los nombres, ciudad, región, código postal, país, ventas y compras en una sola tabla.

Solución:

CREATE FUNCTION fnListarEmpleados (@Mes INT,@Anno INT)
RETURNS @Resultado TABLE(Names NVARCHAR(40),City NVARCHAR(15),Region NVARCHAR(15),
                         PostalCode NVARCHAR(10),Country NVARCHAR(15),Ventas FLOAT,
                         Compras FLOAT) AS
BEGIN
   DECLARE @promedio AS FLOAT
   SELECT @promedio=SUM((d.Quantity * d.unitprice)/ d.Quantity)
   FROM [Order Details] d INNER JOIN Orders o ON o.orderid=d.orderid
   WHERE MONTH(o.orderdate)=@Mes AND YEAR(o.orderdate)=@Anno

   INSERT INTO @Resultado
   SELECT
           E.LastName +’ ‘+ E.FirstName AS Nombre,E.City,E.Region,E.PostalCode,E.Country,
           SUM(d.Quantity * d.unitprice) as Ventas,0.00 as Compras
   FROM
       EMPLOYEES E INNER JOIN ORDERS O ON E.EmployeeID=O.EmployeeID
       INNER JOIN [Order Details] D ON O.orderid=D.orderid
   WHERE MONTH(o.orderdate)=@Mes AND YEAR(o.orderdate)=@Anno
   GROUP BY E.LastName,E.FirstName ,E.City,E.Region,E.PostalCode,E.Country
   HAVING SUM(d.Quantity * d.unitprice) >@promedio
   UNION
   SELECT
       C.CompanyName as Nombre,C.City,C.Region,C.PostalCode,C.Country,
       0.00 as Ventas,sum(d.Quantity * d.unitprice) as Compras
   FROM
       CUSTOMERS C INNER JOIN ORDERS O ON C.CustomerID=O.CustomerID
       INNER JOIN [Order Details] D ON O.orderid=D.orderid
   WHERE MONTH(o.orderdate)=@Mes AND YEAR(o.orderdate)=@Anno
   GROUP BY C.CompanyName,C.City,C.Region,C.PostalCode,C.Country
   HAVING SUM(d.Quantity * d.unitprice) <@promedio
   RETURN
END
GO

8.- Escribir un procedimiento que permita insertar, modificar y eliminar productos considerando la integridad de los datos de acuerdo a la operación a realizar.

Solución:

CREATE PROCEDURE uspProductoIUD (
    @ProductID int, @ProductName nvarchar(40), @SupplierID int,
    @CategoryID int, @QuantityPerUnit nvarchar(20),
    @UnitPrice money, @UnitsInStock smallint, @UnitsOnOrder smallint,
    @ReorderLevel smallint, @Discontinued bit,@Operacion int
)
AS BEGIN
   IF @Operacion = 1 BEGIN
       INSERT INTO Products
        (ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,
         UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued)
       VALUES
       (@ProductName,@SupplierID,@CategoryID,@QuantityPerUnit,
       @UnitPrice,@UnitsInStock,@UnitsOnOrder,@ReorderLevel,@Discontinued)
   END

   IF @Operacion = 2 BEGIN
       UPDATE Products
       SET
           ProductName = @ProductName,SupplierID = @SupplierID,
           CategoryID = @CategoryID,QuantityPerUnit = @QuantityPerUnit,
           UnitPrice = @UnitPrice,UnitsInStock = @UnitsInStock,
           UnitsOnOrder = @UnitsOnOrder,ReorderLevel = @ReorderLevel,
           Discontinued = @Discontinued
       WHERE
           ProductID = @ProductID
   END

   IF @Operacion = 3 BEGIN
       DELETE FROM Products WHERE ProductID = @ProductID
   END
END

Comprobación:

exec uspProductoIUD 78,’Celular LG’,12,2,’1 box’,800,1000,4,2,0,1

SELECT * FROM Products WHERE ProductID = 78

exec uspProductoIUD 78,’Celular LG Actualizado’,12,2,’1 box’,800,1000,4,2,0,2

SELECT * FROM Products WHERE ProductID = 78

exec uspProductoIUD 78,’Celular LG’,12,2,’1 box’,800,1000,4,2,0,3

SELECT * FROM Products WHERE ProductID = 78

Publicado en Prácticas

Deja un comentario

Triggers

Jun 26

Publicado por cynversb

Triggers

Implementar y comprobar un trigger que contabilice el número de participantes en AJEDREZ

Escribimos en el Query:

CREATE TRIGGER number_Participantes ON PARTICIPANTE
FOR INSERT,UPDATE
AS BEGIN
    DECLARE @N INT
        SELECT @N = COUNT(*)
        FROM PARTICIPANTE_HOTEL PH

     PRINT @N
END

Luego realizamos la consulta:

INSERT INTO PARTICIPANTE
(C_ASOCIADO, C_PAIS, N_NOMBRE, T_DIRECCION, D_FECHA_NAC) 
 VALUES
 ( 'A11', 'P1', 'Julio Qwerty', 'Av. Universidad', NULL )

Implementar y comprobar un trigger que contabilice el número partidos que participo un Jugador en CAMPEONATO

Escribimos en el Query:

CREATE TRIGGER Numero_Partidos ON JUG_PARTIDO
FOR INSERT,UPDATE
AS BEGIN
    SELECT C_JUGADOR, COUNT(*)
    FROM JUG_PARTIDO J
    GROUP BY J.C_JUGADOR
END

Luego realizamos la consulta:

INSERT INTO JUG_PARTIDO
(C_JUGADOR,NRO_PARTIDO,N_POSICION,NRO_CAMISETA,
 F_EXPULSADO,F_AMONESTADO,F_GOLEADOR) 
 VALUES
 ('J19', 1, 'DELANTERO', 11, NULL, NULL, NULL )

Implementar mediante trigges la auditoria de participantes en AJEDREZ

Escribimos en el Query:

CREATE TRIGGER Auditoria ON PARTICIPANTE
FOR INSERT
AS BEGIN
  DECLARE @N VARCHAR(3)
  SELECT @N = C_ASOCIADO
  FROM INSERTED

  PRINT 'Codigo : ' + @N
  PRINT 'Fecha de insercion : ' + CONVERT(VARCHAR(50),GETDATE())
END

Luego realizamos la consulta:

INSERT INTO PARTICIPANTE
(C_ASOCIADO,C_PAIS,N_NOMBRE,T_DIRECCION,D_FECHA_NAC)
VALUES
('A12','P1','Carlos Mariategui','Calle Los Geranios',NULL)

Publicado en Prácticas

Deja un comentario

Stored Procedures

Jun 26

Publicado por cynversb

Los Stored Procedures se usan a menudo, pero no siempre, para realizar consultas SQL sobre los objetos del banco de datos de una manera abstracta, desde el punto de vista del cliente de la aplicación. Un procedimiento almacenado permite agrupar en forma exclusiva parte de algo específico que se desee realizar o, mejor dicho, el SQL apropiado para dicha acción.

Stored Procedures

Crear un procedimiento que registre un jugador, solo datos personales en CAMPEONATO

Escribimos en el Query:

CREATE PROCEDURE REGISTRA_JUGADOR @CODIGO CHAR(3), @N_JUGADOR CHAR(223), @FECHA_NAC date AS BEGIN INSERT   (C_JUGADOR,N_JUGADOR,D_NACIMIENTO)
 VALUES
 (@CODIGO,@N_JUGADOR,@FECHA_NAC) END

Luego realizamos la consulta:

EXEC REGISTRA_JUGADOR 'J21','Agustin Xerox',NULL

Crear un procedimiento que liste los jugadores de un Equipo en CAMPEONATO

Escribimos en el Query:

CREATE PROCEDURE LISTAR_JUGADORES_EQUIPO @C_EQUIPO CHAR(2)
AS
BEGIN
  SELECT J.C_JUGADOR
  FROM JUG_EQ_CAMP J
  WHERE J.C_EQUIPO = @C_EQUIPO
END

Luego realizamos la consulta:

EXEC LISTAR_JUGADORES_EQUIPO 'E1'

Crear un procedimiento que registre los movimientos realizados por un jugador y su comentario en AJEDREZ

Escribimos en el Query:

CREATE PROCEDURE REGISTRAR_MOVIMIENTOS_JUGADOR @C_JUGADOR VARCHAR(3), 
                 @NRO_ORDEN INT, @NRO_PARTIDA INT, @N_FICHA VARCHAR(20),
                 @T_POS_INICIAL VARCHAR(20), @T_POS_FINAL VARCHAR(20),
                 @T_COMENTARIO VARCHAR(20) 
AS
BEGIN
    INSERT INTO Movimiento
    (
        Nro_Orden,
        Nro_Partida,
        N_Ficha,
        T_Pos_Inicial,
        T_Pos_Final,
        T_Comentario,
        C_Jugador
    )
    VALUES
    (
        @NRO_ORDEN,
        @NRO_PARTIDA,
        @N_FICHA,
        @T_POS_INICIAL,
        @T_POS_FINAL,
        @T_COMENTARIO,
        @C_JUGADOR
    )
END

Luego realizamos la consulta:

EXEC REGISTRAR_MOVIMIENTOS_JUGADOR 'J01',1,1,'Alfil','E2','D1','JAQUE!'

Publicado en Prácticas

Deja un comentario

Inserción de funciones

Jun 26

Publicado por cynversb

Funciones

Implementar una función que devuelva el número de participantes en el campeonato de AJEDREZ

Escribimos en el Query:

CREATE FUNCTION Num_Part_Campeonato()
RETURNS INT
BEGIN

    DECLARE @N INT
    SELECT @N = COUNT(*)
    FROM PARTICIPANTE_HOTEL PH

 RETURN @N    
END

Luego realizamos la consulta:

SELECT dbo.Num_Part_Campeonato() 'CantPart'

Implementar una función que devuelva la lista de participantes en el campeonato de AJEDREZ

Escribimos en el Query:

CREATE FUNCTION LIST_PARTICIPANTE_CAMPEONATO()
RETURNS TABLE
AS
    RETURN SELECT *FROM PARTICIPANTE P
GO

Luego realizamos la consulta:

SELECT *FROM dbo.LIST_PARTICIPANTE_CAMPEONATO()

Implementar una función que devuelva el número de equipos en los que participo un jugador en CAMPEONATO
Escribimos en el Query:

CREATE FUNCTION Num_Equipos_Jugador(@CODIGO char(3))
RETURNS INT
BEGIN

    DECLARE @N INT
    SELECT @N = COUNT(*)
    FROM JUG_EQ_CAMP J
    WHERE J.C_Jugador = @CODIGO

 RETURN @N    
END

Luego realizamos la consulta:

SELECT dbo.Num_Equipos_Jugador ('J01')

Implementar una función que devuelva la lista de equipos en los que participo un jugador en CAMPEONATO

Escribimos en el Query:

CREATE FUNCTION LIST_EQUIPOS_JUGADOR(@CODIGO CHAR(3))
RETURNS TABLE
AS
    RETURN SELECT C_EQUIPO FROM JUG_EQ_CAMP J WHERE 
    J.C_JUGADOR = @CODIGO
GO

Luego realizamos la consulta:

SELECT *FROM dbo.LIST_EQUIPOS_JUGADOR('J01')

Publicado en Prácticas

Deja un comentario

Tarea N° 05 – Querys en SQL Server (Ajedrez)

Jun 10

Publicado por cynversb

JOIN

1.- Muestra el país de cada jugador

2.- Muestra las fechas de las jornadas realizadas en el hotel con código ‘H04’

ORDER BY

1.- Muestra el país de cada jugador agrupados por el nombre de país

2.- Muestra a los jugadores ordenados por su nivel

HAVING

1.- Muestra los árbitros que tengan mas de 8 años de experiencia

2.- Muestra las salas con capacidad mayor a 20 agrupados por su código y número de sala

JOIN, WHERE, GROUP BY, HAVING

1.- Muestra el nombre de participante y el nombre de hotel de aquellos participantes que iniciaron una reserva en Marzo del 2011

2.- Muestra las salas que tienen capacidad mayor a 20 y código de medio de comunicación igual a ‘M01’

OUTER JOIN

1.- Selecciona todos los hoteles aunque no tengan salas asociadas

2.- Selecciona a todos los participantes aunque no sean jugadores

Publicado en Prácticas

Deja un comentario

Campeonato (ejercicio)

Jun 3

Publicado por cynversb

ERWIN

SQL SERVER

Modelo fisico de la Base de Datos en SQL SERVER

Creación de Tablas

Query:

CREATE TABLE CAMPEONATO(
C_CAMPEONATO CHAR(3) NOT NULL,
N_CAMPEONATO VARCHAR(220),
Q_PARTIDOS INT,
PRIMARY KEY (C_CAMPEONATO))

CREATE TABLE EQUIPO(
C_EQUIPO CHAR(2) NOT NULL,
N_EQUIPO VARCHAR(220),
PRIMARY KEY (C_EQUIPO))

CREATE TABLE JUGADOR(
C_JUGADOR CHAR(3) NOT NULL,
N_JUGADOR VARCHAR(220),
D_NACIMIENTO DATE,
PRIMARY KEY (C_JUGADOR));

CREATE TABLE JUG_EQ_CAMP(
C_JUGADOR CHAR(3) NOT NULL,
C_CAMPEONATO CHAR(3) NOT NULL,
C_EQUIPO CHAR(2),
PRIMARY KEY (C_JUGADOR, C_CAMPEONATO),
FOREIGN KEY (C_JUGADOR) REFERENCES JUGADOR(C_JUGADOR)
)

— ADICIONA CLAVES FORANEAS
ALTER TABLE JUG_EQ_CAMP
ADD FOREIGN KEY (C_CAMPEONATO)
REFERENCES CAMPEONATO(C_CAMPEONATO)

ALTER TABLE JUG_EQ_CAMP
ADD FOREIGN KEY (C_EQUIPO)
REFERENCES EQUIPO(C_EQUIPO)

— BORRA UNA CLAVE FORANEA
— ALTER TABLE JUG_EQ_CAMP
—   DROP FK__JUG_EQ_CA__C_CAM__1A14E395

CREATE TABLE ESTADIO(
C_ESTADIO CHAR(3) NOT NULL,
N_ESTADIO VARCHAR(220),
PRIMARY KEY (C_ESTADIO))

CREATE TABLE PARTIDO(
NRO_PARTIDO INT NOT NULL,
C_ESTADIO_PART CHAR(3),
C_EQUIPO_1 CHAR(2),
C_EQUIPO_2 CHAR(2),
C_CAMPEONATO CHAR(3),
D_PARTIDO DATE,
N_JUEZ_LINEA1 VARCHAR(250),
N_JUEZ_LINEA2 VARCHAR(250),
Q_GOLES_E1 INT,
Q_GOLES_E2 INT,
N_ARBITRO VARCHAR(220),
C_RESULTADO VARCHAR(20),
PRIMARY KEY (NRO_PARTIDO),
FOREIGN KEY (C_ESTADIO_PART)
REFERENCES ESTADIO(C_ESTADIO),
FOREIGN KEY (C_EQUIPO_1)
REFERENCES EQUIPO(C_EQUIPO),
FOREIGN KEY (C_EQUIPO_2)
REFERENCES EQUIPO(C_EQUIPO),
FOREIGN KEY (C_CAMPEONATO)
REFERENCES CAMPEONATO(C_CAMPEONATO)
)

CREATE TABLE JUG_PARTIDO(
C_JUGADOR   CHAR(3) NOT NULL,
NRO_PARTIDO INT NOT NULL,
N_POSICION VARCHAR(220),
NRO_CAMISETA INT NOT NULL,
F_EXPULSADO CHAR(1),
F_AMONESTADO CHAR(1),
F_GOLEADOR   CHAR(1),
PRIMARY KEY (C_JUGADOR, NRO_PARTIDO),
FOREIGN KEY (C_JUGADOR)
REFERENCES JUGADOR(C_JUGADOR),
FOREIGN KEY (NRO_PARTIDO)
REFERENCES PARTIDO(NRO_PARTIDO)

)

Inserción de Datos

Query:

INSERT INTO EQUIPO VALUES
(‘E1’, ‘ALIANZA LIMA’),
(‘E2’, ‘UNIVERSITARIO’),
(‘E3’, ‘SPORT ANCASH’),
(‘E4’, ‘CRISTAL’),
(‘E5’, ‘BOYS’)

INSERT INTO CAMPEONATO
(N_CAMPEONATO, Q_PARTIDOS, C_CAMPEONATO)
VALUES
(‘APERTURA 2011’, 32, ‘A11’),
(‘APERTURA 2010’, 12, ‘A10’),
(‘CLAUSURA 2011’, 28, ‘C11’),
(‘CLAUSURA 2010’, 24, ‘C10’)

INSERT INTO JUGADOR(C_JUGADOR, N_JUGADOR)
VALUES
(‘J01′,’WALDIR SAENZ’),
(‘J02′,’JOSE LUIS CARRANZA’),
(‘J03′,’Roberto Palacios’),
(‘J04′,’Héctor Chumpitaz’),
(‘J05′,’Jorge Soto’),
(‘J06′,’Juan Jayo’),
(‘J07′,’Nolberto Solano’),
(‘J08′,’Rubén Díaz’),
(‘J09′,’Juan Reynoso’),
(‘J10′,’Percy Olivares’),
(‘J11′,’José Velásquez’),
(‘J12′,’Teófilo Cubillas’),
(‘J13′,’Teodoro Fernández’),
(‘J14′,’Franco Navarro’),
(‘J15′,’Hugo Sotil’),
(‘J16′,’Oswaldo Ramírez’),
(‘J17′,’Pedro Pablo León’),
(‘J18′,’Óscar Gómez Sánchez’),
(‘J19′,’Jorge Alcalde’),
(‘J20′,’Claudio Pizarro’)

INSERT INTO JUG_EQ_CAMP VALUES
(‘J01′,’A10′,’E1’),
(‘J02′,’A10′,’E1’),
(‘J03′,’A10′,’E1’),
(‘J04′,’A10′,’E1’),
(‘J05′,’A10′,’E1’),
(‘J07′,’A10′,’E2’),
(‘J08′,’A10′,’E2’),
(‘J09′,’A10′,’E2’),
(‘J10′,’A10′,’E2’),
(‘J11′,’A10′,’E2’),
(‘J06′,’A10′,’E3’),
(‘J12′,’A10′,’E3’),
(‘J13′,’A10′,’E3’),
(‘J14′,’A10′,’E3’),
(‘J15′,’A10′,’E3’),
(‘J16′,’A10′,’E4’),
(‘J17′,’A10′,’E4’),
(‘J18′,’A10′,’E4’),
(‘J19′,’A10′,’E5’),
(‘J20′,’A10′,’E5’),

(‘J01′,’C10′,’E5’),
(‘J02′,’C10′,’E5’),
(‘J03′,’C10′,’E5’),
(‘J04′,’C10′,’E5’),
(‘J05′,’C10′,’E5’),
(‘J07′,’C10′,’E3’),
(‘J08′,’C10′,’E3’),
(‘J09′,’C10′,’E3’),
(‘J10′,’C10′,’E3’),
(‘J11′,’C10′,’E3’),
(‘J06′,’C10′,’E2’),
(‘J12′,’C10′,’E2’),
(‘J13′,’C10′,’E2’),
(‘J14′,’C10′,’E2’),
(‘J15′,’C10′,’E2’),
(‘J16′,’C10′,’E1’),
(‘J17′,’C10′,’E1’),
(‘J18′,’C10′,’E1’),
(‘J19′,’C10′,’E1’),
(‘J20′,’C10′,’E1’)

INSERT INTO ESTADIO VALUES
(‘ES1′,’ESTADIO MONUMENTAL’),
(‘ES2′,’ESTADIO NACIONAL’),
(‘ES3′,’ESTADIO SAN GUSTIN’),
(‘ES4′,’ESTADIO GARCILASO DE LA VEGA’),
(‘ES5′,’ESTADIO ALIANZA LIMA’)

INSERT INTO PARTIDO VALUES
(1, ‘ES1’, ‘E1’, ‘E2’, ‘A10’, ‘20100120’,
‘Albán Ynga,Edgard’, ‘Ávila Minchola,Julio César’, 1, 2,
‘López Cruz,Raúl Eduardo’, ‘GA GANADOR’),
(2, ‘ES2’, ‘E4’, ‘E3’, ‘A10’, ‘20100120’,
‘Albán’, ‘Ávila’, 2, 2, ‘López’, ‘EM EMPATE’),
(3, ‘ES3’, ‘E1’, ‘E3’, ‘A10’, ‘20100320’,
‘Albán’, ‘Ávila’, 2, 3, ‘López’, ‘GA GANADOR’),
(4, ‘ES1’, ‘E2’, ‘E5’, ‘A10’, ‘20100220’,
‘Albán’, ‘Ávila’, 3, 2, ‘López’, ‘GA GANADOR’)

INSERT JUG_PARTIDO VALUES
(‘J01’, 1, ‘DELANTERO’     ,10, ‘N’, ‘S’, ‘S’),
(‘J02’, 1, ‘DEFENSA’       , 5, ‘N’, ‘N’, ‘N’),
(‘J03’, 1, ‘MEDIO CAMPISTA’, 8, ‘S’, ‘N’, ‘N’),
(‘J04’, 1, ‘ARQUERO’       , 1, ‘N’, ‘N’, ‘N’),
(‘J05’, 1, ‘VOLANTE’       , 6, ‘N’, ‘S’, ‘N’),
(‘J07’, 1, ‘DELANTERO’     ,12, ‘N’, ‘S’, ‘N’),
(‘J08’, 1, ‘DEFENSA’       , 7, ‘S’, ‘N’, ‘N’),
(‘J09’, 1, ‘MEDIO CAMPISTA’, 9, ‘S’, ‘N’, ‘N’),
(‘J10’, 1, ‘ARQUERO’       , 1, ‘N’, ‘N’, ‘N’),
(‘J11’, 1, ‘VOLANTE’       ,11, ‘N’, ‘S’, ‘N’),
(‘J17’, 2, ‘DELANTERO’     ,10, ‘N’, ‘S’, ‘S’),
(‘J18’, 2, ‘DEFENSA’       , 5, ‘N’, ‘N’, ‘N’),
(‘J16’, 2, ‘MEDIO CAMPISTA’, 8, ‘S’, ‘N’, ‘N’),
(‘J06’, 2, ‘ARQUERO’       , 1, ‘N’, ‘N’, ‘N’),
(‘J12’, 2, ‘VOLANTE’       , 6, ‘N’, ‘S’, ‘N’),
(‘J13’, 2, ‘DELANTERO’     ,12, ‘N’, ‘S’, ‘N’),
(‘J15’, 2, ‘DEFENSA’       , 7, ‘S’, ‘N’, ‘N’),
(‘J14’, 2, ‘MEDIO CAMPISTA’, 9, ‘S’, ‘N’, ‘N’)

Consultas

Query:

SELECT * FROM JUGADOR;
SELECT * FROM EQUIPO;
SELECT * FROM CAMPEONATO;
SELECT * FROM JUG_EQ_CAMP;

— LOS JUGADORES QUE SE LLAMEN JORGE
SELECT *
FROM JUGADOR J
WHERE J.N_JUGADOR LIKE ‘%JORGE%’

— CAMPEONATOS EN LOS QUE JUGO JORGE
SELECT J.N_JUGADOR, C.N_CAMPEONATO, E.N_EQUIPO
FROM JUGADOR J JOIN JUG_EQ_CAMP JEC ON (J.C_JUGADOR = JEC.C_JUGADOR)
JOIN CAMPEONATO C ON (C.C_CAMPEONATO = JEC.C_CAMPEONATO)
JOIN EQUIPO E ON (E.C_EQUIPO = JEC.C_EQUIPO)
WHERE J.N_JUGADOR LIKE ‘%JORGE%’

— LISTADO DE JUGADORES POR EQUIPO
SELECT C.N_CAMPEONATO, E.C_EQUIPO, E.N_EQUIPO, J.C_JUGADOR, J.N_JUGADOR
FROM JUGADOR J JOIN JUG_EQ_CAMP JEC ON (J.C_JUGADOR = JEC.C_JUGADOR)
JOIN CAMPEONATO C ON (C.C_CAMPEONATO = JEC.C_CAMPEONATO)
JOIN EQUIPO E ON (E.C_EQUIPO = JEC.C_EQUIPO)
ORDER BY C.N_CAMPEONATO, E.N_EQUIPO

SELECT * FROM PARTIDO
SELECT * FROM ESTADIO
SELECT * FROM JUG_PARTIDO

SELECT J.C_JUGADOR, J.N_JUGADOR, E1.N_EQUIPO, E2.N_EQUIPO, P.NRO_PARTIDO,
JP.F_GOLEADOR ‘GOLEADOR’
FROM JUGADOR J JOIN JUG_PARTIDO JP ON(J.C_JUGADOR = JP.C_JUGADOR)
JOIN PARTIDO P ON (JP.NRO_PARTIDO = P.NRO_PARTIDO)
JOIN EQUIPO E1 ON (E1.C_EQUIPO = P.C_EQUIPO_1)
JOIN EQUIPO E2 ON (E2.C_EQUIPO = P.C_EQUIPO_2)

SELECT J.C_JUGADOR, J.N_JUGADOR, E1.N_EQUIPO, E2.N_EQUIPO, P.NRO_PARTIDO,
JP.F_GOLEADOR ‘GOLEADOR’
FROM JUGADOR J JOIN JUG_PARTIDO JP ON(J.C_JUGADOR = JP.C_JUGADOR)
JOIN PARTIDO P ON (JP.NRO_PARTIDO = P.NRO_PARTIDO)
JOIN EQUIPO E1 ON (E1.C_EQUIPO = P.C_EQUIPO_1)
JOIN EQUIPO E2 ON (E2.C_EQUIPO = P.C_EQUIPO_2)
WHERE JP.F_GOLEADOR = ‘S’

MySQL

Modelo Físico de la Base de Datos My SQL

Creación de Tablas MySQL

Query:

CREATE TABLE CAMPEONATO(
C_CAMPEONATO CHAR(3) NOT NULL,
N_CAMPEONATO VARCHAR(220),
Q_PARTIDOS INT,
PRIMARY KEY (C_CAMPEONATO));

CREATE TABLE EQUIPO(
C_EQUIPO CHAR(2) NOT NULL,
N_EQUIPO VARCHAR(220),
PRIMARY KEY (C_EQUIPO));

CREATE TABLE JUGADOR(
C_JUGADOR CHAR(3) NOT NULL,
N_JUGADOR VARCHAR(220),
D_NACIMIENTO DATE,
PRIMARY KEY (C_JUGADOR));

CREATE TABLE JUG_EQ_CAMP(
C_JUGADOR CHAR(3) NOT NULL,
C_CAMPEONATO CHAR(3) NOT NULL,
C_EQUIPO CHAR(2),
PRIMARY KEY (C_JUGADOR, C_CAMPEONATO),
FOREIGN KEY (C_JUGADOR) REFERENCES JUGADOR(C_JUGADOR)
);

— ADICIONA CLAVES FORANEAS
ALTER TABLE JUG_EQ_CAMP
ADD FOREIGN KEY (C_CAMPEONATO)
REFERENCES CAMPEONATO(C_CAMPEONATO);

ALTER TABLE JUG_EQ_CAMP
ADD FOREIGN KEY (C_EQUIPO)
REFERENCES EQUIPO(C_EQUIPO);

— BORRA UNA CLAVE FORANEA
— ALTER TABLE JUG_EQ_CAMP
— DROP FK__JUG_EQ_CA__C_CAM__1A14E395

CREATE TABLE ESTADIO(
C_ESTADIO CHAR(3) NOT NULL,
N_ESTADIO VARCHAR(220),
PRIMARY KEY (C_ESTADIO));

CREATE TABLE PARTIDO(
NRO_PARTIDO INT NOT NULL,
C_ESTADIO_PART CHAR(3),
C_EQUIPO_1 CHAR(2),
C_EQUIPO_2 CHAR(2),
C_CAMPEONATO CHAR(3),
D_PARTIDO DATE,
N_JUEZ_LINEA1 VARCHAR(250),
N_JUEZ_LINEA2 VARCHAR(250),
Q_GOLES_E1 INT,
Q_GOLES_E2 INT,
N_ARBITRO VARCHAR(220),
C_RESULTADO VARCHAR(20),
PRIMARY KEY (NRO_PARTIDO),
FOREIGN KEY (C_ESTADIO_PART)
              REFERENCES ESTADIO(C_ESTADIO),
FOREIGN KEY (C_EQUIPO_1)
              REFERENCES EQUIPO(C_EQUIPO),
FOREIGN KEY (C_EQUIPO_2)
              REFERENCES EQUIPO(C_EQUIPO),
FOREIGN KEY (C_CAMPEONATO)
              REFERENCES CAMPEONATO(C_CAMPEONATO)
);

CREATE TABLE JUG_PARTIDO(
C_JUGADOR CHAR(3) NOT NULL,
NRO_PARTIDO INT NOT NULL,
N_POSICION VARCHAR(220),
NRO_CAMISETA INT NOT NULL,
F_EXPULSADO CHAR(1),
F_AMONESTADO CHAR(1),
F_GOLEADOR CHAR(1),
PRIMARY KEY (C_JUGADOR, NRO_PARTIDO),
FOREIGN KEY (C_JUGADOR)
REFERENCES JUGADOR(C_JUGADOR),
FOREIGN KEY (NRO_PARTIDO)
REFERENCES PARTIDO(NRO_PARTIDO)

);

Publicado en Prácticas

Deja un comentario

Ajedrez (ejercicio)

Jun 3

Publicado por cynversb

ERWIN

SQL SERVER

Modelo Fisico de la base de datos

Creación de Tablas

Query:

CREATE TABLE PAIS(
C_PAIS VARCHAR(2) NOT NULL,
N_PAIS VARCHAR(20),
N_FEDERACION VARCHAR(120),
Q_CLUBS_AJEDREZ INT,
PRIMARY KEY (C_PAIS)
)

CREATE TABLE PARTICIPANTE(
C_ASOCIADO VARCHAR(3) NOT NULL,
C_PAIS VARCHAR(2) NOT NULL,
N_NOMBRE VARCHAR(120),
T_DIRECCION VARCHAR(250),
D_FECHA_NAC DATE,
PRIMARY KEY (C_ASOCIADO),
FOREIGN KEY (C_PAIS) REFERENCES PAIS(C_PAIS)
)

CREATE TABLE JUGADOR(
C_JUGADOR VARCHAR(3) NOT NULL,
Q_NIVEL INT,
PRIMARY KEY (C_JUGADOR),
FOREIGN KEY (C_JUGADOR)REFERENCES PARTICIPANTE(C_ASOCIADO)
)

CREATE TABLE ARBITRO(
C_ARBITRO VARCHAR(3) NOT NULL,
Q_AÑOS INT,
PRIMARY KEY (C_ARBITRO),
FOREIGN KEY (C_ARBITRO)REFERENCES PARTICIPANTE(C_ASOCIADO)
)

CREATE TABLE HOTEL(
C_HOTEL VARCHAR(3) NOT NULL,
N_HOTEL VARCHAR(250),
T_DIRECCION VARCHAR(250),
NRO_TELEFONO NUMERIC(12),
PRIMARY KEY (C_HOTEL)
)
CREATE TABLE PARTICIPANTE_HOTEL(
C_ASOCIADO VARCHAR(3) NOT NULL,
C_HOTEL VARCHAR(3) NOT NULL,
D_INICIO DATE,
D_FIN DATE,
PRIMARY KEY (C_ASOCIADO,C_HOTEL,D_INICIO),
FOREIGN KEY (C_ASOCIADO)REFERENCES PARTICIPANTE(C_ASOCIADO),
FOREIGN KEY (C_HOTEL)REFERENCES HOTEL(C_HOTEL)
)

CREATE TABLE SALA(
C_HOTEL VARCHAR(3) NOT NULL,
N_SALA VARCHAR(120) NOT NULL,
Q_CAPACIDAD INT ,
PRIMARY KEY (C_HOTEL,N_SALA),
FOREIGN KEY (C_HOTEL)REFERENCES HOTEL(C_HOTEL)
)

CREATE TABLE MEDIO_COMUNICACION(
C_MEDIO VARCHAR(3) NOT NULL,
N_MEDIO VARCHAR(120),
PRIMARY KEY (C_MEDIO)
)
CREATE TABLE SALA_MEDIO_COMUNICACION(
C_HOTEL VARCHAR(3) NOT NULL,
N_SALA VARCHAR(120) NOT NULL,
C_MEDIO VARCHAR(3) NOT NULL,
PRIMARY KEY (C_HOTEL,N_SALA,C_MEDIO),
FOREIGN KEY (C_HOTEL,N_SALA)REFERENCES SALA(C_HOTEL,N_SALA),
FOREIGN KEY (C_MEDIO)REFERENCES MEDIO_COMUNICACION(C_MEDIO)
)

CREATE TABLE JORNADA(
C_JORNADA VARCHAR(3) NOT NULL,
D_FECHA_JORNADA DATE,
PRIMARY KEY (C_JORNADA)
)

CREATE TABLE PARTIDA(
NRO_PARTIDA VARCHAR(3) NOT NULL,
C_HOTEL VARCHAR(3) NOT NULL,
N_SALA VARCHAR(120) NOT NULL,
C_ARBITRO VARCHAR(3) NOT NULL,
C_JUGADOR2 VARCHAR(3) NOT NULL,
C_JORNADA VARCHAR(3) NOT NULL,
C_JUGADOR1 VARCHAR(3) NOT NULL,
N_COLOR_J1 VARCHAR(50),
N_COLOR_J2 VARCHAR(50),
Q_ENTRADAS INT,
PRIMARY KEY (NRO_PARTIDA),
FOREIGN KEY (C_HOTEL,N_SALA)REFERENCES SALA(C_HOTEL,N_SALA),
FOREIGN KEY (C_ARBITRO)REFERENCES ARBITRO(C_ARBITRO),
FOREIGN KEY (C_JUGADOR1)REFERENCES JUGADOR(C_JUGADOR),
FOREIGN KEY (C_JUGADOR2)REFERENCES JUGADOR(C_JUGADOR),
FOREIGN KEY (C_JORNADA)REFERENCES JORNADA(C_JORNADA)
)

Inserción de Datos

Query:

–LLENADO DE DATOS
USE AJEDREZ
go
INSERT INTO PAIS VALUES
(‘P1′,’ALEMANIA’,’FEDERACION NACIONAL DE AJEDREZ DE ALEMANIA’,4),
(‘P2′,’PERU’,’FEDERACION NACIONAL DE AJEDREZ DEL PERU’,4),
(‘P3′,’MEXICO’,’FEDERACION NACIONAL DE AJEDREZ DE MEXICO’,5),
(‘P4′,’ARGENTINA’,’FEDERACION NACIONAL DE AJEDREZ DE ARGENTINA’,3)

INSERT INTO PARTICIPANTE VALUES
(‘A01′,’P1′,’JULIO CETELLO’,’AV.PERSHING 2834′,’19840415′),
(‘A02′,’P1′,’MARIA SILVA’,’AV.ANYESTER 2214′,’19880512′),
(‘A03′,’P2′,’LUISA ALCANTARA’,’AV.LA MOLINA 2040′,’19860712′),
(‘A04′,’P2′,’DONATELO PEDELO’,’AV.BEGONIAS 4114′,’19870425′),
(‘A05′,’P3′,’FRANCIS DOMINGUEZ’,’AV.COLONIAL 3514′,’19841015′),
(‘A06′,’P3’,’JULIAN LEGASPY ‘,’AV.CRISANTEMO 2334′,’19901225’),
(‘A07′,’P4′,’SUSAN BACA’,’AV.CHEGUEVARA 2144′,’19850510′),
(‘A08′,’P4′,’LULA TEVEZ’,’AV.JAZMIN 1540′,’19870427′),
(‘A09′,’P1′,’TOROMEO VALDEZ’,’AV.RUISEÑORES 1340′,’19800427′),
(‘A10′,’P2′,’GILBERT MENDEZ’,’AV.JAZMIN 2440′,’19790427′)

INSERT INTO JUGADOR VALUES
(‘A01’,3),
(‘A02’,5),
(‘A03’,6),
(‘A04’,8),
(‘A05’,5),
(‘A06’,3),
(‘A07’,8),
(‘A08’,6)

INSERT INTO ARBITRO VALUES
(‘A09’,10),
(‘A10’,8)

INSERT INTO HOTEL VALUES
(‘H01′,’SHARATON’,’AV.LARCOMAR 1530′,4256786),
(‘H02′,’LOS DELFINES’,’AV.LARCOMAR 1530′,4554846),
(‘H03′,’MOVENPICK’,’AV.ALEMANIA 3450′,4694856),
(‘H04′,’SHARATON’,’AV.LAS AMERICAS 4346′,4335766)

INSERT INTO PARTICIPANTE_HOTEL VALUES
(‘A01′,’H03′,’20110305′,’20110309’),
(‘A02′,’H03′,’20110305′,’20110309’),
(‘A05′,’H03′,’20110305′,’20110309’),
(‘A06′,’H03′,’20110305′,’20110309’),
(‘A03′,’H01′,’20110415′,’20110423’),
(‘A04′,’H01′,’20110415′,’20110423’),
(‘A07′,’H01′,’20110415′,’20110423’),
(‘A08′,’H01′,’20110415′,’20110423’),
(‘A09′,’H04′,’20110415′,’20110423’),
(‘A10′,’H02′,’20110315′,’20110309’)

INSERT INTO SALA VALUES
(‘H04′,’SALA1’,40),
(‘H04′,’SALA2’,30),
(‘H04′,’SALA3’,20),
(‘H02′,’SALA1’,40),
(‘H02′,’SALA2’,30),
(‘H02′,’SALA3’,25),
(‘H02′,’SALA4’,15)

INSERT INTO MEDIO_COMUNICACION VALUES
(‘M01′,’RADIO’),
(‘M02′,’TELEVISION’),
(‘M03′,’INTERNET’)

INSERT INTO SALA_MEDIO_COMUNICACION VALUES
(‘H04′,’SALA1′,’M01’),
(‘H04′,’SALA1′,’M02’),
(‘H04′,’SALA2′,’M01’),
(‘H04′,’SALA2′,’M02’),
(‘H04′,’SALA2′,’M03’),
(‘H04′,’SALA3′,’M01’),
(‘H04′,’SALA3′,’M03’),
(‘H02′,’SALA1′,’M01’),
(‘H02′,’SALA1′,’M02’),
(‘H02′,’SALA1′,’M03’),
(‘H02′,’SALA2′,’M02’),
(‘H02′,’SALA3′,’M01’),
(‘H02′,’SALA3′,’M03’),
(‘H02′,’SALA4′,’M03’),
(‘H02′,’SALA4′,’M02’)

INSERT INTO JORNADA VALUES
(‘J01′,’20110306’),
(‘J02′,’20110307’),
(‘J03′,’20110308’),
(‘J04′,’20110309’),
(‘J05′,’20110417’),
(‘J06′,’20110418’),
(‘J07′,’20110420’),
(‘J08′,’20110421’),
(‘J09′,’20110423’)

INSERT INTO PARTIDA VALUES
(‘001′,’H04′,’SALA3′,’A09′,’A01′,’J05′,’A06′,’NEGRO’,’ROJO’,2),
(‘002′,’H04′,’SALA2′,’A09′,’A02′,’J06′,’A05′,’NEGRO’,’ROJO’,2),
(‘003′,’H02′,’SALA3′,’A10′,’A03′,’J02′,’A07′,’BLANCO’,’AZUL’,2),
(‘004′,’H02′,’SALA3′,’A10′,’A04′,’J04′,’A08′,’BLANCO’,’AZUL’,2)

MySQL

Modelo Fisico de la Base de Datos MySQL

Creación de Tablas en MySQL

Query:

CREATE TABLE PAIS(
C_PAIS VARCHAR(2) NOT NULL,
N_PAIS VARCHAR(20),
N_FEDERACION VARCHAR(120),
Q_CLUBS_AJEDREZ INT,
PRIMARY KEY (C_PAIS)
);

CREATE TABLE JUGADOR(
C_JUGADOR VARCHAR(3) NOT NULL,
Q_NIVEL INT,
PRIMARY KEY (C_JUGADOR),
FOREIGN KEY (C_JUGADOR)REFERENCES PARTICIPANTE(C_ASOCIADO)
);

CREATE TABLE ARBITRO(
C_ARBITRO VARCHAR(3) NOT NULL,
Q_AÑOS INT,
PRIMARY KEY (C_ARBITRO),
FOREIGN KEY (C_ARBITRO)REFERENCES PARTICIPANTE(C_ASOCIADO)
);

CREATE TABLE HOTEL(
C_HOTEL VARCHAR(3) NOT NULL,
N_HOTEL VARCHAR(250),
T_DIRECCION VARCHAR(250),
NRO_TELEFONO NUMERIC(12),
PRIMARY KEY (C_HOTEL)
);
CREATE TABLE PARTICIPANTE_HOTEL(
C_ASOCIADO VARCHAR(3) NOT NULL,
C_HOTEL VARCHAR(3) NOT NULL,
D_INICIO DATE,
D_FIN DATE,
PRIMARY KEY (C_ASOCIADO,C_HOTEL,D_INICIO),
FOREIGN KEY (C_ASOCIADO)REFERENCES PARTICIPANTE(C_ASOCIADO),
FOREIGN KEY (C_HOTEL)REFERENCES HOTEL(C_HOTEL)
);

CREATE TABLE SALA(
C_HOTEL VARCHAR(3) NOT NULL,
N_SALA VARCHAR(120) NOT NULL,
Q_CAPACIDAD INT ,
PRIMARY KEY (C_HOTEL,N_SALA),
FOREIGN KEY (C_HOTEL)REFERENCES HOTEL(C_HOTEL)
);

CREATE TABLE MEDIO_COMUNICACION(
C_MEDIO VARCHAR(3) NOT NULL,
N_MEDIO VARCHAR(120),
PRIMARY KEY (C_MEDIO)
);
CREATE TABLE SALA_MEDIO_COMUNICACION(
C_HOTEL VARCHAR(3) NOT NULL,
N_SALA VARCHAR(120) NOT NULL,
C_MEDIO VARCHAR(3) NOT NULL,
PRIMARY KEY (C_HOTEL,N_SALA,C_MEDIO),
FOREIGN KEY (C_HOTEL,N_SALA)REFERENCES SALA(C_HOTEL,N_SALA),
FOREIGN KEY (C_MEDIO)REFERENCES MEDIO_COMUNICACION(C_MEDIO)
);

CREATE TABLE JORNADA(
C_JORNADA VARCHAR(3) NOT NULL,
D_FECHA_JORNADA DATE,
PRIMARY KEY (C_JORNADA)
);

Publicado en Prácticas

1 comentario

PRACTICA CALIFICADA 2 – PREGUNTA 1

May 26

Publicado por cynversb

MODELO LOGICO

MODELO FISICO

Publicado en Prácticas

Deja un comentario

Buscar por:
Autor
- cynversb
Categorías
Categorías
Entradas recientes
Estadísticas de visita
- 28.934 visitas
- Entradas Populares
- Archivos
- octubre 2020 (1)
- junio 2011 (8)
- May 2011 (13)
Calendario 2011
May 2024

L M X J V S D

1 2 3 4 5

6 7 8 9 10 11 12

13 14 15 16 17 18 19

20 21 22 23 24 25 26

27 28 29 30 31

« Oct
Archivos
Meta
Suscríbete vía Hotmail !

Escribe tu dirección de correo electrónico para suscribirte a este blog, y recibir notificaciones de nuevos mensajes por correo.

Dirección de correo electrónico:

Únete a otros 2 suscriptores

Cynver

Este Blog esta hecho con la finalidad de aportar temas sobre el curso Base de Datos

Archivo del Autor: cynversb

Consultas Básicas SQL

PC3 2010-2 B

VENTAS

AHORROS

NORTHWIND

PC3 2010-2 A

VENTAS

AHORROS

NORTHWIND

Triggers

Triggers

Stored Procedures

Stored Procedures

Inserción de funciones

Funciones

Tarea N° 05 – Querys en SQL Server (Ajedrez)

JOIN

ORDER BY

HAVING

JOIN, WHERE, GROUP BY, HAVING

OUTER JOIN

Campeonato (ejercicio)

Ajedrez (ejercicio)

PRACTICA CALIFICADA 2 – PREGUNTA 1

Autor

Categorías

Entradas recientes

Estadísticas de visita

Calendario 2011

Archivos

Meta

Suscríbete vía Hotmail !