Introduction
In Part-1 we covered the SQL basics like keys, joins, and queries. Now, let’s move a step ahead. In this second part, we’ll explore slightly deeper concepts like subqueries, normalization, transactions, stored procedures, and more — but don’t worry, we’ll keep everything beginner-friendly.
What is a Subquery?
A subquery is just a query inside another query. Think of it as asking one question to help answer another.
Example:
“Find employees working in the Sales department.”
SELECT name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');Here, the inner query finds the department ID for “Sales,” and the outer query finds employees in that department.
Understanding Normalization
Normalization is a method of organizing data so that it’s clean, avoids repetition, and maintains accuracy.
For example, instead of storing a customer’s address in 10 different tables, you store it once and link it with an ID.
- 1NF: Break data into atomic (smallest possible) pieces.
- 2NF: Make sure each column depends on the primary key.
- 3NF: Remove columns that don’t directly depend on the key.
This makes data easier to maintain and update.
What is Denormalization?
Denormalization is the opposite of normalization. It combines tables to make reading data faster — even though it may create some duplicates.
Think of it like keeping both “raw mangoes” and “sliced mangoes” in your fridge. It takes extra space, but it’s quicker when you need a ready-to-eat slice.
Used mainly in data warehouses where speed is more important than storage.
Transactions in SQL
A transaction is a group of SQL operations that work together as one.
For example, when you transfer money:
- Deduct from account A.
- Add to account B.
If either step fails, the whole transaction fails.
SQL follows ACID properties:
- Atomicity: All or nothing.
- Consistency: Data stays valid.
- Isolation: Two transactions don’t mess with each other.
- Durability: Once saved, it stays saved.
What is a Stored Procedure?
A stored procedure is like a reusable recipe stored in the database. Instead of writing the same code again and again, you save it once and run it whenever needed.
Benefits:
- Faster performance
- Reusable
- Can take inputs and return results
Handling NULL Values
NULL means no value. It’s not zero, not blank, just unknown.
- To check: IS NULL or IS NOT NULL.
- To replace:
- SELECT COALESCE(phone, ‘Not Provided’) FROM customers;
This way, if the phone number is missing, it shows “Not Provided.”
UNION vs UNION ALL
- UNION: Combines results and removes duplicates.
- UNION ALL: Combines results and keeps duplicates.
Example:
- UNION = Unique guest list at a wedding.
- UNION ALL = Every guest, even if invited twice.
What are Views?
A view is like a saved query. You don’t copy the whole data, you just create a “virtual table” that shows results from a query.
Uses:
- Simplify complex queries
- Restrict sensitive columns for certain users
- Make queries reusable
Understanding Triggers
A trigger is an automatic action that runs when something happens in a table.
Examples:
- Log every time a record is updated.
- Prevent salary from being reduced below minimum wage.
- Send an alert when a new order is placed.
Aggregate Functions in SQL
Aggregate functions take multiple rows and return a single value.
- COUNT() → how many rows
- SUM() → total of a column
- AVG() → average value
- MIN() → smallest value
- MAX() → largest value
Often used with GROUP BY to group results, like:
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
Interview Tips for Part-2 Topics
- Explain answers in simple terms, not just code.
- Use real-life examples (like money transfer for transactions).
- If stuck, talk about concept first, query later.
Small Practice Queries
- Find employees who don’t have a manager (NULL manager_id).
- Show total sales per region.
- List departments with more than 5 employees.
Best Learning Practices
- Break learning into small chunks (subqueries one day, transactions another).
- Practice on free sample databases like Sakila or AdventureWorks.
- Discuss queries with friends — teaching improves memory.
Conclusion
This part focused on slightly advanced SQL concepts — subqueries, normalization, transactions, stored procedures, views, and more. These are the exact areas interviewers love to test. Practice them with examples, and you’ll be ready for almost any SQL interview.
FAQs
Q1: Can I skip normalization?
Not really. For small projects, maybe yes. But in real jobs, normalization is key to keeping data accurate.
Q2: Do I need to memorize queries?
No. Understand the logic — once you know the “why,” the “how” is easy to recall.
Q3: Are stored procedures still used in modern databases?
Yes! They’re widely used for performance and security reasons.
Q4: Is UNION slower than UNION ALL?
Yes, because UNION has to check and remove duplicates, while UNION ALL doesn’t.
Q5: Can I use views instead of tables?
Views don’t store data, they just show it. So they’re great for simplifying queries, but not a replacement for tables.