You are tasked with identifying products that have sold more units than the average units sold for all products in their respective category. Which SQL query structure is best suited for this scenario?
Simple Subquery
None of the above
Correlated Subquery
Subquery in the FROM Clause
You have a table 'Orders' with order details and want to identify the first order placed by each customer based on the order date. Which combination of concepts would be most appropriate?
CROSS JOIN and GROUP BY
SELF JOIN and WHERE clause
Recursive CTE and aggregate functions
Window function (ROW_NUMBER()) and CTE
What is the primary purpose of using a Common Table Expression (CTE) in SQL?
To define a recursive relationship within a table.
To create a temporary, named result set that exists only within the scope of the current query.
To replace the need for views in a database.
To improve the performance of complex queries by storing intermediate results.
What is a key advantage of using a subquery in the FROM clause to create a derived table?
It allows you to reuse the derived table multiple times within the same query.
It can enhance performance by pre-calculating and storing intermediate results.
All of the above
It improves the readability of complex queries by breaking them down into smaller, more manageable parts.
You are using a correlated subquery to compare values in each row of a table to aggregated data from another table. What is a potential performance concern with this approach?
Correlated subqueries are generally more efficient than joins.
Correlated subqueries can be computationally expensive, especially with large datasets, as the subquery might be executed repeatedly for each row of the outer query.
Correlated subqueries are not recommended for use with aggregated data.
Correlated subqueries can lead to faster data retrieval due to their targeted nature.
What SQL statement is used to apply the changes made within a transaction permanently to the database?
COMMIT
EXECUTE
SAVE
UPDATE
What does the 'Atomicity' property in ACID guarantee?
A transaction either completes entirely or has no effect at all.
Transactions are processed in complete isolation from each other.
Changes made by a committed transaction are permanent.
The database remains in a consistent state before and after a transaction.
What is a CTE (Common Table Expression) in SQL?
A type of index used for optimizing query performance.
A temporary, named result set that can be referenced within a single query.
A stored procedure used to encapsulate and reuse SQL code.
A permanent table structure stored in the database.
You need to find all employees who share the same job title as their manager. Which join operation is MOST suitable for this scenario?
Self-JOIN
FULL OUTER JOIN
LEFT JOIN
CROSS JOIN
Which of the following ACID properties ensures that any changes made within a transaction are permanent, even in case of system failures?
Isolation
Durability
Consistency
Atomicity