Which of the following SQL statements is used to definitively save changes made within a transaction?
SAVE
COMMIT
PERSIST
UPDATE
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.
In SQL, when a subquery is used in the FROM clause, it essentially acts as a:
Join condition.
Filter on the main query.
Temporary table.
Sorting mechanism.
Which type of subquery can access columns from the outer query, potentially leading to performance implications?
Scalar subquery
Non-correlated subquery
Inline view
Correlated subquery
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.
All of the above
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.
You're asked to retrieve the names of all employees who earn more than the average salary of their respective departments. This necessitates calculating the average salary per department and then comparing it to individual employee salaries. Which type of subquery would be most appropriate for this scenario?
Scalar Subquery
Inline View
Correlated Subquery
Non-Correlated Subquery
What is a disadvantage of having too many indexes on a table?
Reduced data integrity because of potential index corruption.
Slower query execution due to the overhead of maintaining the indexes.
Increased complexity in managing the database schema.
Increased storage space required for the indexes.
You are tasked with finding the total sales for each month of the year. You have a table 'Orders' with columns 'OrderID', 'OrderDate', and 'SalesAmount'. Which query correctly calculates the monthly sales totals?
SELECT MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders GROUP BY Month;
SELECT MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders ORDER BY MONTH(OrderDate);
SELECT EXTRACT(MONTH FROM OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders ORDER BY Month;
SELECT STRFTIME('%Y-%m', OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders GROUP BY Month;
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?
Use a stored procedure instead of ad-hoc queries.
Optimize the database server's configuration parameters.
Increase the memory allocated to the database server.
Add an index to the 'customer_city' column.
Which ACID property ensures that any changes made within a transaction are applied to the database in a manner that prevents partial updates, preserving data consistency?
Isolation
Consistency
Atomicity
Durability