MySQL homework 2

A short essay with my course-Database Managemnt homework 2

---

• Context:

This dataset contains information about the dynamics of the disease in an artificial city with its population of 500 people.
The dataset contains the information about the temperature of air in the city and the number of people who are sick.
For each day in the data there must be only one value of temperature measurement and only one measurement of the number of sick people.

• Table schema:

Two table: Sick(year,month,day,nsick), temperature(year,month,day,temperature).
Here year has 4 digits, month has 2 digits (1-12), day has 2 digits(1-31), and nsick can vary from 0 to 500,
temperature can deviate maximum from -50 to + 50 (in Celsius)

• QUestion 1:

Find the highest and lowest temperature for each month along with the average for that month.
Your answer should include sql query and the actual answer.
Answer:

SELECT year,month,MIN(temperature),AVG(temperature) FROM `temperature` GROUP BY year,month

• Question 2:

Table Temperature contains temperature records in Celsius.
Create table TemperatureK which will have the same columns as table Temperature.
Populate table TemperatureK based on the information which is in table Temperature by using INSERT ... SELECT query,
where temperature values in table TemperatureK will be in Kelvin. REMINDER: C + 273 = K
Answer:

CREATE TABLE TemperatureK( 
    year integer,
    month integer,
    day integer, 
    temperature integer 
);
    
INSERT INTO TemperatureK(year,month,day,temperature) 
SELECT year,month,day,temperature+273 FROM temperature

• Question 3:

How many years of observations is recorded in the dataset and which year has the least number of observations?
Provide the numerical answer together with the sql query to get that answer.
Answer:
subquestion 1:

SELECT COUNT(DISTINCT year) FROM temperatur

subquestion 2:

SELECT year,COUNT(*) as num FROM temperature GROUP BY year ORDER BY num ASC LIMIT 1

• Question 4:

What is the lowest temperature in each year on any given day? Provide numerical answer and the sql query.
Answer:

SELECT year,min(temperature) FROM temperature GROUP BY year

• Question 5:

In which months of year 2006 were the total number of sick people more than 80? Provide the numerical answer and the sql query.
Answer:

SELECT M,S FROM (
    SELECT month as M,sum(nsick) as S FROM sick WHERE year=2006 GROUP BY month
)as A WHERE S>80

• Question 6:

Find the day and the number of people who are sick in the data set when this number was the highest.
Use nested query to get the answer. Provide the numerical result and the sql query
Answer:
Solution 1:

SELECT year,month,day,nsick FROM sick WHERE nsick=(
    SELECT max(nsick) FROM sick
)

Solution 2:

SELECT year,month,day,nsick FROM sick order BY nsick DESC LIMIT 1

• Question 7:

Find those years , months and the total number of sick people where this number was less than 100 and more than 50.
Use NOT IN to answer this question.
Answer:
Solution 1:

SELECT year,month,sum(nsick) FROM sick WHERE (year,month) not in ( 
    SELECT S2.year,S2.month FROM sick S2 GROUP BY S2.year,S2.month 
    Having sum(S2.nsick)>=100 or sum(S2.nsick)<=50
) GROUP BY year,month

Solution 2:

SELECT Y,M,SUM FROM (
    SELECT year as Y,month as M,sum(nsick) as SUM FROM sick GROUP BY year,month
)as A WHERE SUM<100 and SUM>50

• Question 8:

Find those months in year 2003 when there are some days in that month the number of sick people was greater than 100.
Use nested queries. Provide the numerical answer and the sql query. Use EXISTS to answer this question.
Answer:

SELECT DISTINCT month FROM sick as A WHERE exists (
    SELECT month FROM sick as B WHERE nsick>100 and year=2003 and A.month=B.month
)