Lesson/practice with using an SQL database with node
- Setup SQL for Node
- SQL in the Cloud: Data.World
- SQL Statements
- SQL Practice
- Business Licenses Dataset
- Land Bank Inventory Dataset
- Alternative: Querying the Detroit Open Data API directly
Tutorial: http://www.sqlitetutorial.net/sqlite-nodejs/
Create table: http://www.sqlitetutorial.net/sqlite-create-table/
Insert rows: http://www.sqlitetutorial.net/sqlite-nodejs/insert/
http://www.sqlitetutorial.net/sqlite-nodejs/query/
https://data.world/alaurenzi/shiftup/workspace
Basic READ Statements
- SELECT
- WHERE
- ORDER BY
- LIMIT
More Advanced READ Statements:
- COUNT
- GROUP BY
- LIKE
- JOIN
Data Modification Statements:
- INSERT
- UPDATE
- DELETE
Questions to answer by writing SQL:
- What were the 5 most recent business licenses obtained?
- How many business permits were opened with
- What type of business category is most common overall?
- What type of business category is most common over the past year? Month?
Map visualization: https://cityofdetroit.github.io/demo-tracker/
Questions to answer by writing SQL:
-
Write a statement to see if there are any properties within your neighborhood?
-
Here is a statement to get the a list of all neighborhoods:
- How many exist in your neighborhood in total? Within each "inventory status type"
- Or:
- How many land bank properties exist in the the "North End" neighborhood?
- How many exist within each "inventory status type" within the "North End"?
Dataset/API Docs: https://dev.socrata.com/foundry/data.detroitmi.gov/mhw8-ppmt
API docs: https://dev.socrata.com/docs/queries/