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
CROSS JOIN and GROUP BY
SELF JOIN and WHERE clause
Window function (ROW_NUMBER()) and CTE
What does the 'Atomicity' property in ACID guarantee?
Transactions are processed in complete isolation from each other.
A transaction either completes entirely or has no effect at all.
The database remains in a consistent state before and after a transaction.
Changes made by a committed transaction are permanent.
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?
Non-Correlated Subquery
Correlated Subquery
Scalar Subquery
Inline View
You have a table named 'Employees' with columns 'EmployeeID', 'Department', and 'Salary'. Write a SQL query to fetch the top 3 highest-paid employees from each department, along with their rank within the department.
WITH RankedEmployees AS ( SELECT EmployeeID, Department, Salary, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank FROM Employees ) SELECT EmployeeID, Department, Salary, Rank FROM RankedEmployees WHERE Rank <= 3;
SELECT EmployeeID, Department, Salary, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank FROM Employees WHERE Rank <= 3;
SELECT EmployeeID, Department, Salary FROM Employees ORDER BY Department, Salary DESC LIMIT 3;
SELECT EmployeeID, Department, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees WHERE Rank <= 3;
You are analyzing sales data and want to display the running total of sales for each month. What SQL concept would be MOST suitable for this task?
Self join with aggregate function
GROUP BY clause with SUM() aggregate function
Window function with SUM() OVER (ORDER BY...)
Correlated subquery with SUM() function
Which of the following ACID properties ensures that any changes made within a transaction are permanent, even in case of system failures?
Atomicity
Isolation
Consistency
Durability
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
LEFT JOIN
FULL OUTER JOIN
INNER JOIN
What is a disadvantage of having too many indexes on a table?
Reduced data integrity because of potential index corruption.
Increased storage space required for the indexes.
Increased complexity in managing the database schema.
Slower query execution due to the overhead of maintaining the indexes.
You need to find all customers in your 'Customers' table who haven't provided a phone number. The 'PhoneNumber' column can contain NULL values. Which query achieves this?
SELECT * FROM Customers WHERE PhoneNumber <> '';
SELECT * FROM Customers WHERE PhoneNumber = NULL;
SELECT * FROM Customers WHERE PhoneNumber IS NULL;
SELECT * FROM Customers WHERE PhoneNumber NOT IN (NULL);
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?
Add an index to the 'customer_city' column.
Increase the memory allocated to the database server.
Use a stored procedure instead of ad-hoc queries.
Optimize the database server's configuration parameters.