SQL - 03 Flashcards
What is a subquery in SQL?
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.
What is a join in SQL?
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.
When should you use a subquery instead of a join?
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.
What are the types of joins and their purposes?
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).
Give an example of a situation where a join is more suitable than a subquery.
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.
Can a subquery be used in a JOIN statement?
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.
What is a window function in SQL?
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.
What are common SQL window functions?
Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), SUM(), AVG(), MIN(), MAX(), and more.
How does ROW_NUMBER() differ from RANK() and DENSE_RANK()?
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.
What is the purpose of the PARTITION BY clause in a window function?
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.
Give an example of when to use the LEAD() and LAG() functions.
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.
How do you calculate a moving average using window functions?
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.
What does a LEFT JOIN do in SQL?
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.
How can a LEFT JOIN result in duplicate rows?
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.
Example scenario where a LEFT JOIN creates duplicates.
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.
How can you avoid duplicates in a LEFT JOIN?
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.
When are duplicates in a LEFT JOIN acceptable or useful?
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.
Tips for managing duplicates in SQL query results.
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.
Left Join
all/ every/ each in a question
What is a Cross Join in SQL?
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.
What is an Inner Join in SQL?
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.
Key Difference Between Cross Join and Inner Join
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.
When to use a Cross Join?
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.
When to use an Inner Join?
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.
