Question 1

What is SQL?

Accepted Answer

SQL stands for Structured Query Language. It is a programming language used for managing and manipulating relational databases. SQL is the international standard language used to communicate with relational database management systems (RDBMS) like PostgreSQL, MySQL, SQL Server, and Oracle. It allows users to create database structures, query data, insert/update/delete records, and control access permissions.

Question 2

What is a database?

Accepted Answer

A database is an organized collection of data stored and accessed electronically. It provides a way to store, organize, and retrieve large amounts of data efficiently. A database is a central repository designed to store structured, semi-structured, or unstructured datasets. Databases are optimized for fast searching, concurrent multi-user reading, secure access controls, and data integrity guarantees (like transaction rollbacks).

Question 3

What is a primary key?

Accepted Answer

A primary key is a column or combination of columns that uniquely identifies each row in a table. It enforces the entity integrity rule in a relational database. A primary key enforces unique identification for every record in a table. It must contain completely unique values and cannot contain NULL values. Relational databases automatically index the primary key to optimize query speeds when looking up rows.

Question 4

What is a foreign key?

Accepted Answer

A foreign key is a column or combination of columns that establishes a link between data in two tables. It ensures referential integrity by enforcing relationships between tables. A foreign key points to the primary key of another table, creating a relational link. This link enforces referential integrity, meaning the database blocks users from adding record values to the child table that do not exist in the parent table, or deleting parent rows that are actively referenced.

Question 5

What is the difference between a primary key and a unique key?

Accepted Answer

A primary key is used to uniquely identify a row in a table and must have a unique value. On the other hand, a unique key ensures that a column or combination of columns has a unique value but does not necessarily identify the row. While both primary keys and unique keys enforce uniqueness, they have key differences. A table can have ONLY ONE primary key, and it cannot accept any NULL values. A table can have MULTIPLE unique keys, and depending on the database system (like PostgreSQL/Oracle), unique keys can accept NULL values (usually only one NULL is allowed, or multiple nulls depending on standards).

Question 6

What is normalization?

Accepted Answer

Normalization is the process of organizing data in a database to minimize redundancy and dependency. It involves breaking down a table into smaller tables and establishing relationships between them. Normalization is a systematic database design practice. The goal is to eliminate data redundancies (which waste storage and create update anomalies) and ensure data is stored logically. It divides massive flat tables into smaller, isolated tables connected by foreign key relations.

Question 7

What are the different types of normalization?

Accepted Answer

The different types of normalization are: First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Boyce-Codd Normal Form (BCNF) Fourth Normal Form (4NF) Fifth Normal Form (5NF) or Project-Join Normal Form (PJNF) Each normalization stage (normal form) has specific rules. **1NF** requires atomic values (no repeating groups in a column). **2NF** requires being in 1NF and having all non-key attributes fully dependent on the primary key. **3NF** requires being in 2NF and having no transitive dependencies (non-key columns depending on other non-key columns). **BCNF** is a stronger version of 3NF.

Question 8

What is a join in SQL?

Accepted Answer

A join is an operation used to combine rows from two or more tables based on related columns. It allows you to retrieve data from multiple tables simultaneously. In normalized databases, related data is stored in separate tables. To retrieve comprehensive datasets, we use JOIN commands to link tables based on common matching columns (typically foreign keys matching primary keys).

Question 9

What is the difference between DELETE and TRUNCATE in SQL?

Accepted Answer

The DELETE statement is used to remove specific rows from a table based on a condition. It can be rolled back and generates individual delete operations for each row. TRUNCATE, on the other hand, is used to remove all rows from a table. It cannot be rolled back, and it is faster than DELETE as it deallocates the data pages instead of logging individual row deletions. DELETE is a DML (Data Manipulation) command that removes rows based on a WHERE filter, logging each row removal, which makes it slower but safer. TRUNCATE is a DDL (Data Definition) command that empties a table completely by deallocating data pages, logging only the deallocation, which makes it fast but blocks row rollbacks in some engines.

Question 10

What is the difference between UNION and UNION ALL?

Accepted Answer

UNION and UNION ALL are used to combine the result sets of two or more SELECT statements. UNION removes duplicate rows from the combined result set, whereas UNION ALL includes all rows, including duplicates. Both commands combine rows from multiple queries. However, **UNION** performs a distinct-sorting operation to identify and remove duplicate rows from the combined dataset, which consumes memory and slows down execution. **UNION ALL** appends the datasets directly, making it much faster.

Question 11

What is the difference between the HAVING clause and the WHERE clause?

Accepted Answer

The WHERE clause is used to filter rows based on a condition before the data is grouped or aggregated. It operates on individual rows. The HAVING clause, on the other hand, is used to filter grouped rows based on a condition after the data is grouped or aggregated using the GROUP BY clause. WHERE and HAVING filter data at different stages. The **WHERE** clause inspects individual rows before grouping and cannot contain aggregate functions (like SUM or COUNT). The **HAVING** clause is executed after GROUP BY and is used specifically to filter aggregated group values.

Question 12

What is a transaction in SQL?

Accepted Answer

A transaction is a sequence of SQL statements that are executed as a single logical unit of work. It ensures data consistency and integrity by either committing all changes or rolling them back if an error occurs. Transactions are used to bundle multiple related database commands. If all commands succeed, we run **COMMIT** to persist the changes permanently. If any command fails, we run **ROLLBACK** to restore the database to its pre-transaction state, keeping the database in a clean, consistent state.

Question 13

What is the difference between a clustered and a non-clustered index?

Accepted Answer

A clustered index determines the physical order of data in a table. It changes the way the data is stored on disk and can be created on only one column. A table can have only one clustered index. A non-clustered index does not affect the physical order of data in a table. It is stored separately and contains a pointer to the actual data. A table can have multiple non-clustered indexes. A **Clustered Index** physically sorts the table rows based on the indexed column (typically the Primary Key). Since data can only be physically sorted one way, a table can have only one clustered index. A **Non-Clustered Index** is a separate index table that stores the indexed column values alongside pointers to the actual table rows, allowing multiple non-clustered indexes to optimize different search fields.

Question 14

What is ACID in the context of database transactions?

Accepted Answer

ACID stands for Atomicity, Consistency, Isolation, and Durability. It is a set of properties that guarantee reliable processing of database transactions. Atomicity ensures that a transaction is treated as a single unit of work, either all or none of the changes are applied. Consistency ensures that a transaction brings the database from one valid state to another. Isolation ensures that concurrent transactions do not interfere with each other. Durability ensures that once a transaction is committed, its changes are permanent and survive system failures. ACID guarantees that relational database transactions process reliably: 
- **Atomicity**: The 'all-or-nothing' rule.
- **Consistency**: Enforces schema rules and triggers.
- **Isolation**: Prevents concurrent updates from corrupting states.
- **Durability**: Ensures committed results survive crashes by writing to transactional redo logs on disk.

Question 15

What is a deadlock?

Accepted Answer

A deadlock occurs when two or more transactions are waiting for each other to release resources, resulting in a circular dependency. As a result, none of the transactions can proceed, and the system may become unresponsive. A deadlock happens when Transaction A holds a lock on Row 1 and requests a lock on Row 2, while Transaction B holds a lock on Row 2 and requests a lock on Row 1. Neither transaction can proceed, creating a circular block. Relational database engines continuously run deadlock detection algorithms, automatically terminating and rolling back one of the transactions (the 'deadlock victim') to break the circle.

SQL Queries & Backend Interview Prep Portal

What is SQL?

What is a database?

What is a primary key?

What is a foreign key?

What is the difference between a primary key and a unique key?

What is normalization?

What are the different types of normalization?

What is a join in SQL?

What is the difference between DELETE and TRUNCATE in SQL?

What is the difference between UNION and UNION ALL?

What is the difference between the HAVING clause and the WHERE clause?

What is a transaction in SQL?

What is the difference between a clustered and a non-clustered index?

What is ACID in the context of database transactions?

What is a deadlock?

What is the difference between a database and a schema?

What is the difference between a temporary table and a table variable?

What is the purpose of the GROUP BY clause?

What is the difference between CHAR and VARCHAR data types?

What is a stored procedure?

What is a subquery?

What is a view?

What is the difference between a cross join and an inner join?

What is the purpose of the COMMIT statement?

What is the purpose of the ROLLBACK statement?

What is the purpose of the NULL value in SQL?

What is the difference between a view and a materialized view?

What is a correlated subquery?

What is the purpose of the DISTINCT keyword?

What is the difference between the CHAR and VARCHAR data types?

What is the difference between the IN and EXISTS operators?

What is the purpose of the TRIGGER statement?

What is the difference between a unique constraint and a unique index?

What is the purpose of the TOP or LIMIT clause?

What is the difference between the UNION and JOIN operators?

What is a data warehouse?

What is the difference between a primary key and a candidate key?

What is the purpose of the GRANT statement?

What is a correlated update?

What is the purpose of the CASE statement?

What is the purpose of the COALESCE function?

What is the purpose of the ROW_NUMBER() function?

What is the difference between a natural join and an inner join?

What is the purpose of the CASCADE DELETE constraint?

What is the purpose of the ALL keyword in SQL?

What is the difference between the EXISTS and NOT EXISTS operators?

What is the purpose of the CROSS APPLY operator?

What is a self-join?

What is an ALIAS command?

Why are SQL functions used?

How do you identify duplicate rows in a database table that lacks primary keys or unique constraints?

How do you delete duplicate rows from a database table while preserving the record with the lowest ID?

Write a SQL query using LEFT JOIN to identify customers who have registered in the database but have never completed a transaction.

Write a SQL query using LEAD() or LAG() window functions to calculate the time difference in minutes between consecutive transactions completed by the same customer.

Write a SQL query using JOINs to verify that a customer's checking balance decreased and their savings balance increased by the exact transaction amount after a Transfer event.

Write a SQL query using window functions (like DENSE_RANK or ROW_NUMBER) to identify the top 3 largest transactions completed by every single customer in the last 30 days.

Write a SQL query using GROUP BY and HAVING to identify e-commerce accounts that have performed more than 5 checkout transactions within a single hour, which may indicate automated bot attacks or double-billing failures.

How do you write a SQL query to verify database referential integrity by identifying orphaned child rows that do not map to active parent records?

SQL Queries & Backend
Interview Prep Portal