SQL Interview Questions

SQL (Structured Query Language) statements are categorized into several types based on their functionality. The primary types of SQL statements are:

1. Data Definition Language (DDL)

DDL statements are used to define and manage database structures.

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

  CREATE TABLE employees (
      id INT PRIMARY KEY,
      name VARCHAR(100),
      position VARCHAR(50)
  );

• ALTER: Modifies an existing database object, such as adding a column to a table.

  ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

• DROP: Deletes an existing database object.

  DROP TABLE employees;

• TRUNCATE: Removes all rows from a table without logging individual row deletions.

  TRUNCATE TABLE employees;

2. Data Manipulation Language (DML)

DML statements are used to manipulate data stored in database tables.

• SELECT: Retrieves data from one or more tables.

  SELECT * FROM employees;

• INSERT: Adds new rows to a table.

  INSERT INTO employees (id, name, position) VALUES (1, 'Alice', 'Manager');

• UPDATE: Modifies existing data in a table.

  UPDATE employees SET salary = 75000 WHERE id = 1;

• DELETE: Removes rows from a table.

  DELETE FROM employees WHERE id = 1;

3. Data Control Language (DCL)

DCL statements are used to control access to data within the database.

• GRANT: Gives a user access privileges to the database.

  GRANT SELECT, INSERT ON employees TO user1;

• REVOKE: Removes access privileges from a user.

  REVOKE SELECT, INSERT ON employees FROM user1;

4. Transaction Control Language (TCL)

TCL statements manage changes made by DML statements and ensure data integrity.

• COMMIT: Saves all transactions to the database.

  COMMIT;

• ROLLBACK: Undoes transactions that have not yet been committed.

  ROLLBACK;

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

  SAVEPOINT savepoint1;

• RELEASE SAVEPOINT: Removes a savepoint.

  RELEASE SAVEPOINT savepoint1;

• SET TRANSACTION: Sets the properties for the current transaction.

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

5. Data Query Language (DQL)

DQL is primarily used to query the database. In practical terms, DQL is often considered part of DML, but its primary function is to retrieve data.

• SELECT: Used to fetch data from a database.

  SELECT name, position FROM employees WHERE salary > 50000;

Each of these types of SQL statements plays a critical role in managing and manipulating data within a relational database. Understanding these types and their specific functions is essential for effectively working with SQL databases.