SQL Commands

SQL Commands Interview with follow-up questions

1. What are the different types of SQL commands?

SQL commands are divided into five categories:

1. DDL — Data Definition Language Defines and modifies the database schema (structure).

  • CREATE — create tables, views, indexes, schemas
  • ALTER — modify existing objects (add/drop columns, change types)
  • DROP — permanently delete objects
  • TRUNCATE — remove all rows from a table (keeps structure)
  • RENAME — rename objects

2. DML — Data Manipulation Language Reads and modifies the actual data.

  • SELECT — retrieve rows
  • INSERT — add new rows
  • UPDATE — modify existing rows
  • DELETE — remove rows
  • MERGE — upsert (insert or update based on a condition)

3. DCL — Data Control Language Manages user permissions.

  • GRANT — give privileges to users/roles
  • REVOKE — remove privileges

4. TCL — Transaction Control Language Controls transaction boundaries.

  • COMMIT — save transaction changes permanently
  • ROLLBACK — undo changes since the last commit
  • SAVEPOINT — create a named rollback point within a transaction
  • SET TRANSACTION — set isolation level

5. DQL — Data Query Language (sometimes treated as a subset of DML)

  • SELECT — some references classify this separately

Common interview gotcha: TRUNCATE is DDL (not DML), so in most databases it cannot be rolled back once executed, unlike DELETE.

↑ Back to top

Follow-up 1

What are some examples of DCL commands?

DCL stands for Data Control Language. It is used to control the access and permissions of database users. DCL commands are used to grant or revoke privileges to users. Examples of DCL commands include GRANT and REVOKE.

Follow-up 2

Can you explain what DDL is?

DDL stands for Data Definition Language. It is used to define and manage the structure of a database. DDL commands are used to create, alter, and drop database objects such as tables, indexes, and views. Examples of DDL commands include CREATE TABLE, ALTER TABLE, and DROP TABLE.

Follow-up 3

What is the purpose of DML commands?

DML stands for Data Manipulation Language. It is used to manipulate the data stored in a database. DML commands are used to insert, update, and delete data in database tables. Examples of DML commands include INSERT, UPDATE, and DELETE.

Follow-up 4

How does TCL work in SQL?

TCL stands for Transaction Control Language. It is used to manage the changes made by DML statements. TCL commands are used to control the transactions in a database, such as committing or rolling back changes. Examples of TCL commands include COMMIT and ROLLBACK.

2. How would you use the SELECT command in SQL?

The SELECT statement retrieves data from one or more tables. Its full clause order is:

SELECT [DISTINCT] column1, column2, expression AS alias
FROM table_name
[JOIN other_table ON condition]
[WHERE filter_condition]
[GROUP BY column1]
[HAVING group_filter]
[ORDER BY column1 [ASC|DESC]]
[LIMIT n OFFSET m];

Common patterns:

Select all columns:

SELECT * FROM employees;

Filter rows:

SELECT first_name, salary FROM employees WHERE department = 'Engineering';

Aggregate with grouping:

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

Join two tables:

SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Logical execution order (not writing order — this is a common interview question): FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

This is why a SELECT alias cannot be referenced in a WHERE clause — the alias is assigned in the SELECT phase, which happens after WHERE.

Best practices: Avoid SELECT * in production code — it pulls unnecessary columns, breaks if the schema changes, and prevents index-only scans.

↑ Back to top

Follow-up 1

Can you explain the syntax of the SELECT command?

The syntax of the SELECT command in SQL is as follows:

SELECT column1, column2, ... FROM table_name WHERE condition;
  • SELECT: keyword used to indicate that you want to retrieve data from a table
  • column1, column2, ...: the columns you want to select from the table. You can specify multiple columns separated by commas, or use the asterisk (*) to select all columns
  • FROM: keyword used to specify the table from which you want to retrieve data
  • table_name: the name of the table from which you want to retrieve data
  • WHERE: keyword used to specify a condition for selecting data. This is optional
  • condition: the condition that must be met for a row to be selected. This is optional

For example, to select the 'name' and 'age' columns from a table named 'students', you can use the following query:

SELECT name, age FROM students;

This will return the 'name' and 'age' columns for all rows in the 'students' table.

Follow-up 2

What happens if you use SELECT without any condition?

If you use SELECT without any condition, it will retrieve all rows from the specified table. This means that the query will return all data in the table, regardless of any specific conditions.

For example, if you use the following query:

SELECT * FROM employees;

It will return all rows and columns from the 'employees' table.

Follow-up 3

How can you use SELECT with the WHERE clause?

You can use the WHERE clause in conjunction with the SELECT command to specify a condition for selecting data from a table. The WHERE clause allows you to filter the rows based on a specific condition.

The syntax for using SELECT with the WHERE clause is as follows:

SELECT column1, column2, ... FROM table_name WHERE condition;
  • SELECT: keyword used to indicate that you want to retrieve data from a table
  • column1, column2, ...: the columns you want to select from the table. You can specify multiple columns separated by commas, or use the asterisk (*) to select all columns
  • FROM: keyword used to specify the table from which you want to retrieve data
  • table_name: the name of the table from which you want to retrieve data
  • WHERE: keyword used to specify a condition for selecting data
  • condition: the condition that must be met for a row to be selected

For example, to select the 'name' and 'age' columns from a table named 'students' where the age is greater than 18, you can use the following query:

SELECT name, age FROM students WHERE age > 18;

This will return the 'name' and 'age' columns for all rows in the 'students' table where the age is greater than 18.

3. What is the purpose of the UPDATE command in SQL?

The UPDATE statement modifies existing rows in a table. The WHERE clause is critical — omitting it updates every row in the table.

Basic syntax:

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

Examples:

Update a single row:

UPDATE employees
SET salary = 95000
WHERE employee_id = 42;

Update multiple columns for multiple rows:

UPDATE products
SET price = price * 1.10, last_updated = CURRENT_DATE
WHERE category = 'Electronics';

Update using a subquery:

UPDATE employees
SET department_id = (SELECT id FROM departments WHERE name = 'Marketing')
WHERE employee_id = 7;

Key behaviors to know for interviews:

  • UPDATE is a DML statement — changes can be rolled back if inside a transaction
  • Without a WHERE clause, all rows are updated (a common and costly mistake)
  • Most databases support UPDATE ... FROM (SQL Server, PostgreSQL) or UPDATE ... JOIN (MySQL) to update rows based on values from another table
  • RETURNING clause (PostgreSQL) lets you see the updated values without a separate SELECT

Common follow-up: How do you update rows in one table based on values in another table? This tests knowledge of JOINs in UPDATE statements or correlated subqueries.

↑ Back to top

Follow-up 1

Can you show an example of how to use the UPDATE command?

Sure! Here's an example of how to use the UPDATE command in SQL:

UPDATE employees
SET salary = 50000
WHERE department = 'Sales';

This example updates the salary column of all employees in the employees table whose department is 'Sales' and sets their salary to 50000.

Follow-up 2

What precautions should you take when using the UPDATE command?

When using the UPDATE command in SQL, it is important to take the following precautions:

  1. Always use the WHERE clause to specify which rows should be updated. Without a WHERE clause, the UPDATE command will modify all rows in the table.
  2. Double-check the conditions in the WHERE clause to ensure that you are updating the correct rows.
  3. Make sure to backup your database before performing any updates, especially if you are making significant changes to the data.
  4. Test your UPDATE statements on a smaller subset of data before running them on the entire table to avoid unintended consequences.

Follow-up 3

How can you use UPDATE with the WHERE clause?

To use the UPDATE command with the WHERE clause, you need to specify the condition that determines which rows should be updated. Here's an example:

UPDATE customers
SET status = 'Inactive'
WHERE last_purchase_date < '2020-01-01';

This example updates the status column of all customers in the customers table whose last_purchase_date is earlier than '2020-01-01' and sets their status to 'Inactive'. Only the rows that meet the specified condition will be updated.

4. How does the DELETE command work in SQL?

The DELETE statement removes rows from a table that match a WHERE condition. Without WHERE, it deletes all rows.

Basic syntax:

DELETE FROM table_name
WHERE condition;

Examples:

Delete a specific row:

DELETE FROM orders WHERE order_id = 101;

Delete based on a subquery:

DELETE FROM sessions
WHERE user_id IN (SELECT id FROM users WHERE account_status = 'suspended');

DELETE vs TRUNCATE — a key interview comparison:

Aspect DELETE TRUNCATE
Category DML DDL
Rollback Yes (inside a transaction) Not in most databases (MySQL InnoDB is an exception)
Speed Slower (logs each row) Much faster (minimal logging)
WHERE clause Supported Not supported
Triggers Fires row-level triggers Does not fire row-level triggers
Resets AUTO_INCREMENT No Yes (in MySQL)

CASCADE behavior: If a foreign key constraint has ON DELETE CASCADE, deleting a parent row automatically deletes related child rows. If it has ON DELETE RESTRICT (or no action), the delete is blocked if child rows exist.

Best practice: Always run a SELECT with the same WHERE clause first to verify which rows will be deleted before executing DELETE.

↑ Back to top

Follow-up 1

Can you explain the syntax of the DELETE command?

The syntax of the DELETE command in SQL is as follows:

DELETE FROM table_name
WHERE condition;
  • DELETE FROM is the keyword used to indicate that you want to delete data from a table.
  • table_name is the name of the table from which you want to delete data.
  • WHERE is an optional keyword that allows you to specify conditions for the deletion. If you omit the WHERE clause, all rows in the table will be deleted.
  • condition is the condition that must be met for a row to be deleted. It can include one or more conditions combined using logical operators such as AND and OR.

Follow-up 2

What happens if you use DELETE without any condition?

If you use the DELETE command without any condition, it will delete all rows from the specified table. This means that the entire table will be emptied and all data will be permanently lost. Therefore, it is important to be cautious when using the DELETE command without any condition, especially on tables that contain important data.

Follow-up 3

How can you use DELETE with the WHERE clause?

You can use the DELETE command with the WHERE clause to delete specific rows from a table based on specified conditions. The WHERE clause allows you to specify one or more conditions that must be met for a row to be deleted. Only the rows that match the specified conditions will be deleted, while the rest of the rows will remain unaffected.

Here is an example of using DELETE with the WHERE clause:

DELETE FROM customers
WHERE age > 50;

This example deletes all rows from the 'customers' table where the 'age' column is greater than 50.

5. What is the difference between the DROP command and the TRUNCATE command?

Both commands remove data from a database, but they differ significantly in scope, behavior, and recoverability.

Aspect DROP TRUNCATE
What is removed Entire table (structure + data + indexes + constraints + triggers) All rows in the table (structure preserved)
DDL category Yes — object is permanently deleted Yes — but schema stays intact
Rollback Generally not (DDL auto-commits in most databases) Not rollback-able in most databases (MySQL InnoDB is an exception)
Speed Fast Very fast (deallocates data pages, minimal row-by-row logging)
WHERE clause Not applicable Not supported
Resets identity/auto-increment N/A (table is gone) Yes — resets the counter
Triggers No DML triggers fire No row-level triggers fire
Foreign key constraints Will fail if another table references this one Will fail if referenced by a foreign key

DROP example:

DROP TABLE employees;  -- table is gone, cannot SELECT from it

TRUNCATE example:

TRUNCATE TABLE session_logs;  -- all rows gone, table still exists

Third comparison — DELETE: DELETE is DML, is fully logged, fires triggers, supports WHERE, and is always rollback-able. Use TRUNCATE when you need to clear a table fast; use DELETE when you need filtering or rollback.

Common interview gotcha: Asking which one can be rolled back. In PostgreSQL, TRUNCATE is transactional and can be rolled back. In MySQL (outside of InnoDB), it typically cannot.

↑ Back to top

Follow-up 1

Can you explain the syntax of the DROP and TRUNCATE commands?

The syntax for the DROP command is as follows:

DROP TABLE table_name;

The syntax for the TRUNCATE command is as follows:

TRUNCATE TABLE table_name;

Follow-up 2

In what scenarios would you use DROP instead of TRUNCATE?

You would use the DROP command instead of TRUNCATE in scenarios where you want to completely remove a table from the database, including all of its data and structure. This can be useful when you no longer need the table or when you want to recreate the table with a different structure.

Follow-up 3

What happens to the data when you use the DROP command?

When you use the DROP command, all data in the table is permanently deleted. The table and all associated objects such as indexes, constraints, and triggers are also removed from the database. This action cannot be undone, so it is important to use the DROP command with caution.

Follow-up 4

What happens to the data when you use the TRUNCATE command?

When you use the TRUNCATE command, all data in the table is removed, but the table structure and associated objects such as indexes, constraints, and triggers remain intact. The table is essentially emptied, but it can still be used to insert new data. Unlike the DROP command, the TRUNCATE command can be undone by inserting new data into the table.

Live mock interview

Mock interview: SQL Commands

Intermediate ~5 min Your own free AI key

Your voice and your AI key never touch our servers; the key stays in this browser and is sent only to Google. Only your round scores are saved to track progress.