Buenas!
Les dejo un parcial tomado por el profesor Moscuzza.
Me fue mal y no entiendo como resolverlo.
Alguien por favor, podría ayudarme con una posible resolución?
Desde ya muchas gracias!

No veo que haya dificultad para resolver el ejercicio, es cuestión de entender lo que hay que hacer.

En el punto 1 hay que crear un procedimiento y recibe de parámetro el Id de Compteción, por ende la instrucción es:

 

CREATE PROCEDURE SPA_ActualizaGanador @IdCompeticion int

AS

Ahí definis el procedimiento y el parámetro de entrada.

Luego nos resta definir que es lo que hay que hacer.
El problema nos plantea lo siguiente:
[list=2]
[*]Actualiza al ganador si contesto correctamente para esa competencia todas las preguntas
[*]Caso contrario no actualiza nada
[/list]

Ahora bien... ¿De qué manera podemos obtener esa información a partir del DER para armar la Query?.

El DER nos dice que para saber si una pregunta es correcta o no, hay que usar la entidad Respuesta.

Entonces partimos de nuestra tabla competiciones y ver como procedemos a obtener la información:

 

SELECT Comp.idCompeticion, JUG.IdJugador, count(1) as RespuestasCorrectas FROM COMPETICIONES COMP

	INNER JOIN LOGS LOG

		ON COMP.IdCompeticion = LOG.COMPETICION

	INNER JOIN RESPUESTA RESP

		ON RESP.idRespuesta = LOG.Respuesta

	INNER JOIN JUGADORES JUG

		ON JUG.idJugador = LOG.Jugador

WHERE COMP.idCompeticion = @IdCompeticion

	AND RESP.EsCorrecta = 'S'

GROUP BY Comp.idCompeticion, JUG.IdJugador

Acá obtenemos la cantidad de respuestas que tiene cada jugador para la competición en cuestión. Como solamente nos pide actualizar si hay alguno con 6 respuestas, debemos filtrar la consulta hecha para considerar esa condición. Para eso agregar la instrucción HAVING

 

SELECT Comp.idCompeticion, JUG.IdJugador, count(1) as RespuestasCorrectas FROM COMPETICIONES COMP

	INNER JOIN LOGS LOG

		ON COMP.IdCompeticion = LOG.COMPETICION

	INNER JOIN RESPUESTA RESP

		ON RESP.idRespuesta = LOG.Respuesta

	INNER JOIN JUGADORES JUG

		ON JUG.idJugador = LOG.Jugador

WHERE COMP.idCompeticion = @IdCompeticion

	AND RESP.EsCorrecta = 'S'

GROUP BY Comp.idCompeticion, JUG.IdJugador

HAVING COUNT(1) = 6

En este punto tenemos casi todo resuelto, ya sabemos la cantidad de jugadores con 6 respuestas correctas. El paso final es ver como usar esta información para actualizar en la tabla de competicion.
Para ello debemos declarar una variable en el procedimiento que almacena el IdJugador que tiene 6 respuestas y actualizar.

Para declarar una variable hay que usar:

 

DECLARE @variable INT;

Solo nos queda asignarle a esa variable el Id de Jugador con 6 respuestas correctas:

 

SELECT @variable = JUG.IdJugador

 FROM COMPETICIONES COMP

	INNER JOIN LOGS LOG

		ON COMP.IdCompeticion = LOG.COMPETICION

	INNER JOIN RESPUESTA RESP

		ON RESP.idRespuesta = LOG.Respuesta

	INNER JOIN JUGADORES JUG

		ON JUG.idJugador = LOG.Jugador

WHERE COMP.idCompeticion = @IdCompeticion

	AND RESP.EsCorrecta = 'S'

GROUP BY Comp.idCompeticion, JUG.IdJugador

HAVING COUNT(1) = 6

Nota:
Si la query no retorna valores, la variable te queda en null.

El resto del procedimiento es hacer updates y retornar 1 o 0.

En resumen, el procedimiento sería el siguiente:

 

CREATE PROCEDURE SPA_ActualizaGanador @IdCompeticion int

AS

BEGIN

-- declaro variable

DECLARE @idJugador INT, @ActualizoDato INT = 1







--obtengo Id Jugador con 6 respuestas

SELECT @idJugador = JUG.IdJugador

 FROM COMPETICIONES COMP

	INNER JOIN LOGS LOG

		ON COMP.IdCompeticion = LOG.COMPETICION

	INNER JOIN RESPUESTA RESP

		ON RESP.idRespuesta = LOG.Respuesta

	INNER JOIN JUGADORES JUG

		ON JUG.idJugador = LOG.Jugador

WHERE COMP.idCompeticion = @IdCompeticion

	AND RESP.EsCorrecta = 'S'

GROUP BY Comp.idCompeticion, JUG.IdJugador

HAVING COUNT(1) = 6





-- Si tengo un id de jugador actualizo la tabla



IF @idJugador IS NOT NULL

BEGIN



SET @ActualizoDato = 0



UPDATE COMPETICIONES

SET GANADOR = @IdJugador

WHERE idCompeticion = @IdCompeticion

END



--Indico si retorno 1 o 0 en funcion si actualizo o no.



Select @ActualizoDato as Valor



END

A grandes rasgos es así, fijate si te sirve y ver de encarar el de SQL solo. Sino chiflá.

Hola Martin.!
Muchas gracias por la ayuda! Me cuesta mucho entender los enunciados

una pregunta: hace falta hacer el join con Jugador? ya que el id del jugador esta en la tabla logs.

yo hice algo parecido pero sin ese join, y devolviendo un valor:

 create procedure actualiza_ganador(@id_competicion int, @retorno bit output) as

begin

	declare @ganador int



	set @ganador = (select l.jugador from Logs l join Respuestas r on l.respuesta = r.idRespuesta 

					where l.competicion = @id_competicion and r.esCorrecta = 'S' 

					group by l.jugador having count(*) = 6)

	

	if (@ganador is not null)

	begin

		update Competiciones set ganador = @ganador where idCompeticion = @id_competicion

		set @retorno = 0

	end

	else set @retorno = 1 

end

go

El dos todavia lo estoy pensado. Cuando tenga algo lo subo.
Muchas gracias!!!

---

en el 2 hice esto:

 

select p.detalle 'Pregunta',  

(select r1.detalle from Respuestas r1 where r1.pregunta = p.idPregunta and r1.esCorrecta = 'S') 'Respuesta Correcta',



(select top 1 r2.detalle from Respuestas r2 where r2.pregunta = p.idPregunta and r2.esCorrecta = 'N' order by r2.letra) 'Incorrecta 1',



(select top 1 r3.detalle from Respuestas r3 where r3.pregunta = p.idPregunta and r3.esCorrecta = 'N'

and r3.letra != 

(select top 1 r2.letra from Respuestas r2 where r2.pregunta = p.idPregunta and r2.esCorrecta = 'N' order by r2.letra) 

order by r3.letra) 'Incorrecta 2',



(select top 1 r4.detalle from Respuestas r4 where r4.pregunta = p.idPregunta and r4.esCorrecta = 'N'

and r4.letra != 

(select top 1 r3.letra from Respuestas r3 where r3.pregunta = p.idPregunta and r3.esCorrecta = 'N'

and r3.letra != 

(select top 1 r2.letra from Respuestas r2 where r2.pregunta = p.idPregunta and r2.esCorrecta = 'N' order by r2.letra) 

order by r3.letra)



and r4.letra != (select top 1 r2.letra from Respuestas r2 where r2.pregunta = p.idPregunta and r2.esCorrecta = 'N' order by r2.letra) 

order by r4.letra) 'Incorrecta 3'



from Preguntas p left join Respuestas r on p.idPregunta = r.pregunta

Buenas!,
En realidad no, no hace falta. Yo lo hice por un formalismo y acostumbrado siempre a vincular a la tabla maestra de jugadores, pero como lo hiciste está bien.
Después es discutible si es necesario usar la variable tipo output, porque el enunciado dice que debe devolver 1 u 0, pero no aclarar si en una variable o si el procedimiento debe retornar un valor.

Respecto a la consulta, lo "dificil" es saber como poner las respuestas encolumnadas.
Para eso se puede resolver de dos maneras distintas:

1. Usando subconsultas
2. Usando eficientemente el concepto de Pivot
   

Usando subconsultas:

 

SELECT P.DETALLE AS PREGUNTA,

(SELECT R.DETALLE FROM RESPUESTAS R WHERE R.PREGUNTAID = P.PREGUNTAID AND R.ESCORRECTA = 'S') AS RESPUESTA_CORRECTA,

(SELECT R.DETALLE FROM RESPUESTAS R WHERE R.PREGUNTAID = P.PREGUNTAID AND R.ESCORRECTA = 'N' 

ORDER BY R.LETRA OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) AS INCORRECTA_1,

(SELECT R.DETALLE FROM RESPUESTAS R WHERE R.PREGUNTAID = P.PREGUNTAID AND R.ESCORRECTA = 'N' 

ORDER BY R.LETRA OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY) AS INCORRECTA_2,

(SELECT R.DETALLE FROM RESPUESTAS R WHERE R.PREGUNTAID = P.PREGUNTAID AND R.ESCORRECTA = 'N' 

ORDER BY R.LETRA OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY) AS INCORRECTA_3

FROM PREGUNTAS P

Acá te devuelve el listado, luego hay que hacer el order by correspondiente.

OFFSET FETCH : https://technet.microsoft.com/es-es/libr...10%29.aspx

Si por esas cosas de la vida el OFFSET FETCH te lo prohiben por no ser Ansi SQL, se hace exactamente lo mismo pero con ROW_NUMBER() que sí lo es.

Usando Pivot:

 

SELECT INCORRECTAS.PREGUNTA, INCORRECTAS.RESPUESTA_CORRECTA,

INCORRECTAS.[1] AS INCORRECTA_1,

INCORRECTAS.[2] AS INCORRECTA_2,

INCORRECTAS.[3] AS INCORRECTA_3

(

SELECT P.DETALLE AS PREGUNTA,

 R.DETALLE AS RESPUESTA_CORRECTA,

 R1.DETALLE AS RESPUESTA_INCORRECTA,

ROW_NUMBER() OVER (PARTITION BY P.DETALLE ORDER BY R1.LETRA) N_PREGUNTA

FROM PREGUNTAS P

INNER JOIN RESPUESTAS R

ON P.PREGUNTAID = R.PREGUNTAID

INNER JOIN RESPUESTAS R1

ON P.PREGUNTAID = R.PREGUNTAID

WHERE R.ESCORRECTA = 'S'

AND R1.RESPUESTAID != R.RESPUESTAID

) X

PIVOT (MAX[R1.DETALLE] FOR N_PREGUNTA IN (1,2,3)) AS INCORRECTAS

Creo que así anda. Probalo igual porque te lo tire en el aire el código y no tengo un motor de sql para probar.

De todas formas, si no manejas bien pivot y las instrucciones de ranking, mejor hacerlo con subconsultas.

Avisame cualquier cosa!.

excelente explicación!
mil gracias loco!!!!

Perdón que siga jodiendo:
Los ordenamientos como serian?

desde ya muchas gracias!

Quizá llegué tarde... pero para el ordenamiento, es armar las querys por separado y meterlas en el order by.

Debería ser algo así:

 

SELECT P.DETALLE AS PREGUNTA,

(SELECT R.DETALLE FROM RESPUESTAS R WHERE R.PREGUNTAID = P.PREGUNTAID AND R.ESCORRECTA = 'S') AS RESPUESTA_CORRECTA,

(SELECT R.DETALLE FROM RESPUESTAS R WHERE R.PREGUNTAID = P.PREGUNTAID AND R.ESCORRECTA = 'N'

ORDER BY R.LETRA OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) AS INCORRECTA_1,

(SELECT R.DETALLE FROM RESPUESTAS R WHERE R.PREGUNTAID = P.PREGUNTAID AND R.ESCORRECTA = 'N'

ORDER BY R.LETRA OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY) AS INCORRECTA_2,

(SELECT R.DETALLE FROM RESPUESTAS R WHERE R.PREGUNTAID = P.PREGUNTAID AND R.ESCORRECTA = 'N'

ORDER BY R.LETRA OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY) AS INCORRECTA_3

FROM PREGUNTAS P

ORDER BY (SELECT COUNT(1) FROM LOGS WHERE LOGS.PREGUNTA = P.IDPREGUNTA AND ESCORRECTA = 'S') DESC,

(SELECT COUNT(1) FROM LOGS WHERE LOGS.PREGUNTA = P.IDPREGUNTA AND ESCORRECTA = 'N') asc, P.IDPREGUNTA ASC