SQL code copied to buffer

SQLtest.online
Task 48: 
Find the station name - name and metro line - routes for all stations located within the 'Little Italy' district.

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 (PostGIS)

The Countries database is a PostGIS sample database designed for geographic and geospatial data analysis. It includes spatial information about countries, capitals, and New York City data such as census blocks, homicides, neighborhoods, streets, and subway stations, suitable for GIS applications and spatial queries.

Table List:

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)
Learning SQL: Generate, Manipulate, and Retrieve Data by Alan Beaulieu
Learning SQL: Generate, Manipulate, and Retrieve Data by Alan Beaulieu
As data floods into your company, you need to put it to work right away―and SQL is the best tool for the job. With the latest edition of this introductory guide, author Alan Beaulieu helps developers get up to speed with SQL fundamentals for writing database applications, performing administrative tasks, and generating reports. You’ll find new chapters on SQL and big data, analytic functions, and working with very large databases.