I am trying to write a sql queiry to answer this question: What lakes tend to ha

Question

I am trying to write a sql queiry to answer this question:

What lakes tend to have the best / worst water quality?

Are the lakes with the best / worst water quality consistent over time?

In the database water qaulity is determined based on these attributes: SEASONAL_LAKE_GRADE_RESULT, PHYSICAL_CONDITION_RESULT, RECREATIONAL_SUITABILITY_RESULT, SECCHI_DEPTH_RESULT, and TOTAL_PHOSPHORUS_RESULT. A few of these items are on a different kind of scale. So a low number for physical condi, recreational suitability, and total phospohorus is good, while a high number for secchi, and seasonal lake grade is good. I am trying to write a SQL query that would asnwer this.... so far i have (lake_name is the name of the column of the lakes):

select LAKE_NAME, MAX(SEASONAL_LAKE_GRADE_RESULT), Min(PHYSICAL_CONDITION_RESULT), Min(RECREATIONAL_SUITABILITY_RESULT), max(SECCHI_DEPTH_RESULT), min(TOTAL_PHOSPHORUS_RESULT)
from lake_data

from this i am only able to get the best lake, but i want at least a top 5.

Explanation / Answer

/* for ms sql */

select top 5 LAKE_NAME, SEASONAL_LAKE_GRADE_RESULT, PHYSICAL_CONDITION_RESULT,

RECREATIONAL_SUITABILITY_RESULT, SECCHI_DEPTH_RESULT, TOTAL_PHOSPHORUS_RESULT

from lake_data

order by SEASONAL_LAKE_GRADE_RESULT desc, SECCHI_DEPTH_RESULT desc,

PHYSICAL_CONDITION_RESULT, RECREATIONAL_SUITABILITY_RESULT, TOTAL_PHOSPHORUS_RESULT;

/* for mysql */

select LAKE_NAME, SEASONAL_LAKE_GRADE_RESULT, PHYSICAL_CONDITION_RESULT,

RECREATIONAL_SUITABILITY_RESULT, SECCHI_DEPTH_RESULT, TOTAL_PHOSPHORUS_RESULT

from lake_data

order by SEASONAL_LAKE_GRADE_RESULT desc, SECCHI_DEPTH_RESULT desc,

PHYSICAL_CONDITION_RESULT, RECREATIONAL_SUITABILITY_RESULT, TOTAL_PHOSPHORUS_RESULT

limit 5;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.