Question

Question: Question 1 - Formulate the following queries in SQL. Schema REEF [reefname, latitude, longitude, ...

Question 1 - Formulate the following queries in SQL.

Schema

REEF [reefname, latitude, longitude, 2006_bleachedarea, summer_maximum_monthly_mean_temperature]

REEFTEMP [reefname, dateofreading, temperaturereading]

CORAL [coralname, coralcode, thermalthreshold]

CORALSAMPLING [sampleno, coralcode, reefname, dateofsampling, bleachpercent]

Find the details of all of reefs which have recorded a temperature-reading above 22 degrees.

Find the details of all reefs which have recorded a temperature-reading below the average temperature-reading across all reefs.

Find names of all reefs that do not have a sample of a coral with name including "Button”.

Find the coral that has the highest thermal-threshold.

Question 2 - Formulate the following queries in SQL.

Retrieve the names of corals which are in ALL reefs.

List reef(s) that have at least all the corals that the reef “Wreck Is.” has.

Answer 1

Question 1:

Find the details of all of reefs which have recorded a temperature-reading above 22 degrees.

Select Reef.* from Reef inner join ReefTemp on Reef.reefName = ReefTemp.reefName where temperaturereading > 22;

Find the details of all reefs which have recorded a temperature-reading below the average temperature-reading across all reefs.

Select * from Reef inner join ReefTemp on Reef.reefName = ReefTemp.reefName where temperaturereading < ( Select Avg(temperaturereading) from ReefTemp);

Find names of all reefs that do not have a sample of a coral with name including "Button”.

Select reefName from Reef inner join CoralSampling on Reef.reefName = CoralSampling.reefName inner join Coral on CoralSampling.CoralCode = Coral.CoralCode where CoralName LIKE '%Button%';

Find the coral that has the highest thermal-threshold.

Select * from Coral where thermalthreshold = (Select Max(thermalthreshold) from Coral);

Question 2 - Formulate the following queries in SQL.

Retrieve the names of corals which are in ALL reefs.

Select CoralName from Coral where CoralCode = All(Select CoralCode from CoralSampling inner join reef on CoralSampling.reefname = Reef.reefName);

List reef(s) that have at least all the corals that the reef “Wreck Is.” has.

Select Reef.reefName where Exists( Select Reef.reefName from Reef inner join CoralSampling on Reef.reefName = CoralSampling.reefName inner join Coral on CoralSampling.CoralCode = Coral.CoralCode where Reef.reefName = 'Wreck Is') ;

Do ask if any doubt. Please upvote.