Hi, within this lecture, we're going to understand what are tables in a SQL and how we can create them.

So, when we first opened the sqliteonline.com, we see some pre-created records for us. And most of the time they change these records. Maybe you're seeing completely different in here, and it really doesn't matter as long as you have a demo page or demo test table. If it doesn't appear in here, don't worry. We're just going to create our own test table as well, and you will understand how it's created.

As you can see, this is like an excel sheet. The best idea to understand about SQL is to think about an excel sheet or an excel file. So, in here we have columns and we have rows, and we have tables as well. Tables are like excel sheets in an excel file. So, demo is kind of an excel sheet, and in here we can see the demo excel sheet. Of course, it's not an excel sheet. It's a SQL object, it's a SQL table. However, you can think it like that and you will understand it in a much better way.

We have this table, we have this sheet and in the sheet, we have columns and rows, and as you can see these are the columns and right now it has the column values of Id, Name, and Hint. It may be different for you. It doesn't matter because we're going to create our own as well. We have the rows which are exactly the things over here, which are essentially the values that we assigned to these columns. So, these are records, these are rows like we see generally in the excel file and the SQLite database is the excel file itself. So, database is the file, tables are the sheets, so, columns and rows are exactly the columns and rows in an excel file. Okay.

So, if we run this, we see this. And what we are running is this essential SELECT * FROM demo comment. SELECT means selecting and the * represents everything. We are actually selecting everything,  every record in the demo table. We are selecting everything from demo. So, it's pretty self-explanatory explanatory as you can see. So, it brings us these records, and we can see everything in here. If we didn't select anything then we wouldn't see anything in this case. What we are selecting, it will be shown in here. And if we filter some results, then we will see that we only get what we want.

So, let's try and create our own table in here, for example. We already have this demo, but we can create our own table as well and it's not done automatically like from the UI by just clicking over here. We have to do it from the command as well like with codes. So, SELECT * FROM demo is a query and we generally use this when we try to get everything inside of a table. And rather than selecting everything, we can create a table if we want. And that's what we are going to learn in this lecture. And we will see how to filter the results in a table later on.

Now, I'm going to just select this and delete this and I'm going to write this and just explain you what it is. So, CREATE TABLE IF NOT EXISTS, it means that create this table if it's not already created for us. And then you're going to have to give it a name. As you can see, I'm writing all the things in uppercase letters which is the syntax when it comes to SQL so you might get lost during this, and just try to follow along with me with the syntax.

Now I'm going to create a table called test. And it will be the table name for us exactly like demo in this case. And then I'm just going to specify the columns that it will have. So, in this case, this case we have Id, Name, and Hint, for example, in the case of demo. And in this case, it stores three parameters or three columns in here like  Id, Name, and Hint. So, whatever I want to store in here, I would have to give it in the comment. It generally starts with the  Id because most of the time we need an  Id Identification. Why? Because the records can have the same name, can have the same hint, they can have the exact same values in the columns over here. But we need some kind of a factor that can be differentiated.

We need an identification, right, so we can differentiate them. We may want to delete them afterwards or we may want to update them afterwards, so we need an identification number and it can be generated automatically. I believe that's what it's doing in this case as well because as you can see  Id started from 1 and continued like 2, 3, 4, 5, 6. So, it can be generated automatically, which is most of the time the case that we see in web applications and websites. I'm going to show you how it's done.

You open the parentheses over here, okay, a brace, and then you write the column name and the column type that you may want to store in this table. For example,  Id, okay like that, and this  Id will be an integer. Integer means a whole number, not the decimal one or like a whole number like 4, 3, 1, 2. And it's not only going to be an integer but it's also going to be an  Id as well. So, I'm going to say PRIMARY KEY. Primary key means that it's going to be a key, it's going to be an  Id, it's going to be automatically assigned by the system but also it will have a value of integer. Integer is a common data type that we use in programming languages, not only in SQL but also in other programming languages as well.

And that's it.  That's how you create  Id column. Now, after that, you can have as many as you want. Not only  Id but maybe name, hint, age, job, surname, title, whatever you may think of. And they will all have different type of datas. For example, in name, we will have a text. So, it's called VARCHAR in SQL. Maybe you heard the term string in other programming languages, it's called VARCHAR in here. It's kind of the character is coming all together and forming a string, something like that. But if you want to work with texts, then you're going to have to just write VARCHAR. For example, in age, I'm going to write integer, so it's going to be a whole number but it's not going to be a primary key. So, I have just specified age as integer. I didn't write primary key in here.

When I execute this with 'Run', this will create my table. But as you can see, we cannot see any records in here because I haven't done a query. Even if I did, then I wouldn't get any results because we haven't put any records in here, right? We haven't stored any data yet. But we have created our test table, and it's showing up on the left hand side, which is good for us right now. We're going to do the rest in the next lecture.