Question

create a view vHW1_Q3 with columns (state, population, NumofZipcode) showing
the total population and the number of zip code (NumofZipcode) of each state and DC. The result should
be sorted by the population size from high to low. The output should have 51 rows. using mysql

Questions based on table Zipcode_info in datamining database. 50 US states and one DC means capital washington.

these are fields in db

+---------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| RecordNumber | int(11) | NO | PRI | 0 | |
| Zipcode | varchar(12) | YES | MUL | NULL | |
| ZipCodeType | varchar(15) | YES | | NULL | |
| City | varchar(30) | YES | MUL | NULL | |
| State | varchar(5) | YES | MUL | NULL | |
| LocationType | varchar(15) | YES | | NULL | |
| Latitude | float | YES | | NULL | |
| Longitude | float | YES | | NULL | |
| Xaxis | float | YES | | NULL | |
| Yaxis | float | YES | | NULL | |
| Zaxis | float | YES | | NULL | |
| WorldRegion | varchar(5) | YES | | NULL | |
| Country | varchar(15) | YES | | NULL | |
| LocationText | varchar(50) | YES | | NULL | |
| Location | varchar(70) | YES | | NULL | |
| Decommisioned | varchar(20) | YES | | NULL | |
| TaxReturnsFiled | int(11) | YES | | NULL | |
| EstimatedPopulation | int(11) | YES | | NULL | |
| TotalWages | int(15) | YES | | NULL | |
| AvgWages | float | YES | | NULL | |
| Notes | varchar(150) | YES | | NULL | |
+---------------------+--------------+------+-----+---------+-------+

Answer 1

The answer of this question should be as follows:

As we have to create a view,

The statement should be:

CREATE VIEW `vHW1_Q3` AS SELECT `State`, `EstimatedPopulation`,`Zipcode` FROM 'datamining.Zipcode_info' ORDER BY 'EstimatedPopulation' DESC;

Hope you like it!

Thanks for Asking!