BD sql

[mastervlad]

Tabelul rezultate(id_concurent, b1, b2, b3, b4, b5, b6, b7, b8, b9, b10) in care se salveaza rezultatele unor concurenti la bowling (10 aruncari, fiecare rezultat fiind un numar intre 0 si 10). Afisati concurentii cu punctaj 0.(3 aruncari ratate)

[TheWiner]

Incearca:

Select id_concurent, count( * ) as Aruncari from rezultate where count( * ) < 7

[QUADMACHINE]

Nu am testat.

SELECT `id_concurent` AS `ConcurentCuRatari`, `b1`, `b2`, `b3`, `b4`, `b5`, `b6`, `b7`, `b8`, `b9`, `b10` FROM `Bowling`
WHERE 0 IN(`b1`, `b2`, `b3`, `b4`, `b5`, `b6`, `b7`, `b8`, `b9`, `b10`) AND HAVING COUNT(*) < 3 AND GROUP BY `id_concurent`

SELECT `id_concurent`, SUM(CASE WHEN
	(b1 <= 0 AND COUNT(b1) < 3) OR
	(b2 <= 0 AND COUNT(b2) < 3) OR
	(b3 <= 0 AND COUNT(b3) < 3) OR
	(b4 <= 0 AND COUNT(b4) < 3) OR
	(b5 <= 0 AND COUNT(b5) < 3) OR
	(b6 <= 0 AND COUNT(b6) < 3) OR
	(b7 <= 0 AND COUNT(b7) < 3) OR
	(b8 <= 0 AND COUNT(b8) < 3) OR
	(b9 <= 0 AND COUNT(b9) < 3) OR
	(b10 <= 0 AND COUNT(b10) < 3) 
	THEN 1 
        ELSE 0 
      END) AS ConcurentCuRatari
FROM `Bowling` GROUP BY `id_concurent`

[QUADMACHINE]

Trebuie sa caute in coloane multiple daca Vasile, George sau Ionel au punctaj 0 de 3 ori in una dintre coloanele b(3 ratari).

ex:

George:

b1 b2 b3 b4 b5 b6 b7 b8 b9 b10

1 1 2 6 6 8 12 0 1 2

True(Conditie adevarata, are trei de 0 adica 3 ratari)

Vasile:

b1 b2 b3 b4 b5 b6 b7 b8 b9 b10

1 0 2 0 6 8 12 0 1 2

George:

b1 b2 b3 b4 b5 b6 b7 b8 b9 b10

1 0 2 6 6 8 12 0 1 2