Write SQL queries for the following from the database Waterlab. For queries 1, 2, and 4, you may optionally use the With clause.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

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 usethe 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.

Attachments:

Expert's Answer

947 Times Downloaded

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of