Consider a DB schema consisting of the following relation schemes: Station (Stat

Question

Consider a DB schema consisting of the following relation schemes: Station (Station_ID, City, State, Lat_N, Long_w) Statistics (Statistic_ID_forastation, Month. Temp, Rain) Now, express the following queries in SOL: 1. Give details of Northern stations (Northern latitude > 39.7): 2. Query to look at the table Statistics, ordered by month and greatest rainfall, with columns rearranged. 3. Write a Query to look at temperatures for July from Statistics, lowest temperatures first, picking up city name and latitude. 4. Write a query to show MAX and MIN temperatures as well as average rainfall for each station. 5. List of stations with year-round average temperature above 50 degrees. 6. Create a view (derived table or persistent query) to convert Fahrenheit to Celsius and inches to centimeters.

Explanation / Answer

1.

Select * from Station where Lat_N > 39.7;

2.

Select * from Statitistics order by Month asc , Rain desc;

3.

Select City,Lat_N ,Temp from Station , Statistics   where Month = 7 and Statistics.Statistics_ID_forastation = Station.Station_ID order by Temp;

4.

Select Statitics_ID_forastation,MAX(Temp),MIN(Temp),AVG(Rain) from Statistics group by Statitics_ID_forastation;

5.

Select * from Station where 50 < (Select AVG(Temp) from Statistics Where Station.Station_ID = Statistics.Statistic_ID_forastation );

6.

Create view Unit_Statistics (Statistics_ID, Month, Temp, Rain) as Select Statistics_ID_forastation,Month,(Temp - 32) * 5 /9,Rain* 0.3937 from Statistics;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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