notes-shivam

SQL - DDL, DQL and DML

4 min read

  1. SQL (Structured Query Language) is a domain-specific programming language used for managing and manipulating relational databases.
  2. It provides a standardized syntax for interacting with databases, enabling users to perform various operations such as creating, modifying, and querying data.
  3. SQL is widely used in the field of database management systems (DBMS) and is supported by most relational database management systems, including MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.

To understand SQL comprehensively, it’s essential to delve into its various components and types of commands, such as DDL (Data Definition Language), DML (Data Manipulation Language), and DQL (Data Query Language). Each of these components serves a specific purpose in the management and manipulation of data within a database system.

SQL Overview:

SQL serves as a powerful tool for working with relational databases. It allows users to perform a wide range of tasks, including:

  • Creating and modifying database schemas (DDL)
  • Inserting, updating, and deleting data (DML)
  • Querying data from tables (DQL)
  • Managing user access and permissions (DCL - Data Control Language)

Data Definition Language (DDL):

DDL comprises a set of SQL commands used for defining the structure and schema of a database. It allows users to create, modify, and drop database objects such as tables, indexes, views, and constraints. DDL commands are essential for setting up the foundation of a database.

  1. CREATE: The CREATE command is used to create new database objects such as tables, indexes, views, etc. It specifies the structure of the object and any constraints associated with it.

    Example:

    CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT
);

  2. ALTER: The ALTER command is used to modify the structure of existing database objects. It allows users to add, modify, or drop columns, constraints, indexes, etc., from a table.

    Example:

    ALTER TABLE Employees
ADD Email VARCHAR(100);

  3. DROP: The DROP command is used to delete existing database objects such as tables, indexes, views, etc. It permanently removes the specified object from the database.

    Example:

    DROP TABLE Employees;

  4. TRUNCATE: The TRUNCATE command is used to remove all records from a table, while keeping the table structure intact. Unlike DROP TABLE, it preserves the table’s schema.

    Example:

    TRUNCATE TABLE Employees;

Data Query Language (DQL):

DQL is a subset of SQL focused on querying data from the database. The primary command used in DQL is SELECT, which retrieves data from one or more tables based on specified criteria. DQL does not manipulate the data; it only retrieves it.

  1. SELECT: The SELECT statement is used to retrieve data from one or more tables in a database. It allows users to specify the columns to retrieve, filter rows based on conditions, sort the result set, and perform various aggregate functions.

    Example:

    SELECT FirstName, LastName FROM Employees WHERE Age > 30;

    This query retrieves the first name and last name of employees who are older than 30 years.

Data Manipulation Language (DML):

DML consists of SQL commands used for manipulating data stored in the database. It allows users to insert, update, and delete records within tables.

  1. INSERT: The INSERT statement is used to add new records into a table. It specifies the columns and values to be inserted into the table.

    Example:

    INSERT INTO Employees (EmployeeID, FirstName, LastName, Age) VALUES (1, 'John', 'Doe', 35);

    This query inserts a new employee record into the Employees table with the specified values.

  2. UPDATE: The UPDATE statement is used to modify existing records in a table. It allows users to change the values of specific columns based on specified conditions.

    Example:

    UPDATE Employees SET Age = 36 WHERE EmployeeID = 1;

    This query updates the Age column for the employee with EmployeeID 1 to 36.

  3. DELETE: The DELETE statement is used to remove records from a table based on specified conditions. It permanently deletes the selected rows from the table.

    Example:

    DELETE FROM Employees WHERE EmployeeID = 1;

    This query deletes the employee record with EmployeeID 1 from the Employees table.

Conclusion:

SQL is a powerful language for managing relational databases, providing a standardized approach to database operations. DDL commands are used for defining the structure and schema of a database, DQL commands are used for querying data from tables, and DML commands are used for manipulating data within tables. By understanding and mastering these SQL components, users can effectively manage and manipulate data in their relational database systems.****

Graph View

Backlinks