Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Reporting with SQL

Well done!

You have completed Reporting with SQL!

Sign up for Treehouse Back to Library
Preview
Video Player
00:00
00:00
00:00
  1. 2x 2x
  2. 1.75x 1.75x
  3. 1.5x 1.5x
  4. 1.25x 1.25x
  5. 1.1x 1.1x
  6. 1x 1x
  7. 0.75x 0.75x
  8. 0.5x 0.5x
Use Up/Down Arrow keys to increase or decrease volume.

Changing the Case of Text Columns

2:45 with Andrew Chalkley

There are two functions for changing cases, UPPER() and LOWER(). Let's look how to use them.

There are number of reasons you want to change the case of the string. 0:00
For example, make all the text in a column uppercase or all the text lowercase. 0:04
The two most common uses are to standardize output and 0:10
when searching for values. 0:14
There are two functions for changing cases. 0:17
Upper for uppercasing values or columns. 0:19
Lower is used for making lower case strings. 0:23
Let's see them in actions. 0:27
Let's say we wanted to bring up a customer's records based on their 0:29
email address. 0:33
Let's run this query. 0:34
Notice how the email address has a capital A. 0:38
That's because like in SQL playground, it's case insensitive. 0:42
But it's not the case in other SQL databases. 0:47
What if we didn't know what SQL database we're using? 0:51
Is there another way we can rewrite this query with a function? 0:54
Let's remove the like keyword and switch it out for 0:59
an equality operator then we can use the lower function 1:02
to transform the values in the email column to lowercase values. 1:07
We've standardized the value we're testing. 1:12
When we run this query in any SQL environment, 1:15
we'll expect to see the same result. 1:19
Searching the email column with case insensitivity. 1:21
See that the contents of the email column is still preserved. 1:27
We're only testing the result of lower email. 1:31
If we wanted to standardize the output of email addresses, 1:36
we could use lower email in this select portion of the query. 1:40
You may require to uppercase what a user enters into a form that's been stored in 1:51
a database. 1:56
In our e-commerce example, postal codes in the UK, 1:57
the UK equivalent of the US zip code system are in uppercase. 2:02
Let's modify this query to search our addresses table. 2:08
We're listing all the UK postcodes. 2:12
We want to plot them on a map to see if we can see 2:14
where all of our customers are in the UK to see if there's any hotspots. 2:17
The developer who's mapping the postal codes on the map says that they require 2:22
all post codes all to be in upper case. 2:27
We can do that with the upper function. 2:30
When we run this query now, we can pass off this report to the developer plotting. 2:39