The Solutions3:39 with Andrew Chalkley
Here are some examples of ways to solve the challenges.
How did you do? Don't worry if you struggled or 0:00 failed to complete any of the tasks. 0:03 Here are my solutions. 0:05 Compare them with your SQL statements. 0:06 It doesn't matter if they're slightly different. 0:09 Often there are number of ways to do same thing in SQL. 0:10 Challenge one was to get the sales from April the 20th to create mailing labels. 0:14 I selected the customer name, delivery address from the sales table. 0:19 In the where clause I used the equality operator. 0:23 Challenge two was to select all Playstation 4 models. 0:27 I asked you to think of three possible ways you could do it, and 0:32 think about which way would be the best. 0:35 You can start with the equality operator, with the OR keyword, 0:37 comparing the product's name with the text Playstation 4 and Playstation 4 Pro. 0:44 An alternative version would be to use the IN keyword with these set of values. 0:52 Using the IN keyword avoids a lot of repetition. 0:58 Finally I could use the LIKE keyword with a wild card. 1:02 I included the wild card after the 4 because that would account for 1:06 any Playstation 4 models in the future. 1:10 Remember LIKE is not case sensitive. 1:13 So this would get any Playstation even if the capitalization of the P and 1:16 S were different. 1:21 LIKE would be the best way to run this function since it's flexible and 1:23 can get any future models, or bundles, that we decide the stock. 1:28 Challenge three was to get the mailing addresses of customers 1:32 without an email address. 1:36 I use the IS key word with the NULL value. 1:38 Remember that you can't use the equality operator with the NULL value. 1:42 Challenge four was to get the email addresses for 1:52 customers with email addresses present. 1:54 This time I combined the IS keyword with the NOT keyword. 1:57 Now marketing can email all customers with an email address. 2:03 Challenge five was to check what products were in surplus in our warehouse. 2:07 I selected the name of the product, and in the WHERE clause, 2:11 I used units in stock greater than 250. 2:16 If i use greater than or equal to it would have 2:23 been included products with 250 in stock. 2:28 Challenge six was to search all orders between 225 and 245 dollars. 2:34 I did this with the keyword BETWEEN and the AND keyword. 2:43 The between keyword includes the minimum and maximum values. 2:47 You could have also done this query with the greater than or 2:54 equal to operator and the less than or equal to operator. 2:58 The final challenge was to find all products with 4k support. 3:02 How I did that was using the like keyword and the short description 3:07 with two wildcards, one before the 4k and one after. 3:13 I hope you were able to complete this practice session successfully and 3:20 create similar reports. 3:24 If not, why not reset each of the coding areas in the SQL playground and 3:26 start again without looking at my solutions. 3:30 It's okay if you need to practice a few more times to memorize the SQL syntax. 3:32 Keep up the good work and I'll see you again. 3:37
You need to sign up for Treehouse in order to download course files.Sign up