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 databaseis 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, likeUsers , Products , or Orders .

The "relational" part is the superpower: you can create relationships between these tables. For example, you can link an Orderto 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 relationaldatabase. 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 SQLdatabase is like a pre-built house where every room has a clear purpose, and all the wiring is fixed. A NoSQLdatabase 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 identifiesevery 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 Keyon 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 keyis the "glue" that creates the relationin 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.