SQL Command Types

 Data Definition Language (DDL)

 Data Definition Language (or simply DDL) enables you to create, change or restructure, and even destroy the basic elements that are contained in a relational database. DDL focuses only on the structure, not the data contained within the elements. These basic elements or data objects include tables, schemas, views and more. Having no independent physical existence, a viewis regarded as a virtual table in which its definition only exists in the metadata. However, the view’s data comes from the table (or tables) where you will derive the view. Stated below are some of the most common DDL commands:

◦ CREATE– This command statement is responsible for building the database structure. Its syntax is:

CREATE TABLE CREATE VIEW

◦ ALTER– This command statement is in charge of changing the database structure after it has been created. Its syntax is:

ALTER TABLE ALTER VIEW

◦ DROP– This command is the reverse of the CREATE statement, which destroys the database structure. Its syntax is:

DROP TABLE

• Data Manipulation Language (DML)

Data Manipulation Language (or simply DML) consists of SQL commands that handle data maintenance functions. This means that you are able to manipulate the data contained within the relational database objects. The command statements, which read like normal English sentences, will allow you to enter, change, remove or retrieve data. The following are the DML statements commonly used:

◦ INSERT– This command statement is used to insert new data values into a certain table. To add values into a table with two columns, use the following syntax:

INSERT INTOTABLE_NAME VALUES (‘value1’, ‘value2’);

TABLE_NAMEis the name of the table where you will be adding the new values. The number of items inside theVALUESparenthesis represents the number of columns of the table, which are arranged in the same order as the said columns. If the values are of character or date/time data types, they need to be enclosed by single quotation marks. This is not required for numeric or null values (the null value should be written as NULL).

◦ UPDATE– This command statement is used to modify or alter pre-existing data values in a table, not add or remove records. The update is done one table at a time or multiple rows/columns of one table within a database. To change a single column, use the following syntax:

UPDATETABLE_NAME SETCOLUMN_NAME= ‘value’ [WHERECONDITION];

As long as the given WHERE clause is satisfied, then the value of the COLUMN_NAMEwill be updated. This could be within one or multiple records of the givenTABLE_NAME.

◦ DELETE– This command statement deletes certain records or even the entire table, not data values from specific columns. To remove a single row or multiple records from a table, use the following syntax:

DELETE FROMTABLE_NAME [WHERECONDITION];

The WHERE clause is an important part of this command if you want to delete selected rows from theTABLE_NAME.

DROP VIEW

• Data Query Language (DQL)

Data Query Language (or simply DQL) consists of commands that perform data selection, which is the main focus of relational database users in the world of SQL. The statement used is SELECT that can be accompanied by other clauses or options so that your extracted results will be in an organized and readable format. You can submit a query to the database using a separate application interface or just a single command-line. The following is a syntax for a simple SELECT statement:

SELECT [* | ALL | DISTINCTCOLUMN1,COLUMN2] FROMTABLE1[,TABLE2];

Using the asterisk(*)means that all columns of the given table are included in the output and will be displayed. The ALL option extracts and displays all values, even duplicates, for a column. On the other hand, using the keyword DISTINCT prevents duplicate rows from being included and displayed in the output. What follows the FROM keyword is a list of one or more tables where you want to get the data. The columns and tables specified in the syntax are all separated by commas.

• Data Control Language (DCL)

Data Control Language (or simply DCL) consists of commands that allow you to manage data access within the database. Furthermore, the database is protected from accidental or intentional misuse by controlling user privileges. DCL concentrates on transactions, which capture all SQL statements that perform database operations and save them in a log file. The following are the common DCL command statements:

◦ GRANT– This statement provides you with certain privileges, like giving you the permission to access the database. Its syntax is:

GRANTPRIVILEGE1, PRIVILEGE2, …TOUSER_NAME

◦ REVOKE– This statement revokes your privileges, like removing your permission to access the database. Its syntax is:

REVOKEPRIVILEGE1, PRIVILEGE2, …TOUSER_NAME

• Transactional Control Commands

Transactional control commands allow users to manipulate various transactions in maintaining database integrity. In SQL, transactions begin when applications are executed. The very first transaction is started at the onset of the SQL application, while the last transaction is ended when the application is terminated. The following are the common transactional control commands:

◦ COMMIT– This statement completes a transaction by making the changes you made to the database permanent, or simply saving the transactions. Its syntax is:

COMMIT [WORK];

In the previous command line, the keywordWORKis optional.

◦ ROLLBACK– This statement’s primary function is to restore the database system to its previous state or undo all the actions that took place in the transaction log. Its syntax is:

ROLLBACK [WORK];

In the previous command line, the keywordWORKis optional.

◦ SAVEPOINT– This statement works with the ROLLBACK command, wherein it creates sections or points within groups of transactions in which you will be performing the ROLLBACK command. Its syntax is:

SAVEPOINTSAVEPOINT_NAME;