You are joining tables 'Customers' and 'Orders' using NATURAL JOIN. Both tables have a column named 'CustomerID'. What happens during the join?
The 'CustomerID' columns are used for the join, avoiding duplicate columns in the result.
The join fails because column names are identical.
Only the 'CustomerID' from 'Customers' is included.
Both 'CustomerID' columns are included, potentially with duplicate data.
You need to represent a hierarchical organization structure in a relational database table. Which approach is most suitable for querying and traversing this hierarchical data?
Storing the entire hierarchy as a single string in a column
Implementing a Recursive CTE to query and navigate the hierarchy
Using multiple tables with foreign key relationships to represent different levels
Creating a separate table for each level of the hierarchy
Which of the following ACID properties ensures that any changes made within a transaction are permanent, even in case of system failures?
Atomicity
Durability
Consistency
Isolation
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.
FULL OUTER JOIN only returns matching rows, UNION ALL returns all rows from both tables.
There is no difference; they achieve the same outcome.
You have a table 'Orders' with columns 'OrderID', 'CustomerID', and 'OrderDate'. You want to rank customers within each order date based on the order they placed (earliest to latest). Which window function should you use?
ROW_NUMBER() OVER (PARTITION BY OrderDate ORDER BY OrderID)
DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate)
RANK() OVER (ORDER BY OrderDate, OrderID)
NTILE(4) OVER (PARTITION BY OrderDate ORDER BY OrderID)
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 = NULL;
SELECT * FROM Customers WHERE PhoneNumber NOT IN (NULL);
SELECT * FROM Customers WHERE PhoneNumber <> '';
SELECT * FROM Customers WHERE PhoneNumber IS NULL;
What is a key advantage of using a subquery in the FROM clause to create a derived table?
All of the above
It can enhance performance by pre-calculating and storing intermediate results.
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.
What is the maximum level of recursion allowed in a recursive CTE?
1,000
10
100
It depends on the database system's configuration.
Which of the following is a valid use case for a recursive CTE?
Retrieving all employees and their managers in a hierarchical structure.
Creating a new table based on the results of a query.
Updating multiple rows in a table based on a condition.
Finding the average salary of employees in a department.
You have a table of orders and want to calculate the running total of order amounts for each customer, ordered by order date. Which SQL feature would be most efficient for this task?
Self Join
Recursive CTE
Window Function
Cross Join