YTread Logo
YTread Logo

SQL Beginner to Advanced in One Hour | CareerFoundry Webinar

Apr 02, 2024
William's events and communications lead here at Care Foundry and tonight it's all about SQL or SQL. It has two ways of pronouncing it. SQL SQL in both directions and we are joined once again by Alex Freeberg, who is the host of the YouTube channel Alex, the analyst. and I know for a fact that there are a lot of Alex fans tonight too because this is streaming on Alex's YouTube channel, so welcome back to the channel all Alex fans. We love his energy, we love seeing his questions and we love him too. your enthusiasm for data cleansing, this is something I've seen without Alex fans very interested in data cleansing, but yes, tonight it's about the sequel from

beginner

to

advanced

in one

hour

and while people joins and if you join on a big leaderboard just leave your name, you know why you are interested in data analytics and where you are joining from, because we usually get a very international crowd and it's great to see where everyone is joining from before start, let me explain briefly, briefly. what career Foundry is who we are ER then career country is the online school for your career change to technology and we guide you from a complete

beginner

to a professional ready to work in data analytics and help you land your first job in the field, we are not old school, our programs are so flexible that you don't have to quit your day job to change careers and you receive regular one-on-one mentoring from not one but two industry professionals, so that's a mentor and a tutor, that's our dual tutoring model and If you don't get a job within 180 days of graduation, we'll refund your tuition in full, so that's our job guarantee.
sql beginner to advanced in one hour careerfoundry webinar
If you have any specific questions about the Foundry degree or the curriculum or anything that we offer, our data analytics program, I highly recommend it. book a call with a program advisor, so if you join us at Big Market, you can just click on the sticky note and if you're watching Alex's live stream, there's a link to book a call on the description below. I don't want to take the Thunder away from Alex, although Alex, I'll pass it on to you. I'm going to fade into the background, but save all your questions at the end because we're going to have a live q that I'm looking forward to. ask your questions on YouTube big market and Linkedin and we'll get those answers and Alex, it's all up to you.
sql beginner to advanced in one hour careerfoundry webinar

More Interesting Facts About,

sql beginner to advanced in one hour careerfoundry webinar...

I'm disappearing into The Ether, okay, thank you very much, I really appreciate it. Hey everyone, I'm really excited to do this

webinar

specifically because if you know me, I'm a huge fan of sequels. I would consider it my bread and butter, my go-to. It's what I think I'm one of the uh in my tech stack, at least personally. I think SQL is one of my best skills, so hopefully I'm going to push this, from the beginner basics to some of the more

advanced

things I use, we might not go crazy. depth, we're not going to do live demos and stuff like that, although that sounds like a lot of fun, but what we will do is we'll do a lot of examples, so I'll explain why you need to know. this and as it gets more advanced, if you are a beginner SQL user you may have never seen some of this stuff and that's okay, or you may not have even known that SQL does these things, but I promise you that makes them and I'll explain how it works and why you might want to learn it so you know you can progress in your learning and a lot of these things that were here are hopefully things that you recognize so let's go ahead and start, this It's just me, I hope you know who I am, but if you don't know who I am, my name is Alex, check out my YouTube channel, okay, let's get started, so again, starting from the basics.
sql beginner to advanced in one hour careerfoundry webinar
I assume you came here without knowing anything about SQL and if you already know it then this should seem very simple to you, but of course, it will progress now. I only have an

hour

or a little less, so I'm I'm going to try to go pretty fast because I also want to answer some questions at the end, so let's get started, what is SQL? SQL is really the way that data is queried, so there are SQL databases, which we'll get to, I think. on the next slide a little bit, how the data is stored, but then you have SQL, which is the structured query language and I wrote a programming language, but I know it's really controversial, it's more of a query language, um, when you think about it. in programming languages. you're thinking about r or python or rust or C plus plus those are more traditional programming languages ​​but some people call SQL programming language and a lot of what SQL is used is just a standard way of interacting with your data, it's just a way for anyone who's using it to be able to write and reproduce exactly, uh, how to retrieve that data and it's used in a lot of different things, it's not just static data or data that's in a database, a lot of times it's also databases. really dynamic data, um. when you're working with web development or back-end systems, as well as data analysis, which is primarily what I've used it for, but also, you know, recently, over the last year or two, I've really been using it a lot. 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 for you so we'll start by understanding what a database is and what a table is. es and then we'll go from there to writing SQL queries and how to retrieve that data so that a database and a table, so the database is a collection of a lot of data, so a lot of tables look, um.
sql beginner to advanced in one hour careerfoundry webinar
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 want to have to go and put everything together from different sources, you know, you put all those things together. sources and then you put it into these databases, so these tables are what's inside a database and the table is like an Excel spreadsheet, that's how I like to describe this, it's just columns and rows of data with different data types and that's really it. Now it's on this screenshot here. I try to make it as big as I can.
In the screenshot here, this is just a MySQL database and you can see on this left side that we have a database called Bakery and then we have tables below it, so these. They are all different tables within a database, so you can store 10 20 30 50 100 different tables within a database. Now underneath we also have things like stored procedure views and functions and other things, but we'll talk more about that. near the end because some of that stuff is a little more advanced, but that's what it is and as you can see here, I have this very simple query that says select everything from this database and table and then we have a little result.
Now we'll get to that a little bit, but just to prepare you for what's coming when we actually start querying or learning how to query, but that's where the data is stored next. I want to talk a little bit, just a little note. just a little note about dql ddl and DML now, when you're actually using them, knowing that these are not the most important thing, actually there are many others as well, but most data analysts don't use them, uh, or not. use the other ones, these are mainly the ones that most data professionals data scientists data analysts business analysts these are the ones that are going to use and they're basically just ways of saying here are the types of statements and functions and things you can use that you will use inside 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 are deleting tables. or delete other things, but you know, as an example, then we have DML.
Now this is where you are inserting, updating and deleting from your table and you can see a little example here of how they are used, so 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 move on to the next one, so now let's get it. So far I've been going pretty fast. I'm going to start slowing down a little bit because This is where everything starts to get, which in my opinion is 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, you usually do it.
They're going to want most of your time. The work will be filtering and sorting and doing exploratory data analysis or knowing that may not be the main part of what you do, but you will have to do this, so knowing how to filter and sort your data is extremely important, so That filtering only allows you to specify exactly what you want to extract from that data, for example we have a table that has 10 million rows of data in 100 columns, which is a lot of data you probably don't need. all of that, then what filtering allows you to do is say, "Okay, I just want these three columns and I only want them where it's equal to this or where it includes this type of information so that we can specify what we're pulling the ranking from. the data". just arranging it so it looks a little better in the output or changing the format, which you know, we can tell just helps visualize the data a little easier, so if I were working with a price, I might say I want to do it in a shipping order or in a descending order.
You might look at the lowest price first or the highest price and a lot of these things are very simple, but as you go they become very, very important. and I don't want to screw up some things later on in the more advanced stuff, but even these little things of just sorting the data actually have big implications on how the data is exported or how the data is placed into products and all these different things. so filtering and sorting data is very important. Here's just a quick example of data filtering. In this database we're looking at bakery employees and in this statement where, this is where we're going to say, this is what I really want to see. my output, so I say the salary is over forty thousand and the department is equal to Bakery, so here we can see that all the salaries are over 40,000 and then we have our department column and that's just the bakery, so you can I also see my wife, she's the main baker there, she's the main baker in our house, that's why she's the main baker on this board.
Now we also have sort data and sort 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 just says okay, I've got my data. I don't want to filter them because we are not removing or specifying them. what data we're getting now we're just seeing how it's displayed in the output so we're sorting on this one we're doing department so we can specify that we want the department in ascending order which means lowest to highest or it means in alphabetical order of the a to z so here we are doing it in the department first we have Bakery because that's B and then marketing and then of course we have the title so we can order in multiple columns and you can do uh ya you know, as many columns as there are in that table, you could do every single one of them if you wanted, although I'm sure it would become completely redundant, but you can filter or you can sort on multiple tables or columns, sorry, next this It's um, this is me.
I don't want to say the most important thing for beginners, but it is one of the most important things: grouping and aggregating data, so let's talk about what it is and then we will see an example, so grouping data refers to combining similar values. into a column and then we can perform something called an aggregate function and these things should be something you've heard of before. If you've ever used Excel, you've heard of things like sum, average, or median of the mean. These types of aggregate functions are things that can be applied to those groupings and so they are really very important because, especially with categorical data, you have a lot of data and you want to see it well, you know, just for this company or for each one . company, you know what your profits are or what your customer retention is or all of these different things, you can look at it at a much larger level than just the individual row level, which can give you just a small snapshot of the data, so which is really cool. for creating reports, statistical analysis and especially when you are trying to look at really large data sets with only millions of rows it is extremely important to know how to group and aggregate data so let's take a look at this example so in our employees table we want Group. based on the department now remember we had about four employees, per department there were four employees in the bakery, I think four in marketing and we just wanted to see the average salary now before, when we looked at it,we were looking at each person had a salary and that's great, but we want to look at a broader level, you know what all their salaries are combined, what the average is, that's what grouping allows us to do, so we're grouping for you . we can see the group by, we are grouping by department, but above we also have to select the department and then use our aggregate function and for this we just use average, so we say average salary and then also naming our column there as salary and that's just an alias of that um that output and then we're grouping by department so that the department point out what's being grouped is that the four people that were in Bakery, the four people that are in marketing are now placed in a row and then we are allowed to put these aggregate functions on them and this is kind of the tip of the iceberg because we are going to get to some more advanced things that also involve grouping data, but in its simplest form this is something that I personally use every time I use SQL.
I mean, it's incredibly common. You need to group data and see aggregate functions on it, so if you're just starting out with these two things that we just looked at, which is where to sort and group, if you can learn those three things very well, that's you. . you know 70 of what you're going to do with basic SQL, so you get a job as your first job as a data analyst and you know this is what you need to know, so it's also very good Get to know me for technical questions too the interview, so if you're in the interview and they ask you about these things, this is something you should know, let's move on to the next one, let me take a sip of water real quick and the next thing. it's joins and subqueries now, from here basically all the way, we've got some, let's start getting a little more advanced, now joins and subqueries aren't crazy.
Advanced, but when I started, the joins blew me away. I hadn't understood it at all. I did not understand. I just didn't understand it. It didn't connect with me for some reason and um, I failed a lot of technical interviews because I didn't know things like joins and subqueries, so let's talk about what they are, joins are fundamental, um, if you know, remember that schematic we had before with our base of data and then we had 10 different tables, imagine you can only use one table at a time that would be horrible because your data is not all in one table, it is often spread across three, four or five, ten different tables and you need to be able to combine them to get the result you need, so joining is the way you actually do it.
So, join combines the rows of two or more tables based on related columns. Let's say we have our employee id from the table we were working with and then, excuse me, we have the employee id in another table, we can join them together. together based on the employee ID, which I'm sure we'll see in the next example, but join is super important, there are many different types of joins and understanding them and actually using them correctly completely changes your result, so if you want to get the correct result when you join something, you should make sure you know these unions now.
I'm going to go over them briefly, but this is something you should definitely look into and see how to do first. we have inner joins and that's when the data from both columns from the different tables when there is overlapping data, that's what will be in our output, the left join takes everything from the left table but it only takes matches from the right table. Otherwise, for a correct join, you take everything from the right table and then only what matches from the left table. If you don't know the combinations, that won't make any sense to you, so I hope you go and know how to do your research. into joins a little bit more, but that's what joins are, it's just how you connect data from different tables um, very, very, very important, but then we have subqueries, which are not joining data, but you can use other tables within the subqueries. and how I like to say that a subquery is a query within a query, so you have your main query and then you have a little mini query inside of it, um and a lot of people call those nested queries, so it helps simplify. things sometimes, um, I'm going to take a look at the examples and talk a little bit more about these because, um, they can be a little confusing, but let's see, there we go, the joins, so this is our join. so let's look at the syntax very quickly, first we select which columns we want from our two tables and then we say we want to join the customers table to the customer orders table and then we have to specify which columns are the same or which columns have data similar in these two tables, so now we say that from the customers table we are taking the customer ID and from the customers orders table we are also taking the customer ID, that's what it partly is, so we are specifying which columns we're bringing in now as a little added bonus, uh, and this is something that once you start using joins, you'll do it every time the little bonus I wanted to add here is when I bake. the from clause or the from statement um when I do Bakery dot customers I use a C alias and then when I do the join on customer orders I use Co so I'm aliasing them now why am I doing so well if you want? to specify a column in your actual select statement, I'm selecting this column and it happens to be in both tables, you have to use that column, you have to specify which table you're pulling it from, so for example I didn't. is in this one, but there is a customer ID in both tables, so I can't just put the customer ID in the select statement because SQL won't know, do you know which table it wants it from?
Don't know. So using the Alias ​​and specifying I want it from the customer table, so I want the C Dot customer ID, this really helps shorten it because otherwise if you don't use that cnco Alias, when select in the select statement, it will be going to be very long, so you have to make the customer orders with the DOT name. your query and it really simplifies, makes it a lot easier, so that's what joins are, again getting it from different tables, we have two tables here that we're pulling from, but now let's look at subqueries. Subqueries are really interesting.
This one is super simple and I formatted it so that you could actually see the subquery well, but some queries are really interesting and they can get very complex, but I want to point out one thing because a lot of people will say well, what's the difference? What should I use a join or a subquery? To be completely honest, I try not to use subqueries often, but it's important to know because there are good use cases for it, but the reason I don't use it much. Subqueries, especially when I'm using a ton of data, it's a little slow and joins aren't really for everything, but 99 of the time joins are going to be a lot faster, so just with speed because I worked with a large amount of data like there were tens of millions or 100 million rows of data using a subquery that could take an extra five or ten minutes to run and join would take like you know a minute or something so you just have to um and this is something we'll talk about at the end of what query optimization is, and so you know how things work on the back end, knowing how things are processed and how fast they're going to go, is important for writing your queries, it's something else. advanced. you learn as you go in your SQL career, but let's look at the subqueries very quickly, so here you can see, bottom right.
Inside these parentheses I tried to make it really obvious, this is our subquery and what This is a full SQL statement or a full SQL query, but it's inside an outer query, so let's start from the top, select everything from bakery.customers , but then we'll say where our customer ID is and then we have our subquery now in the subquery we're not pulling from the customers table, but from a completely different table called ordered items, so now we're looking at which customers have actually ordered items in that items ordered table and then we say we want customer ID from the customer ID table or from the customers table where there is also a customer ID in the items ordered table so in our output we can see that all these people are people who have actually created or bought something and ordered items in that other table, so this is also something you could do with a join, you could just join the customers to the ordered items table and you could join them and again, it's very specific. your use case, but in this case I would definitely use the combination.
I just wanted to show that you can 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 you know, I definitely want to let you think or make you think that you know when you enter reality, many companies when you work with real data and I'm working with large amounts of data in any Fortune 500 company that I know, any technology company, they are going to have an incredible amount of data, for What you know is that if you get a job at a smaller company, there's little data that your queries might not translate. perfectly to the larger company that works with a lot more data and you know, that's definitely something to think about, let's move on to the next thing, so we have Windows function window functions.
I don't want to say that it isn't more advanced. Group purchasing is different but has more functionality and you can do. I think you can do more with it, but it's definitely more advanced and doesn't have as many. Let me back up. Group I is great for viewing categories. Data is really great for simple aggregations, but then when you get to window functions, it's like a big step forward in the kinds of things you can do with it, but like I said, group I is just one of those. four fundamental concepts that you use for a lot of different things, but then window functions have some very specific use cases and we'll look at an example in a moment and window functions are something that when I started I never used.
I didn't use it for probably even six months at my job just because I was working with simpler things, but a lot of different use cases came up where I was like, oh man, I can't do this with Group by. it just doesn't work or my output doesn't look 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 about the difference. between Group By and the window function and we'll see a result on the next slide, but what a window function does is that you can do these calculations and these aggregations very similar to how you do with a group, but you do it on a window or a subset of the data and then in its output it's not grouped, it has each individual row that it was already saved in, so it's hard to visualize, we'll get to that in a moment, I hope not.
I don't remember if I gave a result in this example, but what you can really use it for is things like running totals, sorting, and looking up moving averages, these are things I use it for often, and there are things like row number . where you can give a number to each row and you can do it on data sets and window functions are not just used to view the data, these are things that you know. I've created full reports, automated reports and sent them to clients based on window functions so they say we need running totals for this data, that's easy, so I just created an actual window function for that running total and then you know I send it. as a report, it's very common, it's a very 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 to have this in the result, but here we have it.
We're still working with these employees, so we have all of our employees, but typically what you do is, with a group, you group yourself into something like their department and if we wanted to see their department, we wouldn't. we can use their names because all their names are unique, so with department, when we use group I, we put it all in one row and now we could use aggregate functions. In this example, I wish I had included another one. which would have been like a partition, um, that's my fault and I apologize, but the ones we're looking at are really interesting and they're kind of sorting, um, uh, these are sorting functions, dense sorting, sorting and row number so you can look, we're doing it, um, let's look at the range, so we select the name and the salary, and then we're looking at the range.
Now when we use this, we're going to start, we're going to break it down into something. Now when we use these right banking functions, we don't have to split them, so we just use sort by, so we sort by salary in descending order, so we take the highest salaryfirst and down. to the lowest salary and you can see that in our output we have the salary name and then our salary range. Now this will sort our salary based on the order we put it in, so we sort by salary so the salary range goes one two three four six six eight so we had a match between Dwight and Tom at 45,000 and because they had the exact same range will give you the same number six and six and then for Carl it jumps to eight, so these The window functions are really fantastic.
I wish I had done it. And this is my fault. I apologize, but I have a lesson in this. You can see it on YouTube, but window. The functions are really cool to then partition data, so again, I want, I want one, I want one more, I need to go back in time and redo that, but, if we compare directly to the group when we we grouped on Department, we had to average the entire department and then the department only had one row, with the window functions we were able to 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 end there is a column that has the average and that is actually very useful and then of course there are these different types of functions that are also super useful for sorting.
Let's move on to the next one, give me a second, so now we have SQL optimization. queries, these are the next two or three things that we're going to talk about are much 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 work with small data sets, optimizing queries does nothing, you can change it from 50 milliseconds to 49 milliseconds, it's negligible, but again you have to imagine that you are working in a large A technology company has millions, if not billions, of rows of data and you join tables, you have millions of them and it gets really complex, so optimizing SQL queries is really used to improve the efficiency of how quickly things run and why is that? important?
I'll give you an example: we used a previous 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 a whole process to get the data from The Source that we cleaned. the data using all the transformation rules we had created and then add that data to our pre-existing table. It was a complete process and it was executed. I think every night, well, this was one of our biggest clients. of our largest clients and this was really very important and the process that we were using involved a lot of you know simple things, it wasn't anything wild, it wasn't anything crazy, but there were some things that were taking a lot longer to execute, so I would go to our data engineer and we would look at all these rules that we made to transform 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 work right, which What would happen is that those doctors would look at the data in the morning and it wasn't there, then they would call us and get angry, so it had to be executed. a specific amount of time to optimize these queries and optimize our business rules that we created to transform that data and we would have to make them as efficient as possible, we reduced it to about four hours, it was a lot of data, it was a big process, but we reduced it to about four hours, so that's the difference between a client being really happy and really unhappy, um again, these are much more advanced things that you as an entry level analyst probably won't know about.
I'll be working on it, doing some really intense SQL optimization, but it's really important right now, so if you're just starting out, keep this in the back of your head when you're working with this stuff, so what are some techniques you can use? ? can I use uh to really improve these speeds, a really important one is, especially with big data, indexing, indexing, I never worked with it until I started working at this company called Amerisource Bergen, like a fortune seven, it's like the number seven in Fortune. 500 companies or Fortune 500 list. I had never heard of it, but we worked with data 100 times bigger than what I had at the small company I was working with, so I had to learn how to index very well and we'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 whether you're using a join query versus a subquery versus join um on one column versus two columns um and then Another thing and this is generally not for a data analyst, but you know this is something that would work with a database developer or a data architect about what is appropriate data modeling, now how do you store your data, how do you actually placing the data in your database makes a big difference if it's the right data type if it's stored efficiently if you have the right primary and foreign keys lined up you know there are a lot of different things that go into data modeling and creating a good data architecture and that can play a big role as well, again, it's not something that most data analysts will use or need to know right away, but I think as you get to the higher level or to the lead analyst role, knowing how these things work can make a big difference and do you really know how to show your value by pointing out these things saying hey, this is being executed inefficiently because you know how our data is stored?
Yeah, that last part is, you know, what I was talking about. My example is a bottleneck, so we have, you know, you have a lot of data flowing from a lot of different sources, especially when you have large companies, you know 10, 15, 20 different sources that you're getting data from, maybe the neck of bottle. You know everything is being funneled into one place at a time and that's your bottleneck? So you have to stagger when you're entering data and get that process done correctly so you're not trying to get everything in. at the same time that can be a big bottleneck but there's a lot going on in that let's look at an example of this so I have three examples that are pretty simple the first one is index now indexing is when and this is kind .
It's a hard concept to keep out of my head, but I'll do my best. Indexing is where you say to SQL, you say, "Hey SQL." I'm going to use 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's not stored in the table like this, I want to index this customer name and so you remember it behind scene and then when you ask a query. customer name, you already remember, you don't have to process it in the query, you are already processing it in the back-end and then it goes much faster, to give a better example of indexing, it's something like let's index well. you can index anything, you can index any column, multiple columns, um, but let's say we're indexing as a product, so let's say we're working with data from Amazon, there's a billion products on Amazon and there's all kinds of names and all these different things and the table that are storing this product information is not based on the product id, so it is sorted based on the product id, if we then create an index on the product name and that is stored in the part back is organized when we then run a query based on the product name, you will be able to retrieve that data faster because you already have it stored in order in the back, which you might have to do if you don't have an index, it will go into your memory, it will sort everything and that takes a long time, there are billions of products, it takes a long time, you have to sort it, sort it, then you have to go through again and then select the one you want so that all the work that is being done when you actually do it query can be done with an index beforehand and then every time you query that product, you already have it and you can select it 10 times faster, um for in a speed example, I remember creating indexes that were very specific to what we needed , that's generally what an index is used for that is very, very specific to what we needed and would reduce the execution time of a query by about an hour less.
Like five minutes or seven minutes, it was drastic, but just one more note on indexing, if you're going to try it or you know you've used it before indexing is very specific to query types so you know to really study what It is indexing. That's how it works, but this is a concept that really advanced analysts, data engineers, and people need to understand well to optimize queries. The next one is simply querying faster. Now this example is super simple. Basically, it could have been written. like a subquery but instead we used a union and the union was faster so that's really all it is, sometimes it's as simple as just changing the query a bit knowing how things are processed you know behind scene how to do them. go faster, that's really what can change your query and then the last thing and this is more of a bit of a tip because we'll get to the next one is automation, but we'll get to this.
The next thing is that you can analyze the performance of a query using explain and if you have never done this, do it especially in my sequel. If you go to MySQL and type explanation and then type a query, it will tell you. I know how fast it took, how many rows of data you had to go through. I use this to explain a lot with indexing or other things because indexing can show you, oh, you know, you just had to look at 3,000 rows of data instead of having to look at 100,000 rows of data so again it can explain why things are going wrong. speeding up what type of indexing it's using, go ahead and check it out, it's a lot of fun to play with.
I enjoy it. It sounded very nerdy, but it's true. Okay, let's look at automation now, when I talk about automation, a lot of different things may come to your mind, but in SQL you generally use things like scripts, stored procedures, schedulers, these are things that are probably built in, although I would . I don't think the kind 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 use a lot of things that are built in, like server procedures are things that you can just do. click, you know, create a stored procedure and write what you want to happen in that stored procedure, so it's really just about automating those repetitive tasks.
We use this a lot for data ingestion, so we use a lot of data automation for data ingestion. data cleaning so you know that dirty data is coming from the source, we write our data cleaning rules in our queries to clean that data and then as it comes in it is automatically cleaned and placed where it needs to be placed, it's amazing another stuff. you're 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 result of what that looks like or you know more likely Like a monthly, that's what a lot of reports would be for clients and you can schedule it and automate it using something like a scheduler and a stored procedure or you know a combination of all of these things, but you can get the result of the query. and then you can put it in an excel file and then send it to the client.
You know, that's automation, so you don't have to do it manually. It just saves a ton of time, another thing that's really helpful to me. I already mentioned a few at the bottom, but another thing is something like backing up your data. This is beyond what a data analyst should probably do, but in a small company you might want to back up your database and make sure if your database or your server goes down, you have a Backing up that data is very important so that you can create backup copies. I think that's the example I used in the next one, but you can create backups, so this here is a super simple one night automation. backup, that's all, this is done every night, which is not common.
Typically you do it once a quarter or something like that, or you know, once a month, depending on the company, but for this it's a nightly backup, so this event is, you know. It's basically scheduling saying hello every day and you can even specify the day and time, um, etc., we want to backup that and put a copy of a database here in a safe location where, if If something happened, we would still have our data in our business, we could still be really important and then down below it says: do this, create the database, create the table, create that and then you can still automate it.further.
I mean, it's really almost infinite what you can do. SQL, let me see, yeah, so these are some real world examples. Now I've talked about some real world examples, but I wrote some too, so a lot of the things that just SQL are not crazy advanced things, but just writing. Simple SQL queries, can be used for things like managing your inventory, making sure you have the products you need in a business to track your sales. I've done a lot of sales tracking, 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 analyzed patient data by sending it to pharmaceutical companies to real hospitals and saying here are your statistics here, you know, survival rates, these are all things I did within SQL for years, really very useful things, I went a little further, only you can also use it for web applications, something I'm doing right now. which is, you can use SQL to restore and recover data on websites and 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 recover almost immediately . when you click on something that's coming in and getting the data and the SQL databases as well as those automated processes and processes to be used in these systems, in these websites and in these platforms, is the latest and this is more for the data analysts, but data analysts can use it a lot for generating reports. just to get insights, do exploratory data analysis, calculations, reporting, data cleaning, data manipulation, I could go on and I could say like 10 other things that are off the top of my head, SQL is really important, it's one of those skills, um , and just to give you context, SQL is the first thing I learned as a data analyst.
I figured I like it. I looked up and thought what do data analysts need to know and SQL came up and I said, let me try to learn that. It is true that it was very difficult for me to learn, but I can tell you that throughout my career, that is what has made me the most money, it has been the most useful skill of my entire career, more than Excel, it depends on Excel . a little bit lower great sales have also been very useful, more so than Tableau python power bi, any other tool. SQL has been my number one skill, so oh man, I got it done in time.
I wasn't sure if I was too close to one. 'watch, but SQL has been my number one skill and even today, I now do queries. I own my own business. I do a lot of consulting with companies and often talk to them about SQL. How do you know how to best use SQL with small startups? Etc. or using the cloud to talk a lot about that, but you know SQL is even portable to the cloud, where I used SQL a lot in Azure and AWS with cloud databases, so SQL is not. It's not just a database or a local or local server that's in your company a lot of times now most companies are using things like um or even crms, but they're using things like um, you know, cloud platforms and cloud hosting platforms to store. your data or cloud storage to store your data and then you know SQL is yeah, I can't talk, I can't say enough about SQL um, it's just fantastic, it's amazing, I recommend learning it.
I had to move on quickly, uh, blame the race. Foundry blames William. I had to go through the archives because we have a limited amount of time, but you know, everything I went through in this

webinar

are things that I think you should learn at some point in your career, so some of those things. like if you know automation and query optimization, that gets pretty advanced, it can be hard to learn, but I promise you it will pay off, it's super, super cool stuff, I don't know, it excites me, that's all. I have um I think William will be back we'll ask some questions hopefully you ask me about the sequel because I love talking about SQL uh I could talk about it all day um amazing Alex thank you so much for presenting a very insightful presentation and Many questions.
I got there, but for anyone who has some lingering questions about SQL or anything related to data analysis, now is the time. I have staff. I'm also on YouTube and Linkedin, so post your questions there too, we'll try. and filter them I have a couple of questions first to start tonight um Alex yes um if you have your desk and you know data analysis um CV comes to your desk and it's from someone who's just starting out as a junior data analyst, what kind of programming languages ​​you would like to see, what would be the main ones you would like to see on that CV, yes that is a great question, the first thing to remember is every company is different, so when I talk about SQL I will give you more perspective broad and then I will be more specific.
I always talk for a long time. I apologize. Every company is different, so some companies may not even use SQL. but you're trying to look at the numbers, what is the percentage of data companies that hire data analysts that use SQL? It's going to be a high amount, so you want to play with the numbers. SQL is a skill you should have on your resume because a lot of companies use it, um, but what would you be looking at? Let's say I'm just hiring, you know, I'm pretending I'm a hiring manager, but I'm pretending I'm at a communications company from my old company.
I was hiring for the position I used to have as a data analyst. We use a lot of power from SQL Excel. We were in the Microsoft store. power bi Azure python um even some more advanced stuff and Really, what what? When I was on the hiring team, we were hiring data analysts, business analysts, and we really just wanted a solid SQL foundation that we wanted. With a business analyst, we just wanted a little bit of SQL, a little bit of Excel, but for data analysts we wanted a solid SQL foundation we would do technical testing to make sure your resume was good, but we were always looking 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 hired nurses who were nurses for 20 years but we really needed someone who knew SQL so they like to learn SQL and they are part time, but they had an incredibly deep understanding of domain knowledge etc.
You know, that's something to think about, is using your previous experience and then we always wanted to look at Excel, so you know, for those types of positions, we were mainly looking for foundational skills and then good domain knowledge, but again, it varies. a lot. you know, if a company doesn't use SQL, they might use, they have to store data in some way, maybe they use pandas or maybe or in Python, maybe these pandas, maybe they just use Excel or some CRM database that No. use SQL, but your database knowledge with SQL is still transferable to a lot of that stuff and you have experience using data, so I'm just trying to tie it into SQL.
I'm just following the question here from mu um, so if it's that kind of CV comes to your desk and the person only knows Excel and SQL do you think they have a good job? Good chances of getting a job in the market. It's possible. It's possible. Especially in companies, you know that's all they need. If you know it right, that may be all you need, in fact, I mean, maybe I know it for sure because the first job I was hired for I only had SQL and Excel on my resume, because it's already new, but you already know.
As I went on, I thought, oh, I need more stuff. It's a little sad to see. You know there are other basic skills in there, but it's definitely possible. It's just that I think you know that a resume can get into the interview. I know I would recommend a few more skills that show you know your data better. SQL Excel are absolutely fundamental and fantastic skills to get started with. You know they are hard skills, but, as a data analyst, you know you work in a career. How important do you think soft skills are? In this profession you hit me at the perfect time because literally yesterday I recorded a video about this that I will publish in a couple of weeks.
The perfect moment. That is, there is a whole process. you have you are trying to get a job so you can get into an interview your hard skills are what will get you to that interview you could put leader you could put good communicator you could put you know good team player whatever you want to put on your resume, they won't get you an interview, they need to know that you have the hard skills, so the hard skills get you into the interview and then in the interview, the hard skills again, they will push and become a little less important. in the actual interview, because they already know you have it, they are simply confirming you, but your social skills are what will really sell you.
I can speak for myself. My soft skills probably got me most of my jobs when I was starting out because I didn't have a lot of hard skills so I was very motivated, I was very excited to work my way into the field so I think that shined through and I think that probably more or less it helped me more. of my first two jobs, um, in the real job are the most important hard skills or soft skills. I think hard skills are a little more important. You know you have to be able to do your job, but this is where soft skills come into their own. play into your real job you have to know how to work with people because you don't work in a silo.
I feel sorry for all those introverts out there. I also thought you could work in a nursing home but you can't, usually in a team and there are clients, customers, stakeholders and managers like you, they are always engaging and communicating with someone, so those who build those relationships between managers , co-workers and you depend on them for certain parts of your job, it's really important that you have to know, 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, they can really affect your hard skills, you know, that's where it becomes a lot more that you need, you need both, a great response, so the video will be released soon, the soft skills video uh, I think maybe next week maybe two weeks wow, I think this would also be a good time to just plug Alex in.
YouTube channel for anyone in Foundry's professional audience, anyone in Big Market, to go and check out Alex's channel. Alex, the analyst on YouTube. I think when we started doing webinars together, I think it was around 200,000 and now. You have over 500,000, so the voice of data analysis is Alex. Subscribe to Alex's channel. And also another shout out to Alex's fans tonight who joined us. Now some questions have arrived on YouTube and it's a Question we get asked a lot also in the professional boundaries, so when you're just starting out in data analysis or even before you know you're studying, how do you get hands-on experience?
You know many jobs say we want to. to see you, we want to see some experience, we want to see a portfolio, we want to see projects, but if you haven't worked in the field before, where did you get that practical experience? Yes, super important, really, a question that a lot. Of people when they first start ask how they like this job, this entry level job needs one year of experience. I don't have that, how do I get a year of experience? If I can't get a job, um, there are some. different things you can do and I'm going to add one extra thing at the end, uh, which is something I've been talking about recently.
The first thing is and probably the most popular and the best thing to do, this is the best. In my opinion, what you have to do is create projects. It sounds really simple, but most people don't do it and then they miss out on people who like hiring managers who value that and I was a hiring manager myself who valued that. um projects uh, 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 know, you really dig into it, you can put it on GitHub and that's a project and then when I know that on your resume you can even have a projects section, the projects section.
You know, it's really cool to point and say that you know what they're going to ask you in an interview. Know? Do you know SQL? How well do you know SQL? they're using SQL they're going to ask you this don't you mean oh I just took a course that's a bad answer it doesn't look good they say oh this guy is just taking courses that aren't good what you What I mean you know very well, this project that I was working on sounds much better, this project that I was working on, you know, I was analyzing some trends ofslide very briefly and for anyone interested in doing a program with Care Foundry, we are currently offering a tuition reduction of up to 17 on our career change programs and all you have to do is book a call with the program advisor on bigmarker by simply clicking on the sticky note or if you are watching on YouTube you will find the link below in the description and you can book a call with one of our program advisors.
I know more personally, and they are there to answer any questions you may have about the industry, jobs locally, or anything about the Foundry career program. Also check out the Foundry career blog and thank you so much for all the questions tonight. They are really very good and they love to see so much commitment and Alex, thank you very much for joining us again. Do we have another event coming soon? Not yet, but that skills, skills, skills workshop sounds fun, we should do one of those. we should do that Alex Alex we're definitely going to have you back on the channel I feel like next time I'll have you back on the channel we'll have a million subscribers I'm sorry every time we do one Of these you get another 200,000 subscribers but thank you so much for present tonight and thank you all for joining and we'll see you next time and we've recorded this so I'll send the email tomorrow. with everything so don't worry about it

If you have any copyright issue, please Contact