[MySQL] Using The Following Tables, Provide Appropriate Queries To The Following Questions. Note: Outputs Are Shown In Order To Help Develop The Queries.

SQL-2
1. Which state in the US has maximum number of counties. Show: State, Number of Counties.
Show only one row for the top ranked state.
+——-+——————–+
| state | Number of Counties |
+——-+——————–+
| Texas | 254 |
+——-+——————–+
2. Which are the ten counties in the US that reported highest 7-day average of cases on
Jan 15, 2022? The query output should show these columns: Date, County, State, 7-Day
Moving Average of Daily Cases. The labels/headers of columns in the query output should
match the labels in the question, i.e. (Date, County, State, 7-Day Moving Average of Daily
Cases).
+————+————-+————+————————————-+
| Date | county | state | 7-Day Moving Average of Daily Cases |
+————+————-+————+————————————-+
| 2022-01-15 | Los Angeles | California | 41682 |
| 2022-01-15 | Harris | Texas | 18775.9 |
| 2022-01-15 | San Diego | California | 13862.3 |
| 2022-01-15 | Miami-Dade | Florida | 13331.6 |
| 2022-01-15 | Maricopa | Arizona | 12112.7 |
| 2022-01-15 | Cook | Illinois | 9925.57 |
| 2022-01-15 | Orange | California | 8832.86 |
| 2022-01-15 | Broward | Florida | 6871.86 |
| 2022-01-15 | Bexar | Texas | 6532 |
| 2022-01-15 | Riverside | California | 6391.29 |
+————+————-+————+————————————-+
3. Which five states reported highest number of total cases in the year 2021? The query
output should show these columns: State, Total Cases in 2021. The labels/headers of
columns in the query output should match the labels in the question. Sort the output by
“Total Cases” in decreasing order.
1
+————–+————-+
| state | Total Cases |
+————–+————-+
| California | 5016498 |
| Texas | 3929917 |
| Florida | 3873411 |
| New York | 3601020 |
| Pennsylvania | 1860542 |
+————–+————-+
4. If Mortality Rate is the ratio of number of deaths to the number of cases, which ten US
states has had the lowest average mortality ratio? The query output should show these
columns: State, Mortality Ratio. The labels/headers of columns in the query output should
match the labels in the question. The mortality ratio values should be rounded off to max
two decimal places.
+—————-+—————–+
| state | Mortality Ratio |
+—————-+—————–+
| American Samoa | NULL |
| Utah | 0.80 |
| Alaska | 0.80 |
| North Dakota | 0.84 |
| Vermont | 0.86 |
| Colorado | 0.94 |
| Rhode Island | 1.07 |
| New York | 1.11 |
| Washington | 1.15 |
| New Hampshire | 1.18 |
+—————-+—————–+
6. Compare Mortality Ratio of US states by month in the 4th quarter of 2021. Show: State,
Month-Year, Mortality Ratio. Sort the output by state and month.
## Only top rows are shown for illustration
+—————-+——-+—————–+
| State | Month | Mortality Ratio |
+—————-+——-+—————–+
| Alabama | 12 | 1.25 |
Page 2
| Alabama | 11 | 3.60 |
| Alabama | 10 | 4.05 |
| Alaska | 12 | 2.31 |
| Alaska | 11 | 1.33 |
| Alaska | 10 | 0.66 |
| Arizona | 12 | 2.02 |
| Arizona | 11 | 1.14 |
| Arizona | 10 | 2.01 |
| Arkansas | 12 | 2.06 |
| Arkansas | 11 | 2.34 |
| Arkansas | 10 | 4.64 |
| California | 12 | 0.77 |
| California | 11 | 11.74 |
| California | 10 | 1.59 |
| Colorado | 12 | 1.60 |
| Colorado | 11 | 1.09 |
+—————-+——-+—————–+
7. Show summed by month the total of daily cases and daily deaths reported by Hidalgo
county in Texas in 2021. The query output should show these columns: State, County,
Year, Month, Sum of Daily Cases, Sum of Daily Deaths.
+——-+———+——+——-+——————–+
| State | County | Year | Month | Sum of Daily Cases |
+——-+———+——+——-+——————–+
| Texas | Hidalgo | 2021 | 1 | 11797 |
| Texas | Hidalgo | 2021 | 2 | 12978 |
| Texas | Hidalgo | 2021 | 3 | 7854 |
| Texas | Hidalgo | 2021 | 4 | 3559 |
| Texas | Hidalgo | 2021 | 5 | 2272 |
+——-+———+——+——-+——————–+
8. Summarize total vaccines administered (use the “admin” column” in the table) in the state
of Texas by each month starting from Jan 1, 2021. The output should show: Month-Year,
Total Vaccines Administered.
+————+—————————-+
| Month-Year | Total Vaccines Adminstered |
+————+—————————-+
| 2021-1 | 43010987 |
| 2021-2 | 112496523 |
| 2021-3 | 267880487 |
| 2021-4 | 471869283 |
| 2021-5 | 654603944 |
| 2021-6 | 732799588 |
Page 3
| 2021-7 | 822101607 |
| 2021-8 | 891804945 |
| 2021-9 | 941366915 |
| 2021-10 | 1036312761 |
| 2021-11 | 1078255877 |
| 2021-12 | 1213032682 |
| 2022-1 | 1302372889 |
| 2022-2 | 173030240 |
+————+—————————-+
Page 4