SQL syntax and basic queries - SQL Tutorial

- [Instructor] It's important to understand the SQL syntaxes and write some few queries. Now before moving forward, we need to set our stage. We have already created a database that is online store. Let's have some meaningful tables get created. Inside this database, we'll have a table like categories, which will store the type of products. For example, it could be electronics, it could be clothing, it could be household stuff, something like that. Then we create one more table that is for product. It will store the product like the cloths, the electronics items, etc. And all this product is a part of some category. Then we have a customers who's going to buy this product and the order which capture which customer has bought which product. So let's have this meaningful tables it created, which we're going to use throughout this entire course. Now, once we created this table, let's insert some dummy data inside it and then we start fetching the data and playing with it. So before getting late, let's jump to our PG admin four and start creating these tables. Now this is our PG admin four. We are already into our online store database. Inside this, we have tables. Now this time let's start writing the SQL to create all those tables and insert the data. For that, I have selected this online store and click on top of this query tool. Now a query window will get up here. I'm going to write all the queries here. So whenever you want to create the table, the command is create table and you have to give the table name. For example, the first table which we want to create is categories. Now this categories table will have a two column. One is we call it id, and this will be let's say have a serial primary key. Now you must be thinking what is the serial primary key? Now this serial primary key is like autogenerated IDs. So this will be an autogenerated ID. Whenever you insert any new row into this category table, automatically, a sequential number get started. For example, for first row, the ID will become one. For second row, it'll become two, and so on, so forth. So instead of this just id, let me give this name as category_id. Now, I want to have a one more column and let's say column name is name, which is storing the category name. And the data type is varchar. Now, varchar is a variable character. It means that if there 100 characters you try to store as the name, it'll take the space of 100 characters. But if you have a less number of characters in your name, then it will occupy less space. We are restricting that, the maximum width would be 100 characters and that's it. Now click on this execute script button. What it's saying, there is one error. The error is, I forget to put the comma here. Done. So our table got created. This is first table get done. Let's create the next table for the product. To save the time, I have copied the product table query here. In this, we have a product_id, which is again like a serial primary key. We have a product name that is again, varchar. We have product price. I have selected it of type decimal because product price can be of a decimal type, right? I mean, you can have a value which is a point in itself like 20.5, 20.6, something like that. That's why we have the price is decimal description again as a varchar. Tags, varchar. And this time, I said it, right? That a product belongs to a category. Every product is a part of some category, right? So how we references that, we have a category_id column here and we are enforcing the integrity constraint that is a foreign key. We're saying that whatever be the category_id, you will put this category_id should be available in this category table. So I'm saying that I have a foreign key that is a category_id which is referencing the category table and category-id column. So if at any point of a time, you try to insert any row into the product table where the category_id does not exist in the category table, it'll not allow you to insert. That is what the foreign key constraint here. And then we have just a one more column, that is a supplier column. I'll just select this query and say execute. Now one more table got created. Now let's create one more table that is the customer. So this is the query for the customer table. Customer, customer_id. Again, I give it like a serial primary key so that customer-id will automatically generated and like an incremental manner, customer_name, email, phone_number, address, and the city. Let's just run this and this will create our customer table. Now lastly, we have to create our orders table. And this is the query for orders table. Create table orders, and then we give order_id, customer_id, all information about the order like total_quantity, total_amount, order_rating. If you notice here we have added this customer_id and product_id because order cannot get created without the customer_id and the product_id. A customer is buying something, right? That is the order. Let's add the foreign key constraint here as well. How we can do that? We say comma, FOREIGN KEY customer_id is referencing customer table, right? So you can see that in customer table, we have this customer column. Similarly for product, I want that the product_id should be referencing the product table. So I'd say FOREIGN KEY product_id. Let me copy this, paste it here. And this is belonging to nothing but a products table. So I'm saying that whatever be the product_id, you will put it here. That should belongs to the products table. Now let me just run this query so that our final table that is the order table also get created. Done. So we have created the four tables. All the tables which we have created so far is empty. So let's just go and insert some data inside it. How? Let me just write a query. Query would be something like this where I'm seeing that insert into categories table column named name. The value is electronics, clothing, home and kitchen. Let me run this. You can see that three rows got inserted. If you look at the table, table has two columns. One is serial primary key, that is category_id and other one is name. I don't have to worry about this category_id because this automatically get generated, so I have to just insert only the name. How you will do that? You write a command INSERT into table name, exact name, the column name, and values, you give all the possible options. Done. Now, similarly, let's insert quickly for other tables. So this is for the product table. Now, in the product table, I'm saying that my laptop does not have a price. So I just say null for now. Description, tags, category_id is one and then the supplier name. So I just try to insert these five rows inside it and then just say execute. A five rows will get inserted into our product stable. Now quickly insert the data into the customer table. How? Insert into customer, then all the customer column names. And under the values, you are providing every row information. So this is every row information followed by comma so that this second row, then third row like that. So let's just select all this and say execute. Our data got inserted into the customer table as well. Now finally, we have to just insert the data for our orders table. I have a query. Again, query written in the very similar manner, insert into orders, that is a table name. Then you give all the column names. And after the values, you just provide all the default values for those columns. And just say execute. Now you can see that the four rows got inserted. Now let's just quickly see what is in our table. For that, you have to just write select * from tables. So let's start from categories table. So I will say select * from categories. When you run that, you will see the information in the category table. Similarly, let's just quickly see in the rest of the table. In the orders table, in the customer table, and in our products table. So you just write the command, select * from products. You see product information. You do it for the customer. You see customer information. And you do for the orders table. So you see the orders information. Hence, we have loaded the data successfully into our tables. Now you must be thinking that every time, it doesn't make sense to pull out all the data. You are not looking to fetch all the data all the time. You are completely right. So in the next video, we will see how we can filter these records and fetch only the information which we are looking for.