SQL for Absolute Beginners

Now today we're going to be learning all about SQL, AKA SQL. And SQL is the most widely used programming language for data for the simple reason that it's the obvious way to work with data in a database. So if you are a data analyst or you're a data scientist, then having some sort of SQL skills is like saying, I can send an email. It's just one of those things that people fundamentally assume you can do. Even if you don't have data in your job title, then having a few SQL skills is going to be very useful because quite often data is stored in a database and you want to get access to it. Today we learn how to get started. Our guest is Yasmin Ludolph. She is a senior content developer here at Data Camp. She's created seven courses and a dozen projects, plus assisted on creating many more of them. She's also the creator of the intermediate SQL course, so she definitely knows what she's talking about in this area. And Yasmin actually made a transition. So previously she was a marketing manager in the music industry. She switched to working in data and this is how you find her here today. So please take it away. Yasmin, let's learn how to write some SQL.

Thank you so much, Richie. So, so happy to be here. Thanks to everyone for taking some time out of your day to be with us. Welcome to this code along on SQL for absolute beginners. Just a little bit of things to get us started. We're going to be using a data set that's from an Austin animal shelter that we found online that talks about the animal outcomes of animals that went through their shelter. Our main goal for today is to have fun. I'm a big advocate of making learning enjoyable because I think I learn a lot more in that way. So that's what I've put as our primary goal. And our secondary goal is of course to practice our SQL skills. And why are we doing that? Well, Richie kind of already touched base on that and it's really an essential skill. Today many businesses want their employees to be really self sufficient when it comes to querying data in their local work. So it's useful for anyone in any role, even if whether you're looking to be in the data field or not. So with that I'm going to move over to our workbook. So if you've signed up to the registered, I should say then you should have a link to this already and if not, I think you should let the guys in the chat know and we'll sort you out. So this, today's codalong will be using the skills that you've gained from the introduction and intermediate SQL courses on Data Camp, and it's a chance for you to apply them here. But don't worry if you haven't taken those courses. We'll be going through everything step by step, and perhaps this will inspire you to complete the course afterwards. All right, so we are going to first start talking about databases.

So.

So we'll just cover this quite lightly because it's more advanced than what we'll need to go through today. But when we're talking about SQL, usually you are querying information that is stored in a database. Now, databases store and organize data electronically using tables, and those are usually organized in rows and columns, but they're sometimes also referred to as records and fields. This is one thing about data and data science is that everything has like 10 synonyms, which can get annoying, but this is one of them. So rows and columns, records and fields, you know, you'll see them used interchangeably. So when we're talking about SQL and, yeah, when we're talking about SQL and databases, you can. You can see those terms come about. Now, a relational database defines the relationships between tables of data inside of a database. So as an example, just to think about this a bit more, a business might have a table that lists everything they sell, and then it could include the ID of those items as well. And then they could have another table that lists, for example, the customer orders that has the same product IDs, and then they could have so many more other tables that has more product information, customer details, sales information, yada, yada, yada. So you can already picture that this can get quite big and a big spider web of information. And all of this would be under a relational database. Making a database is more advanced and it's not something we'll be going over today in depth. This is all we need to know for what we're going to cover today. Actually, thankfully, we are going to query data from a CSV file, which is something this Data Lab workbook allows us to do. And this has already been loaded for you, so there's nothing you need to download or install. We'll just get started right away. All right, so as mentioned, we are using data from an Austin animal shelter that they've kindly provided online. And, yeah, it records the outcome of every animal that they've handled over several years. And what I've done here is I've included a description of the data for us to review this is actually copied directly from the source of the data. And it's always useful to just have a look at this before you really start with any coding that helps you to understand what kind of questions you might be able to ask. If we take a quick glance at things here, we can see we do have quite a few columns and a lot of them are text based. We have a couple of timestamp columns. It's good to keep this in mind. And we can probably do things like grouping, sorting, finding the most common of something within a time period, things like that. So this can give you an idea of what kind of questions you can answer with the data available. All right, so first step, and we're going to start with some coding. So before we really start answering questions, asking and answering questions, we'll explore the data a bit more to see what it's all about. And we'll look at an example of looking at all of the columns, but only some of the rows. Since there are a lot of rows in this data, we'll just get a snapshot which will give us a good idea of what the whole data set looks like just from looking at the sample. So the first thing we want to do is make sure that this little dropdown is selected as data frames and CSV before we do anything else. Okay, now we'll get started. So to view all of the columns, we could write a select statement that lists all of the column names that we saw in the table above. But that is quite long and it's not something I really want to do right now because I'll probably make a typo on all of the titles and then it'll just be a problem. But we are instead going to use a shortcut, which is something that's called select all. So and it's with this Asterix here what this does, it selects all the columns in one go without you needing to type out all of the names yourself. Then handy feature of datalab is that you can access, you can query data in a CSV file and that's already been loaded for you. The name of our CSV file is animalshelter CS CSV and I'm using single quotes here, but you can also use double quotes to do this part. And then as mentioned, we just want to look at a little snapshot. So I'm going to limit it to 10 rows only. And then we're going to run the cell. Cool. So that gives us our first query and we've been able to just get a snapshot of what the data looks like and we can just skim through it and see what is going on and we can cross reference it with the data description that was above as well. So we have an animal id, we have names, we have a date, time, month, year, date of birth, animal types, breeds, the color of the animal. So some interesting information here, but do you notice anything that maybe is a bit off off? Feel free to post in the chat if you spot anything that might not be consistent with what was provided in the data description. Well, for one, the data source, if I just scroll back up, had listed that month year as a floating timestamp. And a floating timestamp is, well, what you see in these two columns with these dates and a timestamp section, which is the zeros on the other on the end of the date and month year looks a bit different. So I think they made a little bit of an error there. And it looks more like a text field for us. So we'll keep that in mind when we want to query this information later on. This is why it's always a good idea to just have a little inspection of things and cross reference. Because as much as everybody wants to provide information, sometimes they're just, you know, something gets updated somewhere and not in another place. All right, so we've done that. Now another helpful thing is to understand how big the data set is. So sometimes the data description will tell you the number of rows that you're working with and things like that. In this case they didn't. And I think that's because this data set actually updates quite regularly. So it's always growing. But we'll just count it ourselves today. So we're going to use our handy asterisk again. That is the sort of symbol for all. And we'll throw in a function that counts everything. So count. And another thing is when you're using these functions like count, or there are other functions that exist like sum or average, it's always handy to then alias the result. And the result would be the name of the column that is printed afterwards. It's useful to do this because it'll just clearly show what it is you actually did in that column. Otherwise it might just show the function or something a little weird and messy. And if you have a lot of these, it'll get super confusing super fast. So we'll just do this as total rows, alias and and query our CSV.

File.

And we hit run. All right, so there's quite a few rows here, which is pretty cool. This is a pretty decent Size of a data set to practice with. Yeah, I'm quite happy with that. And now there's a little bit of self practice for you. So can you think of another way that you can count the rows or, or the records of this data set without using Count Star? Is there another way to do that? So I'll give you a few seconds to think about that and then I'll jump in and go through another way of doing the same thing. All right, how did everyone do? Did you manage to think of it? Let me see. Is there anything in the chat? Nope, can't really tell. Anyway, we'll show you another way of doing a similar thing. So, and same thing as counting or is also counting one of the column names. So I'll just choose a column name, animal type, because I like, I like that one. And I'll remember to include the alias. I'll call it type count. And it's from the animal shelter CSV. Let's see if it gives us the same number. It does. So this is another way of doing a similar thing. And yeah, just handy to know both and always good to compare because sometimes, you know, you never know what you might find. All right, so step two, we're going to get a little bit deeper and explore the data further. And I don't know about you, but the first question I have is what are the different animal types that went through this shelter? And I would expect to see things like cats and dogs, but I wonder if they had anything more exotic. So let's find out and first let's see how many different animal types they recorded in the shelter. Yes, so we'll use count again here. Good way to go. And instead of counting animal type directly, because we saw in the previous one up here that if I just do animal type, it will just count everything. And I don't want to do that. I want to count or the different animal types. So everything that was like a cat and a dog, that's two types. All right, so to do that I would use a keyword called distinct, which will just count every unique element in that column rather than counting everything in that column. And again, I'll use an alias. And query from our CSV file. All right, let's see how many different animal types they worked with. Five different animal types. It's not as many as I saw, but maybe they like have some really broad naming conventions for these animal types. So let's find out a bit more so we know how many different animal types there were. And let's see what those types were and how many there were of each type. All right, so we're still going to use count, but now we're going to throw in another column into our query and do a bit of grouping to understand the breakdown of these animal types more. So let's count how many each. Five of the five types there were. So to start, we're going to select animal type again. And now we're going to count the animal type. Now we're going to do the same count that we saw when we were counting everything, but we're going to add something new here that makes this a little bit different. Type count. And then it's from animalshelter CSV. And then we are going to do some grouping. So there is this thing called the group by statement. We're going to group by animal type. Now what this does is it'll group each of the five. It will create five different groups, basically, or like five buckets if you want to imagine it, if you're more visual. And we'll count everything in that bucket for us. And. Yeah, let's see if it worked. Cool. So now we can see the actual animal types and how many there were of each. So loads of cats and dogs like we expect, expected, and a few other things. So bird, other, random, what other is. And livestock, which is a surprising one for me. I just wouldn't expect those to go through an animal shelter, but apparently they do. So might be something interesting to explore more later. But first I would like you to try something on your own again. And maybe you notice that when we first look at the sample of the data that some of the animals had a null in the name column. So I'll just scroll back up. So see here, there's this thing called null, null, null. So that means that they had no name or the name is missing, they didn't record it, and for some reason someone forgot to put the name down. So can you write a query based on what you've learned on the courses or maybe what you know, just offhand that will find out how many animals don't have a name. I'll give you a few seconds or a minute to figure this out for a while. All right. If you have any questions, feel free to pop them in the chat. Or maybe you already have the answer. Pop your answer in the chat. If you're enjoying this video, feel free to like as well. That'll be good. Okay. How did everybody do? Anybody got the answers? How's it going? Are you well? Yes. No, Maybe. So, okay. Using the where statement. I can see. Yeah, that's, that's a good way to start. What else would you use with where statement? Yes, all right, no worries. We'll go through it now. So what we have to do is to find out how many animals don't have a name is we're counting again. So select count. We're going to count everything that doesn't have a name. So love our trusty little asterisks. We'll do our aliasing to keep things nice and readable. And it's from animalshelter CSV, we'll add the where clause. Now we want to find out where the name is a null. So we'll do the name. And now SQL has this other handy little bit of code where you just type is null. And this will count everything that was a null value in the table and it will tell you how many animals did not have a name. 48,000. That's a lot. That's a bit sad. Why don't they have names? They should do better. All right, well, hopefully you got that or if not, you followed along and this made sense. Ask a question if not, and we'll see if we can help out. So we'll use this new part in the next question as well. And let's see, were the animals without a name or a specific animal type that might be interesting. Maybe it was like all of the others. I don't know what an other is. We haven't found that out yet. Maybe it's a livestock thing. And so we'll. Maybe what we'll do is we'll select all the specific animal types, filter for the ones that don't have a name and see what comes up. All right, let's give it a go. So if you remember to select the different animal types, we use this thing called distinct. Then from our CSV file, spell it right and then where name is null, just like in the previous one, see what comes up. Okay, so dog, bird, livestock, other cats. So basically all of the categories, they all had animals with no names. So yeah, we'll see. All right, well, I think maybe we should look at livestock and other a bit more since those are quite broad and let's see if maybe there's something else we can find out about them. And maybe if you recall in the data description, we do actually have a breed column in the data set. Where is it there an animal breed. So maybe that can give us a bit more information on what other and livestock is all about. So let's try that yeah. So what we want to do is we want to select the animal type and the breed. Yeah, that's what we want to do. Animal type, that's how the column is spelled, right? Yeah. And the breed. So those are the two things we want to filter or we want to output. And it's from our animal shelter, CSV. And we're sticking with the ones without a name because we just want to give them a bit of attention because they never got a name. So let's just show them some. Some love. All right, so this is similar to what we've done up to now, but we've. We're selecting two columns. We filtered out the ones without a name and now we want to add another filter where we're just going to look at the animal type that are other. And livestock. Now, those are text fields. So what we can do is add another filter with. And you don't need to type where. Again, you just can type and where animal type is in parentheses. Other. Oops, don't want to go there. And livestock. Cool. So in this case, I think you do need to use single quotes, because it's essentially looking for the text that is in a column cell and it needs single quotes to do that. Okay, and then what else do we need to do? So we want to group things by the animal type and the breed here so that we can organize the data that way so we can see what animal, what breed was in livestock and what breed was in other. Now, when it comes to this group by statement, it'll always want you. Not bread breeding will always want you to include all of the columns that don't have an aggregation happening with them. So what that means an aggregation is like accounting or taking the average of something. So it's basically like, what. What are you doing with that bucket? It wants to know what the bucket is. And that's why you'll need to select all of the columns that don't have an aggregation. In this case, we don't have any. We want to see all of them. All of the columns are there, but I think maybe we might come across it later on. We'll see. It's definitely in the courses, though, if you want to have a deeper dive on how the group by statement works. Another thing we're going to do here, just to order things, and I've done this because I have actually already looked at the data and how it outputs here. I've done a little bit of research and I do want us to just have a quick snapshot of Livestock to appear. So I'm going to add something that's called an order by statement, which adds a bit of alphabetical ordering to the output, and it will let us see some of the livestock at the top. Otherwise there's quite a lot of others. We won't see any others unless we scroll through a lot and we want this to look a little better. Okay, so this is our output. So as you can see, there's just a few livestocks and a ton of others. But what are some interesting things here? These animals without a name. A sheep mix, A pygmy, an emu, a fox and a skunk and a ferret. Very interesting. These are all animals that I would not think appear would go through a shelter, but I suppose, you know, it's an animal shelter, so why not? All right, so I'm gonna take a little pause here to just check in with the team to see if there's any questions or any breaks to we need to take. How's everything going, guys?

Yeah, we do have a few questions from the audience already, so let's just take a look at those. All right, so the first question comes from Belina. So Belen says, are the quotes required of best practice? So I think this is when you're dealing with strings and things. Can you talk me through what you need to do with quotes? There's.

Yeah, so I guess there's a few different instances where you might want to use quotes. For example, uniquely to Data Lab, where we are querying a CSV file, we are needing to use quotes here to reference the file. And this can be single or double quotes, and that is needed in the where clause. Here we're using quotes again, and it is needed to reference a string variable within a cell. So a textual element that is in the. In the. In a row slash column intersection. So that's the cell. And you do need to put single quotes there to do that. Another occasion where you might come across quotations marks in SQL is if you're dealing with column names that are not written in the best practice format. So, for example, if this animal type column was written with a space in between, you probably have to use quotation marks in order to use that column in the select statement as well. But to be honest, as with most code, just kind of try something, see if there's an error message, and the error message will tell you where you've gone wrong and you can then take a next step to fix it after that. I do that all the time. And don't be Afraid to do that as well.

Nice. Yeah. So I think the single quote ones are probably the most common use of. Of quotes there. Yeah. All right, so we've got another question from Belina. Well done, Bellina, for asking lots of questions. So why does the index not stop the one? Is that passing through from zero Indexing? Oh, so this is a Data Lab specific thing, I think in the display tables.

Yeah, I think that came up in this first one. Yeah, came up here. So that's a Data Lab thing that I'm not really sure how to explain, I'm afraid.

Yeah, I think it's because we use the same table format for both SQL and Python tables and Pandas. Data frames always include an index, so Data Lab tables also always include an index that starts with zero. So it's just a display thing, this case. All right, so next question from hs. Thank you. Is the alias title part of the table? Yeah. Can you just talk us through where the aliases come from?

Where they come from? Oh, that's a philosophical question. I don't. Don't know where they come from. But so the alias, it's just renaming a column, essentially. So, yeah, in terms of the table, you're not really changing anything. It's just a. I don't know, what's a good descriptive word? It's just a temporary name for a column for your specific query to just keep things readable and understandable. For example, in this one here, that's visible right now, you can see our alias is showing up the output as total rows. If I remove that and just run it again, without it, it'll just say count star, which is a bit like, okay, but what is it counting? Don't know. You'll just add an alias. It just keeps everything organized. Because as much as you'd like to think I do this as well, as much as I'd like to think that I'll remember what my code was about tomorrow, I won't. I'll forget. So adding these aliases so that I remember what I counted a lot faster. It's just. It'll make life simpler, I promise.

Nice. All right, so let's do one more question, then we'll move on. This one comes from Billy. So Bill asks, can queries be made? I think this should be more memory efficient. So basically, do you need to worry about how long queries take to run and how can you do them more effect, more efficiently so they go run quicker?

Yes, that's definitely something. That is something that more advanced SQL users consider it's more of a consideration. Once you're doing things like joining different tables and doing a lot more complex querying and aggregating and maybe taking the average or creating other calculations, that's when that really becomes more important for this level. In terms of the beginner coding, the way that you would try to make things more efficient is to just understand when you'd want to look at everything or just a little bit of something. We'd use the limit clause in that sense. Just take a snapshot for a bit.

We do have a course on that. It's called improving query performance in SQL Server. A lot of the stuff is SQL Server specific, but most of the principles.

We have a SQL server one and we have a PostgreSQL database.

Is there a Postgres one as well?

Okay.

So, yeah, there's a improving query performance in postgres as well. So if you want to learn how to make your code go faster, take one of those courses. All right, let's carry on with this one. I will go to more questions at the end.

Cool. Right? Where did we leave off? Here we go. All right, so this is our sort of final ish step. Step three. We're going to go into a bit more about cats and dogs. Because it's everyone's favorite, I think. So let's start with what's the most common cat breed at this shelter. So we are going to use a lot of what we've already talked about and kind of combine everything and change a couple of minor things. So let's see. Feel free to start coding ahead of me. See if you can already figure out how to answer this question. In the meantime, I'll just start talking. So we wanna. What are we finding the most common cat breed of the shelter. So we want to select the breed and we're gonna count the breed as well. If you remember from counting the animal type. And we're doing the breed count for our little alias, so we remember what everything is from the same CSV file. Shelter CSV. And we're going to filter most common cat breeds. So we want to filter for the animal type that is the cat. So animal type. Now just reference a question. Again, we're referring to a text cell of a. Of a column in the data set. We want to find the word cat in the animal type column. I'm going to use single quotes for that cat. All right, group by breed. So as I mentioned earlier, group by statement likes to know what the buckets. What buckets? It's counting. So we're counting breed, but we don't need to add breed count here. We just add the first that's not been aggregated, which is just the breed column on its own in this breed in this group by statement. Okay, now I want to order it. I'm looking for the most column most common. So I want to order it so that the highest number of cat breed counts is at the top. So I'll use order by for that and order use the breed count alias a little bit about that in a second. So I'm going to use another thing here in the order by statement, because I want it to be from the highest number to the lowest number. So it's in descending order. So we add a little short des desk desk thing at the end of order by. And another reference to a question earlier about just making things a little bit more efficient. I don't need to see the ENT output here. I'm just looking for the most common one. So I'm just gonna ask for like the top five rows, so limit five. All right, let's see. Domestic short hair mix. Domestic medium hair mix. I mean, I, I don't know that much about cats. I don't know if this is like really specific breed or not, but this doesn't tell me a lot of information personally. But good to know that there's a lot of those out there. Um, all right, so just briefly on this order by segment of this query here. So you may be wondering, but hang on. Order, breed count isn't actually a real column in this table. You just kind of made it up in this alias here, which is true, it's not an actual column. So the reason we can use it in order by is that at this point the select statement has already done its job and we can use the alias here too. Now, this isn't always the case in every single line of this query. For example, we would not be able to use an alias in the group by statement. Now, even though the select statement with the alias is written before these other two lines, it's not actually the first thing to run when the code is running. So the code runs in a different order than what you write it. So that's something to keep in mind as well once you're learning to do more advanced queries. So, for example, it's actually the from statement that runs first, and then from here, I think it's the where statement. And then it's group by, and then it's select and then it's order by. I used to know this off by heart. I don't know. It's so much I rely on just running something and it telling me if I got it wrong and then I have to do it again. But I'm pretty sure that's the order. Something to keep in mind that the written order of SQL isn't necessarily the way it's being executed in the background. Which is why we can use the alias in the order by statement. Let's add a little something new. Still going to be a similar query. But now we'll find out the most common common dog names. But we'll only do the most common dog names of the year 2023. So I've gone to 2023 because when I pulled this data set they didn't have all of the 2024 data up yet. So you can definitely use this workbook, go to the website and maybe you can add the rest of the data and find out what last year's most common name was. But we're going to do 2023 for this time. So instead of. Yeah, it's very similar to the last one. So let's just get started. So we're going to select name this time instead of read, we're going to count the name and we'll use an alias and it's from our CSV file. And we'll do a filter. No, that's not his animal. Animal type. This time it is dog. Now we have another filter we want to add, which is to find the year 2023. If we go back to our little first sample of the data and the description, we could maybe use this datetime column. But it's a timestamp, it's a bit tricky. I don't want to use it. This column. Month, year. It looks like it's not actually a timestamp, it looks like a text file. So let's test that theory. And I'm going to use this column here to write the rest of my filter. Nope, I'm going to keep going there. Okay, so month, year. Now so if you notice the column, it was written like month, space, year. And what we want to do is just find anything that has 2023. So we want to look for something that is similar that has 2023 written in it somewhere. So we'll use like in this filter and we're going to use something called a wild card, which is this percent symbol and then followed by 2023 and another percent symbol. So what this percent symbol means is that I don't care what is before 2023 or after. I'm just looking for the existence of 2023 in this column somewhere. As long as that criteria is met, then it would like to capture that. So this is useful. So if I were to just take out the percent at the front, then it would only look for instances where 2023 comes first in the text. And similarly, if I were to take it off the back, then it would only look for instances where 2023 is a the end of the text. Now, I think we could actually do it this way for this dataset. But just in case, somewhere in the hundred thousand rows of data that they've written it the other way around, I'm going to put both in so that I capture those as well. Okay, so now we're going to go back to what we know before. So we're going to group by the name. I'm going to order by the name count because I can use the alias from top to bottom. And I'm just going to look at the first few rows. Okay. Luna is the most popular name of 2023, which doesn't surprise me at all. I love that name. Although it does put me off that it's that popular. So I may need to think of a new name for my future Dollar Dog. But we'll see. I still like Luna, Max, Bella, Milo, Desi. Anybody have dogs with those names? Let us know. All right, how many animals were adopted in 2023? Our next question. Let's see what we need to use to query this because I don't quite remember. So we'll scroll back up quickly into our sample. So we want to find that the outcome is adopted. So outcome type is the column that we'd like. And we're looking for the adoption word. Okay, cool, let's use that. Scroll back down. Hopefully the scrolling isn't too fast. All right, so we will. What are we doing? We're counting how many animals were adopted in 2023. So we're going to count. And we're going to count everything again. Everything in 2023. So count all as adopted from our animal shelter CSV file. Where. So the column was. What was it? Outcome type. And we wanted the word adoption. Adopted. Adoption. Adoption. Okay. And we're going to do our little year filter again. Percent sign. 2023, percent sign. Okay, let's see. 6634. That's nice. I like this number. That's very good. All right, your one final time for you to do things on your own. So remember everything we've looked at today and can you answer this question of what was the most adopted animal type and breed in 2023? So I'll give you a bit of time to have a look through all the other queries we've done today, see what you can add together. And then we'll write this one final query and that'll be it for us today. And remember, add any questions in the chat and we'll have a bit of time to do them after this. This period.

It.

Okay, how did everyone do? Is everyone getting along with the query? Is it it a challenge? Is it doing? Are you doing well? How's it feeling? Let us know. All right, so I'll get started then. I'm just explaining our last, or talking through, actually our last query. So what was the most adopted animal type and breed in 2023? So, animal type and breed, those are two things we're interested in. And we want to count how many were adopted. So count all as adopted from our CSV file where outcome type equals adoption. Did everyone get this far? All right, let's keep going. So we're looking for 2023 again. So month, year, like percent 2023. Yep. And now, so if you remember, we want to tell SQL and the group statement, which buckets are we counting? So we're actually counting both the animal type and the breed, and we'll order by our aliased column, adopted from top to bottom. Okay, well, let's just throw in a limit as well, because we're looking for the most adopted animal type and a breed. Cat, domestic, short hair. All right, and then dog comes in at number three with a pit bull. Interesting. Okay, I like that. German shepherd. That's my favorite dog. I like that. Great. How did everyone do? Did you manage to write that query on your own? If so, congratulations. Well done. If not, no worries at all. This was just a practice session. And feel free to go to our courses to get some more practice if you'd like, or just use this workbook to carry on as well. All right, with that, we're at the end, and I'll like to ask Richie to come back. Let us know if we have any questions.

Wonderful. Thank you so much, Yasmin. That was very cool stuff. Yeah, lots of questions from the audience. If you're not written your question yet, please do add them to the chat now. We've got plenty of time for those. Also, please do remember to hit that like button if you have enjoyed the session and learned some things. All right, we have quite a few questions. Questions around. What is a Pig mix. I'll start with this one from Belina. So as just while you were talking there, I was trying to look this up to see can you have a pig mixed with another animal? It seems like you do have pig, human sort of genetic hybrids for organ transplants. Chat. GPT also suggested that you could get pigs and goats mixed together, but I think that might be a hallucination. I'm hoping it is. So, yeah, not quite sure what the pig mix refers to within the concept of this data set.

Wow.

Yeah. All right. Anyway, I was a whole separate rabbit hole I went down. So there was a, a more related question to this data set. So Rich is asking, how do you separate the many different variations of pig mix and group them together in a sensible way? This is quite a common task is when you've got lots of different variations on a category, how do you clean up those different categories? Do you want to talk through the, the principles of how you might go about doing this?

Yeah. So this is something where you'd, you'd, you know, I think there's, it depends on the person and how you want to approach this, but I would just. Sticking to the skill set that we have here today, I would probably use the wild cards that we have, for example, for this month year query. And if I notice that everything has pig mix written, even it's got like something else like Picmix and then I'll just search for pig mix and it'll capture all of the ones that are not just pig mix as well. So that's one way to do it.

Yeah, yeah, that's a very good point, is that there are lots of tools for dealing with text and those are going to be pretty useful for cleaning up those sort of categorical columns. All right, nice. So let me see. This one comes from Graham. So Graham says, is he sequel K sensitive? I hope most people have noticed that it's sort of yes in some places, no in others, but yes. Just want to clarify when is it case sensitive?

Oh, this is, this is. Got to be honest, this is one of my pet peeve questions because I always have an existential crisis when I come across a case insensitivity issue and I've. I forget what life is. So I, I think, yeah, I mean it's definitely case sensitive. I think when you're querying the column names, in fact, you know, like I said before, you don't remember everything. When you're, when you're learning to code, sometimes you just need to figure it out.

Yeah. So with the Column names. If you just write the column name as is, it's not case sensitive, but if you put it the call name in double quotes, it treats it as case sensitive in most variations. I think SQL Server, you have to wrap it in square brackets instead. Oh, wait, interesting.

It still runs. Okay, this is what I mean. This is one of those questions where I'm just like.

Okay, I think the advice I was given might have been PostgreSQL advice. This is running DuckDB in the background. So different versions of SQL, different answers for this. But one thing where it's always going to be case sensitive is going to be the. I think the values in those categories. So in the where bit where it says outcome type is adoption, I think that it's going to be case sensitive.

Just correct this one. I'll go back up where. So let's just put that lowercase. Yeah, so that's case sensitive. So we've got zero now instead of when I put the capital letter. Got 6,000. Yeah.

Okay, so it's case sensitive in a very few.

Yes and no is the answer.

Yeah, mostly not, but occasionally. All right, so this next question comes from Trudy. So Trudy says, is querying a way of extracting data from dataset? Yeah, I just want to talk through, like, what's the jargon? Querying me?

Yeah, so querying. I think it's more about asking questions and extracting insights rather than extracting data. Because you're not physically, you know, removing anything, so to speak. Everything is still intact in the database. You're not changing anything there. You're just kind of rebuilding it and spitting it out in a different format to answer your question. Specific questions. So you're just finding out some little. Discovering little bits of information, extracting insights rather than actually extracting like anything physical.

Yeah, I like the idea of it's like asking questions because I suppose although the most common query type is going to be a select query where you're retrieving data from database, you can also do things like inserting new data into the database or deleting data or. Or updating existing data as well. So, yeah, that's more like if you're a database administrator, you get to do that. And usually the analysts aren't allowed to break things in that way. But yeah, there are other types of queries. Okay, so let me see. This one comes from Matthio. So Matthew's saying, wouldn't adopt be a better word to filter on?

Maybe.

Do you want to try seeing what would happen if you.

Yes, because there is this rto adopt. I'm not really sure what rto adopt means, but let's try. Let's see what happens. Why not? This is part of the fun. So let's see. So if you wanted to do that, I would. What would I do? So I could use the like again, I think. And do is the capitalize or not? It is capitalized. Good. I like that. Let's see if it works. Where was I?% no, that's not a percent percent adopt. Does that work? Let's see. Did it work? Oh, we got a different number. So yeah, I think it did capture the other rto adopts as well. So probably a better way to query. But since I don't know what rto adopt means and I can safely assume what adoption means, I went with adoption. I'm gonna go with that explanation.

RTO return to owner, maybe. Who knows?

Oh, yeah, that could be a good one.

I'm making things up.

Okay.

Oh, actually, while we looking at like, I don't know whether DuckDB has this, but there's an I like keyword as well, so case insensitive stuff because you're worrying about does do you need to do capital A and adopt?

Oh, I've never used that. Interesting.

Okay. All right, so next one comes from Hayden. So Hayden says, can you explain group by statement a little more? Group by is like the first slightly tricky thing you generally come across in this language. So let's go through that properly.

Yeah, so what the group by statement does. So if you imagine you've got a table. Well, let's just use our little sample. Don't want to scroll that. Let's go back to our sample 10. So what it does is it groups rows that have the same value that you've specified into like a summary of one row. So in this case, you know, outcome type. We looked at everything that was adoption, so it would group all of the adoption rows into one and then summarize it in a way that we've asked it to. So for example, in one of the last queries, we asked us to count how many there were. So it'll just summarize that for us in one go. It can also do things like the summary can be different. So maybe if you're grouping a numeric number, you could find like the average number or something. So if I using a different data set that I'm just making up now, maybe you have like movies that all and you have like a table that has movies and the length of the movie and the genre of the movie, and you'll use the group by statement to maybe group by the genre, and then you'll find the average length of all of those movies. So that's a handy way the group by statement works. I don't know if that explanation was any better or helpful, but hopefully it provided a bit more context. Yeah. Oh, I think you're on mute, Richie.

Oh, sorry. Yeah. Yes, much clearer and I guess, yeah, go and take the course. We do teach group by in many, many places throughout the SQL courses. So if you take a few intro courses, you should come across it a few times. All right, wonderful. So this is actually a pretty interesting idea. So from Billy. So Billy says can use the extract function instead of a wild card to extract the year from the month year column. So extract, I think, is a DuckDB specific function. So the answer, I think, is yes, but it's not usable in all different types of SQL. So let me have a look. You should be able to do. Yeah, extract. And then year is a string from. From whatever the date column is. Yasmin, I don't know whether you want to try this live or not. I'm just. I'm just looking at the DuckDB docs now to see if we can figure out between us. Go on. Do you want to find one where you. Where you're pulling out the year from? Month year.

Well, the month year. Well, I don't know if it'll work on a text column. It def. It works well on date columns. Should we try both?

Yeah, I think extract works from a string column. We can try it both.

What am I doing here?

Instead of month year, like 2020, you're going to do extract, open parentheses. And then the first thing is going to be year in single quotes. And then. Yeah, I think single quotes.

Yeah.

And then the second argument is. Oh, sorry, not sync the argument. You can say from date and then month year, I think. And then get rid of all the other bits. Yeah. Let's see if this works. It's nice to just experiment with stuff like. Okay, oh, we need to get that first month here, get rid of that bit before extract. Let's try this. Yeah. Okay. Don't like it. Okay, this is one for the audience to play around with. It looks like it's probably possible. We're not quite sure. We've not used this before, but in theory that sounds like a very good idea. All right. On that, on that failure. Let's. Let's move on to future webinars because we've got a lot coming up for you. So tomorrow, this is for the New Year's resolution crowd. So we've got a session on using data and AI to improve your fitness. So we've got the chief product officer from Zing Coach and the head of R D from the same company just talking about how they make you, how well, basically how you can use data to an AI to do fitness more effectively. On Thursday, it's going to be me and Adele talking about data, AI trends and prediction. So what's likely to happen over the next year? And on Friday, we've got Shannon from Microsoft talking about dashboard. Oh, sorry. Shannon and Stephanie talking about dashboard design in Power bi. So if you're interested in analytics, then that's definitely worth coming along to. So tons of stuff. Yeah. And please do come back for future sessions. You can use the QR code to register again. Please do hit like on this video if you've enjoyed it. I hope to see you all again future sessions. Thank you again to Yasmin. That was really cool stuff.

Thank you.

Thank you to recent moderating. Thank you to everyone who asked a question. Thank you to everyone who showed up today. See you.