The SQL has been established as a language that can be used by both casual users as well as skilled programmers. It offers a wide range of processing capabilities, simpler ones that may be used by the beginners, and the more composite by the expert class of users.
The various processing capabilities of SQL are:
|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 the 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 programmings 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 are likely to comprise of enhanced features for integrity checking.|
|Transaction Control||SQL also includes commands for specifying the beginning and end of transactions along with commands for having control over transaction processing.|
Data Definition Language (DDL)
The database schema is specified by a set of definitions that are 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 which are 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 define 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 the table. Again, this is a significant advantage over earlier data models, in which the structure of the database 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 which 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.