SQL Data Definition Language Statements

In this chapter you will gain a deeper understanding of the three Data Definition Language statements – CREATE, ALTER and DROP. Using SQLiteStudio, you will also learn how to encode the corresponding SQL statements that handle the database structure.
Again, a database object is any defined logical unit that stores or references data. When you have a collection of database objects, you create a schema that is associated with one particular database owner. The focus of this chapter is the basic form of data storage, which is the relational database table. A simple table further consists of rows, which corresponds to the records of data, and columns, which are also known as fields that contain an assigned particular type of data. A database table will always have at least one column and a row that is composed of one or more fields.

CREATE Statement

The numerous forms of the SQL CREATE statement are responsible for constructing vital database structures and objects – tables, views, schemas, domains and so on. The act of creating tables could be easy, but you need to take into consideration numerous factors. Planning table structures before actual implementation could save you time and effort since you do not need to reconfigure after the tables have been created.
Here are some of the factors to take into consideration through when creating tables:

Type of data the table will contain

Table and column names

Primary key (the column that makes each row of data unique to avoid duplicate records in a table)

Column length

Columns containing null values

The column names (field1, field2, field3, field4 and field5) and the field data types are written inside the parenthesis, separated by commas. Anything indicated inside the brackets are considered optional and the syntax statement finally ends with a semicolon.
Using the CUSTOMER TABLE from Chapter 2, you will create a new database table using SQLiteStudio.

Create a New Database

1. Open SQLiteStudio by double-clicking the application icon on your desktop.


2. Click the DATABASE menu then select ADD A DATABASE.


3. Type Sample_DB inside the FILE input box (which is the name of the new database) then click OK.


4. You will now have the Sample_DB object inside the Database Navigator pane.

Create a New Table

1. Click the TOOLS menu then select OPEN SQL EDITOR. You will have the SQL editor area at the right pane. If you double-click Sample_DB, you will see TABLES and VIEWS under the database object. Right now, there are no tables nor views present so you will create one using the SQL Editor pane.


2. Under the QUERY Tab, type the following lines of code
CREATE TABLE Customer_TBL (CustomerID INTEGER NOT NULL PRIMARY KEY, CustomerName VARCHAR NOT NULL, JobPosition VARCHAR, CompanyName VARCHAR NOT NULL, USState VARCHAR NOT NULL, ContactNo BIGINTEGER NOT NULL);

Click the EXECUTE QUERY button on top of the QUERY Tab or press F9 on the keyboard. You will now have the Customer_TBL table with 6 columns.

ALTER Statement
The SQL ALTER statement is used to modify database objects, specifically tables. Altering table elements can include adding and dropping columns, changing column definitions, adding and dropping constraints, modifying table storage values and more.

Alter a Table by Adding a New Column

1. A new column that contains the company address of the customer will be added to the Customer_TBL table. Type the following lines of code under the QUERY tab:
   ALTER TABLE Customer_TBL ADD CompanyAdd VARCHAR;


2. After clicking the EXECUTE QUERY button , the CompanyAdd column is added after the ContactNo column. This field contains values of string data type.

DROP Statement
You use the SQL DROP statement if you want to delete database objects. Thus, the DROP TABLE statement is used to delete tables that you do not need anymore. Once this line is executed, all the data and metadata contained in the table are also removed. DROP TABLE is considered to be the easiest command to execute. However, an error will occur if the table to be deleted is being referenced by another table in the database. That is why you need to be cautious when performing the DROP statement to avoid deleting objects by mistake (most especially if there are multiple users who access the database).
The RESTRICT option is used if an error is to be returned when a table referenced by another database object is dropped. On the other hand, the CASCADE option allows the table and all other referencing objects to be deleted. There are some SQL application programs that do not permit the CASCADE option to guarantee that there will be no invalid database objects.

Drop an Existing Table

1. Since there is only one table in the database (Customer_TBL), you do not need to worry whether to use the RESTRICT or the CASCADE option. Simply enter the following line of code inside the QUERY tab.
   DROP TABLE Customer_TBL;


2. Click the EXECUTE QUERY button deleted.

In this chapter you have learnt how to encode programming lines using the common DDL command statements in creating, altering and dropping database tables in SQL. In the next chapter you will learn the different DML commands that will allow you to manipulate information contained in database tables.