SQL Server Interview Topic 3: Joins and Reports

This topic checks whether you can combine data from multiple tables and prepare report-style results. Full stack developers need this for screens like student marks report, class summary, attendance report, and fee collection report.

🎯 Interview Goal

You should be able to explain joins and report queries using related tables like Student, Exam, ExamMarks, Class, and Teacher.

Q18. What is a JOIN in SQL Server?

Quick interview answer:

A JOIN combines rows from two or more tables using a related column. For example, Student and ExamMarks can be joined using StudentId. Joins help us show meaningful data instead of only id values.

Study in detail: INNER JOIN - This article explains the basic join idea with clear examples.

Q19. What is an INNER JOIN?

Quick interview answer:

An INNER JOIN returns only matching rows from both tables. If a student does not have matching marks, that student will not appear in the result. Use it when both sides must exist.

Study in detail: INNER JOIN - This lesson shows how matching records are returned.

Q20. What is a LEFT JOIN?

Quick interview answer:

A LEFT JOIN returns all rows from the left table and matching rows from the right table. If there is no match, right-side columns show NULL. Use it when you still want to show students even if marks are not entered yet.

Study in detail: LEFT and RIGHT JOIN - This article explains how missing related data appears in join results.

Q21. What is the difference between RIGHT JOIN and LEFT JOIN?

Quick interview answer:

LEFT JOIN keeps all rows from the left table. RIGHT JOIN keeps all rows from the right table. Most teams prefer LEFT JOIN because it is easier to read when the main table is written first.

Study in detail: LEFT and RIGHT JOIN - This lesson compares both joins with examples.

Q22. What are FULL JOIN and CROSS JOIN?

Quick interview answer:

FULL JOIN returns all rows from both tables and shows NULL where there is no match. CROSS JOIN returns every possible combination between two tables. CROSS JOIN can create many rows, so use it carefully.

Study in detail: FULL and CROSS JOIN - This article explains both joins with simple result tables.

Q23. What is a SELF JOIN?

Quick interview answer:

A SELF JOIN joins a table with itself. It is useful when rows in the same table are related. For example, an Employee table can store both employee and manager details, and ManagerId can refer to another employee row.

Study in detail: SELF JOIN - This lesson explains how one table can be used twice in the same query.

Q24. What are aggregate functions?

Quick interview answer:

Aggregate functions calculate one result from many rows. Common examples are COUNT, SUM, AVG, MIN, and MAX. In a school report, we can count students, calculate average marks, or find highest marks.

Study in detail: Aggregate Functions - This article explains report calculations with practice queries.

Q25. What is GROUP BY?

Quick interview answer:

GROUP BY groups rows before calculating aggregate results. For example, we can group students by class and count how many students are in each class. It is commonly used in dashboard and report queries.

Study in detail: GROUP BY and HAVING - This lesson explains grouping step by step.

Q26. What is the difference between WHERE and HAVING?

Quick interview answer:

WHERE filters rows before grouping. HAVING filters groups after grouping. For example, WHERE can select only active students, and HAVING can show only classes where average marks are above 75.

Study in detail: GROUP BY and HAVING - This article shows the correct order and usage.

Q27. What is UNION?

Quick interview answer:

UNION combines results from two queries and removes duplicate rows. UNION ALL combines results but keeps duplicates. Use UNION ALL when duplicates are allowed and performance matters.

Study in detail: UNION and UNION ALL - This lesson explains how to combine similar result sets.

Q28. What is a subquery?

Quick interview answer:

A subquery is a query written inside another query. It helps when one query needs a value or result from another query. For example, we can find students whose marks are above the class average.

Study in detail: Subqueries - This article explains subqueries with beginner-friendly examples.

Practice Before Next Topic

Write queries for these tasks:

1. Show student name with exam marks using INNER JOIN.
2. Show all students even when marks are missing using LEFT JOIN.
3. Count students class-wise using GROUP BY.
4. Show only classes with more than 30 students using HAVING.
5. Combine current and old student lists using UNION ALL.

💡 Beginner Interview Tip

For join questions, always mention which table is the main table and which column connects the tables.