Data Definition Language (DDL) in SQL
The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.
The most important DDL statements in SQL are:
• CREATE TABLE – creates a new database table
• ALTER TABLE – alters (changes) a database table
• DROP TABLE – deletes a database table
How to create table
Creating a database is remarkably straightforward. The SQL command which you have to give is just:
CREATE DATABASE dbname;
In this example you will call the database GJUniv, so the command which you have to give is:
CREATE DATABASE GJUniv;
Once the database is created it, is possible to start implementing the design sketched out previously.
So you have created the database and now it’s time to use some SQL to create the tables required by the design. Note that all SQL keywords are shown in upper case, variable names in a mixture of upper and lower case.
The SQL statement to create a table has the basic form:
CREATE TABLE name( col1 datatype, col2 datatype, …);
So, to create our User table we enter the following command:
CREATE TABLE User (FirstName TEXT, LastName TEXT, UserID TEXT, Dept TEXT, EmpNo INTEGER, PCType TEXT );
The TEXT datatype, supported by many of the most common DBMS, specifies a string of characters of any length. In practice there is often a default string length which varies by product. In some DBMS TEXT is not supported, and instead a specific string length has to be declared. Fixed length strings are often called CHAR(x), VCHAR(x) or VARCHAR(x), where x is the string length. In the case of INTEGER there are often multiple flavors of integer available. Remembering that larger integers require more bytes for data storage, the choice of int size is usually a design decision that ought to be made up front.
How to Modify table
Once a table is created it’s structure is not necessarily fixed in stone. In time requirements change and the structure of the database is likely to evolve to match your wishes. SQL can be used to change the structure of a table, so, for example, if we need to add a new field to our User table to tell us if the user has Internet access, then we can execute an SQL ALTER TABLE command as shown below:
ALTER TABLE User ADD COLUMN Internet BOOLEAN;
To delete a column the ADD keyword is replaced with DROP, so to delete the field we have just added the SQL is:
ALTER TABLE User DROP COLUMN Internet;
How to delete table
If you have already executed the original CREATE TABLE command your database will already contain a table called User, so let’s get rid of that using the DROP command:
DROP TABLE User;
