SQL Beginner to Advanced in One Hour | CareerFoundry Webinar

0:00 William events and Communications lead here at career Foundry and tonight is all about SQL or SQL it's got two ways you can pronounce it SQL 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 uh 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 without Alex's fans very much into Data cleaning um but yes tonight's all about sequel from beginner to Advanced in one hour and whilst people are joining and if you're joining on big marker just drop

0:45 your name you know 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 ER so career country is the online school for your career change into 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

1:32 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 call Link in the description below I don't want to take away the Thunder from Alex though Alex I'm passing it over to you I am going to disappear into the background but save all your questions at the end because we will be having a live q 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 answers and Alex it's over to you I'm disappearing into the into The Ether all right thank you so much I really appreciate it uh hey everybody I'm super excited to do this webinar specifically because if you know me I am

2:18 a huge huge huge sequel fan um I would consider it my bread and butter my go-to it's what I think I am one of the uh I in my tech stack at least for personally I think SQL is like one of my best skills and so um I'm hopefully going to kind of drive this uh from very beginner really the basics all the way up to some of the more advanced things that I use um we may not go crazy in depth we're not going to do doing live demos and stuff like that although that sounds really fun um 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 um if you're kind of a beginner SQL user you may never have seen some of these things and that's okay or you may not even have known that SQL does these things but I

3:04 promise you it does and I'll kind of walk through um how it works and why you might want to learn it so that you know you can progress your learning and a lot of these things that were that are in here are hopefully things that you recognize so we're gonna go ahead and get started um this is just me hopefully you know who I am but if you don't know who I am my name is Alex uh check out my YouTube channel all right let's let's get going so uh again starting at the very Basics I'm assuming that you came in here knowing nothing about SQL and if you already do then this should seem like a breeze to you um 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 want to answer some questions at the end so let's get

3:50 started what is SQL so SQL is really just the way that you query the data so there's SQL databases uh 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 um when you think programming languages you're thinking of r or python or rust or C plus plus those are more traditional programming languages um but some people call SQL programming language and a lot of what SQL 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 uh how to retrieve that data and it's used in a

4:37 lot of different things it's not just data statically or data you know just sitting in a database oftentimes it's also really Dynamic databases um when you're working with like web dev or back-end systems as well as data analysis which is mostly what I've used it for but I've also uh you know recently within the past year or two really been using a lot with some more um Dynamic databases that are a lot of moving parts and it gets pretty crazy um I say crazy but it may not be crazy to you um so we're going to start off by just kind of understanding what a database is what a table is and then we'll go from there to actually about writing SQL queries and how to actually retrieve that data so a database and a table so the database is

5:23 going to be a collection of a lot of data so a lot of tables views um it can be a lot of different things and and the um the database is what really stores everything in one central place which is great because you don't want to have to go and piece everything together from different sources you know 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 is like an Excel spreadsheet that's how I like to describe this just um 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 the screenshot here this is just a mySQL database and you can see on this left hand side we have a database called

6:09 Bakery 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 stored 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 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 want to talk a little just just a

6:56 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 uh 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 going to use and they're basically just ways of saying here are the types of um statements and functions and things you can use 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 we're altering tables we're dropping tables or dropping other things but you know as an

7:42 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 DK UL 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 uh 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 what 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 going to want most of your work is going to be is around filtering and sorting and and

8:27 kind of doing exploratory data analysis or doing you know it may not be the main uh 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 want to pull from that data for example we have a table that has 10 million rows of data in 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 you know we can you know it just helps with

9:15 um visualizing the data a little bit easier so if I was working with a price I could say I want to do it in a sending order or a 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 um and I don't want to ruin some stuff later on in the more advanced stuff but even these 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

10:02 statement this is where we're going to say here's what I actually want to see in my output so I'm saying where the salary is greater than forty thousand 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 a lead Baker at our house which is why she's the lead Baker in this 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 we're not removing or specifying what data we're pulling now we're just seeing how it's displayed in

10:48 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 uh you know as many columns as are in that table you could do every single one if you wanted to although I am positive would become completely redundant but you can filter or you can order on multiple tables or columns sorry next this is um this is I don't want to say the most important

11:33 thing for beginners but it's one of the most important things um 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 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 want to see okay you know for just this company or for each company you know what is their profits or what is uh their customer retention or all

12:19 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 want to group based off of the department now remember we had about four employees uh per departments 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 want to kind of see at a larger level you know what are all

13:07 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 uh our column there as salary and that's just an alias of that um of that output and so we're grouping by the department so the department is what's being grouped all four of those uh people who were in Bakery all four of those people who are in marketing are now put into one row and then we're allowed to put these you know these aggregate functions on them and this is just kind of the tip of the iceberg because we're going to get to

13:52 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 uh SQL I mean it's just so incredibly common to need to group data and look at aggregate functions on it um so if you're just starting out the these two things that we've just looked at which is where order by and grouping if you can learn those things three things really well that's you know 70 of what you're going to be doing with basic SQL so you get into a job as your first job as a data analyst and you know this is kind of the stuff that you need to know and so this is also really good to know um for technical interview questions as well so if you're getting you're interviewing and they ask you about

14:38 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 sub queries now from here basically to the end we have some we're going to start getting a little bit more advanced now joins and subqueries aren't crazy Advanced but when I was first starting out uh joins just like blew my mind I hadn't 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 um 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 um if we you know kind of think back to

15:25 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's not all sitting in one table oftentimes it's spread over three four or five ten 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

16:12 super important there's a lot of different types of joins and understanding them and and really using them correctly is it completely changes your output so if you want to get the right output when you join something together you have to make sure you know these joins now I'm just going to briefly go over them but this is something that you should definitely look up um 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 that's what will be in our output the left join takes everything from the left table but only takes matches from the right table that is 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 going to

16:57 make zero sense to you so I hope you go and you you know kind of dig into joins a little bit more but that's what joins are it's just how you connect data from from different um tables very very very important but then we have sub queries which is it's not joining data together but you are able to utilize other tables within sub queries and how I like to kind of say a sub queries it's a query within a query so you have your main query and then you have a little mini query within it um and a lot of people call those nested queries and so it does help with simplifying things sometimes um I'm going to take a look at the examples and I'll talk a little bit more about these because um they can be a little bit confusing

17:44 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 uh 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

18:29 bakery.customers in the from clause or the from statement um when I do Bakery dot 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 want to 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 because it SQL isn't going to know you know which table does he want it from I don't know so using the Alias and specifying I want it from the customers

19:15 table so I want C Dot customer ID this really helps with um shortening it because otherwise if you don't use that Alias of cnco then when you're selecting in the select statement it's going to be really long so you have to do customers DOT first name customer orders.lastname and just gets really really long um and it looks bad so if you shorten it to that Alias you can use that throughout all of your um statements really and all your query and it just really simplifies it makes it a lot easier so that's what joins are again getting it from different tables we have two tables here we're pulling from but now let's look at sub queries um sub queries are really interesting this one is a super simple and I formatted it to where you could really

20:00 see the sub query well but some queries are really interesting and they can get really complex but one thing I want to note because a lot of people will say well what is the difference you know what should I use a join or a sub query and just to be completely upfront I try not to use sub queries a lot um but it's important to know because there are good use cases for it but the reason I don't use a lot of sub queries especially when I'm using a ton of data is that it's a little bit slow um 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 sub query could take an extra like five or ten minutes

20:47 to run and join would take like you know a minute or something and so you just have to um and this is something we'll talk about at the end which is query optimization um and so you know knowing how things operate on the back end knowing how things um uh process and how quickly they'll go is important to to writing your queries it's a more advanced thing that you learn as you get further on in your you know SQL career career but let's look at sub queries really quickly so right here you can see uh down to the right Within These parentheses I tried to make it really obvious this is our sub query 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

21:32 from the very top we're selecting everything from bakery.customers but then we're going to say where our customer ID is in and then we have our sub query now in the sub query we're not pulling from the customers 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 ID table or from the customers 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 uh 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

22:20 to the ordered items table and you could join those together and so again it's 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 sub query and again if you're working with a small amount of data data it could be completely negligible in terms of speed it could be an extra like 30 milliseconds basically nothing but you know I I definitely want to kind of let you think of or make you think that you know when you get into the real uh a lot of companies when you work with real data and you're working with large data at any you know Fortune 500 company any tech company they're going to have an insane amount of data um and so you know if you get a job at a

23:05 smaller company it's a little data your queries might not translate perfectly over to the larger company that works of a lot more data and you know that's that's definitely something to think about let's go on to the next thing so we have Windows functions window functions are I don't want to say it's not a more advanced group buy 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 uh let me backtrack group I is great for looking at categorical data it's really great for simple aggregations but then when you get to window functions it's

23:51 like a it's kind of like a big step up in in um the kinds of things that you can do with it but like I said group I is just one of those four uh 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 start on I never used I didn't use it for probably um 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 going to talk a little bit about the difference between Group by and window function and we'll kind of look

24:36 at an output in the 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 I was already kept on so um it's hard to visualize it we'll see in just a little bit uh I hope I don't I can't remember if I gave an output in this example um but what you can really use it for is things like running totals uh ranking and finding moving averages these are things that I often use it for um and there's things like row number where you can uh give a number to every single row and you can do that over sets

25:23 of data and the window functions are not just used for you know looking at the data these are things that you know I've built entire reports automated reports and sent them to clients based off of window functions so they're like you know we need rolling totals for for this data that's easy so I just created a real uh I create a window function for that rolling total and then you know 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 um but uh here we have we're still working with this employees so we have all of our employees but typically what you do is

26:10 uh with a group by is you'd group on something like their department and if we wanted to look at their Department we would wouldn't be able to use their names because all their names are unique so with department when we use group I we put it all onto one row and we'd be able to use aggregate functions now uh in this example I I wish I had included one other one uh which would have been just like a partition um that that's my fault and I apologize but the ones that we're looking at are really interesting and these are kind of ranking um uh these are ranking functions so rank dense Rank and row number so you can see we're doing it um 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 going to

26:55 start we're going to um break it up by something now for us when we're using these right banking 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 so 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 going to give them the same number six and six and then for Carl it skips down to eight so uh these window functions are really

27:41 fantastic I wish I would have uh 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 I want I wanted one I want one more I need to go back in time and and redo that but um 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 right 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 uh different types of functions that are super useful for ranking as well

28:27 let's go into the next one give me one sec so now we have optimizing SQL queries this is um these next two or three things that we're going to talk about are a lot more advanced these are things that I I was learning um from a lot of data scientists data Engineers um who were really good at optimizing queries because you have to take a 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 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

29:13 and you're joining to tables have millions and it's 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 uh 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 uh it would add that data to our pre-existing table it was a 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

30:01 and the process that we were using um involved a lot of you know simple stuff it wasn't anything wild it wasn't anything crazy um 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 and optimize our our business rules that we made to transform that data and we would have to

30:46 make them as efficient as possible 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 um again that's much more advanced stuff stuff that you know as an entry level analyst you probably won't be working on that much um doing really intense SQL optimization but it's very important now 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 um what are some techniques that you can use uh for actually improving these speeds one really big one is 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 seven

31:32 come 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 100 times larger data than I did at the small company I was working with so I had to learn indexing uh really well and we'll um I'll talk about indexing a little bit more in a second but then we have uh you can rewrite your queries to just change the order of things change if you're using a join versus a sub query versus joining um on one column versus two columns um and then another thing and this usually isn't for a data analyst but you know this is something that would work with like a database developer one or a data architect on which is proper data modeling now how you store your data how the data actually sits in your database

32:17 makes a big difference is it the correct data type is it stored efficiently do you have the right primary and foreign Keys uh lined up you know 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 um again not something that most data analysts will use or 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 you know show your worth by by you know kind of pointing these things out saying hey this is running inefficiently because of you know how our data is being stored um yeah the last part is uh you know kind of what I was talking about in my

33:03 example which is bottleneck so we have um you know you you have a lot of data flowing in from a lot of different sources especially when a large companies you have you know 10 15 20 different sources that you're getting data um maybe the bottleneck is you know 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 uh 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 a lot to that let's look at um an example of this so I have three examples that are you know pretty simple the first is the index now indexing is when and this is

33:49 kind of a tough uh 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 going to 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 um and so that you remember it uh 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 back end and then it just goes a lot faster um to give a better example of indexing is something like um let's index well you can index anything you can index any column

34:34 multiple columns um but let's say we're indexing like a product so let's say we're and we're working with Amazon data there's a billion products on Amazon right and there there are all sorts of names and all these different things and the table that they're storing this uh this product information is it's not it's 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 going to 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 going to order all of it and that takes a long time there's

35:19 billions of products it takes a long time has to order it ordered 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 anytime you you query that product it already has it and it can select it 10 times faster um for in just like a speed example I remember creating indexes that um were very specific to what we needed that's usually what you use an index for very very specific to what we needed and they would reduce the the time for a query execution from like an hour down to like five minutes or seven minutes it was drastic um but just one more note on indexing um if you're gonna try it out or you you

36:05 know have used it before indexing is very specific to types of queries so you know really study what indexing is how it works but this is a concept that really Advanced analysts and data engineers and people they they need to understand well um for query optimization the next one is just uh querying faster now this example is super simple it's basically uh 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 uh you know behind the scenes to make them go faster that really is what um what changing your query can do

36:50 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 going to 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 my sequel um if you go to mySQL and you type in explain and then you write a query it's going to tell you you know how fast it it uh 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 it you know 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 ahead go and check that out it's it's really fun

37:37 um 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 stored 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 um in SQL but you can also just use a lot of things that are built in like server procedures are things that you can just clicks you know create stored procedure and write out what you want to have happen in that stored procedure so it's really just automating those

38:23 repetitive tasks we use this a ton uh with bringing data in so data ingestion we used a lot of data automation with data cleaning so you know dirty data is coming in from the source we write our data cleaning rules um 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 know you're grouping my data or using a Windows function on your data and your client wants to see that you know they want to see maybe a daily output of what that looks like or you know more likely like a monthly is is what um a lot of reports would be for clients and you can schedule that and automate that using something like a scheduler and a stored procedure or you know a combination of all these things but you

39:10 can get that output of the query and then you can put it into an Excel file and then send that to the client um you know that's that's automation so you don't have to manually do that um it just saves so so so much time another thing that's really helpful for and I already mentioned a few at the bottom but another thing is something like backing up your data um 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 um backups I think that's the example I used in the in the next one um but you can create backups so this right here is a super simple um automation of a nightly backup that's

39:57 all this is so every single night which is not common uh usually you do like once a quarter or something like or you know once a month depending on the company um but for this it's a nightly backup and so this event is um you know it's it's basically scheduling saying hey every day and you can specify even the day the time um Etc we're gonna We want to back that up um 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 in 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 uh in SQL

40:44 let me see yeah so these are some real world examples now I've talked about a few real world examples um but I 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 um 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 um so I worked with cancer data for years and so analyzing patient data sending that back to pharmaceutical companies to actual hospitals and saying

41:30 here are your stats here your you know survival rates these are all things I did within SQL for years um really really useful stuff um I I went a little bit beyond uh just that you can also use it for web applications something that and I'm doing right now uh which is you can use SQL to restore and retrieve data on websites and so things like Amazon and all your favorite uh 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 that's going in and getting that data and SQL databases as well as those processes and automated processes are used in these systems in these websites and these platforms the last thing and this is more for data

42:17 analysts but data 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 there's a it could go on and I could say like 10 other things that's on the top of my head the SQL is really important it's one of those skills um it and just to give you context SQL is the first thing I ever learned as a data analyst I kind of guessed I like um 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 uh 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 has been the most useful skill out of my entire career more than Excel

43:04 it's up Excel is just a little lower big sales been super useful too more than Tableau python power bi any other tool SQL has been my number one skill and so um 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 um I I do consult now I own my own business I do a lot of Consulting with companies and a lot of times I'm I'm talking them about SQL I'm talking how to you know utilize SQL better with small companies startups Etc or use the cloud to talk a lot about that but you know sql's 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

43:51 uh database or server that's in your company a lot of times now most companies are using things like um or even crms but are using things like um you know Cloud platforms and Cloud hosting platforms to store their data or cloud storage to store their data and so you know SQL is yeah I can't talk I can't say enough good things about SQL um it's just fantastic it's amazing highly recommend learning it I had to go through fast uh blame career Foundry blame William I had to go through files because we have a limited amount of time but it you know everything I went through in uh in this webinar are things that I 100 think you should learn at some point in your career so some of those things like you know the

44:37 Automation and um uh query optimization that stuff's it gets a pretty Advanced it can be tough to uh learn but I promise you it will pay out it's super super good stuff um I don't know it gets me excited that's all I got um I think William's gonna come back we'll do some q a hopefully you ask me about sequel because I love talking about SQL uh I could talk about it all day um 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 um I've got staff I'm 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

45:23 first to kick things off tonight um Alex if um if if you've got your desk and you know a data analytics um CV lands on your desk and from someone who's just starting out as a junior data analyst what 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 that's a great question um the first thing to remember is every company is different so you know when I talk about SQL I'm going to kind of give you a broader and then I'll get more specific I always talk a long time I apologize um every company is different so some companies might not even use SQL but you're looking at try to look at like the numbers what are the percentage of data company or companies that are hiring data analysts that are using SQL

46:09 it's going to be a high amount and so you want to kind of play the numbers um so SQL is a skill just to have on your resume because a lot of companies use it um but what would I be looking at let's say I'm just hiring for you know I'm pretending I'm I was a hiring manager but I'm pretending I'm at a com 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 um even some more advanced stuff and genuinely what 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 uh with a business analyst we just wanted a little bit of SQL little Excel but for data analysts we wanted a strong

46:55 SQL Foundation we would do technical tests um to make sure that was on their resume was good but we would always look for SQL we wanted to see if they had some experience um 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 learned SQL and they're part-time but they had crazy in-depth knowledge of domain knowledge um and so you know that's something to think about is utilizing your previous experience and then we always wanted to see Excel um and so you know for those types of positions we were mostly looking for the fundamental skills and then good domain knowledge um but again it varies widely

47:42 um you know if a company doesn't use SQL they may use they they have to store data somehow right maybe they're uh they use pandas or maybe or in Python maybe these 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 I'm just following up on the question here from mu um so if 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 um especially you know at companies that that's all they need if you know that well that may be all you need in fact I

48:29 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 um I because it's already new but you know as I got more advanced I was like oh I need more things this is kind of sad to look at you know there's some other core skills in there but it's definitely possible um it's just I think you know for a resume to get into the interview you know I would recommend a few more skills that showcase that you know data better the SQL Excel are absolutely fundamental fantastic skills to start out with awesome and and you know these are kind of like hard skills but um as a data analyst you know working in a career how important do you think soft skills are

49:15 um in this profession you hit me up the perfect time because literally yesterday I recorded a video on this that I'll be released in a couple weeks the perfect timing um it's 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 going to get you into that interview you could put leader you could put good communicator you could put you know good team worker whatever you want to put on your resume it's not going to get you an interview they need to know you have the hard skills so hard skills gets 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 going to sell you

50:01 um I can speak for myself I my soft skills uh 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 Shone through and I think that probably more or less got me most of my first two jobs um 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 an asylum but you can't you're usually on a team and there's clients and customers and stakeholders and and managers like

50:46 you're always committing and communicating with somebody so those those building those relationships between managers between co-workers and depending on them for certain parts of your job it's really important you have to know um you 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 your hard skills you know you that's where it becomes a lot more of a you need you need both great answer and so the video is dropping soon the soft skills video uh I think in uh maybe next week maybe two weeks wow I think this would be also a great time to just plug um Alex's YouTube channel so anyone from the

51:31 career Foundry audience anyone on Big Market do go and check out uh 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 um the voice of data analytics is Alex do subscribe to Alex's Channel um and also another shout out to Alex's fans tonight who've joined us um now a few questions have come in over on YouTube and it's a question we get asked a lot also at career boundaries so when you're just starting out in data analytics or even prior to like you know you're studying um 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

52:18 experience from yeah super important really um 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 like if I can't get a job um there's a few different things you can do and I'm going to add one bonus thing at the end uh 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 which is build projects um 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 who where I valued that um projects uh let's take SQL for

53:03 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 um 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 uh well this project that I was working on that sounds so much better this project I was working on you know I was analyzing some some

53:48 customer Trends and Amazon data um and you can even make projects that are specific to a domain so I was in healthcare I used to have how I still do but I have Healthcare projects that I used that were specific to my domain and they would ask me you know you know tell us about how you 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 um within you know diagnosis or diagnoses and so though the project says my number one thing the second thing would be uh before the bonus the second thing would be um volunteering so that's actually one thing that I did when I first started out there's a local charity um 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

54:34 how much they're helping I'm like these are bad um so I messaged the the message the company is just saying hey I'd love to volunteer redo this for you guys uh 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 um 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 offer up and and then doing these things it it can be hard to find clients at first that's why it's my last one but I think in the future

55:20 um I I think freelancing is going to be a lot bigger than it is now especially in the data World um mostly due to remote work and AI I think a lot of smaller companies who have never used Data before are going to start wanting to utilize data and they're going to 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 want to hire on a full data team they can't afford it or you know they don't want to invest that much in data infrastructure so I think freelancing is something that is going to be more prominence 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

56:05 talked to somebody the other day who I posted on LinkedIn about freelancing and someone's like yeah freelancing led to my first job I freelance for this company you know they gave me a shot I did well freelance with them for three months and they hired me on full time so you know 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 the design the UI design or if it's the date to analysis analytics um so 100 agree there look in your network you will find real world projects so probably a little bit closer than you probably think yeah um no I agree with that

56:52 um Alex let's zoom out a little bit here and let's look at some challenges so you know what do you think are some common challenges or roadblocks that beginners in data analytics often encounter it's like learning SQL or using it I would say um just in terms of like data analytics in general you know when you when you're starting out what are some challenges but also we can we can zoom in as well on um SQL um broadly speaking when you're first getting into analytics I think one of the biggest challenges and this is something that I talk to a lot of different people about so it's not just my opinion which is um you don't know what you don't know when you're first getting started you kind of get dropped into like a company

57:37 and you don't know anything um you know and it's it's really daunting to go into a company and you have to learn their data infrastructure how they how they their processes and their workflows and and where all their data is their customers it's it's really tough especially if you don't already have previous experience as a data analyst um it's really tough so I would say that's probably one of the biggest challenges um is just being very overwhelmed and feeling like you don't know anything it's it's it's really tough um with SQL specifically I think one of the biggest challenges is you you encounter um a lot especially in the in a workplace you encounter a lot of people who know it really well

58:24 um 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 in 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 sequel like was writing advanced stuff I'd never seen before I was like what is he doing that's not sequel that's like some some different language it was really intimidating because I just um 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 is it's in the real workplace you 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

59:10 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 uh 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 I had a 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 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 um I don't do that anymore but I used to

59:57 do that and that that for me I was like okay my skill level's getting better I'm starting to do what they need me to be need me to be able to do that gave me a lot of confidence and so um that for me was the best the next thing and and I guess I kind of did this and but it helped other people more I think which is really trying to connect with people at your work and letting them Mentor you um I did have a mentor at work but I don't think that that helps with my imposter syndrome I kept having to ask him questions and I felt bad I was like Hey how do you do this I don't know how to do this um so that didn't help me but I have heard from many people who said the exact opposite they were like you know I went to my boss or I went to my co-worker and I asked and they helped me and that really helped me gain more

1:00:43 confidence in my skills and being part of the team um so I think everybody's a little different but um yeah imposter syndrome hits everybody even at three years in I hit it a little bit um for like a couple months where I was like man this these people are really good and I'm like not at their level um but you know you just kind of catch up you just got to do what you got to do to catch up and and and show your worth um and and take on good projects and do a good job I've got some flashbacks of joining a

…