- 7 hours ago
Build your SQL skills from the basics to more advanced techniques in this practical, example-led webinar with Alex the Analyst. Learn how databases and tables work, then explore writing queries with SELECT, filtering with WHERE, sorting with ORDER BY, and summarizing data using GROUP BY and aggregate functions.
The session also introduces joins and subqueries for working across tables, window functions for ranking and calculations, and query optimization topics such as indexing and EXPLAIN. Whether you’re learning SQL for data analytics, preparing for a data analyst interview, or strengthening skills for work with larger datasets, this beginner-to-advanced SQL tutorial offers a clear overview of concepts used in real-world analysis.
The session also introduces joins and subqueries for working across tables, window functions for ranking and calculations, and query optimization topics such as indexing and EXPLAIN. Whether you’re learning SQL for data analytics, preparing for a data analyst interview, or strengthening skills for work with larger datasets, this beginner-to-advanced SQL tutorial offers a clear overview of concepts used in real-world analysis.
Category
📚
LearningTranscript
00:00:00William, events and communications lead here at CareerFoundry and tonight is all about SQL or
00:00:05SQL. It's got two ways you can pronounce it, SQL or SQL. Both ways go and we're joined once again
00:00:11by Alex Freeberg who is the host of Alex the Analyst YouTube channel and I know for a fact
00:00:17that there's a lot of Alex fans out there tonight also because this is being streamed on Alex's
00:00:23YouTube channel. So welcome back to the channel all of Alex's fans. We love your energy, we love
00:00:29to see your questions. Also we love your enthusiasm for data cleaning. This is one thing that I've seen
00:00:34with Alex's fans very much into data cleaning. But yes tonight's all about SQL from beginner to
00:00:40advanced in one hour and whilst people are joining and if you're joining on Big Marker just drop your
00:00:45name, you know, why you're interested in data analytics and where you're joining from because
00:00:50we usually get a super international crowd and it's great to see where everyone is joining from.
00:00:54Before we kick off let me just briefly briefly briefly explain what CareerFoundry is, who we
00:01:00are. So CareerFoundry is the online school for your career change into tech and we guide you from
00:01:04complete beginner to job ready professional in data analytics and help you land your first job in the
00:01:11field. We're not any old school, our programs are so flexible that you don't need to quit your day job
00:01:15to change careers and you get regular one-to-one mentorship from not one but two industry professionals.
00:01:21So that's a mentor and a tutor. That's our dual mentorship model and if you don't land a job within
00:01:26180 days of graduation we refund your tuition in full. So that's our job guarantee. If you've got any
00:01:33specific questions about CareerFoundry or the curriculum or anything that we offer our data
00:01:37analytics program, I do recommend booking a call with a program advisor. So if you're joining us on
00:01:41Big Marker you can just click the sticky note and if you're watching Alex's live stream there is a
00:01:47book a call link in the description below. I don't want to take away the thunder from Alex though.
00:01:52Alex, I'm passing it over to you. I am going to disappear into the background but save all your
00:01:56questions at the end because we will be having a live Q&A which I'm very much looking forward to.
00:02:00So get your questions in on YouTube, Big Marker and LinkedIn and we will get those answered. And Alex,
00:02:05it's over to you. I'm disappearing into the ether.
00:02:09All right. Thank you so much. I really appreciate it. Hey everybody. I'm super excited to do this
00:02:15webinar specifically because if you know me, I am a huge, huge, huge SQL fan. I would consider it my
00:02:22bread and butter, my go-to. It's what I think I am one of the, in my tech stack at
00:02:28least for personally,
00:02:29I think SQL is like one of my best skills. And so I'm hopefully going to kind of drive this
00:02:36from very beginner, really the basics all the way up to some of the more advanced things that I use.
00:02:41We may not go crazy in depth. We're not going to do doing live demos or stuff like that. Although
00:02:46that sounds really fun. But what we will be doing is we'll be doing a lot of examples. So I'll
00:02:51kind
00:02:51of walk through why you need to know this. And as it gets more advanced, if you're kind of a
00:02:57beginner
00:02:57SQL user, you may never have seen some of these things and that's okay. Or you may not even have
00:03:02known that SQL does these things, but I promise you it does. And I'll kind of walk through how it
00:03:08works and why you might want to learn it so that, you know, you can progress your learning. And a
00:03:14lot
00:03:14of these things that were, that are in here are hopefully things that you recognize. So we're
00:03:19going to go ahead and get started. This is just me. Hopefully you know who I am, but if you
00:03:25don't
00:03:27check out my YouTube channel. All right, let's, let's get going. So again, starting at the very
00:03:34basics, I'm assuming that you came in here knowing nothing about SQL. And if you already do, then this
00:03:38should seem like a breeze to you. But then again, it'll progress. Now I only have an hour or a
00:03:44little
00:03:44bit less. So I'm going to try to go pretty quick because I also want to answer some questions at
00:03:49the
00:03:49end. So let's get started. What is SQL? So SQL is really just the way that you query
00:03:55the data. So there's SQL databases, uh, which we'll get to, I think in the next slide a little
00:04:00bit, which is how the data is stored. But then you have SQL, which is the structured query language.
00:04:06And it's, I wrote a programming language, but I know that's really controversial. It's more of a
00:04:12query language. Um, when you think programming languages, you're thinking of R or Python or Rust
00:04:17or C++. Those are more traditional programming languages. Um, but some people call SQL programming
00:04:23language. And a lot of what SQL is used for is just a standard way to interact with your data.
00:04:29It's just a way for anybody who's using it to be able to write and reproduce exactly, uh, how to
00:04:35retrieve that data. And it's used in a lot of different things. It's not just data statically or, or
00:04:41data, you know, just sitting in a database. Oftentimes it's also really dynamic databases. Um, when you're
00:04:47working with like web dev or, or backend systems, as well as data analysis, which is mostly what I've
00:04:53used it for, but I've also, uh, you know, recently in the past year or two, really been using a
00:04:57lot
00:04:58with some more, um, dynamic databases that are a lot of moving parts and it gets pretty crazy. Um,
00:05:05I say crazy, but it may not be crazy to you. Um, so we're going to start off by just
00:05:10kind of
00:05:10understanding what a database is, what a table is, and then we'll go from there to actually, uh,
00:05:15about writing SQL queries and how to actually retrieve that data. So a database and a table.
00:05:22So the database is going to be a collection of a lot of data. So a lot of tables views.
00:05:27Um,
00:05:28it can be a lot of different things. And the, um, the database is what really stores everything in
00:05:35one central place, which is great because you don't want to have to go and piece everything
00:05:39together from different sources. You know, you kind of gather all those sources and then you put
00:05:44it into these databases. So these tables are then what's within a database and the tables like an
00:05:50Excel spreadsheet. That's how I like to describe it. It's just, um, it's just columns and rows of
00:05:55data with different data types. And that's really all it is. Now in this screenshot here, I try to
00:06:01make it as big as I could in the screenshot here. This is just a MySQL database. And you can
00:06:06see on
00:06:06this left-hand side, we have a database called bakery. And then we have tables underneath that.
00:06:12So these are all different tables within one database. And so you can store 10, 20, 30, 50,
00:06:20a hundred different tables, uh, within one database. Now underneath that, we also have
00:06:25things like views, store procedures, uh, and functions and other things, but we'll kind of
00:06:29talk about more of that stuff near the end because some of those things are a little bit,
00:06:33a little bit more advanced, but that's what it is. And so as you can see here, I have this
00:06:37query,
00:06:38very simple saying, select everything from, uh, this database and table. And then we have a little
00:06:43output. Now we'll get to that a little bit, but just to kind of prime you, uh, for what's coming
00:06:48when we actually start querying or learning about how to query, but that's where the data is stored.
00:06:54Next, I want to talk a little, just, just a tiny note, just a tiny note on DQL, DDL, and
00:07:00DML.
00:07:01Now, when you're actually using it, knowing these isn't like the most important thing. There's
00:07:06actually a lot of other ones as well, but most data analysts don't use these, uh, or don't use
00:07:11the other ones. These are primarily the ones that most data professionals, data scientists, data
00:07:15analysts, uh, business analysts, these are the ones that they're going to use. And they're basically
00:07:19just ways of saying, here are the types of, um, uh, statements and functions and things you can use
00:07:26that you will use within SQL. So we have DQL, which is just the query language. That's how we select
00:07:32our data. And then we have DDL, which is the definition language. So we're creating tables
00:07:37or altering tables, we're dropping tables or dropping other things. But, you know, as an
00:07:42example, then we have DML. Now this is where you're inserting, updating, and deleting from
00:07:47your table. And you can see a little example here, uh, about how those are used. So the DQL
00:07:54is just selecting your data. The DDL is actually creating a table. And then the DML is inserting
00:08:00data into that table. Let's go to the next one. So now, uh, we're going to get, I kind
00:08:07of have gone pretty quick so far. I'm going to start slowing down a little bit because
00:08:10this is where everything starts getting, in my opinion, a lot more interesting. So when
00:08:16you're in a real job, when you're a data analyst and you're given a lot of data, typically what
00:08:23they're going to want most of your work is going to be is around filtering and sorting
00:08:27and, and kind of doing exploratory data analysis or doing, you know, that may not be the main,
00:08:31uh, part of what you do, but you're going to have to do this. So knowing how to filter
00:08:36and sort your data is extremely important. So filtering just allows you to specify exactly
00:08:42what you want to pull from that data. For example, we have a table that has 10 million rows of
00:08:48data
00:08:48and a hundred columns. That's a lot of data. Most likely you don't need all of that. So what filtering
00:08:53allows you to do is say, okay, I just want these three columns and I only want it where
00:08:57it equals this or where it includes this type of information. So we can specify what we're
00:09:02pulling, sorting the data talks about just arranging it. So we're just making it look a little better
00:09:08in the output or changing how it's formatted, uh, which, you know, we can, you know, it just
00:09:14helps with, um, visualizing the data a little bit easier. So if I was working with a price,
00:09:19I could say, I want to do it in ascending order or descending order. I could see the lowest price
00:09:24or the highest price first. And a lot of these things are, um, they're very simple, but as you
00:09:31get more advanced, they become very, very important. Um, and I don't want to ruin some stuff later on,
00:09:37the more advanced stuff, but even these small things of just sorting your data actually has big
00:09:42implications, uh, in how your data is exported or how the data is put into, uh, products and all
00:09:49these different things. So filtering and sorting data is very, very important. Here's just a quick
00:09:54example of, uh, filtering data. So in this database, uh, we're looking at bakery employees
00:10:01and in these where statement, this is where we're going to say, here's what I actually want to see
00:10:06in my output. So I'm saying where the salary is greater than 40,000 and the department is equal
00:10:12to bakery. So here we can see all the salaries are greater than 40,000. And then we have our
00:10:17department column and that's only the bakery. So, uh, you can also see my wife. She's the lead
00:10:22baker there. She's the lead baker at our house, which is why she's the lead baker in this, uh, in
00:10:26this table. Now we also have sorting data and sorting data. Uh, we're just doing the exact same,
00:10:32uh, columns that we were doing before, or the exact same table that we were doing before,
00:10:36but now we're using order by an order by is just saying, okay, I have my data. I don't want
00:10:42to
00:10:42filter it because we're not, we're not removing or specifying what data we're pulling. Now we're just
00:10:47seeing how it's displayed in the output. So then we're ordering by, in this one, we're doing
00:10:51department. So we can specify, we want department in ascending, which means lowest to highest, or it
00:10:58means in alphabetical order, a to Z. And so here we're doing it, uh, on the department, we have
00:11:03bakery first, cause that's B and then marketing next. And then of course we have the title. So we're
00:11:09able to, uh, order on multiple columns and you can do, uh, you know, as many columns as are in
00:11:15that
00:11:15table. You could do every single one if you wanted to, although I am positive would become
00:11:19completely redundant, but you can filter or you can order on multiple tables or columns. Sorry.
00:11:27Next, this is, um, this is, I don't want to say the most important thing for beginners,
00:11:34but it's one of the most important things, um, grouping and aggregating data. So let's just talk
00:11:39about what it is. And then we'll look at an example. So grouping data refers to combining similar
00:11:44values in a column, and then we can perform something called an aggregate function. And these
00:11:48things should be, um, something you've heard about before. If you've ever used Excel, you've
00:11:52heard of things like some, or the average, or the median, the mean, um, these, these types of
00:11:57aggregate functions are things that you can apply to those groupings. And so they're really, really
00:12:03important because you have, especially with categorical data, you have a lot of data and you want to
00:12:11see, okay, you know, for just this company or for each company, you know, what is their profits or
00:12:17what is, uh, their customer retention or all these different things. You can look at that at a much
00:12:22larger level than just the individual row level, which may only give you a tiny snapshot of the
00:12:27data. So it's really great for creating reports, statistical analysis, and especially when you're
00:12:32trying to look at really large data sets with just millions of rows, uh, grouping and aggregating
00:12:38data is extremely important to know. So let's take a look at this example. So in our employees
00:12:44table, we want to group based off of the department. Now, remember we had about four
00:12:48employees, uh, per departments. There were four employees in the bakery, I think four in the
00:12:53marketing, and we just wanted to look at the average salary. Now, before, when we were looking
00:12:58at it, we were looking at each person. So each person had a salary and that's great, but we want
00:13:03to
00:13:03kind of see at a larger level, you know, what are all of their salaries combined? What is the average?
00:13:09So that's what grouping allows us to do. So we're grouping by, you can see the group by,
00:13:14we're grouping by department, but up above, we also have to select the department and then use
00:13:18our aggregate function. And for this, we're just using average. And so we're saying the average
00:13:23salary. Then we're also naming our, uh, our column there as salary. And that's just an alias
00:13:30of that, um, of that output. And so we're grouping by that department. So the department
00:13:35is what's being grouped. All four of those, uh, people who are in bakery, all four of those people
00:13:41who are in marketing are now put into one row. And then we're allowed to, uh, put these, you know,
00:13:47these aggregate functions on them. And this is just kind of the tip of the iceberg because we're going
00:13:52to get to some more advanced things that involve grouping data as well. But in its simplest form,
00:13:56this is something that I personally use every single time I use a SQL. I mean, it's just so
00:14:03incredibly common to need to group data and look at aggregate functions on it. Um, so if you're just
00:14:09starting out the, these two things that we've just looked at, which is where order by and grouping,
00:14:16if you can learn those things, three things really well, that's, you know, 70% of what you're going
00:14:22to be doing with basic SQL. So you get into a job as your first job as, as a data
00:14:27analyst. And you
00:14:28know, this is kind of the stuff that you need to know. And so this is also really good to
00:14:32know, um,
00:14:34for technical interview questions as well. So if you're getting, you're interviewing and they ask
00:14:38you about these things, this is something that you should know. Let's go into the next one. Let me get
00:14:43a sip of water real quick. The next thing is joins and sub queries. Now from here, basically to the
00:14:54end, we have some, we're going to start getting a little bit more advanced now joins and sub queries
00:14:59aren't crazy advanced, but when I was first starting out, uh, joins just like blew my mind. I hadn't,
00:15:06I couldn't understand it at all. I didn't, I was like, it just didn't, it didn't connect with me for
00:15:12whatever reason. And, um, I failed many a technical interview because I didn't know things like joins
00:15:17and sub queries. So let's talk about what they are. Joins are fundamental. Um, if we, you know,
00:15:24kind of think back to that schema that we had earlier with our database, and then we had 10
00:15:29different tables. Imagine you're only able to use one table at a time. That would be horrible,
00:15:36uh, because your data is not all sitting in one table. Oftentimes it's spread over three,
00:15:41four or five, 10 different tables. And you need to be able to combine those to get the output that
00:15:47you need. And so joining is how you actually do that. So joining combines the rows from two or more
00:15:54tables based on related columns. Let's say we have our employee ID, uh, from the table that we were just
00:16:02working with. And then, excuse me, we have the employee ID in another table. We're able to join
00:16:07those together based off the employee ID, which I'm sure we'll see in the next example, but joining
00:16:12is super, super important. There's a lot of different types of joins and understanding them
00:16:17and, and really using them correctly is it completely changes your output. So if you want
00:16:23to get the right output, when you join something together, you have to make sure, you know, these
00:16:27joins. Now I'm just going to briefly go over them, but this is something that you should definitely
00:16:31look up, um, and see how to do. So first we have inner joins and that's when the data from
00:16:38both of
00:16:38those columns, from the different tables, when there's overlapping data. So that's, that's what
00:16:42will be in our output. The left join takes everything from the left table, but only takes
00:16:47matches from the right table. That is the opposite for a right join. It takes everything from the right
00:16:52table. And then only what is matching from the left table. If you don't know joins, that's going to
00:16:57make zero sense to you. So I hope you go and you, you know, kind of dig into joins a
00:17:01little bit more,
00:17:02but that's what joins are. It's just how you connect data from, from different, um, tables.
00:17:07Very, very, very important. But then we have sub queries, which is, it's not joining data together,
00:17:14but you are able to utilize other tables within sub queries and how I like to kind of say a
00:17:22sub
00:17:22query is it's a query within a query. So you have your main query and then you have a little
00:17:28mini
00:17:28query within it. Um, and a lot of people will call those nested queries. And so it does help with
00:17:35simplifying things. Sometimes, um, I'm going to take a look at the examples and I'll talk a little
00:17:39bit more about these because, um, they can be a little bit confusing, but let's look at, oh, there we
00:17:46go joins. So this one is our joins one. So let's look at the syntax really quickly. First, we're
00:17:52selecting what columns we want from both of our tables. And then we're saying we want to join the
00:17:58customers table to the customer orders table. And then we have to specify what columns are the same
00:18:04or what columns have similar data in these two tables. So now we're saying from the customer table,
00:18:09we're taking the customer ID and from the customer orders table, we're also taking the customer ID.
00:18:14That's what that on part is. So we're specifying what columns we're pulling in. Now as a little
00:18:20added bonus, uh, and this is something that once you start using joins, you'll do every time, but a
00:18:26little bonus I wanted to add in here is when I do bakery dot customers in the from clause, the
00:18:32from
00:18:32statement, um, when I do bakery dot customers, I use an alias C. And then when I do in the
00:18:38join on the
00:18:40customer orders, I use C O. So I'm aliasing those. Now, why am I doing that? Well, if you want
00:18:45to
00:18:45specify a column in your actual select statement, I'm selecting this column and it just happens to be
00:18:51in both, uh, tables. You have to use that column. You have to specify what table are you pulling it
00:18:57from? So for example, and I didn't do it in this one, but there's a customer ID in both of
00:19:02these
00:19:02tables. So I can't just put customer ID in the select statement because it SQL isn't going to
00:19:08know, you know, which table does he want it from? I don't know. So using the alias and specifying,
00:19:14I want it from the customers table. So I want C dot customer ID. This really helps with, um,
00:19:21shortening it because otherwise, if you don't use that alias of C and CO, then when you're selecting
00:19:27in the select statement, it's going to be really long. So you have to do customers dot first name,
00:19:32customer orders dot last name, and it just gets really, really long. Um, and it looks bad. So
00:19:37if you shorten it to that alias, you can use that throughout all of your, um, statements really,
00:19:43uh, and all your query, and it just really simplifies it, makes it a lot easier. So that's
00:19:47what joins are. We're again, getting it from different tables. We have two tables here we're
00:19:51pulling from, but now let's look at sub queries. Um, sub queries are really interesting. This one
00:19:58is a super simple and I formatted it to where you could really see the sub query. Well, but
00:20:03sub queries are really interesting and they can get really complex. But one thing I want to note
00:20:08because a lot of people will say, well, what is the difference? Well, you know, what should I use
00:20:12a join or a sub query? And just to be completely upfront, I try not to use sub queries a
00:20:20lot. Um,
00:20:21but it's important to know cause there are use good use cases for it. But the reason I don't use
00:20:26a lot of sub queries, especially when I'm using a ton of data is that it's a little bit slow.
00:20:30Um,
00:20:31and joins actually not for everything, but 99% of the time joins going to be a lot faster. So
00:20:38just
00:20:38with speed, um, because I worked with a lot of data, like there were tens of millions or a hundred
00:20:43millions of rows of data using a sub query could take an extra like five or 10 minutes to run
00:20:47and join
00:20:48would take like, you know, a minute or something. And so you just have to, um, and this is something
00:20:53we'll talk about at the end, which is query optimization. Um, and so, you know, knowing how
00:20:59things operate on the backend, knowing how things, um, uh, process and how quickly they'll go is
00:21:05important to, to writing your queries. It's, it's a more advanced thing that you learn, um, as you get
00:21:10further on in your, you know, SQL career career, but let's look at sub queries really quickly.
00:21:15So right here, you can see, uh, down to the right within these parentheses, I tried to make it
00:21:21really obvious. This is our sub query. And what this is, is it's a complete SQL statement or a
00:21:27complete SQL query, but it's within a outer query. So let's start from the very top. We're selecting
00:21:34everything from bakery.customers, but then we're going to say where our customer ID is in. And then
00:21:41we have our sub query. Now in the sub query, we're not pulling from the customers table. We're
00:21:45pulling from a completely different table called ordered items. So now we're seeing what customers
00:21:51have actually ordered items in that ordered items table. And so we're saying we want customer IDs
00:21:57from the customer ID table or from the customers table, where there's also a customer ID in the
00:22:03ordered items table. So we're pulling out in our output. You can see all of these people are people
00:22:08who have actually, uh, created or bought something and ordered items in that other table. So this is
00:22:16also something that you could do with a join. You could just join the customers to the, uh, ordered
00:22:21items table, and you could join those together. And so again, it's, it's very specific on your use
00:22:28case, but in this instance, uh, I would definitely be using the join. I just wanted to show that you
00:22:33could do it with a sub query. And again, if you're working with a small amount of data,
00:22:36it's com could be completely negligible in terms of speed. It could be an extra, like 30 milliseconds,
00:22:43basically nothing. But, you know, I, I definitely want to kind of let you think of, or make you think
00:22:49that, you know, when, when you get into the real, uh, a lot of companies, when you work with real
00:22:55data
00:22:55and you're working with large data at any, you know, fortune 500 company, any tech company,
00:23:00they're going to have an insane amount of data. Um, and so, you know, if you get a job at
00:23:05a smaller
00:23:05company, it's a little data, your queries might not translate perfectly over to the larger company
00:23:11that works with a lot more data. And, you know, that's, that's definitely something to think about.
00:23:17Let's go on to the next thing. So we have windows functions.
00:23:23Window functions are, I don't want to say it's not a more advanced group by it's different,
00:23:30but it has more functionality and you can do, I think you can do more with it, but it's definitely
00:23:35to more of the advanced side and it doesn't have as many, uh, let me backtrack. Group by is great
00:23:44for looking at categorical data. Um, it's really great for simple aggregations, but then when you
00:23:50get to window functions, it's like a, it's kind of like a big step up in, in, um, the kinds
00:23:55of things
00:23:56that you can do with it. But like I said, group by is just one of those four, uh, fundamental
00:24:00concepts that you use for a lot of different things. But then window functions has some very
00:24:04specific use cases. And we'll look at example in a little bit. And window functions are something
00:24:10that when I first started out, I never used, I didn't use it for probably, um, even six months
00:24:15into my job, just because I was working with more of the simple stuff, but there came about a lot
00:24:20of
00:24:20different use cases where I was like, Oh man, I can't do this with group by it just doesn't work.
00:24:24Or my output isn't looking how I need it to look. I need to use a window function. So I
00:24:29had to use,
00:24:30I had to learn it. So I'm going to talk a little bit about the difference between group by and
00:24:35window function. And we'll kind of look at an output in the, in the next slide. But what a
00:24:39window function does is you can perform these calculations and these aggregations, much like
00:24:44you can a group by, but you do it over a window or a subset of the data. And then
00:24:50in your output,
00:24:51it's not grouped together. It's has its each individual row that it was already kept on. So
00:24:57it's hard to visualize it. We'll see in just a little bit. Uh, I hope I don't, I can't remember
00:25:04if I gave an output in this example. Um, but what you can really use it for is things like
00:25:09running
00:25:10totals, uh, ranking and finding moving averages. These are things that I often use it for. Um,
00:25:15and there's things like row number where you can, uh, give a number to every single row
00:25:21and you can do that over sets of data. Um, and the window functions are not just used for,
00:25:27you know, looking at the data. These are things that, you know, I've built entire reports,
00:25:32automated reports and sent them to clients based off of window functions. So they're like,
00:25:36you know, we need rolling totals for, for this data. That's easy. So I just create a real,
00:25:41uh, uh, I create a window function for that rolling total. And then, you know, I send that
00:25:46out as a report. That's really common. It's a really common type of thing that you'll see with
00:25:51window functions. Let's take a look and see if I included it. Okay, perfect. I'm glad I had this
00:25:58in the output. Um, but, uh, here we have, we're still working with this employees. So we have all of
00:26:06our employees, but typically what you do is, uh, with a group by is you'd group on something like
00:26:12their department. And if we wanted to look at their department, we wouldn't be able to use their
00:26:18names because all their names are unique. So with department, when we use group by, we'd put it all
00:26:22onto one row and we'd be able to use aggregate functions. Now, uh, in this example, I wish I had
00:26:29included one other one, uh, which would have been just like a partition. Um, that that's my fault and I
00:26:34apologize, but the ones that we're looking at are really interesting. And these are kind of ranking,
00:26:39um, uh, these are ranking functions. So rank dense rank and row number. So you can see we're doing
00:26:46it. Um, let's look at the rank. So we have select first name and salary, and then we're looking at
00:26:52rank. Now, when we use this, we're going to start, we're going to, um, break it up by something. Now
00:27:00for us, when we're using these ranking functions, we don't have to break it up. So we just use
00:27:04order by, so we're ordering by the salary in descending. So we're taking the highest salary
00:27:08first, all the way down to the lowest salary. And you can see there's in our output, we have first
00:27:13name salary, and then our salary rank. Now this is going to rank our salary based off of the order
00:27:20that we put it in. We ordered by salary. So the salary rank goes one, two, three, four, six, six,
00:27:25eight. So we had a match between Dwight and Tom at 45,000. And because they were the exact same
00:27:32rank
00:27:32is going to give them the same number, six and six. And then for Carl, it skips down to eight.
00:27:38So, uh, these window functions are really fantastic. I wish I would have, uh, and this is my
00:27:44fault. I do apologize, but I have a, I have a lesson on it. You can go watch on YouTube,
00:27:47but
00:27:48window functions are really great for then partitioning data. So again, I want, I wanted one,
00:27:56I want one more. I need to go back in time and, and redo that. But, um, if we compare
00:28:00it directly
00:28:00to the group by, when we grouped on department, we had to do the average of all the department,
00:28:04and then the department only had one row, right? With window functions, we could get the average
00:28:11of each department, but then we don't have to group them all in one row. We can have all
00:28:14of our data, but then at the very end, there's a column that has the average. And that's really,
00:28:20really useful. And then there, of course, there are these, uh, different types of functions
00:28:23that are super useful for ranking as well. Let's go on to the next one.
00:28:30Give me one sec.
00:28:33So now we have optimizing SQL queries. This is, um, these next two or three things that we're
00:28:40going to talk about are a lot more advanced. These are things that I, I was learning, um,
00:28:46from a lot of data scientists, data engineers, um, who were really good at optimizing queries,
00:28:54because you have to take a, take a step back when you're working with little small data sets,
00:29:00optimizing queries doesn't do anything. It may change it from 50 milliseconds to 49 milliseconds.
00:29:05It's, it's negligible. But again, you have to imagine you're working at a big tech company.
00:29:11They have millions, if not billions of rows of data. Um, and you're joining to tables that have
00:29:15millions and it's, it gets really complex. So optimizing SQL queries is really used for
00:29:21improving the efficiency of how fast things run. And why is that important? I'll give you one
00:29:29example. Uh, we used, uh, at an old job I used to have, I was a data analyst and we
00:29:35used to have
00:29:35this job that would run overnight and we would do an entire process. So we would get the data from
00:29:40the
00:29:41source. We would clean the data using all the transformation rules that we had created.
00:29:45And then, uh, it would add that data to our preexisting table. It was a, it was a whole
00:29:52process and it ran, I think every single night. Well, this was one of our biggest clients, one of
00:29:57our most important clients. And so this was like really, really important. And the process that
00:30:02we were using, um, involved a lot of, you know, simple stuff. It wasn't anything wild. It wasn't
00:30:08anything crazy. Um, but there were some things that were taking a lot longer to run. And so I
00:30:16would go in with our data engineer and we would look at all these rules that we did for transforming
00:30:21the data. And we had to figure out how to optimize these SQL queries to run overnight. So sometimes if
00:30:28we did things incorrectly, it would take 11 hours to run. Well, what would happen is, is those doctors
00:30:33would then look at the data in the morning and it wasn't there. And so they would call us and
00:30:37they'd be
00:30:37upset. So it had to run in a specific amount of time. So we would optimize these queries and, and
00:30:42optimize our, our business rules that we made to transform that data. And we would have to make them
00:30:46as efficient as possible. And we got it down to about like four hours. It was a lot of data.
00:30:51It was a
00:30:51big process, but we got it down to about four hours. So that's the difference of a client being
00:30:55really happy and being really unhappy. Um, again, that's much more advanced stuff, stuff that, you
00:31:01know, as an entry-level analyst, you probably won't be working on that much. Um, doing really
00:31:06intense SQL optimization, but it's very important to know. So if you're kind of starting out,
00:31:12just remember this in the back of your head when you're working with these things. So, um,
00:31:16what are some techniques that you can use, uh, for actually improving these speeds? One
00:31:20really big one, especially with big data is indexing. Indexing is, I never worked with it until I,
00:31:27uh, started working at, um, this company called Amerisource Bergen, like a fortune seven company.
00:31:32They're like number seven on the fortune 500 company or fortune 500 list. I had never heard
00:31:37of it, but we worked with like a hundred times larger data than I did at the small company I
00:31:42was
00:31:42working with. So I had to learn indexing, uh, uh, really well. And we'll, um, I'll talk about
00:31:48indexing a little bit more in a second, but then we have, uh, you can rewrite your queries to just
00:31:54change the order of things change. If you're using a join versus a sub query versus joining, um,
00:32:00on one column versus two columns. Um, and then another thing, and this usually isn't for a data
00:32:05analyst, but you know, this is something that would work with like a database developer on
00:32:10or a data architect on, which is proper data modeling. Now, how you store your data,
00:32:15how the data actually sits in your database makes a big difference. Is it the correct data type? Is
00:32:20it stored efficiently? Do you have the right primary and foreign keys, uh, lined up? You know,
00:32:25there's a lot of different things that go into data modeling, uh, and creating a good data
00:32:30architecture. And so that also can play a big part. Um, again, not something that most data
00:32:36analysts will use or need to know right away, but I think as you get to more to the senior
00:32:42level or
00:32:43lead analyst role, um, knowing how these things work can make a big difference. And you can really,
00:32:49you know, show your worth by, by, you know, kind of pointing these things out saying, Hey,
00:32:52this is running inefficiently because of, you know, how our data is being stored. Um,
00:32:59yeah, the last part is, uh, you know, kind of what I was talking about in my example,
00:33:04which is bottlenecks. So we have, um, you know, you, you have a lot of data flowing in from a
00:33:10lot
00:33:10of different sources, especially when a large companies, you have, you know, 10, 15, 20 different
00:33:14sources that you're getting data. Um, maybe the bottleneck is, you know, it's all funneling into
00:33:19one place at one time and that's your bottleneck. And so you need to stagger when you're bringing
00:33:24the data in and have that, uh, have that process done correctly so that you're not trying to bring
00:33:29everything in at once. Uh, that can be a huge bottleneck. And so, but there's a lot, a lot to
00:33:35that. Let's look at, um, an example of this. So I have three examples, um, that are, you know,
00:33:43pretty simple. The first is the index. Now indexing is when, and this is kind of a tough,
00:33:50uh, concept to just say off the top of my head, but I'm going to do my best indexing is
00:33:54where you
00:33:55kind of tell SQL, you say, Hey, SQL, I'm going to be using this, uh, in this example, these customer
00:34:01names a lot. And I want you to remember that in the back of your head, um, even though it
00:34:05isn't
00:34:05stored in the table this way in order, I want to index this customer name. Um, and so that you
00:34:12remember it, uh, behind the scenes. And then when I query on customer name, you already remember it.
00:34:17You don't have to process it in the query. You're processing it already in the backend.
00:34:21And then it just goes a lot faster. Um, to give a better example of indexing is something like,
00:34:29um, let's index, well, you can index anything. You can index any column, multiple columns. Um,
00:34:36but let's say we're indexing like a product. So let's say we're, and we're working with Amazon
00:34:42data. There's a billion products on Amazon, right? And there, there are all sorts of names
00:34:46and all these different things. And the table that they're storing this, uh, this product
00:34:51information is it's not, it's, it's based off product ID. So it's ordered on product ID.
00:34:55Well, if we then go and create an index on the product name and that it's stored in the back
00:35:02kind of has it organized when we then run a query based off of the product name, it's going
00:35:07to be able to retrieve that data faster because it already has it stored in order in the back.
00:35:13What it might have to do if you don't have an index is it's going to go into its memory.
00:35:17It's going to order all of it. And that takes a long time. There's billions of products.
00:35:21It takes a long time. It has to order it, order it, order it. Then it has to go back
00:35:24through
00:35:24and then select the one you want. So all of that work that is being done when you actually query
00:35:30it
00:35:30could be done with an index beforehand. And then anytime you, you query that product,
00:35:35it already has it and it can select it 10 times faster. Um, for in just like a speed example,
00:35:42I remember creating indexes that, um, were very specific to what we needed. It's usually what you
00:35:48use an index for very, very specific to what we needed. And they would reduce the time for a query
00:35:53execution from like an hour down to like five minutes or seven minutes. It was drastic. Um,
00:36:01but just one more note on indexing. Um, if you're going to try it out, are you,
00:36:05you know, have used it before indexing is very specific to types of queries. So,
00:36:12you know, really study what indexing is, how it works, but this is a concept that really advanced
00:36:17analysts and data engineers and people, they, they need to understand well, um,
00:36:22for query optimization. The next one is just, uh, querying faster. Now, this example is super simple.
00:36:29It's basically, uh, it could have been written like a sub query, but instead we used a join
00:36:33and the join was faster. And so that's really all it is, is sometimes it's as simple as just
00:36:38changing around your query a little bit, knowing how things process on, uh, you know, behind the
00:36:43scenes to make them go faster. That, that really is what, um, what changing your query can do.
00:36:50And then the last thing, and this one is like more of a, uh, kind of a tip because we'll
00:36:55get to our
00:36:55next one is automation, but we're going to get to this next one, which is you can analyze a query's
00:37:01performance using explain. And if you've never done this, go do it. Uh, especially in MySQL. Um,
00:37:06if you go to MySQL and you type in explain, and then you write a query, it's going to tell
00:37:11you,
00:37:12you know, how fast it, it, uh, took, how many rows of data it had to look through.
00:37:17I use this explain a lot with indexing or other things because indexing can show you, oh, it,
00:37:23you know, you only had to look through 3000 rows of data instead of having to look through
00:37:29100,000 rows of data. So again, it can explain why things are speeding up, what type of indexing
00:37:34it's using. Go, go and check that out. It's, it's really fun, um, to mess around with. I enjoy it.
00:37:41It sounded very nerdy, but it's true. All right. Let's take a look at automation.
00:37:48Now, when I talk about automation, uh, a lot of different things can come to your mind,
00:37:53right? But in SQL, it typically is using things like scripts, store procedures, schedulers. These
00:38:00are things that most likely are built in. Although you wouldn't believe the kinds of things you can
00:38:05do in SQL. You can write some really advanced, like what I would consider pretty advanced code
00:38:10in SQL, but you can also just use a lot of things that are built in like store procedures are
00:38:15things
00:38:15that you can just clicks, you know, create store procedure and write out what you want to have
00:38:19happen in that store procedure. So it's really just automating those repetitive tasks. We use this a
00:38:26ton, uh, with bringing data in. So data ingestion, we use a lot of data, uh, automation with data
00:38:32cleaning. So, you know, dirty data is coming in from the source. We write our data cleaning rules,
00:38:37um, in our queries to clean that data. And then as it's coming in, it's automatically being cleaned
00:38:42and being put where it needs to be put. It's amazing. Um, another thing is creating reports.
00:38:47So just that data, you know, you're grouping by a data or using a windows function on your data
00:38:52and your client wants to see that, you know, they want to see maybe a daily, uh, output of what
00:38:56that
00:38:57looks like, or, you know, more likely like a monthly is, is what, um, a lot of reports would be
00:39:01for
00:39:02clients. And you can schedule that and automate that using something like a scheduler and a start
00:39:07procedure or, you know, a combination of all these things, but you can get that output of the query
00:39:12and then you can put it into an Excel file and then send that to the client. Um, you know,
00:39:16that's,
00:39:17that's automation. So you don't have to manually do that. Um, it's, it just saves so, so, so much time.
00:39:23Another thing that's really helpful for, and I already mentioned a few at the bottom,
00:39:26but another thing is something like backing up your data. Um, this goes above what a data
00:39:32analyst should most likely be doing, but at a small company, you might, but backing up your
00:39:36database and making sure if your database or your server crashes, that you have a backup of that
00:39:42data, super important. So you can create, um, backups. And I think that's the example I used in
00:39:47the, in the next one. Um, but you can create backups. So this right here is a super simple,
00:39:54um, automation of a nightly backup. That's all this is. So every single night, which is not common,
00:39:59uh, usually you do like once a quarter or something like, or, you know, once a month,
00:40:04depending on the company. Um, but for this, it's a nightly backup. And so this event is,
00:40:11um, you know, it's, it's basically scheduling saying, Hey, every day, and you can specify even
00:40:17the day, the time, um, et cetera, we're going to, we want to back that up, um, and put like
00:40:22a copy
00:40:23of a database over here in a secure location where if anything were to happen, we would still have
00:40:29our data and our business could continue really important. And then down below it's saying, do
00:40:34this, create the database, create the table, create that. And then you can automate that even further.
00:40:39I mean, there's, it's really almost endless what you can do, uh, in SQL. Let me see. Yeah. So these
00:40:46are some real world examples. Now I've talked about a few real world examples. Um, but, uh, I did write
00:40:53down a few as well. So a lot of the things that just SQL, not the crazy advanced stuff, but
00:40:59just
00:40:59writing simple SQL queries, it can be used for things like managing your inventory, making sure you
00:41:04have the products you need at a company for tracking your sales. I've done sales tracking a
00:41:09lot, um, analyzing customer data or analyzing patient data. I worked a lot in healthcare
00:41:14where we had to analyze a lot of patient data. So I was working with, uh, hematology oncology,
00:41:20which is blood and cancer. Um, so I worked with cancer data for years. Um, and so analyzing patient
00:41:26data, sending that back to pharmaceutical companies, to actual hospitals and saying, here are your stats,
00:41:31here, your, you know, survival rates. These are all things I did within SQL for years. Um, really,
00:41:37really useful stuff. Um, I went a little bit beyond, uh, just that you can also use it for
00:41:43web applications, something that I'm doing right now, uh, which is you can use SQL to restore and
00:41:48retrieve data on websites. And so things like Amazon and all your favorite, uh, companies where
00:41:54they store products are not even products, even like Netflix, they store a lot of data
00:41:58in databases that they're retrieving almost immediately. When you click on something,
00:42:02it's going in and getting that data, um, and SQL, uh, databases, as well as those processes and
00:42:09automated processes are used, um, in these systems and these websites and these platforms.
00:42:14The last thing, and this is more for data analysts, but data, data analysts can use it a lot
00:42:20for reporting, do a lot for just getting insights, performing exploratory data analysis,
00:42:25calculations, reports, data cleaning, um, data manipulation. There's a, it could go on and I
00:42:32could say like 10 other things that's on the top of my head, but SQL is really important. It's one
00:42:36of those skills. Um, it, and just to give you context, SQL was the first thing I ever learned
00:42:42as a data analyst. I kind of guessed I like, um, I looked up, I was like, what did data
00:42:47analysts
00:42:48need to know? And SQL popped up and I was like, let me try to learn that. Uh, it was
00:42:51really hard for
00:42:52me to learn admittedly, but I can tell you over the course of my career, that has been,
00:42:57that is what's earned me the most money and has been the most useful skill out of my entire career.
00:43:02More than Excel, it's up, Excel is just a little lower, but Excel has been super useful too.
00:43:08More than Tableau, Python, Power BI, any other tool, SQL has been my number one skill. And so,
00:43:16um, oh man, I made it in time. All right. I wasn't sure if I was too close to one
00:43:19o'clock,
00:43:22but SQL has been my number one skill. And even today, um, I, I do consult now. I own my
00:43:28own
00:43:28business. I do a lot of consulting with companies and a lot of times I'm, I'm talking to them about
00:43:33SQL. I'm talking how to, you know, utilize SQL better, um, with small companies, startups,
00:43:38et cetera, or use the cloud. I talk a lot about that, but you know, SQL is even transferable to
00:43:43the cloud where I used a lot of SQL and Azure and AWS with databases in the cloud. So SQL
00:43:48isn't just
00:43:49an on-prem or on-premises, uh, database or server that's in your company. A lot of times now,
00:43:55most companies are using things like, um, or even CRMs, but are using things like, um, you know,
00:44:02cloud platforms and cloud hosting platforms to store their data or cloud storage to store their data.
00:44:07And so, you know, SQL is, yeah, I can't talk. I can't say enough good things about SQL. Um,
00:44:13it's just fantastic. It's amazing. Highly recommend learning it. I had to go through fast,
00:44:17uh, blame career foundry, blame, blame William. I had to go through fast cause we have a limited
00:44:22amount of time, but it, you know, everything that I went through in, uh, in this webinar are things
00:44:30that I 100% think you should learn at some point in your career. So some of those things like,
00:44:36you know, the automation and, um, uh, query optimization, that stuff's, it gets a pretty
00:44:42advanced. It can be tough to, uh, learn, but I promise you it will pay out. It's super,
00:44:48super good stuff. Um, I don't know, it gets me excited, but that's all I got. Um, I think
00:44:53William's going to come back. We'll do some Q and a, hopefully you asked me about SQL. Cause
00:44:57I love talking about SQL. Uh, I can talk about it all day.
00:45:02Awesome, Alex. Thank you so much for presenting very enlightening. And a lot of questions came
00:45:07through, but for anyone out there, um, who has got some lingering questions about SQL or anything
00:45:12related to data analytics, now is the time. Um, I've got staff, um, also on YouTube and LinkedIn.
00:45:18So do post your questions there too. We'll try and filter them through. I've got a couple of
00:45:22questions first to kick things off tonight. Um, Alex, if, um, if, if, if you've got your desk and,
00:45:29you know, a data analytics, um, CV lands on your desk, um, from someone who's just starting out as a
00:45:35junior data, um, analyst, what, what kind of programming languages would you like to see?
00:45:41What would be the kind of the top ones that you'd like to see on that CV?
00:45:45Yeah, that's a, that's a great question. Um, the first thing to remember is every company is
00:45:49different. So, you know, when I talk about SQL, I'm going to kind of give you a broader and then
00:45:55I'll get more specific. I always talk a long time. I apologize. Um, every company is different.
00:46:00So some companies might not even use SQL, but you're looking at, try to look at like the numbers.
00:46:04How, what are the percentage of data company or companies that are hiring data analysts that are
00:46:08using SQL? It's going to be a high amount. And so you want to kind of play the numbers. Um,
00:46:13so
00:46:13SQL is a, is a skill just to have on your resume because a lot of companies use it. Um,
00:46:18but what
00:46:18would I be looking at? Let's say I'm just hiring for, you know, I'm pretending I'm, I was a hiring
00:46:23manager, but I'm pretending I'm at my old company. I was hiring for the position that I used to have
00:46:28as
00:46:28a data analyst. We used a lot of SQL, Excel power. We were in Microsoft shop, power BI, Azure, Python.
00:46:37Um, I'll even some more advanced stuff. And genuinely what, what, when I was on the hiring
00:46:44team, we were hiring data analysts, business analysts, and we really just wanted a strong
00:46:48SQL foundation. We wanted, uh, with a business analyst, we just wanted a little bit of SQL,
00:46:53little Excel, but for data analysts, we wanted a strong SQL foundation and we would do technical
00:46:57tests, um, to make sure that was on their resume was good, but we would always look for SQL.
00:47:02We wanted to see if they had some experience. Um, and the experience wasn't always as a data
00:47:08analyst because we were a healthcare company. So sometimes we would hire nurses, um, who were
00:47:13nurses for 20 years, but we really needed someone who knew SQL though. So they like learn SQL in their
00:47:19part-time, but they had crazy in-depth knowledge of domain knowledge. Um, and so, you know, that's
00:47:25something to think about is utilizing your previous experience. And then we always wanted to see
00:47:30Excel. Um, and so, you know, for those types of positions, we were mostly looking for the
00:47:35fundamental skills and then good domain knowledge. Um, but again, it, it varies widely. Um, you know,
00:47:43if a company doesn't use SQL, they may use, they, they have to store data somehow, right? Maybe
00:47:47they're, uh, they use pandas or maybe are in Python. Maybe these pandas, maybe just use Excel
00:47:53or some CRM database that doesn't use SQL, but your database knowledge with SQL is still
00:47:58transferable to a lot of those things and you have experience using data. So just trying to tie it
00:48:03back to SQL. Awesome. Um, just following up on the question here from MU. Um, so if, if, if that
00:48:10kind of CV lands on your desk and, um, the person only knows Excel and SQL, do you think they've
00:48:15got a
00:48:15good job, a good chance of getting a job on the market? It's possible. It is possible. Um, especially,
00:48:23you know, at companies that that's all they need. If you know that well, that may be all you need.
00:48:28And in fact, I mean, I say maybe I know for a fact, because my first job that I was
00:48:33hired for,
00:48:33I only had SQL and Excel on my resume. Um, I, because that's all I knew, but you know, as
00:48:41I got
00:48:41more advanced, I was like, Oh, I, I need more things. This is kind of sad to look at, you
00:48:46know,
00:48:47there's some other core skills in there, but it's definitely possible. Um, it's just, I think,
00:48:51you know, for a resume to get into the interview, you know, I would recommend a few more skills that
00:48:57showcase that, you know, data better, but SQL and Excel are absolutely fundamental, fantastic skills
00:49:03to start out with. Awesome. And, and, you know, these are kind of like hard skills, but, um, as a
00:49:09data
00:49:09analyst, you know, working in a career, how important do you think soft skills are, um,
00:49:15in this profession? You hit me at the perfect time. Cause literally yesterday, I recorded a video
00:49:20on this that'll be released in a couple of weeks. You hit me at the perfect timing. Um, it's so
00:49:27there's a whole process, right? You have, you're trying to get a job in order to get into an
00:49:32interview. Your hard skills are what are going to get you into that interview. You could put leader,
00:49:36you could put good communicator, you could put, you know, good team worker, whatever you want to
00:49:41put on your resume. It's not going to get you an interview. They need to know you have the hard
00:49:44skills. So hard skills get you into the interview. Then in the interview, the hard skills, again,
00:49:50they'll press on it. They become a little less important in the actual interview because they
00:49:54already know you have it. They're just kind of confirming, but your soft skills are what really
00:49:59is going to sell you. Um, I can speak for myself. I, my soft skills, uh, probably got me most
00:50:04of my
00:50:04jobs when I was first starting out because I didn't have many hard skills. So I was just, I was
00:50:09really motivated. I was really excited to work, to get into the field. And so I think that shown
00:50:14through and I think that probably more or less got me most of my first two jobs, um, in the
00:50:20actual
00:50:20work is hard skills or soft skills more important. I think hard skills are just ever so slightly more
00:50:26important. You know, you have to be able to get your work done, but here's where soft skills come
00:50:31into a huge play in your actual work. You have to know how to work with people because you don't
00:50:35work in a silo. I'm sorry, uh, all those introverts out there. I also thought you could just work in
00:50:40a
00:50:40silo, but you can't. You're usually on a team and there's clients and customers and stakeholders and,
00:50:44and managers. Like you're always communicating, communicating with somebody. So those, those
00:50:49building those relationships between managers, between coworkers and depending on them for certain
00:50:54parts of your job, it's really important. You have to know, um, you know, how to communicate,
00:51:00how to get along, how to prioritize, how to time things, uh, how to schedule things. These are
00:51:08all soft skills that if you don't do well in it, it can really cripple your hard skills. You know,
00:51:13you, that's where it becomes a lot more of a, you need, you need both.
00:51:18Great answer. And so the video is dropping soon. The soft skills video.
00:51:23Uh, I think maybe next week, maybe two weeks.
00:51:26Wow. I think this would be also a great time to just plug, um, Alex's YouTube
00:51:30channel. So anyone from the career foundry audience, anyone on big market, do go and check
00:51:35out, uh, Alex's channel, Alex, the analyst over on YouTube. I think when we first started doing
00:51:40webinars together, you're about, I think you're about 200,000 and now you're well over 500,000.
00:51:45So, um, the voice of data analytics is Alex. Do subscribe to Alex's channel. Um, and also another
00:51:52shout out to Alex's fans tonight who've joined us. Um, now a few questions have come in over on
00:51:57YouTube and it's a question we get asked a lot also at career foundry. So when you're just
00:52:00starting out in data analytics or even prior to that, you know, you're studying, um, how do you
00:52:06actually get practical experience? You know, a lot of jobs are saying we want to see, you know,
00:52:11we want to see some experience. We want to see a portfolio. We want to see projects, but if you've
00:52:15not worked in the field before, where'd you get that practical experience from?
00:52:20Yeah, super important. Really. Um, a question that a lot of people, when they're first starting out,
00:52:24ask, they're like this job, this entry-level job needs a year experience. I don't have that.
00:52:29Hi, how do I get a year experience if I can't get a job? Um, there's a few different things
00:52:34you
00:52:34can do. And I'm going to add one bonus thing at the end, uh, which is something that, uh, I
00:52:39just
00:52:39recently have been talking about. The first thing is in the, probably the most popular and the best
00:52:45thing to do. This is the best thing in my opinion to do, which is build projects. Um, it sounds
00:52:50so
00:52:50simple, but most people don't do it. And then they miss out on the people who like hiring managers
00:52:56who value that. And I, myself was a hiring manager where I valued that, um, projects, uh, let's take
00:53:02SQL. For example, you take a big data set, you bring it in, you analyze it, you, you write some
00:53:07advanced queries. You really, you know, you really dig into it. You can put that up on GitHub and
00:53:12that's a project. And then when, you know, on your resume, you can even have a projects section,
00:53:18the project section. Um, you know, it's really great for pointing to and saying, you know,
00:53:26they're going to ask you in an interview, you know, do you know SQL? How well do you know SQL?
00:53:30If they're using SQL, they're going to ask you this. You don't want to say, Oh, I just took a
00:53:35course. That's a bad answer. It does not look good. They're like, Oh, this guy's only taking
00:53:39courses. That's not good. What you want to say is I just, you know, uh, well, this project that I
00:53:44was
00:53:44working on, that sounds so much better. This project I was working on, you know,
00:53:47I was analyzing some, some customer trends and Amazon data. Um, and you can even make projects
00:53:52that are specific to a domain. So I was in healthcare. I used to have, I still do, but I
00:53:56have healthcare projects that I used that were specific to my domain. And they would ask me,
00:54:01you know, you know, tell us about how you SQL. I could point to those projects and be like,
00:54:05you know, working with ICD codes, you know, CPT codes. Here's how I found these patterns or whatever,
00:54:10um, within, you know, diagnoses or diagnoses. And so those projects is my number one thing.
00:54:16The second thing would be, uh, before the bonus, the second thing would be, um, volunteering.
00:54:21So that's actually one thing that I did when I first started out, there's a local charity,
00:54:25um, that I just, I donated to, I really liked what they did, but then I went on their website
00:54:30and I was like, I was like, Oh boy, they're doing some like visualizations to try to showcase how,
00:54:35what, how much they're helping. I'm like, these are bad. Um, so I messaged the, I messaged the
00:54:39company just saying, Hey, I'd love to volunteer, redo this for you guys. Uh, just pro bono.
00:54:43Cause I like what you guys, your cause and they let me do it. And I put that on my
00:54:47resume and it
00:54:47was great. So that's like another thing you can do now. Here's the most recent, this is my bonus one.
00:54:53And this is the most recent thing I've kind of been getting into and really like studying and
00:54:58like looking at, which is freelancing. Um, freelancing is not something I personally ever
00:55:04did. I do consulting now, which is a little bit different, but freelancing. So going on companies
00:55:09like Fiverr offer up and, and then doing these things, it, it can be hard to find clients at
00:55:16first. That's why it's my last one. But I think in the future, um, I think freelancing is going to
00:55:22be
00:55:22a lot bigger than it is now, especially in the data world. Um, mostly due to remote work and AI.
00:55:30I think a lot of smaller companies who have never used data before are going to start wanting to
00:55:35utilize data and they're going to want little tasks done here or people to come in and kind of
00:55:39advise them here and there where freelancing will become a lot more popular because they don't want
00:55:44to hire on a full data team. They can't afford it, or, you know, they don't want to invest that
00:55:48much
00:55:48in a, in data infrastructure. So I think freelancing is something that is going to be more prominent in
00:55:54the future. Um, and it's something that can give you experience with smaller tasks, um, and smaller
00:56:00projects, just like one-off with companies. It can also lead to full-time jobs. I just talked to
00:56:06somebody the other day who I posted on LinkedIn about freelancing and someone was like, yeah,
00:56:10freelancing led to my first job. I freelanced for this company. You know, they, they gave me a shot.
00:56:14I did well freelance with them for three months and they hired me on full-time. So, you know,
00:56:18freelancing can lead to bigger things as well. So that's everything I got in this head about that.
00:56:25I completely agree as well. Um, I think our career specialists at Career Foundry would
00:56:29recommend also reaching out to your network. Everyone's got family, friends or relatives or
00:56:33friends of friends who maybe have small businesses, um, who need a little bit of help sometimes,
00:56:38whether it's with the design, the UI design, or if it's the data analytics, analytics. Um, so a hundred
00:56:44percent agree that look in your network, you will find real world projects. They're probably a little
00:56:48bit closer than you probably think. Yeah. Um, no, I agree with that. Um, Alex, let's zoom out a little
00:56:54bit here and let's look at some challenges. So, you know, what do you think are some common
00:56:58challenges or roadblocks, um, that beginners in data analytics often encounter?
00:57:05So like learning SQL or using it?
00:57:08I would say, um, just in terms of like data analytics in general, you know, when you, when
00:57:12you're starting out, what are some challenges, but also we can, we can, uh, zoom in as well
00:57:16on, um, SQL.
00:57:19Um, broadly speaking, when you're first getting into analytics, I think one of the biggest
00:57:24challenges, and this is something that I've talked to a lot of different people about.
00:57:28So it's not just my opinion, which is, um, you don't know what you don't know. When you're
00:57:34first getting started, you kind of get dropped into like a company and you don't know anything.
00:57:39Um, you know, and it's, it's really daunting to go into a company and you have to learn their
00:57:45data infrastructure, how they, how they, their processes and their workflows and, and where
00:57:50all their data is, their customers. It's, it's really tough, especially if you don't
00:57:54already have previous experience as a data analyst. Um, it's really tough. So I would
00:57:59say that's probably one of the biggest challenges, um, is just being very overwhelmed and feeling
00:58:04like you don't know anything. It's, it's, it's really tough. Um, with SQL specifically,
00:58:11I think one of the biggest challenges is you, you encounter, um, a lot, especially in
00:58:20the, in a workplace, you encounter a lot of people who know it really well, um, who've
00:58:25been doing it for 10, 15, 20 years and can write it backwards and forwards. And when you're
00:58:29first getting started, like me, when I first got it started, man, I was, I felt like the
00:58:34biggest imposter in the world. I was like, I don't belong here. Like that guy, my boss
00:58:39who at the time, my boss was like crazy good at SQL, like was writing advanced stuff.
00:58:43I'd never seen before. I was like, what is he doing? That's not SQL. That's like some,
00:58:47some different language. It was really intimidating because I just, um, I just, you know, I saw
00:58:56everyone else doing really well in it and I wasn't doing well. So I think that's one
00:58:59of the bigger challenges is, is it's in the real workplace you are in the, when you're
00:59:04practicing and learning it, you get these little small use cases and you learn everything.
00:59:08But then when you get into the real world, it's like a mountain was dropped on you, but
00:59:12you just have to like climb that mountain until you like learn enough and know enough to
00:59:16like, you know, get there. And so I think those are the biggest challenges, uh, for those
00:59:21two.
00:59:22And what do you think some of the tips are for overcoming imposter syndrome when you're
00:59:25just starting out, as you just mentioned?
00:59:28Um, yeah, I had, I had, I had quite a bit of imposter syndrome when I first started out.
00:59:33I think the biggest thing for me personally, and I'll give another advice, but that didn't
00:59:38really help me. But the number one thing that helped me was I just studied like crazy.
00:59:42I would go home after, and I don't recommend this. I was just, I really needed that job.
00:59:47Like I was pretty poor. So like, I really needed that job. So I'd go home and study the
00:59:51stuff that I was doing at work to make sure that the next day I could do a better job.
00:59:55Um, I don't do that anymore, but I used to do that. And that, that for me, I was like,
00:59:59okay, my skill level's getting better. I'm starting to do what they need me to be, need me to be
01:00:04able to do. That may give me a lot of confidence. And so, um, that for me was the best,
01:00:10the next thing. And, and I guess I kind of did this and, but it helped other people more,
01:00:15I think, which is really trying to connect with people at your work and letting them mentor you.
01:00:21Um, I did have a mentor at work, but I don't think that that helps with my imposter syndrome.
01:00:26I kept having to ask him questions and I felt bad. I was like, Hey, how do you do this?
01:00:31I don't know
01:00:31how to do this. Um, so that didn't help me, but I have heard from many people who said the
01:00:35exact
01:00:36opposite. They were like, you know, I went to my boss or I went to my coworker and I asked
01:00:40and
01:00:40they helped me. And that really helped me gain more confidence in my skills and being part of
01:00:45the team. Um, so I think everybody's a little different, but, um, yeah, imposter syndrome hits
01:00:51everybody. Uh, even at three years in, I hit it a little bit, um, for like a couple months where
01:00:57I
01:00:57was like, man, this, these people are really good. And I'm like, not at their level. Um, but you know,
01:01:03you just kind of catch up, you just got to do what you got to do to catch up and,
01:01:06and,
01:01:06and show your worth, um, and, and take on good projects and do a good job.
01:01:13I've got some flashbacks of joining a new sports team. When you kind of like look at the team,
01:01:17you think, ah, maybe not, maybe not going to be as good as the other people here. Um, there's also
01:01:22a couple of questions coming in on big market and on YouTube, um, about, you know, how essential
01:01:27is it to have statistics and also math as part of data analytics? Is it an essential
01:01:33component if you're starting out? Um, okay. All right. I'm going to be a little controversial
01:01:40here and I know this is controversial, but here's my opinion. If you know, basic math and
01:01:49super basic statistics, you can do most of what you need as a data analyst. I don't think you need
01:01:56to go crazy in depth. Now, when I first started out, I was using for most of my job for
01:02:03almost
01:02:03three or four years, I was just using averages, you know, window functions, um, with, with
01:02:10aggregations and stuff. That's mostly what I did. I was never using like, uh, any type of regression
01:02:15analysis and stuff like that, which is more math heavy. Now, as I've gotten more advanced,
01:02:22as I've gotten more into my career at a mid-level senior level, that's where I think the statistics
01:02:27and math come more into play. One of the thing is I think that there are certain domains that
01:02:32lean more heavily on statistics, like financial analysis. You're going to, if you're going into
01:02:38finance, you're going to be most likely using a lot more statistics and a lot more, um, a lot
01:02:44more math than you would if you worked with something like, like at my company or, which was
01:02:48healthcare. Um, we would do, at least for the data analyst part of our data science team, we would
01:02:56do, you know, some more advanced stuff with pretty, you know, longer calculations, but it wasn't like
01:03:01anything crazy. It was like, take this and this and this and this and divide and multiply and,
01:03:05you know, to get what we need for to get a percentage, but it wasn't anything crazy. So like,
01:03:10I think genuinely, I think that scares a lot of people, but I don't think I, me personally,
01:03:14I don't think it's as important as a lot of people say for most people. That's my caveat.
01:03:22A little bit of controversy. I am mindful of the time. Um, I will be taking some more questions.
01:03:26I'm trying to, I'm trying to group questions together because I'm see some ones which are
01:03:29coming through, which, um, are similar. Um, Alex, the industry moves very fast. How do you keep up
01:03:35to date? What are the resources that you use to kind of keep up to date with everything that's going
01:03:39on?
01:03:41Um, okay. So I, I'm, I am not everywhere, but everyone I that's around me is everywhere.
01:03:49So they send me things. I, I would say I learn a lot from getting it, having a good community.
01:03:55You know, my Alex, the analyst channel has a fantastic community. I also have like a discord
01:03:59where people will send me things all the time. And they're like, Hey, have you heard about this
01:04:03new programming language called Mojo? And I'm like, I haven't heard about, let me look into it.
01:04:08Um, I also learn a lot, believe it or not, from Twitter. Um, I, Twitter's just a, I mean,
01:04:13it's a black hole of information so you can get lost pretty quick, but at my level, I can kind
01:04:18of filter it pretty quickly. Like, I don't need to know that. Oh, that's really interesting. And so
01:04:21Twitter, LinkedIn, um, medium, um, just a good community. I would say though, I probably get
01:04:28most, not Mo, maybe like 40% from my community sending me things and, and out sending me messages
01:04:35on LinkedIn and in emails. Then I get like 30% from, or maybe 20% from LinkedIn, 20%
01:04:42from Twitter,
01:04:43and then, you know, 20% from somewhere else. Um, but that's where I get a lot of my information
01:04:48at
01:04:48least. Fantastic. And also, um, for people starting out, how essential is it to have a portfolio of your
01:04:56work? Um, it's not, it's not required, but here's what I'll say. Here's, here's kind of my like
01:05:05mentality on it. Um, and it, it'll either make a hundred percent sense or no sense.
01:05:10When I apply to jobs, I think about it in percentages. So I'm applying to a job. If I
01:05:15just send in a resume, let's say I have a 5% chance of getting an interview. But if I
01:05:22send in a
01:05:22resume and I have a portfolio, maybe I have a 7% chance. And if I send in a resume
01:05:27and this,
01:05:28and I talk to a recruiter, I have a 10% chance. I look at it in terms of percentages
01:05:32of likelihood
01:05:33of getting interviews or, or getting a job. I think anything that can improve your odds,
01:05:38you should be doing. So if that's a cover letter, you know what, do a cover letter.
01:05:42I don't do them, but do them. If you think it's going to improve your chances,
01:05:45a portfolio is going to improve your chances. In my opinion, it's just going to, even if it's just by
01:05:502%,
01:05:50but then you expand that to 300 different applications. That's very useful. That could,
01:05:56that can make the difference between getting an interview or not. So I personally really
01:06:01recommend it. I always push the people I work with. I mentor, um, you know, on my YouTube channel,
01:06:05I always push people to creating some type of portfolio.
01:06:12Awesome. Fantastic. I will just say that the key takeaway on the career foundry program is a
01:06:17portfolio that is what you will be working towards putting the projects together. Um,
01:06:21so you do have a key takeaway when you do a career foundry program, Alex, thank you so much for
01:06:27presenting this evening. I know we've run a little bit over time. I'm sorry that I couldn't take
01:06:30everybody's questions. There were a lot of questions. There are also some very specific
01:06:34questions. Um, what I would say is definitely go over to Alex's channel. Uh, there's some fantastic
01:06:40videos over there. Uh, do subscribe also, because I know that the career foundry YouTube team are in the
01:06:45background do check out the career foundry YouTube channel. Um, for anyone who is, um, interested in data
01:06:51analytics and has enjoyed this session this evening, I'm just going to share a link on big marker, but
01:06:57next week we're going to be joined by Dr. Humera, um, who is doing a skills workshop, um, which is
01:07:03going
01:07:03to be looking at Google sheets and it's taking it from the raw data right the way through to
01:07:07visualization. So I'm going to post a link there, uh, switch the slide over, um, this slide over very
01:07:14briefly. And for anyone who is interested in doing a program with career foundry, we are currently
01:07:19offering a tuition reduction of up to 17% off of our career change programs. And all you have to
01:07:25do
01:07:26is book a call with a program advisor, either on big marker, just by clicking the sticky note, or, um,
01:07:31if
01:07:31you're watching on YouTube, you'll find the link below in the description, uh, and you can book a call with
01:07:35one of our program advisors. I know the more personally, um, and they are there to answer any
01:07:40questions that you may have about the industry, um, jobs in your locality or anything about the
01:07:44career foundry program. Um, also do check out the career foundry blog and thank you so much for all
01:07:50of the questions this evening. They were really, really good and love to see so much engagement
01:07:54and Alex, thank you so much for joining us again. Uh, have we got another event coming up soon?
01:07:59Not yet, but that skills, that skills thing, skills workshop sounds fun. We should do one of those.
01:08:04We should do that. Alex, Alex, we're definitely going to have you back on the channel. I feel
01:08:07the next time, next time I'll have you back on the channel, you'll have a million subscribers.
01:08:11I feel like every time, every time, every time we do one of these, you get another 200,000
01:08:15subscribers, but, um, thank you so much for presenting this evening and thanks for everyone
01:08:19to joining and we will see you all next time. And we've recorded this. So tomorrow I'm going
01:08:24to be sending out the email with everything. Uh, so don't worry about that.