SQL Questions

To create a temporary, named result set that exists only within the scope of the current query.

To improve the performance of complex queries by storing intermediate results.

To replace the need for views in a database.

To define a recursive relationship within a table.

FULL OUTER JOIN combines rows based on a join condition, UNION ALL appends all rows.

FULL OUTER JOIN eliminates duplicates, UNION ALL retains them.

There is no difference; they achieve the same outcome.

FULL OUTER JOIN only returns matching rows, UNION ALL returns all rows from both tables.

SELECT ProductID, ProductName, 
CASE
    WHEN Price = NULL THEN 'Not Priced'
    ELSE Price
END AS ModifiedPrice
FROM Products;

SELECT ProductID, ProductName, 
COALESCE(Price, 'Not Priced') AS ModifiedPrice
FROM Products;

SELECT ProductID, ProductName, 
CASE
    WHEN Price IS NULL THEN 0
    ELSE Price
END AS ModifiedPrice
FROM Products;

SELECT ProductID, ProductName, 
ISNULL(Price, 'Not Priced') AS ModifiedPrice
FROM Products;

Optimize the database server's configuration parameters.

Use a stored procedure instead of ad-hoc queries.

Add an index to the 'customer_city' column.

Increase the memory allocated to the database server.

Consistency

Atomicity

Isolation

Durability

Recursive CTE

Window Function

Cross Join

Self Join

Storing the entire hierarchy as a single string in a column

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

SELECT MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales
FROM Orders
ORDER BY MONTH(OrderDate);

SELECT EXTRACT(MONTH FROM OrderDate) AS Month, SUM(SalesAmount) AS TotalSales
FROM Orders
ORDER BY Month;

SELECT MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales
FROM Orders
GROUP BY Month;

SELECT STRFTIME('%Y-%m', OrderDate) AS Month, SUM(SalesAmount) AS TotalSales
FROM Orders
GROUP BY Month;

DISCARD

REVERT

ROLLBACK

UNDO

NTILE()

RANK()

DENSE_RANK()

ROW_NUMBER()