SQL Commands | DDL, DQL, DML, DCL and TCL Commands

Last Updated: 01-Jul-2024 10:48:00
616 Views
Summarize

Git is a distributed version control system DVCS designed for efficient source code management, suitable for both small and large projects. It allows multiple developers to work on a project simultaneously without overwriting changes, supporting collaborative work, continuous integration, and deployment. This Git and GitHub tutorial is designed for beginners to learn fundamentals and advanced concepts, including branching, pushing, merging conflicts, and essential Git commands. Prerequisites include familiarity with the command line interface CLI, a text editor, and basic programming concepts. Git was developed by Linus Torvalds for Linux kernel development and tracks changes, manages versions, and enables collaboration among developers. It provides a complete backup of project history in a repository. GitHub is a hosting service for Git repositories, facilitating project access, collaboration, and version control. The tutorial covers topics such as Git installation, repository creation, Git Bash usage, managing branches, resolving conflicts, and working with platforms like Bitbucket and GitHub. The text is a comprehensive guide to using Git and GitHub, covering a wide range of topics. It includes instructions on working directories, using submodules, writing good commit messages, deleting local repositories, and understanding Git workflows like Git Flow versus GitHub Flow. There are sections on packfiles, garbage collection, and the differences between concepts like HEAD, working tree, and index. Installation instructions for Git across various platforms Ubuntu, macOS, Windows, Raspberry Pi, Termux, etc. are provided, along with credential setup. The guide explains essential Git commands, their usage, and advanced topics like debugging, merging, rebasing, patch operations, hooks, subtree, filtering commit history, and handling merge conflicts. It also covers managing branches, syncing forks, searching errors, and differences between various Git operations e.g., push origin vs. push origin master, merging vs. rebasing. The text provides a comprehensive guide on using Git and GitHub. It covers creating repositories, adding code of conduct, forking and cloning projects, and adding various media files to a repository. The text explains how to push projects, handle authentication issues, solve common Git problems, and manage repositories. It discusses using different IDEs like VSCode, Android Studio, and PyCharm, for Git operations, including creating branches and pull requests. Additionally, it details deploying applications to platforms like Heroku and Firebase, publishing static websites on GitHub Pages, and collaborating on GitHub. Other topics include the use of Git with R and Eclipse, configuring OAuth apps, generating personal access tokens, and setting up GitLab repositories. The text covers various topics related to Git, GitHub, and other version control systems Key Pointers Git is a distributed version control system DVCS for source code management. Supports collaboration, continuous integration, and deployment. Suitable for both small and large projects. Developed by Linus Torvalds for Linux kernel development. Tracks changes, manages versions, and provides complete project history. GitHub is a hosting service for Git repositories. Tutorial covers Git and GitHub fundamentals and advanced concepts. Includes instructions on installation, repository creation, and Git Bash usage. Explains managing branches, resolving conflicts, and using platforms like Bitbucket and GitHub. Covers working directories, submodules, commit messages, and Git workflows. Details packfiles, garbage collection, and Git concepts HEAD, working tree, index. Provides Git installation instructions for various platforms. Explains essential Git commands and advanced topics debugging, merging, rebasing. Covers branch management, syncing forks, and differences between Git operations. Discusses using different IDEs for Git operations and deploying applications. Details using Git with R, Eclipse, and setting up GitLab repositories. Explains CI/CD processes and using GitHub Actions. Covers internal workings of Git and its decentralized model. Highlights differences between Git version control system and GitHub hosting platform.

2 trials left

SQL (Structured Query Language) is a standard language for managing and manipulating databases. It includes a variety of commands grouped into different categories based on their functionality. In this article, we will explore the different types of SQL commands: DDL (Data Definition Language), DQL (Data Query Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language). Each command will be explained in detail with examples.

SQL Commands

Data Definition Language (DDL)

DDL commands are used to define and modify the structure of database objects such as tables, indexes, and views. The primary DDL commands are `CREATE`, `ALTER`, `DROP`, and `TRUNCATE`.

CREATE

The `CREATE` command is used to create a new database object, such as a table, view, or index.

Command Description Syntax
CREATE It is used to create a new table or database object. CREATE TABLE table_name (column1 datatype, column2 datatype, ...);

Example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

ALTER

The `ALTER` command is used to modify an existing database object, such as adding, deleting, or modifying columns in a table.

Command Description Syntax
ALTER It is used to modify the structure of an existing table. ALTER TABLE table_name ADD column_name datatype;

Syntax:

--- ADD Column
ALTER TABLE table_name
ADD column_name datatype;

--- DROP Column
ALTER TABLE table_name
DROP COLUMN column_name;

-- MODIFY Column
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Example:

ALTER TABLE employees
ADD department VARCHAR(50);

ALTER TABLE employees
DROP COLUMN department;

ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(15, 2);

DROP

The `DROP` command is used to delete an existing database object, such as a table, view, or index.

Command Description Syntax
DROP It is used to delete an existing table, database, or other objects. DROP TABLE table_name;

Example:

DROP TABLE employees;

TRUNCATE

The `TRUNCATE` command is used to remove all records from a table, but the table structure remains.

Command Description Syntax
TRUNCATE It is used to delete all rows from a table without deleting the table itself. TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE employees;

Data Query Language (DQL)

DQL commands are used to query and retrieve data from a database. The primary DQL command is `SELECT`.

SELECT

Command Description Syntax
SELECT It is used to retrieve data from the database. SELECT column1, column2, ... FROM table_name WHERE condition;

 The `SELECT` command is used to retrieve data from the database.

Example:

SELECT name, position, salary
FROM employees
WHERE salary > 50000;

Data Manipulation Language (DML)

DML commands are used to manipulate data stored in the database. The primary DML commands are `INSERT`, `UPDATE`, and `DELETE`.

INSERT

 The `INSERT` command is used to add new records to a table.

Command Description Syntax
INSERT It is used to add new rows of data to a table. INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Example:

INSERT INTO employees (id, name, position, salary, hire_date)
VALUES (1, 'John Doe', 'Manager', 75000, '2023-01-01');

UPDATE

 The `UPDATE` command is used to modify existing records in a table.

Command Description Syntax
UPDATE It is used to modify existing data in a table. UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Example:

UPDATE employees
SET salary = 80000
WHERE id = 1;

DELETE

 The `DELETE` command is used to remove existing records from a table.

Command Description Syntax
DELETE It is used to delete existing rows from a table. DELETE FROM table_name WHERE condition;

Example:

DELETE FROM employees
WHERE id = 1;

MERGE

The MERGE command is used to perform an UPSERT operation, which inserts or updates data based on whether a condition is met.

Command Description Syntax
MERGE It is used to perform an UPSERT operation (insert or update data based on a condition). MERGE INTO target_table USING source_table ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 WHEN NOT MATCHED THEN INSERT (columns) VALUES (values);

Example : 

MERGE INTO employees AS target
USING (SELECT 1 AS employee_id, 'John' AS first_name, 'Doe' AS last_name, '[email protected]' AS email, '2023-01-01' AS hire_date, 65000 AS salary) AS source
ON (target.employee_id = source.employee_id)
WHEN MATCHED THEN
    UPDATE SET target.salary = source.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, email, hire_date, salary)
    VALUES (source.employee_id, source.first_name, source.last_name, source.email, source.hire_date, source.salary);

Data Control Language (DCL)

DCL commands are used to control access to data in the database. The primary DCL commands are `GRANT` and `REVOKE`.

GRANT

The `GRANT` command is used to give permissions to users.

Command Description Syntax
GRANT It is used to give permissions to users. GRANT privilege_name ON object_name TO {user_name | PUBLIC | role_name};

Example:

GRANT SELECT, INSERT
ON employees
TO john_doe;

REVOKE

The `REVOKE` command is used to remove permissions from users.

Command Description Syntax
REVOKE It is used to take back permissions from users. REVOKE privilege_name ON object_name FROM {user_name | PUBLIC | role_name};

Example:

REVOKE SELECT, INSERT
ON employees
FROM john_doe;

Transaction Control Language (TCL)

TCL commands are used to manage transactions in the database. The primary TCL commands are `COMMIT`, `ROLLBACK`, and `SAVEPOINT`.

COMMIT

The `COMMIT` command is used to save all changes made in the current transaction.

Command Description Syntax
COMMIT It is used to save the changes made during the current transaction. COMMIT;

Example:

UPDATE employees
SET salary = 80000
WHERE id = 1;
COMMIT;

ROLLBACK

The `ROLLBACK` command is used to undo changes made in the current transaction.

Command Description Syntax
ROLLBACK It is used to undo changes made during the current transaction. ROLLBACK;

Example:

UPDATE employees
SET salary = 80000
WHERE id = 1;
ROLLBACK;

SAVEPOINT

 The `SAVEPOINT` command is used to set a point in a transaction to which you can later roll back.

Command Description Syntax
SAVEPOINT It is used to undo changes made during the current transaction. SAVEPOINT savepoint_name;

Example:

SAVEPOINT sp1;
UPDATE employees
SET salary = 80000
WHERE id = 1;
ROLLBACK TO sp1;

Detailed Command Descriptions

Data Definition Language (DDL) Commands

CREATE

The `CREATE` command is fundamental in SQL as it is used to create new database objects. The following examples illustrate creating various database objects such as tables and indexes.

Creating a Table:

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

Creating an Index:

CREATE INDEX idx_salary
ON employees (salary);

In these examples, the `CREATE TABLE` statement defines a new table named `departments` with columns for `id`, `name`, and `location`. The `CREATE INDEX` statement creates an index on the `salary` column of the `employees` table, which can speed up queries involving the salary column.

ALTER

The `ALTER` command is used to change the structure of an existing database object. It can add, modify, or drop columns in a table, as well as other alterations.

Adding a Column:

ALTER TABLE employees
ADD birth_date DATE;

Modifying a Column:

ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12, 2);

Dropping a Column:

ALTER TABLE employees
DROP COLUMN birth_date;

These examples show how to use the `ALTER TABLE` statement to add a new column (`birth_date`), modify an existing column (`salary`), and drop a column (`birth_date`) from the `employees` table.

DROP

The `DROP` command is used to remove database objects permanently. This command deletes the object and all its data.

Dropping a Table:

DROP TABLE departments;

Dropping an Index:

DROP INDEX idx_salary;

These statements demonstrate how to use the `DROP` command to delete the `departments` table and the `idx_salary` index.

TRUNCATE

The `TRUNCATE` command is used to remove all rows from a table quickly. It is similar to the `DELETE` command without a `WHERE` clause but is faster and uses fewer system and transaction log resources.

Truncating a Table:

TRUNCATE TABLE employees;

This command deletes all rows in the `employees` table, effectively resetting it while retaining its structure.

Data Query Language (DQL) Commands

SELECT

The `SELECT` command is the most commonly used SQL command. It retrieves data from one or more tables and can include various clauses and functions to filter, sort, and aggregate data.

Basic SELECT Statement:

SELECT name, position, salary
FROM employees;

SELECT with WHERE Clause:

SELECT name, position, salary
FROM employees
WHERE salary > 50000;

SELECT with JOIN:

SELECT e.name, e.position, d.name as department
FROM employees e
JOIN departments d ON e.department_id = d.id;

SELECT with Aggregate Function:

SELECT department_id, AVG(salary) as average_salary
FROM employees
GROUP BY department_id;

These examples show how to use the `SELECT` statement to query data from the `employees` table with various conditions and joins.

Data Manipulation Language (DML) Commands

INSERT

The `INSERT` command is used to add new records to a table. It can insert a single row or multiple rows in one statement.

Inserting a Single Row:

INSERT INTO employees (id, name, position, salary, hire_date)
VALUES (2, 'Jane Smith', 'Developer', 60000, '2023-01-15');

Inserting Multiple Rows:

INSERT INTO employees (id, name, position, salary, hire_date)
VALUES 
    (3, 'Alice Johnson', 'Designer', 55000, '2023-02-01'),
    (4, 'Bob Brown', 'Analyst', 50000, '2023-03-01');

These statements illustrate how to use the `INSERT` command to add new records to the `employees` table.

UPDATE

The `UPDATE` command modifies existing records in a table. It can update one or more columns for rows that meet specific conditions.

Updating a Single Column:

UPDATE employees
SET salary = 70000
WHERE id = 2;

Updating Multiple Columns:

UPDATE employees
SET position = 'Senior Developer', salary = 65000
WHERE id = 2;

These examples demonstrate how to use the `UPDATE` command to change the salary and position of an employee in the `employees` table.

DELETE

The `DELETE` command is used to remove existing records from a table. It can delete all rows or specific rows based on a condition.

Deleting Specific Rows:

DELETE FROM employees
WHERE id = 3;

Deleting All Rows:

DELETE FROM employees;

These statements show how to use the `DELETE` command to remove records from the `employees` table.

Data Control Language (DCL) Commands

GRANT

The `GRANT` command is used to provide specific privileges to users or roles. These privileges control what operations the users can perform on the database objects.

Granting Privileges to a User:

GRANT SELECT, INSERT
ON employees
TO john_doe;

Granting Privileges to a Role:

GRANT SELECT, INSERT
ON employees
TO role_employee;

These examples illustrate how to use the `GRANT` command to provide select and insert privileges on the `employees` table to a user and a role.

REVOKE

The `REVOKE` command is used to remove specific privileges from users or roles.

Revoking Privileges from a User:

REVOKE SELECT, INSERT
ON employees
FROM john_doe;

Revoking Privileges from a Role:

REVOKE SELECT, INSERT
ON employees
FROM role_employee;

These statements demonstrate how to use the `REVOKE` command to remove select and insert privileges on the `employees` table from a user and a role.

Transaction Control Language (TCL) Commands

COMMIT

The `COMMIT` command is used to save all changes made in the current transaction. Once a transaction is committed, the changes become permanent.

Committing a Transaction:

UPDATE employees
SET salary = 80000
WHERE id = 2;
COMMIT;

This example shows how to use the `COMMIT` command to save changes made to the `employees` table.

ROLLBACK

The `ROLLBACK` command is used to undo changes made in the current transaction. It reverts the database to the state it was in before the transaction began.

Rolling Back a Transaction:

UPDATE employees
SET salary = 80000
WHERE id = 2;
ROLLBACK;

This example demonstrates how to use the `ROLLBACK` command to undo changes made to the `employees` table.

SAVEPOINT

The `SAVEPOINT` command is used to set a point within a transaction to which you can later roll back.

Creating a Savepoint:

SAVEPOINT sp1;

Rolling Back to a Savepoint:

ROLLBACK TO sp1;

This example illustrates how to use the `SAVEPOINT` command to create a savepoint and then roll back to it.

Important SQL Commands

Some of the most important SQL commands are:

  1. SELECT: Used to retrieve data from a database.
  2. INSERT: Used to add new data to a database.
  3. UPDATE: Used to modify existing data in a database.
  4. DELETE: Used to remove data from a database.
  5. CREATE TABLE: Used to create a new table in a database.
  6. ALTER TABLE: Used to modify the structure of an existing table.
  7. DROP TABLE: Used to delete an entire table from a database.
  8. WHERE: Used to filter rows based on a specified condition.
  9. ORDER BY: Used to sort the result set in ascending or descending order.
  10. JOIN: Used to combine rows from two or more tables based on a related column between them.

Summary Table of SQL Commands

 

Command Description Syntax
CREATE Creates a new database object CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
ALTER Modifies an existing database object ALTER TABLE table_name ADD column_name datatype;
DROP Deletes an existing database object DROP TABLE table_name;
TRUNCATE Removes all records from a table TRUNCATE TABLE table_name;
SELECT Retrieves data from the database SELECT column1, column2, ... FROM table_name WHERE condition;
INSERT Adds new records to a table INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
UPDATE Modifies existing records in a table UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
DELETE Removes existing records from a table DELETE FROM table_name WHERE condition;
GRANT Provides specific privileges to users or roles `GRANT privilege_name ON object_name TO {user_name
REVOKE Removes specific privileges from users or roles `REVOKE privilege_name ON object_name FROM {user_name
COMMIT Saves all changes made in the current transaction COMMIT;
ROLLBACK Undoes changes made in the current transaction ROLLBACK;
SAVEPOINT Sets a point within a transaction to which you can roll back SAVEPOINT savepoint_name;

Conclusion

Understanding SQL commands is crucial for working effectively with databases. DDL, DQL, DML, DCL, and TCL commands each serve specific purposes, from defining and modifying the structure of database objects to managing data and controlling access. By mastering these commands, you can efficiently create, query, update, and manage your database systems. This comprehensive guide provides detailed explanations and examples to help you become proficient in using SQL commands.

You may also like this!