SQL DDL Commands

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?

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.

CREATE DATABASE baransel

Let’s see if the database has been created.

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

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.

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;

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;

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

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.

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.

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.

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.

The ALTER command takes three different parameters:

• ADD
• DROP
• MODIFY

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

ALTER TABLE table_name ADD column_name column_property

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

ALTER TABLE students ADD date datetime

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

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

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

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.

ALTER TABLE students DROP date

Thus, the date column was deleted.

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

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()

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:

DROP DATABASE baransel

Table Deletion

DROP TABLE students

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