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 permanent table structure stored in the database.
A stored procedure used to encapsulate and reuse SQL code.
You need to find all employees who share the same job title as their manager. Which join operation is MOST suitable for this scenario?
CROSS JOIN
Self-JOIN
LEFT JOIN
FULL OUTER JOIN
What is the primary purpose of using a Common Table Expression (CTE) in SQL?
To define a recursive relationship within a table.
To replace the need for views in a database.
To create a temporary, named result set that exists only within the scope of the current query.
To improve the performance of complex queries by storing intermediate results.
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 can lead to faster data retrieval due to their targeted nature.
Correlated subqueries are not recommended for use with aggregated data.
In SQL, what is the purpose of a transaction?
To optimize query performance.
To retrieve data from a database.
To define a unit of work that must be executed as a whole to maintain database consistency.
To create a backup of the database.
What does a clustered index do?
It defines the order in which data is physically stored in the table.
It improves the performance of all queries on the table.
It creates a copy of the table data, sorted by the indexed column.
It stores a pointer to the data pages where the indexed values are located.
You have a table named 'Orders' with columns 'OrderID' and 'CustomerID'. You need to find the customers who have placed more than 5 orders. Which query achieves this using a subquery in the FROM clause?
SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(*) > 5
SELECT CustomerID FROM Orders WHERE EXISTS (SELECT 1 FROM Orders WHERE CustomerID = Orders.CustomerID GROUP BY CustomerID HAVING COUNT(*) > 5)
SELECT CustomerID FROM (SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders GROUP BY CustomerID) AS CustomerOrders WHERE OrderCount > 5
SELECT DISTINCT CustomerID FROM Orders WHERE OrderID IN (SELECT OrderID FROM Orders GROUP BY OrderID HAVING COUNT(*) > 5)
Which of the following SQL statements is used to definitively save changes made within a transaction?
SAVE
COMMIT
UPDATE
PERSIST
Which window function would you use to assign a unique, sequential number to each row within a partition of a result set?
RANK()
ROW_NUMBER()
DENSE_RANK()
NTILE()
You are tasked with finding employees who do not manage any other employees. Which type of JOIN would be most suitable for this scenario?
RIGHT JOIN
INNER JOIN