For parameter ‘Dissolved Ammonia’, sample date less than ‘2016-04-30’, and station 10 or 87, list station information as shown below along with result and column MaxR, which is the maximum value of result for each station for the mentioned restrictions.
data mining
Description
Project 1 ISM 6205
MSIS Spring 2020 Batra
Write SQL queries for the following from the database
Waterlab. For queries 1, 2, and 4, you may optionally use the With clause.
1. For parameter
‘Dissolved Ammonia’, sample date less than ‘2016-04-30’, and station 10 or 87,
list station information as shown below along with result and column MaxR,
which is the maximum value of result for each station for the mentioned
restrictions. You may optionally use
the With clause. Order by station id and sample date.
2. The purpose of
this query is to show the ratio of result/average for each parameter (AvgR)
where the ratio is greater than 40. In other words, the restriction is result
> 40*AvgR. List station information as shown below along with the parameter,
date, result, average for the parameter, and the ratio result/AvgR as Ratio1.
You may optionally use the With clause. Although the results shown for AvgR and
Ratio1 are cast as decimal (8,2), you don’t have to use the Cast keyword.
3. List counties that satisfy both conditions (not just any
of the two): 1) the result is greater than 0.5 for the parameter ‘Dissolved
Arsenic’, and 2) the result is greater
than 250 for the parameter ‘Dissolved Hardness’. Read the question carefully:
county name, not station id. No sorting needed.
4. For the parameter ‘Turbidity’, show all cases where the
difference in result is greater than 35 for the same station. You may
optionally use the With clause to restrict the rows for the parameter ‘Turbidity’
before you check for stations that on different occasions have had result
difference > 35. No sorting needed.
