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.

 

Instruction Files

Related Questions in data mining category