Question Description
I’m working on a MySQL multi-part question and need guidance to help me learn.
I would like assistance in these queries. regarding the table below
mysql> select * from sales ;
+——+——+——+——–+——-+
| xs | tloc | yrs | months | sales |
+——+——+——+——–+——-+
| NC | T09 | 2015 | 12 | 882 |
| NC | T08 | 2018 | 7 | 851 |
| NC | T02 | 2017 | 9 | 504 |
| NC | T01 | 2012 | 7 | 721 |
| NC | T04 | 2014 | 6 | 408 |
| NC | T02 | 2012 | 5 | 393 |
| NC | T08 | 2015 | 12 | 725 |
| NC | T06 | 2012 | 8 | 445 |
| NC | T07 | 2017 | 2 | 426 |
| NC | T04 | 2012 | 8 | 434 |
| NC | T09 | 2014 | 6 | 721 |
| NC | T02 | 2016 | 8 | 677 |
| NC | T09 | 2012 | 11 | 691 |
| NC | T01 | 2017 | 11 | 528 |
| NC | T01 | 2015 | 12 | 542 |
| NC | T07 | 2014 | 1 | 316 |
| NC | T06 | 2013 | 10 | 485 |
| NC | T04 | 2016 | 2 | 356 |
| NC | T03 | 2013 | 8 | 608 |
| NC | T02 | 2016 | 4 | 393 |
| ND | T05 | 2017 | 6 | 836 |
| ND | T01 | 2013 | 10 | 531 |
| ND | T05 | 2017 | 4 | 875 |
| ND | T02 | 2017 | 1 | 889 |
| ND | T05 | 2016 | 2 | 634 |
| ND | T09 | 2017 | 12 | 309 |
| ND | T07 | 2014 | 1 | 722 |
| ND | T04 | 2018 | 10 | 485 |
| ND | T08 | 2016 | 10 | 458 |
| ND | T01 | 2018 | 5 | 694 |
| ND | T02 | 2018 | 11 | 512 |
| ND | T02 | 2018 | 11 | 456 |
| ND | T01 | 2015 | 12 | 529 |
| ND | T03 | 2018 | 9 | 790 |
| ND | T06 | 2012 | 9 | 888 |
| ND | T04 | 2018 | 11 | 749 |
| ND | T02 | 2015 | 1 | 582 |
| ND | T07 | 2014 | 4 | 626 |
| ND | T07 | 2017 | 3 | 897 |
| ND | T01 | 2013 | 7 | 343 |
| NH | T08 | 2014 | 9 | 724 |
| NH | T09 | 2018 | 2 | 412 |
| NH | T02 | 2013 | 9 | 323 |
| NH | T08 | 2012 | 10 | 870 |
| NH | T05 | 2015 | 12 | 411 |
| NH | T02 | 2013 | 10 | 756 |
| NH | T08 | 2018 | 9 | 857 |
| NH | T03 | 2012 | 12 | 546 |
| NH | T09 | 2017 | 8 | 526 |
| NH | T04 | 2012 | 5 | 318 |
| NH | T06 | 2014 | 8 | 847 |
| NH | T04 | 2013 | 12 | 344 |
| NH | T02 | 2013 | 11 | 414 |
| NH | T05 | 2012 | 1 | 523 |
| NH | T09 | 2016 | 12 | 375 |
| NH | T07 | 2016 | 9 | 395 |
| NH | T01 | 2016 | 4 | 583 |
| NH | T08 | 2018 | 6 | 743 |
| NH | T02 | 2015 | 11 | 440 |
| NH | T08 | 2018 | 5 | 732 |
| NJ | T03 | 2015 | 4 | 565 |
| NJ | T03 | 2017 | 9 | 828 |
| NJ | T08 | 2017 | 6 | 731 |
| NJ | T09 | 2013 | 1 | 324 |
| NJ | T02 | 2016 | 2 | 477 |
| NJ | T03 | 2015 | 9 | 574 |
| NJ | T05 | 2012 | 7 | 810 |
| NJ | T06 | 2018 | 4 | 678 |
| NJ | T03 | 2012 | 10 | 874 |
| NJ | T07 | 2014 | 7 | 732 |
| NJ | T08 | 2018 | 5 | 441 |
| NJ | T05 | 2017 | 12 | 482 |
| NJ | T09 | 2017 | 6 | 670 |
| NJ | T02 | 2015 | 2 | 458 |
| NJ | T03 | 2013 | 4 | 523 |
| NJ | T06 | 2013 | 8 | 514 |
| NJ | T01 | 2013 | 4 | 622 |
| NJ | T04 | 2016 | 7 | 500 |
| NJ | T02 | 2015 | 7 | 588 |
| NJ | T04 | 2017 | 1 | 833 |
| NM | T09 | 2016 | 10 | 728 |
| NM | T03 | 2017 | 1 | 339 |
| NM | T09 | 2017 | 4 | 725 |
| NM | T03 | 2012 | 10 | 365 |
| NM | T09 | 2016 | 8 | 571 |
| NM | T04 | 2016 | 2 | 421 |
| NM | T02 | 2018 | 2 | 834 |
| NM | T01 | 2014 | 12 | 581 |
| NM | T01 | 2015 | 12 | 475 |
| NM | T03 | 2016 | 10 | 704 |
| NM | T02 | 2016 | 10 | 482 |
| NM | T05 | 2018 | 6 | 711 |
| NM | T01 | 2013 | 1 | 640 |
| NM | T04 | 2016 | 5 | 691 |
| NM | T06 | 2013 | 7 | 427 |
| NM | T02 | 2016 | 1 | 529 |
| NM | T01 | 2012 | 2 | 362 |
| NM | T05 | 2014 | 3 | 766 |
| NM | T01 | 2016 | 6 | 604 |
| NM | T09 | 2018 | 5 | 559 |
| NV | T09 | 2016 | 10 | 699 |
| NV | T05 | 2014 | 2 | 591 |
| NV | T07 | 2018 | 9 | 515 |
| NV | T01 | 2012 | 12 | 326 |
| NV | T05 | 2015 | 5 | 438 |
| NV | T01 | 2013 | 10 | 341 |
| NV | T03 | 2013 | 1 | 795 |
| NV | T06 | 2014 | 5 | 782 |
| NV | T01 | 2016 | 8 | 852 |
| NV | T02 | 2014 | 4 | 380 |
| NV | T07 | 2015 | 11 | 483 |
| NV | T01 | 2012 | 6 | 302 |
| NV | T09 | 2016 | 2 | 315 |
| NV | T06 | 2013 | 12 | 772 |
| NV | T09 | 2016 | 6 | 795 |
| NV | T02 | 2015 | 3 | 881 |
| NV | T09 | 2015 | 3 | 722 |
| NV | T09 | 2016 | 2 | 798 |
| NV | T05 | 2016 | 2 | 815 |
| NV | T03 | 2013 | 7 | 736 |
| NY | T08 | 2016 | 3 | 829 |
| NY | T08 | 2016 | 4 | 332 |
| NY | T05 | 2015 | 7 | 664 |
| NY | T09 | 2016 | 5 | 815 |
| NY | T02 | 2016 | 8 | 467 |
| NY | T03 | 2012 | 2 | 651 |
| NY | T05 | 2013 | 10 | 382 |
| NY | T09 | 2016 | 3 | 726 |
| NY | T04 | 2018 | 3 | 444 |
| NY | T06 | 2012 | 3 | 459 |
| NY | T08 | 2016 | 12 | 673 |
| NY | T03 | 2013 | 9 | 684 |
| NY | T05 | 2015 | 3 | 800 |
| NY | T04 | 2012 | 8 | 758 |
| NY | T05 | 2018 | 11 | 557 |
| NY | T05 | 2017 | 6 | 833 |
| NY | T01 | 2017 | 7 | 667 |
| NY | T07 | 2018 | 2 | 512 |
| NY | T01 | 2018 | 12 | 687 |
| NY | T06 | 2017 | 1 | 722 |
+——+——+——+——–+——-+
140 rows in set (0.00 sec)
3) Write a query to generate a report as follows (20)
<state>,<towns>,<number_of_towns_in_state>,<total_sales>
4) Write a query to generate a report as follows (20)
<state>,<town_that_generated_the_most_revenue>
5) Write a query to generate a report as follows (10)
<state>,<year>,<total_sales>
6) Write a query to generate a report as follows (10)
<state>,<year>,<month>,<total_sales>
7) Write a query to generate a report as follows (20)
<state>,<year>,<month>,<town_that_generated_the_most_revenue>
8) Write a query to generate a report as follows (20)
<state>,<year>,<town1>,<total_sales1>,<town2>,<total_sales2>
subject to the following conditions:
town1 and town2 are in the same state,
total_sales1,total_sales2 are the total sales for town1 and town2 for that year
and total_sales1 > total_sales2
9) Same kind of report as in 8 subject to the following conditions
town1 and town2 are in the same state,
total_sales1,total_sales2 are the total sales for town1 and town2 for that year
and total_sales1 is the maximum revenue for that year by any town in the state
and total_sales2 is the minimum revenue for that year by any town in the state
