SQL & MySQL Interview Questions 21-25 (Practical Queries & Normalization Theory)

Hello! In our previous lessons, we've covered the fundamentals of database structure, indexes, and complex queries. We've learned how to find and delete data.

In this lesson, we'll round out our practical SQL skills by learning how to handle missing data (`NULL`) and modify existing data (`UPDATE`). We'll also look at a crucial set operator, `UNION`. Then, we will pivot to the most important theory in database design: Normalizationand Denormalization. Understanding this theory is what separates a good developer from a great one.

21. Write SQL to retrieve employees who do not have managers.

This is a classic question that tests your understanding of `NULL` values. In a typical `Employees` table, a `manager_id` column links to the `employee_id` of that person's manager. The top-level person (like the CEO) won't have a manager, so their `manager_id` will be `NULL`.

The key is that you cannot write `WHERE manager_id = NULL`. In SQL, `NULL` is not equal to anything, not even itself. You must use the`IS NULL` operator.

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO Employees VALUES
(1, 'Alice Smith', 3),  -- Manager is Carol
(2, 'Bob Johnson', 3),  -- Manager is Carol
(3, 'Carol White', 4),  -- Manager is David
(4, 'David Brown', NULL); -- David is the CEO

-- Select the employee(s) with no manager
SELECT name
FROM Employees
WHERE manager_id IS NULL;

Expected Output:

+-------------+
| name        |
+-------------+
| David Brown |
+-------------+
1 row in set

22. Write SQL to increase the salary of all employees by 10%.

This is a straightforward question to test your knowledge of the `UPDATE` statement. This command modifies existing rows in a table.

You use `UPDATE TableName` to specify the table, and `SET column = new_value` to perform the calculation. Because the question says "all employees," we do not use a `WHERE` clause.

-- Let's use a simple salary table
CREATE TABLE Salaries (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO Salaries VALUES
(1, 'Alice', 80000.00),
(2, 'Bob', 100000.00);

-- Show data *before* the update
SELECT * FROM Salaries;

-- Give everyone a 10% raise
UPDATE Salaries
SET salary = salary * 1.10;

-- Show data *after* the update
SELECT * FROM Salaries;

Output (Before):

+----+-------+-----------+
| id | name  | salary    |
+----+-------+-----------+
|  1 | Alice |  80000.00 |
|  2 | Bob   | 100000.00 |
+----+-------+-----------+
2 rows in set

Output (After):

+----+-------+-----------+
| id | name  | salary    |
+----+-------+-----------+
|  1 | Alice |  88000.00 |
|  2 | Bob   | 110000.00 |
+----+-------+-----------+
2 rows in set

23. What is the difference between UNION and UNION ALL?

Both `UNION` and `UNION ALL` are set operators used to combine the result sets of two or more `SELECT` statements. The columns in the `SELECT` statements must be the same number and have similar data types.

Analogy: You have two guest lists for a party.

CREATE TABLE Table1 (name VARCHAR(50));
CREATE TABLE Table2 (name VARCHAR(50));

INSERT INTO Table1 VALUES ('Alice'), ('Bob'), ('Carol');
INSERT INTO Table2 VALUES ('Bob'), ('David'), ('Eve');

-- 1. Using UNION (removes the duplicate 'Bob')
SELECT name FROM Table1
UNION
SELECT name FROM Table2;

-- 2. Using UNION ALL (keeps the duplicate 'Bob')
SELECT name FROM Table1
UNION ALL
SELECT name FROM Table2;

Output (UNION):

+-------+
| name  |
+-------+
| Alice |
| Bob   |
| Carol |
| David |
| Eve   |
+-------+
5 rows in set

Output (UNION ALL):

+-------+
| name  |
+-------+
| Alice |
| Bob   |
| Carol |
| Bob   |
| David |
| Eve   |
+-------+
6 rows in set

Tip: Always use `UNION ALL` unless you specifically need to remove duplicates. It is significantly more performant.

24. What is normalization? Explain 1NF, 2NF, 3NF, and BCNF.

Normalization is the formal process of organizing the columns and tables of a relational database to reduce data redundancy and improve data integrity.

Analogy: It's like cleaning a very messy closet. Instead of one giant pile of "clothes" (a single table), normalization is the process of creating a "shirt drawer" (a `Shirts` table), a "sock drawer" (a `Socks` table), and a "pants rack" (a `Pants` table). This makes it easier to add new clothes (insert), change an outfit (update), or get rid of an item (delete) without messing up the whole closet.

The Normal Forms:

25. What is denormalization, and when is it used?

Denormalization is the intentional process of adding redundant (duplicate) data back into a database.

We just spent all that time cleaning our closet (normalization), so why would we undo it?

The Reason: Performance.

A highly normalized database (like in 3NF) is fantastic for `INSERT`, `UPDATE`, and `DELETE` operations because data is in one place. However, it can be very slow for `SELECT` queries, as you have to `JOIN` many small tables back together (e.g., `Users`, `Posts`, `Comments`, `ZipCodes`).

Analogy: Your closet is perfectly normalized (shirts, pants, socks all separate). But it takes you 5 minutes to assemble an outfit (`JOIN`s are slow). You decide to create a "work uniform" on a single hanger with the shirt, pants, and tie all bundled together. This is redundant(you now have a shirt on a hanger *and* one in the drawer), but your "get ready for work" query is now blazing fast.

When is it used?