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
Data Definition Language in SQL
SQL is an acronym for Structured Query Language. Though it is termed as a ‘query language’, it defines the schema of the database, it modifies the database and also it can 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 carry 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 which 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:
numeric: The numeric data type specifies a number. Now, a numeric data type can express an exact value or an approximate value.
1. 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 the 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 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 give precision.
2. Approximate Numeric Value
- 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.
- double precision: It can hold a floating-point number which has precision twice the real data type. Here, the precision is between 22 to 53. Its storage size is 8 bytes.
- 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.
This kind of data type stores alphanumeric data.
- 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.
- varchar(n): This data type holds the character string of variable length. It will just store the exact number of characters that user specifies thereby saving the storage space. The varchar has a maximum length of 32,672 characters.
4. Date and Time
This data type holds the data in the context of date and time.
- date: This data type holds the date in term 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.
- time: This data type holds time and expresses it in the format (HH:MM:SS).
- 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.
SQL Schema Definition
SQL DDL defines the schema of a relation using a command create table. The general form of ‘create table’ command is as follow:
Create table r(A1 D1, 2 D2, ……An Dn, (integrity constraint1), ……
r is the name of the relation; Ai 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 condition 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 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 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 delete command, the general form of delete command is:
delete from r where P;
In case we want to delete an entire relation, from the database then we can use drop table command.
drop table r;
The alter table command adds an attribute to an already existing relation 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 an already existing relation 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
- SQL data definition language defines the set of relations in a database.
- SQL DDL also specify the structure or schema of each relation in a database.
- SQL DDLs create table command creates a relation and specifies the set of attributes and the domain for each attribute in a relation. It also specifies the integrity constraint for that particular relation.
- SQL DDLs drop command is used to delete the entire relation in a database.
- SQL DDLs alter command is used to add or delete the attributes in a relation.
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.