SQL Interview Questions - Advanced 🎯

Welcome to the advanced SQL tutorial for CodeYourCraft! In this lesson, we'll dive deeper into SQL concepts, preparing you for real-world scenarios and advanced SQL interview questions. Let's get started! 📝

Normalization 💡

Normalization is the process of organizing data in a database to minimize redundancy and improve data integrity. There are four normal forms (1NF, 2NF, 3NF, and 4NF) to ensure optimal database design.

First Normal Form (1NF)

A table is in 1NF if:

It contains unique rows (no duplicate values in columns)
Each column contains atomic (indivisible) values

Second Normal Form (2NF)

A table is in 2NF if:

It is in 1NF
Each non-key column is fully dependent on the primary key

Third Normal Form (3NF)

A table is in 3NF if:

It is in 2NF
There are no transitive dependencies (a non-key attribute depends on another non-key attribute)

Joins 💡

Joins combine rows from two or more tables based on a related column called the join key. There are four types of joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

INNER JOIN

An INNER JOIN returns only the matching rows from both tables involved in the join.

sql

SELECT Orders.order_id, Customers.customer_name
FROM Orders
INNER JOIN Customers
ON Orders.customer_id = Customers.customer_id;

LEFT JOIN

A LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there are no matches, NULL values are used for the right table's columns.

sql

SELECT Orders.order_id, Customers.customer_name
FROM Orders
LEFT JOIN Customers
ON Orders.customer_id = Customers.customer_id;

RIGHT JOIN

A RIGHT JOIN returns all rows from the right table and the matching rows from the left table. If there are no matches, NULL values are used for the left table's columns.

sql

SELECT Orders.order_id, Customers.customer_name
FROM Orders
RIGHT JOIN Customers
ON Orders.customer_id = Customers.customer_id;

FULL OUTER JOIN

A FULL OUTER JOIN returns all rows when there is a match in either the left or right table, with NULL values used for non-matching columns.

sql

SELECT Orders.order_id, Customers.customer_name
FROM Orders
FULL OUTER JOIN Customers
ON Orders.customer_id = Customers.customer_id;

Stay tuned for more advanced SQL concepts in our next lessons! 📝 💡 🎯