In SQL, what is the purpose of a transaction?
To define a unit of work that must be executed as a whole to maintain database consistency.
To optimize query performance.
To create a backup of the database.
To retrieve data from a database.
Which ACID property ensures that a transaction's changes are permanent if it completes successfully, even in the event of a system crash?
Isolation
Durability
Atomicity
Consistency
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?
DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate)
NTILE(4) OVER (PARTITION BY OrderDate ORDER BY OrderID)
ROW_NUMBER() OVER (PARTITION BY OrderDate ORDER BY OrderID)
RANK() OVER (ORDER BY OrderDate, OrderID)
In SQL, a self-join is used to:
Join two tables with different schemas.
Join a table to itself using an alias.
Combine data from multiple tables based on a common column.
Improve query performance by reducing the number of joins.
Which of these is NOT a common SQL performance tuning technique?
Using appropriate data types for columns.
Filtering data on indexed columns whenever possible.
Selecting only the required columns instead of using SELECT *.
Using wildcard characters (%) at the beginning of a search pattern.
Which of the following SQL statements is used to definitively save changes made within a transaction?
COMMIT
UPDATE
PERSIST
SAVE
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?
Implementing a Recursive CTE to query and navigate the hierarchy
Creating a separate table for each level of the hierarchy
Storing the entire hierarchy as a single string in a column
Using multiple tables with foreign key relationships to represent different levels
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 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.
Add an index to the 'customer_city' column.
Optimize the database server's configuration parameters.
Increase the memory allocated to the database server.
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 LIKE 'A%e' AND ProductName LIKE 'B%e';
SELECT * FROM Products WHERE ProductName LIKE 'A%' OR ProductName LIKE 'B%' AND ProductName LIKE '%e';
SELECT * FROM Products WHERE ProductName BETWEEN 'A' AND 'B' AND ProductName LIKE '%e';