Okay, today we will talk about SQL or SQL which stands for Structured Query Language. And it's basically database language. So far we have built this application and one of the pages that you see here is the page where we can comment a very common function among many social media websites. Right now we're using a fake backend server that somebody else wrote, however, we want to be able to build our own because this whole course is about full stack, not only the front end but the backend. So right now we are kind of venturing into the backend development. Now what happens usually with backend is we got the annotate here. So we got to the backend, we got a database somewhere db, oops, and then that database is going to store all this data that we see. Now the front end FE needs to access the database somehow, so there's going to be a middleman and that middleman is going to be the backend server B. So front end called backend, backend, hey, I got a request for a resource, I got to look at the database, so they're going to go into the database, get the data and redirect that back to front end. That's basically like that, but right now we're not doing backend until tomorrow, this specific part, so today focus on learning the database. And then later we do the backend, it's going to be very smooth when we learn database. Anyway, that's the overview. Now in the next session I'll be using Postgres and PG Admin, so you got to make sure to install it beforehand. But for now I'm just going to talk about structured query language. So this is the website I'm going to use. Now SQL or structured query language is a way through which you can interface with a database like the language, hey, I want some data, hey, I want to insert some data, hey, I want to delete, hey, I want to update, create new things to store data. So the most popular one is SQL today, of course there's other kinds of solutions that they usually call no SQL like MongoDB. But for purposes of this course we just work with SQL and there are many kinds of SQL implementations. You can think of it like cars, you know, Honda makes cars, Ford makes cars, many companies make their own kind of car, but they're all kind of the same, right? So in SQL we can think of SQL as the car and there are many implementations or companies that build the car. So for example you got MySQL, a really popular one. We got Postgres, the one I use in this class. We got SQL server from Microsoft, we got Oracle SQL and on and on and on, so many kind of implementations. But you need not worry because they pretty much follow the same standard SQL language. Each one have their gimmicks and specific stuff but for the most part if you learn SQL in general you can do any one of them. In any case let's get started here. So how does data get stored? How do they get stored in tables? As you think of Excel, spreadsheet, Google Sheets, it's basically that. So we got columns and we got rows. Now if you notice on the right hand side here we have this table of customers and it's used to store information for customers. So we got columns and each column is like a specific piece of information about the customer and we got the rows and each row represents one specific unique customer. As you can see we got five columns, so customer ID. This column here is to uniquely identify the record. It's very common that you're going to see in SQL the ID column and that one is there because we want to be able to distinguish between say we have two people with the same name. That's totally possible in today's world, so vast. And how do we distinguish between John Doe and another John Doe? That's why we have to have the ID column here. Now this ID column is usually called the primary key, PK, so you're going to see that a lot. And then we got other columns, first name, we got last name and age and then country. It's very self-explanatory what each of them, what information they hold, right? For example, if I look at the third column, this is for David Robinson, he's 22 from the U.K. So very simple. So we got also here the orders table to keep track of all, supposedly this is supposed to be like an e-commerce website or something, this example. And keep track of orders and you can see we've got the primary key which is order ID and we got the item name, keyboard, mouse, monitor, we got the amount supposedly charged by that and then the customer who purchased that. You can see the customer is referenced by their ID, not specifically by name or any other attribute of the person. And that's intentional because we want to keep data separate in their own place to ensure there's a single source of truth. And we want to point to that place via what this ID here, for example, if somebody ordered a mouse, I'm just going to keep track of customer ID number four and then I can look up in the customer's table, okay, find number four and that's John Reinhardt, 25 from the U.K. So he was the one who bought that. And we don't want to be including information for customer in the orders table and instead what we do later on as we learn is we combine tables together to visualize the data. Okay, it's called the join. Now, this column here that you see that points to another table's record is called a foreign key or FK. So you might hear that a lot, okay? So remember, primary key is the one that uniquely identifies the rows whereas the foreign key is a column that points to another table's row. And finally, there's other table here about shipping and you know what that is, something to do, shipping tracking of whether it's been shipped or not. So status and other stuff. We'll focus more on customers and a little bit of orders today. Now, let's finally, so we can write the code here in this input tab and you can click run SQL and you will see the result in the bottom here. So the very basic SQL query, that's what we call a query, we want to consult the database, hey, I want to ask you about something. So a very simple one is asked about just to show all the data for a column. Let's say I want to know all the data for the customer's column. So first I got to do select and I got to tell what columns I want to select. If you want all of them, it's a star. Now, you notice I put select in uppercase and there's a reason for that. It's because it's a naming convention. Whenever we have keywords that are reserved for SQL, I will be using uppercase. You don't have to do it, but that's what people usually do. In any case, select all the columns, star. Star usually means everything in general in programming languages, including SQL. And then space from, that's another reserved keyword, and then the table name. What's the table name? It's customers. Now, it's important that usually we want to follow the same C capitalized customers like it is. Okay, it's important that every character there. So semicolon to finish it. Like we saw before, when we learned CSS, we learned JavaScript. Usually put a semicolon at the end of statements. Same thing with SQL. And if you click run SQL, you should see at the bottom, basically that whole customer stable is available here for us to see. And yeah, so if you wanted to select only first name column, you could just say instead of star, first underscore name, run SQL, and there you go, have it. So we got all the first names for every person. And then if you want also the last name, you can put a comma here separating them like this. And people usually put a space just for styling, readability, it's not necessary. Now you got first name and last name, so you can go on and select any column you want. And if you want all of them, you can go back to star. Now this data set's very small, okay? We only had five rows for customers. So typically what you're going to see is a lot of data, like millions of rows. So if you run like this in a production environment, millions of rows, it's going to be really slow, you probably might crash something. So usually we want to limit the query to just a few samples. So if you want to do that, you can add limits. Like if I want to only see the first, only three of them, I can do limit three there. And it only gives me a sample size three. Okay, so now another thing that's interesting, let me remove the limit here. Let's say I want to know where my customers are from. Like usually when there are people who are data engineers and they're often looking for, to answering questions that help with business intelligence. And for example, so I suppose we're one in those rows and we want to know, hey, I need to know where my customers are from so I can better target my advertisement or something like that. So you would go, okay, I got to tell customers table, I can do just star here. I want to find out where the front, one way, okay, I can run star from customers. This is website we've been using, by the way, I'm pasting the Zoom chat. So I can go here one by one and look up where the customer is from, right? Okay, we've got USA, right down USA. We've got UK, right down UK, UA and so on. This data set is small, again, if it's usually going to be millions of rows, we don't have time and it's just too tedious to go through a million rows. So what we do instead, okay, let's focus on just country, right? That's all I care about, that's the column I want to select. That's the website of being using, by the way, it's in the Zoom chat. So this is a little better, I have less information to worry about, but I still got duplicates and there might be millions of rows. I know for sure in the world, there's at most 194 or five countries, right? So that's, I have to kind of remove the duplicates, how would I do that? Well, one way is adding distinct before the column name country here. That way to consolidate everyone of them into only one of them. So this is a way better to identify all our customers' countries. So we have answered the question, hey, your customers are from USA, UK, UAE, therefore we can target advertisement to those countries instead of wasting money in other ones. So that's what the data engineer would answer to the question that was posed. Okay, nice. Now, that's enough of retrieving information. How about adding some filters? Maybe I want to find out some information based on a certain column value. Maybe I want to know, maybe for some reason I want to know, okay, who are my customers? His last name is Do. Okay, let's try that out. Select all the columns star from customers. Now the keyword here is where after the from table name. So where, okay, last name. Now it's going to equal sign a single quotes and D, capital D, O, E, okay? So that way I'm going to select all the records whose last name is Do. I don't know, maybe some silly question to ask, right? But if you ever need it, it's there. Maybe some customer came in, hey, they're in the system, but they don't remember their account ID. So you got to look up by the name. Usually you do that when you go to the doctor or dentist. They ask, what's your name? And they look it up, right? So that's the filter they would use internally in the program there. Okay, great. So what if I want to, oh, one thing I didn't tell him, right? I put a capital D here, so what happens if I put lowercase? So it doesn't find anything. So you got to be careful, okay? The casing matters. Of course, there's a way you can normalize this if you were to do a lower function like that, that's one way, and make this lowercase. Everything is going to be lowercase, so that could work. Okay. What if I also, for some reason, want to narrow down that even more? I just want to find the row whose first name is John. Obviously, visually, I can see it. It's a small data set, right? But in practice, there'll be millions of rows. I cannot do it by eye. So what I would do is I would go here and add another and condition. So okay, I want the person whose last name is Do, and the first name, that's the column name, is equal to, let's say, John. That way, I narrow down to only that person. But it's probably very likely that if you had a real data set, that you would find multiple rows because I'm sure there are people, there is more than one person named John Do in this very vast world, right? So we've got to be careful of our queries as we narrow them down. We've got to be aware of the data set and everything. Okay. I hope that's fine so far. And so if we're okay, we can move on to inserting records. Yeah, that's just retrieving. We ask questions. Now how do we add records? Let's go. And here's insert into the table name, customers, for example. Let's add a new person to the customers. And then we've got to say values. Now we're doing the implicit way right now, then I'll show you the explicit. And then parentheses, semicolon, then you write each value comma separated. Now how do I know what columns there are there? Well, you can look up in the table description what columns there are. Obviously, here in this website, it tells us right away if you look at the right hand side or the left hand side. But in the actual system, you'd have to right click somewhere, hey, describe the column or something, some command like that. So as you can see in the left hand side, I have the definition for customers table. And the columns are customer ID, first name, last name, age, and country. But you notice there's something in square brackets in blue color if you can see the color. And that's the type of that specific column. So every column has a data type. So the customer ID, it's a nint, which stands for integer number, okay, one, two, three, and so on. No point, no fraction. And then we got first name, which is a vercare, which means character varying string, which is a sequence of characters. And the parentheses on 100 means, okay, you can write at most 100 characters. So you could have one character string to characters all the way to 100. Same for last name, and then age is also integer and country is also vercare 100. So there are many other kinds of data types. There's dates, date with time, timestamp with time zone. What's called, there's a boolean, which is true or false, and so on and so on. Okay, let's insert customer values. So we got to follow the same order here as the column from the top to the bottom. So first is the customer ID. We can look at the table and see that six, comma, the next one. I don't need to have a space here, but it's nice for readability. Last name, let's add Anna, and then vercare is usually have the quotes, right, single quotes. Last name, I put long, L-O-N-G, and then age, 33, country, Canada, in quotes. If I do that, it's going to insert a new record if you notice the right-hand side. This is very nice because we can see in real time the tables. Obviously, in the real, actual thing, we wouldn't see it because there's way millions of records. So you'd have to do select from customers again, where, whatever, to find that Anna is actually there. Maybe you can just select from, select star from customers where customer ID is six. And then we find Anna. Anyway, great, so pretty much like that. Now, if you don't like this order for some reason, you want to swap things around, it can be more explicit. And here, before values, you can add the definition for each column, the names separated by a comma inside the parentheses. For example, let's follow the order, customer ID. And then for some reason, I don't want to write the first name first. I want to do last name. I don't know why, but you would do that, and then let first name afterward. And then age and country. So if you do this, notice last name and first name is swapped, the order. So I got to take this Anna here and move to after long like that. And if I insert there, it's going to complain. And let's read the error. So it's saying error, unique constraint failed, customers dot customer ID. So it's saying, okay, the customers table dot the it's column customer ID, there's a unique constraint that failed. So basically, when we have primary keys, which is the ID of the row, that's usually a unique, which means there can be no more than one row with the same ID. Because the point is we need something to uniquely identify this, right, to be get rid of all those. The ambiguity that could be two or three or four people, the same name. How do we know they're different? That's the ID. That's why we always add a constraint of uniqueness to that specific column. And you're going to see this a lot. If you have like, be explicit about a table name, you have to put the dot and the left-hand side means what table the right-hand side belongs to. Right, this is a column of the customers table. So we're going to see this a lot when we have like ambiguity. Maybe you're doing a join, combining two tables. And for some reason, one table has the same column name as the other table. So it's not going to know, okay, which table you're talking about, this or that. So that's why I've got to say table name dot. Anyway, let's fix that. We can add six instead of six for the ID, say seven, because that's the next one, right? And by the way, usually when we insert new records, we don't have to look up what's the next ID. I have to write it like this. Usually that's taken care of with what's called auto increment. And basically, you could omit the ID, and then it would automatically fetch and find out what's the next integer in the sequence. Postgres, in particular case a Postgres, it does this with what's called sequences. And we're going to see it later. Anyway, I can add another n on there. Notice the other columns are repeated, so they don't have a unique constraint. I suppose that's totally fine, right, to have the same person name, same age, same country, two different people, you know? Okay. And that's pretty much how you insert records. Nothing magical about it cannot say anything else. Just follow the syntax. You know, if you don't remember, just look it up, right? Like I said in previous lectures, you have the search engine. Okay, how to insert records in SQL? Or you can ask ChatGPT, GitHub, Copilot, whatever AI bought, how to do this in SQL, and you'll find out the syntax, you know? Obviously, I did this so many times, I remember now, but there are times that I forget the specific keywords, so I have to look it up. So that's totally acceptable. Okay, let's move on to deletion. I got, if for some reason I add more records here, eight, nine. I have so many duplicates, I don't want that. How do delete records? Well, the keywords delete from, and then table name, customers. But if you do it just like this, be very careful. This will delete every single row in the database table. So you don't want to do that ever, ever, ever. Don't do this without a filter aware. So typically, we want to filter by the primary key or ID. But for the sake of example, I'm going to show you how you can filter with another. For example, I want to delete everybody whose last name is Long and then first name is Anna. So to do that, I would do your where, last name, Long and first name, Anna. And keep in mind this, every single row matches this condition, whether deleted. This is not the ideal. This is not what you want to do. It's just an example. You see all of them wiped out, got wiped out. So what's the best practice? Well, you want to always delete one thing and one thing only. And you got to do that by the customer ID. In this case, the primary key, for example, I want to delete David here. So customer ID three. So that way, I know for sure I'm only deleting one row. Why? Why do I know that? Because customer ID is a column that's a primary key. It has a unique constraint. So there's only one and only a one row with that ID if it exists, right? So that way, I'm very safe to delete. There you go. So that's deletion. Pretty much delete from table name where primary key equals some number. OK. And yeah. For the sake of having data here, let's see. Let's say I want to update now records instead of. So we learned how to retrieve information. Now, how do we update that? Maybe somebody moved to a different country and you need to update them in the system. Maybe Robert move Robert Luna customer ID to move to Canada. How would we do that? Well, update table name customers and then you're going to set. That's the keyword. Then a column name country equals Canada like this. But if you do it like this, it's going to be every single row is going to move to Canada. I'll show you just for the sake of example, but don't do it ever without a filter where. You see everybody moved to Canada. No good, no good. Now I'm going to refresh my page to reset this, but keep in mind a real world. We cannot reset it just like this, right? OK, let me add a filter now. I just want to apply that to Robert Luna. Obviously, I can go by first name and last name, but that's not a very good filter because there could be more than one person with that name. So usually we always want to go by the primary key, which is customer ID. In this case is Robert Luna is customer ID two. So if I do that, only one and only one record will be updated. So Robert Luna has moved to Canada. OK, and that's up update. Again, not much mystery. It's always like that. Just copy this and keep in mind, use it whenever you need it. Now we can move on to combining tables with a join. But before I do that, let me try something. I'm trying to find out what a specific person ordered, and maybe I want to target some ads and encourage them to buy more stuff. Let's say customer four. Yeah. OK, John Reinhardt from the UK. What has been up to? Let's see. We first understand his customers for so I can go to select, start from orders. That's the different table, right? I want to find out what they're been ordering where customer ID. That's the foreign key equals four. So this customer has been buying keyboard and mouse. OK, great. So we can add maybe you can put some ads for them to buy more computer stuff. That's what the point of this query would be, right, if you're doing some data stuff. Anyway, that's great, but I had to look up the customer ID here, and isn't there a way to put side by side? Maybe you want to report this to somebody else, and you want to add the customer information here side by side, and right now you would have to do it separately. So how do we join these tables together? So let's go here. So what do you do here is called the inner join, but I want to do it for everybody first so you can understand and I put the filter later. OK, so I'm going to remove that filter. So select start from orders. So I don't combine orders to customers inner join. And what's the table customers on so I got to match a condition. Now there are different kinds of joins. I'm going to join on the matching customer ID. So customer ID. Now this is ambiguous, right? We got orders has customer ID, and customer stable has customer ID. So I got to say which one do you want, OK? And then basically you can do like dot customers dot, and then equals orders dot customer ID like that, OK? Just because the columns have the same name. And then you can see here, this is the output. Now let me see if we can put this to the side. The way I visualize this is basically if you notice this part here, I'm annotating. That comes from orders. And the other part, let me put in blue, is coming from customers. That's how I visualize this. But basically we match one column row with another column row based on their customer ID here. You notice this part here. They're matching. So four and four equal matched display, four and four equals match display, and so on is someone, OK? So that's what's happening there. So that way I can have, oh, OK, keyboard was bought by John, Reinhardt from the UK, mouse was bought by John. You can have like a report, and they know exactly the customer information without them having to manually do it themselves and go to the database and ask, who is this customer for? Who is this customer one? You know, it's very tedious. OK, so here are all my drawings. I might have noticed there's two repeated things. So if you want to eliminate these two, you've got to manually type in the star here, the column names. It's kind of tedious. Let's do it just for the sake of example. Or if you want our ID, you type it there, item, amounts, customer ID, but it's going to have a problem. Watch out, run SQL, read this ambiguous column name, customer ID, because both tables have the same column name. So we've got to say either orders or customers. In this case, it doesn't matter, right? So I can say orders dot, and it should run fine. And I can keep on adding more, right? I can go, OK, what's the next thing from the customers, right? First name, last name. It's implicitly from customer stable, right? Aid from customer stable, entry from customer stable. And there you go. Now we only have one customer ID. That's kind of tedious to type all of them like that. OK. So what else can we do? Ask some, yeah, we can do the filter, right? If you want to just start us from John like we did before, where customer ID is four, that should narrow down. And there you go. There's still the customer dot or orders dot ambiguity. And you get a report of everything this customer is bought. OK, that's great. Now let's ask another question. Maybe you're trying to find out how many keyboards were sold. How would we do that? Well, the very naive way is go to the orders table, select all the columns, right? And do it like this. Do it by eye. OK, one keyboard, two keyboards, and then a million other ones, other rows to read by eye. And that's not what you want to do. We want to make it better with a filter where item equals keyboard. That's a little bit better, however, there could be millions of them as well. And how do we count things? Can the computer just count the rows it tells the number? Yes. What we do is type count here, count parentheses, star parentheses. And that way you get all the keyboard sales. Now somebody might be nitpicking, hey, what's count star? Count what? Well, we can add a navies here to change this name. After the column here can add as keyboard sales. That way we change the column name here in the output. Somebody could be even pickier. I want a space between the words. Well, put some quotes, put a space, and then finish off with the quotes. The quotes are needed because otherwise you'd think it's two separate things with a space. That way you have a space there for the nitpickers. Great. So you answered the question. Now you can know if you want to keep selling the product or not. The sales are low. We don't want to have that anymore for sale. But if it's selling a lot, you might want to order some backfilling more. People demand is high. Okay, great. So that's the pretty much basic things for SQL. And I didn't tell you much about the joins, but there are different kinds of joins. We learned the inner join. And I'd like to reference this diagram if you ever need trouble understanding which join to do. Let me see if I can paste in a Zoom chat. Basically, there are different kinds of joins. And this is a VAN diagram. The one we did is the one in the middle here with a blue color. This is the one we did. Now, what does A and B mean? A is just one table, and B is another table. For example, A is orders, B is customers. So what we did is combine them, matching the customer ID. Therefore, we took whatever's the intersection, right? Both records are in both tables when we match. And but not the only in A and not only in B. If you do want those cases, you would go, for example, this one here. This would take, okay, there's a record that could be matching, could be in A and B, that is a customer, right? A customer has some orders, right? But also it matches customers who order nothing, right? If you think about from a point of view of A being customers and B being orders, there could be the case that some customers didn't order anything. Therefore, it wouldn't match any row, but you still want to show their information. So you still want to fill this side here that's only in customers. And then the intersection is basically the customers who order something because there's an order record in B matching the customer in A. So this is what I would use. And here's the code, select table name, the column names. From the table A, left join, let's call it left join. We did an inner join, right? This one, left join, table B on the matching keys, right? You gotta match the foreign key in one table with the primary key in the other. And so on, you got other kinds of joins. I always reference this when I'm trying to solve a problem, figure out how to answer a question in a sequel. I never really remember everything, so I reference this a lot when you think about the problem. Anyway, that's about it for that. Now let's move back and learn how to create our own tables. So basically, so far we've been working with tables that already exist, but how do we store information that we design ourselves? Let's say we want to keep traffic cars. What are the attributes of a car you have to think about? And those will be the columns, right? A car has a color that will column. A car has a year of manufacture, right? That's another column. A car has a make company who built the car. It's another column, and so on. You just think about what you need to keep track of, and you keep. Once you got that down, you can create the table by saying create table like this. And then the name, let's say cars, our lower case. And then parentheses, and usually we break a line and put the definition between them like this. And usually I write one column definition for row. It's just a styling for readability. You don't have to follow. Obviously you could write everything in one single line. Okay, the car color. So we're going to say, okay, color. And what's the type of color, right? It could be, it's basically a sequence of characters, right? Red, green, whatever. So sequence of characters is a character varying or a vericare. How many characters at most should we allow? But in parentheses. I don't know. If you don't know, just put 255. It's just standard. If you need to change it later, you can do it. Increase the length. And then you put a comma there to add another column. Let's say make, right? Basically the same thing as a sequence of characters like Honda, Toyota. So vericare 255. I don't know if the company name will ever exceed 255 characters. If I ever need more, I can change it. Let's do a year. This one can be just an integer and no dangly comma. I think it's not permitted. When I do this, another table will be created here and it's empty. You see the left-hand side. Color, column, make, and year. Let me keep this. So if you want to comment something out and see if we can put dash, dash in the beginning of the line, basically this code would be ignored by the interpreter. Anyway, let's insert some records. Basically inserting two cars, values, follow the order. Notice that we didn't add a primary key, right? We should have added that as well. Well, we can do that in the next session when we work with PG Admin. Color, let's do red, make, Toyota, Camry. Oops, I didn't put the model. That would be another column, right? I didn't put it there. I'll show you how to add a column later. A year, let's do 1988 and click that. It should insert and you can keep going. Maybe another one, blue, and 84, and so on and so on. Now I'm going to comment this out so it's not executed in New York. How do we add another column? Let's say, oh, I need to keep track of the model too. Well, you can see author table, cars, add column, and then you define a column here, just like we did in the create table here. Well, it's a model, there, care, I guess, right? And that's it, semicolon. When you do that, a new column appears, model. And then comment this out. You can use update, right? Remember that statement to update so we can set the Toyota to something and the Honda to something. Okay, so update, cars, set, I want to set the model to Camry, but wear what? Wear, usually we do primary keys since I don't have one. I assume there's only two records right now, so I'm safe to do this. Wear make is Toyota, and I could add more if I want to narrow it down. But I know for sure there's only one there, right now. And you got there, Camry, and the other one, I don't know, Honda Civic, wear make is Honda. And there you go, you can update. Yeah, so that's how you create a table and how you can alter a table. If you don't remember these, again, look it up. You can always look at documentation for Postgres or whatever SQL you're using. It's usually always the same for every implementation. Alter table and then, name, do something, maybe add a constraint, like uniqueness, add a column, even remove a column, right? I think it's maybe called drop, I don't remember. I gotta look it up, let's see. Remove column, SQL. There you go, alter table, drop, not remove, let's drop. And that's it. Yeah, this website, by the way, is really good. It's very good reference, have all the commands, stuff. I can drop a table, too, if you need to get rid of table. Drop table, table name. Okay. Yeah, I think that's it for this session. The next one will be about PG Admin, where we build table for the app we've been building. So let's take a break.