SQL Interview Questions Your Ultimate Guide

Question: How do you handle NULL values in SQL?

Answer:

In SQL, NULL represents the absence of a value or an unknown value. It is different from an empty string or zero; NULL simply means that the value is missing or undefined. Handling NULL values correctly is crucial to ensure data integrity and avoid unexpected behavior in queries. Here are several ways to handle NULL values in SQL:

1. Checking for NULL values:

• IS NULL: Used to check if a value is NULL.
• IS NOT NULL: Used to check if a value is not NULL.

SELECT * FROM Employees WHERE salary IS NULL;
SELECT * FROM Employees WHERE salary IS NOT NULL;

• Example: The query SELECT * FROM Employees WHERE salary IS NULL; retrieves all rows where the salary is NULL (i.e., not yet assigned).

2. Handling NULL in SELECT queries:

• COALESCE(): This function returns the first non-NULL value in a list of expressions. It is useful for substituting a NULL with a default value.

  SELECT COALESCE(salary, 0) FROM Employees;

  • In this example, if salary is NULL, it will return 0 instead.

• IFNULL() (MySQL) / NVL() (Oracle): Similar to COALESCE(), these functions replace NULL values with a specified value. Different databases have different implementations:

  • In MySQL: IFNULL(expression, replacement_value)
  • In Oracle: NVL(expression, replacement_value)

  SELECT IFNULL(salary, 0) FROM Employees;  -- MySQL

• NULLIF(): This function compares two expressions. If they are equal, it returns NULL; otherwise, it returns the first expression.

  SELECT NULLIF(salary, 0) FROM Employees;

  • If salary is 0, the result will be NULL; otherwise, it will return the actual salary.

3. NULL in Aggregates:

• When performing aggregate functions like SUM(), AVG(), COUNT(), MIN(), and MAX(), NULL values are ignored. For example, the SUM() function does not count NULL values, but it will still calculate the sum of non-NULL values.

  SELECT SUM(salary) FROM Employees;

  • If there are NULL values in the salary column, they will be ignored, and only non-NULL salaries will be summed up.

4. Using NULL in INSERT statements:

• You can insert a NULL value into a column if the column allows NULL values.

  INSERT INTO Employees (name, salary) VALUES ('John Doe', NULL);

  • In this example, the salary is inserted as NULL, assuming that the column allows NULLs.

5. Filtering and Handling NULL values in WHERE clause:

• NULL values cannot be compared using = or !=. Instead, you must use IS NULL or IS NOT NULL.

  SELECT * FROM Employees WHERE salary = NULL;  -- Incorrect, won't work!
  SELECT * FROM Employees WHERE salary IS NULL; -- Correct

• To handle NULLs when filtering, you can use IS NULL or IS NOT NULL in the WHERE clause, as shown above.

6. Default Values for NULL:

• You can set default values for columns to avoid inserting NULL values when no data is provided. This is typically done when creating or altering the table schema.

  CREATE TABLE Employees (
      id INT,
      name VARCHAR(50),
      salary DECIMAL(10, 2) DEFAULT 0.00
  );

  • In this case, if no salary is provided during an insert, it will default to 0.00 instead of NULL.

7. Replacing NULL values in an UPDATE statement:

• If you want to update NULL values to something else, you can use the UPDATE statement with the IS NULL condition.

  UPDATE Employees SET salary = 0 WHERE salary IS NULL;

  • This query sets all NULL salary values to 0.

8. Handling NULLs in JOINs:

• In JOIN operations, NULL values are treated specially:

  • INNER JOIN: NULL values from either table are excluded.
  • LEFT JOIN (or RIGHT JOIN): If a NULL value is in the left table, NULLs will appear in the right table columns (and vice versa).

  SELECT E.name, D.department_name
  FROM Employees E
  LEFT JOIN Departments D ON E.department_id = D.department_id;

  • If an employee doesn’t belong to any department, their department_name will be NULL in the result.

9. NULL in SQL Constraints:

• Columns can be defined to allow NULL or not. If you want to prevent NULL values, you can define a column as NOT NULL.

  CREATE TABLE Employees (
      id INT NOT NULL,
      name VARCHAR(50) NOT NULL,
      salary DECIMAL(10, 2) NULL
  );

  • The salary column can accept NULL values, while id and name cannot be NULL.

Key Takeaways:

• NULL represents an unknown or missing value and cannot be treated like other values (e.g., zero or empty string).
• Use IS NULL or IS NOT NULL to check for NULL values.
• Functions like COALESCE(), IFNULL(), and NVL() help handle NULLs by substituting default values.
• Aggregate functions ignore NULL values, but you can use COUNT() to include NULL values by counting rows.
• When inserting, updating, or selecting, carefully handle NULLs to avoid errors and unexpected results.

Question: Write a SQL query to find employees who have not received a salary in the last month.

Answer:

To find employees who have not received a salary in the last month, we need to compare the salary_date (or similar date column) with the current date and filter out employees who received their last salary payment in the past month. Here’s a SQL query to achieve this:

Assuming the following table structure:

• Employees table with columns: employee_id, name
• Salaries table with columns: employee_id, salary_amount, salary_date

SELECT E.employee_id, E.name
FROM Employees E
LEFT JOIN Salaries S ON E.employee_id = S.employee_id
WHERE S.salary_date < CURDATE() - INTERVAL 1 MONTH OR S.salary_date IS NULL;

Explanation:

• LEFT JOIN: We use a LEFT JOIN to ensure that even employees who don’t have any salary records are included in the result.
• S.salary_date < CURDATE() - INTERVAL 1 MONTH: This condition filters out employees who received their last salary more than a month ago.
• S.salary_date IS NULL: This condition includes employees who haven’t received any salary at all (i.e., they don’t have any entries in the Salaries table).

This query will return all employees who have not received a salary in the last month or have never received a salary.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as SQL interview questions, SQL interview experiences, and details about various SQL job positions. Click here to check it out.