SQL - 03

Question 1

What is a subquery in SQL?

Answer 1

A subquery is a SQL query nested within another SQL query. It is used to perform operations on data before joining, for isolating specific queries for readability, or for queries that depend on another query’s results.

Question 2

What is a join in SQL?

Answer 2

A join in SQL is used to combine rows from two or more tables, based on a related column. It is primarily used for straightforward combinations of data where tables have related information.

Question 3

When should you use a subquery instead of a join?

Answer 3

Use a subquery when you need to perform operations on a subset of data before combining it with other data, or when your query logic is dependent on the results of another query.

Question 4

What are the types of joins and their purposes?

Answer 4

Common types of joins include INNER JOIN (returns rows with matching values in both tables), LEFT JOIN (returns all rows from the left table, and matching rows from the right table), RIGHT JOIN (opposite of LEFT JOIN), and FULL JOIN (combines results of both LEFT and RIGHT JOINS).

Question 5

Give an example of a situation where a join is more suitable than a subquery.

Answer 5

A join is more suitable when you need to combine information from different tables that share a common column, like combining customer details from a ‘Customers’ table with their orders from an ‘Orders’ table.

Question 6

Can a subquery be used in a JOIN statement?

Answer 6

Yes, subqueries can be used within a JOIN statement. They are often used in the ON clause to define the conditions of the join or in the SELECT clause to provide additional columns.

Question 7

What is a window function in SQL?

Answer 7

A window function performs a calculation across a set of table rows that are somehow related to the current row. Unlike regular aggregate functions, window functions do not collapse rows and allow you to perform calculations over ‘windows’ of rows.

Question 8

What are common SQL window functions?

Answer 8

Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), SUM(), AVG(), MIN(), MAX(), and more.

Question 9

How does ROW_NUMBER() differ from RANK() and DENSE_RANK()?

Answer 9

ROW_NUMBER() assigns a unique number to each row starting from 1. RANK() gives the same rank to rows with the same value, skipping subsequent ranks. DENSE_RANK() is similar to RANK() but doesn’t skip ranks.

Question 10

What is the purpose of the PARTITION BY clause in a window function?

Answer 10

PARTITION BY divides the rows into different partitions or subsets, and the window function is applied to each partition separately. This is similar to ‘GROUP BY’ but does not aggregate the rows into a single output row per group.

Question 11

Give an example of when to use the LEAD() and LAG() functions.

Answer 11

Use LEAD() to access data from a subsequent row, and LAG() to access data from a preceding row. They are useful in calculations that need to compare or contrast values in different rows, such as calculating the difference in sales between two consecutive months.

Question 12

How do you calculate a moving average using window functions?

Answer 12

To calculate a moving average, use the AVG() function combined with the OVER() clause, specifying the range of rows to include in each calculation, typically using ROWS BETWEEN.

Question 13

What does a LEFT JOIN do in SQL?

Answer 13

A LEFT JOIN in SQL returns all rows from the left table and the matched rows from the right table. If there are no matches in the right table, the result is NULL on the right side.

Question 14

How can a LEFT JOIN result in duplicate rows?

Answer 14

Duplicates can occur in a LEFT JOIN if there are multiple matching rows in the right table for a row in the left table. Each matching row in the right table creates a new row in the result set, paired with the left table row.

Question 15

Example scenario where a LEFT JOIN creates duplicates.

Answer 15

Consider two tables, ‘Employees’ and ‘Projects’. If one employee is assigned to multiple projects, a LEFT JOIN on employee ID will create a separate result row for each project they’re associated with, duplicating employee details.

Question 16

How can you avoid duplicates in a LEFT JOIN?

Answer 16

To avoid duplicates, you can use DISTINCT, group by a unique key, or include conditions in the JOIN clause or WHERE clause to narrow down the results to unique rows.

Question 17

When are duplicates in a LEFT JOIN acceptable or useful?

Answer 17

Duplicates can be useful when you need a complete picture of all combinations of related rows across tables, like when listing all projects each employee is working on, even if it means repeating employee details.

Question 18

Tips for managing duplicates in SQL query results.

Answer 18

Understand the data model and relationships, carefully design JOIN conditions, use aggregation functions if appropriate, and apply DISTINCT or GROUP BY judiciously to manage duplicates.

Question 19

Left Join

Answer 19

all/ every/ each in a question

Question 20

What is a Cross Join in SQL?

Answer 20

A Cross Join in SQL produces a Cartesian product of the two tables involved. It combines each row of the first table with each row of the second table, often resulting in a large number of rows.

Question 21

What is an Inner Join in SQL?

Answer 21

An Inner Join in SQL returns rows that have matching values in both tables. It is used to combine rows from two or more tables based on a related column between them.

Question 22

Key Difference Between Cross Join and Inner Join

Answer 22

The key difference is that a Cross Join does not require any condition to join two tables and returns all possible combinations, while an Inner Join requires a join condition and only returns rows where the condition is true.

Question 23

When to use a Cross Join?

Answer 23

Use a Cross Join when you need to pair each row of one table with each row of another table, such as generating all possible combinations of products and colors for a product catalogue.

Question 24

When to use an Inner Join?

Answer 24

Use an Inner Join when you need to combine rows from two or more tables based on a related column and you are only interested in rows where the join condition is met.

Question 25

Example scenario for using Cross Join

Answer 25

If you have a table of students and a table of courses, and you want to see every possible student-course combination, a Cross Join would be used.

Question 26

Example scenario for using Inner Join

Answer 26

If you have a table of employees and a table of departments, and you want to list employees only in the departments they belong to, an Inner Join would be used.

Question 27

What is a correlated subquery in SQL?

Answer 27

A correlated subquery is a subquery that references columns from the outer query. It cannot be executed independently of the outer query because it uses the outer query’s values to complete its calculation.

Question 28

How does a correlated subquery differ from a regular subquery?

Answer 28

Unlike a regular subquery, which runs once and returns values to be used by the outer query, a correlated subquery runs once for each row processed by the outer query, using values from the outer query in its calculations.

Question 29

Example of a correlated subquery.

Answer 29

Consider an Employees table and a Salaries table. To find employees who earn more than the average salary in their department, you can use a correlated subquery that compares each employee’s salary with the average salary of their department.

Question 30

When should you use a correlated subquery?

Answer 30

Use a correlated subquery when you need to compare values in a row with other values in the same table, or when each row’s result depends on calculations involving other rows.

Question 31

Performance consideration for correlated subqueries.

Answer 31

Correlated subqueries can be slow, especially for large datasets, because the subquery may need to be executed for each row in the outer query. Optimizing indexes and considering alternative queries like joins can sometimes improve performance.

Question 32

Can a correlated subquery be used in the SELECT, WHERE, and FROM clauses?

Answer 32

Yes, correlated subqueries can be used in SELECT, WHERE, and FROM clauses, though their usage in the FROM clause (in the form of a derived table or inline view) is less common.