Advanced SQL and MongoDB Interview
Questions
Here's a more detailed and extensive list of SQL and MongoDB questions, covering a
broader range of topics such as advanced queries, indexing strategies, schema design,
transaction handling, replication, sharding, and database security. This elaboration should
help you gain a deeper understanding of both relational and NoSQL databases:
Advanced SQL Interview Questions
1. What is the difference between `HAVING` and `WHERE` clauses?
- **Answer**:
- **`WHERE`**: Used to filter rows before grouping occurs in a `GROUP BY` statement.
- **`HAVING`**: Used to filter groups after the `GROUP BY` operation has been performed.
Example:
```sql
SELECT department, COUNT(*)
FROM employees
WHERE age > 30
GROUP BY department
HAVING COUNT(*) > 5;
```
2. Explain the concept of `Window Functions` in SQL.
- **Answer**: A window function performs a calculation across a set of table rows that are
related to the current row. Unlike aggregate functions, it does not reduce the number of
rows.
```sql
SELECT employee_id, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
```
3. What are common table expressions (CTEs), and when would you use them?
- **Answer**: A CTE is a temporary result set that you can reference within a `SELECT`,
`INSERT`, `UPDATE`, or `DELETE` statement. It’s useful for simplifying complex queries,
breaking them into readable parts.
```sql
WITH sales_cte AS (
SELECT customer_id, SUM(order_total) AS total_sales
FROM orders
GROUP BY customer_id
)
SELECT * FROM sales_cte WHERE total_sales > 1000;
```
Advanced MongoDB Interview Questions
1. How is schema design different in MongoDB compared to relational
databases?
- **Answer**: MongoDB uses a flexible, schema-less structure, allowing for dynamic
document schemas. In contrast, relational databases enforce a strict schema with
predefined column types and constraints.
2. What are MongoDB’s data types?
- **Answer**: MongoDB supports various data types, including:
- **String**
- **Number** (int, long, double, decimal)
- **Boolean**
- **Date**
- **Array**
- **ObjectId**
- **Document** (subdocuments)
3. What is the `ObjectId` in MongoDB?
- **Answer**: The `ObjectId` is a unique identifier for MongoDB documents. It is a 12-byte
identifier consisting of a timestamp, machine identifier, process ID, and a counter.
```javascript
{ _id: ObjectId("507f191e810c19729de860ea") }
```
