Transforming your results

Applying SQL to Real-World Problems

Dmitriy (Dima) Gorenshteyn

Lead Data Scientist, Memorial Sloan Kettering Cancer Center

Transforming strings

UPPER(<column>) & LOWER(<column>)

SELECT city,
       UPPER(city) AS upper_city,
       LOWER(city) AS lower_city
FROM address;

city          lower_city     upper_city
Lethbridge    lethbridge     LETHBRIDGE    
Woodridge     woodridge      WOODRIDGE    
Lethbridge    lethbridge     LETHBRIDGE    
Woodridge     woodridge      WOODRIDGE

Transforming numbers

Operators: add (+), subtract (-), divide (/), multiply(*)

SELECT replacement_cost,
       replacement_cost + 2 AS updated_cost,
       replacement_cost / length AS cost_per_minute
FROM film;

replacement_cost     updated_cost     cost_per_minute
20.99                22.99            0.24406977        
12.99                14.99            0.27062500        
18.99                20.99            0.37980000

Transforming dates

EXTRACT(<part> FROM <date_column>)

SELECT rental_date,
        EXTRACT(YEAR FROM rental_date) AS rental_year,
        EXTRACT(HOUR FROM rental_date) AS rental_hour
FROM rental;

rental_date            rental_year    rental_hour
2005-05-30 23:54:19    2005            23        
2005-05-30 23:55:36    2005            23        
2005-05-31 00:06:02    2005            0

Time to transform!

Applying SQL to Real-World Problems