SQL Tutorial: Transforming your results

Want to learn more? Take the full course at https://learn.datacamp.com/courses/applying-sql-to-real-world-problems at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work. --- In the last lesson you reviewed some of the most essential SQL commands for returning data. This lesson will cover several commands for transforming the columns returned from these queries. Let's begin with string transformations. Two commonly used functions are UPPER and LOWER. These functions convert values of a string column into upper case or lower case, respectively. This example illustrates how to use these functions. Here the city column from the address table is converted to both upper and lower case and is aliased appropriately as can be seen in the resulting output. The output of numeric columns can also be transformed. Since these columns contain numbers, you can simply use standard operators such as addition, subtraction, division and multiplication. In this example we transform the numeric column replacement_cost from the film table. In the first transformation, a new column named updated_cost is created by adding two to the replacement_cost column. We can also use operators with multiple columns; for the second transformation the cost_per_minute column is calculated by dividing replacement_cost by length. Date and time columns can also be transformed. A common use case when working with dates and times is to extract date-time parts from these columns. In postgres and MySQL databases you can use the EXTRACT function to do exactly that. When using the EXTRACT function you need to input a command which uses the following syntax: first, you need to provide the date or time part you wish to extract this is followed by the FROM command and, finally, the column from which you you would like to extract the part from. In the example here we use EXTRACT to return the year and the hour from the rental_date co