#15 Create a view called employee_samples that when called displays the first na

Question

#15 Create a view called employee_samples that when called displays the first name, last name, depth, ph, temperature, dissolved oxygen, conductivity, and description for all well samples. The view should sort the records by last_name in ascending order. After creating this view, select from it while showing the average depth for each method of the water samples collected by Dun Ciccitti. Give the average column an alias of avg_depth.

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

CREATE VIEW employee_samples
as
select first_name,last_name,depth,ph,temperature,dissolved oxygen,
conductivity,description
from Employee E
left join water_sample WS
on E.employee_id=WS.employee_id
left join well W
on WS.well_id=W.well_id
left join method M
on M.method_id=WS.method-id
order by last_name asc

select avg(depth) as avg_depth,method_id from employee_samples
where description='Dun Ciccitti' ----(column_name not specified for the value 'Dun Ciccitti')
group by method_id

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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