SQL Basics Summary

Okay, so let's do a first query. I'm gonna delete everything here. And I'm just gonna go and say select space star space rom space customers. So this is my first query. It's very simple. And if you notice, I use different kinds of cases, uppercase all caps for select all caps with rom. So this is not really necessary, but it's like a naming convention. When we have keywords from the SQL language, we usually capitalize everything. Okay, so what does it do is allows you to get information for a specific table. In this case, the syntax is always to say select and then space in star means, okay, I want to see all the columns space from from the right hand side of from, you have to say the table name. In this case, I have a table called customers. So make sure to use the same casing here. I know there might be some SQL implementations that don't care about upper lower case, but I suggest you always try to pay attention to the casing. Okay, I'm gonna click run SQL. As you can see in the output here at the bottom, I see the output for the table customers. And it tells me, okay, got several rows. First row being John Doe from the USA, age 31. And then the second being Robert Luna, 22 from the USA, so on. Okay, so the star here means I want to get this column and this column and this column and that column, basically all the columns in the table. If you notice in the right hand side, the left hand side of this page, we kind of have a enumeration of all the columns for each table. The table of customers has these columns here. So when you say star, you're saying, I want to see the value for all of these columns. Now, if you want just a specific column information from a column, maybe I only care about somebody's last name. I can go here instead of star, I can say last under for our name. That's the exact name for the column here. And you can see here as well. And I click run, what I'm going to get is the last name for each row. So it gives you the first is Doe, the second is Luna, so on. Now, what if I want first and last name? Now that's easy, just separate them by a comma, okay? So I can either put before or after. Let me put at before. So I'm going to say first under for our name, may not have a comma there. And I click run SQL. You're going to see now I get the first name and the last name. Okay, I usually like to add a space after punctuation like comma, but that's not required. It's just for me to better visualize things. Does anybody have any question? You can always post to the comments there. Let me see if I... Anybody have any question? Question you keep putting underscore, is that needed? Yes. So, you have to pay attention. Every character matters, the case, it matters. So if you see this column name here, the column is literally first name underscore name. So you have to use the underscore there. So different SQL implementations kind of have their own naming convention. In this case, they are using what's called this convention here of underscore case. So meaning every word that you have, you're going to separate it with underscore. If you had like without the underscore for the programming languages, it's kind of difficult to have a space because it would mean like two different things. So that would be really confusing. So that's why we always put something to separate the words or you don't have to put anything. There are some naming conventions where all you have to do is capitalize the first word, the first letter of each word like Pascal, Casey, Microsoft, SQL server. Okay, but you gotta have the underscore. All right, more questions. Pretty much a data repository, plain company started data. What's the purpose of semi colon? Semi colon here is always needed at the end of every statement in SQL. So I forgot to let you know about that. So in SQL, all the commands have to end in a semi colon. That's just for the syntax of the language. Okay, so make sure to add that semi colon at the end. Implementation the database will determine how you carry information in it. I'm not sure your question, Maggie, but every database has their own implementation of SQL that might not necessarily be the same. I do not know the internals of the database implementation here. So that's something you don't need to know how the database is built run internally. You just need to know the language called SQL in order to interface with that and be able to manipulate or get information out of it. Can we use like first name plus last name? That's called concatenation and it is possible. There's a function that there are functions that you can use to concatenate things. But the way I did things right now is separate columns. But if you're interested in that, I think it's called concat and you would call it here like this and pass the arguments and finish with like this. Something like that. Just look it up and you eventually find something. Okay, so I'm gonna continue. So let's talk about country. Okay, where are people? Where are the countries? So if I wanna select only the countries from these customers, I would write here to let country from customers. So I know where my customers are from. So you can see I have customer from USA, another one from USA, another UK, then UK, then UA. As you can see it repeats, right? Because I have one person per row, even though you don't see their first, last name. But this is kind of sucked. But if I have like a lot of people, one million people, there certainly isn't a million countries, right? So I wanna be able to see a list of all the countries my customers come from without necessarily having to go through all the one million rows. So in order to do that, you should do this thing before the column name here. So what this does is it groups every duplicate into a single row. So if I click run SQL, I only see unique country names. That way, if I had a million rows, I don't have to go through every one of them to see which, who is from what. If I do this thing, I can consolidate into probably at most 190 something countries, right? There's no more than 200. So somebody asked the question I wanna know, the way prayer information is SQL standard, regardless of how it was implemented? Yes. Like I said in the beginning, standard SQL as a standard language, if you'll know how to write SQL, this way we're writing, you can do pretty much any SQL database because they all follow the same patterns. It's a standard. They just implement, when I say implement, They just implement when I say implement it just means they Construct the thing you're gonna use. It's like a blueprint. You have a blueprint from a car Everybody's gonna follow that same rules, but the blueprint by itself don't necessarily Let you drive right because you need somebody to build a car So sequel is like the blueprint and the companies have built the car meaning their own cars, right? We have different kind of makes so that each have their own variation But they all fall in the same pattern so we can now drive over wheel we can break and throttle and so on