[MySQL] Using the following tables, provide appropriate queries to the following questions. Note: outputs are shown in order to help develop the queries.

TABLE #1

mysql> DESC counties;

+------------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+---------------+------+-----+---------+-------+

| date | date | NO | PRI | NULL | |

| countyfips | varchar(5) | NO | PRI | NULL | |

| new_cases | mediumint | YES | | NULL | |

| case_avg7 | float | YES | | NULL | |

| new_deaths | smallint | YES | | NULL | |

| death_avg7 | float | YES | | NULL | |

| flag | enum('y','n') | YES | | NULL | |

+------------+---------------+------+-----+---------+-------+

7 rows in set (0.00 sec)

mysql> SELECT * FROM counties LIMIT 3;

+------------+------------+-----------+-----------+------------+------------+------+

| date | countyfips | new_cases | case_avg7 | new_deaths|death_avg7

+------------+------------+-----------+-----------+------------+---------

| 2021-01-01 | 10001 | 156 | 127.429 | 1 | 1.42857

| 2021-01-01 | 10003 | 222 | 352.714 | 2 | 2.85714

| 2021-01-01 | 10005 | 230 | 148.143 | 1 | 1.28571

+------------+------------+-----------+-----------+------------+--------

3 rows in set (0.00 sec)

TABLE #2

mysql> DESC countyfips;

+------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| countyfips | varchar(5) | NO | PRI | NULL | |

| county | varchar(35) | YES | | NULL | |

| statefips | varchar(2) | YES | MUL | NULL | |

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> SELECT * FROM countyfips LIMIT 3;

+------------+------------+-----------+

| countyfips | county | statefips |

+------------+------------+-----------+

| 10001 | Kent | 10 |

| 10003 | New Castle | 10 |

| 10005 | Sussex | 10 |

+------------+------------+-----------+

3 rows in set (0.00 sec)

TABLE #3

mysql> DESC statefips;

+-----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+-------+

| statefips | varchar(2) | NO | PRI | NULL | |

| state | varchar(24) | YES | | NULL | |

| abbrv | char(2) | YES | MUL | NULL | |

+-----------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> SELECT * FROM statefips LIMIT 3;

+-----------+----------------------+-------+

| statefips | state | abbrv |

+-----------+----------------------+-------+

| 1 | Alabama | AL |

| 10 | Delaware | DE |

| 11 | District Of Columbia | DC |

+-----------+----------------------+-------+

3 rows in set (0.00 sec)

TABLE #4

mysql> DESC states;

+------------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+------------+------+-----+---------+-------+

| date | date | NO | PRI | NULL | |

| statefips | varchar(2) | NO | PRI | NULL | |

| new_cases | mediumint | YES | | NULL | |

| case_avg7 | float | YES | | NULL | |

| new_deaths | smallint | YES | | NULL | |

| death_avg7 | float | YES | | NULL | |

+------------+------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

mysql> SELECT * FROM states LIMIT 3;

+------------+-----------+-----------+-----------+------------+---------

| date | statefips | new_cases | case_avg7 | new_deaths |death_avg7 |

+------------+-----------+-----------+-----------+------------+---------

| 2021-01-01 | 1 | 4521 | 3331.57 | 45 | 27.4286 |

| 2021-01-01 | 10 | 608 | 630.143 | 4 | 5.57143 |

| 2021-01-01 | 11 | 269 | 259.429 | 2 | 4.57143 |

+------------+-----------+-----------+-----------+------------+---------

3 rows in set (0.00 sec)

TABLE #5

mysql> DESC vaccine;

+-----------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+---------+------+-----+---------+-------+

| date | date | NO | PRI | NULL | |

| abbrv | char(2) | NO | MUL | NULL | |

| statefips | char(2) | NO | PRI | NULL | |

| admin | bigint | YES | | NULL | |

| admin12 | int | YES | | NULL | |

| admin18 | int | YES | | NULL | |

| admin65 | int | YES | | NULL | |

+-----------+---------+------+-----+---------+-------+

7 rows in set (0.01 sec)

mysql> SELECT * FROM vaccine LIMIT 3;

+------------+-------+-----------+-------+---------+---------+---------+

| date | abbrv | statefips | admin | admin12 | admin18 | admin65 |

+------------+-------+-----------+-------+---------+---------+---------+

| 2020-12-13 | AS | 60 | 0 | 0 | 0 | 0 |

| 2020-12-13 | GU | 66 | 0 | 0 | 0 | 0 |

| 2020-12-13 | VI | 78 | 0 | 0 | 0 | 0 |

+------------+-------+-----------+-------+---------+---------+---------+

3 rows in set (0.00 sec)