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 BY ClauseOrder of StatementsChallenge: Total Ride Time for Each Subway LineAverage Student's Grade ChallengeSearching for the Top Math Students ChallengeHAVING ClauseSeveral Aggregate Functions in the QueryMore Complex Filtering
2. Nested Subqueries
Nested QueriesChallenge: Non-tech Department EmployeesUsing Inner Subqueries in the FROM SectionChallenge: Employees With More Than Average SalaryUNION ClauseChallenge: Combining Table Results
3. Joining Tables
JOIN 2 TablesChallenge: Get Category By Product PriceChallenge: Get Minimum Price By CategoryChallenge: Get Product Amount By CategoryLEFT, RIGHT and INNER JOINsChallenge: Table JoinChallenge: FULL 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.

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


              
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.

Let's break down the SQL query above.

This query creates an empty table with two columns: id and some_info.

Notice the data types used for each column:

  • INT is for integer data;
  • VARCHAR(50) is for text, allowing up to 50 characters.

We won't cover all data types now, as there are many. We'll focus on the main ones in this section and explore others as we continue 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

In SQL, constraints are rules applied to columns to ensure data integrity. For example, when we use PRIMARY KEY next to ID, it means this column must have unique values and cannot be empty. Each table can have only one PRIMARY KEY.

Here are some common constraints:

  • NOT NULL: This ensures the column cannot have empty values.
  • UNIQUE: This ensures all values in the column are different.
  • DEFAULT: This sets a default value for the column if no value is provided.

These are just a few examples. Let's see how we can modify a table to include these constraints:


              
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 must always have a value, as it cannot be empty or null. Also, if no salary is specified, it will default to 50000.

Using constraints like these helps ensure that the data in your table is accurate and follows the rules you set.

Task
test

Swipe to show code editor

Your task is to create a table named library.

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 learn everything written there later in this section.

Brief Instructions

  • Use a CREATE query to create a new table named library.
  • The table should have four columns: id, title, author, and pages.
  • For the first column, specify INT PRIMARY KEY.
  • For the second column, specify VARCHAR(50) NOT NULL.
  • For the third column, specify VARCHAR(50).
  • For the fourth column, specify INT.

Solution

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.

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


              
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.

Let's break down the SQL query above.

This query creates an empty table with two columns: id and some_info.

Notice the data types used for each column:

  • INT is for integer data;
  • VARCHAR(50) is for text, allowing up to 50 characters.

We won't cover all data types now, as there are many. We'll focus on the main ones in this section and explore others as we continue 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

In SQL, constraints are rules applied to columns to ensure data integrity. For example, when we use PRIMARY KEY next to ID, it means this column must have unique values and cannot be empty. Each table can have only one PRIMARY KEY.

Here are some common constraints:

  • NOT NULL: This ensures the column cannot have empty values.
  • UNIQUE: This ensures all values in the column are different.
  • DEFAULT: This sets a default value for the column if no value is provided.

These are just a few examples. Let's see how we can modify a table to include these constraints:


              
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 must always have a value, as it cannot be empty or null. Also, if no salary is specified, it will default to 50000.

Using constraints like these helps ensure that the data in your table is accurate and follows the rules you set.

Task
test

Swipe to show code editor

Your task is to create a table named library.

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 learn everything written there later in this section.

Brief Instructions

  • Use a CREATE query to create a new table named library.
  • The table should have four columns: id, title, author, and pages.
  • For the first column, specify INT PRIMARY KEY.
  • For the second column, specify VARCHAR(50) NOT NULL.
  • For the third column, specify VARCHAR(50).
  • For the fourth column, specify INT.

Solution

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