SQL Questions

Using appropriate data types for columns.

Using wildcard characters (%) at the beginning of a search pattern.

Selecting only the required columns instead of using SELECT *.

Filtering data on indexed columns whenever possible.

5 rows

8 rows

15 rows

3 rows

Returns the rank of each row based on the values in a specified column.

Determines the number of rows in a result set.

Calculates the running total of a numeric column.

Assigns a unique sequential integer to each row within a partition.

SELECT EmployeeID, ManagerID
FROM EmployeeHierarchy
WHERE ManagerID IS NULL;

SELECT EmployeeID, ManagerID
FROM EmployeeHierarchy
ORDER BY ManagerID;

WITH RecursiveHierarchy AS (
    SELECT EmployeeID, ManagerID
    FROM EmployeeHierarchy
    WHERE EmployeeID = 1
    UNION ALL
    SELECT eh.EmployeeID, eh.ManagerID
    FROM EmployeeHierarchy eh
    JOIN RecursiveHierarchy rh ON eh.ManagerID = rh.EmployeeID
)
SELECT *
FROM RecursiveHierarchy;

SELECT EmployeeID, ManagerID
FROM EmployeeHierarchy
WHERE EmployeeID = 1;

Inline View

Correlated Subquery

Non-Correlated Subquery

Scalar Subquery

Using multiple tables with foreign key relationships to represent different levels

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

Correlated subquery with SUM() function

GROUP BY clause with SUM() aggregate function

Self join with aggregate function

Window function with SUM() OVER (ORDER BY...)

To optimize query performance.

To retrieve data from a database.

To create a backup of the database.

To define a unit of work that must be executed as a whole to maintain database consistency.

Changes made by a committed transaction are permanent.

The database remains in a consistent state before and after a transaction.

Transactions are processed in complete isolation from each other.

A transaction either completes entirely or has no effect at all.

Both 'CustomerID' columns are included, potentially with duplicate data.

The join fails because column names are identical.

Only the 'CustomerID' from 'Customers' is included.

The 'CustomerID' columns are used for the join, avoiding duplicate columns in the result.