8.2: Define a Database Using SQL Data Definition Language

It is common knowledge to most who are familiar in the coding world that SQL is used universally to define databases and perform certain actions to interact with the content in those databases. Of these, there exist 4 major categories: Data Definition Languages, Data Query Languages, Data Manipulation Languages, and Data Control Languages. 

A Data Definition Language, also known as DDL, is what consists of the “SQL commands that are used to define the database schema” (Varshini, D., 2019, August 26). DDL is also used to modify the content within the database: the database objects as well as the schemas. There are many different commands, including CREATE, DROP, ALTER, TRUNCATE, COMMENT, and RENAME. Each of these commands has its own unique utility and function when implemented as a SQL query. For instance, CREATE and DROP are used to simply create and delete database tables. ALTER can be used to add new columns to a table. So for example, if we had the table below: 

EmployeeName	EmployeeID	Position
John	123	Engineer
Jeff	456	Accountant
Jason	789	QA

Figure \(\PageIndex{1}\): Employee (Kashefi 2020) 
 

If we wanted to add each employee’s salary, we could use the command: 

ALTER TABLE Employee
ADD Salary 

Which would give us this table: 

EmployeeName	EmployeeID	Position	Salary
John	123	Engineer
Jeff	456	Accountant
Jason	789	QA

Figure \(\PageIndex{2}\): Employee (Kashefi 2020) 

And because the salary column was not filled, we are left with a new, blank column. If we wanted to clear all the information but leave the table and columns, we could use TRUNCATE:

TRUNCATE TABLE Employee 

Which would leave us with this table: 

EmployeeName	EmployeeID	Position	Salary

Figure \(\PageIndex{3}\): Employee (Kashefi 2020) 

The next category used to define a database with SQL is Data Query Language, or DQL. This category of language is actually used to perform manipulations to the data within schemas rather than the schemas themselves as mentioned above. The primary example of DQL is the SELECT command which selects a set of data which you want to perform your operation on and does this by retrieving the data from the database (Varshini, D., 2019, August 26). It is also worth noting that the primary purpose of DQL is to get a schema relation based on the query submitted. More information on DQL, its commands, and its uses can be found in Section 5.3. 

DML, or Data Manipulation Language, is the next major category, which deals with data present in the database. This is different from DQL, because DQL only takes into consideration data from the schema object. It is also worth noting that this category includes most of the commonly used SQL statements such as INSERT, DELETE, and UPDATE. These allow users to actually modify data in small increments, rather than make sweeping changes that would be seen in DDL. For example, let's look back at the table in Figure Figure \(\PageIndex{3}\).

Currently this table is empty, but what if we wanted to add a new employee who has a name, ID, position, and salary? We would use this command: 

INSERT INTO Employee(EmployeeName,EmployeeID,Position,Salary)
VALUES (“Jonah”, 159, Janitor, 1000000) 

Which would leave us with this table: 

EmployeeName	EmployeeID	Position	Salary
Jonah	159	Janitor	1000000

Figure \(\PageIndex{4}\): Employee (Kashefi 2020)

Next is DCL, which stands for Data Control Language. This category is mostly concerned with the rights and permissions of other users on the network database and has commands which manage these. These commands such as GRANT and REVOKE simply grant or revoke permissions to a specified user on the database system. This is important for both data security and data integrity. It ensures that data can only be seen by people who must see it, and it also ensures data cannot be changed by someone who does not have an understanding of what is happening in the database. 

The last category we will discuss is TCL. TCL was not mentioned above, as there is debate on whether or not TCL is actually something that should be considered a major category within the data definition namespace. However, to be thorough we will touch on it briefly here. It essentially deals with transactions of data within the database. It does this through the use of commands such as COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION. All of these perform a certain action by specified keyword on a transaction. This can give users the ability to control versions of their databases. This can become important in the case of incorrect information or even a corrupted database.