SQL code copied to buffer

SQLtest.online
Task 12:
Calculate the population density for each neighborhood in New York City. Include to the output the columns name and popn_per_sqkm, and sort the results in descending order by population density.

Write your request in the field below and click the "Check it!" button.

Use PostGIS (PostgreSQL) syntax to write your answer. Descriptions of tables are provided at the bottom of the screen.

Get hint Copy code Clear editor

Countries Database: table structure and geospatial overview

The Countries database (PostGIS) is a sample dataset for geographic and geospatial analysis with SQL.

It includes spatial data for countries and capitals, plus New York City layers such as census blocks, homicides, neighborhoods, streets, and subway stations.

The Countries database contains 7 main tables.

List of tables

countries - list of countries with geometry.
  • idunique record identifier (PK)
  • namecountry name
  • bordercountry geometry (MultiPolygon, SRID 4326)
id name border
1 France MultiPolygon(...) [SRID=4326]
  • PRIMARY KEY, btree (id)
capitals - list of capitals with location.
  • idunique record identifier (PK)
  • namecapital name
  • country_idreference to country (FK)
  • locationcapital location (Point, SRID 4326)
id name country_id location
1 Paris 1 Point(...) [SRID=4326]
  • PRIMARY KEY, btree (id)
  • FOREIGN KEY (country_id) REFERENCES countries(id)
nyc_census_blocks - New York City census blocks with demographic data.
  • gidunique record identifier (PK)
  • blkidcensus block ID
  • popn_totaltotal population
  • popn_whitewhite population
  • popn_blackblack population
  • popn_nativnative population
  • popn_asianasian population
  • popn_otherother population
  • boronameborough name
  • geomcensus block geometry (MultiPolygon, SRID 4326)
gid blkid popn_total popn_white popn_black popn_nativ popn_asian popn_other boroname geom
1 360050001001000 1000 500 200 50 150 100 Manhattan MultiPolygon(...) [SRID=4326]
  • PRIMARY KEY, btree (gid)
nyc_homicides - New York City homicide incidents.
  • gidunique record identifier (PK)
  • incident_dincident date
  • boronameborough name
  • num_victimnumber of victims
  • primary_moprimary motive
  • idincident ID
  • weaponweapon used
  • light_darklight or dark condition
  • yearyear of incident
  • geomincident location (Point, SRID 4326)
gid incident_d boroname num_victim primary_mo id weapon light_dark year geom
1 2003-01-01 Manhattan 1 Unknown 1 Firearm D 2003 Point(...) [SRID=4326]
  • PRIMARY KEY, btree (gid)
nyc_neighborhoods - New York City neighborhoods.
  • gidunique record identifier (PK)
  • boronameborough name
  • nameneighborhood name
  • geomneighborhood geometry (MultiPolygon, SRID 4326)
gid boroname name geom
1 Manhattan Financial District MultiPolygon(...) [SRID=4326]
  • PRIMARY KEY, btree (gid)
nyc_streets - New York City streets.
  • gidunique record identifier (PK)
  • idstreet ID
  • namestreet name
  • onewayone-way indicator
  • typestreet type
  • geomstreet geometry (LineString, SRID 4326)
gid id name oneway type geom
1 1 Broadway NO avenue LineString(...) [SRID=4326]
  • PRIMARY KEY, btree (gid)
nyc_subway_stations - New York City subway stations.
  • gidunique record identifier (PK)
  • objectidobject ID
  • idstation ID
  • namestation name
  • alt_namealternative name
  • cross_stcross street
  • long_namelong name
  • labellabel
  • boroughborough
  • nghbhdneighborhood
  • routesroutes
  • transferstransfers
  • colorcolor
  • expressexpress indicator
  • closedclosed indicator
  • geomstation location (Point, SRID 4326)
gid objectid id name alt_name cross_st long_name label borough nghbhd routes transfers color express closed geom
1 1 1 Times Square Times Sq 7th Ave Times Square-42nd Street Times Sq Manhattan Midtown 1,2,3,7,A,C,E,N,Q,R,S,W 42nd St Red Yes No Point(...) [SRID=4326]
  • PRIMARY KEY, btree (gid)
SQL Essentials for Data Analysis: A 50-Day Hands-on Challenge Book by Benjamin Bennett Alexander
SQL Essentials for Data Analysis: A 50-Day Hands-on Challenge Book by Benjamin Bennett Alexander
If you’re wondering whether you should learn SQL, the answer is simple: absolutely. SQL (Structured Query Language) is the language of data, and data powers every modern business. Whether you’re a data analyst, data scientist, business analyst, software engineer, financial analyst, product manager, or marketing professional, SQL helps you retrieve, analyze, and understand the information that drives decisions.