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: Normalization and 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 (JOINs 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?