Mastering Database Commands and Operations: Unleashing the Power of Data Management
Structured Query Language (SQL) is a programming language used to manage and manipulate data stored in relational databases. SQL was first developed in the 1970s by IBM as a part of the System R project, and it was later adopted by many other database vendors.
SQL has become the standard language for interacting with relational databases and is widely used in many industries including finance, healthcare, retail and technology. SQL is used to perform various database operations such as creating, modifying, deleting, inserting, updating, deleting and retrieving data from one or more tables.
One of the main advantages of SQL is its simplicity and ease of use. SQL commands are written in a declarative format, meaning that users only need to specify what they want the database to do and the system takes care of the implementation. Additionally, SQL is highly scalable and can handle large amounts of data with ease.
Types of SQL Commands
Data Definition Language (DDL)
Data Definition Language (DDL) and it is a type of SQL commands used to define and modify the structure of database objects. DDL commands are used to Create, Modify and Delete database objects such as tables, views, indexes and procedures. DDL commands also define the relationships between these objects, specify constraints to maintain data integrity and set permissions for accessing these objects.
Data Definition Language (DDL) commands include:
- Create : Used to create new database objects, such as tables, views and procedures.
CREATE TABLE employees (
ID INT,
first_name VARCHAR(25),
last_name VARCHAR(25),
department VARCHAR(25),
address VARCHAR(25)
);- Alter : Alter is used to modify the structure of existing database objects, such as adding or deleting columns from a table or changing the data type of a column.
-- ADDING COLUMN
ALTER TABLE employees
ADD phone_no INT;
-- DELETE COLUMN
ALTER TABLE employees
DROP COLUMN last_name;
-- CHANGING DATA TYPE
ALTER TABLE employees
MODIFY COLUMN phone_no VARCHAR(15);- Drop : Drop is used to delete an existing database object, such as a table or view
DROP TABLE employees;- Truncate: Truncate is used to delete all the data from a table, but not the table structure itself.
TRUNCATE TABLE employees;Data Manipulation Language (DML)
Data Manipulation Language (DML) commands are used to manipulate or modify the data within the database. DML commands are used to add, update, delete or retrieve data from the database.
Data Manipulation Language (DML) commands include:
Insert : Insert command is used to insert one or more rows of data into a table. You can either specify the values for all columns or only for a subset of columns. If you don’t specify a value for a column, it will be set to NULL.
INSERT INTO employees(ID, first_name, last_name, department, address)
VALUES (457,'Fahad','Arshad','IT','Peshawar');Update : Update is used to modify existing data in one or more rows of a table. You can update one or more columns, and you can also use the WHERE clause to update only specific rows.
UPDATE employees
SET first_name = 'M.Fahad'
WHERE ID = 457;Delete : Delete is a DML command which allows SQL users to remove single or multiple existing records from the database tables.
DELETE FROM employees
WHERE ID=457;Data Query Language (DQL)
Data Query Language (DQL) is used to retrieve and manipulate data stored in a Relational Database Management System (RDBMS). DQL commands are used to perform queries and retrieve data from one or more tables in a database. The most commonly used DQL command is the SELECT statement, which allows you to retrieve data from one or more tables based on specified criteria.
Data Query Language (DQL) uses only one command:
- Select : The Select statement specifies the columns you want to retrieve and the table you want to retrieve data from. It can also include other clauses such as WHERE, GROUP BY, HAVING, and ORDER BY to filter, group and sort the retrieved data.
SELECT *
FROM employees
WHERE Department = 'IT'
ORDER BY ID ASC;Data Control Language (DCL)
DCL stands for Data Control Language is used to control access to data stored in a relational database. DCL commands are used to grant or revoke privileges to users or groups of users, control data access and enforce security measures to ensure the integrity of the database.
Data Control Language (DCL) commands include:
- Grant : Grant command is used to grant privileges to users or groups of users on a particular database object, such as a table or view.
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO usama;- Revoke : Revoke command is used to revoke previously granted privileges from users or groups of users on a particular database object.
REVOKE SELECT ON employee FROM hamza;Transaction Control Language (TCL)
Transaction Control Language(TCL) is used to manage transactions in a relational database. TCL commands are used to control the transactional behavior of the database, which is important for ensuring data consistency and integrity.
Transaction Control Language (TCL) commands include:
- Commit : The COMMIT command is used to make permanent the changes made during a transaction. Once a COMMIT statement is executed, all changes made to the database within the transaction are saved permanently.
BEGIN TRANSACTION;
COMMIT;- Roll Back : The ROLLBACK command is used to undo changes made during a transaction and restore the database to its previous state. Once a ROLLBACK statement is executed, all changes made to the database within the transaction are discarded.
BEGIN TRANSACTION;
UPDATE Employee
SET salary = salary * 1.1
WHERE department = 'Sales';
COMMIT;- Save Point : The SAVEPOINT command is used to create a save point within a transaction, which can be used to rollback only part of the transaction.
BEGIN TRANSACTION;
UPDATE Employee
SET salary = salary * 1.1
WHERE department = 'Sales';
SAVEPOINT my_savepoint;
UPDATE Employee
SET salary = salary * 1.05
WHERE department = 'Marketing';
ROLLBACK TO my_savepoint;
UPDATE Employee
SET salary = salary * 1.02
WHERE department = 'Marketing';
COMMIT;- Set Transaction : SET TRANSACTION is a SQL command that is used to set transaction characteristics such as isolation level, read/write access mode, and the transaction name for a particular transaction.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE Employee
SET salary = salary * 1.1
WHERE department = 'Sales';
COMMIT;Conclusion
In conclusion, SQL is a powerful programming language used for managing and manipulating data in relational databases. SQL commands allow users to Retrieve, Insert, Update and Delete data from databases, as well as create and modify database objects such as tables, indexes and views.
SQL commands are designed to be simple and intuitive, making it easy for users to interact with and manage data. Some of the most commonly used SQL commands include Select, Insert, Update, Delete, Create, and Drop.
- Data Manipulation Language (DML): Data Manipulation Language (DML) used to manipulate data in a table. It includes commands such as SELECT, INSERT, UPDATE, and DELETE
- Data Definition Language (DDL): Data Definition Language (DDL) is used to define or modify the structure of the database. It includes commands such as CREATE, ALTER, TRUNCATE and DROP
- Data Control Language (DCL): Data Control Language is used to control access to data in the database. It includes commands such as GRANT and REVOKE
- Transaction Control Language (TCL): Transaction Control Language is used to manage transactions in the database. It includes commands such as COMMIT and ROLLBACK
- Data Query Language (DQL) : Data Query Language is used to retrieve data from the database. It includes only one command, SELECT
