16. Describe the difference between CHAR and VARCHAR data types

CHAR and VARCHAR both store character strings but manage storage differently.

CHAR (Fixed-length)

• Storage: CHAR(n) uses a fixed length. Every value occupies n characters, padding with spaces if necessary.
• Example: With CHAR(5), storing ABC takes all five slots (ABC ).
• Use cases: Ideal when data length is predictable and consistent.

VARCHAR (Variable-length)

• Storage: VARCHAR(n) uses only as much space as the actual string plus some overhead; no extra padding.
• Example: With VARCHAR(5), storing ABC uses only three characters.
• Use cases: Preferred when data length varies and storage efficiency matters.

Choosing between CHAR and VARCHAR

• Use CHAR for fixed-length data that benefits from uniform storage.
• Use VARCHAR for variable-length data to conserve space.
• In most cases, VARCHAR is favored due to better storage utilization.

17. What is an INDEX and why is it important?

An INDEX is a specialized data structure that speeds up data retrieval from a table. It helps the database locate rows faster, lowering query complexity and improving performance.

Why indexes matter

• Faster lookups: An index creates an ordered structure so the database can quickly find rows based on indexed column values.
• Lower query cost: Searches or sorts on indexed columns avoid scanning the entire table.
• Better sorting and joins: Indexes make ordering and join operations more efficient.
• Optimized WHERE clauses: Indexed columns significantly speed up filtering conditions.

Usage tips

• Create indexes with CREATE INDEX on columns frequently used in WHERE clauses, joins, or ORDER BY.
• Avoid over-indexing; too many indexes increase storage requirements and slow down INSERT/UPDATE/DELETE operations.
• Choose indexes strategically to balance read performance with write overhead.

18. How do you insert a new record into a table?

Use the INSERT INTO statement to add a new row:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

• table_name: the table receiving the new row.
• column1, column2, ...: columns to populate.
• value1, value2, ...: the values to insert.

Example:

INSERT INTO employees (employee_id, employee_name, department_id, salary)
VALUES (101, 'John Doe', 3, 50000);

This inserts a full record into employees. If a column is auto-incremented, omit it:

INSERT INTO employees (employee_name, department_id, salary)
VALUES ('Jane Smith', 2, 60000);

Here the database generates employee_id automatically.

19. Explain the purpose of the LIMIT clause

LIMIT restricts how many rows a query returns. It is useful when you only need a subset of results, which reduces memory usage and speeds up responses.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition
LIMIT row_count;

• SELECT: columns to return.
• FROM: source table.
• WHERE (optional): filter condition.
• LIMIT: maximum number of rows to retrieve.

Example:

-- Fetch the top 5 highest-paid employees
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

This query returns only five rows, ordered by salary from highest to lowest.

20. How do you update data in a table?

Use the UPDATE statement to modify existing rows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• table_name: table to update.
• SET: columns and new values.
• WHERE: condition that selects which rows to change.

Example:

-- Update the salary of employee 101
UPDATE employees
SET salary = 55000
WHERE employee_id = 101;

Always include a WHERE clause to avoid updating every row accidentally. Ensure new values match the columns’ data types and constraints.