SQL & MySQL Interview Questions 1-5 (Databases 101: Relational, SQL/NoSQL, Keys)

Welcome! As a full-stack developer, the "stack" implies more than just the Python code you write. The other half of the equation is the data. Understanding how data is stored, organized, and retrieved is a critical skill.

This lesson covers the five most fundamental, "must-know" concepts of database design. You don't need to be a database administrator (DBA), but knowing these answers will give you a huge amount of confidence and show that you understand how a complete application is built. We'll use simple analogies and code examples to make sure it all sticks.

1. What is a relational database? Explain with examples.

A relational database is a way of storing information in a highly structured way. The easiest analogy is a collection of linked spreadsheets.

Each "spreadsheet" is called a table. Each table stores a specific type of information, like Users, Products, or Orders.

The "relational" part is the superpower: you can create relationships between these tables. For example, you can link an Order to a specific User. This system is very reliable and ensures your data stays consistent and organized.

2. What is SQL, and how does it differ from NoSQL databases?

This is a fundamental comparison between two major database philosophies.

SQL (Structured Query Language)
SQL is the language you use to communicate with a relational database. It's how you ask questions, like:

-- Get all columns from the 'Users' table
SELECT * FROM Users;

-- Add a new product to the 'Products' table
INSERT INTO Products (name, price) VALUES ('Super Widget', 19.99);

-- Change a user's email
UPDATE Users SET email = 'new.email@example.com' WHERE user_id = 101;

NoSQL (Not Only SQL)
NoSQL is a broad category of databases that do not use the strict table-and-row structure. They were designed for flexibility, speed, and scaling to massive amounts of data (like you'd see at Google or Facebook).

Analogy: A SQL database is like a pre-built house where every room has a clear purpose, and all the wiring is fixed. A NoSQL database is like a box of high-tech Lego bricks; you can build anything, but you're also responsible for the design.

Key Differences:

3. What is a table in a database? Define rows and columns.

This is the most basic building block of a relational database. The spreadsheet analogy works perfectly here.

Here is what that looks like in SQL:

-- This defines the 'Table' and its 'Columns'
CREATE TABLE Users (
    user_id INT,        -- A column for numbers
    username VARCHAR(50), -- A column for text (up to 50 chars)
    email VARCHAR(100),   -- A column for text (up to 100 chars)
    join_date DATE        -- A column for dates
);

-- This creates a 'Row' in the table
INSERT INTO Users (user_id, username, email, join_date) 
VALUES (101, 'alice', 'alice@example.com', '2025-11-17');

4. What is a primary key? How is it different from a unique key?

This is a great question that tests your attention to detail. Both are "constraints" that enforce uniqueness, but they have different purposes.

Primary Key (PK)

A primary key is a column (or columns) that uniquely identifies every single row in a table. It is the row's main "identity."

Analogy: Think of a user_id. It is the one, non-negotiable ID for that user. You can change your name or email, but your user_id (the primary key) should never change.

Unique Key (UK)

A unique key is a constraint on a column that also ensures all values in it are unique. It's used to enforce a business rule, not to be the primary identifier.

Analogy: In our Users table, user_id is the Primary Key. We might also put a Unique Key on the email column. We don't want two users to register with the same email, so this constraint is perfect.

Here is what that looks like in SQL:

CREATE TABLE Users (
    -- The PRIMARY KEY: Cannot be NULL, must be unique.
    -- This is the table's main identifier.
    user_id INT PRIMARY KEY, 
    
    username VARCHAR(50) NOT NULL,
    
    -- The UNIQUE key: Must be unique, but could be NULL.
    -- This enforces a business rule.
    email VARCHAR(100) UNIQUE
);

Summary: Every car has one Primary Key (its VIN number). It can also have several Unique Keys (like a license plate number). Both are unique, but only the VIN is its true, permanent identity.

5. What is a foreign key? Explain with an example.

If the primary key is a table's identity, the foreign key is the "glue" that creates the relation in a relational database.

A foreign key is a column in one table that refers to the primary key of another table. This creates a link between the two tables.

A Perfect, Simple Example

Imagine we have two tables:

In the Posts table, the author_id column is a Foreign Key. It points to the user_id (Primary Key) column in the Users table.

Here is the SQL that creates this relationship:

-- 1. Create the 'Users' table first
CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

-- 2. Create the 'Posts' table
CREATE TABLE Posts (
    post_id INT PRIMARY KEY,
    title VARCHAR(200),
    
    -- This column's type MUST match the type of Users.user_id
    author_id INT, 
    
    -- This is the "glue"
    -- It links 'author_id' in this table
    -- to 'user_id' in the 'Users' table.
    FOREIGN KEY (author_id) REFERENCES Users(user_id)
);

This link allows us to ask powerful questions, like "Get all posts where the author's username is 'alice'."

This also enforces referential integrity. The database will physically prevent you from creating a new post with an author_id of 999, because no user with that ID exists in the Users table. This keeps your data clean and prevents "orphan" records.