Previously, we worked for different companies and executed SELECT queries for their needs. However, we need to learn how to create and modify tables.

Let's get straight to it!

Tables are created using the CREATE statement, which has a similar structure to the SELECT statement, except instead of selecting data, it creates data.

Let's look at the syntax:

Note

When you run these examples, you won't get any output because these examples only create a new table. If you run the code again, you will get an error saying that the table already exists. These code snippets are examples, and later in the task, data will be inserted into these newly created tables and displayed on the screen so you can see that everything is working.

Now, let's break down what's written above.

This query will create an EMPTY table with two columns: id and some_info.

Pay attention to the data types being used. The words INT or VARCHAR denote the data type for each column. For example, INT represents integer data, while VARCHAR(50) represents text with a maximum of 50 characters.

We won't delve into all data types now, as there are quite a few. We'll focus on the main data types in this section, and we'll address each of them as we progress in learning!

For example, let's create another table with different data types:

With this query, we create an empty table that should contain information about users, including:

1. An ID with an integer data type;
2. Information about the name, with a VARCHAR(50) data type;
3. Information about the birth date, with a DATE data type.
4. Information about the salary, with a floating-point number data type;
5. Whether the user is active, with a data type that only accepts true or false values.

Constraints

You may have noticed that next to each ID value, we put the words PRIMARY KEY. This is called a constraint and signifies a restriction imposed on this column.

For example, PRIMARY KEY ensures uniqueness and identifies each row in the table. There can only be one such column in the table.

There are also other constraints, such as:

• NOT NULL: Ensures that the column will not contain NULL values;
• UNIQUE: Ensures the uniqueness of all values in the column or combination of columns;
• DEFAULT: Sets a default value for the column if no value is specified when inserting data for that column.

These are not all the constraints that are used, but for now, we will need exactly these.

Let's consider an example where we modify the previous table:

Now the name column cannot have empty or null values, and the default value for the salary column is 50000.

In this way, you can use constraints to limit the columns of a table during creation.

Let's move on to a small practical task where you will create a table using the CREATE statement.