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