Top 20 SQL Questions and Answers for MNC Interviews
Prepare for your MNC SQL interviews with confidence using these top 20 questions and answers covering basic syntax, advanced concepts, performance optimization, and security measures. Master SQL techniques for data manipulation, aggregation, handling missing values, and understanding transaction properties. https://www.microsoft.com/
**Understanding Basic Syntax and Concepts**
**Types of JOINs and Their Usage**
1. **Explaining JOINs in SQL**
**Question:** What are the main types of JOINs in SQL, and when should you use each?
**Answer:**
– **INNER JOIN:** Returns rows with matching conditions in both tables.
– **LEFT/RIGHT JOIN:** Includes all rows from one table and matching rows from the other.
– **FULL JOIN:** Combines all rows from both tables, regardless of matching conditions.
– **CROSS JOIN:** Generates a Cartesian product, potentially leading to large datasets.
2. **Understanding Subqueries**
**Question:** What are subqueries, and how can they be utilized in SQL?
**Answer:**
– Subqueries are nested queries used for complex filtering or aggregation within SELECT, WHERE, or FROM clauses.
– *Example:* Identifying customers who placed orders before their orders were shipped.
**Handling Intermediate & Advanced Concepts**
**Aggregation and Filtering**
3. **Filtering Dates**
**Question:** How do you write a query to filter dates within a specific range?
**Answer:**
– Utilize WHERE clause with BETWEEN operator (inclusive) or > and < (exclusive).
– *Example:* `SELECT * FROM orders WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’`.
4. **Aggregation Functions**
**Question:** What are aggregation functions, and how are they used?
**Answer:**
– These functions summarize grouped data.
– *Example:* `SELECT country, COUNT(*) AS customer_count FROM customers GROUP BY country`.
5. **Distinct vs. Group By**
**Question:** Explain the difference between DISTINCT and GROUP BY.
**Answer:**
– **DISTINCT:** Removes duplicate rows based on specified columns.
– **GROUP BY:** Groups rows based on specific columns and performs aggregate calculations.
**Intermediate & Advanced Concepts**
**Handling Missing Values and Top N Records**
6. **Handling NULL Values**
**Question:** How do you handle missing values (NULLs) in SQL?
**Answer:**
– Use IS NULL, IS NOT NULL, COALESCE, or IFNULL functions appropriately.
– *Example:* `SELECT * FROM products WHERE price IS NOT NULL`.
7. **Retrieving Top Records**
**Question:** Write a query to find the top N records from a table.
**Answer:**
– Utilize ORDER BY DESC and LIMIT/TOP clauses.
– *Example:* `SELECT * FROM customers ORDER BY total_purchases DESC LIMIT 10`.
**Window Functions and Self-Joins**
8. **Understanding Window Functions**
**Question:** What are window functions, and how are they used?
**Answer:**
– These functions apply calculations within partitions of a dataset.
– *Example:* `SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rank FROM products`.
9. **Modeling Hierarchical Relationships**
**Question:** Explain how self-joins can be used to model hierarchical relationships.
**Answer:**
– Self-joins connect a table to itself based on a relationship column.
– *Example:* Finding employees and their direct reports in an organization’s hierarchy.
**Creating Views and Performance Optimization**
10. **Working with Views**
**Question:** How do you create and use views in SQL?
**Answer:**
– Views are virtual tables based on stored queries, simplifying complex logic reuse.
– *Example:* Creating a view for customers’ recent orders.
11. **Optimizing Query Performance**
**Question:** What are some techniques for optimizing query performance?
**Answer:**
– Utilize appropriate indexes, avoid SELECT *, efficiently handle joins, and write clear and concise queries.
– *Example:* Creating an index on the most frequently used join column.
12. **Ensuring Data Security**
**Question:** How can you ensure data security in SQL queries?
**Answer:**
– Use parameterized queries to prevent SQL injection attacks.
– Grant least privilege access and avoid using wildcard characters in WHERE clauses.
– *Example:* Using prepared statements or stored procedures with parameters.
**Understanding ACID Properties and Specific Scenarios**
13. **Understanding ACID Properties**
**Question:** Explain the ACID properties of transactions.
**Answer:**
– Atomicity, Consistency, Isolation, Durability ensure data integrity in database operations.
14. **Calculating Rolling Sales Totals**
**Question:** Write a query to calculate rolling sales totals for the past X months.
**Answer:**
– You can achieve this using either window functions or subqueries.
15. **Identifying Duplicate Records**
**Question:** How can you identify duplicate records in a table?
**Answer:**
– There are a few ways to identify duplicate records.
**Common Challenges in Data Warehousing and Advanced Topics**
16. **Challenges in Data Warehousing**
**Question:** What are common challenges in data warehousing, and how can SQL address them?
**Answer:**
– Common challenges include: large data volumes, dimensional modeling, data quality, and performance.
17. **Understanding Subquery Correlation**
**Question:** Explain the concept of subquery correlation and how it’s used.
**Answer:**
– Subquery correlation allows subqueries to reference columns from outer queries, enabling more complex filtering and calculations.
18. **Exploring Common Table Expressions**
**Question:** What are common table expressions (CTEs), and how do they benefit SQL queries?
**Answer:**
– CTEs are temporary named result sets within a query, improving readability and modularity.
19. **Utilizing Stored Procedures and Functions**
**Question:** What are stored procedures and functions, and how are they used?
**Answer:**
– Stored procedures: Pre-compiled sets of SQL statements, enhancing performance and security.
– Functions: Reusable blocks of code performing specific tasks, reducing redundancy and promoting modularity.
20. **Distinguishing OLTP and OLAP Databases**
**Question:** Describe the difference between OLTP and OLAP databases and how SQL usage differs in each.
**Answer:**
– **OLTP (Online Transaction Processing):** Handles high-volume, real-time transactions, often using joins and filtering.
– **OLAP (Online Analytical Processing):** Focuses on complex data analysis, utilizing aggregation, window functions, and complex joins.
By mastering these concepts and practicing diverse scenarios, you’ll be well-equipped to tackle SQL questions in your MNC interviews with confidence! https://techinterviewhero.com/
