SQL Questions

Karan Bhardwaj

Score: 🔥 123

Intermediate

Took 2 months

Programming Language

SQL

46%

Questions Attempted

15 / 15

Correct Answers

Incorrect Answers

Points Earned

🔥 7

Question 1

You have a table 'Customers' with a column 'LastPurchaseDate' that can contain NULL values. Write a query to retrieve all customers who have NOT made a purchase in the last 90 days.

SELECT *
FROM Customers
WHERE LastPurchaseDate IS NULL;

SELECT *
FROM Customers
WHERE LastPurchaseDate < DATEADD(day, -90, GETDATE());

SELECT *
FROM Customers
WHERE LastPurchaseDate IS NOT NULL AND LastPurchaseDate < DATEADD(day, -90, GETDATE());

SELECT *
FROM Customers
WHERE LastPurchaseDate < GETDATE() - 90;

Explanation:

This query correctly identifies customers who haven't made a purchase in the last 90 days by considering both NULL values (meaning they've never purchased) and dates older than 90 days ago. It uses IS NULL to check for null values and DATEADD function to subtract 90 days from the current date.

Question 2

You have a table named 'Employees' with columns 'EmployeeID', 'FirstName', 'LastName', and 'Salary'. You need to categorize employees based on their salary. Write a SQL query using CASE statement to display 'High Earner' if the salary is above 50000, 'Average Earner' if the salary is between 30000 and 50000, and 'Low Earner' otherwise.

SELECT EmployeeID, FirstName, LastName,
CASE
    WHEN Salary > 50000 THEN 'High Earner'
    WHEN Salary BETWEEN 30000 AND 50000 THEN 'Average Earner'
    ELSE 'Low Earner'
END AS SalaryCategory
FROM Employees;

SELECT EmployeeID, FirstName, LastName,
CASE
    WHEN Salary > 50000 THEN 'High Earner'
    WHEN Salary < 30000 THEN 'Low Earner'
    ELSE 'Average Earner'
END AS SalaryCategory
FROM Employees;

SELECT EmployeeID, FirstName, LastName,
CASE
    WHEN Salary = 50000 THEN 'High Earner'
    WHEN Salary BETWEEN 30000 AND 50000 THEN 'Average Earner'
    ELSE 'Low Earner'
END AS SalaryCategory
FROM Employees;

SELECT EmployeeID, FirstName, LastName,
CASE
    WHEN Salary > 50000 THEN 'High Earner'
    ELSE 'Low Earner'
END AS SalaryCategory
FROM Employees;

Explanation:

This query correctly uses the CASE statement to categorize employees based on the provided salary ranges. The BETWEEN operator is used effectively to define the range for 'Average Earners'.

Question 3

What is the result of a CROSS JOIN between a table with 5 rows and a table with 3 rows?

5 rows
3 rows
8 rows
15 rows

Explanation:

A CROSS JOIN produces a Cartesian product of the two tables. This means every row in the first table is combined with every row in the second table, resulting in 5 * 3 = 15 rows.

Question 4

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?

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

Explanation:

Recursive CTEs are ideal for representing and querying hierarchical data. They allow you to traverse the hierarchy recursively, retrieving data at different levels efficiently.

Question 5

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, 
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;

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;

Explanation:

This query utilizes the COALESCE function, which efficiently handles NULL values. It replaces NULL prices with 'Not Priced' while retaining the original prices for other products.

Question 6

You're asked to retrieve the names of all employees who earn more than the average salary of their respective departments. This necessitates calculating the average salary per department and then comparing it to individual employee salaries. Which type of subquery would be most appropriate for this scenario?

Non-Correlated Subquery
Correlated Subquery
Scalar Subquery
Inline View

Explanation:

A correlated subquery is executed once for each row in the outer query. In this case, for every employee, the subquery calculates the average salary of their department. This dynamic calculation, dependent on the outer query's row, makes correlated subqueries the right choice.

Question 7

You have a table 'Orders' with order details and want to identify the first order placed by each customer based on the order date. Which combination of concepts would be most appropriate?

CROSS JOIN and GROUP BY
SELF JOIN and WHERE clause
Window function (ROW_NUMBER()) and CTE
Recursive CTE and aggregate functions

Explanation:

You can use ROW_NUMBER() to assign a unique rank to each order by customer based on order date. Encapsulating this logic in a CTE allows you to then filter for the first order (ROW_NUMBER() = 1) for each customer.

Question 8

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 = NULL;

SELECT * FROM Customers WHERE PhoneNumber <> '';

SELECT * FROM Customers WHERE PhoneNumber IS NULL;

SELECT * FROM Customers WHERE PhoneNumber NOT IN (NULL);

Explanation:

In SQL, NULL represents the absence of a value. To compare against NULL, you must use the 'IS NULL' operator. Using '=' with NULL will not yield the desired results.

Question 9

Which of the following is a valid use case for a recursive CTE?

Finding the average salary of employees in a department.
Retrieving all employees and their managers in a hierarchical structure.
Updating multiple rows in a table based on a condition.
Creating a new table based on the results of a query.

Explanation:

Recursive CTEs excel at traversing hierarchical or tree-like structures within data. They are particularly well-suited for scenarios like retrieving all employees and their managers in a hierarchy, where each level of management is connected.

Question 10

Which window function would you use to assign a unique, sequential number to each row within a partition of a result set?

ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()

Explanation:

ROW_NUMBER() assigns a unique sequential integer to each row within the partition of a result set. It's used when you need a simple, unbroken sequence.

Question 11

Which of the following SQL statements is used to definitively save changes made within a transaction?

SAVE
COMMIT
UPDATE
PERSIST

Explanation:

In SQL, the COMMIT command is the instruction used to finalize changes made within a transaction. It signifies that all operations within the transaction were successful, and their effects should be permanently written to the database.

Question 12

What is the purpose of the ROW_NUMBER() function in SQL?

Calculates the running total of a numeric column.
Assigns a unique sequential integer to each row within a partition.
Returns the rank of each row based on the values in a specified column.
Determines the number of rows in a result set.

Explanation:

The ROW_NUMBER() function in SQL is specifically designed to assign a unique sequential integer to each row within a partition of a result set. Partitions are determined by the PARTITION BY clause, and the numbering restarts for each partition.

Question 13

What is the primary difference between a FULL OUTER JOIN and a UNION ALL operation in SQL?

FULL OUTER JOIN combines rows based on a join condition, UNION ALL appends all rows.
FULL OUTER JOIN only returns matching rows, UNION ALL returns all rows from both tables.
FULL OUTER JOIN eliminates duplicates, UNION ALL retains them.
There is no difference; they achieve the same outcome.

Explanation:

The core distinction lies in how they combine data. FULL OUTER JOIN merges rows from tables based on a specified join condition, while UNION ALL directly appends all rows from the involved tables without considering any condition.

Question 14

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

Explanation:

Window functions with a suitable ORDER BY clause within the OVER clause are specifically designed to perform calculations across a set of rows related to the current row, such as running totals, making them the most efficient way to calculate running totals.

Question 15

What SQL statement is used to apply the changes made within a transaction permanently to the database?

SAVE
COMMIT
UPDATE
EXECUTE

Explanation:

COMMIT is used to end a transaction and make all changes permanent in the database.