Review & Practice with SQL Playgrounds3:50 with Andrew Chalkley
In this video we'll review some of the concepts that we've covered in this stage and cover what you'll need to do to in your practice session.
You've learned a lot over the last section of videos. 0:00 You've learned how to filter and 0:03 search through results in a number of different ways. 0:05 You've learned the general pattern of filtering using the WHERE keyword followed 0:08 by a condition. 0:11 Actually, you've learned that you can use multiple conditions using the AND 0:13 keyword and the OR keyword. 0:17 The AND keyword is used to compare all conditions for a particular row. 0:19 All is used if you wanna test if either of the conditions are true. 0:24 Conditions come in a few other forms too. 0:28 One common condition is using an operator to compare values. 0:31 [SOUND]. 0:34 [SOUND] Equality, inequality, less than, and greater than, to name a few. 0:35 [SOUND] Then we have the IN keyword followed by the values separated by 0:40 commas wrapped in parentheses. 0:45 This can check if the value in the column matches any one of the values in 0:48 the column we specify [SOUND]. 0:52 Next, we use BETWEEN, which allows us to search for ranges of values, 0:54 numeric and date. 0:58 [SOUND]. 0:59 We use the the light keyword with combinations [SOUND] of search patterns 1:03 and word cards to search for values. 1:07 [SOUND]. 1:09 Finally, we discovered that we can represent missing values [SOUND] in sequel 1:10 with null. 1:14 We learned that we can't use regular operators with null. 1:15 You have to use the IS or IS NOT keywords in conjunction with NULL. 1:18 That's a lot of stuff, and you feel proud of yourself to get this far. 1:24 Remember, how I said the data in databases are related to each other? 1:28 Well, you can query two tables at once and bring back information from both. 1:32 Let me show you a general case and then we'll create a real life example. 1:37 To select from two tables, you can write a comma after the first table name 1:42 followed by your second [SOUND] table name. 1:46 [SOUND] Then you can set up the relationship between the two tables in 1:49 the where clause. 1:53 You can use the first table's name followed by a period, then the column that 1:54 stores the value that matches the second table's column information. 1:58 Let's wire up the loans table with the books table. 2:04 Let's take a look at the database schema for both of the tables. 2:07 As you can see, the loans table has a book_id, 2:12 which corresponds to the book's table, id column. 2:15 So, using our general example above. 2:20 Let's plug in the loans table for our first table name, and books as our second. 2:28 Finally, the column names. 2:45 The loans table has the book_id. 2:47 And the books table has just id. 2:52 And there it is, data from both tables are returned. 3:01 The data is related, 3:05 this is just a teaser of the type of queries we'll be using in future courses. 3:07 Now it's time for another practice session. 3:13 Open the sequel playground with this video, and take a look 3:16 at the database schemer to refamiliarize yourself with the movie database. 3:19 We have the actors, movies, and reviews table. 3:24 I've included several questions about the data in the database 3:28 to help you practice what you've learned through this stage. 3:32 Once your happy, you can move on to your final assessment, 3:36 remember if you ever feel rusty you can come back here and practice some more. 3:39 Once again, you've done some awesome work, be sure to keep using these skills and 3:45 practice. 3:49
You need to sign up for Treehouse in order to download course files.Sign up