DBMS & SQL interview questions

Preview Questions

1. If two different transactions read the same data item but modify different columns, can there still be a concurrency problem? Explain with an example.

   Yes, a concurrency problem can still occur even if two transactions modify different columns of the same row. This happens because both transactions read the same data at the same time and then update their respective...

2. What’s the difference between lossless decomposition and dependency preservation? Can you achieve one without the other?

   Lossless decomposition and dependency preservation are two important properties in database normalization, but they serve different purposes. * Lossless decomposition ensures that when a relation is divided into two or...

3. What are all the concurrency problems faced when multiple transactions run together?

   The main concurrency problems in transactions occur when multiple transactions execute at the same time and try to access or modify the same data without proper control. These problems can lead to inconsistent or...

4. How do you identify slow running queries?

   You can identify slow-running queries using the EXPLAIN and EXPLAIN ANALYZE commands provided by most relational databases like PostgreSQL and MySQL. The EXPLAIN command shows the query execution plan — that is, how the...

5. How is data independence achieved in dbms?

   Data independence in DBMS means that changes made in one level of the database do not affect other levels. It allows the structure of data to be modified without requiring changes to the application programs that use...

6. What happens if you delete a record that’s referenced as a foreign key in another table but with a CASCADE NULL rule?

   If you delete a record that is referenced as a foreign key in another table and the foreign key constraint has a CASCADE NULL rule (also known as ON DELETE SET NULL), the related foreign key values in the child table...

7. What is the difference between database schema and instance?

   The schema and instance are two key concepts in a database that describe its structure and state at different points in time. Schema refers to the overall logical structure or blueprint of the database. It defines how...

8. Explain the concept of a stored procedure in DBMS.

   A stored procedure in DBMS is a precompiled set of SQL statements that are stored and executed on the database server. Instead of writing the same SQL queries again and again in an application, you can save them as a...