Okay, today we'll talk about SQL Structured Query Languages, so just an overview why we use this. Basically, you go on any website today, social media, whatever favorite app you're using, and what you see is the front end is the visuals, so we got a website or an app, but then you're typing some information, you're making a post, you're talking to somebody. So where does all that data go? It has to be stored somewhere, so it remembers the next time you access the system. You have all that data there, you have all your references and settings remembered. So that's all done in what's called a database system. So what usually happens is what you see communicates with a back end server, and that server in turn will ask a database to retrieve some information or will give it some information so it can store for later use. And one of the most popular database systems today is SQL, or also pronounced SQL, and it's called a structured query language. It's like a standard language through which we can talk to a database system. And just like we have cars of all different makes and models, and so we have SQL, if you look up RDB and mass, which stands for relational database management system, you're going to see there's multiple flavors like MySQL, Postgres, MariaDB, et cetera. So there's a sequence like a blueprint for creating a car. You got the blueprint, but the car is not yet constructed. Who constructs the car? The companies, right? We got Honda, we got Ford. So each company has their own kind of a different style, but in the end it's the same kind of mechanism for the car. The car drives the same way. The car has the same attributes, you know, but they all construct the car in a slightly different way. And that's the same with all these SQL flavors. So you got MySQL, MariaDB, you got Microsoft SQL server, and so on, but they're all the same thing. So if you learn SQL, you can pretty much do any of these flavors. Okay, now that you've got that understanding, going back here to this website that I'll use. Again, I'm going to paste the link in the Zoom chat for those who have arrived later. We're going to use this environment because it's very nice to get started with SQL. So it's called relational database because there's a relation between things. In this case, between tables. So we're going to store information in tables, and each table has some rows. Think of it like Microsoft Excel with spreadsheets, Google Sheets, you know how we have columns and rows. Pretty much the same thing. So if you look here in this right-hand side, these are the tables that we already have created for us. And there's a table for customers to keep track of every single customer. So you can see the column name here. And then it's for each piece of information. For example, we have a person's first name, that's one column. And then we have a person's last name, that's another column. And then the age, and then the country that person's from, and so on. So each column represents a piece of information. And then if you want to access information for a specific record, in this case, it's a record of customers. So each row is a different customer. So row number one here would be for the customer, John Doe, whose age is 31 from the USA. And then the second customer is the second row and so on. You see, the rows is each unique record. And then every column is a piece of information about that record. And then we can have multiple tables. So we have a table of customers, and that's where all the customer information goes. Now, if you want to keep track of the orders, you can create a table here of orders. And then you can see what orders, in this case, it's probably like an e-commerce kind of thing. Somebody is selling something, and they're keeping track of the sales of the orders. So somebody ordered a keyboard, another person ordered a mouse. So each row is a unique order, a different order. And you can see here, there's actually a column that tells us who ordered this. What typically happens is we have an ID or identifier that points to another table. In this case, the customer ID four. So this means the customer whose ID is four bought a keyboard or ordered a keyboard. So in order for us to understand who this customer is, we would have to go to the customers table, look up the customer ID whose ID is four, and then find out John Reinhardt is the one who bought the keyboard. So this is important to separate the information, the concerns, because that's the point of the relational in the part of the database. Because if you want to access information, you have to combine tables together. So they're related by a specific piece of information. So you always want to separate things because you don't want to duplicate the information. You know, imagine you have customer information in the artist table. That'll be kind of a mess to what if I change the customer name in this table and then the other table would be out of sync. And then this other table would be out of sync. So everything has to be like in one single place, single source of truth. So if you need to access that information, just point to the ID, the identifier. And as we'll see later, we can combine these tables into a single table if you're curious. Okay, how can we access information in one single table? You can combine them with joins. And finally, there's this other table about shipping. But enough of that, let's get to practice here. Yeah, I've made another person. Okay, so here in this editor, you're going to see this text area here. That's where you can type what we call the SQL queries. This is when you ask information about the database. And you want to interface with the database through these statements or commands. Now you might have noticed the left hand side here, there's some stuff more information about each table. This just means the definition of the table information. In this case, the customer's table, we have the following columns. And then the thing in square brackets is the type of each piece of data. In this case, the first name is a var care, meaning character varying, basically sequence of characters. And then you see ages int, int means integer. It's a number integer, you know, one, two, three, four, and so on. Anyway, enough of that, we can talk about that later. I can click run SQL to see your query executed here at the bottom. And when I say select first name, age from customers, it's basically selecting the column's first name and age from the stable customers. But let's start from scratch or delete everything here. So the most basic way to interface with the database is to ask for information. Hey, I want to find out about customers. So how would I do that? Well, I would use the select keyword. Now mind you, I wrote everything uppercase, and the reason is just for convention. You actually don't have to write uppercase, but every time I have like a special or reserve keyword from the SQL language, I will make it uppercase. And then space, and then let's add a star, and then space from, that's another keyword, and then the table name. So I want to get information about customers, so I make sure to write the table name here exactly as it's written here, with the capital C and U, and the rest lowercase. And then I finish off a semicolon. That's important. When you have statements in SQL, finish with a semicolon. Okay, so what this means, okay, I want from the table of customers to select all, star means all, the columns. When I click run SQL, you're going to see in the bottom, the exact same thing we see on the right will appear at the bottom. As you're taking the table and all the records. Obviously, this is a small data set that we have for practice, but in the real scenario, we would have probably millions of rows, if not more. So that's really hard to read everything at once. But okay, so this way we can get information about everybody. So each row is a customer, and we have each piece of information as the columns. Now if you want to be more specific, I don't care about the age and the country, I just want people's names. You can always start off, maybe just the first name here. I just want the first name, so I can select the name of the column. In this case, first underscore name. Make sure there's an underscore. You got to type it the same way it's defined here in the column. You see first underscore name, or you can look in the left-hand side here. And then click run sequel, and you're just going to see the first name for each person. And of course, you can have people with the same first name, so be careful, don't confuse because there's a John in this first row and another John in the fourth, but they're not the same. They're different people, technically. And then if you want more information, you can keep adding more columns, and you separate them with a comma. So you would do, for example, I want the last name, last underscore name. I can type it there. And now I can get the first name and the last name. Now, what I usually like to do just for my coding style is I always put space after punctuation like comma. So you might see that a lot, but it's optional. You don't have to have it. But I think it's nicer for us to see that way. Also, you don't have to have the statements all in one line. You can have them separated like this. I put the from in another line that's totally fine as well. As you're going to see, if you get more and more complex with your queries, they're going to be very long. So it's nicer on the eye to break it into multiple lines. There's usually a convention for that, different conventions that you can use for this kind of style. Okay. And that's that. Keep going, adding up more columns or removing or changing the R is maybe one last name first for some reason. And then the first name has the second column. You can swap them. It's going to be just swap. That's the output. If you have any questions, let me know. Is there any keyboard to run SQL? What do you mean? Is there any keyboard to run SQL? Shortcut to run this on this environment? I don't really know, to be honest. Shift, enter, doesn't work, alt, enter. I don't know. If you find out, let me know. There might be a key listener here somewhere. I don't know. Anyway, so nice. Now what if I have a question, so let's try to figure this out. I want to know where my customers are from. So I need to ask my database information about my customer's country, right? We first understand that the database has a column called country that allows me to see who's where they're from. So maybe I can start off saying select country from customers, because country is the name of the column. So you can see USA, USA, UK, UK, UAE. Okay, this is all right. But imagine there's a million rows here. I cannot possibly go through a million rows. It takes a long time. It's very tedious just to understand where my customers are from. So what I have to do is here, okay, I realize there's multiple USA's, right? I don't need to know that, I just need to know that there's at least one person from the USA, at least one from the UK, to understand that I have customers there. So in this case, what I can do is remove duplicate rows. If I precede my column name of the keyword distinct, so if I put distinct space there and click run SQL, it will kind of collapse or consolidate all the duplicate country rows into a single one. So that way I can understand, okay, USA, UK, and UAE. That's much nicer. And I know that there's under 200 countries in the world, so that's much better than reading a million rows to find out where they're from. Okay, and then I could give this list to whoever's in charge of business, let them know that, hey, these are our customers. You might want to target these countries more, something like that, you know, business intelligence. Any questions so far? Okay, let's go on. So that's distinct. How about we filter things? Like maybe I want to, somebody comes up to our, you know, maybe you're a shop owner and some customer comes, they want to do something, then you've got to look up their record on our database. How would you do that? Well, usually people give their names and that kind of stuff. So how about we go and find out, if they say their last name is Do, how can we find all the people whose last name is Do here? So I can select, let's do select star, select all, I'm not putting one single line from customers, but I wanted only the customers whose last name is Do. So I would add a where you were here, after the from table name, where. So where the last name, that's the name of the column is equal to, and then you got to add some single quotes here and type the name between with the Do there and click run SQL. Now I can find all the rows. These are all the people whose last name is Do, okay, obviously there are multiple people whose last name is Do, so it's not very good to find unique records, but at least I narrow down more than I had before. Okay, and they could do it more, they could give the first name as well, then you could look up that, but before we do that, I want to just do this experiment. What if I put lowercase D here? What happens? It gives nothing, result set is empty. So be careful with the casing that might matter, okay, in this case. So I put back capital D. Now I might be asked, oh, that sucks. Well, there's a way to not care about the casing, maybe you could use a function called lower and lower everything, both the last name and the thing you're looking for. So they're kind of normalized. You can look up the lower function by yourself later. In any case, like I said, okay, I want to know also by the first name. They tell me that their first name is John, so how can we do where last name is Do and first name is John? Well, we're going to use the end keyword. And then first name, the column name equals John, oops, and then click run SQL, now we narrow down even more. The last name is Joe, the first name is John, okay. Now typically, when we look up records, we look by a unique identifier, so you might have noticed this column customer ID here, and this is usually called the primary key or PK for short, and it's usually always present in when you create SQL tables. You add a unique identifier table, usually called the ID for short, and that's the primary key who uniquely identifies the record, because you know there might be a person whose name is John, last name is Do, more than one person, right, in the world that's got to be more than one person, so many people. So that's why usually when we look up customers, we look up their ID, right, account ID, customer ID, whatever. So here instead of saying first name, last name, you typically would say, hey, customer, what's your ID, what's your account ID, so we would say here where our customer ID is, this case for John, that would be a one, right. And then you click run SQL and you find exactly that one person, that's just going to be one single row, okay. Okay, enough of that, let's learn about inserting new records, because we talk a lot about retrieving information, how about inserting new information, you got new customers, they got to be added to the database. So to do that, we're going to use insert into, those are the two keywords, inserting two table name, table name, let's add a new customer. So customers, that's a table name. And then values, that's another keyword. And then parentheses here, finish semicolon, and then we've got to type the values for each column in order, in the parentheses. So to understand the order here, we look at the definition, first is customer ID, then it's first name, then it's last name, age and then country. So follow that order, separate with a comma. So let's see here. So a customer ID has got to be six for the ID because there's already five. So the next one, usually we always increment by one, and that's already usually taken care of by the system when you work with that in the real application. But for the sake of our exercise, let's add the ID there. And then the first name, let's add Anna with a single quotes, comma, and then long will be the last name, and then 21, age, and then from Canada, Canada then quotes. So I've noticed that I put quotes for Anna, but no, not for the number, that's because the data types for the column. I know that the customer ID here is int, meaning itager. So you don't really need to put the quotes. But for the case of the first name, last name, that's a vercare. Vercare is basically a character varying string, string is a sequence of characters. It could be in this case up to 100 characters, meaning I can have one character, two characters all the way to 100. And yeah, so if I click run SQL, I will see, notice how it added to the end of the table customers a new row. Okay, so what if I try doing clicking again? This is error, let's read the error. Unique constraint failed customers dot customer ID, what does this mean? Well typically when we have a primary key, that's a unique identifier to the row, meaning there can be no other row with the same primary key. In this case it's saying that this is called a constraint, in the case it's a unique constraint for that column. So when you read this customers dot customer ID, customers is a table name, dot, and then customer ID is the column name. When you see the dot, the thing on the right usually belongs to the thing on the left. That's like an object notation in programming in general. Okay, and yeah, so let's learn how to insert, you know, I had to remember the order of the columns here, but you don't have to remember that if you specify here on the left-hand side of the, yeah, let me see, right-hand side, sorry, left-hand side of values and right-hand side of the table name, you can specify the column names, in this case customer ID, and then first name, and then last name. This is the explicit way. The one that I first did is implicit, and then AAC is kind of tedious, and let me break it into a new line here so you see it better. And if you have it this way, you can kind of swap the order, maybe for some reason, you don't want to, I don't know why, but you want to say the last name first, so you want to say long before Anna here, for some reason. You can do that, just swap them here, like that, you can see I put the last name before the first name, even though that's not the original order, and if I put, let me put seven here to make it, and then if I insert and search the same way, even though I put them in different order here. So that's why, usually you put the like this explicitly, if you don't remember the order, but you're always going to kind of understand it. Somebody asked the question, if it's int value, so we don't need quote, that's right. So if you have an integer, you don't need to put the quotes surrounding the number. Now the comma, that's another matter, that's from the syntax, for example here, when we have values that we have to specify when inserting a new row, we separate each value with a comma, and I put a space for the sake of readability, you don't have to have a space after the comma, okay? Now the comma is just part of syntax of the language SQL. Doesn't matter if you do not add all the column parentheses, let's see. You know, some columns, you can specify if the value is required or not, so that would depend whether this is required by default or not, let's see. I don't know specifically this implementation of SQL and how these columns, if they're required or not, that's called the allow no constraint. So let me remove the country here, remove the country and see what happens, see if it's required. Oh, I got to change the ID to eight. Okay, that's it. So I was able to add a customer without a country, so apparently this table was created with a country not being a required value, so you can allow no values. No is like an empty and you all, you're going to see that a lot. So basically allow no, right? Okay. Now you can see it's empty there at the country. Anyway, if anybody, everybody's okay, I want to move to the next thing. All right. Okay, let's say, you see I added land on like three times, let's say I want to remove one of them or all, how do you remove things? That's the delete from and then table name, customers, but stop right here. Delete from table name is dangerous because if you run this just like this, everything we wiped out, don't do this. So whenever you need to delete something, you have to always put a filter aware to say one thing. So where usually it's the ID, customer ID equals something. So delete from the table customers where customer ID, in this case, I want to delete this repeated duplicate Anna, that's who's customer ID is seven. So I put seven there and semicolon, okay? This will only delete one. I know for sure there's only one row because I'm doing by primary key, primary key is unique. There's only one row who's customer ID is seven. So I am very safe to know that this is fine. I'm not deleting all the rows. If I run that, it's gone there. How will we recognize if it's allowed or not? That's usually you can see that in your system. It's because this environment doesn't quite show it. But when you create a table and you can ask information about to describe a table that already exists, it will tell you there whether it allows no or not. So you can see it from your actual system. I'll show you later as I'll show an example of a graphical user interface for Postgres. Anyway, okay, remember folks, always put aware and delete, otherwise you delete all the rows. But if you really, for the sake of example, you want to delete by something else, maybe you want to delete all the people whose name is long, just for example here, okay? I can put here last name long. If I do this, just looking at this small data set, I know there's only two people whose last name is long. So that's if I intend to delete them, this is fine. But in the real world, there's so many millions of records, you cannot possibly know that those are the only people that you're going to delete. So don't do it in practice. If you don't, okay, so I deleted them because I know for sure this small data set. Nobody has there any way to readjust the pk once we delete one row. What do you mean readjust the primary key? Typically the primary key once given, you don't change it, okay? Because that's not good to change because, you know, imagine our customer and you come in, hey, my customer ID is four. And then for some reason it was changed to 10. It confused everything and everything that depended on that would be messed up. So we typically don't change when the customer, the primary key once it's given, okay? Now let's talk about updating. For some reason, some customer moved to Canada. Let's update their record. Okay, let's say Robert Luna moved to Canada. How we do that? Well, update is the keyword. Pable name is customers. And then we're going to set whatever, let's set the country to Canada. Country equals Canada. Okay, be careful. Again, like delete, if you just do this, every single row be updated to Canada. For the sake of example, I'm going to do it, but don't do it. I'm going to click run and see what happens. Everybody moved to Canada. This is not good. Never do this. You always want to add the filter, the where, okay, when you update. Okay, I'm going to reset this because this is not real world, but the real world is, this is a pain too, okay? Don't do that. I reset so I get my tables back, my table information back. Anyway, you want to add a where here? Usually it's by ID because that's the safest choice. And what was it? Robert Luna's ID too, right? Customer ID equals two. That way only change Robert Luna, customer ID two goes, now he's in Canada. Jennifer asks, what if we do by mistake if we have deleted the wrong customer? Well, you try not to do that. If you delete it, it's gone, but yes, there are ways to backup. There's always backup, meaning you're running constantly backing up the database. And if by some access, you did that, it'll be kind of a pain, but it's possible to recover it. What you do is got to look at the last backup and recover that, but that's usually not straightforward to do, okay? You never do that. Be careful when you do these operations. When in the real world, it will ask you, are you sure you want to do that? If you're using web interface? So think before you click, okay. But there are ways to always reverse, but it's complicated in the sense that we have millions of customers using the app all the time, and something happens, you want to go back in time in a snapshot of a previous thing. To do that, you've got to stop everybody from kind of a very simplistic way. Everybody has to stop writing to the database, hey, you cannot use the system right now, let's stop a little bit so we fix the problem. But if you stop, you're blocking other companies, other people's jobs, and then ultimately it comes out to money, right? They're going to lose money because they could be working, and so it's very bad. In practice, when you're programming systems, there are things like transactions, which if something goes wrong in the code, you write the code so that you try to do this, do that, and anywhere in the, all those steps goes wrong, it just rolls back or reverses everything. Let's call it transaction if you look into that. Anyway. Yeah. What next? Yeah. We did update, did the lead update. So update just like this. Just make update, table name, set, whatever column, and then the value, and then where. And that's all I can say about update. Let's talk about joining. Now that we got to the basics, okay, that's nice. How about I select star from orders? And I want to know, okay, what if David here, I want to know what he ordered. What are his orders? So how would I do that? Well, I would select from the table orders. I'm doing all the columns of star, and I have to do a filter, right? Because all the orders are from different people. So if I just want from the person here, I would first find out what's the customer ID. In this case, for David here, it would be customer ID three. So let's put customer ID three. So that way, if I run this, I know what that person ordered, all his orders. Okay, he bought a monitor for $12,000. That's a lot for a monitor. But yeah, yeah, you're right. Yeah, so this way, we can find out what people orders are. But you know, this is okay, but I want more information about the customer in this table here of the output. So what we usually do is combine the tables together, you know, and to do that, it's called a join. Now, how do we do this here? Let me refine this query. So select star from orders. Now we're going to do what's called inner join because we're matching the following. You see in the orders table, like let's look at the order number three, there's a column called customer ID. That's three. That's actually matching the customer ID column of the customer's table. And that matches David Robinson from the UK. So we can take, okay, let's match the customer ID in orders with the customer ID in customers. So when you're talking about the, this column customer ID in orders that points to something outside another table, that's called a foreign, foreign key in short FK. Okay. Now, when we do the inner join here, we know what table customers. Now I got to say, what do you want to match on? That's a keyword on. Now, if I put here customer ID, it's equal customer ID, that would be a problem. Ambiguity, right? Because one table has the same name. It just happened as this is, they have the same name here, but they don't have to have the same name. Okay. Usually I would say ID just for the customer's primary key. But for the sake of this, it's got to be a problem. So to take away that ambiguity, you have to proceed the column name with the name of the table dot. So in this case, let's say customers dot. So I want to inner join orders, order inner join customers on the customer table, customer ID column, is equal to the orders table dot customer ID. Okay. That way, you see, it's still ambiguous. What is wrong here? Ambiguous column name, customer ID. That's because of the where. The where saying customer ID is three, but which column? Is it orders? Is it customers? Well, in this case, it doesn't matter, right? They're the same value. So just put whatever there. You can put order the customer. They put customers dot. And as you can see, we combine the tables. If you visually, you know, to help you understand this visually, let me see if I can, let me take it. Annotate here. So what happened here was, we took this side here. That's the order stable, right? Order ID item amount. That's the red, if you can see the color. And then the other side is, I put in purple, is the customers. So we put the match them and put them side by side. That's how you see it visually. That's at least how I see it. If you're having trouble understanding what's going on. Okay, so we match the customer ID. And then we take one row from order. And then one row from the customer. And they all have the same customer ID and then put them side by side. And that way we can have the customer information there. Clear all my drawings. Close. Okay. So that's the join. Now I know, okay, the customer name is David, his age, and where he's from. Obviously, I have a customer ID twice here. That's because of the star here. So you could narrow it down if you could just want to select maybe order ID, item, separate everything, comma, amounts. I don't care about customer ID. Maybe just the last name. And then I can click around SQL, I can narrow that down. Okay. For order three, $12,000 for the last name Robinson. So that way I'm taking permission from the table orders and also from the table customers. Now, if I didn't have the where here and I put a star back, I can see all the orders and all the respective customers. You see they each conveniently match and put them side by side. So I know their names, Asian country. Somebody asked, could you also do an example of a join without a foreign key? So let me show you a diagram here. SQL diagram, then see if you can find it. Usually that join that I did was called the inner join, but there's actually other types of joining SQL. And I always like to reference this Venn diagram here to remember what's the syntax. And if you think about table, one table is A and the other table is B. In this case, customer is A, R is B or vice versa. The one I did is the one in the middle, meaning I'm matching the foreign key with the primary key and the other, meaning the records have to be in both tables, meaning I have the intersection. Now, it could very well be that a customer, you have customer would have made no orders. In that case, if you did the inner join, they wouldn't appear because you're excluding A. If A is customers. And for that case, if you want to include the customers who have no orders, you would use this other join here that has the A filled, meaning there are customers, and they can either have no orders or orders. If have no orders, they are on the side of A. If they do have orders, they are intersection because B is the artist table. So intersection means they appear in both. Okay, so this is a left join. And you can see the syntax, select, table name, sorry, column names and from table, left join instead of inner, and then on the key. So if I were to go here, I don't know if any customers, who doesn't order? One, two, three, four, Betty, right? Betty doesn't have any order, right? I can see here, visualize, there's no Betty. You see my query, my output, there's no Betty. But if I want to show her there, maybe if I try left join here, let's see what happens. Oh, let me make sure what's A, what's B before I do that. Okay, select from orders, orders A. I did A, okay? So it's the opposite. So maybe if I interchange this with from customers being A, left join, orders being B. If I do it that way, then it'll be correct. You see, Betty appeared here, even though she didn't order anything. Now she's appearing in the list because we did the left join instead of inner. Now you can see there's nothing appearing for the order because she didn't order anything yet. So if you switch back to inner join, Betty will be gone. And if you want Betty to appear, the customer has no orders, put a left join there. All right, I hope that answered the question about no foreign key. I think that's what I got from your question. But yes, what about if I want customer ID to only be displayed once or to merge them? I think I answered that. You can select the specific columns, right? Like I said, if you don't want to display it twice, you can select it here specifically. Okay, customer ID, first name, and you got to select everyone there. Anyway, let me copy this for you. It pasted in the chat. We obviously don't have time to talk about all the joins and stuff, so I suggest you do that as a follow-up. I post that in the chat. When do you do the inner join? When you need to match the records there at one table, and they'll also appear in the other table, right? That's the most common join when you match them in the intersection. You have to think about what you're trying to find out first to figure out what the join is. Just think about the diagrams. One set, the other set. Set A is table A, set B is table B, and think about if you need information for both tables or just one. What happens if one customer doesn't have information in the other? Anyway, pick up move on now. Yeah, and just for fun here, how can we know how many keyboards were sold? Obviously, visually, you can see us too, right? But imagine there's a million rows. We couldn't possibly go through a million one. So we could do a count. Let me show you first manually how we would do it. We'll select stars from orders where the item is keyboard. Obviously, that's the manual way, and I can see it's two rows. Obviously, in the real world, that's so many millions. We're not going to be possibly counting millions of rows by eye. So we've got to ask the computer to do it. So what we do is put a count star here with the parentheses. Easy as that. Click run, and you can see the count is two. Okay, just put the count, parentheses star, close parentheses to count the records that match the rows. If you don't like this heading here, count star, like you're reading it, what is count star? What is this number for? You can add an alias. You can say as keyboard sales, for example. And we'll replace that with keyboard sales just for readability. If you want to add a space, I know some people, what if I want to add a space between the words? Well, I put the quotes here, and add a space there. Although in SQL, I don't recommend adding spaces for stuff. It's a pain, especially column names, never add space. Even the casing might matter in certain flavors of SQL. It might be a pain to work with if you put uppercase for table names. And space, don't do that. Usually you follow the naming convention of your flavor SQL. For example, Microsoft SQL Server, they use like Pascal case. It's like the first letter of each words capitalized, and there's nothing between the words. In Postgres, I usually use everything lowercase and underscore between the words. So just follow whatever convention the SQL flavor uses. Okay. What else? Yeah, I think that's it. Obviously, I didn't cover how to create our own tables, but I can easily show you quickly how to do that. Let's say I want to create a table of cars. I want to keep track of the car, make, model, and near. How would I do that? Well, it's create table. That's the keywords. Create table. And then the table name is going to be cars. I'm using a lowercase convention here. And then you got to put the parentheses there. And what we usually do, visually we break a line and put a semicolon at the end and write one definition of column per line. But obviously you can do everything in one line, but it's very hard to read. And let me show you here. Let's say I want to keep track of the make of the car. You know, Honda, Toyota. I can put the name of the column. Space. What's the type here? Well, the type is going to be bear care, because that's extreme characters, right? And you can specify how many. What's the limit? How many characters at most? Usually I put 255. That seems okay, right? Do you know any maker has more than 255 characters for its name? You can increase it. And then put a comma if you add another one. Let's say the model of the car. Also bear care. I don't know. You can put 255 whatever character limit. And then if you want the year, the year is like integer, right? There's no decimal fractional. So I can put integer there. And I think this is good. I'll remove the comma here. So as you can see to create a table, just create table name of the table parentheses, define each column name followed by the type, the data type of that column. And then close the parentheses semicolon. Now if you want to know what other data types of looking at documentation for the SQL flavor, I just know by heart is that the most common ones are bear care and int. So that's why I know. You can, I think you can also put shortcut int here. And bear care can also be called character varying. So there are different aliases for the same thing. So if you click run, you can see there's now table cards here. And obviously it's empty, so we can insert new records. Let me copy here and make sure to delete the cars there because we already did it. Don't run it twice. Insert into cars, values, and I'll put Toyota Camry 2009. I click run, it's going to insert there. And then if I do another one, Honda Civic, insert there. And I can see here at the table. Now if you want to, I'll alter an existing table. Maybe I want to add, yes, somebody asked, should you add a primary key? Yes, usually we always add an ID column for the primary key. But for the sake of time and just for the sake of example, I omitted that, okay? But usually you would do that. Let's say I want to add the color of the car. I want to keep track of the color. How can we add a column to an existing table? Where are we going to say alter table? And then name of the table cars. Okay, what do you want to do? You want to alter and add column, a new column. What's the name of the column? Color, color, what's the type? Okay, very character 55, right? It's a sequence of characters. Semi-colon, click run, it's going to add a color there. I see. Okay. And then you can keep going, add more stuff. Maybe I want to add, maybe some cars are no longer in production. Maybe you want to add a column like obsolete. You can do that, alter table, cars, add column. I'll call it obsolete, but you can call it whatever you want. This is just like a value that's a flag. A flag value is either true or false, a Boolean if you know the term. So we can define it like a Boolean. It's basically zero or one. Zero means false, one means true, right? Is it obsolete? Zero is false, no. And is it one is true, meaning yes. And if you want to add a default value to this, maybe if you create new records, I want the default to be zero, meaning not obsolete, no, false. This is how you add it if you want a default value. The default keyword and then the value that's going to be default. And if I click run, it's going to add obsolete. You can see all of them defaulted to zero. Yeah, and if I want to add another record, maybe certain into cars, cars. I want to add values forward. That's the make and then the model fiesta. And I looked it up. This one is no longer in production, so I'm going to mark it as obsolete. But the color here, let's put red. And then let's say it's obsolete, I'll put true. So you can either put true or you can put one. It's the same. Okay, if I put true, let's see what happens. Table cards, five columns, four values supplied. What did I miss here? Oh, I missed a year, right? So read the error. I need the year here. Let's put 1979 before the color. And there you go. I added the Ford Fiesta, color red. Obsolete's marked as one, which is true or yes, whatever you think about, something active. Switch or toggle on. Okay. Yeah, I think that's it. And like I said, I'm going to demonstrate this on. By the way, I recommend Postgres. If you want to practice this on your own machine, I recommend you download and install Postgres. So you can have it, a database running there in your own computer. And I will show you Postgres comes with this graphical user interface called PGAdmin. That's very convenient. That I'll show you right now. This is PGAdmin. And you can click here to expand servers and then the connection. And then you have databases. I'm going to use the default one that's Postgres. So you can expand and then schemas expand and then public expand and then tables. And you can see an abnormal tables. I can right click here and create a new table. And it's a nice graphical user interface. So I don't have to type the commands all by myself. And I can see I put the name of the table here like cars. I'm going to demonstrate what I just did in text by commands. I'm going to do it graphically. And there's an owner out of all the other stuff I don't care right now. Columns is this what matter. Click here to add a row. Let's say make. You can see the data type. This is convenient because you can learn about all the other data types from this drop down. You know, there's date and all this other stuff. So I'm looking for the character varying of their care. If you type their care doesn't find it because here they call it character varying. Okay. Same thing. But don't put the square brackets. That's something else without square brackets. And then the precision is 255. The one you can change the limit, the length. And remember how somebody asked about not know being required. So if you want this column values to be required, you mark it as not know here. See that toggle in market as not know. That means the user has to provide a make always cannot leave it blank. And here it's asking, is this a primary key? And it can market if it's primary key. Obviously it's not. And I can keep going if you want a default value. I don't have an add another row. I'll quickly do whatever I did before. Character varying to 55 plus what else did we have here. You can see it's very convenient. And we don't have to type it by hand. I added color. And now what is it? Character varying 255. And finally the obsolete column obsolete. This is a Boolean. You can see Boolean. And you can market with default zero here like I had before. And what's nice about this environment is if I click the SQL tab, it tells us what you just did graphically. What's going to run in actual SQL command. You see create table, public dot cars. And make and the type character very 255 the same as bear care not know. This is how you mark it as required. Okay. You put not know after the type definition. And then comma, model, character varying, your integer, blah, blah, blah, obsolete, Boolean default zero like we had done manually. Okay. So this is how you can find out. If you don't remember how to create a table, this is convenient. You can click create table and click on the SQL and refresh your memory. And then if you click save, it's going to create the table. What happened here? The photo special type integer. This is probably because I have to put false here instead of zero. Is my guess. Yeah. So put zero in this specific case. So you can see tables here. Somebody has how would you know about the tables? You can either right click, see the properties and see the columns again and their definitions. And this is nice where you can right click. You can make the view all data. You can add here in this, you can either write the query here like we had done before manually, or you can use this interface and add new data here. Obviously, I think I have to use script, create, insert to add a new one. And then you see it how it types for us. So we don't have to type by hand. So we just replace the values here. Yeah. Put a Honda Civic 2008 red, not obsolete. I put false there. Click play. And then I right click this again, view, edit that all rows. I can see it's now here. And this thing is nice because I can duplicate, copy, you know, and do other stuff. And I can even delete, I think, if I click here and click the trash, stuff like that. Anyway, I think we're out of time. I hope you like this. I encourage you to download a postgres. And it's going to come with this program so you can play around creating tables and seeing what the code looks like. Yeah, so let's see. I think I'm going to finish the recording.