Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.CREATE and Constraints | DDL and DML in SQL
Intermediate SQL
course content

Course Content

Intermediate SQL

Intermediate SQL

1. Grouping
GROUP BYOrder of StatementsTotal Ride Time for Each Subway Line ChallengeAverage Student's Grade ChallengeSearching for the Top Math Students ChallengeHAVINGSeveral Aggregate Functions in the QueryMore Complex Filtering
2. Nested Subqueries
Nested QueriesNon-tech Department Employees ChallengeUsing Inner Subqueries in the FROM SectionEmployees With More Than Average Salary ChallengeUNION ClauseCombining Table Results
3. Joining Tables
JOIN 2 TablesGet Category By Product PriceGet Minimum Price By CategoryGet Product Amount By CategoryLEFT, RIGHT and INNER JOINsTable Join PracticeFULL JOIN Usage
4. DDL and DML in SQL
CREATE and ConstraintsALTER and INSERT OperationsDROP and TRUNCATEUPDATE and DELETE

book
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:


              
1234

            
CREATE TABLE example (
    id INT PRIMARY KEY,
    some_info VARCHAR(50)
);
copy

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:


              
1234567

            
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    birthdate DATE,
    salary DECIMAL(10, 2),
    is_active BOOLEAN
);
copy

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:


              
1234567

            
CREATE TABLE users_2 (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    birthdate DATE,
    salary DECIMAL(10, 2) DEFAULT 50000,
    is_active BOOLEAN
);
copy

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
test

Swipe to show code editor

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.

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 4. Chapter 1
toggle bottom row

book
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:


              
1234

            
CREATE TABLE example (
    id INT PRIMARY KEY,
    some_info VARCHAR(50)
);
copy

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:


              
1234567

            
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    birthdate DATE,
    salary DECIMAL(10, 2),
    is_active BOOLEAN
);
copy

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:


              
1234567

            
CREATE TABLE users_2 (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    birthdate DATE,
    salary DECIMAL(10, 2) DEFAULT 50000,
    is_active BOOLEAN
);
copy

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
test

Swipe to show code editor

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.

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 4. Chapter 1
Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
We're sorry to hear that something went wrong. What happened?
some-alt