Intermediate

SQL Questions

Programming Language

10 questions

Please login before attempting the quiz

Question 1

You are given a table 'Products' with columns 'ProductID', 'ProductName', and 'Price'. The Price column can contain NULL values for products not yet priced. Write a SQL query to retrieve all products, replacing NULL prices with the string 'Not Priced'.

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

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

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

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

Question 2

In SQL, when a subquery is used in the FROM clause, it essentially acts as a:

Temporary table.
Sorting mechanism.
Filter on the main query.
Join condition.

Question 3

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?

Using multiple tables with foreign key relationships to represent different levels
Implementing a Recursive CTE to query and navigate the hierarchy
Storing the entire hierarchy as a single string in a column
Creating a separate table for each level of the hierarchy

Question 4

You are given a table 'EmployeeHierarchy' with columns 'EmployeeID' and 'ManagerID', representing an employee hierarchy. Write a recursive SQL query to retrieve the entire hierarchy starting from the CEO (EmployeeID = 1).

SELECT EmployeeID, ManagerID
FROM EmployeeHierarchy
ORDER BY ManagerID;

SELECT EmployeeID, ManagerID
FROM EmployeeHierarchy
WHERE ManagerID IS NULL;

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;

Question 5

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
Window Function
Recursive CTE
Cross Join

Question 6

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?

Increase the memory allocated to the database server.
Use a stored procedure instead of ad-hoc queries.
Add an index to the 'customer_city' column.
Optimize the database server's configuration parameters.

Question 7

You are tasked with finding the total sales for each month of the year. You have a table 'Orders' with columns 'OrderID', 'OrderDate', and 'SalesAmount'. Which query correctly calculates the monthly sales totals?

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

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

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

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

Question 8

Which ACID property ensures that changes made within a transaction are permanently written to the database if the transaction succeeds?

Atomicity
Consistency
Durability
Isolation

Question 9

Which type of subquery can access columns from the outer query, potentially leading to performance implications?

Scalar subquery
Inline view
Non-correlated subquery
Correlated subquery

Question 10

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
FROM Employees
ORDER BY Department, Salary DESC
LIMIT 3;

SELECT EmployeeID, Department, Salary, 
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM Employees
WHERE Rank <= 3;

SQL Questions

Please login before attempting the quiz

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Please login before attempting the quiz

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Login Account