What SQL statement is used to apply the changes made within a transaction permanently to the database?
EXECUTE
UPDATE
SAVE
COMMIT
What is a CTE (Common Table Expression) in SQL?
A stored procedure used to encapsulate and reuse SQL code.
A temporary, named result set that can be referenced within a single query.
A type of index used for optimizing query performance.
A permanent table structure stored in 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 >= DATEADD(day, -7, GETDATE());
SELECT DISTINCT CustomerID FROM Orders WHERE YEAR(OrderDate) = 2022 AND MONTH(OrderDate) = 12;
SELECT DISTINCT CustomerID FROM Orders WHERE OrderDate LIKE '%2022-12%';
SELECT DISTINCT CustomerID FROM Orders WHERE OrderDate BETWEEN '2022-12-25' AND '2022-12-31';
What is a key difference between a regular subquery and a correlated subquery?
Regular subqueries can modify data, while correlated subqueries are read-only.
Correlated subqueries are executed only once for the entire outer query, while regular subqueries are executed for each row.
Correlated subqueries depend on the outer query for their results, while regular subqueries are independent.
Regular subqueries can access columns from the outer query, while correlated subqueries cannot.
In SQL, a self-join is used to:
Join two tables with different schemas.
Combine data from multiple tables based on a common column.
Improve query performance by reducing the number of joins.
Join a table to itself using an alias.
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, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees 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;
You need to find all employees who share the same job title as their manager. Which join operation is MOST suitable for this scenario?
Self-JOIN
CROSS JOIN
LEFT JOIN
FULL OUTER JOIN
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?
Window Function
Cross Join
Recursive CTE
Self Join
What is a disadvantage of having too many indexes on a table?
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.
Reduced data integrity because of potential index corruption.
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 NOT IN (NULL);
SELECT * FROM Customers WHERE PhoneNumber IS NULL;