Basic SQL#

NOTE: Assume these examples are run on PostgreSQL >= 13 at least. As of 2022, I’m running these on Arch Linux with PostgreSQL 14.

Sometimes I do \x to turn on extended display in psql prompt.

Adults Only#

SELECT
    name
  , age
FROM users
WHERE age >= 18;

Raise to the Power#

SELECT
  POWER(number1, number2) AS result
FROM decimals;

Looks like we can also use POW(b, e).

Calculating Batting Average#

SELECT
    player_name
  , games
  , ROUND(
      hits::NUMERIC / at_bats::NUMERIC),
      3
    )::VARCHAR(8) AS batting_average
FROM yankees
WHERE at_bats >= 100
ORDER BY batting_average DESC;

We can use <value>::<type> to cast from one type to another. For example, 1::VARCHAR(4) turns the

ROUND():

SELECT ROUND(1::NUMERIC, 2) AS num;
num | 1.00

SELECT ROUND(1.435::NUMERIC, 2) AS num;
num | 1.44

SELECT ROUND(1.7545::NUMERIC, 3) AS num;
num | 1.755

Division:

SELECT (10 / 3) as num;
num | 3

SELECT (10::NUMERIC / 3::NUMERIC) as num;
num | 3.3333333333333333

10 / 3 does INTEGER division. To make it do fractional division, we must make the expression decimal/fractional somehow. Technically, one value being NUMERIC is enough to make the whole expression NUMERIC (instead of doing integer division).

As a side note, Ruby (and some other languages) accept the same “trick”. One value being fractional makes the entire expression fractional:

$ pry --simple-prompt
>> 10 / 3
=> 3

>> 10.0 / 3
=> 3.3333333333333335

SQL with Pokemon: Damage Multipliers#

SELECT
    pokemon_name
  , element
  , (str * multiplier) as modifiedStrength
FROM pokemon
JOIN multipliers
ON element_id = multipliers.id
WHERE str * multiplier >= 40
ORDER BY modifiedStrength DESC;