Introduction to Various Types of Queries and Their Uses

Introduction to Various Types of Queries and Their Uses (Database Management Systems) 💻📊

In Database Management Systems (DBMS), queries are used to interact with the database to retrieve, manipulate, update, and delete data. Queries are written in SQL (Structured Query Language), which is the standard language for interacting with relational databases. Below is an introduction to the various types of queries in DBMS and their uses.

1. Data Query Language (DQL) 🔍

DQL is used to retrieve data from a database. The main query in DQL is:

1.1 SELECT Query 📑

• Purpose: The SELECT statement is used to query the database and retrieve data from one or more tables.

• Syntax:

  SELECT column1, column2 FROM table_name WHERE condition;

• Use Cases:
  • Retrieve all records from a table: SELECT * FROM Employees; 🔄
  • Retrieve specific columns: SELECT Name, Age FROM Employees; 🧑‍💼
  • Filter data using conditions: SELECT * FROM Employees WHERE Age > 30; 🎯
  • Combine multiple conditions: SELECT * FROM Employees WHERE Age > 30 AND Department = 'HR'; 💼
  • Sorting data: SELECT * FROM Employees ORDER BY Name ASC; 🔠

2. Data Definition Language (DDL) 🛠️

DDL is used to define or modify the structure of a database and its objects (like tables, indexes, views, etc.).

2.1 CREATE Query 🆕

• Purpose: Used to create new tables, databases, or other database objects.

• Syntax:

  CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
      ...
  );

• Use Case:

  • Create a new table:

    CREATE TABLE Employees (
        ID INT PRIMARY KEY,
        Name VARCHAR(100),
        Age INT,
        Department VARCHAR(50)
    );

    🏗️

2.2 ALTER Query 🔧

• Purpose: Used to modify the structure of an existing table, such as adding, deleting, or modifying columns.

• Syntax:

  ALTER TABLE table_name ADD column_name datatype;
  ALTER TABLE table_name MODIFY column_name datatype;
  ALTER TABLE table_name DROP COLUMN column_name;

• Use Cases:

  • Add a new column to a table:

    ALTER TABLE Employees ADD Salary DECIMAL(10, 2);

    💵

  • Modify an existing column:

    ALTER TABLE Employees MODIFY Age INT;

    ✏️

  • Remove a column:

    ALTER TABLE Employees DROP COLUMN Department;

    ❌

2.3 DROP Query 🗑️

• Purpose: Used to delete a table or database entirely.

• Syntax:

  DROP TABLE table_name;
  DROP DATABASE database_name;

• Use Case:

  • Drop a table:

    DROP TABLE Employees;

    🚮

3. Data Manipulation Language (DML) 🔄

DML is used to manipulate the data within the tables. The main DML queries include:

3.1 INSERT Query ➕

• Purpose: Used to add new rows (records) into a table.

• Syntax:

  INSERT INTO table_name (column1, column2, ...)
  VALUES (value1, value2, ...);

• Use Case:

  • Insert a new record into the Employees table:

    INSERT INTO Employees (ID, Name, Age, Department)
    VALUES (1, 'John Doe', 25, 'HR');

    🎉

3.2 UPDATE Query ✏️

• Purpose: Used to modify existing records in a table.

• Syntax:

  UPDATE table_name
  SET column1 = value1, column2 = value2, ...
  WHERE condition;

• Use Case:

  • Update a specific record in the Employees table:

    UPDATE Employees
    SET Salary = 50000
    WHERE ID = 1;

    💵

3.3 DELETE Query ❌

• Purpose: Used to delete records from a table.

• Syntax:

  DELETE FROM table_name WHERE condition;

• Use Case:

  • Delete a record based on a condition:

    DELETE FROM Employees WHERE ID = 1;

    🗑️

4. Data Control Language (DCL) 🔒

DCL is used to control access to data in the database. It deals with user permissions and security.

4.1 GRANT Query ✅

• Purpose: Used to provide specific privileges to users.

• Syntax:

  GRANT privilege ON object TO user;

• Use Case:

  • Grant a user the ability to select data from a table:

    GRANT SELECT ON Employees TO user_name;

    🔑

4.2 REVOKE Query 🔓

• Purpose: Used to remove specific privileges from users.

• Syntax:

  REVOKE privilege ON object FROM user;

• Use Case:

  • Revoke a user’s select privilege:

    REVOKE SELECT ON Employees FROM user_name;

    🛑

5. Transaction Control Language (TCL) ⏳

TCL is used to manage transactions within the database.

5.1 COMMIT Query 💾

• Purpose: Used to save all changes made in a transaction permanently to the database.

• Syntax:

  COMMIT;

• Use Case:

  • Save changes after an INSERT, UPDATE, or DELETE query:

    COMMIT;

    💾

5.2 ROLLBACK Query 🔙

• Purpose: Used to undo all changes made during the current transaction.

• Syntax:

  ROLLBACK;

• Use Case:

  • Undo changes if a transaction needs to be rolled back:

    ROLLBACK;

    🔄

5.3 SAVEPOINT Query 🔖

• Purpose: Used to set a point within a transaction to which you can later roll back.

• Syntax:

  SAVEPOINT savepoint_name;

• Use Case:

  • Set a savepoint:

    SAVEPOINT sp1;

    💡

5.4 SET TRANSACTION Query ⚙️

• Purpose: Used to set properties for a transaction, such as isolation level.

• Syntax:

  SET TRANSACTION ISOLATION LEVEL level;

• Use Case:

  • Set the isolation level of a transaction:

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    🔒

Conclusion 🎯

In summary, queries in DBMS are essential for interacting with and manipulating the data in databases. The different types of queries serve distinct purposes:

• DQL for data retrieval 🔍
• DDL for defining and modifying database structure 🛠️
• DML for data manipulation 🔄
• DCL for managing access control 🔒
• TCL for transaction management ⏳

Understanding these query types and their appropriate use cases is vital for efficiently managing and interacting with databases. 📚