sql query to fix

[YoungJaeChoung]

I think there are SQL queries that need to be fixed. (Thank you for sharing DB files & questions)

---

1.

• file name: GeoNuclearData.json

• question: "What are the top 3 countries which have the most nuclear power plants?"

• query (as is): "SELECT Country FROM nuclear_power_plants GROUP BY Country ORDER BY sum(Name) DESC LIMIT 3"

• query (to be): "SELECT Country FROM nuclear_power_plants GROUP BY Country ORDER BY count(*) DESC LIMIT 3"

---

2.

• file name: GeoNuclearData.json

• question: "How many countries have at least 3 nuclear power plants?"

• query (as is): "SELECT Country FROM nuclear_power_plants Group BY Country HAVING count(Name) > 3"

• query (to be): "select count(*) from (select Country from nuclear_power_plants group by Country having count(*) >= 3)"

---

3.

• file name: GreaterManchesterCrime.json

• question: 'When was the last instance of a violent or sexual offense in Manchester?'

• query (as is): SELECT CrimeID FROM GreaterManchesterCrime WHERE Type = "Violence and sexual offences" ORDER BY CrimeTS DESC LIMIT 1

• query (to be): select max(CrimeTS) from GreaterManchesterCrime where Type = 'Violence and sexual offences'

---

4.

• file name: GreaterManchesterCrime.json

• question: 'What is the top 3 area of crime conducted?'

• query (as is): SELECT Location FROM GreaterManchesterCrime GROUP BY Location ORDER BY count(*) DESC LIMIT 3

• query (to be): select LSOA from GreaterManchesterCrime group by LSOA order by count(*) desc limit 3;

---

5.

• file name: GreaterManchesterCrime.json

• question: Which neighborhood/area has the highest burglary rate?

• query (as is): SELECT Location FROM GreaterManchesterCrime WHERE Type = "Burglary" GROUP BY Location ORDER BY count(*) DESC LIMIT 1

• query (to be): select LSOA from GreaterManchesterCrime where Type = 'Burglary' group by LSOA order by count(*) desc limit 1

[Chia-Hsuan-Lee]

Hello YoungJae,

Thanks for your interest and sharing the findings on our dataset!