Spreadsheets and SQL for Beginners | Google Data Analytics Certificate

this video is part of the google data analytics certificate providing you with job-ready skills to start or advance your career in data analytics get access to practice exercises quizzes discussion forums job search help and more on casera and you can earn your official certificate visit grow.google datacert to enroll in the full learning experience today this will give you a clearer picture of how to use these tools the rest of the program will build on from what you learn here we'll start with a closer look at spreadsheets we'll break spreadsheets down to their basics to better understand a few of their features and functions you'll also learn how you might want to use them in your work as a data analyst for example how do you search your data to make it easier to use we'll find out next we'll see sql in action data analysts use sql in their work all the time like when they need a large amount of data in seconds to help answer a quick business question chances are you're not familiar with sql that's okay you'll learn how using sql is just like ordering food at a super speedy restaurant your sql query might not be as delicious but you won't have to wait long to get your order speaking of food what better topic than dessert you can think of data visualization as the dessert to the meal of data analytics it's served at the end of your analysis after you've done what you need to get the right data for a question or task we've already seen that visualizations come in a lot of forms like graphs or charts and just like dessert they're a treat to look at you'll learn more about these visual representations and see other examples of how they might look then you'll get to talk about visualizations with other future data analysts just like yourself we'll wrap things up with an assessment but you'll have time to review what you've learned before then okay let's keep going by the way is anyone else hungry now on october 17 2019 we celebrated the 40th anniversary of a very special event well special for people like me anyway in 1979 visit calc was introduced to the world as the first computer spreadsheet program while spreadsheets have changed a lot since then it was still an important achievement and so now we celebrate october 17th every year as spreadsheet day while there's a good chance you've never been to a spreadsheet day party spreadsheets are a big part of data analytics the sooner you make friends with spreadsheets the better trust me they'll save you a lot of time as a data analyst and make your job easier this spreadsheet is one example of how an organized spreadsheet might look in this video we'll demonstrate some basic spreadsheet concepts for all of you who are new to this world this might be a review for some of you more experienced folks out there but it never hurts to practice what you know plus you might still learn a new trick or two i showed you this image earlier let's explore it further because it's a great example of the three main features of a spreadsheet cells rows and columns they'll be a part of almost everything you do in a spreadsheet from making a simple grocery list to analyzing a complex data set i use spreadsheets to manage everything from my own personal finances to the annual homecoming party my friends and i have every year i'm the planner so i use a spreadsheet to keep things in order making sure we have everything we need speaking of keeping things in order the columns in a spreadsheet are ordered by letter and the rows are ordered by number so when you talk about a specific cell you name it by combining the column letter and row number where the cell is located for example in this spreadsheet the word row is in cell d3 pretty simple right let's get started in an actual spreadsheet you can complete all of these steps in just about any spreadsheet program let's get to know your spreadsheet a little better now alright we'll start with some basic operations keep in mind as an analyst you won't always create your own data sets but for now let's do just that i'll click in cell a2 and type my first name in the cell like this next i'll click in cell b2 and type my last name don't worry if your name doesn't fit in the cell you can always make the columns wider if you need to all you have to do is click and drag the right edge of the column until the name fits or you can also use the text wrapping feature which will set cells to automatically change their height and allow the text in the cell to fit to use this feature select the cells columns or rows with text then use the format menu to look at the text wrapping options it is automatically set to allow the text to overflow out of the cell but you can wrap the text instead so all of the text is visible the clip option will cut off the text in the cell so only the text that fits is visible there it is we've added data now let's label the data this is important for organization adding labels to the top of the columns will make it easier to reference and find data later on when you're doing analysis the column labels are usually called attributes an attribute is a characteristic or quality of data used to label a column in a table you might hear them called variables or a few other names too all right let's add some attributes to our data i'll click in cell a1 and type the words first name in cell b1 i'll type last name we'll make these attributes bold so they stand out more spreadsheets can be really big so you want to make sure your data is clearly labeled and easy to find so let's make these attributes stand out i can use my cursor to select the cells with the attributes then i'll click the bold icon to make the attributes bold looking good so far ready to add some more data let's start with some new attributes first i'll add a column for age by typing age in cell c1 then i'll add two more attributes in the next two columns let's go with favorite color and favorite dessert i'll make them bold too and to fit the labels in the cells i'll adjust the size of the columns just like before now keep in mind there are more ways to adjust the size of columns and rows if you have questions about using spreadsheets a quick search online will usually help you find what you need we've also included a reading with more tips and information about spreadsheets okay let's get back to it now i can add my own data to the data set i'll type in my age and favorite color and dessert in the appropriate cells next i'll add data for two more people we now have three rows of data in a data set a row is called an observation an observation includes all of the attributes for something contained in a row of a data table in this case row 3 is an observation of willa stein because we see all of her attributes in this row so now we know spreadsheets let you do lots of things with data you can store and organize data like we've done in this spreadsheet but you can go even further and reorganize existing data too here i'll show you how let's say we want to organize our data by age there's a simple way to do that first we'll need to select all of our columns with data so that all of it gets reorganized together then we can go to our data menu here we have some options let's select sort range this will let us choose how to organize the column next we'll choose a to z which will organize our numbers in order from the smallest to the largest now we want to watch out for our header row which is the word age the attribute for this column we'll check that box this makes sure that the word age stays in place all right now we're ready to sort voila we just reorganized our data by sorting it from the smallest number to largest and as we go further you'll discover lots of other ways to work with data in a spreadsheet including functions and formulas let's finish with a quick example of a formula you can think of formulas as one way of manipulating data in a spreadsheet formulas are like a calculator but more powerful a formula is a set of instructions that performs a specific calculation using the data in a spreadsheet to do this the formula uses cell references for the values it's calculating let me show you here we go we'll click in the next cell in the age column then we'll type an equal sign all formulas begin with this symbol next we type average this is the function we are using in the formula we've briefly discussed how functions work before but it's okay if you don't completely understand them yet we'll take a closer look later on in this case we follow the function average with the left parentheses now we can add the names of the cells where we find the data we're using these are the cell references the formula will use to make its calculation we'll start at the top with cell c2 c2 represents the value in the cell in this case 36 then we'll add a colon next to it which shows that we have a range of numbers in consecutive cells finally we complete our formula by adding the last cell reference in the range c4 and a right parenthesis to end it then we press enter to perform the calculation and there it is the formula has given us the average age of the ages in this data set we've just analyzed some data we'll want to store the data for later use in google sheets a spreadsheet is automatically saved in your google drive for excel and other spreadsheets you'll save them as a file and now you know some basics for using spreadsheets once you're used to these concepts you'll be able to learn even more about spreadsheet tools it's a lot to digest so feel free to re-watch and practice on your own you can even make your own version of this spreadsheet with your own data we'll work together in the spreadsheet soon as well for now good job for sticking with me through this it'll be worth it [Music] as you might remember earlier we touched on the query language sql in this video you'll see sql in action and finally learn what you can do with it by taking a look at some examples of specific queries i guess you can call this the sql sql we'll try to make this one at least as good as the first remember sql can do lots of the same things with data that spreadsheets can you can use it to store organize and analyze your data among other things but like any good sql it is on a larger scale bigger more action-packed think of it as super-sized spreadsheets for example you might want to consider a spreadsheet when you have a smaller data set like 100 rows but if your data seems to go on forever and your spreadsheet is struggling to keep up sql would be the way to go when you use sql you'll need a place where the sql language is understood if you've ever gone somewhere and not known the language it can be challenging to communicate you might think you're asking for one thing and get something completely different well sql knows that feeling sql needs a database that will understand its language so let's talk there are a number of databases out there that use sql you may use several of them during your time as a data analyst but here's the thing no matter which database you use sql basically works the same in each for example in sql queries are universal we've talked about queries before but it never hurts to have a refresher a query is the way we use sql to communicate with the database here's the structure of a basic query you can see that with this query we can select specific data from a table by adding where we can filter the data based on certain conditions all right let's get started we'll open our database and see how sql can communicate with it to do some simple data tasks first let's select our data we'll use an asterisk to select all of the data from the table and with that simple query the database calls up the table we need magic let's add where to the query to show how that changes with data we get you can see that the data now only shows movies that are in the action genre and that's it a basic query in sql pretty cool huh there are plenty of other commands that you'll use in queries as you continue for now though we can celebrate learning about three big ones select from and where as you continue the program you'll have the opportunity to use sql yourself so i hope that this video was a useful sneak peek at what's coming later like with any new language learning it takes time and now it's time to move on [Music] wow your data analysis toolbox is getting full learning about both spreadsheets and sql will get you far in the world of data analysis there's more to learn of course and lots more tools you'll be able to use but your future is looking bright and it's about to look even brighter because we're here to talk more about data visualization i'll tell you a little more about the role of data visualization tools in data analytics and give you a chance to see those tools in action later in this video you might remember that data visualization is the graphical representation of information for tons of data analysts it's the most exciting part of their job because they get to see their hard work pay off with something interesting not to mention that data visualization is beautiful and useful i was floored when i got to google and started to get a quarterly data report in my email it had a big slide deck where people contributed their visualizations it was definitely a source of light as i started to build my own visualizations if you're not impressed by my story let me tell you about florence nightingale does that name ring a bell she's responsible for much of the philosophy of modern nursing and believe it or not she was also a data analyst during the crimean war in the 1850s thousands of soldiers were dying every day nightingale wanted to find a way to reduce the number of deaths after examining the data she found that the majority of soldiers were dying from preventable conditions to convince hospital administrators that they needed to focus on these conditions she created a chart showing the number of deaths over several months the much larger blue sections individualization represent the preventable deaths her work directly led to major changes in patient care and she did all of this over 150 years ago without a computer one of the main reasons nightingale created this visualization was to make the data easier to digest for her audience she felt she'd be more successful convincing the stakeholders using visuals instead of just words and numbers she was right tables filled with data while necessary for analysis just aren't able to show trends and patterns as quickly and clearly as visualizations can imagine you receive an assignment that needs to be completed the same day you gather the data you need in a table could you explain your findings using the table yes you probably could but a better idea would be to use a visualization like this bar graph something like this makes it much easier for you to explain quickly and you've got the benefit of a cool graphic to back up your analysis as a data analyst you'll want to create visualizations that make the data easy to understand and interesting to look at so show it off stakeholders may not have much time to devote to the data your job will be to make their time worthwhile let's go back to that data table we created earlier in the course if you created your own for practice you can open it up now or try this out later here's the data we added before let's create a visualization of the data by inserting a chart a bar graph boom you can see that the spreadsheet visualized the data from our table in a way that made the most sense it created a bar graph or column chart to compare the ages of each person by name but you might have figured that out already that's the beauty of visualization it shows data analysis quickly and clearly we can use chart editor to adjust the chart different spreadsheet programs might have different ways to do this but they all have visualization functions and ways to edit those visualizations all right for now let's just look at the suggested charts we can make the bars go horizontally using a bar chart that looks great so let's close the chart editor there are lots of options to look at but we'll keep it basic for now feel free to try other visualizations if you practice later now we can adjust our chart to make our whole spreadsheet look clean and professional excellent i hope you learn to love data visualization as much as i do maybe you'll become a data visualization pioneer just like florence nightingale as a budding data analyst you've started to feel your utility built with valuable tools that you'll use throughout the rest of the program having spreadsheets sql and data visualization know-how will help make you an ace data detective you'll be able to use these tools throughout the data analytics process as you move forward congratulations on finishing this video from the google data analytics certificate access the full experience including job search help and start to earn the official certificate by clicking the icon or the link in the description watch the next video in the course by clicking here and subscribe to our channel for more from upcoming google career certificates

Channel: Google Career Certificates

Views: 71,936

Rating: 4.9568415 out of 5

Keywords: Grow with Google, Career Change, Tech jobs, Google Career Certificate, Google Career Certificates, Job skills, Coursera, Certification, Google, professional certificates, professional certificate program, Data analyst, Data analytics, Data analysis, Data analytics for beginners, What is data analytics, Sql, Data, R Programming, Spreadsheets, sql database tutorial for beginners, sql database, database sql, data visualization, excel data analysis, data analysis in excel

Id: N9Q3pOoVYIM

Length: 21min 19sec (1279 seconds)

Published: Fri Mar 19 2021