SQL DDL Commands
Tags: sql, database, beginners, programming
Published: 2022-03-20 12:45:59

---
canonical_url: https://baransel.dev/post/sql-ddl-commands/
---

> [Sign up to my newsletter!](https://baransel.dev/newsletter/).

## What is SQL DDL(Data Definition Language)?

DDL deals with the type of data in the database. In other words, we do all of these with DDL, what will be the relations between the tables in the database and the types of data in the table. DDL basically consists of three commands.

> `CREATE`: Used to create objects (Table/Database etc.)
> 
> 
> `ALTER`: It is used to modify objects.
> 
> 
> `DROP`: Used to delete objects

## `CREATE` COMMAND

It is used to create database and database objects. What are database objects?

| Table            | A collection of rows with related columns                                                                                          |
|------------------|------------------------------------------------------------------------------------------------------------------------------------|
| Constraints      | It imposes restrictions on values contained in columns                                                                             |
| Index            | Provides fast access to data                                                                                                       |
| View             | Provides access to information from one or more tables or views                                                                    |
| Stored Procedure | A set of SQL commands                                                                                                              |
| Trigger          | A set of SQL commands that are automatically executed when the user makes a change on the information (A kind of stored procedure) |

In this article, we will create databases and tables that are database objects.

### Create Database

> `CREATE DATABASE database_name`

And this usage is the same for all database management systems.

```sql
CREATE DATABASE baransel
```

Let's see if the database has been created.

<img alt="Database created" src="https://baransel.dev/post/sql-ddl-commands/database.png" style="height:75%;width:75%;"/>

As you can see, we have created the **baransel** database.

### Create Table

Before creating the table, we need to specify on which database you will create the table. For this;

The general usage is as follows.

> `use database_name`

```sql
use baransel
```

We have specified on which database you will create it, now let's create our table.

The general usage for this is as follows.

> `CREATE TABLE table_name(column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size))`

It is used in the form.

Then let's create the **students** table as an example.

```sql
CREATE TABLE students(
    student_id int,
    student_name varchar(15),
    student_surname varchar(20),
    student_average_grade float);
```

Let's explain what we did, first we created a table named `students`, then first we created a column named `student_id` with data type `integer` in this database. Then we added our other `varchar()` data type columns, here `varchar(15)` states that it can take **up to 15 characters**, you can get any data length you want.

This is how to create a table in a basic simple way. But we need to add some properties to this table, for example, `student_id` must be different for each student so that I can distinguish them.

### Primary Key Expression

A `primary key` is used to make a field, i.e. a column, a unique value in a table. What does this mean if there is a student with `student_id` 15 in the table, you cannot add a second student with `student_id` of 15.

Let's use it now;

```sql
CREATE TABLE students(
    student_id int primary key,
    student_name varchar(15),
    student_surname varchar(20),
    student_average_grade float);
```

By typing a `primary key` next to the variable type, we have informed that this column is a `primary key`, that is, a unique value.

> Note:
> 
> 
> Did you get an error like this
> 
> 
> `#1050 - Table 'students' already exists`
> 
> 
> Don't worry, it gave an error because we created a table named `students` before. Because table names are singular, think of them as primary. There cannot be more than one table with the same name in a database. So let's delete our first table and run it again.


Now you have to ask me a question here; If the `student_id` number is not entered, then how will we distinguish it? That is, if it fills in other fields and leaves the `student_id` column blank. In this case, you cannot distinguish, for this we must make the `student_id` part mandatory. Most of the time, when registering on a site or a place, you have received a warning that the username or email address cannot be blank. Here we will warn the user that the `student_id` cannot be empty.Let's do it now;

```sql
CREATE TABLE students(
    student_id int not null primary key,
    student_name varchar(15),
    student_surname varchar(20),
    student_average_grade float);
```

This is how we got it done. The `student_id` column is now both **primary** and **not null**, so it cannot be left blank. So is there anything missing? No, it's not over yet, we got the `student_id` part, but we forgot to check something. You have come across many times today, just because you have to enter a 10-character phone number, or you will enter an exam score, but you must enter a value between `0 - 100` for the exam score. Yes, we should check that here. For this;

### Check Statement

```sql
CREATE TABLE students(
    student_id int not null primary key check (student_id <100),
    student_name varchar(15),
    student_surname varchar(20),
    student_average_grade float);
```

Yes, we have stated here that our `student_id` number can be **max** 100.

So, should it give an error only when it is greater than 100? ID number cannot be negative, then we have to check more than one situation.

```sql
CREATE TABLE students(
    student_id int not null primary key check (student_id <100 and student_id >0),
    student_name varchar(15),
    student_surname varchar(20),
    student_average_grade float);
```

We stated above that `student_id` is primary, cannot be empty and will be less than 100 and greater than 0.

What if multiple columns have separate conditions? For example, if both columns need to be primary ? If 3 columns need to be not null? Should we write one by one?

### Constraint Statement

Constraint expression is the expression we will use when we control more than one column. Let's show it with an example.

```sql
CREATE TABLE students(
    student_id int,
    student_name varchar(15),
    student_surname varchar(20),
    student_average_grade float,
    class_no int,
    constraint p_key primary key(student_id,class_no));
```

Here, we specified that the `class_no` and `student_id` columns are the `primary key` at the same time. My request from you is to use the word `constraint` together with the `check` statement in the same way.

Finally, let's show another expression.

### Identity Expression

In some cases, not ourselves, but the id number, etc. We will want to add the columns automatically. We will use the `identity` statement for this. If you wish, you can specify that adding to the table will be automatically added to the table with the `IDENTITY(1,1)` statement. This usage is used in the sense of starting from 1 and continuing at intervals.

```sql
CREATE TABLE students(
    student_id int not null IDENTITY(1,1),
    student_name varchar(15),
    student_surname varchar(20),
    student_average_grade float,
    class_no int,
    constraint p_key primary key(student_id,class_no));
```

## ALTER COMMAND

This command, which is change and update, is used to update the tables. Let's see our table first.

<img alt="Table Structure" src="https://baransel.dev/post/sql-ddl-commands/table-structure.png" style="height:75%;width:75%;"/>

The `ALTER` command takes three different parameters:
* ADD
* DROP
* MODIFY

### `ADD`: This parameter allows us to add columns to the table.

```sql
ALTER TABLE table_name ADD column_name column_property
```

Now let's add a new column, for example the date column;

```sql
ALTER TABLE students ADD date datetime
```

Now let's look at the final version of our table;

<img alt="Date Table Structure" src="https://baransel.dev/post/sql-ddl-commands/date-table-structure.png" style="height:75%;width:75%;"/>

As you can see, we have added the `date` column.

### `DROP`: With this parameter, it allows us to delete a column from the table.

```sql
ALTER TABLE table_name DROP COLUMN column_name
```

Now let's delete a column in our table, I'm deleting the `date` column we just added.

```sql
ALTER TABLE students DROP date
```

<img alt="Table Structure" src="https://baransel.dev/post/sql-ddl-commands/table-structure.png" style="height:75%;width:75%;"/>

Thus, the `date` column was deleted.

### `MODIFY`: Allows you to update the column specified in the table with this parameter.

```sql
ALTER TABLE table_name MODIFY COLUMN column_name column_property
```

Now, let's make it `text` while the data type of the `student_name` column on the table is `varchar()`

```sql
ALTER TABLE students MODIFY COLUMN student_name text
```

## `DROP` COMMAND

This command is for deleting the database and tables we have created.

### Database Deletion

Common usage for deleting an existing database:

```sql
DROP DATABASE baransel
```

### Table Deletion

```sql
DROP TABLE students
```

In this way, we learned `database` and `table` deletion operations.

> [Sign up to my newsletter!](https://baransel.dev/newsletter/).