none
Consultas en SQL Server 2008

    Question

  • Buenas, tengo que realizar unas consultas en SQL Server 2008 me han faltado de realizar 2 de ellas que aún no he logrado solucionar, lo primero que hago es utilizar el Álgebra Relacional para Optimizar la Consulta y luego traducirlo a lenguaje SQL. Les detallo las tablas y mis sentencias en Álgebra Relacional para que me puedan ayudar si es posible:

    DETALLE DE TABLAS

    Profesor

    P#

    Nomp

    Desp

    P1

    RAUL

    105

    P2

    SIMON

    103

    P3

    ROSA

    107

    P4

    ADRIAN

    107

     

    Asignatura

    A#

    Noma

    A1

    FISICA

    A2

    QUIMICA

    A3

    DIBUJO

    A4

    MATEMATICA

     

    Clases

    C#

    Piso

    Bloque

    C1

    1

    1

    C2

    1

    2

    C3

    2

    1

     

    Asistencia

    P#

    A#

    C#

    P1

    A1

    C1

    P1

    A2

    C3

    P2

    A4

    C1

    P3

    A3

    C3

    P3

    A3

    C2

    P4

    A2

    C1

    P4

    A2

    C2

    P3

    A3

    C1

     

    Consultas que debo resolver:

    1. Obtener los nombres de los profesores que asisten soloa clases del bloque 1
    2. Obtener las clases en las que se imparten todas las asignaturas.

    Para la primera consulta tengo lo siguiente:

    Álgebra Relacional:

    Π{Nomp}(σ{Profesor.P# = P#}(Profesor × [Π{P#}(σ{Asistencia.C# = C#}(Asistencia  × Π{C#}(σ{Bloque = '1'}(Clases))))]―[Π{P#}(σ{Asistencia.C# = C#}(Asistencia  × Π{C#}(σ{Bloque ≠ '1'}(Clases))))]))

    SQL:

    SELECT Nomp FROM Profesor, (SELECT P FROM Asistencia, (SELECT C FROM Clases WHERE Bloque = '1')alias1  WHERE Asistencia.C = alias1.C MINUS SELECT P FROM Asistencia, (SELECT C FROM Clases WHERE Bloque <> '1')alias2  WHERE Asistencia.C = alias2.C)alias3 WHERE Profesor.P = alias3.P

     

     Esta consulta debería dar como resultado P1(RAUL) y P2(SIMON).

    Como se puede observar en la sentencia de Álgebra utilizo la Diferencia de Conjuntos'-'en el que selecciono las clases del bloque 1 MINUS la selección de las clases del que no son del bloque 1, si no me equivoco con esto obtendría solo las clases que se dan únicamente en el bloque 1, luego lo anterior lo uno con la tabla profesor mediante un producto cartesianoy realizó la selección del nombre del profesor o profesores.

    Sin embargo me he dado cuenta que SQL Server no incluye el MINUSy para ello se debería usar el LEFT JOIN. Esto no lo he podido implementar.

     

    Y para la segunda consulta implemente lo siguiente:

    SELECT Clases.C# FROM Asignatura JOIN Clases ON (Clases.C# = Asignatura.C#) WHERE Asignatura IN(SELECT A# FROM Asignatura)

    Lo anterior debe dar como resultado C1. Pero la consulta me muestra todas las clases.

    Saturday, March 17, 2012 6:09 AM

Answers

  • Hola.

    Para la cuestión 1, lo que llamas "MINUS" se podría implementar con EXCEPT, pero también, como apuntas, con LEFT JOIN o con "not exists":

    select P#
    from Asistencia
    where 
      Bloque = 1 and --Dan clase en el bloque uno
      P# not in (select #P from Asistencia where Bloque <> 1)-- y en ningún otro

    Para la segunda cuestión, es un poco más compleja. Lo puedes obtener mediante recuentos. Lo haré primero por pasos y luego lo juntamos todo en la misma consulta.

    -Determino cuántas asignaturas hay
    select count(distinct A#) from Asignaturas
    
    -- veo qué número de asignaturas se dan en cada aula
    select C#, N_Asig = count(distinct A#)
    from Asistencia
    group by C#
    
    --Y de esas, tengo que filtrar por el resultado de la primera consulta 
    
    
    select C#--, N_Asig = count(distinct A#)
    from Asistencia
    group by C#
    having count(distinct A#) = (select count(distinct A# from Asignaturas)
    

    El álgebra ya te la dejo para ti.


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.blogspot.es/ Sígueme en twitter en http://twitter.com/qwalgrande

    • Marked as answer by Pachini Saturday, March 17, 2012 2:51 PM
    Saturday, March 17, 2012 8:15 AM
    Moderator
  • -- R1

    select P.*
    from
        profesor as P
        inner join
        (
        select
            A.P#
        from
            asistencia as A
            inner join
            clases as C
            on A.C# = C.C#
        group by
            A.P#
        having
            min(C.Bloque) = 1
            and max(C.Bloque) = 1
        ) as R
        on P.P# = R.P#;

    Tambien vale usar la negación, como sugirio Alberto. Encontrar profesores que asisten a clases en el bloque 1, y que no existe una tupla donde el profesor asiste una clase en otro bloque.

    -- R2

    Este problema se conoce como Division Relacional (Relational Division). En este caso es una división exacta, sin resto.

    Encontrar las clases, en la tabla asistencia, agrupando por clase y donde la cantidad de las distintas asignaturas que se imparten en esta clase, es igual al número de asignaturas en la tabla asignatura. En este caso la tabla [asistencia] es el numerador y la tabla [asignatura] es el divisor.

    select
        C.*
    from
        clases as C
        inner join
        (
        select
            A.C#
        from
            asistencia as A
        group by
            A.C#
        having
            count(distinct A.A#) = (select count(*) from asignatura)
        ) as R
        on C.C# = R.C#;


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Pachini Saturday, March 17, 2012 2:51 PM
    Saturday, March 17, 2012 2:29 PM

All replies

  • Hola.

    Para la cuestión 1, lo que llamas "MINUS" se podría implementar con EXCEPT, pero también, como apuntas, con LEFT JOIN o con "not exists":

    select P#
    from Asistencia
    where 
      Bloque = 1 and --Dan clase en el bloque uno
      P# not in (select #P from Asistencia where Bloque <> 1)-- y en ningún otro

    Para la segunda cuestión, es un poco más compleja. Lo puedes obtener mediante recuentos. Lo haré primero por pasos y luego lo juntamos todo en la misma consulta.

    -Determino cuántas asignaturas hay
    select count(distinct A#) from Asignaturas
    
    -- veo qué número de asignaturas se dan en cada aula
    select C#, N_Asig = count(distinct A#)
    from Asistencia
    group by C#
    
    --Y de esas, tengo que filtrar por el resultado de la primera consulta 
    
    
    select C#--, N_Asig = count(distinct A#)
    from Asistencia
    group by C#
    having count(distinct A#) = (select count(distinct A# from Asignaturas)
    

    El álgebra ya te la dejo para ti.


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.blogspot.es/ Sígueme en twitter en http://twitter.com/qwalgrande

    • Marked as answer by Pachini Saturday, March 17, 2012 2:51 PM
    Saturday, March 17, 2012 8:15 AM
    Moderator
  • -- R1

    select P.*
    from
        profesor as P
        inner join
        (
        select
            A.P#
        from
            asistencia as A
            inner join
            clases as C
            on A.C# = C.C#
        group by
            A.P#
        having
            min(C.Bloque) = 1
            and max(C.Bloque) = 1
        ) as R
        on P.P# = R.P#;

    Tambien vale usar la negación, como sugirio Alberto. Encontrar profesores que asisten a clases en el bloque 1, y que no existe una tupla donde el profesor asiste una clase en otro bloque.

    -- R2

    Este problema se conoce como Division Relacional (Relational Division). En este caso es una división exacta, sin resto.

    Encontrar las clases, en la tabla asistencia, agrupando por clase y donde la cantidad de las distintas asignaturas que se imparten en esta clase, es igual al número de asignaturas en la tabla asignatura. En este caso la tabla [asistencia] es el numerador y la tabla [asignatura] es el divisor.

    select
        C.*
    from
        clases as C
        inner join
        (
        select
            A.C#
        from
            asistencia as A
        group by
            A.C#
        having
            count(distinct A.A#) = (select count(*) from asignatura)
        ) as R
        on C.C# = R.C#;


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Pachini Saturday, March 17, 2012 2:51 PM
    Saturday, March 17, 2012 2:29 PM
  • Gracias por su ayuda!! Me sirvió de mucho!
    Saturday, March 17, 2012 2:51 PM
  • Existe el EXCEPT  que es equivalente al MINUS. Saludos
    Thursday, June 21, 2012 3:47 PM