SQL subqueries flashcards

What is a subquery?

A subquery is a SQL query nested inside another query, used to provide results for the outer query.

True or False: Subqueries can return multiple rows.

True. Subqueries can return one or more rows, depending on the context of their use.

Fill in the blank: A subquery is also known as a __________.

nested query.

What are the main uses of subqueries?

- Filtering results - Calculating aggregates - Comparing values

Cause → Effect: Why use subqueries?

To break complex queries into simpler parts, improving readability and manageability.

How does a subquery differ from a join?

A join combines rows from different tables based on a related column, while a subquery executes independently.

What is the syntax for a basic subquery?

SELECT column_name FROM table_name WHERE column_name IN (SELECT column_name FROM table_name);

Question: Can subqueries be used in WHERE clauses?

Yes, subqueries are commonly used in WHERE clauses to refine results based on related data.

Example: Subquery to find employees in the same department.

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');

What are correlated subqueries?

Correlated subqueries reference columns from the outer query, executing once for each row processed by the outer query.

What is a single-row subquery?

A subquery that returns only one row. Use for comparisons in WHERE clauses.

True or False: Multi-row subqueries can return multiple rows and columns.

False. Multi-row subqueries return multiple rows but only one column.

Give an example of a correlated subquery.

SELECT * FROM Employees e WHERE e.salary > (SELECT AVG(salary) FROM Employees);

What type of subquery is used in the SELECT clause?

A scalar subquery. It returns a single value for each row processed.

Fill in the blank: A subquery can be used in the _____ clause.

WHERE, FROM, SELECT, HAVING.

Compare correlated and non-correlated subqueries.

- Correlated: references outer query - Non-correlated: independent of outer query

What is a nested subquery?

A subquery within another subquery, allowing complex queries to be constructed.

What is the primary use of EXISTS subqueries?

To check the existence of rows returned by the subquery, often used in conditions.

Cause → Effect: Using a subquery in the WHERE clause.

Cause: Restricts results based on conditions. Effect: Filters data more precisely.

What is a subquery in the FROM clause?

A derived table that can be joined with other tables, allowing complex aggregations.

True or False: Subqueries can improve readability and modularity of SQL queries.

True. They break complex queries into simpler components, enhancing clarity.

Provide a scenario for using a multi-column subquery.

Finding employees with specific combinations of job title and department, e.g., SELECT * FROM Employees WHERE (job_title, department) IN (SELECT job_title, department FROM Positions);

Define a subquery in a SELECT statement.

A subquery is a SQL query nested inside another query. It provides data to the outer query, enabling complex data retrieval.

What does the term 'correlated subquery' mean?

A correlated subquery references columns from the outer query. Its result depends on the outer query's values, leading to a row-by-row evaluation.

True or False: Subqueries can be used in the SELECT clause.

True. Subqueries can be included in the SELECT clause to compute derived values for each row of the main query.

Fill in the blank: Subqueries can be used in the ______ clause to filter results.

WHERE clause.

Compare subquery and join for data retrieval.

Subquery: Nested queries, simpler for specific tasks. Join: Combines tables directly, often more efficient for large datasets.

Provide an example of a subquery in a SELECT statement.

SELECT employee_id, (SELECT AVG(salary) FROM employees) AS avg_salary FROM employees;

What is the purpose of using subqueries?

Subqueries simplify complex queries by breaking them into manageable parts, allowing for more organized and readable SQL.

List two types of subqueries in SELECT statements.

- Single-row subquery - Multiple-row subquery

What happens if a subquery returns no rows?

If a subquery returns no rows, the outer query will not match any records, potentially resulting in an empty result set.

How can you use a subquery in the HAVING clause?

Example: SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > (SELECT AVG(emp_count) FROM departments);

True or False: Subqueries can return multiple columns.

False. Subqueries in SELECT statements typically return a single column for use in expressions or comparisons.

What is an inline view?

An inline view is a subquery in the FROM clause acting as a temporary table for the outer query. It allows complex data manipulation.

Why should you avoid using correlated subqueries?

Correlated subqueries can lead to performance issues as they execute once for each row processed by the outer query.

True or False: Subqueries can slow down query performance.

True - They can be less efficient than joins, especially in large datasets due to multiple executions.

Fill in the blank: Using __________ instead of subqueries can improve performance.

Joins - They are typically optimized better by the database engine.

What is a benefit of using EXISTS over IN?

EXISTS stops processing as soon as it finds a match, improving performance with large data sets.

Compare subqueries and joins in terms of performance.

- Joins are generally faster. - Subqueries can be clearer but slower. - Joins leverage indexing better.

How can indexing help with subqueries?

Indexing speeds up data retrieval, which is especially beneficial for subqueries that filter large datasets.

What should you consider when nesting subqueries?

Nesting too deeply can lead to complex queries that are challenging to optimize and debug.

Cause → Effect: What is the effect of using too many subqueries?

Can lead to degraded performance and longer execution times due to the increased complexity of query execution.

What is an alternative to subqueries for aggregation tasks?

Common Table Expressions (CTEs) can often simplify complex aggregations and improve readability.

Provide an example where a subquery is less efficient than a join.

SELECT * FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Country = 'USA'); This is often better as: SELECT * FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Country = 'USA';