SQL

Data Definition Language Commands in SQL

updated on

This article describes Data Definition Language Commands in SQL.

Database Schema

In order to start working with a database, first of all we need to decide the structure and content of the database. In other words, we need to find out the number of tables in the database, their structure, and relationship among these tables. Furthermore, for each table we need to define the various columns with their data types as well as certain constraints. For instance, whether a column or field can accept duplicate values or not. All these things constitute the schema of the database.

Data Definition Language

Basically, Structured Query Language (SQL) comprises of commands to create, manipulate, and access data from the database. These commands fall in various categories. For the purpose of defining database schema, we use DDL (Data Definition Language) commands.

The DDL commands include Create, Drop, Alter, Rename, and Truncate. While, the Create command is used to create a database, table, view, tablespace, index, sequence, synonym or a storage group. The drop command can delete a database, table, view, and so on. Also, the alter command in SQL can be used to change a table, database, view, index, and so on. In order to change the name of a table, we can use the rename command. There is also a truncate command that we can use to delete all records from a table.

Examples of Data Definition Language Commands in SQL

The following examples show the usage of Create Table command.

create table Student(enrolment_no varchar(11) primary key, student_name varchar(50), address varchar(200), contact_no varchar(10), gender varchar(1), emailed varchar(100), course_id integer);

create table SemesterShiftSection(enrolment_no varchar(11) primary key, sem_no integer, shift_name varchar(1), section_no integer);

create table Subject(course_id integer primary key, subject_id varchar(8), subject_name varchar(100), maximum_marks integer);

create table Result(enrolment_no varchar(11) primary key, subject_id varchar(8), marks1 integer, marks2 integer, marks3 integer, finalmarks integer)

Table Structure

Creating Tables in SQL
Creating Tables in SQL

Similarly, the examples of Alter Table commands are given below.

The following command adds a column in the Student table.

alter table Student add column(country varchar(30))

As can be seen, we can also change the name of a column.

alter table Student CHANGE column emailed emailid varchar(100)

Similarly, we can add a Unique constraint to an existing column.

alter table Student add CONSTRAINT uk_1 UNIQUE (emailid);

Structure of the Table Student After Executing above Commands

Performing Data Definition Language Commands in SQL
Performing Data Definition Language Commands in SQL

Further Reading

SQL Practice Exercise

You may also like...
SQL

SQL Queries – Basic to Advanced
SQL

SQL Practice Exercise
SQL

Learning Simple Queries in SQL