The database is needed everywhere, whatever service it is. So, now comes how to store the data in the database, or how to fetch the data from the database. In this article, we are going to discuss the SQL in DBMS.
What is SQL in DBMS?
The data in a relational database is created, maintained, stored, and retrieved using the standard database language known as structured query language (SQL).
It is explained using tuple relational calculus and relational algebra. All significant RDBMS distributions provide SQL as part of a package.
While using data manipulation properties, we may store and retrieve data from the database, the data definition properties assist in designing and modifying the schema.
SQL in DBMS stands for structured Query language. By the name itself, we can understand it’s a type of language. Language is something which is used for communication. We can communicate with each other if we know any language. Something similar is SQL, let’s see exactly what it is.
So, in the system, if the user wants to talk to the database, then, in that case, the user has to connect with a database, and that can be done through SQL in DBMS.
in 1970, E.F. Codd published his research on the topic Relational Model, In which he talked in detail about the relations, tables, etc.
Read more about Relational Models in DBMS here.
Later, to implement the same, IBM started experimenting with this and came up with the concept of SQL.
Characteristics of SQL in DBMS
Let’s look at some of the important characteristics of SQL in DBMS:
- SQL in DBMS is a Domain-specific language: Domain-specific language means it will only work with relational databases, because in a relational database, the data is stored in the form of Tables or relations, and each table has rows and columns.
- SQL in DBMS is considered as the declarative language: Declarative language basically tells what to do. like the simple query needs to be written which will tell what to do and all the things will be taken care of by the database then.
- SQL uses Keys and Constrains: Different foreign keys and primary keys are used in SQL in DBMS.
Commands in SQL in DBMS
Commands in SQL in DBMS basically tell us the way to perform different tasks, such as inserting values in the database, reading the value from the database, or modifying the value in the database.
There are 5 major commands in SQL in DBMS that can be used.
- DDL
- DQL
- DML
- DCL
- TCL
Let’s see each of them in detail:
DDL for SQL in DBMS
DDL stands for the Data Definition Language in SQL in DBMS. It is basically used to define the schema in the database. In the database, this command is basically responsible to create, modify and delete the structure of the database objects.
General Users cannot use these commands, these commands can only be used by DBA i.e., Database Administrator
Read in detail about Database Administrators.
Here is the list of the DDL commands in SQL in DBMS
1. CREATE: Create command in the SQL in DBMS is used to create the database or the database objects such as relations/tables, views, functions, index, etc. For example, if we have to create the schema.
CREATE DATABASE DATABASE_NAME;
# This will create the database
CREATE SCHEMA SCHEMA_NAME;
# This will help in creating the schema in that database
CREATE TABLE TABLE_NAME
(
COL_NAME1 data_type ( column size ),
COL_NAME2 data_type ( column size),
COL_NAME3 data_type ( column size),
…
COL_NAMEn data_type ( column size)
) ;
# This will help in creating a Table
2. ALTER: Alter command is used to alter or modify the structure of the existing database.
ALTER TABLE TABLE_NAME ADD COL_NAME COLUMN_DEFINITION;
# This command is used to add the new column in that table.
ALTER TABLE TABLE_NAME DROP COL_NAME1, COL_NAME2;
# This command is used to delete that column from that table.
3. DROP: The drop command is used to delete the objects of the database, such as if the schema needs to be deleted, the command can be:
DROP DATABASE DATABASE_NAME;
# This will delete the full database
DROP SCHEMA SCHEMA_NAME;
# This will delete that particular schema from that database
DROP SCHEMA TABLE_NAME;
# This command will delete the table from the database
TRUNCATE: This DDL command is used to clear the table, which means it deletes the full record from the table. For example:
TRUNCATE TABLE TABLE_NAME;
# This command is used to remove the records from the table.
4. RENAME: In order to change the name of the database table, rename command is being used. For example:
RENAME TABLE OLD_TABLE_NAME TO NEW_TABLE_NAME;
# This command will help in changing the old table name to the new table name.
DQL for SQL in DBMS
This SQL in DBMS command is used to read the data from the database. The only DQL command is the SELECT statement. DQL stands for Data Query Language.
The SELECT command is used to get the attributes from the tables with conditions described in the where clause. For Example:
SELECT EXP1, EXP2, EXP3 from TABLE_NAME WHERE conditions;
# This command will fetch the EXP1, EXP2, EXP3 from the table where the mentioned conditions are true.
DML for SQL in DBMS
This SQL in DBMS Command is used to make modifications in the database, any changes done in the database will be done by DML commands. DML stands for Data Manipulation Language.
Here is the list of DML commands:
1. INSERT: The INSERT command is used to insert the data into the tuples or rows of the table. For example
INSERT INTO TABLE_NAME
VALUES (VAL1, VAL2, VAL2, …. VALN);
# This command is used to insert the following values into the given table name, all the values given depend on the number of attributes the table has.
2. UPDATE: This DDL command is used to update the existing value in a table based on certain conditions. For example:
UPDATE TABLE_NAME SET COL_NAME1 = Val1 WHERE COL_NAME2 = val2
# This command will update the value of COL_NAME1 with Val1, it will update all the places where the value of COL_NAME2 is val2.
3. DELETE: If any row needs to get deleted from the table, then this command will be used. For example:
DELETE FROM TABLE_NAE WHERE condition;
# This command will help in deleting the particular record from the table where the given condition is met.
DCL for SQL in DBMS
DCL stands for Data Control Language. This command in SQL in DBMS is basically used to grant permission to the database user or to revoke particular permission from the database user.
Here are some DCL Commands:
1. GRANT: This DCL command is used to grant to access the database to the user. For example:
GRANT SELECT, UPDATE ON TABLE_NAME TO USER1, USER2;
# This command is used to give the select and update command access to the user1 and user2 and in table table_name.
2. REVOKE: This command is used to revoke the access of the permissions from the particular user. For example:
REVOKE SELECT, UPDATE ON TABLE_NAME TO USER1, USER2;
# This command is used to revoke the permission to select and update from user1 and user2 in table Table_name.
TCL for SQL in DBMS
TCL stands for Transaction Control Language. This command in SQL in DBMS is only used with the Database DML commands such as Update, insert, delete, etc.
Here are some of the TCL commands:
1. COMMIT: This TCL command is used for saving all the transactions to the database. For example:
DELETE FROM TABLE_NAME WHERE CONDITION;
COMMIT;
#This command will delete the records from the table and will save the updated table in the database also.
2. ROLLBACK: All those transactions which are not saved in the databases, can be rollback using this command.
DELETE FROM TABLE_NAME WHERE CONDITION;
ROLLBACK;
# This command is used to roll back the transaction since it is not being saved in the database.
3. SAVEPOINT: This TCL command is used to rollback the transaction till a certain point, it won’t rollback the entire transaction, for example:
SAVEPOINT SAVEPOINT_NAME;
# Till where the transaction needs to be roll_back, savepoint can be created till there and this command will roll back the transaction till that point.
SQL in DBMS is very important to understand, as it is considered the backbone of the Database Management System. It is used to give the read and write access to the databases to the users. SQL makes the query faster and it becomes easier for the user to access the database.