Which of these tools can be used to analyze query performance in SQL?
Query optimizer
Data definition language (DDL)
Transaction control language (TCL)
Query execution plan
What is the primary purpose of using a Common Table Expression (CTE) in SQL?
To define a recursive relationship within a table.
To improve the performance of complex queries by storing intermediate results.
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.
What is the maximum level of recursion allowed in a recursive CTE?
1,000
It depends on the database system's configuration.
100
10
Which of these is NOT a common SQL performance tuning technique?
Using wildcard characters (%) at the beginning of a search pattern.
Selecting only the required columns instead of using SELECT *.
Filtering data on indexed columns whenever possible.
Using appropriate data types for columns.
What is a key advantage of using a subquery in the FROM clause to create a derived table?
It can enhance performance by pre-calculating and storing intermediate results.
It improves the readability of complex queries by breaking them down into smaller, more manageable parts.
It allows you to reuse the derived table multiple times within the same query.
All of the above
You have a table 'Orders' with columns 'OrderID', 'CustomerID', and 'OrderDate'. Write a SQL query to find the customers who placed orders in the last week of the year 2022.
SELECT DISTINCT CustomerID FROM Orders WHERE OrderDate >= DATEADD(day, -7, GETDATE());
SELECT DISTINCT CustomerID FROM Orders WHERE OrderDate LIKE '%2022-12%';
SELECT DISTINCT CustomerID FROM Orders WHERE OrderDate BETWEEN '2022-12-25' AND '2022-12-31';
SELECT DISTINCT CustomerID FROM Orders WHERE YEAR(OrderDate) = 2022 AND MONTH(OrderDate) = 12;
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?
None of the above
Simple Subquery
Subquery in the FROM Clause
Correlated Subquery
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 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.
Correlated subqueries are generally more efficient than joins.
What does a clustered index do?
It improves the performance of all queries on the table.
It defines the order in which data is physically stored in 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 want to retrieve products from a 'Products' table where the product name starts with 'App' and ends with 'e'. Which query will give you the correct result?
SELECT * FROM Products WHERE ProductName BETWEEN 'App' AND 'e';
SELECT * FROM Products WHERE ProductName LIKE 'App_e';
SELECT * FROM Products WHERE ProductName LIKE 'App%e';
SELECT * FROM Products WHERE ProductName IN ('App', 'e');