Basic SQL

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

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

Adults Only :: SQL Challenge

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

Raise to the Power :: SQL Challenge

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

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

Calculating Batting Average :: SQL Challenge

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, Haskell (and some other languages) accept the same “trick”. One value being fractional makes the entire expression fractional:

Ruby IRB repl session
$ pry --simple-prompt
>> 10 / 3
=> 3

>> 10.0 / 3
=> 3.3333333333333335
Haskell GHCI repl session
ghci> :type 1
1 :: Num p => p
ghci> :type 1.1
1.1 :: Fractional p => p
ghci> :type (1 + 1.1)
(1 + 1.1) :: Fractional a => a

SQL with Pokemon:: Damage Multipliers :: SQL Challenge

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;