How to Go from a Beginner to a Pro SQL User | 6 SQL Concepts — Transcript

Full transcript

1. 0:00SQL is one of the most popular and
2. 0:02widely used data tools in the world.
3. 0:04It's the language operating behind the
4. 0:06scenes of every database you interact
5. 0:08with, whether you realize it or not. But
6. 0:11here's the thing. Most people only learn
7. 0:13enough SQL to get by. A select statement
8. 0:16here, a wear clause there, maybe a join
9. 0:18if you're feeling bold enough. But SQL
10. 0:21goes so much deeper than that. Hi, I'm
11. 0:23Matt with Maven Analytics and today I'll
12. 0:25be walking you through six steps to take
13. 0:27you from beginner to advanced SQL
14. 0:29practitioner. We'll be covering
15. 0:31everything from foundational query
16. 0:33structure to more advanced logic. And by
17. 0:36the end of this video, you should have a
18. 0:38clear road map on mastering SQL. For
19. 0:40each section, I'll introduce the
20. 0:42concept, share what you should know, and
21. 0:44explain why it matters.
22. 0:52The first is the big six. Now the big
23. 0:54six are like the bedrock of SQL and
24. 0:57create the foundation for most of your
25. 0:59query structure. And what they are is
26. 1:02select from, where, group by, having,
27. 1:05and order by. They define what data
28. 1:07you're pulling in, from where, how it's
29. 1:10filtered, summarized, and sorted. All of
30. 1:12this in a clean logical sequence. Think
31. 1:15of these as the foundation of all your
32. 1:17SQL query. These are relatively basic,
33. 1:19but understanding them is the jumping
34. 1:21off point for more complex SQL queries.
35. 1:24So, here's what to know. I already
36. 1:25explained these, but let me break them
37. 1:27down. Select tells you what fields to
38. 1:29return. From defines the source tables,
39. 1:32where filters your rows before grouping.
40. 1:35Group by aggregates those rows into
41. 1:38groups. Having filters based on those
42. 1:41groupings. And order by sorts the final
43. 1:44result. And that's pretty much it. And
44. 1:45once you've mastered these basics,
45. 1:47you'll start to hit limits in your
46. 1:48flexibility, which is where conditional
47. 1:51logic like case statements come into
48. 1:53play. All right, so number two, case
49. 1:54statements. Kate are SQL's way of
50. 1:57handling if this then that logic. It
51. 2:00allows you to create custom fields and
52. 2:02categories based on conditions in your
53. 2:04data. All of this done within your
54. 2:06query. You can create new labels and
55. 2:08classifications without having to alter
56. 2:11your original tables. All right, so what
57. 2:12to know? Well, you definitely want to
58. 2:14know the basic syntax which goes like
59. 2:16this. Case when condition then result
60. 2:21else your fallback end. And because it's
61. 2:24if this then that logic, this applies to
62. 2:27tools like Excel if you're more
63. 2:28comfortable with that as well, like an
64. 2:30if statement. Very similar logic but in
65. 2:33SQL. You'll also want to know how to
66. 2:34perform aggregations within your case
67. 2:36statements like sum case when or count
68. 2:39case when and also nesting case
69. 2:41statements for more complex logic. And
70. 2:43here's why case statements matter. They
71. 2:45allow you to create nuance and greater
72. 2:47intelligence into your queries. Using
73. 2:49case statements inside of aggregate
74. 2:51functions allow you to break down
75. 2:52metrics by categories that may not exist
76. 2:55in your source data. And you can also
77. 2:57replicate logic that might otherwise
78. 2:59require multiple queries. Once you get
79. 3:01the hang of case statements, your next
80. 3:03step is to learn how to combine data
81. 3:05from different tables, which brings us
82. 3:07to joins. All right, number three,
83. 3:09multi-table analysis, aka joins. So, in
84. 3:13most real world data sets, information
85. 3:15is spread across multiple tables. Joins
86. 3:18are what allow us to bring those
87. 3:19different pieces together into one
88. 3:21query. They allow us to combine rows
89. 3:23across tables based on a common field.
90. 3:26So, here are the different types of
91. 3:27joins you should know. There's inner
92. 3:30join, which only returns matching
93. 3:32records from both tables. This is
94. 3:34probably one of the most common forms of
95. 3:35join. Left join, which returns all
96. 3:38records from the left table and only
97. 3:40matching records from the right table. A
98. 3:43right join, which is the opposite,
99. 3:44returns all records from the right table
100. 3:46and only matching records from the left
101. 3:48table. And full outer join, which brings
102. 3:51in all records from both tables. Note
103. 3:53that you can also append or stack tables
104. 3:55vertically using union or union all. All
105. 3:58right. All right. So, why do joins
106. 3:59matter? Without joins, you're limited to
107. 4:02flat, isolated tables, and this rarely
108. 4:05reflects how data is actually stored.
109. 4:07With joins, you can conduct a more
110. 4:09comprehensive analysis. And
111. 4:10understanding the different types of
112. 4:11joins help you control which rows appear
113. 4:14and how they're matched. Next, let's
114. 4:16break down complex logic into more
115. 4:18manageable steps using subqueries and
116. 4:21CTE. So subqueries in CTE or common
117. 4:25table expressions let you write queries
118. 4:27inside of queries. So it's like writing
119. 4:29your SQL in layers. Sometimes queries
120. 4:32can get pretty long. So this definitely
121. 4:35helps you to stay organized. So instead
122. 4:37of trying to cram all your logic into
123. 4:39one massive statement, you modularize
124. 4:42your SQL query into parts to make it
125. 4:44more readable. So a subquery is a query
126. 4:47nested inside of another query. They're
127. 4:49typically used in the select from or
128. 4:52wear clause and create logic that your
129. 4:54outer query then depends on. A CTE is
130. 4:58like a temporary table you're creating
131. 5:01using a width statement that you can
132. 5:05reference throughout your query. So
133. 5:06again, it's like a table you're creating
134. 5:09within your query that your outer query
135. 5:12can reference. Some of the key
136. 5:13differences are that subqueries exist
137. 5:16within your main query whereas a CTE
138. 5:19will precede your main query so that it
139. 5:22can be referenced. So again, here's why
140. 5:25these matter. So both of them help you
141. 5:27break down complex logic into multi-step
142. 5:31digestible parts and overall they make
143. 5:34your SQL easier to read and debug. So
144. 5:37CTE are especially helpful when you work
145. 5:39within a team that needs to view your
146. 5:41queries. But it's also just helpful for
147. 5:43your own sanity because again queries
148. 5:45can get quite large. All right, so now
149. 5:47let's talk about one of SQL's most
150. 5:48powerful functions and that is window
151. 5:50functions. So window functions allow you
152. 5:53to perform calculations across rows but
153. 5:56unlike grouping they help you keep your
154. 5:58detailed records intact. In other words,
155. 6:01you get the power of aggregation but
156. 6:03without collapsing your data set. Some
157. 6:05common functions to know are row number,
158. 6:08rank and dense rank. Use partition by to
159. 6:11restart calculations per group and use
160. 6:14order by within the over clause to
161. 6:16define row order. Window functions are
162. 6:18really helpful because they allow you to
163. 6:20perform things like uh percent changes,
164. 6:22running totals, rankings, and honestly
165. 6:26so much more. And all of this while
166. 6:28keeping every row of data visible. And
167. 6:30there are essential functions for
168. 6:31advanced trend analysis and report
169. 6:34building. So some pretty great use cases
170. 6:36for this are when you need to compare
171. 6:38rows to rows around it like running
172. 6:41totals or top performers something like
173. 6:43that. And all right so window functions
174. 6:45are powerful but they often rely on
175. 6:47clean structured data. So finally let's
176. 6:50explore functions that can help you
177. 6:52format your data. Data type functions.
178. 6:54Essentially data type functions help you
179. 6:56to clean and prep your data. You can do
180. 6:58things like manage strings, numbers,
181. 7:00dates, nles and data type conversion.
182. 7:03They help you to transform your data.
183. 7:04They help you fix formatting issues,
184. 7:06standardize inputs, and handle missing
185. 7:08data. And here are some functions that
186. 7:10you should know. Numeric functions,
187. 7:12which include math functions like log or
188. 7:14power. And rounding functions like round
189. 7:17and floor. Type conversion like cast and
190. 7:20convert. Date time functions like
191. 7:22current date, day of week or date diff.
192. 7:24Spring string functions like length,
193. 7:26trim, upper, lower, concat, and like.
194. 7:29and null handling like ifnull and
195. 7:32coalesce. So it should be obvious but
196. 7:34these functions matter because they help
197. 7:36you to clean messy data and data is
198. 7:38often messy in the real world. Really
199. 7:40you're just preparing your data set by
200. 7:42reducing errors and inconsistencies.
201. 7:45Also, if you'd like to learn all these
202. 7:46functions in a practical manner, then
203. 7:48I'd highly suggest checking out one of
204. 7:50Maven's 10E immersive cohort programs.
205. 7:53In the cohort, you'll be guided by a
206. 7:55live team of expert instructors to learn
207. 7:58SQL from beginner to advanced, learning
208. 8:01these exact SQL skills. And each
209. 8:03immersive we do is skill specific. So we
210. 8:06have one for Excel, PowerBI, Python, and
211. 8:08of course SQL. We run each skill
212. 8:11specific cohort twice a year, and our
213. 8:13next SQL group is starting very soon.
214. 8:15I'm also the one that facilitates every
215. 8:17cohort, so you know you're getting a
216. 8:19familiar face if you sign up. Check the
217. 8:21link in the description for more info.
218. 8:23And there you go. Six steps to help take
219. 8:25you from beginner to advanced SQL. If
220. 8:28you take the time to learn and practice
221. 8:29each of these, then you'll be well on
222. 8:31your way to becoming a confident,
223. 8:33capable data analyst that uses SQL. And
224. 8:36more importantly, you'll have the tools
225. 8:38available to answer questions about your
226. 8:40data. If this video was helpful, be sure
227. 8:42to like and subscribe, and I'll see you
228. 8:44in the next one.
229. 8:47[Music]