11. Differentiate INNER JOIN and OUTER JOIN

INNER JOIN and OUTER JOIN are distinct join types with different behaviors:

INNER JOIN

• Key trait: Returns rows only when the join condition matches in both tables.
• Result: Contains rows that have matching values in both tables.
• Syntax: SELECT ... FROM table1 INNER JOIN table2 ON condition;

OUTER JOIN (LEFT, RIGHT, FULL)

• Key trait: Returns rows from at least one table even if the join condition fails.
• Result types:
  • LEFT JOIN (or LEFT OUTER JOIN): All rows from the left table plus matching rows from the right table.
  • RIGHT JOIN (or RIGHT OUTER JOIN): All rows from the right table plus matching rows from the left table.
  • FULL JOIN (or FULL OUTER JOIN): Every row from both tables, whether or not there is a match.
• Syntax: SELECT ... FROM table1 LEFT|RIGHT|FULL JOIN table2 ON condition;

Example:

-- INNER JOIN
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- LEFT JOIN
SELECT c.customer_id, c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

The INNER JOIN returns rows only where both tables have matching departments. The LEFT JOIN returns all customers, plus their orders when they exist.

12. How is DISTINCT used?

DISTINCT removes duplicate rows from query results so only unique values appear in the selected columns.

Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name
WHERE condition;

• SELECT DISTINCT: chooses the columns to return unique values for.
• FROM: identifies the source table.
• WHERE (optional): filters rows before deduplication.

Example:

-- Retrieve unique departments
SELECT DISTINCT department
FROM employees;

This query lists every department exactly once, eliminating duplicates.

13. Explain database normalization

Normalization is the process of structuring a database to reduce data redundancy and improve data integrity. It helps maintain consistent, efficient data storage.

Common normal forms include First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), Boyce-Codd Normal Form (BCNF), Fourth Normal Form (4NF), and Fifth Normal Form (5NF).

Key normal forms

• 1NF: Each cell holds a single value, and column values share the same data type.
• 2NF: The table is in 1NF, and every non-key column depends fully on the entire primary key.
• 3NF: The table is in 2NF, and non-key columns do not depend on other non-key columns (no transitive dependency).
• BCNF: A stronger version of 3NF ensuring every determinant is a candidate key.

Benefits

• Less redundancy: Store each piece of data once, minimizing duplicates and saving space.
• Consistency: Well-structured tables reduce the risk of inconsistent data.
• Better performance: Properly normalized schemas can improve query efficiency.

Over-normalizing may introduce too many joins and hurt performance, so balance normalization with practical requirements.

14. What is a PRIMARY KEY?

In a database table, a PRIMARY KEY is a column or set of columns whose values uniquely identify each row. It enforces uniqueness and ensures every record is distinguishable.

Characteristics

• Unique: No two rows share the same primary key value.
• Not null: Primary key columns cannot contain NULL.
• Identifier: Serves as the definitive way to reference each record.

Syntax

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

For composite keys:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    PRIMARY KEY (column1, column2)
);

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT
);

employee_id uniquely identifies each row in employees.

15. What is a FOREIGN KEY and how is it used?

A FOREIGN KEY is a column (or set of columns) in one table that references a primary key or unique key in another table. It defines relationships between tables and enforces referential integrity.

Key roles

• Establish relationships: Links rows in the child table to rows in the parent table.
• Preserve integrity: Prevents orphaned records by requiring referenced values to exist in the parent table.

Syntax

CREATE TABLE child_table (
    column1 datatype,
    foreign_key_column datatype,
    FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_key_column)
);

• foreign_key_column: column in the child table that stores the reference.
• parent_table: table that owns the referenced primary key.
• parent_key_column: primary key column in the parent table.

Example:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Here, department_id in employees references department_id in departments, linking each employee to a department and enforcing valid relationships.