SQL and Parts of SQL language

by

SQL is a popular programming language, which is used to manage the relational databases and to perform various operations on its data. In a relational database, data is stored in tabular form, and there are proper defined rows and columns in each table where data is stored.

The full form of SQL is the Structured Query language. At the time of development name of SQL is SEQUEL (Structured English Query Language). It is an ANSI (American National Standard Language) standard language that helps in storing, manipulating, and retrieving data from the database. There are various defined commands into SQL through which we perform operations.

There are four basic commands. These are

  1. DDL
  2. DML
  3. DCL
  4. TCL

1. Data Definition Language (DDL)

DDL statements are used for defining, altering, and managing the schema and structure of a database and database objects like tables, indexes, and views. These commands do not manipulate data itself but rather define how the data is organized and structured.

Key DDL commands include:

  • CREATE: Used to create a new table, view, or other database objects.
  • ALTER: Modifies the structure of an existing database object, such as adding or deleting columns in a table.
  • DROP: Deletes an entire database object, permanently removing its structure and data.
  • TRUNCATE: Removes all records from a table, including all spaces allocated for the records are removed.
  • RENAME: Renames an existing database object.

2. Data Manipulation Language (DML)

DML statements are used for managing data within database objects like tables. This includes inserting data into tables, updating existing data, and deleting data. DML is all about data manipulation and retrieval.

Common DML commands are:

  • SELECT: Retrieves data from a database table.
  • INSERT: Adds new rows of data to a table.
  • UPDATE: Modifies existing data within a table.
  • DELETE: Removes rows of data from a table.

3. Data Control Language (DCL)

DCL statements are used to control access to data in a database. This is primarily about permissions and access rights for users and roles to ensure security and proper access control.

The main DCL commands include:

  • GRANT: Gives users access privileges to the database.
  • REVOKE: Removes access privileges from users.

4. Transaction Control Language (TCL)

TCL statements are used to manage the changes made by DML statements. It allows SQL statements to be grouped together into logical transactions. This is crucial for maintaining the integrity and consistency of data within the database. TCL commands help manage transactions, ensuring that database operations are executed safely and efficiently.

Key TCL commands are:

  • COMMIT: Commits a transaction, making all changes made during the transaction permanent.
  • ROLLBACK: Reverts the database to the last committed state, undoing any changes made during the current transaction.
  • SAVEPOINT: Sets a savepoint within a transaction, to which you can later roll back.
  • SET TRANSACTION: Specifies characteristics for the transaction.

For a Relational Database system, SQL is a standard language. Most of the Relational Database Management systems like MySQL, MS Access, Oracle, Sybase, Informix, Postgres, and SQL Server use SQL as their standard database language.

Overall basic functions of SQL are the following

  • We can access a database through SQL
  • We can insert new records in the database through database
  • We can update the database through SQL
  • We can delete the database too with the help of it