Show the description, average ph, and maximum conductivity for each method where

Question

Show the description, average ph, and maximum conductivity for each method where the average ph is greater than neutral (i.e. 7). (SQL)

SELECT m.description,ws.conductivity, ws.ph

FROM(SELECT MAX(conductivity) AS max_conductivity FROM water_sample

WHERE AVG(ph) > 7);

This is what I have so far but dont know how to joing the tables to return the method description and give the tables an alias.

Below are my tables.

employee * employee_id first-name last_name email (0) gender (0) ob A a employee_id a wellid water_sampleA - startdate end-date (o) # sample-id - ph (0) temperature (0) E street (o) E city (o) state (o) s conductivity (o) dissolved_oxygen (o) E zipcode (o) hire-date (0) date time (0) well_id method-id employee_id well # wellid s depth (o) 9 street (o) city (o) - state (o) zipcode (o) method A methodid description -

Explanation / Answer

The query for the question is given below:-

SELECT m.description,AVG(ws.ph) AS Average_ph,MAX(ws.conductivity) AS Maximum_Conductivity
FROM water_sample AS ws,method AS m
WHERE m.method_id = ws.method_id AND AVG(ws.ph)>7;

To join the two tables (water_sample and method) you have to match their method_id as done in the where condition and alias for the table is given using the AS keyword.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.