SQL most important command for interview

1. Data Query Language (DQL)

These commands are used to retrieve data from the database.

• SELECT: Retrieves data from one or more tables.
  • Example: “Get all records from the employees table.”
    • SELECT * FROM employees;
  • Example: “Get names and salaries of employees who earn more than $50,000.”
    • SELECT name, salary FROM employees WHERE salary > 50000;

2. Data Definition Language (DDL)

These commands are used to define and modify database structures like tables, views, and indexes.

• CREATE: Creates a new table, database, or other objects.

  • Example: “Create a new table called employees.”
    • CREATE TABLE employees (id INT, name VARCHAR(50), salary DECIMAL(10, 2));

• ALTER: Modifies an existing database object (like a table or column).

  • Example: “Add a new column called hire_date to the employees table.”
    • ALTER TABLE employees ADD hire_date DATE;

• DROP: Deletes a table, view, or other database objects.

  • Example: “Delete the employees table.”
    • DROP TABLE employees;

• TRUNCATE: Deletes all rows from a table, but keeps the table structure.

  • Example: “Remove all records from the employees table.”
    • TRUNCATE TABLE employees;

3. Data Manipulation Language (DML)

These commands are used to insert, update, and delete data in the tables.

• INSERT: Adds new records (rows) to a table.

  • Example: “Add a new employee to the employees table.”
    • INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 60000);

• UPDATE: Modifies existing records in a table.

  • Example: “Increase the salary of employee with ID 1.”
    • UPDATE employees SET salary = 65000 WHERE id = 1;

• DELETE: Removes records from a table.

  • Example: “Delete employee with ID 1 from the employees table.”
    • DELETE FROM employees WHERE id = 1;

4. Data Control Language (DCL)

These commands are used to control permissions and access to the database.

• GRANT: Provides specific privileges to a user or role.

  • Example: “Grant the SELECT privilege to user john on the employees table.”
    • GRANT SELECT ON employees TO john;

• REVOKE: Removes privileges from a user or role.

  • Example: “Revoke the SELECT privilege from user john on the employees table.”
    • REVOKE SELECT ON employees FROM john;

5. Transaction Control Language (TCL)

These commands are used to manage transactions, ensuring consistency and reliability.

• COMMIT: Saves the changes made during a transaction.

  • Example: “Commit all changes made in the current transaction.”
    • COMMIT;

• ROLLBACK: Reverts all changes made during the current transaction.

  • Example: “If something goes wrong, roll back the changes.”
    • ROLLBACK;

• SAVEPOINT: Sets a point within a transaction to which you can later roll back.

  • Example: “Create a savepoint in the transaction.”
    • SAVEPOINT sp1;

• SET TRANSACTION: Configures properties like transaction isolation levels.

  • Example: “Set the transaction isolation level to SERIALIZABLE.”
    • SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;