DBMS and SQL Basics

DBMS and SQL Basics

Almost every software interview has a few DBMS questions, and many ask you to write a SQL query on the spot. The concepts below cover the questions asked most often in campus interviews, with queries you can practise on any MySQL or PostgreSQL database.

1. What is the difference between DBMS and RDBMS?

A DBMS is software for storing and managing data. An RDBMS stores data in related tables with rows and columns, enforces relationships with keys and uses SQL. MySQL, PostgreSQL, Oracle and SQL Server are RDBMSs; most interview questions assume an RDBMS.

2. Explain primary key, foreign key, candidate key and unique key.

A candidate key is any column set that can uniquely identify a row. The primary key is the candidate key chosen as the main identifier; it cannot be NULL. A unique key also prevents duplicates but allows a NULL. A foreign key refers to the primary key of another table and keeps relationships valid.

CREATE TABLE departments (
  id   INT PRIMARY KEY,
  name VARCHAR(50) UNIQUE
);
CREATE TABLE employees (
  id      INT PRIMARY KEY,
  email   VARCHAR(100) UNIQUE,
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(id)
);

3. What is normalisation? Explain 1NF, 2NF and 3NF.

Normalisation organises tables to reduce duplicate data and update anomalies.

• 1NF: each column holds atomic values; no repeating groups.
• 2NF: 1NF, and every non-key column depends on the whole primary key (no partial dependency).
• 3NF: 2NF, and non-key columns depend only on the key, not on other non-key columns (no transitive dependency).

Example: storing a department name in every employee row breaks 3NF; move it to a departments table.

4. What are ACID properties?

Atomicity: a transaction happens completely or not at all. Consistency: it moves the database from one valid state to another. Isolation: concurrent transactions do not interfere. Durability: once committed, changes survive crashes. A bank transfer is the classic example: debit and credit must both happen or neither.

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;   -- or ROLLBACK if anything fails

5. Explain the types of joins.

INNER JOIN returns matching rows from both tables. LEFT JOIN returns all rows from the left table and matches (or NULL) from the right. RIGHT JOIN is the reverse. FULL OUTER JOIN returns all rows from both. A SELF JOIN joins a table to itself, for example employees and their managers.

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id;

6. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes selected rows (with WHERE), is logged row by row and can be rolled back in a transaction. TRUNCATE removes all rows quickly, resets auto-increment and keeps the table structure. DROP removes the whole table, structure included.

7. What is an index? When should you not use one?

An index is a data structure (usually a B-tree) that lets the database find rows without scanning the whole table, like a book index. It speeds up WHERE, JOIN and ORDER BY on indexed columns, but slows down inserts and updates and uses extra space. Avoid indexes on small tables, on columns that are rarely searched and on columns with very few distinct values.

CREATE INDEX idx_employees_dept ON employees(dept_id);

8. Write a query to find the second-highest salary.

This is one of the most asked SQL questions. Two common solutions:

-- Using a subquery
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Using DENSE_RANK (handles ties, works for Nth highest)
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t WHERE rnk = 2;

9. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after GROUP BY and can use aggregate functions.

SELECT dept_id, COUNT(*) AS staff
FROM employees
WHERE active = 1
GROUP BY dept_id
HAVING COUNT(*) > 10;