Chapter-1

1. What is a database transaction?

A database transaction is a sequence of one or more SQL operations executed as a single unit. It ensures data integrity by making sure all operations either complete successfully or none take effect. Transactions follow the ACID properties to ensure reliability in database systems.

2. What is Atomicity in transactions?

Atomicity ensures that all the operations within a transaction are completed fully or not at all. If any part of the transaction fails, the entire transaction is rolled back. This prevents the database from ending up in a partial or inconsistent state due to incomplete operations.

3. What is Consistency in transactions?

Consistency ensures that a transaction transforms the database from one valid state to another valid state. It preserves the defined rules, constraints, and integrity of the database. If a transaction violates a constraint, it is rolled back to maintain data validity.

4. What is Isolation in transactions?

Isolation means that the operations of a transaction are separated from others. Each transaction is executed independently, without being affected by other simultaneous transactions. This prevents issues like dirty reads, non-repeatable reads during concurrent execution.

5. What is Durability in transactions?

Durability ensures that once a transaction is committed, its changes are permanent in the database. Even in the case of a system crash or power failure, the results of committed transactions remain intact and will not be lost.

6. What is the purpose of the COMMIT command?

The COMMIT command is used to save all the changes made by the current transaction permanently to the database. Once committed, these changes cannot be undone. It marks the successful end of a transaction and ensures all operations are finalized.

7. What is the ROLLBACK command used for?

ROLLBACK is used to undo changes made by a transaction before it is committed. It reverts the database to the last consistent state. This is useful when errors occur during a transaction and you want to cancel all interim changes.

8. What is a SAVEPOINT in SQL?

A SAVEPOINT is a point set within a transaction to which you can later roll back. It allows partial rollback, meaning only some of the operations in a transaction are undone. This is useful for handling errors in complex transactions.

9. What are the main states of a database transaction?

The main states of a database transaction are Active, Partially Committed, Committed, Failed, and Aborted. These states describe the life cycle of a transaction from the point it starts execution until it either successfully completes or fails and is rolled back.

10. What happens in the Active state of a transaction?

In the Active state, the transaction is currently executing its operations such as reading, writing, or modifying data. It has not yet completed. If an error occurs or the system crashes during this phase, the transaction can move to the Failed or Aborted state.

11. What is the Partially Committed state?

The Partially Committed state is reached when a transaction has completed its final statement but before all changes are permanently saved. At this point, the system performs final checks and writes data to log files. It is a transition point before full commitment.

12. What does the Committed state mean?

In the Committed state, all operations of the transaction are successfully completed and the changes are permanently recorded in the database. Once a transaction is committed, the database guarantees durability, and the changes will persist even if a system crash occurs afterward.

13. What is the Failed state in a transaction?

A transaction enters the Failed state when an error or issue occurs that prevents it from continuing. This might result from a system crash, constraint violation, or deadlock. In this state, none of the transaction’s changes should be visible to other transactions.

14. What does it mean when a transaction is in the Aborted state?

In the Aborted state, a transaction has been rolled back due to failure. All changes made during its execution are undone. After aborting, the transaction may either restart automatically or wait for user intervention. This ensures the database remains consistent.

15. How does a transaction move from Active to Committed?

A transaction moves from Active to Partially Committed once all its operations are complete. If no failure occurs during the final checks, it moves to the Committed state where all changes are made permanent. If an error occurs, it transitions to the Failed state instead.

16. What are group functions in SQL?

Group functions, also known as aggregate functions, perform calculations on a set of values and return a single value as a result. Examples include SUM(), AVG(), MAX(), MIN(), and COUNT(). They are commonly used with the GROUP BY clause to summarize data.

17. What does the SUM() function do in SQL?

The SUM() function calculates the total of all numeric values in a specified column. It is useful when you want to get the total of values, such as total sales, income, or quantities. It only works on numeric data types.

18. What is the purpose of the AVG() function?

The AVG() function returns the average (mean) value of a numeric column. It is commonly used to find averages like average salary, average score, or average price in a dataset. It ignores NULL values in its calculation.

19. How does the COUNT() function work?

The COUNT() function returns the number of rows in a result set. You can use COUNT(*) to count all rows, or COUNT(column_name) to count only non-NULL values in that column. It’s useful for checking data size or frequency.

20. What does the MAX() function return?

The MAX() function returns the highest value in a column. It can be used on numeric, date, or even text data. For example, it can find the maximum salary, the latest order date, or the alphabetically last name in a list.

21. What does the MIN() function return?

The MIN() function returns the smallest value in a column. Like MAX(), it works on numeric, date, or string data. It’s commonly used to find the lowest score, earliest date, or alphabetically first name in a dataset.

22. What is the purpose of the GROUP BY clause in SQL?

The GROUP BY clause groups rows that have the same values in specified columns. It is used with group functions to perform aggregate operations like totals or averages per group. For example, you can calculate total sales per region using GROUP BY.

22. Can we use multiple group functions in a single query?

Yes, you can use multiple group functions in a single SQL query. For example, you can use SUM(), AVG(), and COUNT() together to show total, average, and count of sales in one result, often grouped by a common field.

23. What is the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts all rows in a table, including rows with NULL values. COUNT(column_name) only counts rows where the specified column is not NULL. This distinction is important when dealing with incomplete or optional data fields.

24. What is the purpose of the DISTINCT clause in SQL?

The DISTINCT clause is used to remove duplicate rows from a query result. It ensures that only unique values appear in the output. This is useful when you want to get a list of different values from a column, such as unique customer names or cities.

25. Can the DISTINCT clause be used with multiple columns?

Yes, DISTINCT can be used with multiple columns. In that case, the query returns unique combinations of values across the specified columns. For example, SELECT DISTINCT city, state FROM customers; will return unique pairs of city and state combinations.

26. What is a CROSS JOIN in SQL?

A CROSS JOIN returns the Cartesian product of two tables, meaning each row from the first table is combined with every row from the second table. If table A has 3 rows and table B has 4, the result will have 3 × 4 = 12 rows.

27. When should a CROSS JOIN be used?

A CROSS JOIN is used when every combination of rows from two tables is needed. It’s useful for generating test data or pairing all items from one table with items from another. It should be used carefully, as it can generate large result sets.

28. What is an EQUI JOIN in SQL?

An EQUI JOIN is a type of join where tables are joined using a condition with an equality operator (=). For example, SELECT * FROM employees JOIN departments ON employees.dept_id = departments.dept_id; is an equi join linking rows based on a common key.

29. How is EQUI JOIN different from INNER JOIN?

An EQUI JOIN uses the equality operator to match rows, which is a specific case of an INNER JOIN. In fact, most INNER JOIN operations are equi joins. However, inner joins can also include other conditions or inequalities in the ON clause.

30. What is the purpose of the UNION operator in SQL?

The UNION operator is used to combine the results of two or more SELECT queries into a single result set. It automatically removes duplicate rows unless UNION ALL is used. All queries must have the same number and type of columns.

31. What is the difference between UNION and UNION ALL?

UNION removes duplicate rows from the result set, returning only distinct records. UNION ALL, on the other hand, includes all rows, even if they are duplicates. UNION ALL is faster because it doesn’t perform the extra step of removing duplicates.

32. Can we use ORDER BY with UNION?

Yes, you can use ORDER BY with UNION, but it must be applied after the final SELECT statement, not within individual queries. For example: SELECT name FROM employees UNION SELECT name FROM managers ORDER BY name;

33. What are the requirements for using UNION in SQL?

To use UNION, all participating SELECT queries must return the same number of columns, and the corresponding columns must have compatible data types. The column names in the final result are usually taken from the first SELECT statement.