From the course: Complete Guide to SQL for Data Engineering: from Beginner to Advanced

Introduction to databases and tables - SQL Tutorial

From the course: Complete Guide to SQL for Data Engineering: from Beginner to Advanced

Start my 1-month free trial Buy for my team

Introduction to databases and tables

“

- [Instructor] What is database? I know you must have this question in your mind. A database is like a well-organized digital filing cabinet. There, we store information. It's like a place to keep our data in an orderly manner, so that we can easily find, manage, and update it whenever needed. If we talk in more technical terms, a database is a structured collection of a data that is organized and stored in a way that facilitates efficient retrieval, management, and updation of information. Let's take a one real life example to understand the database. Imagine that if we have a one smart shopping application where people are buying their grocery items using this online app. Now, this online app can have a database that is a smart shopping database. In this database we can have tables. Tables is used to organize your data in a relational manner. Here, imagine that you have a different products. For example, you have fruit juice, you have electronic items, you have clothes, you have toys. It'll have multiple variety of items. So a customer can buy any of these product. Now you must be thinking, what about the rows inside this table? Because a table ideally should have rows, right? So imagine that a food juice can be a for multiple type. It could be apple juice, it could be pineapple juice, it could be orange juice. Similarly, you could have a clothes for mens, for womens, for kids. So you have a variety of the clothes available. So all these clothes probably a part of a clothe table. Similarly, all these variety of a fruit juice would be part of this fruit table. And then what about the columns? So every table will have a columns, right? So similarly, all these item have some properties. For example, every fruit juice have some color, some weight, some price, some ingredients list, et cetera. So whatever be the properties of your item is, you will put it as a column in your table. So overall you can understand that, how your online shopping store can have a database representation in terms of tables, rows, and columns. Now let's just go to our pgAdmin 4 and create our first database, and create some tables. Now on our pgAdmin 4, let's first create our database. To create the database, what we will do is we'll right click on this Database options, and Create, Database. Now give the name of your database. I just give a name as OnlineStore, and I say Save. So now you can see that our online store database got created. Now in this online data store, we're going to create the tables. So in this online data store, click on Schemas, and under the Schemas, for creation of the tables, right click on this Tables and say Create, Table. Now we're going to create the table for our online store. Let's say we have a table called FruitJuice. Now in this table of a fruit juice, we have to add couple of columns. Now you can give the column name, say Plus. First column name, let's have id, which will representing the unique id corresponding to every fruit juice we're going to add under this table. I say this should be of a type of an integer. If you want to say it's a primary key, select Primary key. Remember, primary key is a column which uniquely identify every row. For example, if we have a one table where we're going to store all the people information in the US, then the primary key could be our social security number. Because the social security number or the SSN would be unique for all the individuals, right? So similarly, whenever you create a table, generally you have a one primary key. Primary key helps to identify the unique rules inside the table. So this id column is representing the fruit juice ID. That will be a unique one. And for that I said Primary key is true. Now let's add one more column that is the name column. Here we are trying to save the name of fruit juice. Now for this, the datatype could be the varchar. Varchar is a variable character. Now directly, there is no varchar option coming here. It's character varying is equal to varchar. So if you have any exposure to any of the databases, a varchar is here. You can represent it like character varying. You can give the precision, let's say a hundred character length. Let's have a one more column. This time, let's say I have a price column. Again, price, I want it to be of an integer type. So it's an integer, and this cannot be a null. And name also I want to be a not null, means is I want that every fruit juice, which you are trying to store, have at least a name and have a price. So that's why we are saying the column name here is as a not null. And let's say Save. Now this has created a table called FruitJuice. And here you can see a corresponding query for the same. Now on the FruitJuice, right click and you can see Count Rows. Now as soon as you try to do a count rows, on the right side bottom corner, you can see that the total row count is zero. Because we have just created the table, we have not stored any rows inside it. Select this table and click on this All Rows option. Here you can see that a query comes up, which is trying to select from the table and showcase all the rows. Many times you're going to utilize this window a lot. Reason is this is a very popular way of writing the queries and accessing the tables. If you want to add the rows through a UI, what you can do is you can click here, Add Rows, and you can add some rows. Say ID is 1. The name of a fruit juice, let's say I call it like OrangeJuice. And the price for the OrangeJuice is let's say $100. Let's have add a one more row. I call the ID as 2. And this is something like AppleJuice, and the price is, let's say $200, and done. Now click on this Save Data Change, so that the rows which you have inserted get saved. Now if you run again this query, you will see the two rows got printed. That is OrangeJuice and the AppleJuice. So that's how you can create the tables, create the databases, and insert the rows into our PostgreSQL database server. This forms the foundation of data storage in SQL. Let's dive into the basic SQL syntax and how to write queries to fetch data in the next video. See you there.

Contents