SQL has been established as a language that can be used by casual users and skilled programmers. It offers a wide range of processing capabilities, simpler ones that beginners may use and more composite by the expert class of users.
The various processing capabilities of SQL are:
| Type | Description |
|---|---|
| Data Definition Language (DDL) | The SQL DDL offers commands for defining relation schemas, deleting relations, creating indexes, and modifying relation schemas. |
| Interactive data manipulation language | DML includes query languages based on both relational algebra and tuple relational calculus. |
| Embedded Data Manipulation Language | The embedded form of SQL is designed for use within general-purpose programs such as PL/1, COBOL, Fortran, Pascal, or C. |
| View Definition | The SQL DDL also includes commands for defining views. |
| Authorization | The SQL DDL includes commands for specifying access rights to relations and views. |
| Integrity | SQL provides some limited forms of integrity checking. Future products and standards of SQL will likely comprise enhanced features for integrity checking. |
| Transaction Control | SQL also includes commands for specifying the beginning and end of transactions and commands for controlling transaction processing. |
Data Definition Language (DDL)
The database schema is specified by a set of definitions expressed by a particular language called data definition language. Changes to the structure of a database are handled by a different set of SQL statements, collectively termed as SQL Data Definition Language, or DDL.
Using DDL statements, you can do the following:
- Define and create a new table.
- Remove a table that is no longer needed.
- Alter the definition of an existing table.
- Classify a virtual table (view) of data.
- Establish security controls for a database.
- Index creation to make table search faster.
- Control the physical storage of data by the DBMS.
DDL is also used to define the length of data items. It may also specify the encoding the program uses in the data items (binary, character, bits, string, etc.).
Although the DDL and DML are two distinct parts of the SQL language, in most SQL-based DBMS products, the split is a conceptual one only. Usually, the DDL and DML statements are submitted to the DBMS in the same way, and they can be freely intermixed in both interactive SQL sessions and programmatic SQL applications. If a program or user needs a table to store its temporary results, it can create the table, populate it, manipulate the data, and then delete it. Again, this is a significant advantage over earlier data models, in which the database structure was fixed when the database was created.
Data Manipulation Language (DML)
After the database schema has been specified and the database has been created, the data can be manipulated using a set of procedures that are expressed by a particular language called a data manipulation language.
By data manipulation, language means:
- Retrieval of information stored in the database.
- Insertion of new information into the database.
- Deletion of information from the database.
- Modification of data in the database.