Project 1 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 Ratiol. You may optionally use the With clause. Although the results shown for AvgR and Ratiol 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. Project 1 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. STATION_ID STATION NAME CUFTON CLIFTON CUFTON CLIFTON Del Valle Reservoir Del Valle R ow Del Valle Reservoir COUNTY NAME RESULT MA Contra Costa 0.13 0.13 Contra Costa 0.06 0.13 Contra Costa 0.05 0.13 Contra Cort 0.03 0.13 Alameda 0.01 0.08 Alameda 0 05 008 Alameda 0.08 008 PARAMETER ved Aman Dissolved Ammonia Dissolved Ammonia Dissolved Ammonia Dissolved Ammonia Desaved Armania Dissolved Ammonia SAMPLE DATE 2016-01-19 14 20 00 000 2015-02-15 1425.00000 2016-03-14 13.500.000 2016-04-18 12-10-00 000 2016-01-12 10:10:00 000 2016-02-23 09 45 00000 2015-03-22 09.05.00.000 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 Ratiol. You may optionally use the With clause. Although the results shown for AvgR and Ratiol are cast as decimal (8,2), you don't have to use the Cast keyword. STATION_JO 2368 45310 46627 46625 COUNTY_NAME PARAMETER Colusa Dissolved Boron Butte Dissolved Lead Dissolved Chromium Desolved Zinc Total Comum Glenn Total nc Bure Toon SAMPLE DATE RESULT AR Ratio 1 2016-08-31 153500000 214 035 329 2016-01-11 11:20:00.0000.146 000 75.44 2016-02-23 10 00 00 000 233 034 6922 2016-02-23 1000:00 000 68 104 65.61 2016-02-23 10,00.00.000 245 044 5530 2016-02-23 100000000169 3 4 435 2016-01-11 1120000009942 1 9491 4793 46627 45310 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. COUNTY_NAME Butte Colusa 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. STATION_ID 45922 STATION_NAME First_Dt D10A - Mallard 2016-06-07 08:45:00.000 D10A - Mallard 2016-06-07 08:45:00.000 Second_DE 2016-11-07 09:50:00.000 2016-09-07 09:00:00.000 First 44.2 442 Second 7.8 45922 5
