Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # Running docker container and restoring database
- 1. docker pull mcr.microsoft.com/mssql/server:2017-latest
- 2. docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' \
- --name 'sql1' -p 1401:1433 \
- -v sql1data:/var/opt/mssql \
- -d mcr.microsoft.com/mssql/server:2017-latest
- 3. docker exec -it sql1 mkdir /var/opt/mssql/backup
- 4. docker cp us_national_statistics.bak sql1:/var/opt/mssql/backup
- 5. docker exec -it sql1 "bash"
- 6. /opt/mssql-tools/bin/sqlcmd -S localhost -U SA [The password is: <YourStrong!Passw0rd>]
- 7. RESTORE DATABASE US_NATIONAL_COPY FROM DISK = "/var/opt/mssql/backup/us_national_statistics.bak" WITH REPLACE, RECOVERY, MOVE "us_national_statistics" TO "/var/opt/mssql/data/usns_copy.mdf", MOVE "us_national_statistics_log" TO "/var/opt/mssql/data/usns_copy.ldf";
- # Interacting in SQL
- ## List all databases
- ```
- SELECT name from master.sys.databases
- GO
- ```
- ```
- name
- --------------------------------------------------------------------------------------------------------------------------------
- master
- tempdb
- model
- msdb
- US_NATIONAL_COPY
- (5 rows affected)
- ```
- ## Switch to US_NATIONAL_COPY database
- ```
- USE US_NATIONAL_COPY
- GO
- ```
- ```
- Changed database context to 'US_NATIONAL_COPY'.
- ```
- ## List all the base tables in this database
- ```
- SELECT TABLE_NAME FROM US_NATIONAL_COPY.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
- GO
- ```
- ```
- TABLE_NAME
- --------------------------------------------------------------------------------------------------------------------------------
- education_codes
- employment_categories
- household_income
- energy_census_and_economic_data_us_2010_2014
- person_economic_info
- states
- (6 rows affected)
- ```
- ## List all column names from person_economic_info
- ```
- SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='person_economic_info'
- GO
- ```
- ```
- COLUMN_NAME
- --------------------------------------------------------------------------------------------------------------------------------
- age
- marital_status
- address_state
- income
- income_category
- car_price
- car_price_category
- education
- years_employed
- retired
- employment_category
- gender
- length_at_current_residence
- wireless
- multiple_lines
- voice_mail
- pager
- internet
- caller_id
- call_waiting
- own_tv
- own_dvd_player
- own_4k_tv
- own_smartphone
- own_computer
- own_fax
- read_newspapers
- (27 rows affected)
- ```
- ## Creating View: annotated_person_info
- I am going to have state_number and state_name in this view only. I could not understand the complete question as to why do we need employment categoy in this.
- ```
- CREATE VIEW annotated_person_info AS SELECT numeric_id, us_state_terr from states ;
- GO
- ```
- ```
- 1> select * from annotated_person_info
- 2> GO
- numeric_id us_state_terr
- ----------- --------------------------------------------------
- 0 Not Reported
- 1 Alabama
- 2 Alaska
- 3 Arizona
- 4 Arkansas
- 5 California
- 6 Colorado
- 7 Connecticut
- 8 Delaware
- 9 Florida
- 10 Georgia
- 11 Hawaii
- 12 Idaho
- 13 Illinois
- 14 Indiana
- 15 Iowa
- 16 Kansas
- 17 Kentucky
- 18 Louisiana
- 19 Maine
- 20 Maryland
- 21 Massachusetts
- 22 Michigan
- 23 Minnesota
- 24 Mississippi
- 25 Missouri
- 26 Montana
- 27 Nebraska
- 28 Nevada
- 29 New Hampshire
- 30 New Jersey
- 31 New Mexico
- 32 New York
- 33 North Carolina
- 34 North Dakota
- 35 Ohio
- 36 Oklahoma
- 37 Oregon
- 38 Pennsylvania
- 39 Rhode Island
- 40 South Carolina
- 41 South Dakota
- 42 Tennessee
- 43 Texas
- 44 Utah
- 45 Vermont
- 46 Virginia
- 47 Washington
- 48 West Virginia
- 49 Wisconsin
- 50 Wyoming
- 51 American Samoa
- 52 District of Columbia
- 53 Federated States of Micronesia
- 54 Guam
- 55 Marshall Islands
- 56 Northern Mariana Islands
- 57 Palau
- 58 Puerto Rico
- 59 Virgin Islands
- (60 rows affected)
- ```
- ## For each state, give the following summary information (result should have one row per state). Use the annotated_person_info view to includes the name of the state from the states table
- ```
- Number of people reported
- Number of 4k televisions
- Number of smartphones
- Highest income
- Average income
- Percentage of respondents that are male
- ```
- First, I am going to join the two tables on the basis of state_id:
- ```
- select p.address_state state_code, a.us_state_terr from person_economic_info p inner join annotated_person_info a on p.address_state = a.numeric_id
- ```
- ```
- 40 South Carolina
- 0 Not Reported
- 13 Illinois
- 13 Illinois
- 25 Missouri
- 12 Idaho
- 22 Michigan
- 21 Massachusetts
- 3 Arizona
- 3 Arizona
- 4 Arkansas
- ```
- Now, I have to select the average income and the state name.
- ```
- select avg(p.income) average_income, p.address_state state_code, a.us_state_terr state_name from person_economic_info p inner join annotated_person_info a on p.address_state = a.numeric_id group by p.address_state, a.us_state_terr
- GO
- ```
- ```
- average_income state_code state_name
- -------------- ----------- --------------------------------------------------
- 50934 0 Not Reported
- 92300 23 Minnesota
- 130800 46 Virginia
- 124157 29 New Hampshire
- 64412 9 Florida
- 70611 15 Iowa
- 52695 3 Arizona
- 379500 52 District of Columbia
- 110550 32 New York
- 99961 26 Montana
- 68013 12 Idaho
- 80931 35 Ohio
- 55961 6 Colorado
- 24500 43 Texas
- 19000 49 Wisconsin
- 72682 21 Massachusetts
- 108750 27 Nebraska
- 144041 38 Pennsylvania
- 61725 7 Connecticut
- 98250 44 Utah
- 50630 1 Alabama
- 13000 50 Wyoming
- 90896 24 Mississippi
- 122500 47 Washington
- 85459 18 Louisiana
- 123039 30 New Jersey
- 70571 10 Georgia
- 54461 41 South Dakota
- 57660 4 Arkansas
- 76218 19 Maine
- 119376 25 Missouri
- 85000 36 Oklahoma
- 75777 13 Illinois
- 81500 42 Tennessee
- 61735 5 California
- 106790 22 Michigan
- 105972 33 North Carolina
- 93400 39 Rhode Island
- 64792 16 Kansas
- 136000 45 Vermont
- 48132 2 Alaska
- 79333 48 West Virginia
- 90469 17 Kentucky
- 139339 31 New Mexico
- 107883 34 North Dakota
- 85368 40 South Carolina
- 60650 11 Hawaii
- 86782 20 Maryland
- 119888 28 Nevada
- 79475 14 Indiana
- 94703 37 Oregon
- 63089 8 Delaware
- 79000 51 American Samoa
- (53 rows affected)
- ```
- Next step would be to get the number of people reported:
- In the next step, I can go for max(income) to get the highest income per state using group by.
- ```
- select avg(p.income) average_income, max(p.income) highest_income, p.address_state state_code, a.us_state_terr state_name from person_economic_info p inner join annotated_person_info a on p.address_state = a.numeric_id group by p.address_state, a.us_state_terr
- 2> GO
- ```
- ```
- average_income highest_income state_code state_name
- -------------- -------------- ----------- --------------------------------------------------
- 50934 725000 0 Not Reported
- 92300 743000 23 Minnesota
- 130800 309000 46 Virginia
- 124157 496000 29 New Hampshire
- 64412 525000 9 Florida
- 70611 674000 15 Iowa
- 52695 759000 3 Arizona
- 379500 592000 52 District of Columbia
- 110550 665000 32 New York
- 99961 530000 26 Montana
- 68013 437000 12 Idaho
- 80931 243000 35 Ohio
- 55961 406000 6 Colorado
- 24500 40000 43 Texas
- 19000 24000 49 Wisconsin
- 72682 333000 21 Massachusetts
- 108750 476000 27 Nebraska
- 144041 489000 38 Pennsylvania
- 61725 368000 7 Connecticut
- 98250 419000 44 Utah
- 50630 359000 1 Alabama
- 13000 19000 50 Wyoming
- 90896 320000 24 Mississippi
- 122500 463000 47 Washington
- 85459 788000 18 Louisiana
- 123039 613000 30 New Jersey
- 70571 873000 10 Georgia
- 54461 238000 41 South Dakota
- 57660 707000 4 Arkansas
- 76218 441000 19 Maine
- 119376 884000 25 Missouri
- 85000 252000 36 Oklahoma
- 75777 1070000 13 Illinois
- 81500 467000 42 Tennessee
- 61735 718000 5 California
- 106790 706000 22 Michigan
- 105972 481000 33 North Carolina
- 93400 345000 39 Rhode Island
- 64792 297000 16 Kansas
- 136000 434000 45 Vermont
- 48132 362000 2 Alaska
- 79333 126000 48 West Virginia
- 90469 837000 17 Kentucky
- 139339 568000 31 New Mexico
- 107883 382000 34 North Dakota
- 85368 462000 40 South Carolina
- 60650 426000 11 Hawaii
- 86782 374000 20 Maryland
- 119888 504000 28 Nevada
- 79475 976000 14 Indiana
- 94703 382000 37 Oregon
- 63089 529000 8 Delaware
- 79000 190000 51 American Samoa
- (53 rows affected)
- ```
- Now, for "Number of people reported", I will have to have the count per state. So, I am going to use the COUNT function.
- In order to get the count of number of people per state:
- ```
- 1> select count(*) number_of_people_reported, address_state state_code from person_economic_info group by person_economic_info.address_state
- 2> GO
- number_of_people_reported state_code
- ------------------------- -----------
- 455 0
- 120 23
- 5 46
- 57 29
- 211 9
- 175 15
- 365 3
- 2 52
- 40 32
- 77 26
- 224 12
- 29 35
- 285 6
- 6 43
- 3 49
- 126 21
- 64 27
- 24 38
- 255 7
- 8 44
- 395 1
- 3 50
- 87 24
- 6 47
- 137 18
- 51 30
- 257 10
- 13 41
- 324 4
- 128 19
- 69 25
- 16 36
- 207 13
- 10 42
- 340 5
- 110 22
- 37 33
- 15 39
- 135 16
- 4 45
- 392 2
- 3 48
- 166 17
- 53 31
- 43 34
- 19 40
- 209 11
- 115 20
- 45 28
- 183 14
- 27 37
- 267 8
- 3 51
- (53 rows affected)
- ```
- Now. combining this query with the previous query:
- ```
- select count(*) number_of_people, avg(p.income) average_income, max(p.income) highest_income, p.address_state state_code, a.us_state_terr state_name from person_economic_info p inner join annotated_person_info a on p.address_state = a.numeric_id group by p.address_state, a.us_state_terr
- GO
- ```
- ```
- number_of_people average_income highest_income state_code state_name
- ---------------- -------------- -------------- ----------- --------------------------------------------------
- 455 50934 725000 0 Not Reported
- 120 92300 743000 23 Minnesota
- 5 130800 309000 46 Virginia
- 57 124157 496000 29 New Hampshire
- 211 64412 525000 9 Florida
- 175 70611 674000 15 Iowa
- 365 52695 759000 3 Arizona
- 2 379500 592000 52 District of Columbia
- 40 110550 665000 32 New York
- 77 99961 530000 26 Montana
- 224 68013 437000 12 Idaho
- 29 80931 243000 35 Ohio
- 285 55961 406000 6 Colorado
- 6 24500 40000 43 Texas
- 3 19000 24000 49 Wisconsin
- 126 72682 333000 21 Massachusetts
- 64 108750 476000 27 Nebraska
- 24 144041 489000 38 Pennsylvania
- 255 61725 368000 7 Connecticut
- 8 98250 419000 44 Utah
- 395 50630 359000 1 Alabama
- 3 13000 19000 50 Wyoming
- 87 90896 320000 24 Mississippi
- 6 122500 463000 47 Washington
- 137 85459 788000 18 Louisiana
- 51 123039 613000 30 New Jersey
- 257 70571 873000 10 Georgia
- 13 54461 238000 41 South Dakota
- 324 57660 707000 4 Arkansas
- 128 76218 441000 19 Maine
- 69 119376 884000 25 Missouri
- 16 85000 252000 36 Oklahoma
- 207 75777 1070000 13 Illinois
- 10 81500 467000 42 Tennessee
- 340 61735 718000 5 California
- 110 106790 706000 22 Michigan
- 37 105972 481000 33 North Carolina
- 15 93400 345000 39 Rhode Island
- 135 64792 297000 16 Kansas
- 4 136000 434000 45 Vermont
- 392 48132 362000 2 Alaska
- 3 79333 126000 48 West Virginia
- 166 90469 837000 17 Kentucky
- 53 139339 568000 31 New Mexico
- 43 107883 382000 34 North Dakota
- 19 85368 462000 40 South Carolina
- 209 60650 426000 11 Hawaii
- 115 86782 374000 20 Maryland
- 45 119888 504000 28 Nevada
- 183 79475 976000 14 Indiana
- 27 94703 382000 37 Oregon
- 267 63089 529000 8 Delaware
- 3 79000 190000 51 American Samoa
- (53 rows affected)
- ```
- Now, I need to figure out how to have a condition and then have it's result counted:
- ```
- 1> select count(*) number_of_4k_tv_owners, address_state state_code from person_economic_info where own_4k_tv=1 group by address_state
- 2> GO
- number_of_4k_tv_owners state_code
- ---------------------- -----------
- 423 0
- 120 23
- 5 46
- 57 29
- 208 9
- 174 15
- 342 3
- 2 52
- 40 32
- 77 26
- 222 12
- 29 35
- 277 6
- 6 43
- 3 49
- 125 21
- 62 27
- 22 38
- 249 7
- 7 44
- 371 1
- 2 50
- 86 24
- 6 47
- 137 18
- 51 30
- 248 10
- 13 41
- 310 4
- 125 19
- 69 25
- 15 36
- 202 13
- 9 42
- 329 5
- 110 22
- 36 33
- 14 39
- 133 16
- 4 45
- 379 2
- 3 48
- 164 17
- 50 31
- 43 34
- 19 40
- 207 11
- 113 20
- 44 28
- 181 14
- 26 37
- 259 8
- 3 51
- (53 rows affected)
- ```
- So, the query in the final query formed as of now is:
- ```
- select count(case when p.own_4k_tv = 1 then 1 end) number_of_4k_tv_owners, count(*) number_of_people, avg(p.income) average_income, max(p.income) highest_income, p.address_state state_code, a.us_state_terr state_name from person_economic_info p inner join annotated_person_info a on p.address_state = a.numeric_id group by p.address_state, a.us_state_terr
- GO
- ```
- ```
- number_of_4k_tv_owners number_of_people average_income highest_income state_code state_name
- ---------------------- ---------------- -------------- -------------- ----------- --------------------------------------------------
- 423 455 50934 725000 0 Not Reported
- 120 120 92300 743000 23 Minnesota
- 5 5 130800 309000 46 Virginia
- 57 57 124157 496000 29 New Hampshire
- 208 211 64412 525000 9 Florida
- 174 175 70611 674000 15 Iowa
- 342 365 52695 759000 3 Arizona
- 2 2 379500 592000 52 District of Columbia
- 40 40 110550 665000 32 New York
- 77 77 99961 530000 26 Montana
- 222 224 68013 437000 12 Idaho
- 29 29 80931 243000 35 Ohio
- 277 285 55961 406000 6 Colorado
- 6 6 24500 40000 43 Texas
- 3 3 19000 24000 49 Wisconsin
- 125 126 72682 333000 21 Massachusetts
- 62 64 108750 476000 27 Nebraska
- 22 24 144041 489000 38 Pennsylvania
- 249 255 61725 368000 7 Connecticut
- 7 8 98250 419000 44 Utah
- 371 395 50630 359000 1 Alabama
- 2 3 13000 19000 50 Wyoming
- 86 87 90896 320000 24 Mississippi
- 6 6 122500 463000 47 Washington
- 137 137 85459 788000 18 Louisiana
- 51 51 123039 613000 30 New Jersey
- 248 257 70571 873000 10 Georgia
- 13 13 54461 238000 41 South Dakota
- 310 324 57660 707000 4 Arkansas
- 125 128 76218 441000 19 Maine
- 69 69 119376 884000 25 Missouri
- 15 16 85000 252000 36 Oklahoma
- 202 207 75777 1070000 13 Illinois
- 9 10 81500 467000 42 Tennessee
- 329 340 61735 718000 5 California
- 110 110 106790 706000 22 Michigan
- 36 37 105972 481000 33 North Carolina
- 14 15 93400 345000 39 Rhode Island
- 133 135 64792 297000 16 Kansas
- 4 4 136000 434000 45 Vermont
- 379 392 48132 362000 2 Alaska
- 3 3 79333 126000 48 West Virginia
- 164 166 90469 837000 17 Kentucky
- 50 53 139339 568000 31 New Mexico
- 43 43 107883 382000 34 North Dakota
- 19 19 85368 462000 40 South Carolina
- 207 209 60650 426000 11 Hawaii
- 113 115 86782 374000 20 Maryland
- 44 45 119888 504000 28 Nevada
- 181 183 79475 976000 14 Indiana
- 26 27 94703 382000 37 Oregon
- 259 267 63089 529000 8 Delaware
- 3 3 79000 190000 51 American Samoa
- (53 rows affected)
- ```
- Ref: [Stackoverflow answer](https://stackoverflow.com/a/7319527/10834788)
- Now, I will do the same count method for smartphone owners:
- ```
- select count(case when p.own_smartphone = 1 then 1 end) number_of_smartphone_owners, count(case when p.own_4k_tv = 1 then 1 end) number_of_4k_tv_owners, count(*) number_of_people, avg(p.income) average_income, max(p.income) highest_income, p.address_state state_code, a.us_state_terr state_name from person_economic_info p inner join annotated_person_info a on p.address_state = a.numeric_id group by p.address_state, a.us_state_terr
- GO
- ```
- Now, for "Percentage of respondents that are male", I need to apply the maths formula in sql.
- First going with the basic sql query of calculating percentage:
- ```
- select address_state, (count(case when p.gender = 'm' then 1 end) * 100.0 / count(*)) male_percentage, (count(case when p.gender = 'f' then 1 end) * 100.0 / count(*)) female_percentage from person_economic_info p group by p.address_state
- ```
- Now, I will put this in the final query formed:
- ```
- select (count(case when p.gender = 'm' then 1 end) * 100.0 / count(*)) male_percentage, count(case when p.own_smartphone = 1 then 1 end) number_of_smartphone_owners, count(case when p.own_4k_tv = 1 then 1 end) number_of_4k_tv_owners, count(*) number_of_people, avg(p.income) average_income, max(p.income) highest_income, p.address_state state_code, a.us_state_terr state_name from person_economic_info p inner join annotated_person_info a on p.address_state = a.numeric_id group by p.address_state, a.us_state_terr
- GO
- ```
- ```
- 2> GO
- male_percentage number_of_smartphone_owners number_of_4k_tv_owners number_of_people average_income highest_income state_code state_name
- ---------------------------- --------------------------- ---------------------- ---------------- -------------- -------------- ----------- --------------------------------------------------
- 51.208791208791 87 423 455 50934 725000 0 Not Reported
- 52.500000000000 30 120 120 92300 743000 23 Minnesota
- 60.000000000000 1 5 5 130800 309000 46 Virginia
- 52.631578947368 11 57 57 124157 496000 29 New Hampshire
- 46.919431279620 55 208 211 64412 525000 9 Florida
- 54.285714285714 29 174 175 70611 674000 15 Iowa
- 47.397260273972 69 342 365 52695 759000 3 Arizona
- 50.000000000000 0 2 2 379500 592000 52 District of Columbia
- 45.000000000000 6 40 40 110550 665000 32 New York
- 48.051948051948 19 77 77 99961 530000 26 Montana
- 46.875000000000 38 222 224 68013 437000 12 Idaho
- 51.724137931034 5 29 29 80931 243000 35 Ohio
- 50.175438596491 69 277 285 55961 406000 6 Colorado
- 66.666666666666 2 6 6 24500 40000 43 Texas
- 33.333333333333 0 3 3 19000 24000 49 Wisconsin
- 49.206349206349 22 125 126 72682 333000 21 Massachusetts
- 32.812500000000 21 62 64 108750 476000 27 Nebraska
- 50.000000000000 2 22 24 144041 489000 38 Pennsylvania
- 49.411764705882 38 249 255 61725 368000 7 Connecticut
- 50.000000000000 1 7 8 98250 419000 44 Utah
- 46.582278481012 80 371 395 50630 359000 1 Alabama
- 66.666666666666 0 2 3 13000 19000 50 Wyoming
- 54.022988505747 19 86 87 90896 320000 24 Mississippi
- 50.000000000000 1 6 6 122500 463000 47 Washington
- 51.094890510948 34 137 137 85459 788000 18 Louisiana
- 45.098039215686 12 51 51 123039 613000 30 New Jersey
- 52.529182879377 44 248 257 70571 873000 10 Georgia
- 53.846153846153 1 13 13 54461 238000 41 South Dakota
- 53.395061728395 72 310 324 57660 707000 4 Arkansas
- 50.781250000000 32 125 128 76218 441000 19 Maine
- 44.927536231884 13 69 69 119376 884000 25 Missouri
- 56.250000000000 1 15 16 85000 252000 36 Oklahoma
- 57.971014492753 39 202 207 75777 1070000 13 Illinois
- 80.000000000000 3 9 10 81500 467000 42 Tennessee
- 51.470588235294 63 329 340 61735 718000 5 California
- 49.090909090909 22 110 110 106790 706000 22 Michigan
- 56.756756756756 9 36 37 105972 481000 33 North Carolina
- 40.000000000000 4 14 15 93400 345000 39 Rhode Island
- 47.407407407407 34 133 135 64792 297000 16 Kansas
- 75.000000000000 0 4 4 136000 434000 45 Vermont
- 46.683673469387 75 379 392 48132 362000 2 Alaska
- 33.333333333333 2 3 3 79333 126000 48 West Virginia
- 52.409638554216 42 164 166 90469 837000 17 Kentucky
- 54.716981132075 12 50 53 139339 568000 31 New Mexico
- 41.860465116279 8 43 43 107883 382000 34 North Dakota
- 57.894736842105 2 19 19 85368 462000 40 South Carolina
- 49.760765550239 52 207 209 60650 426000 11 Hawaii
- 42.608695652173 29 113 115 86782 374000 20 Maryland
- 66.666666666666 9 44 45 119888 504000 28 Nevada
- 48.087431693989 38 181 183 79475 976000 14 Indiana
- 59.259259259259 6 26 27 94703 382000 37 Oregon
- 50.936329588014 60 259 267 63089 529000 8 Delaware
- 33.333333333333 0 3 3 79000 190000 51 American Samoa
- (53 rows affected)
- ```
- ## For each state AND gender, give the same information (result should have two rows per state)
- Now, in this case, I need to have two groups - state and gender. Although, I will remove the male percentage because it does not make sense now that we have a proper classification:
- ```
- select p.gender gender, count(case when p.own_smartphone = 1 then 1 end) number_of_smartphone_owners, count(case when p.own_4k_tv = 1 then 1 end) number_of_4k_tv_owners, count(*) number_of_people, avg(p.income) average_income, max(p.income) highest_income, p.address_state state_code, a.us_state_terr state_name from person_economic_info p inner join annotated_person_info a on p.address_state = a.numeric_id group by p.address_state, a.us_state_terr, p.gender order by state_code
- ```
- ## Show each state’s rank for i)People responding ii) Number of smartphones iii)Average income
- This will be three different queries.
- i) People responding:
- ```
- select count(*) number_of_people, p.address_state state_code, a.us_state_terr state_name from person_economic_info p inner join annotated_person_info a on p.address_state = a.numeric_id group by p.address_state, a.us_state_terr order by number_of_people desc
- ```
- ii) Number of smartphones:
- ```
- select count(case when p.own_smartphone = 1 then 1 end) number_of_smartphone_owners, p.address_state state_code, a.us_state_terr state_name from person_economic_info p inner join annotated_person_info a on p.address_state = a.numeric_id group by p.address_state, a.us_state_terr order by number_of_smartphone_owners desc
- ```
- iii) Average income
- ```
- select avg(income) average_income, p.address_state state_code, a.us_state_terr state_name from person_economic_info p inner join annotated_person_info a on p.address_state = a.numeric_id group by p.address_state, a.us_state_terr order by average_income desc
- ```
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
