Q1: What is a CROSS JOIN, and when would you use it?
Answer:
A CROSS JOIN returns the Cartesian product of two tables, meaning it pairs each row of the first table with every row of the second table. It’s useful when you need every combination of two tables but should be used cautiously because it can result in a large number of rows.
Q2: What are SELF JOIN and when would you use it?
Answer:
A SELF JOIN is a join where a table is joined with itself. This is useful when you need to compare rows within the same table, such as when you have hierarchical data (e.g., employees reporting to managers in the same table).
Q3: What is a UNION and UNION ALL in SQL?
Answer:
-
UNION: Combines the results of two or more
SELECTqueries, removing duplicate rows. -
UNION ALL: Similar to
UNION, but it does not remove duplicates. It’s faster because it doesn’t perform the additional work of eliminating duplicates.
Q4: What is a Window Function, and how does it differ from regular aggregate functions?
Answer:
A Window Function allows you to perform calculations across a set of table rows related to the current row. Unlike aggregate functions, which return a single result (e.g., SUM, COUNT), window functions maintain individual rows while calculating aggregated results (e.g., ROW_NUMBER(), RANK(), LEAD(), LAG()).
Q5: How would you use a CASE statement in SQL?
Answer:
The CASE statement is a conditional expression used to create if-else logic in SQL queries. It’s commonly used in SELECT queries to return different values based on conditions or to group data into specific categories.
Q6: What is the DISTINCT keyword used for in SQL?
Answer:
The DISTINCT keyword is used to return only distinct (unique) values in a query result. It removes duplicate rows based on the columns selected in the SELECT statement.
Q7: What is a LEFT OUTER JOIN and how does it differ from a RIGHT OUTER JOIN?
Answer:
-
LEFT OUTER JOIN: Returns all rows from the left table and the matched rows from the right table. If no match exists, the result is
NULLon the right side. -
RIGHT OUTER JOIN: Returns all rows from the right table and the matched rows from the left table. If no match exists, the result is
NULLon the left side.
Q8: What are Common Table Expressions (CTEs) and how do they differ from subqueries?
Answer:
A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE query. It improves readability and modularity. Unlike subqueries, CTEs can be referenced multiple times in a query and are easier to manage.
Q9: What is the purpose of the GROUP BY clause in SQL?
Answer:
The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. It’s often used with aggregate functions like COUNT(), SUM(), AVG(), etc., to perform operations on each group of data.
Q10: Can you explain the use of the HAVING clause with GROUP BY?
Answer:
The HAVING clause is used to filter results after the GROUP BY operation, often in conjunction with aggregate functions. It allows you to set conditions on the grouped data, such as filtering out groups with low counts or averages.