You are tasked with finding employees who do not manage any other employees. Which type of JOIN would be most suitable for this scenario?
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
INNER JOIN
What is the primary difference between a FULL OUTER JOIN and a UNION ALL operation in SQL?
FULL OUTER JOIN eliminates duplicates, UNION ALL retains them.
FULL OUTER JOIN combines rows based on a join condition, UNION ALL appends all rows.
There is no difference; they achieve the same outcome.
FULL OUTER JOIN only returns matching rows, UNION ALL returns all rows from both tables.
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 EXTRACT(MONTH FROM OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders ORDER BY Month;
SELECT MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders ORDER BY MONTH(OrderDate);
SELECT MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders GROUP BY Month;
SELECT STRFTIME('%Y-%m', OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders GROUP BY Month;
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?
Window function with SUM() OVER (ORDER BY...)
GROUP BY clause with SUM() aggregate function
Self join with aggregate function
Correlated subquery with SUM() function
Imagine you need to find pairs of employees who share the same job title without displaying the same employee twice (e.g., Employee A paired with Employee B is the same as Employee B with Employee A). Which SQL concept would be most efficient for this task?
Cross Join
INTERSECT
Self-Join
UNION
What is the maximum level of recursion allowed in a recursive CTE?
1,000
It depends on the database system's configuration.
10
100
Which of the following ACID properties ensures that any changes made within a transaction are permanent, even in case of system failures?
Durability
Atomicity
Consistency
Isolation
In SQL, a self-join is used to:
Improve query performance by reducing the number of joins.
Join two tables with different schemas.
Join a table to itself using an alias.
Combine data from multiple tables based on a common column.
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 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)
SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(*) > 5
In SQL, what is the purpose of a transaction?
To retrieve data from a database.
To create a backup of the database.
To optimize query performance.
To define a unit of work that must be executed as a whole to maintain database consistency.