Practice Session3:27 with Andrew Chalkley
Let's review the operators and functions we've used in this section and practice what we've learned.
You've learned an incredible amount of information. 0:00 Let's go through a quick recap and have another practice. 0:03 In addition to the comparison operators you already know, 0:07 you've learned that operators can manipulate values. 0:10 For example, using the concatenate operator 0:14 to add multiple pieces of text together, we'll take a look at some more later. 0:17 You've been introduced to a new concept called functions. 0:23 [SOUND] Functions can perform more complex operations on values. 0:27 [SOUND] In this stage, we've used an assortment of text manipulation functions. 0:31 Functions can be used to the select portion of your query and be aliased. 0:36 They can be used in any of the conditions you write or 0:41 even in the order by sections. 0:44 In this section of the course, we explored functions that perform operations on text 0:47 or strings of characters. 0:53 Firstly is the length function that takes in a column name and 0:55 it returns the length of each value in the results set. 0:59 Upper change the text in any specified column to the uppercase version. 1:03 And the lower did the lowercased versions. 1:09 SUBSTR or substring was used to get smaller sections of text. 1:11 This is the first function we used multiple values to 1:17 get the transformation we wanted. 1:19 We first used the column name, the starting point, and 1:22 then the length of the sub string we want to extract to create the excerpt. 1:26 Finally, we took a look at the replace function. 1:31 This could be used to replace sections of strings with other strings. 1:35 Maybe you have a bad words list that you want to remove from comments or reviews. 1:39 Replace would help you do that. 1:45 To use replace, pass in the text column name you want to apply the function to. 1:48 Then the target string you want to replace, and 1:52 then the value you want to replace the target with. 1:55 All of these values that we've entered into functions 1:58 have a programming term associated with them. 2:01 Arguments are sometimes referred to as parameters. 2:04 You may hear this term to refer to values passed into functions. 2:09 For example, the first parameter is the column name or 2:13 it looks like you've got the arguments the wrong way around. 2:18 Now that we've recapped what we covered, 2:21 let's practice these new found skills in a test database. 2:24 Launch the SQL playground now, familiarize yourself with the schema, and 2:28 then proceed to answer the questions in each of the coding areas. 2:33 Let me show you something that could help you with testing out things before 2:37 running a query. 2:40 If you want to test out a function on a spring value of your choosing, 2:42 you can type SELECT then the function name, 2:46 LENGTH, and then passing the string of your choosing. 2:52 I'm going to use test. 2:57 When you run this statement, you get the number four. 3:00 That's because that's the length of the string test. 3:03 You can do this with all functions and operators. 3:07 It's really handy to try things out on your data that you control 3:11 before running a query that will hit the columns in your database. 3:15 Once you're done practicing with strings, take the final assessment for 3:20 this stage and then we'll start talking numbers. 3:24
You need to sign up for Treehouse in order to download course files.Sign up