You have a table 'Customers' with a column 'LastPurchaseDate' that can contain NULL values. Write a query to retrieve all customers who have NOT made a purchase in the last 90 days.
SELECT * FROM Customers WHERE LastPurchaseDate IS NULL;
SELECT * FROM Customers WHERE LastPurchaseDate < DATEADD(day, -90, GETDATE());
SELECT * FROM Customers WHERE LastPurchaseDate IS NOT NULL AND LastPurchaseDate < DATEADD(day, -90, GETDATE());
SELECT * FROM Customers WHERE LastPurchaseDate < GETDATE() - 90;
Which window function would you use to assign a unique, sequential number to each row within a partition of a result set?
DENSE_RANK()
NTILE()
ROW_NUMBER()
RANK()
You have a large table with millions of records. You frequently query the table based on a specific column 'customer_city'. What is the most effective way to improve the performance of these queries?
Optimize the database server's configuration parameters.
Add an index to the 'customer_city' column.
Use a stored procedure instead of ad-hoc queries.
Increase the memory allocated to the database server.
What is a key difference between a regular subquery and a correlated subquery?
Regular subqueries can modify data, while correlated subqueries are read-only.
Correlated subqueries are executed only once for the entire outer query, while regular subqueries are executed for each row.
Regular subqueries can access columns from the outer query, while correlated subqueries cannot.
Correlated subqueries depend on the outer query for their results, while regular subqueries are independent.
What is the primary purpose of using a Common Table Expression (CTE) in SQL?
To create a temporary, named result set that exists only within the scope of the current query.
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.
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?
Subquery in the FROM Clause
Simple Subquery
None of the above
Correlated Subquery
Which SQL statement is used to discard the changes made within a transaction and revert to the previous state?
UNDO
ROLLBACK
DISCARD
REVERT
What is a disadvantage of having too many indexes on a table?
Increased complexity in managing the database schema.
Increased storage space required for the indexes.
Slower query execution due to the overhead of maintaining the indexes.
Reduced data integrity because of potential index corruption.
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?
Recursive CTE and aggregate functions
Window function (ROW_NUMBER()) and CTE
CROSS JOIN and GROUP BY
SELF JOIN and WHERE clause
In SQL, when a subquery is used in the FROM clause, it essentially acts as a:
Filter on the main query.
Join condition.
Temporary table.
Sorting mechanism.