Transforming your results
1. Transforming your results
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.2. Transforming strings
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.3. Transforming numbers
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.4. Transforming dates
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 column.5. Time to transform!
Now it's your turn to practice transforming your columns.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.