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.

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. 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

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme