ejercicio de mysql

[Copia]

hola, estoy practicando con el siguiente ejercicio:

Mostrar un resumen de los programas instalados indicando cuantos de ellos tienen licencia, considerar solo aquellos equipos adquiridos durante los años 2000 hasta el 2002, y cuyo estado actual todavia este vigente, ademas se debe de excluir a aquellos equipos que durante el año 2001 hayan tenido mas de 2 revisiones tecnicas en su plan de mantenimiento.

Para esta consulta tengo la siguientes relaciones:
http : // img34.imageshack.us/i/telmex.png/

la consulta que tengo es:

Código sql:

SELECT P.CodigoPrograma,P.Descripcion AS Programa,COUNT(EC.CodigoEquipoComputo) AS 'PCS',
       SUM(PI.FlagTieneLicencia) AS 'Pcs con licencia'
FROM EquipoComputo AS EC, ProgramasInstalados AS PI, Programas AS p
WHERE (EC.CodigoEquipoComputo=PI.CodigoEquipoComputo) AND (PI.CodigoPrograma=P.CodigoPrograma)
      AND (YEAR(EC.FechaAdquisicion) BETWEEN 2000 AND 2002) AND
       (EC.CodigoEquipoComputo IN (SELECT SUM(PM.CodigoEquipoComputo)
                                      FROM EquipoComputo AS EC,PlanMantenimiento AS PM
                                      WHERE(EC.CodigoEquipoComputo=PM.CodigoEquipoComputo) AND
                                           (YEAR(EC.FechaAdquisicion)=2001)
                                      GROUP BY (PM.CodigoEquipoComputo)
                                      HAVING SUM(PM.CodigoEquipoComputo)<2))
 
GROUP BY P.Descripcion;
 

he creado tambien la base de datos pero al ejecutar no me muestra resultado alguno, me puese a verificar los datos y si existe por lo menos 1 que se debe mostrar.

PD: no trato de hacer spam, pero la imagen lo pueden ver uniendo toda la direccion sin espacios.

[Copia]

creo que he avanzado pero aun no me termina de correr la condicion que dice:

"ademas se debe de excluir a aquellos equipos que durante el año 2001 hayan tenido mas de 2 revisiones tecnicas en su plan de mantenimiento."

Tengo como dato a una pc con codigoprograma = 13 que esta 5 veces usado, uno se descarta porque es del 2005 no cumple condicion, tres pcs lo tienen instalado dos con licencia y una no, pero una de las tres codigoequipocomputo = 13 es del 2001 y tiene como 6 revisiones tecnica entonces se debe de borrar.

EL codigo es este:

Código Sql:

SELECT P.CodigoPrograma,P.Descripcion AS Programa,COUNT(EC.CodigoEquipoComputo) AS 'PCS',
       SUM(PI.FlagTieneLicencia) AS 'Pcs con licencia'
FROM EquipoComputo AS EC, ProgramasInstalados AS PI, Programas AS p
WHERE (EC.CodigoEquipoComputo=PI.CodigoEquipoComputo) AND (PI.CodigoPrograma=P.CodigoPrograma)
      AND (YEAR(EC.FechaAdquisicion) BETWEEN 2000 AND 2002) AND NOT
       (EC.CodigoEquipoComputo IN (SELECT SUM(PM.CodigoEquipoComputo)
                                      FROM EquipoComputo AS EC,PlanMantenimiento AS PM
                                      WHERE(EC.CodigoEquipoComputo=PM.CodigoEquipoComputo) AND
                                           (YEAR(EC.FechaAdquisicion)=2001)
                                      GROUP BY (PM.CodigoEquipoComputo)
                                      HAVING (SUM(PM.CodigoEquipoComputo)>2)))
 
GROUP BY P.Descripcion;
 

[serser]

Creo que aquí:

(year(EC.FechaAdquisicion)=2001)

dberías utilizar PM.FechaRevision en vez de EC.FechaAdquisicion, y negar la condición:

(year(PM.FechaRevision)!=2001)

[gnzsoloyo]

En tu ejemplo estás comparando el valor de EC.CodigoEquipoComputo, no con los códigos, sino con la suma del valor de los códigos que cumplen con la condición indicada.
Eso no tiene sentido.
Prueba:

Código sql:

SELECT
  P.CodigoPrograma,P.Descripcion `Programa`,
  COUNT(EC.CodigoEquipoComputo) `PCS`,
  SUM(PI.FlagTieneLicencia) AS `Pcs con licencia`
FROM
  EquipoComputo EC
  INNER JOIN ProgramasInstalados PI USING(CodigoEquipoComputo)
  INNER JOIN Programas p USING(CodigoPrograma)
WHERE
      (YEAR(EC.FechaAdquisicion) BETWEEN 2000 AND 2002) AND
      NOT (EC.CodigoEquipoComputo IN (SELECT
                                         PM.CodigoEquipoComputo
                                      FROM
                                         EquipoComputo EC
                                         INNER JOIN PlanMantenimiento PM USING(CodigoEquipoComputo)
                                      WHERE
                                         (YEAR(EC.FechaRevision)=2001)
                                      GROUP BY (PM.CodigoEquipoComputo)
                                      HAVING SUM(PM.CodigoEquipoComputo)>2
                                     )
GROUP BY P.Descripcion;
 

Simplifiqué un poco el código y le puse la relación donde tiene que ir (en el INNER JOIN y no en el WHERE).
Por lo demás, estoy de acuerdo con Serser.

[Copia]

gracias por las respuestas, han tenido toda la razon el prome era la forma como comparaba en la subseleccion.

el codigo final me quedo así:

Código sql:

SELECT
  P.CodigoPrograma,P.Descripcion `Programa`,
  COUNT(EC.CodigoEquipoComputo) `PCS`,
  SUM(PI.FlagTieneLicencia) AS `Pcs con licencia`
FROM
  EquipoComputo EC
  INNER JOIN ProgramasInstalados PI USING(CodigoEquipoComputo)
  INNER JOIN Programas p USING(CodigoPrograma)
WHERE
      (YEAR(EC.FechaAdquisicion) BETWEEN 2000 AND 2002) AND
      NOT (EC.CodigoEquipoComputo IN (SELECT
                                         PM.CodigoEquipoComputo
                                      FROM
                                         PlanMantenimiento PM
                                      WHERE
                                         (YEAR(PM.FechaRevision)=2001)
                                      GROUP BY (PM.CodigoEquipoComputo)
                                      HAVING SUM(PM.CodigoEquipoComputo)>2
                                     ))
GROUP BY P .Descripcion
ORDER BY P.CodigoPrograma;
 

No se si pueden explicarme un poco la relacion que hacen con inner join, los entiendo en parte pero ese using que ponen usa exclusivamente un campo de la tabla?. Tambien vi que para renombrar no uso as.. como en esto: ProgramasInstalados PI

SI tienen ahi algo que me ayude a comprender los inner Join :D

saludos y gracias

[gnzsoloyo]

1. Los INNER JOIN realizan una junta natural entre dos tablas, pero requieren especificar cuáles campos se van a relacionar, y sólo devuelve aquellos registros donde se cumple la relación. Los campos se deben especificar con la cláusula ON, si los campos tienen nombre diferente en cada tabla, o se pueden especificar con USING(campos), si en ambas tablas tiene el mismo nombre.
El USING() es más rápido que el ON, por lo que yo lo suelo preferir; además no requiere que sea un sólo campos. En realidad dentro de los paréntesis pueden ir todos los campos que se desea que se consideren en el JOIN al mismo tiempo. Eso si: todos los campos listados deben existir con esos nombres en ambas tablas.

2. La cláusula AS dejó de ser obligatoria hace años. Los DBMS la aceptan por cuestiones de compatibilidad con códigos viejos, pero ya no es necesario ponerla. El MySQL reconoce como alias cualquier palabra que siga a continuación del nombre de una base, una tabla o un campo.

Lo mejor que puedes hacer para comprender los INNER JOIN es leer el manual y ver algo más de SQL:
13.2.7.1. Sintaxis de JOIN

JOIN en SQL

[Copia]

me pongo a leer lo que me dejaste.

Con la misma relacion me piden que haga lo siguiente:

Implementar un procedimiento almacenado para reubicar una pc donde se ingrese el CodigoEquipoComputo y la nueva ubucacion como condicion se tiene que:

1.- se debe almacenar en el campo observaciones de la pc, la antigua ubicacion.
2.- no se puede cambiar la ubicacion si el equipo esta de baja, el codigo del estado que da de baja a una pc es 2.

Entonces arme lo siguiente:

Código sql:

CREATE PROCEDURE Sp_ReUbicar(xCodigoEquipoComputo VARCHAR(10),
                                       xCodigoUbicacion VARCHAR(8))
BEGIN
    DECLARE ubicacionAnterior VARCHAR(8);
    SET ubicacionAnterior = (SELECT EC.CodigoUbiacion
                             FROM EquipoComputo EC
                             WHERE(EC.CodigoEquipoComputo=xCodigoEquipoComputo));
 
        IF ((SELECT estado FROM EquipoComputo
             WHERE (CodigoEquipoComputo=xCodigoEquipoComputo) <> 2) THEN
 
             UPDATE EquipoComputo SET CodigoUbicacion=xCodigoUbicacion,
                                      Observacion=ubicacionAnterior
             WHERE  CodigoEquipoComputo=xCodigoEquipoComputo;
    END IF
END
 

pero me sale error al ejecutar el sql con el administrator

[serser]

Creo que te sobra un paréntesis de apertura en el IF y te falta cerrar el END IF con ;

[Copia]

tiene usted razon, faltaba solo eso para que se compile el procedimiento en resumen me quedo este codigo:

Código mysql:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Sp_ReUbicar`(xCodigoEquipoComputo varchar(10),
                                       xCodigoUbicacion varchar(8))
BEGIN
    declare ubicacionAnterior varchar(8);
    set ubicacionAnterior = (select EC.CodigoUbiacion
                             FROM EquipoComputo EC
                             where(EC.CodigoEquipoComputo=xCodigoEquipoComputo));
 
        if ((select estado FROM EquipoComputo
             where (CodigoEquipoComputo=xCodigoEquipoComputo)) <> 2) then
 
             update EquipoComputo set CodigoUbicacion=xCodigoUbicacion,
                                      Observacion=ubicacionAnterior
             where  CodigoEquipoComputo=xCodigoEquipoComputo;
    end if;
END
 

Pero lo e ejecutado asi: CALL Sp_ReUbicar(4,7);

y me da este error: Unknown column 'EC.CodigoUbiacion' in 'field list'

e visto los campos y estan todos bien?? cual podria ser esta vez el error?

[gnzsoloyo]

- Verifica que la tabla EQUIPOCOMPUTO tenga un campo denominado `CodigoUbicacion`.
- Si lo tiene, probemos suprimir los alias que no se necesitan:

Código sql:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Sp_ReUbicar`
  (IN xCodigoEquipoComputo VARCHAR(10),
  IN xCodigoUbicacion VARCHAR(8))
BEGIN
  DECLARE ubicacionAnterior VARCHAR(8);
  SET ubicacionAnterior = (SELECT CodigoUbiacion
                           FROM EquipoComputo
                           WHERE CodigoEquipoComputo=xCodigoEquipoComputo);
  IF(SELECT estado FROM EquipoComputo WHERE CodigoEquipoComputo = xCodigoEquipoComputo) <> 2) THEN
    UPDATE EquipoComputo
      SET
          CodigoUbicacion = xCodigoUbicacion,
          Observacion = ubicacionAnterior
    WHERE CodigoEquipoComputo = xCodigoEquipoComputo;
  END IF;
END
 

Además, ten en cuenta que en MySQL se debe indicar si los parámetros de un SP son de entrada (IN), salida (OUT) o entrada-salida (INOUT). Si no los pones, solamente serán IN.
Yo sugiero ponerlo aunque sea redundante para evitar confusiones y ceñirse mejor al estándar de sintaxis.

[serser]

Creo que me ha pasado lo mismo que te está pasando a tí, Copia.

He revisado el código varias veces sin encontrar ningún error hasta que me he parado detenidamente y me he dado cuenta de esto:

(SELECT CodigoUbiacion...

¡Te falta una c entre la 'i' y la 'a'!

[gnzsoloyo]

Eso se me pasó....

[Copia]

o no... no puede ser, esa clase de errores dan que ahcer.

Gracias por la ayuda pero aun no me corre como debe, este es el resultado tras:
call(4,7)

codigoEquipoComputo
4
Codigoubicacion
4 //no modifico
observacion
servt // no puso lo que debia

no se ha modificado nada, mira por curiosidad el equipo 7 pero esta igual.

[serser]

Copia, te puedo decir que el procedimiento funciona porque ayer me puse a probarlo por curiosidad. Fíjate bien a ver si el codigoEquipoComputo 4 tiene un estado = 2 ???.

De todas formas de dejo el código que me funciona:

Código:

CREATE PROCEDURE Sp_ReUbicar(IN xCodigoEquipoComputo VARCHAR(10), IN xCodigoUbicacion VARCHAR(8))
BEGIN
   DECLARE ubicacionAnterior VARCHAR(8);
   SET ubicacionAnterior = (SELECT CodigoUbicacion
                            FROM EquipoComputo
                            WHERE(CodigoEquipoComputo=xCodigoEquipoComputo));

       IF ((SELECT estado FROM EquipoComputo
            WHERE (CodigoEquipoComputo=xCodigoEquipoComputo)) <> 2) THEN

            UPDATE EquipoComputo SET CodigoUbicacion=xCodigoUbicacion,
                                     Observacion=ubicacionAnterior
            WHERE  CodigoEquipoComputo=xCodigoEquipoComputo;
   END IF;
END

[Copia]

hola.. pues te dejo la base de datos con la que estoy trabajando.. para que vea, cuando lei su comentario queria que me coma la tierra porque no me habia fijado si el codigo del estado era 2, por suerte no fue asi, y probe con otros.

aca esta: http : // w w w .megaupload.com/ ?d=50X80849

[serser]

El fallo está en que llamas al procedimiento con parámetros numéricos y los tipos a los que hacen referencia son VARCHAR. Para que funcione tienes que porner:

CALL Sp_ReUbicar('04',7);

El 7 lo acepta tal cual porque en codigoubicacion no tienes ceros delante. Por eso es mejor llamarlo así siempre:

CALL Sp_ReUbicar('04','7');

[Copia]

sierto tienes razon, gracias amigo