Richard Cotton teaches SQL column transformations: string functions like upper and lower, numeric operations across columns, and extracting date and time parts using the extract function in Postgres and MySQL.
Chapters
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 come convert values of a string column into uppercase or lowercase, 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 the 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 datetime 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 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. Now.
