SQL Query Examples

Example queries and API calls to try.

Suggest Edits

🚧

Top SQL Tip

One of the top SQL syntax "gotchas" we help our users with is quoting within queries. In Postgres, identifiers (like column and table names) may sometimes be enclosed in double-quotes ("column name") while string literals must be enclosed in single-quotes ('Hello World').

For example, If we have a text data column named print_statements in a table named logs and wanted to SELECT all records where the value is hello world, we could write a query like this:

SELECT * FROM logs WHERE print_statements = 'hello world'

Here, the double-quotes are not needed. However, when you are referencing an identifier with upper-case letters, spaces, or special characters (except for underscore), you do need them! It doesn't hurt to include them if it makes querying more clear for you.

For example:
SELECT * FROM "username/repo_name"."Logs Are Cool" WHERE "print statements" = 'hello world'

Here, because the table Logs Are Cool has spaces and uppercase characters, it must be quoted. Similarly for the column named print statements.

In both cases, the single-quotes are required. If omitted, you will get an error saying that the identifier hello was not found, because Postgres will assume the unquoted text up to the space is an identifier.

If you are getting errors when running a query, it's always a good idea to double-check your quotes. You can read more about this in SQL Identifiers.

Example Queries

Join queries

visit

SELECT * FROM "bitdotio/big_mac_index".big_mac_index 
INNER JOIN "bitdotio/economic_freedom_index".world_index 
ON iso_code = iso_a3;

bit -k <your key> query -q 'SELECT * FROM "bitdotio/big_mac_index".big_mac_index 
INNER JOIN "bitdotio/economic_freedom_index".world_index 
ON iso_code = iso_a3;'

API_KEY=<your-api-key>
curl --request POST \
  --url https://api.bit.io/api/v1beta/query/ \
  --header 'Accept: application/json' \
  --header "Authorization: Bearer ${API_KEY}" \
  --header 'Content-Type: application/json' \
  --data '{"query_string": "SELECT * FROM \"bitdotio/big_mac_index\".big_mac_index 
INNER JOIN \"bitdotio/economic_freedom_index\".world_index 
ON iso_code = iso_a3;"}' \ | jq

Compare across countries the number of available hospital beds with the percentage of GDP spent on pharmeceuticals in the year 2012.

SELECT 
 country_name, 
 "indicator_name", 
 n2012, 
 beds_join.indicator, 
 beds_join.value 
FROM 
 "bitdotio/world_bank"."beds" 
INNER JOIN 
 (SELECT "time", location, indicator, value 
  FROM "bitdotio/pharmaceutical_spending"."percent_gdp" 
  WHERE "time"=2012) 
 AS beds_join 
ON country_code=location;

bit -k <your key> query -q 'SELECT 
 country_name, 
 "indicator_name", 
 n2012, 
 beds_join.indicator, 
 beds_join.value 
FROM 
 "bitdotio/world_bank"."beds" 
INNER JOIN 
 (SELECT "time", location, indicator, value 
  FROM "bitdotio/pharmaceutical_spending"."percent_gdp" 
  WHERE "time"=2012) 
 AS beds_join 
ON country_code=location;'

API_KEY=<your-api-key>
curl --request POST \
  --url https://api.bit.io/api/v1beta/query/ \
  --header 'Accept: application/json' \
  --header "Authorization: Bearer ${API_KEY}" \
  --header 'Content-Type: application/json' \
  --data '{"query_string":
"SELECT 
 country_name, 
 \"indicator_name\", 
 n2012, 
 beds_join.indicator, 
 beds_join.value 
FROM 
 \"bitdotio/world_bank\".\"beds\" 
INNER JOIN 
 (SELECT \"time\", location, indicator, value 
  FROM \"bitdotio/pharmaceutical_spending\".\"percent_gdp\" 
  WHERE \"time\"=2012) 
 AS beds_join 
ON country_code=location;
"}' \ | jq

Updated 10 months ago