What is the primary difference between a FULL OUTER JOIN and a UNION ALL operation in SQL?
There is no difference; they achieve the same outcome.
FULL OUTER JOIN only returns matching rows, UNION ALL returns all rows from both tables.
FULL OUTER JOIN eliminates duplicates, UNION ALL retains them.
FULL OUTER JOIN combines rows based on a join condition, UNION ALL appends all rows.
What is the primary purpose of using a Common Table Expression (CTE) in SQL?
To improve the performance of complex queries by storing intermediate results.
To define a recursive relationship within a table.
To create a temporary, named result set that exists only within the scope of the current query.
To replace the need for views in a database.
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 IN ('App', 'e');
SELECT * FROM Products WHERE ProductName LIKE 'App_e';
What is the result of a CROSS JOIN between a table with 5 rows and a table with 3 rows?
3 rows
15 rows
5 rows
8 rows
From a table 'Products', retrieve all products whose names start with 'A' or 'B' and end with 'e'.
SELECT * FROM Products WHERE ProductName LIKE '[AB]%e';
SELECT * FROM Products WHERE ProductName BETWEEN 'A' AND 'B' AND ProductName LIKE '%e';
SELECT * FROM Products WHERE ProductName LIKE 'A%' OR ProductName LIKE 'B%' AND ProductName LIKE '%e';
SELECT * FROM Products WHERE ProductName LIKE 'A%e' AND ProductName LIKE 'B%e';
What is a key advantage of using a subquery in the FROM clause to create a derived table?
It improves the readability of complex queries by breaking them down into smaller, more manageable parts.
It can enhance performance by pre-calculating and storing intermediate results.
It allows you to reuse the derived table multiple times within the same query.
All of the above
What is the maximum level of recursion allowed in a recursive CTE?
100
1,000
It depends on the database system's configuration.
10
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?
Inline View
Scalar Subquery
Correlated Subquery
Non-Correlated Subquery
In SQL, what is the purpose of a transaction?
To retrieve data from a database.
To optimize query performance.
To define a unit of work that must be executed as a whole to maintain database consistency.
To create a backup of the database.
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 LIKE '%2022-12%';
SELECT DISTINCT CustomerID FROM Orders WHERE OrderDate >= DATEADD(day, -7, GETDATE());
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;