Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Database Languages
Clients can store, direct, and recover information from data sets utilizing programming known as a data set administration framework (DBMS). DBMS languages and interfaces, an essential component of a DBMS, enable user interaction with the database system. We will analyze a few DBMS dialects and connection points in this article utilizing models.
DBMS Languages
Data Definition Language (DDL)
Data Definition Language (DDL) is a type of database language used to define a database's structure. Clients can add, eliminate, and adjust data set objects including tables, perspectives, and files.
DDL Commands
There are several DDL commands, such as −
CREATE
The create command is used to create a new database object, such as a table, view, or index. The syntax of the CREATE command will change based on the kind of object that is being formed.
Example
For instance, the SQL query that follows creates the "employees" database with the columns "id," "name," and "salary" −
CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, salary DECIMAL(10,2) );
Output Table
+----+--------+---------+ | id | name | salary | +----+--------+---------+
ALTER
ALTER is used to change a database object's existing structure. You can create, alter, or delete columns, constraints, or indexes with the ALTER command.
Example
For instance, the "employees" database gets a new field called "department" thanks to the SQL query that follows −
ALTER TABLE employees ADD department VARCHAR(50);
Output Table
+----+--------+--------+-------------+ | id | name | salary | department | +----+--------+--------+-------------+
DROP
DROP is used to remove a table, view, or other existing database object. The object and any related data are permanently deleted with the DROP command.
Example
For instance, the "employees" table is deleted with the SQL query that follows −
DROP TABLE employees;
Output
The "employees" table and all of its data would be permanently wiped following the execution of the "DROP TABLE employees" command, and there would be no input or output table to show.
TRUNCATE
Every piece of data from a table that already exists can be removed using TRUNCATE. The Shorten order, as opposed to the DROP order, basically eliminates the information from a data set, not the actual table.
Example
Let’s say this was the employee's table −
+----+--------+--------+-------------+ | id | name | salary | department | +----+--------+--------+-------------+ | 1 | Alice | 50000 | Engineering | | 2 | Bob | 60000 | Marketing | | 3 | Charlie| 75000 | Sales | | 4 | Dave | 65000 | Engineering | | 5 | Eve | 55000 | Marketing | +----+--------+--------+-------------+
For instance, the "employees" table is truncated by the SQL expression that follows −
TRUNCATE TABLE employees;
Output Table: +----+------+-------+------------+ | id | name | salary| department | +----+------+-------+------------+ | | | | | +----+------+-------+------------+
The "employees" table's data is completely removed by the TRUNCATE command, but the table itself is left intact. The table would still be there once the command was run, but it would be empty of data rows. The output table displays the "employees" table's structure after the command has been run but without any data.
RENAME
This command is used to rename a table or column in an existing database.
Example
Input Table
+----+--------+--------+-------------+ | id | name | salary | department | +----+--------+--------+-------------+ | 1 | Alice | 50000 | Engineering | | 2 | Bob | 60000 | Marketing | | 3 | Charlie| 75000 | Sales | | 4 | Dave | 65000 | Engineering | | 5 | Eve | 55000 | Marketing | +----+--------+--------+-------------+
For instance, the "staff" table is renamed in the following SQL query from the "employees" table −
RENAME TABLE employees TO staff;
Output Table
+----+--------+--------+-------------+ | id | name | salary | department | +----+--------+--------+-------------+ | 1 | Alice | 50000 | Engineering | | 2 | Bob | 60000 | Marketing | | 3 | Charlie| 75000 | Sales | | 4 | Dave | 65000 | Engineering | | 5 | Eve | 55000 | Marketing | +----+--------+--------+-------------+
The CQL command "RENAME TABLE employees TO staff" just renames the existing "employees" table to "staff" in the current keyspace, therefore there is no change to the input and output tables. The table's columns and contents stay the same.
As a result, the data and structure of the input and output tables are identical. The sole distinction is the name of the table, which has been changed from "employees" to "staff".
The management of a database's structure depends on DDL instructions. DBAs may build, alter, and destroy database objects including tables, views, and indexes using DDL commands. Additionally, data integrity restrictions like primary keys, unique constraints, and foreign keys are enforced via DDL commands.
Data Manipulation Language (DML)
You may alter and edit data in a relational database using the Data Manipulation Language (DML), a subset of SQL. In order to insert
DML Commands
There are several DML commands, such as −
INSERT
The INSERT command is used to add data to a table. The syntax of the INSERT command is as follows −
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Example
Consider a table having the columns "id," "name," and "email" named "customers." We can use the following command to add a new record to the table −
Input Table
Let’s say this was our initial table
+----+---------+---------------------+ | id | name | email | +----+---------+---------------------+ | 0 | Alice | alice@example.com | | 2 | Bob | bob@example.com | | 3 | Charlie | charlie@example.com | | 4 | Dave | dave@example.com | | 5 | Eve | eve@example.com | +----+---------+---------------------+
INSERT INTO customers (id, name, email) VALUES (1, 'John Doe', 'john.doe@example.com');
Output Table
+----+---------+---------------------+ | id | name | email | +----+---------+---------------------+ | 0 | Alice | alice@example.com | | 1 | John Doe| john.doe@example.com| | 2 | Bob | bob@example.com | | 3 | Charlie | charlie@example.com | | 4 | Dave | dave@example.com | | 5 | Eve | eve@example.com | +----+---------+---------------------+
UPDATE
A table's existing data can be changed using the UPDATE command. The UPDATE command has the following syntax −
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Example
For illustration, suppose we need to change the email address for a client with ID 1. We can use the following command to this −
Input Table
Let’s say this was our initial table
+----+---------+---------------------+ | id | name | email | +----+---------+---------------------+ | 0 | Alice | alice@example.com | | 2 | Bob | bob@example.com | | 3 | Charlie | charlie@example.com | | 4 | Dave | dave@example.com | | 5 | Eve | eve@example.com | +----+---------+---------------------+
UPDATE customers SET email = 'johndoe@example.com' WHERE id = 1;
Output Table
+----+---------+---------------------+ | id | name | email | +----+---------+---------------------+ | 0 | Alice | alice@example.com | | 1 | John Doe| johndoe@example.com| | 2 | Bob | bob@example.com | | 3 | Charlie | charlie@example.com | | 4 | Dave | dave@example.com | | 5 | Eve | eve@example.com | +----+---------+---------------------+
DELETE
To remove current data from a table, use the DELETE command. The DELETE command has the following syntax −
DELETE FROM table_name WHERE condition;
Example
Let's assume, for illustration, that we wish to remove the customer record with the id of 1. We can use the following command to this −
DELETE FROM customers WHERE id = 1;
Using the same input table −
Output Table
+----+---------+---------------------+ | id | name | email | +----+---------+---------------------+ | 0 | Alice | alice@example.com | | 2 | Bob | bob@example.com | | 3 | Charlie | charlie@example.com | | 4 | Dave | dave@example.com | | 5 | Eve | eve@example.com | +----+---------+---------------------+
SELECT
To get data out of a table, use the SELECT command. The SELECT command has the following syntax −
SELECT column1, column2, ... FROM table_name WHERE condition;
Example
Consider the scenario where we need to get the names and email addresses of every consumer who has made a transaction.We can use the following command to this −
Input Table
+----+----------+-----------------------+-------------------+ | id | name | email | has_made_purchase | +----+----------+-----------------------+-------------------+ | 1 | John Doe | john.doe@example.com | true | | 2 | Jane Doe | jane.doe@example.com | false | | 3 | Bob Smith| bob.smith@example.com| true | | 4 | Alice Lee| alice.lee@example.com| true | +----+----------+-----------------------+-------------------+
SELECT name, email FROM customers WHERE has_made_purchase = true;
Output Table
+----------+-----------------------+ | name | email | +----------+-----------------------+ | John Doe | john.doe@example.com | | Bob Smith| bob.smith@example.com| | Alice Lee| alice.lee@example.com| +----------+-----------------------+
You may alter and edit data in a relational database using the robust subset of SQL known as Data Manipulation Language (DML). The INSERT, UPDATE, DELETE, and SELECT commands are the four primary DML commands. These commands enable you to add fresh data to tables, edit already-existing data, remove data from tables, and retrieve data from tables based on certain criteria. Developers and data analysts both utilize these commands, which are crucial for dealing with databases.
Data Control Language (DCL)
A subset of SQL called Data Control Language (DCL) offers instructions to control access to database objects. It is used to manage database permissions and user access. We will talk about the different DCL commands and give use examples in this post.
DCL Commands
There are several DCL commands, such as −
GRANT
A user or role can be granted specified capabilities on a database object using the GRANT command. The GRANT command has the following syntax −
GRANT SELECT, INSERT, UPDATE ON employees TO jane;
Example
For instance, suppose we have a database called "employees" and we want to give the user "jane" access to SELECT, INSERT, and UPDATE rights. The following command can be used −
GRANT SELECT, INSERT, UPDATE ON employees TO jane;
Output
The GRANT command does not really construct an output table, therefore the output table would be identical to the input table. However, the command would give the user Jane access to the SELECT, INSERT, and UPDATE functions on the employees table.
REVOKE
Removing previously given rights from a user or role on a database object is done using the REVOKE command. The REVOKE command has the following syntax −
REVOKE privilege_name ON object_name FROM user_or_role;
Example
Say, for illustration, that we wish to remove the user "jane" from the "employees" table's SELECT privilege. The following command can be used −
REVOKE SELECT ON employees FROM jane;
Output
The CQL statement "REVOKE SELECT ON employees FROM jane" just revokes the SELECT permission previously given to the user "jane" on the "employees" database in the current keyspace, which explains why there is no change in the input and output tables. The table's columns and contents stay the same.
As a result, the data and structure of the input and output tables are identical. The sole change is that in the present keyspace, the user "jane" would no longer be allowed to do SELECT queries on the "employees" table.
DENY
A user or role can have certain rights on a database object denied by using the DENY command. The DENY command has the following syntax −
DENY privilege_name ON object_name TO user_or_role;
Example
For instance, suppose we wish to prevent the user "jane" from inserting data into the "employees" database. The following command can be used −
DENY INSERT ON employees TO jane;
Output
The CQL statement "DENY INSERT ON employees TO jane" only forbids the "jane" user from attempting to insert data into the "employees" table, which explains why the input and output tables are left unchanged. The table's columns and contents stay the same.
As a result, the data and structure of the input and output tables are identical. The "employees" table's access control permissions, which have been changed to deny the "INSERT" permission to the "jane" user, are the only thing that differs.
ALTER
An existing user or role's permissions can be changed using the ALTER command. The ALTER command has the following syntax −
ALTER USER user_name WITH {GRANT|DENY} privilege_name ON object_name;
Example
For instance, suppose we wish to give the user "jane" the SELECT privilege on the "employees" table. The following command can be used −
ALTER USER jane WITH GRANT SELECT ON employees;
Output
This command has no related output table. If the operation is successfully carried out, the Cassandra database's user account's permissions are only updated. When the user next signs in or tries to run a query that needs the SELECT permission on the "employees" table, the changed permissions will take effect.
Commands are available in the Data Control Language (DCL) to control permissions and access to database objects. The primary commands in DCL are ALTER, GRANT, REVOKE, and DENY. You can give or remove particular capabilities to users or roles on database objects using these procedures. Database administrators and security professionals both use these commands because they are crucial for managing security and access control in a database.
Transaction Control Language (TCL)
Database management systems (DBMS) employ Transaction Control Language (TCL) as a collection of instructions to handle transactions. Transaction beginning and end points, visibility restrictions, and result management are all controlled by TCL commands. Most DBMSs provide the three TCL commands COMMIT, ROLLBACK, and SAVEPOINT.
TCL Commands
There are several TCL commands, such as −
COMMIT
One uses the COMMIT command to correctly complete a transaction. All changes made during a transaction are permanently saved to the database when the COMMIT command is used. The COMMIT command has simple syntax, as follows −
COMMIT;
Example
Consider a database table called "customers" that holds details about customers like name, address, and phone number. Consider the following transaction that modifies the customer's phone number with ID 101::
Input Table
+-----+--------+-------------------+ | id | name | phone | +-----+--------+-------------------+ | 100 | Alice | 555-123-4567 | | 101 | Bob | 555-987-6543 | | 102 | Charlie| 555-555-1212 | +-----+--------+-------------------+
START TRANSACTION; UPDATE customers SET phone = '123-456-7890' WHERE id = 101; COMMIT;
Output Table
+-----+--------+-------------------+ | id | name | phone | +-----+--------+-------------------+ | 100 | Alice | 555-123-4567 | | 101 | Bob | 123-456-7890 | | 102 | Charlie| 555-555-1212 | +-----+--------+-------------------+
The aforementioned code will initiate a transaction, add ID 101 to the customer's phone number, and then commit the database modifications. The modified phone number will be permanently kept in the database after the transaction has been committed.
ROLLBACK
To reverse a transaction or return the database to its prior state, use the ROLLBACK command. The database resumes its previous state when a ROLLBACK command is used, wiping away all the changes performed during the transaction. The ROLLBACK command has the following syntax −
ROLLBACK;
Example
Consider a database table called "orders" that has data about client orders, including the order number, date, and total. Let's say we have a transaction that adds the following new order to the database −
Input Table
+----------+-------------+--------------+ | order_id | order_date | order_amount | +----------+-------------+--------------+ | 1 | 2023-04-12 | 100.00 | | 2 | 2023-04-13 | 150.00 | +----------+-------------+--------------+
START TRANSACTION; INSERT INTO orders VALUES (1001, '2023-04-14', 50.00); ROLLBACK;
Output Table
+----------+-------------+--------------+ | order_id | order_date | order_amount | +----------+-------------+--------------+ | 1 | 2023-04-12 | 100.00 | | 2 | 2023-04-13 | 150.00 | +----------+-------------+--------------+
The aforementioned code will initiate a transaction, add a fresh order to the database, and then undo all database modifications. The database will go back to its previous state when the transaction is rolled back, the new order is deleted.
SAVEPOINT
The SAVEPOINT command is used to provide a transactional point that may be later rolled back to. A savepoint is established in the transaction when the SAVEPOINT command is used. To reverse all changes made after the savepoint, execute the ROLLBACK TO SAVEPOINT command later on. The SAVEPOINT command has the following syntax −
SAVEPOINT savepoint_name;
Example
Assume we have an "inventory" database table, which provides details about the inventory such as the product name, quantity, and price. Let's say we have a transaction that modifies a product's amount as follows −
Input Table
+--------------+----------+ | product_name | quantity | +--------------+----------+ | Product A | 20 | | Product B | 30 | +--------------+----------+
START TRANSACTION; UPDATE inventory SET quantity = quantity - 5 WHERE product_name = 'Product A'; SAVEPOINT update_quantity; UPDATE inventory SET quantity = quantity + 5 WHERE product_name = 'Product B'; ROLLBACK TO SAVEPOINT update_quantity; COMMIT;
Output Table
+--------------+----------+ | product_name | quantity | +--------------+----------+ | Product A | 15 | | Product B | 30 | +--------------+----------+
To sum up, TCL commands are crucial for controlling transactions in a DBMS. To successfully complete a transaction, use the COMMIT command. To undo a transaction and return the database to its prior state, use the ROLLBACK statement. Finally, use the SAVEPOINT command to specify a point in the transaction to which we may later roll back. The consistency and integrity of the database may be preserved by appropriately understanding and employing TCL instructions.
Conclusion
A database management system's interfaces and languages are crucial parts. The database system's particular needs determine the language and interface to use. To make sure that the database system is successful, these variables must be carefully taken into account. Users can interact with the DBMS in a way that best suits their needs by picking the proper language and interface.
5K+ Views
