Database Interview Questions

1.what is index

A database index is a performance optimization tool that allows the database to find rows faster when you query by specific columns.

CREATE INDEX idx_users_email ON users(email);

idx_users_email is the name of the index.

It improves performance for queries filtering by email.

📈 Types of Indexes

Trade-offs

2. what are keys in database

In a database, keys are columns or combinations of columns that are used to identify, link, or enforce rules on data. They’re essential for ensuring data integrity, optimizing performance, and enabling relationships between tables.

1. Primary Key

• A unique identifier for each row in a table.
• Cannot be NULL or duplicated.
• Every table should have one primary key.

2. Foreign Key

• A column in one table that references the primary key in another table.
• Used to create relationships between tables.

3. Unique Key

• Enforces that all values in the column are distinct.
• Like a primary key, but a table can have multiple unique keys.

4. Composite Key

• A key made up of multiple columns.
• Uniquely identifies a row using the combination of values.

5. Candidate Key

• A column (or set of columns) that can uniquely identify a row.
• The best candidate is chosen as the primary key.
• Others can be considered as alternate keys.

6. Super Key

• Any set of columns that can uniquely identify a row.
• All candidate keys are super keys, but not all super keys are candidate keys.

7. Index Key (Not a constraint, but performance-related)

• Columns used in an index to speed up searching.
• Often overlaps with primary/foreign/unique keys.

3. What is diff between MongoDB and MySQL

✅ Choose MongoDB When:

• You want fast prototyping or flexible schema
• You deal with JSON or nested data
• You’re building real-time analytics, IoT, or document storage

✅ Choose MySQL When:

• You need complex queries, joins, and constraints
• You require strict schema and relationships

4.What is Left Join?

• Returns all rows from the left table (table A),
• And the matching rows from the right table (table B).
• If no match is found in the right table, the result will contain NULL for right table’s columns.

Syntax:

SELECT A.id, A.name, B.order_id

FROM Customers A

LEFT JOIN Orders B

ON A.id = B.customer_id;

5. What is Right Join?

• Returns all rows from the right table (table B),
• And the matching rows from the left table (table A).
• If no match is found in the left table, the result will contain NULL for left table’s columns.

Syntax:

SELECT A.id, A.name, B.order_id

FROM Customers A

RIGHT JOIN Orders B

ON A.id = B.customer_id;

6. I have Employee table and below column emp_id, dept_id, salary and i want to find 4 employee maximum salry from multiple departments.give sql query for that.

SELECT emp_id, dept_id, salary

FROM (

SELECT

emp_id,

dept_id,

salary,

ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn

FROM Employee

) AS ranked

WHERE rn <= 4;

7.Diff Between Having vs Where and Primary Key vs Unique key?

WHERE vs HAVING

👉 WHERE: filters before grouping
👉 HAVING: filters after GROUP BY

1. Primary Key vs Unique Key

8. Primary key vs Unique key?

• Primary key: unique + NOT NULL + identifies row
• Unique key: unique but can allow NULL (depends DB)
• Foreign key?

✅ Answer:
Maintains relationship between tables.
Ensures referenced row exists.

9. Does sql.Open() create a database connection immediately?

No. sql.Open() initializes a *sql.DB, which represents a pool of connections. It validates the configuration but doesn’t necessarily establish a connection immediately. Calling db.Ping() or executing the first query causes an actual connection attempt.

10. Is sql.DB thread-safe?

Yes. A single *sql.DB is safe for concurrent use by multiple goroutines and should generally be shared across your application.

11.What happens if all connections are busy?

If the number of in-use connections reaches MaxOpenConns, additional requests wait until a connection is returned to the pool (or until the operation times out if a context deadline is reached).

12. Should you call sql.Open() for every request?

No. Create one *sql.DB when your application starts and reuse it. Creating a new *sql.DB for every request defeats the purpose of connection pooling and can hurt performance.

For Golang backend interviews, this is one of the most commonly asked database topics. Understanding how database/sql manages connection pooling and how to tune SetMaxOpenConns, SetMaxIdleConns, SetConnMaxLifetime, and SetConnMaxIdleTime is often expected.