MySQL: Relational Database Management

1. What is the difference between CHAR and VARCHAR?

Answer: CHAR is fixed-length, padding with spaces (e.g., CHAR(10) always uses 10 bytes). VARCHAR is variable-length, storing only the actual data (e.g., VARCHAR(10) uses less space for shorter strings).

Example:

CREATE TABLE test (
    char_col CHAR(10),
    varchar_col VARCHAR(10)
);

INSERT INTO test VALUES ('abc', 'abc');
-- char_col: 'abc       ', varchar_col: 'abc'
                        

2. How does PRIMARY KEY differ from UNIQUE KEY?

Answer: PRIMARY KEY uniquely identifies rows, allows no NULLs, and only one per table. UNIQUE KEY ensures unique values, allows NULLs, and multiple per table.

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(50) UNIQUE
);
                        

3. What is the purpose of GROUP BY?

Answer: GROUP BY groups rows with the same values in specified columns into summary rows, often used with aggregate functions like COUNT, SUM.

SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department;
                        

4. What are the benefits of indexing?

Answer: Indexes speed up query performance by allowing faster data retrieval. However, they increase storage and slow down write operations (INSERT/UPDATE).

CREATE INDEX idx_salary ON employees(salary);
                        

5. How do you optimize a slow MySQL query?

Answer: Use EXPLAIN to analyze query execution, add indexes on frequently queried columns, avoid SELECT *, and optimize JOINs by ensuring proper indexing.

EXPLAIN SELECT * FROM employees WHERE salary > 50000;
                        

6. What is a foreign key, and why is it important?

Answer: A FOREIGN KEY is a field in one table that uniquely identifies a row in another table, enforcing referential integrity.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
                        

7. What is the difference between UNION and UNION ALL?

Answer: UNION combines result sets and removes duplicates, while UNION ALL combines result sets without removing duplicates, making it faster.

SELECT name FROM employees
UNION
SELECT name FROM contractors;
                        

8. What is a self-join, and when is it used?

Answer: A self-join joins a table with itself, useful for hierarchical data (e.g., employees and their managers).

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;
                        

1. What does this query do?

SELECT department, AVG(salary)
FROM employees
GROUP BY department;
                        

Answer: Calculates average salary per department.

Explanation: GROUP BY groups rows by department, and AVG computes the average salary for each group.

2. What is wrong with this query?

DELETE FROM employees;
                        

Answer: Deletes all rows without a WHERE clause.

Explanation: Without WHERE, all data in the table is deleted, which may be unintentional.

3. What does this query return?

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
                        

Answer: All employees with their department names, including those without a department.

Explanation: LEFT JOIN includes all rows from employees, with NULL for unmatched departments.

4. What is the purpose of this statement?

CREATE INDEX idx_salary ON employees(salary);
                        

Answer: Creates an index to speed up salary-based queries.

Explanation: Indexes improve performance for WHERE and ORDER BY clauses on the salary column.

5. What does this trigger do?

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
SET NEW.last_updated = NOW();
                        

Answer: Sets last_updated to current timestamp before updating a row.

Explanation: The trigger automatically updates the last_updated column for each row modified.

6. What does this stored procedure do?

DELIMITER //
CREATE PROCEDURE GetHighEarners()
BEGIN
    SELECT name, salary
    FROM employees
    WHERE salary > 50000;
END //
DELIMITER ;
                        

Answer: Retrieves employees with salary greater than 50,000.

Explanation: The procedure encapsulates a query for reuse, improving maintainability.

7. What is the output of this query?

SELECT name
FROM employees
WHERE salary > 50000
ORDER BY salary DESC
LIMIT 1;
                        

Answer: Returns the name of the highest-paid employee with salary > 50,000.

Explanation: ORDER BY salary DESC sorts in descending order, and LIMIT 1 returns only the top result.