Hello and welcome, today we'll talk about SQL SQL, structured querying language, database. I will use this website, www.programiz.com slash SQL slash online dash compiler. I'll be doing the examples there. In this environment, you can type in this text area here and click run SQL, and you get the result in the bottom. The left hand side will provide you details about the table and their data types, and the right hand side here will provide the data we are working with, the tables. Okay, so what is SQL or SQL, structured query language? So when you go on the browser today to your favorite social media website or app, that is called the front end, what people see. And then you make a comment on somebody else's picture or photo, and then when you come back later to the app, it's still there, the comment. How does it get stored? Well, there's actually something behind the scenes called the back end, and in the back end there's a server that is serving requests for resources, in this case the comments, the photos, and so on. Now those resources, the data has to be stored somewhere, and that somewhere is called the database. So somewhere there's a database, and the back end server interfaces, it's kind of the intermediate between the database and the front end, what people see. Now this lesson is about the database, and in particular the kind of database we talk about is SQL, structured query language that's used to interface with this database that's also called a relational database. So you might see the name RDBMS, relational database management systems, that's pretty much using SQL. Now for SQL, as we usually pronounce, other people say SQL, doesn't matter, and there are many implementations. You can think of that as, you know, everybody kind of drives a different kind of car, even though every one of them is a car, they're made by different companies, or make, right? You got Honda, Toyota, Ford, and Hyundai, and so on. Now that's the same for SQL, you're going to see out there different kinds or implementations of SQL, although it's the same thing, you're going to see MySQL, you're going to see Microsoft SQL Server, Oracle SQL, Postgres SQL, and so on and so on. So every company came along and kind of built their own, in the same way we have different cars from different makes. Anyway, so if you learn SQL, there should be no problem for you to use any of those implementations. So let's learn the SQL language. In this environment, we're going to type what we call queries, which is basically we're asking the database to do something. Now how does the data get stored? Well, if you ever heard of spreadsheets like Microsoft, Excel, Google Sheets, that's pretty much like that. We got a table, we got columns, and we got rows. Now in the context of SQL here, for example, the right hand side, we use tables to keep track of a specific kind of information. For example, if we want to keep track of your customers, it would create a customer stable like this, and then you would define columns, and each column is storing a specific attribute of that customer. For example, we got the first name, last name, age, country, and it could add more and more. Now this first customer ID is a unique identifier column that we typically have, and it's called a primary key or PK. I'm going to talk more about that later. Anyway, so we can also store order kind of information like the orders, and for that we create a separate table, orders, and then we store each row is like a separate order from a customer, and we got the column specifying the attribute of that order. In that case, what's the item that was sold? What's the amount? Like how much was charged? What customer was bought that? Now in the case of customer data here, you see that we don't store the customer name and age and so on. We actually just give it an ID, which is the customer ID, so that we can look it up in the separate table of customers. So you don't want to get all the data together. You want to separate them into different resources. Like you've got customers, you've got orders, you've got shipings like this third one. You might have other kinds of tables. Each of the tables are supposed to be with a single source or resource, because we need to establish a single source of truth for the data. If we had the customer data in orders, we would duplicate the data, and that would, in the end, in the long run, be amount to a huge mess. So what we actually do instead is if we need to reference data from another column, we just combine tables with what's called a join. You're going to see that later. Anyway, that's the overview. Now let's learn how to first ask for information. And here we got a table of customers. So the most basic keyword that we can use is select. Now you're going to see me type the words capitalized, and you don't have to do that, but that's just the convention in the SQL language when we write the queries. If you have a special word or a reserved word like select, I always capitalize. And then you're going to follow that with a space, and then the name of the table, sorry, the name of the columns you want to select. For example, we want to go over the customers. If we want every single thing, every single column, you just say star. Star means every column, all. And then space from, that's another keyword, the name of the table. So you make sure to write that exactly as it's here with the C, the first C capitalized. And then you finish off with a semicolon. And click Run SQL, you should see the same table on the right-hand side appear here. Now keep in mind, this is like a convenient web environment that already shows the data on the right-hand side, but it's not always going to be the case when you're working on an actual implementation. You're not going to see the data right away, so you've got to write queries to see it. And also another matter is, the data set here is very small. So we can see all of them in only one screen. But that's not going to be the case in practice, right? We've got to have probably millions of rows, and that's not practical to look at them individually. So keep that in mind as we're doing the examples. Anyway, select all columns, star from the table customers, and you've got the columns here, and each row represents a different customer. So you can see the customer ID number one has first name John, last name Doh, age 31 from country USA. Now you also keep in mind that there could be another person with the same first name, right? We got in line this customer number four, John. And the person has a last name, but it could also be the case they shared the same last name. So we could have John Doh somewhere and another John Doh somewhere else. So how do you differentiate between those two? That's why we got this column ID here that's usually always present in database tables. That's called a primary key. It's a unique identifier for the record. There can be no other row with the same ID here, with the same primary key, okay? So that's why we always add that. And it's also a unique constraint if you try to later going to see if you try to add the same customer ID is not going to work because it doesn't allow you, okay? So now if you need to select only specific columns, say I don't care about the customer ID, only the first names for some reason, I can go and replace the star with the name of the column. Now you got to write it exactly like here, first underscore name, no spaces, okay? There's not first space name. Anyway, we got all the first names for our customers. If you want to add more columns, you just add a comma and separate the column names like last name, that should give me the first and last name. And you can make the last name first if you want, cut that, base it here, add a comma, and then make sure to remove that other one. Click run sequel. Now you got the last name on the left-hand side. Usually just for a matter of readability, I always like to add a space after the comma, okay? It works the same way. All right. Now, let's find out, let's say, there are people who are always trying to answer questions about the business, usually called data engineers, for example, or I don't know, you're working on business intelligence, and you want to find out information about your business, so you would go into the SQL database and do some queries to answer a few questions. The first question I want to ask is, where are customers from? That's one question you would have to answer. How would we do that? Well, first you understand that there's a table for customers, that's where the customer data is. Okay, so we got from customers. Now, what column provides information about the origin of the person? Where are they from? What is the country column? So I can just select that column. So I select country from customers. That way you can know where my customers are from. But in order to compile a list, so I can present to maybe the CEO of the company, I would have to go through each row and understand and write down the name of the country. You notice that there are people from the same country, and because the data set is small, I can do it by eye, but in practice, there will be millions of records or rows. So I cannot just do it by eye here, so it's really hard. So how can we make this better? Well, first, how about we eliminate all these duplicates? I don't care about the same people of the same country. Can't we just consolidate all the countries that are repeated into one? Yes. What you do is before the column name country, you add this thing, keyword like that, with a space. That way you're going to consolidate them into just one USA, one UK, one UA. Now it's a lot better to know where your customers are from. So you can save and copy and paste this output, maybe it's going to be into a spreadsheet, and you can present that to your manager or CEO, and they will understand where the customers are from. You know for sure, they're at most 195 or so countries, and that's easy to count, right? Instead of millions of rows. Okay, now let me just show you a quick way of counting countries. Let's say I don't care about exactly they're from, but I want to know how many countries we are serving, how many countries in the world we are doing business. We can just count the number rows. Obviously we can do it by hand, it's one, two, three, but it would probably be harder if there was a hundred and something. So to make that easy, we can just add here before the column name counts parentheses and then close the parentheses after the column name. You can do that, and it should tell you how many countries I think the distinct is should be inside because it's given five, yeah. So if you do just count country without the distinct inside, it will count pretty much every single row. So you have to put the distinct inside so it eliminates the duplicates first before counting. All right, so that will give you three, which is correct. Okay, let's see. For those who just came in, this is the website I'm using to do the examples. It's in the Zoom chat. Okay, now let's learn how to refine our search because right now we just keep selecting all the records with star, select star from customers. And obviously, in the real world scenario, you don't want to be doing this. Why don't we want to select star from table name? Because there could be millions of rows. And to select all those millions of rows would probably take a lot of time and it would probably crash your database client. So we usually don't do that. We just select a smaller sample. We limit that. So after we say the column name from column name, we put a limit. Let's say I only want to see two, and I click run, I want to see the two rows. Okay, now there's also something called the offset that you can skip the rows. If you need to skip those two for some reason, you can use offset two. And that should give you, I think, according to this default order, it's going to give David and John. There you go. So these two keywords are very important when you do the back end of any application to do pagination, a very simple pagination. Like, usually you don't want to show all the records in one page. That's impossible, millions of them. You just paginate to maybe 100, like you're doing email, right? Say you're checking your email, that's usually 50 to 100 per page. So you got to click next or next page. That's how they do it with SQL in the very basic form. Use these two. Okay. Limit is the number per page. Like 100 offset is basically if it's zero, it's the first page. And then if you want to go to the next page, add 100. And then the next page, add 100, 200, and then 300, and so on. Okay, and off of that, let's go back to our small data set. Now let's learn how to filter. Like I said, this is going to give a lot of stuff. I don't care much. How about I want to find out, let's say a customer comes in to the store and they ask, you know, they want to order something and the cashier asks, okay, what's your customer ID so I can check in the system? And they don't remember. So they have to tell you the name. Okay, my first name is John. Okay, so how do we find all the records, first name John? Well, you add where after the column, the table name. So select start from customers where column name, first name, is equal to a John like this. The space is just for readability, okay? I always like to add a space before and after the equal sign. Now, this is the equal sign for comparison. If you already know how to program in another language, usually the single equal sign is attribution or assignment. So be careful with that. Here's comparison, which is the double equals in other languages. And there you go. I found all the rows whose first name is John. Now, obviously you've got it never down. So we ask the customer, okay, what's your last name? So we would add another thing here. How can you combine two at the same time? This and that, it's just add and here. So and last name is dough. Like that. Now you can narrow down it even more. Obviously there's a small data set. I'm sure there'll be more than one John dough out there. So you probably want to narrow it down even further if you still haven't found it. But the best way of finding something usually is where primary key equals the number. Like somebody's account number, order number, and so on and so on. So really the best way would be if the customer know his number. They could give you like, they have a card and they give you his membership card and you look out by the number, you would say customer ID. Let's say, let's say for baddie. Baddie is like customer ID five. So we look that and we find baddie in the record. Okay, so that's usually how we do it. Something I didn't talk about, let me go back to the first name, John. I want to find my first name, John. Did you notice, if I put lowercase j here, it matters. It can't find anything. Result, set is empty. So be careful with that. If you don't want to care about the, if it's lower uppercase, you should use something like the lower function. For example, you can call lower here parentheses on a column name and that would turn the column name to lowercase, all the letters lowercase, so you can compare against this lowercase and that would find it. Okay, so this is like a normalization. So you want to make sure everything is lowercase so you can compare. I could do the same upper case. I think there's upper. Let's try and you put uppercase. Yeah, same thing. Okay, I think that's all for retrieval. Let's go to insertion. Okay, we learned how to find out information, but how do we add more new info? Well, we're going to use insert into those are the key keywords. And then followed by the table name, let's insert a new customer, take customers and then you're going to say values and then parentheses, semicolon and then we're going to type each value within the parentheses. Okay, now what's the order we got to follow? It's the order of the definition of the table. So on the left hand side, you're going to see customers here. This is the definition for each column. So each column has a name and a type, a data type. So this is the order, customer ID first, first name, last name, age and country. Now what is the type? The type is pretty much what you can store. For the customer ID, that's an integer, one, two, three and so on. That therefore we, the type here that's uses int, integer. First name is a sequence of characters, right? And in this case, it's a vercare, which stands for character varying string. String is a sequence of characters and the parentheses 100 means the limit of characters, the length is at most 100, which means you could have one character, two characters or three and so on all the way to 100 characters. Okay. And so on. They are all kind of the similar that we learn. Age is int, country vercare 100. Okay. Let's follow. Let's try out the deliberately put five here for the customer. Even though I already know there's a customer ID five. First name would be, let's say Anna. And then last name long. You notice I'm separating every column value with a comma and the space is just for readability. And for integers, I don't need quotes, but for strings or character varying, I need the single quote. And then age, let's do 33 country. Let's do Canada. And I'll click run sequel and we're going to get an error. Why? Let's read error unique constraint failed customers dot customer ID. So this is telling you that the table customers dot its column customer underscore ID. The constraint called uniqueness is failed. Why? Because the customer ID is meant to be something unique to the row. There can be no other row with the customer ID five. Therefore it doesn't allow me to do that. So what usually happens is our tables in practice, when you're working on a real system, they usually have auto incrementing primary keys, which means the next time you insert a new record, it automatically increments from the last ID. In this case would be five plus one was just six. So here we have to do it manually, but usually you don't have to do it. And there you go. I inserted this new row. Okay, this is the implicit way of inserting the explicit way you got to tell it the column names after the table name before value. So you got to say here explicitly customer underscore ID underscore comma first name last name age and country. So this is the explicit right because this first one has to match the six year and this one the second in the parentheses after values. And then the third and age and country, etc. All in corresponding respectively. I can also change this order if you for some reason I don't know we want to change that it's okay like I want to insert last name first. Right last name first before the first name I changed the order swap. In that case I'll have to swap these two. Let me break a line here so it's easier for you to see. It doesn't matter if there's a line break. Okay, so I'm going to swap these two. Like that. So our last name comes first. Now if I try to run it's going to error because the primary key so I got to add seven. And there you go we got another Anna there. Notice that the other columns are not unique. So you could have two and along from Canada the same age. I think that's possible. Right. Anyway, this is usually the explicit way that we use. And that's it for insertion pretty much always like this. I always sometimes forget the syntax you got to look it up. So you can use the search engine how to insert sequel and it will tell you the syntax again, or ask your AI chat bot. Okay, but it's always like this. All right, so how about we talk about deletion. Important one but very dangerous. So we're going to delete from the two keywords. Table name is customers. Let's say we don't like duplicate Anna, we want to delete. What do we do the lead from table name and then usually we add a filter. Okay, if you do it like this, it's very dangerous. This will delete every single row. Never do this. You always want to add where. And then usually we delete by the primary key, which we know for sure it's only one and one record only. Because if you do by other stuff, for example, where first name is Anna. This is not a good thing because we could have many people called Anna and all of those would be deleted. Let me try showing you. If I do that, all Anna's got deleted, which is not what we want. Okay. So never do that. You want to do by primary key. So you'll, for example, I want to delete David Robinson. I do customer ID is three. And that way I only know that only one row will be deleted. I am very safe in doing that. Gone. Okay, so always delete by the primary key because you're no for sure only at most one row will be deleted. And that's pretty much it for delete. No secret. Just got to remember the syntax, delete from table name where primary key equals whatever. Okay, let's learn how to update. Let's let me refresh this so you can get the data back. But this doesn't happen in the real world, right? You don't get the data back just like that. Anyway, we've got the reset there. So now I'm going to update. Let's say Robert Luna went moved to the Canada. How would we do it? We got to change the column country to Canada for only Robert Luna. So let's do update from that's the keyword. Sorry, not from update. Table name customers. You see how sometimes you forget you confuse the syntax. That's why you always want to reference do a search quick search if you forget. Okay, then you're going to do what set. That's another keyword. The column name country equals Canada. Now in this context equals means attribution or assignment setting in your value. Now, if you do it like this, guess what's going to happen. Just like that. Everybody moved to Canada. Oh, no. So never do this. Always add aware. Let me reset. Always add aware. And typically we do by primary key, right? Costumer ID. In this case, Robert Luna is customer ID too. If you do by the first name and last name, there's no guarantee there's only one and only one row that could be multiple. Many people with the same name. So you're going to accidentally change somebody else's country. So you don't want to do that. So always do by the primary key. The ID. Now Robert's moved to Canada, Robert Luna. Okay. And that's updating. If you need to add more stuff, think about a comma here and add other columns. And then that's it. If you have questions, always let me know in the chat. Okay. Now I'm going to show you how. Let's see. Actually, before I do the joins, which is combining tables, let me just show you how can order your results. If we do select star from customers run sequel, I think the default ordering is by ID ascending, right? You can see one, two, three, four. If for some reason you want to invert or reverse that by ID descending, you can do order by like this, order space by after the table name. And the column customer ID. And if you want descending, you have to say space D E S C. If you want the default, which is implicit ASC is like this. Anyway, if you do D E S C, you're going to see it's ordered by customer ID descending. You can also order by other columns. Maybe you want the alphabetical for the first name and put ascending for A to Z. And you can see from A to Z. If you want, Z to A is descending. You can see Robert goes all the way to B. Okay, so the order by. And yes, you can put multiple order bys here with a comma. All right, so yeah, let's learn how to combine tables now. You know, for some reason, I want to know about orders, right? Let's say select star. Now let's use another table orders. I want to find out who is ordering stuff. So this is a separate table. I changed the table name here. Okay, here are my orders. What if I want to know where customer ID is for, which is John, right? John has been ordered some stuff. We generate a report. This is all of John's order, but we go to our whoever requested the report, the supervisor, and he says, okay, I don't have the customer information. What is what is the customer name? I need to call the customer and you give me this information, but you provide no name where they're from their age. So I got to know that because I'll be talking to them. How do we do that? Well, we want to combine tables. It's called a join. So let me show you how you can join orders with customers. So you're going to go here, select star from orders, going to do inner join. There's different kind of joins, but the one we want to do is inner join. The table name is customers because you want to add information for the customer and on. Now this on is where we combine typically what we call the foreign key with the primary key. So you say you see that the orders table has the customer ID column, which points to the primary key of customers, right? The customer ID column here. So in the context of the table that point that's pointing in this case orders, it's called a foreign key because it's a foreign key. It's a column on another table. Okay, and then we're going to do on customers dot customer ID. Now, why did I say customer dots because it's ambiguous? There's two tables with the same column name. I don't know which is which. So if I want to be explicit about saying I want the customer ID column of the customer stable, I have to put a dot like this. Okay, I want to match this with the customer ID of customers, which is the primary key. So I say equals orders dot customer ID like that. Let me put that in a new line. So it's easier for you to see. Okay, now let's see. Oh, we got a problem. It's the same thing I told you it's ambiguous column name. I don't know which you said customer ID for which table you're talking about. In this case, it doesn't really matter if it's order of customers. Okay, so we can choose either. Let's do orders dot. And there you go. Now I'm going to annotate here to kind of show you what it means. I when I look at it, I look at two things. I am visualizing this table here, which is orders. And I'm visualizing the right hand side being the customer stable. We combine them together matching the foreign key in orders with the primary key in customers. Okay, that's what's happening here. That's why we have on. We're matching the foreign key with the primary key. Sorry, the opposite way, right? I put orders is the foreign key and customers cross my ideas the primary. Okay, so now you can go to your boss and just give this and here. Okay, now I got the customer info. Nice. By the way, if you see this repeated, you don't like it. You got to specify each column individually here. It's kind of tedious. So you would have to say order ID, comma item, comma amount, comma orders dot customer ID to not be ambiguous. And then first name. Oops. Last name. Think age and country, right? Kind of tedious. But that's the way it is. Now we eliminate that duplicate. Okay, now that's one kind called the inner joint in the inner joint, the record has to match. So there must be a row in one table and there must be another row and another table that matches. That's called the inner. SQL Venn diagram is what I always reference to find out what kind of joint or combination of the table I want to do. And let me show you this image and share with you. The one we did is this one in the middle. That's the inner joint. You can see the code here. If you were to do that one, I imagine we got table A and table B. If you want to match records that appear in both of these tables, which is the intersection of the Venn diagram, you would do inner joint like this. Select the columns from table A and inner joint table B on the keys, right? Usually the primary and the foreign keys. Now this A here is just an alias. You don't have to have it. You can rename the table if you want. If you were to do another kind of SQL query joint, you would choose the other ones. There are many of them. For example, let's say I want to select all the customers and their orders, right? But there could be the case that some customers don't have any order. If you use the inner joint, it would match them with orders, but then the customers who have no orders would be eliminated. So that's the case where I would do a left joint like this because I got customers which is table A. And those customers, they might have orders, which might be in table B, and that's the intersection, right? But some customers do not have orders, so they would just lie here on the side of A, not intersecting. So that's why the red is also selecting them. So that's the case where I use left joint, where table A is customers, table B is orders. Okay. Now, enough of that. Let's go back here. And yeah, I think I've showed you pretty much most of the basic concepts. I showed you count before. I can show you again. I don't think it's that useful. You already learned. So you can use SQL. Basically, the most basic function is to start data, right? You're going to have your application. You're going to perform these queries to either retrieve the data to show in the front end or to start some data accepting from the user input. Another thing that people do is analyze the data that you already have. Data engineering, people are asking questions, your boss, your CEO of the company, they want to know what customers are doing so they can learn what to do next, what next ideas to implement. So they'll be asking a lot of questions related to the business, and you answer them by doing SQL queries, which might not be as simple these ones, but might be a little bit more complex. As you aggregate the data, you can add summation functions and so on in a more advanced use case. Great. So far we have tables that already exist. How to create our own? Well, that's easy. Create table. Let's say you want to creep track of cars. Let's make a table called cars. You can say create table cars. My convention is always lowercase. And I'm used like that. And if I have multiple words, a separate underscore. That's just my convention. Other, if you work, whatever you're working with in a company, they have their own convention, according to their SQL implementation. Okay, then you add parentheses, and you define the columns and their data types. People usually like to break into multiple lines like this. Close the parentheses, semicolon. And then each line is for one column. And we usually add what's called indentation, which is the space at least two on the left hand side every time we open parentheses. So it's easy to read. Yeah, a computer doesn't care. Okay, let's create a table. Okay, you've got to think about, okay, cars. What kind of information do you want to store? Maybe manufacturer, right? Make. Okay, got make column. What's the type? Well, we got Honda, Hyundai, there's just a sequence of characters, right? So we can save their care or character varying. And how many, what's the limit for characters? If you don't know, just put 255, that's like a 255 characters, just a standard. And if you need more, you can increase later. If you need less, I don't know, for some reason, you want to save some characters, you can decrease it. Comma and then the other columns. Let's do another column. What else do you want to keep track of in a car? When was the car built? Maybe the year. So year column. That's going to be an integer, right? You can use the integer data type. And if you have more columns, you put a comma and type more. If you don't have, just put it like that. Let's do it like this. And run. And we got a new column here, cars. Empty, nothing yet, right? By the way, if you want to ignore something in SQL, you can type the dash dash in the beginning of the line. And that becomes what's called a comment. So I want to do a comment here to just keep it for reference, but it's not going to be run. Okay, let's do select start from cars. Whoops, lower case. There's nothing, right? Okay, let's insert. How about we dash dash dash that I'm going to insert. I'm going to insert into cars. Like this, right? Values. Sometimes I forget values and that messes me up. Okay, what are the columns to be explicit here? Make and year. Let's do on the 1987. All right. Let's do it. There you go. Now you have one. If you do the select, right? You're not always going to see on the side. So that's why you got to do select like that. Honda 1987, you can add more. Let me do dash dash so it doesn't run that. I don't know for 77 and you got another row. Okay, so that's how you do it. Now, how about if we add another column? How can we do that? Easy. Okay, so you alter table cars, add column. Let's say you want to keep track of, I don't know, you have model, we have color. I have so many things, right? Which one do you want to do? Let's add the color. Actually, sorry, model. Model is just a very sequence of characters. Let's do 255. And now we got model there. And if you want to change it, you would update, right? Let's say I want to change for the Honda. I know there's only two records, so I'm safe to do update. Cars, set, model, let's do civic. Where, make is Honda. But in the practice, when there's so many records, you don't have that. You have to do by the primary key, which we didn't add, right? We should have added a unique ID. Anyway, you should have changed civic there. And you can do the same for Ford. Oh, sorry. Where, make is Ford. I don't know, focus about that. There you go. And set the second one. And only because we all know for sure there's only two, right? Only one row. But usually you don't want to do that. And that's it. Create a table and search. Okay, now I want to show you how, a very cool thing. If you want to run a SQL in your own computer, have a SQL server, a Honda recommend is Postgres. You can download from this website. And it should come with a client, a graphical user interface called PG admin. And that one is very nice. I'll show you how it, what it looks like. Looks like this. So basically you can create a new server to connect to Postgres. And by default, it has the database Postgres and the user Postgres like this. But it could create your own by right click, create. User and you can create a new database. In any case, if I use the default, you can expand like this, scheme us. And in the database, we have multiple tables and already created cars here, but I'll do another one. So you can right click here, create table, and it's all graphical. And you can name the table. Let me call it cars too, because they already have cars and can give the owner, the user, I'll keep Postgres and I'll kind of find the columns. If I click the columns tab here and you can click to add a row. Let's say, let's do the same thing we did manually, right? We did make, right? And you can click here and see all the possible data types. That's why it's so nice to use PG admin. So there's so many of them. And you're not going to find their care because here they call it character varying. Okay. Like that. And then you can put 255 characters like we did. And then you can add another column. We did here, right? Here is an integer. So I think if you look for integer, you'll find it. And finally, we have the model. And if you choose here, that's going to be character varying, not with the square brackets. Okay. Don't make that mistake. And 255 length. And before you click submit, what you can go to other settings, but what is really nice about PG admin is if you click the sequel tab at the end, it tells you what a sequel query it's going to run. So this graphical user interface is just a front for actual sequel queries that you would run manually. So if you ever need to refresh your memory about doing anything in sequel, you can use PG admin as a try to create something or do something and tell you how to do it here. So create table, cars to and just like we did before. And this public is just a schema. So from postgres, you don't have to have it. It's by default public. And then alter table, change the owner of the table. So it's very nice. So you can save that and it's going to run that query in the background and you have the table right here. And you can expand to see the columns constraints and other stuff. These are the columns. If you need to add another column, it's easy. Right click here on the table name and click properties and you can go to columns and you can click to plus. Let's add the color here color. And it's going to be character varying to 55 and I click sequel and you see that's what it's doing. Alter table. If it exists, add a column and then the definition for the color character varying to 55, which is the same as fair care by the way. So very nice. Click save and it will run. And you should see if you refresh, right click. You should refresh and you see the new column color here. If you want to see the data, you can right click view added data or rows. And it should give you this nice graphical user interface along with the query that was used. So select star from cars to so you can even add records here. I think if I were to right click scripts, insert script, it will write for you that whole thing. So you don't have to remember inserting to the column. Sorry, the table name, all the columns and all the values. So we just replace the question marks. Let's say we got on the 1987 civic and then let's say read, click play or a five in the keyboard and you should insert that and right click here again. The table name all rows and you will see the table, the row is here. So you can even I think copy it and duplicate and do all kinds of stuff here. Very nice. Anyway, if you want to like a free free right writing of queries, you can leverage the what's called the query tool, which is this part right now. I cannot type anything. So I got to go here tools query tool. And it will give me one of where I can write so I can write whatever right select star from cars just like I was doing in. The browser. And it'll tell me okay nice. Can I copy that maybe duplicate I don't know maybe that's a change it I can change the value here. So if I change civic to for some reason change the year okay. And I can do all that. Yeah, what else for people who like command line. There's also the tool here. By the way, some of these tools that not get activated unless you are click the database here. So if you're clicked outside the database it will show great out. So make sure you want to expand. And I think you have to click one of these databases and it should appear because it needs to connect to the database to order to run these. Anyway, the physical tool is basically command line. And it tells okay I'm connected to the postgres database and I'm waiting for your command so I can type select star from cars. Oops, and it will run that and give me some outputs. You know through the command line. Probably less pretty than query tool for some people. If you need help here is backslash question mark. For the P SQL commands and backslash H for SQL commands. It tells you everything. And nice if you want to list the tables is backslash D. And it shows me the tables that I have I have so many now and the sequences which is used for the primary key increment. Yeah, I can describe the table by backslash D space cars. And that shows me the columns like that. All right, so I find it I use this all the time, not necessarily in PG admin but in a virtual machine on the cloud I use P SQL a lot when I connect to via SSH. Yeah, so I it's really powerful tool, but I also love PG admin also use that a lot, especially in development. So I really recommend you download postgres. So installing your computer it comes with PG admin so you can play around and learn SQL make your own tables and such and build your own applications. Okay, and with that I finished this lecture.