Discussione Need help with SQL Query Interview Questions

[urooj]

Hello,

I hope you're all doing well. I am currently preparing for SQL interviews, and I came across a valuable resource that contains a collection of a list of SQL query interview questions.

While the resource is excellent, I encountered a few challenges with some of the questions, and I'm hoping to seek guidance from this knowledgeable community. If any of you have experience with SQL queries or have previously tackled SQL questions, your assistance would be highly appreciated.

Here are the specific questions I'm having trouble with:

1. [Question 1 - Replace NULL with 0 in SQL](#question1)
2. [Question 2 - Customers who never placed an order](#question2)
3. [Question 3 - Second highest salary](#question3)

If you have any insights, suggestions, or code snippets that could help me better understand and solve these challenges, please feel free to share them in this thread. Any explanations or step-by-step breakdowns of your thought process would be immensely beneficial.

I am eager to learn and improve my SQL skills, and I believe this platform offers a great opportunity to collaborate and grow as developers.

Thank you all in advance.

 

[Dazorn]

Hello,

I hope you're all doing well. I am currently preparing for SQL interviews, and I came across a valuable resource that contains a collection of a list of SQL query interview questions.

While the resource is excellent, I encountered a few challenges with some of the questions, and I'm hoping to seek guidance from this knowledgeable community. If any of you have experience with SQL queries or have previously tackled SQL questions, your assistance would be highly appreciated.

Here are the specific questions I'm having trouble with:

1. [Question 1 - Replace NULL with 0 in SQL](#question1)
2. [Question 2 - Customers who never placed an order](#question2)
3. [Question 3 - Second highest salary](#question3)

If you have any insights, suggestions, or code snippets that could help me better understand and solve these challenges, please feel free to share them in this thread. Any explanations or step-by-step breakdowns of your thought process would be immensely beneficial.

I am eager to learn and improve my SQL skills, and I believe this platform offers a great opportunity to collaborate and grow as developers.

Thank you all in advance.

Hello,
let's start.

Question 1 - Replace NULL with 0 in SQL​

You can easily achieve this task with a built-in SQL function... ISNULL(expression)
It's usage and implementation can be different based on which SQL solution you're using.

The basical form returns a boolean value (zero or one) based on the expression you gave it. Let's see it with an example:

ISNULL(NULL) -- returns 1
ISNULL('hello') -- returns 0

Other implementations, like SQL Server's one, permits you to specify another argument as a replacement value in case the returned value is 1, for example:

ISNULL(NULL, 'hello') -- returns 'hello' because the first argument is null

Anyway, you can use ISNULL function with CASE statement to achieve the desired result:

CASE WHEN ISNULL(COLUMN) THEN 0 END

Question 2 - Customers who never placed an order​

Let's assume we have two tables:

TABLE CUSTOMERS
(
    ID BIGINT,
    FULL_NAME VARCHAR(50),
    ...
)

TABLE ORDERS
(
    ID BIGINT,
    CUSTOMER_ID BIGINT,
    ...
)

As you can see, you have a relationship N to 1 from order to customer, which means a customer can have multiple orders but each order refers to only one customer in particular. Said that, it's easy to retrieve customers who have not placed orders:

SELECT        A.FULL_NAME
FROM        CUSTOMERS AS A
WHERE        (
    SELECT    COUNT(0)
    FROM    ORDERS B
    WHERE    B.CUSTOMER_ID = A.ID
) = 0

In this case, I used a sub-query to check (foreach customer) if the ORDERS table has no records related to the current customer (the COUNT function).
You can also increase performances of this query for sure, but let's keep it simple for now. You will be able to investigate further on this and find a better (and more complex also) solution.

Question 3 - Second highest salary​

As before, let's assume we have the following table:

TABLE SALARIES
(
    ID INT,
    INCOME FLOAT,
    ...
)

To achieve the task you can refer to LIMIT and OFFSET functions of SQL:

• LIMIT means you put a forced end to the results fetched and just return N records
• OFFSET grants you the possibility to skip N records (in order obviously)

Let's see them with an example:

SELECT    ID, INCOME
FROM    SALARIES
ORDER BY INCOME DESC
LIMIT 1 OFFSET 1

In this case I firstly ordered all the retrieved records by INCOME property descendant (highest salary as first record), then I applied LIMIT function to just take 1 record and finally with OFFSET function I skipped the first record (the highest salary). At the end, this query just return the second highest salary.

I hope you have a more clear vision now
Feel free to ask more explanations if needed