Course Content
Intermediate SQL
Intermediate SQL
CREATE and Constraints
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:
CREATE TABLE example ( id INT PRIMARY KEY, some_info VARCHAR(50) );
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:
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), birthdate DATE, salary DECIMAL(10, 2), is_active BOOLEAN );
With this query, we create an empty table that should contain information about users, including:
- An
ID
with an integer data type; - Information about the
name
, with aVARCHAR(50)
data type; - Information about the birth date, with a
DATE
data type. - Information about the salary, with a floating-point number data type;
- Whether the user is active, with a data type that only accepts
true
orfalse
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:
CREATE TABLE users_2 ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, birthdate DATE, salary DECIMAL(10, 2) DEFAULT 50000, is_active BOOLEAN );
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.
Task
Your task is to create a table named library
(use this exact name so that the tests work correctly).
This table should have 4 columns:
id
- integer primary key;title
- varchar, not null;author
- varchar;pages
- int.
At the end of the query, be sure to put a semicolon (;
).
Please use these column names exactly as specified.
Note
On the right, you will see a large amount of code; do not modify it. It is written to ensure that your solution is correctly checked. We will discuss everything written there later in this section.
Thanks for your feedback!
CREATE and Constraints
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:
CREATE TABLE example ( id INT PRIMARY KEY, some_info VARCHAR(50) );
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:
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), birthdate DATE, salary DECIMAL(10, 2), is_active BOOLEAN );
With this query, we create an empty table that should contain information about users, including:
- An
ID
with an integer data type; - Information about the
name
, with aVARCHAR(50)
data type; - Information about the birth date, with a
DATE
data type. - Information about the salary, with a floating-point number data type;
- Whether the user is active, with a data type that only accepts
true
orfalse
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:
CREATE TABLE users_2 ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, birthdate DATE, salary DECIMAL(10, 2) DEFAULT 50000, is_active BOOLEAN );
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.
Task
Your task is to create a table named library
(use this exact name so that the tests work correctly).
This table should have 4 columns:
id
- integer primary key;title
- varchar, not null;author
- varchar;pages
- int.
At the end of the query, be sure to put a semicolon (;
).
Please use these column names exactly as specified.
Note
On the right, you will see a large amount of code; do not modify it. It is written to ensure that your solution is correctly checked. We will discuss everything written there later in this section.
Thanks for your feedback!
CREATE and Constraints
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:
CREATE TABLE example ( id INT PRIMARY KEY, some_info VARCHAR(50) );
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:
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), birthdate DATE, salary DECIMAL(10, 2), is_active BOOLEAN );
With this query, we create an empty table that should contain information about users, including:
- An
ID
with an integer data type; - Information about the
name
, with aVARCHAR(50)
data type; - Information about the birth date, with a
DATE
data type. - Information about the salary, with a floating-point number data type;
- Whether the user is active, with a data type that only accepts
true
orfalse
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:
CREATE TABLE users_2 ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, birthdate DATE, salary DECIMAL(10, 2) DEFAULT 50000, is_active BOOLEAN );
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.
Task
Your task is to create a table named library
(use this exact name so that the tests work correctly).
This table should have 4 columns:
id
- integer primary key;title
- varchar, not null;author
- varchar;pages
- int.
At the end of the query, be sure to put a semicolon (;
).
Please use these column names exactly as specified.
Note
On the right, you will see a large amount of code; do not modify it. It is written to ensure that your solution is correctly checked. We will discuss everything written there later in this section.
Thanks for your feedback!
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:
CREATE TABLE example ( id INT PRIMARY KEY, some_info VARCHAR(50) );
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:
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), birthdate DATE, salary DECIMAL(10, 2), is_active BOOLEAN );
With this query, we create an empty table that should contain information about users, including:
- An
ID
with an integer data type; - Information about the
name
, with aVARCHAR(50)
data type; - Information about the birth date, with a
DATE
data type. - Information about the salary, with a floating-point number data type;
- Whether the user is active, with a data type that only accepts
true
orfalse
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:
CREATE TABLE users_2 ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, birthdate DATE, salary DECIMAL(10, 2) DEFAULT 50000, is_active BOOLEAN );
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.
Task
Your task is to create a table named library
(use this exact name so that the tests work correctly).
This table should have 4 columns:
id
- integer primary key;title
- varchar, not null;author
- varchar;pages
- int.
At the end of the query, be sure to put a semicolon (;
).
Please use these column names exactly as specified.
Note
On the right, you will see a large amount of code; do not modify it. It is written to ensure that your solution is correctly checked. We will discuss everything written there later in this section.