SQL Data Definition Language (DDL)

SQL Data Definition Language defines the set of relations in a database. SQLs DDL specifies the schema of each relation in the database and also the relevance between the relations in a database. So in this section, we will discuss SQL data definition language in detail.

Content: SQL Data Definition Language

1. Data Definition Language in SQL
2. SQL Data Types
3. DDL Commands
4. SQL Schema Definition
5. Key Takeaways

Data Definition Language (DDL) in SQL

SQL is an acronym for Structured Query Language. Though it is termed as a ‘query language’, it also defines the schema of the database, modifies the database and can also be used to specify special security constraints on the data in the database.

SQLs data definition language (DDL) defines information about each relation in a database which is listed below in detail:

• SQLs DDL specifies the schema of each relation i.e. the logical design of each relation which states the name of that relation, attributes it carries and also specifies the domain of those attributes.
• SQL DDL specifies the integrity constraint which makes sure that any changes made to the database don’t affect the consistency of data.
• SQL DDL also maintains the set of indices for each relation which let you retrieve the records from the database quickly.
• SQL DDL maintains the information about the security of data in the database and it also keeps the information regarding the authorization for each relation in the database.
• SQL DDL also describes the storage structure of each relation on the hard disk.

SQL Data Types

Every language has variables to operate on, these variables are called attributes. Every attribute has a data type (domain) associated with it that specifies the set of values that attribute can hold. Now, in relational DBMS, each relation is a collection of attributes that defines the nature of that relation. SQL DDL specifies the domain for each attribute in a relation.

SQL DDL has a variety of built-in data types which we will discuss in detail below:

1. Numeric: The numeric data type specifies a number. Now, a numeric data type can express an exact value or an approximate value.

a. Exact Numeric value

• int: The int type contains the whole number that can either be positive or negative. It ranges from -2,147,483,648 to 2,147,483,647. This data type doesn’t hold decimal or fractional digits. This data type has a storage size of 4 bytes.
• smallint: It is a subset of the integer. It ranges from -32,768 to 32,767. It requires only 2 bytes of storage size.
• bigint: It is just the opposite of smallint. It ranges from -9,223,372,036,854,775,808 to 9,223,372,036,854,755,807. It requires 8 bytes of storage size.
• numeric(p, d): This data type holds a fixed-point number which also has a user-defined precision i.e. the fractional component. This data type holds the number which has p digits in total and d number of digits are to the right of the decimal point.
• decimal: Like numeric data type, this type also has a fractional component. The difference is that this data type has a larger precision as compared to the numeric data type. The storage size for this datatype is based on the given precision.

b. Approximate Numeric Value

1. real: This data type holds a floating-point number with single precision. Here, the precision is between 1 to 21. It has 4 bytes of storage size.
2. double precision: It can hold a floating-point number that has precision twice the real data type. Here, the precision is between 22 to 53. Its storage size is 8 bytes.
3. float: This data type allows the user to specify the precision and then the computer decides whether to go for the single precision or double precision.

2. String

This kind of data type stores alphanumeric data.

1. Char(n): This data type holds the character string of fixed length, specified by the user. If the string length is less than the specified length, then the remaining spaces are filled with blanks which leads to wastage of storage. Well, if you don’t specify the length SQLs DDL assumes the length of one character. The character data type has a storage space of 254 characters.
2. varchar(n): This data type holds the character string of variable length. It will just store the exact number of characters that the user specifies thereby saving the storage space. The varchar has a maximum length of 32,672 characters.

3. Date and Time

This data type holds the data in the context of date and time.

1. date: This data type holds the date in terms of the year, month & day in a particular order YYYY-MM-DD. Here, the year is expressed in four digits ranging from 0001 to 9999. Whereas, the month and day are expressed in two digits.
2. time: This data type holds time and expresses it in the format (HH:MM:SS).
3. timestamp: This data type expresses date and time in the format YYYY-MM-DD HH:MM:SS. It ranges from 1970-01-01 00:00:01 to 9999-12-31 23:59:59.

DDL Commands

Data definition language commands are used to define the database structures such as to define the relations or tables for a database along with their attributes.

CREATE: The CREATE command is used to define a new relation in a database.

DROP: The DROP command is used to remove or eliminate an existing relation in the database.

ALTER: The ALTER command is used to modify the existing relation in the database.

TRUNCATE: The TRUNCATE command is used to eliminate all the instances of the table, thereby it preserves the outer structure of the table/relation.

RENAME: The RENAME command is used with ALTER command either to modify the name of relation or its attributes.

SQL Schema Definition

SQL DDL defines the schema of a relation using a command create table. The general form of the ‘create table’ command is as follow:

Create table r(A1 D1, 2 D2, ……An Dn, (integrity constraint1), ……
(integrity constraint_n));

r is the name of the relation; A_i is the name of an attribute in relation r; Di is the domain of the attribute. The create command is completed using a semicolon.

For example, we have to create a relation ‘Student’ then:

Create table Student (Student-id varchar(4), name varchar(20) not null, department varchar (20),
primary key (student-id));

This command would create a relation named Student which has three attributes student-id, name, department. This command also specifies that the attribute student-id is a primary key of the Student relation.

SQL data definition integrity constraints are logical conditions applied to the relation for maintaining the consistency of the data in the database.

When we create a relation, it is initially empty. To load data into a relation we use the insert command. For example, if we want to insert a student entity into Student relation then:

insert into Student values (CS12, ‘Steive’, ‘Computer’);

Remember, the values you specify in the insert command must be corresponding to the order of attributes you listed in create table command.

In case we want to delete any tuple or entity from a relation we can use the delete command, the general form of the delete command is:

delete from r where P;

In case we want to delete an entire relation, from the database then we can use the drop table command.

drop table r;

The alter table command adds an attribute to already existing relationships in a database. Its general form is

Alter table r add A D;
//where A is an attribute name and D is its corresponding domain.

Similarly, we can delete an attribute from already existing relationships in a database using alter drop command. The general form of alter drop command is:

Alter table r drop A;
// A is an attribute to be dropped from relation A

So, the SQL data definition language basically defines the structure of relations in the database. It also specifies the integrity constraints which prevent the damaging of data in the database.