Data Analyst Bootcamp for Beginners (SQL, Tableau, Power BI, Python, Excel, Pandas, Projects, more)

Key Takeaways

you are in the right place to learn to become a data analyst in this massive boot camp Alex the analyst will cover all the core topics that data analysts need to know and along the way you'll build plenty of projects to gain hands-on experience hello everybody my name is Alex freeberg better known as Alex the analyst on YouTube and in this video you're going to be taking my entire data analyst boot camp this boot camp is comprised of videos that I've made over the past 3 years and they cover a lot of different topics like SQL Excel powerbi tableau and python throughout the boot camp there are a lot of Hands-On guided projects that will really help you learn these skills well and speaking of projects there's an entire Part near the end where you can build a free portfolio website where you can put all of your projects on so that hiring managers and recruiters can go and look at all these projects that you've built if you wanted to go even more in depth into the skills that we learn in this boot camp I have a data analytics learning platform called analyst Builder analy Builder was designed specifically for data analyst so all of the courses and all the content are just for you and it has a coding section where you can learn and practice for technical interviews and lastly before we jump into the boot camp I want to give a huge shout out to free code camp for putting this all together personally learned a ton from free code camp and so I'm really honored that my boot camp is going to be here for you guys to learn and I really hope you enjoy it what's going on everybody it is 2023 and in this video I'm going to help you become a data [Music] analyst we're going to start at the very beginning assuming you haven't started this process at all of becoming a data analyst if you already have you can kind of find IDE identify where you are in this process and then go from there now before we dive into everything I want to warn you I will be mentioning my own channel a lot in this video I have videos and playlists on just about every single topic that we're going to be talking about today I'll have all the links to those videos in the description so you can dive into those topics more in depth so I hope that's okay and it's all completely free I've been building this out for the past 3 years and honestly you can probably get 90% of the way to learning everything you need for data analytics just on my channel so now that I've warned you let's J been of number one and that is learn the data analyst skills now there are literally a hundred different things that you can learn for data analytics you can learn things like alter X or a cloud platform or different programming languages but there are some core skills that I recommend you start out with before kind of branching into some of those other skills the number one skill that I always recommend people start with is SQL SQL is just one of those fundamental skills I think everybody should learn even if you don't use SQL you'll use some variation of SQL if your company has a large enough data set SQL is used to actually query and retrieve data from a database so if your company collects data which every company does they're going to put it somewhere to store it's usually stored in a database and sqls how you get that data from the database I think SQL is also fairly easy to learn which makes it really good when you're just starting out I have several playlists dedicated to SQL starting from beginner all the way to Advanced and you can learn all of that for free one other reason why I think you should learn SQL first is that a lot of companies interview or have a technical interview during the interview process on SQL that's something that really caught me off guard when I was first starting out out because I thought it was going to be more behavioral I didn't even know what a technical interview was so knowing SQL actually became a really important part of interviewing and getting a job as a data analyst the second skill that I would learn is a business intelligence tool like Tableau or powerbi now there are a ton of different bi tools I can literally name 10 off the top of my head that I've used throughout my career but what I will say is that learning something like Tableau or powerbi is pretty transferable to almost all those other bi tools they're all fairly similar and how they do things and how they show display the data you most likely won't have a technical interview asking you about Tableau or powerbi like to build something for them that usually does not happen but the combination of SQL where you can query your data and then taking that data to build something that is a really really great combination to learn right away I have entire series on both Tableau and powerbi with projects on my channel the third skill that I would learn is Excel now most people have used Excel they know what Excel is and how it's used but it can be used a little bit differently for a data analyst for example example in Excel a lot of people haven't cleaned data in Excel or built charts and graphs using Excel and those are things that data analysts would probably do excel is also just a fundamental skill that every company is going to expect you to know so I have an entire playlist dedicated to excel to actually walk you through how to use it for data analysis the fourth skill that I recommend you learn is python now a lot of people will have python higher up on their list they only use Python they don't use SQL or a bi tool they just do everything in Python now python is a fantastic tool you can use it to manipulate your data to create data visualizations and a ton more like web scraping and regular expression and a hundred different other things but it can be kind of hard to learn it took me a long time to really learn the basics very well that's really the only reason why it is farther back I feel like SQL and a bi tool are really easy to learn and really pack a big punch whereas python can be quite tough to learn in my experience and you may not use it as often as you would something like SQL or a bi tool if you're interested in learning py python I have an entire series dedicated to python as well as projects that you can build again I warned you there's going to be a lot of self-promotion in this video I have videos on just about every single one of these topics the fifth and the last skill that I recommend you learning and this is the only one that I don't have a series on yet I will make those is learning a cloud platform like AWS Google Cloud platform or Azure there's no denying that these platforms have played a huge impact in how we use data as a whole in the data analyst industry they can be kind of tough to learn though if you aren't using it Hands-On in an actual job I think that learning a cloud platform is already something that most people should start working towards because in the future it's only going to become more prevalent now where can you go and actually learn all of these skills that you need to become a data analyst well the number one place I'd recommend of course is my channel I have free tutorials on all these skills and a lot of other topics and I think it's just a really great place to start the next place that I recommend you looking at is udemy I recommend udemy especially if you're just starting out because it's pretty pretty cheap you can buy an entire course entire SQL course for $10 or $15 and they have courses on every single one of these skills and I just recently made a video called DIY data analysts curriculum using udemy for under $75 so you can create an entire curriculum to learn all of these skills for under $75 which is just amazing the next place I'm going to recommend you look is corsera now udemy is fantastic they have really good instructors and good courses but as a whole I find that sometimes corsera just has more professional or better content corsera is a bit more expensive though you're looking at $59 per month for all of their courses or you can pay upfront an annual fee of $399 so again it's just a lot more expensive I moved to corsera once I started having a data analyst job and had a bit more money but when I was first starting out I just couldn't afford it so I went to udemy and it was a really great place to start there's also places like data camp and data Quest that kind of gamify learning and they're more text based so all these other platforms udem me corsera and me they're all video based but if you like reading data camp and data Quest are a lot more of text where you can learn it by reading it and doing it after you learn all of these skills the next thing that I recommend you do is actually build projects with those skills now what is building a project actually mean it means taking a skill and then building something out of it that you can then show a potential employer for example if you went through and learn Tableau you go and take a data set and you could build a visualization and a dashboard in tableau and that would be a project with these projects you can build something called a portfolio and I usually call it a portfolio website a portfolio website is a website that you create where you store all of your projects and then you can share that with recruiters and hiring managers so that they can see all of your work now do you absolutely need a portfolio to show employers no you don't but it does help in two different ways the first thing that it may do is actually help you land the interview if you have a link on your resume and they click on it they may see your skills and see your projects and be like man this person really knows what they're doing this is exactly what we need the second reason that I recommend building projects is because most likely during your interview you're going to get asked questions like how have you used SQL how have you used Tableau and if you don't have any experience in that you're just going to say well you know I've taken courses to learn it but with a project you can be a lot more specific you'll be able to say well I actually just built out this project in Tableau I took the data and cleaned it in Excel and then I put it in Tableau and built out this Dash board and here are the insights that I found from this data set it's just a much better answer and as a hiring manager myself I can tell you that it is definitely beneficial to build out these projects The Next Step that I recommend you take in becoming a data analyst is building a data analyst resume the resume to say the least is extremely important it's what's going to actually allow you to land an interview to potentially get a job now if you were like me when I was first starting out I had a resume it just had nothing to do with data analytics so how do you make a data analyst resume if you don't have any experience as a data analyst well you are asking the perfect questions because the very first things that we talked about are what are going to go on your resume those skills and those projects if you have no experience or degree like myself who has a recreational therapy degree if you have no background in this it can be really daunting to kind of display that you know what you're doing and that a company should hire you so what I usually recommend is right beneath your contact at the top you put your skills and your projects that you built out on your resume things like work experience and education should go on your resume as well but just a little bit lower you want them to see those things before they see that your last work experience was at Domino's and you have a degree in Marine Biology it's just not relevant to data analysis and if you put those things at the top they're probably going to rule you out right away the fourth step to become a data analyst is actually applying you have the skills you have the projects you have the resume now you're ready to start applying for those data analyst jobs now there's there's a lot of different opinions on how you need to go about applying for data analyst jobs but I'll give you my take on it and this has been the most successful for me in my career the first thing I want to mention is actually what I would not do which is just blindly apply on glass door monster zip recruiter and all these other platforms to just any data analyst job that you can find now I'm not against this I think you should do that but I don't think that's the only thing that you should do because the chances of you getting a call back or actually hearing something back are extremely low to really increase your chance of becoming a data analyst I highly highly highly recommend working with a recruiter a recruiter is literally someone who is there to help you find a job now when I first started out I didn't understand what a technical recruiter was at all I was kind of nervous or scared to work with him but it's actually pretty simple a company has a position that they want to fill and they don't want to spend hours and hours and hours to find someone to fill that position so they hire a recruiter a recruiter is going to go out and try to find someone to fill that position AKA you and so if you go into talk to that recruiter and they have a position that opens up they will help you get that interview and then if you get a job let's say for $50,000 the company is going to pay that recruiter let's say 10% of your salary so they'll give them $5,000 so you don't actually lose or have anything to lose using a recruiter you can reach out to Recruiters in several ways and I've done every variation but I'll tell you my most successful way which was using LinkedIn there are tens of thousands of Recruiters on LinkedIn I made an entire video of how you can reach out to recruiters and what to St Recruiters on LinkedIn to help you land a job so be sure to check out that video when you actually get to that point but you can also just cold email and cold call these recruiting companies but to me it's just not as effective as reaching out directly on LinkedIn and this is just a bonus one the last thing that you need to do is accept a job offer so on step number four after you apply to those jobs you do actually have to go in interview and then get a job offer which you will accept I just thought I'd mentioned that just in case that was not super clear now that was a lot of stuff let's talk about time frames to actually complete all of these things now doing all of these things from scratch is going to take a while but let's break it down by each step and see how long I generally think it's going to take let's start with step number one which is actually learning the skills now just to be up front this one probably is going to take the longest for most people for most people to learn all of these skills it's going to take around 3 to four months now if you don't learn a cloud platform and python which are the last ones that I recommend and you just focus on SQL a to in Excel I think you can do that in under 3 months that is very dependent though on how much time you have to study that time frame is more for someone who has several hours per day maybe 3 hours in the end of a night after you go to work that is someone who has quite a bit of time to dedicate to learning during their week of course that time frame is going to take longer if you don't have as much time to dedicate to learning now let's look at number two which was creating projects and a portfolio of projects from my experience when you're first starting out it takes a lot longer to actually create these projects it can take one one or two weeks per project I usually recommend people doing three to five projects in their portfolio before they start applying and since they can take anywhere from 1 to two weeks you're looking at anywhere from 3 to 6 weeks The Next Step was to create a data analyst resume now in my opinion this one should take the shortest out of every single step here because you're really just kind of reformatting a resume or creating a resume you're just adding skills you're adding your projects and then kind of reformatting it to make it look nice this should hopefully take under a week but if you use something like a professional service so they help you build a resume it could take one to two weeks the two last steps which kind of go hand inand are step four and five which is actually applying for jobs and then Landing a job now this process can take as little as a month or it can take as long as 6 months or a year it really depends on how you're applying where you're applying and just the kind of luck that you're having with actually Landing interviews I've seen people who have never had any experience land a job within a month of starting to apply and it's incredible it's amazing but it doesn't happen too often you're usually looking at around 2 to 4 months on average to land your first data analyst job if you put all of those together and kind of average everything out you're looking at around 6 months total for the entire process now I don't want that to discourage you okay 2023 is a long year you have a lot of time and it doesn't have to take 6 months you could do it faster you could do it in three months and just prove me wrong but if you are really focused and you are really driven to become a data analyst this year I know that you can do it now to maybe boost your spirits and make you feel a little bit better I didn't know any of these things when I first started out I didn't have anyone telling me kind of a plan on what to do I had to go out and figure all these things out by myself and it took me almost a year to land my first real data analyst job so with all that being said I hope that this video is helpful I hope you now have a path on how to become a data analyst this year and that my channel can be a big part of that so thank you guys so much for watching I really appreciate it if you like this video be sure to like And subscribe below and I'll see you in the next video [Music] what's going on everybody my name is Alex freeberg and in today's video we're going to be starting our basics of SQL series now in this series we're going to be going over everything you need just to get started and then in future videos we're going to be going over some intermediate Concepts and some more advanced concepts and then in the final series we're going to be going over some portfolio projects in this video in particular we're going to be downloading SQL Server Studio we're going to be creating our tables inserting data into our tables and in future videos we're going to actually learn how to query those tables if you already have SQL Server management Studio downloaded you can skip ahead to where we actually create the tables and insert the data into the tables if you don't care about that at all and you're just looking at a query I would skip to the next video where we actually start quering the data that we inserted into those tables so to download SQL Server management Studio we actually have to download two things and I have both links right here I'm going to leave those in the descriptions that you guys have those but this one is to actually download SQL Server management studio so let's go down here I actually deleted it off my computer so I can walk through this with you guys so we're going to download that let's also go over here this is actually a server so we have to download a SQL server and if you go down right here there's a free version now I don't need the developer version I'm just going to download the express version it's actually smaller so let's download that as well now once this is done running we're going to open it up and I'll show you what to do next so it just finished running let's click on it all right so we need to install it we're going to click yes and this is going to take a little while so this popped up I clicked install and it's been running for the past couple minutes apparently I was not recording so I apologize for that but that's all I did so now it's been installed I'm actually going to pull it up right here and let's open it up now when it pulls up it's going to ask you to connect to a server and that's why we downloaded the SQL Express server so let's connect to that and there you go it's as easy as that so now we have SQL Server management Studio set up and we are good to go so the first thing that we need to do is actually create a database so let's go over here to databases and let's click new database and let's just do SQL tutorial keep it simple and if we click that it's going to create our database for us now when you open up the database there's going to be a lot of stuff you really do not need to know all this really what we're going to be sticking to is this tables right here uh as of right now we do not have any tables so we need to create tables now there's two ways that you can do that you can click right here and you can go to new and create table we're not actually going to do that we're going to create it using a script or a t-sql so we're going to go over here and do new query and we will get started on actually creating uh the two tables that we're going to be using for all the stuff going forward all right so let's get rid of me CU you really don't need to be seeing me anymore let's get started by doing our very first table which is going to be our employee demographics table so let's start off by saying create table and we have to name it so let's do employee demographics and enter down we want to do an open parenthesis now we need to specify what our column names are going to be and what the data type is for each column so let's start off with employee ID and we want that to be an integer so that'll be like 1 2 3 4 uh anything numeric now we want to do first name and let's make that varar 50 if you don't know what these data types are that's okay uh that will probably be covered in a different video that's not really necessary for this video uh let's do last name we'll also make that varar 50 let's do age make that an integer and very last let's do gender and we will make that varar 50 as well so now we have our very first table let's run that and we'll see if it works we'll go over here we'll refresh our tables and there you go so we have our very first table let's go up here let's get rid of this one and now let's create our second table so we're going to do basically the exact same thing but we're going to have a little bit different information in it this is going to be our employee salary table so let's do create table and again we need to name it and enter and open parenthesis so now we're going to do the same thing we're going to do employee ID let's make that an integer now we want the job title because we want to know what they do and this one is going to be varar 50 because we keep it pretty simple whoops and then for our very last one we're going to do salary and that will be integer as well and I'll just do PR here so let's create this table let's see if it is there and there we go so let's open up one of these tables really quick see what's in there see what it looks like as you can see we do not have any information in there uh when you create a new table sometimes when you open it up you're going to see this if you want to get rid of that you just need to do a I think it's called A Hard refresh or something like that but you can do control shift R let's see if it works for me I just did it all right it goes away so now it recognizes it as a table so we're good there let's go back here and let's get rid of all this we've already created our tables now we want to insert the data into our tables so let's see what that looks like let's do insert into and now we need to specify what table we're inserting our data into so let's start off with employee demographics let's do values so now we have to select what values we're going to put into um into this table so now we're going to have to do the employee ID so let's do 101 then we're do first name so let's do Jim last name Halpert and then his age let's say he's 30 and he is a male now just for fun let's execute that let's go back to this table right here and execute and as you can see all of our information actually went in there so now we have his employee ID his first name his last name age and gender now we need a lot more information uh for this table in order to actually learn a lot of the concepts of quering the table so I'm actually going to go through and add a ton more information I'm not going to bore you through that but I will show you the final product before I actually hit execute so stick with me I'm actually just going to cut to the end where I insert all my stuff down on here and then if you want that I'll probably leave it in the description or maybe put in my GitHub or something so you can easily just go copy and paste that if that's what you want to do so I'll see you in a few seconds all right so I have all my values right here I actually going to take this one out cu I already did that one but this is our additional information let's insert that into our table real quick and go back here and take a look at it and there you go this is going to be our core information that we are querying off of uh in future videos so that table is completely finished let's go back here we're going to get rid of this because now we want to insert our information to our other table so let's do insert into and let's do employee and now we're going to do salary so let's do values to specify that we're inserting values into there and in this one we have employee ID so again let's do in th1 that's gym his job title is salesman and let's say his salary is $45,000 and let's execute that and you can't see it but down here it says it's done let's go to that table and as you can see that is inserted I'm going to do the exact same thing as I did before I am going to fill out all these and in a second it will be done uh on your side and then again I will leave it in the description or I'm going to put it on my GitHub and you guys can just copy and paste that if that's what you want to do or you can write it out whatever you want to do all right just like before I'm going to get rid of this first one that is Jim he is already done now let's insert this information Ed is finished let's go back here and there we go now we have both of our tables and we are good to go for future videos so thank you so much for sticking all the way through this one in the next video we're going to actually begin uh quering the table and learning the select the from the where the group by and the order by statement everything is in these upcoming videos so stick around and we will learn all of that together thank you so much for joining me if you like this type of content be sure to subscribe below and I'll see you in the next video what is going on everybody my name is Alex freeberg and in today's video we're going to be going over the select and the from statement so if you joined us for our last video we went over creating our tables and inserting data into those tables and so we have this employee demographics table and we also have this employee salary table and today we're going to be walking through the select statement in the fir statement on these tables so here are some of the concepts that we're going to be going over today let's just get it started by doing select everything and let's do this from the employee demographics table so let's execute this if we wanted to only show the first names we can just do first name and run that and if we want first name and last name we can just separate that by using a comma and it will return those well if we want to return all columns and all rows then all we have to do is use this star so that's what the star does now we have nine rows of data here and if we only wanted to return let's say the top five we can easily do that and we can just say top five of everything now the reason this could be useful is say you have a table that has millions of rows in it and you only want a small sample you can say select top 1,000 and when you do that it will only select the top five rows now let's get everything back in here really quick because we're going to move on to this distinct feature so when we use distinct we're actually saying that we want the unique values in a specific column so if we say distinct and then let's do employee ID D everything should be returned so all nine rows should be returned and that's because every single one of these are unique now let's try gender so there's only going to be two results the male and the female and that's because there's only two distinct values in that column now let's look at all of our data again so now we want to look at count now count is very simple all is going to do is going to show us all the non null values in a column so let's look at last name for example if we do count of last name all that's going to give us is a count of nine because we have nine last names if for whatever reason somebody's last name was left out and that was null then it would have returned maybe eight or seven depending on how many were actually in there so if an entire column was null we it would be a Return To Zero and if you notice we are not given a column name that's because this is derived information based off the last name so if we want to actually give this a name so that that column does not say no column name we can use this as right here so once you put as you can actually name it so since this is the count of the last name we'll write last name count keep it simple and if we execute that as you can see we have last name count right there so that's how you use that as let's look at all of our data again we want to look at some Max mins and averages right now and the only column here where it would be useful to do it on is age but let's actually go over and let's look at our salary table and at our salary table we have some really interesting salaries that I think would be a little bit more useful for this information so let's go over to employee salary all all right and let's look at this table really quick so we have our salary now we want to look at the maximum salary that is in uh that column and that is going to be $65,000 now let's say we wanted to know what the minimum salary was let's execute this and the person who makes the least money is making $36,000 now what's the average what is the average salary for all employees that's going to be $ 48,5 so so super easy to use all of these things they're extremely useful I use them every single day so I know that each of these are very very useful and are definitely among the basics that you have to know let's look real quick at everything really quick so we just learned the select statement but learning this from statement really quick is also important up here this actually shows us that we're already Hitting off the SQL tutorial database but let's say we change it to master when we try to run this it's going to give us an error and that's because now we're hitting off this database and this database does not have this table in it so in order to do this in order to still hit off that table while up here we're actually hitting off a different table we can change this information so the from statement you have to specify three separate things the first thing that you need to specify is the database so let's say we want to hit off the SQL tutorial database now we want to select what table we're going to do this is actually a dbo so let's put dbo there's there's a lot that can go into that um it's not worth getting into now but dbo do and let's do employee salary when we execute this our information comes up even though up here we're still hitting off the master database when we specify it right here then we actually are choosing what database and what table a hit off of and so it does not matter what it is up here so that's how you use the from statement in the next video we're going to be going over the wear statement and then after that the group by and order by statement and that will be the complete basics of SQL tutorial and then we'll start getting into a little bit more fun stuff some more advanced concepts which I think it be really really exciting for everybody to learn thank you guys so much for joining me I really appreciate I hope this has been helpful if you like this type of content subscribe below and I'll see you in the next video thanks and goodbye what's going on everybody my name is Alex freeberg and in this video we're going to be going over the we statement and SQL in the very first video we created our table inserted data into our table in the second video we went over the select and the from statement and now we are on to the wear statements now what does the wear statement do it helps limit the amount of data and specify what data you want returned we have quite a few Concepts that we're going to be covering today let's just start out with something really easy let's do where first name equals gym really simple so we're selecting everything where our first name equals gym and this is our output so really really simple now let's try where it does not equal this right here says does not equal gym and let's execute that and as you can see we have everybody except Jim Halbert in there so now let's look at the greater than or less than so in this table I think the one that we're going to look at is age so let's look at age and let's do where it's greater than 30 and when we execute that we're going to get everyone who is over the age of 30 now as you can see we're not including people who are 30 years old if we want to include people who actually are 30 years old we're going to add the equal sign right there so we should be seeing people who are now 30 so before Pam and Jim were not in there and now they are if we do the exact same thing let's do less than 32 here's everyone that's going to be included but if we want to include the people who are 32y old then we are just going to add that equal sign and now the people who are 32 years old like Toby and Meredith are now included if we want to go even further we want people who are less than or equal than 32 and who are male we can say where gender equals male so now we have two two things that we are specifying that we need we need somebody whose age is less than 32 and we need their gender to be male so let's execute that and we have four people who meet that criteria so that's what the and statement does if we write or then only one of these criteria has to be correct in order for it to be met so if we hit execute now we're saying anybody who's under the age or equal to 32 or their gender equals male so if we look down here Michael Scott is actually 35 years old so he's over 32 but since he is male he is now included let's get rid of everything really quick I want to look at this like really quick so let's execute just that and if you do that you highlight just that hit execute then it uh will only run what you have highlighted so now let's look at this whole table now when you're using like you typically are doing this for sometimes numerical but most of the time you're using it for text information so if we're looking at this right here if I'm looking at last names and let's say I want everybody whose last name starts with s you can't really do that with anything else so I'm going to say where it's like and then I'm going to say s and after that I'm going to put a percent sign that's actually called a wild card and if I close that off what this is saying is is I want every last name where it starts with where it's like where it only starts with an S so let's run this really quick now we have two people whose last names start with s now if I put a wild card at the beginning we are now saying where there's an S anywhere in anybody's name so let's execute this and see what we get so now even if the S is like flenderson towards the end it's still counts so you can specify multiple things in here as well so let's say I want it to start with s that would return shre and Scott but now I want something that also has an o in it so so it has an S at the beginning and then somewhere in there there's an O now let's execute that and there's only one person that meets that criteria so you can do that for multiple things you can even say OT TT and let's execute that and he's still going to be returned and if we put C at the back it's not going to be returned because it follows it in order so isn't s o TT C the C would actually need to go over here so now we have s c o t t and although there's a bunch of wild cards in here it is going to return Scott so that is a little bit a little hint at how you can use like there is a little bit more that goes into it you can use it for numerics um there's a lot of things that you can use this for but this is just the basics how you can use it today how you get started on using the like a nutshell that is how you use like and as I said before you can use like with numerical data as well but for demonstration purposes I wanted to use text Data let's get rid of this really quick um let's look at our entire table and I wanted to show you how to use null and not null I can't really show you how to use null because I do not have any null Fields I could easily update this table and make n but that's in a future video where it's a little bit more advanced where you can start altering your data but just for purposes of showing you what null and not null is let's do where first name is null and if we see that is not going to return anything but if we say is not null it's going to return everything because nothing in here is null nothing in this first name column is null so that's how you use it um there are a lot of use cases where you actually will use null and not null that will be in future videos probably in the project section or the portfolio section we weren't able to show really how to use this super well but just as a demonstration that's really all it does it looks at the whole column and whether it is null or not null that's really all it's used for this is actually super useful and you can use it in a ton of situations but again for demonstration purposes that's really all it does so let's get rid of this let's look at in really quick so in is kind of like the equal statement but it's multiple equal statements so let's say we want to say we first name equals gy and then we were like wait we also want to include Michael Scott so then we would have to write and where first name equals and then we would do Michael and then etc etc for anybody that we wanted to include but if we said in we could do an open parentheses and then we can say gy we can say Michael and we can say as many people as we want going down the road just separating it by commas and if we had execute everything would be returned so it really is just a condensed way to say equal for multiple things so that is the we statement I think the wear statement can get extremely complex but this really is highlighting the basics so if you can learn all of these Concepts you will absolutely have the basics down and will be set to go over some more intermediate and more advanced things with the we statement later on in the next video we're going to be going over the group buy and the order buy and then we are done with the SQL Basics and then you can practice and work your way up into my intermediate level videos which are going to be coming out very shortly after these videos thank you guys so much for joining me if you like this tutorial Series be sure to subscribe below and I'll see you in the next video going on everybody my name is Alex freeberg and in today's video we're going to be going over the group by and the order by statements in previous videos we created tables we went over to select the from and the where and now we are at the very end of our SQL basic series if you stayed with us for the whole time hopefully you have learned a lot and learned the basics of SQL in future videos we're going to be going over intermediate and even more advanced concepts and even going through portfolio projects that you can use to put on your resume if you like this type of content be sure to subscribe below but let's get into it for today the group by statement is similar to distinct in the select statement in that it's going to show the unique values in a column the difference is is if we say distinct gender what's going to be returned is the very first unique value of female and the very first unique value of male but if we say gender and we say Group by gender it's only going to return two values but in these two values we actually have all the males rolled up into this one row and all the females rolled up into this one row now let me further show you what that means if I say count of gender now you can see that this whole time there were six males in this one row and there were three females in this one row so with a distinct it really is only showing us what value is in there that's unique but with the group by it's showing us what the unique value is but it's also rolling them all up into one column that we can use it for other things now real quick I want to be able to see both of these at the same time so let's just put this up here and let's run this so we can actually see both now let's add age to this statement down here or this query and let's only run this one and I want to show you what happens and why it happens we're now looking at gender age and then the count of gender so if we look down here we only have one male who is 29 we have one male who is female that's age 30 and so on and so forth so none of these people are both the same gender and the same age if for example we had two or three people who were male and who were 30 years old then we would have a two or a three over here so this count is actually being counted at each row that's being returned so for our data that we have today this isn't a fantastic example CU it really split it out there any that were the same but as you can see you can put multiple columns as long as you put multiple down here now why did we not have to put this count gender down here in this group by that's because this count gender is actually a derived field or derived column it's derived based off the gender column so it's technically not a real column that's in the table it's one that we're creating that's fictional uh per se so the age and the gender are actual fields or actual columns that are in our table they have to be down here and like I said before it's the comparison to that distinct in the select statement because we're looking at the distinct of gender and age so we're saying distinct across multiple columns both gender and age now as we had it before we were only looking at gender it's going to roll all of those up into just male and female but if we want to add more we can easily add more in this group by statement we can still do things like where age is greater than 31 we can still do those things so let's execute this and our numbers are going to change now we're doing it based off gender and we're looking at the count of people whose age is greater than 31 which is smaller than before now let's look at order bu I'll do it down here really quick for demonstration but I am eventually going to come up here and use it because I think it'll be a little bit better to completely round out this query down here let me give this a name let's do count of gender and then let's come down here and let's order by uh let's order by count gender and when we run that it's going to do 1 three and that's because as a default SQL has an ascending feature which is going to be smallest to largest going down if we want to change that we can change it to descending that's going to be largest to smallest so now we have 31 and if we want to do it based off gender and we do it descending now we have Z to A and so that's going to be male female and if we get rid of that it's going to do the the default ascending and let's see what that brings female male now for what we're trying to do let's look at this large table so I think it's going to be a little bit more descriptive or a little bit better visually let's do order by and let's do age let's run this and it's going to order smallest to largest if we do descending it's going to do largest to smallest now you don't only have to do just one thing you can do multiple columns so if I wanted to do age and then gender I can do that as well so let's do gender and let's run that so now we have the age but under the age we also have it ordered by female and that's an ascending order so AB BC d f so females first so it's going to be female first and then it's going to be male and again female and male now we don't have to just let it be ascending for each one if I wanted to do it reverse in this column I can do descending now let's run that and when we have 30 now male is first and female second and if I wanted to do that over here I can do descending and now we have them both descending so it's going to go top to bottom and we have 32 it's going to be male 32 female so you can specify lots of different things in here and we don't actually have to use column names we could just use numbers so if I wanted to do 1 2 3 4 5 I could but let's try to replicate the exact same thing before this would be column 1 2 three four so let's do where four descending and then let's do five descending and if we execute that it's going to give us the exact same result as if we' actually put in the column name and I I do use this a lot oftentimes I don't use the column name I just if it's a small table I'll just use the number so in my actual queries I do this a lot where I just use the number instead of the column name so that is the group buy and the order by statement and if you have walked through my previous videos you should be completely done with the basics of SQL so congratulations the next thing to do is really just practice the basics because the basics are what you're going to be using day in day out and so what I would recommend is create a few more tables query those tables try to think of use cases and what you would actually want to know from that information after that I would move on to my intermediate videos if those are already out and then I would move on to my Advanced videos those are going to go over some more challenging topics but things that would be very useful for anybody to know in my next video I'm going to be going over intermediate SQL topics things like joins and subqueries and a ton more so if I already have posted those be sure to go check those out on my page and if I haven't I hope to have those up soon thank you thank you guys so much for watching I really appreciate it if you learned anything in this basics of sequel Series be sure to subscribe below and I'll see you in the next video what's going on everybody my name is Alex freeberg and today we're going to be starting our intermediate SQL series if you joined us for our last series we walked through the basics of SQL which is everything you needed just to get started and in this series we're going to be walking through some intermediate Concepts to really take your skills up to the next level now today we're going to be walking through joins but let me show you what you can expect from the entire series for this intermediate course so we're going walking through joins today and then in future videos we're walking through unions case statements updating and deleting data Partition by data types aliasing creating views having versus the group by statement the get date function primary care of your foreign key and then we're going to have an advanced course and this is not set in stone yet but these are some of the things that I think I will be going through or walking through we're going through CTE CIS tables or system tables subqueries temp tables string functions regular expression store procedures and then importing and exporting data so with all that being said let's get into it all right now let's get rid of me because we do not need to be seeing me for the rest of the series at the very top here are some of the things that we're going to be going through today which are inner joins and then outer joins and in the outer joins we have a few different styles or a few different types of outer joins now a join is a way to combine multiple tables into a single output for now we're going to be using the employee demographics and the employee salary table so let's get a look at both of these tables and see what's in them in our employee demographics table we have employee ID first name last name age and gender and then down here in our employee salary table we have employee ID job title and salary if you notice they have a similar column and that's going to be the employee ID now when you're doing a join you have to do this based off a similar column and typically you want it to be a unique field so we're going to be using the employee ID from both tables to join these tables together to create one output so let's get rid of this real quick and let's start building our query to join these two tables together so the first thing we're going to do is an inner join so let's do select everything and let's do it from SQL tutorial. db. employee demographics and let's do join we can also say inner join but join by default is going to say iner and we're going to do SQL tutorial. db. employee salary now we have to join them together which is what we talked about earlier and we're going to be doing that based off the employee ID so for th