Intermediate SQL Interview Questions for Freshers / Entry Level

Intermediate-level SQL interview questions for Freshers / Entry Level positions.

1. What is a foreign key and why is it used?

Answer: A foreign key is a column that references the primary key of another table. It enforces referential integrity (can't insert invalid references, can't delete referenced rows). Creates relationships between tables (one-to-many, many-to-many).

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

-- This fails if department 999 doesn't exist:
INSERT INTO employees (id, name, department_id) VALUES (1, 'John', 999);

2. Explain UNION vs UNION ALL.

Answer: Both combine result sets from multiple queries. UNION removes duplicates (slower, requires sorting). UNION ALL keeps all rows including duplicates (faster). Use UNION ALL when you know there are no duplicates or need all rows.

-- UNION: Removes duplicates
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- UNION ALL: Keeps duplicates (faster)
SELECT email FROM active_users
UNION ALL
SELECT email FROM newsletter_subscribers;

-- Requirements: Same number of columns, compatible types
-- Column names come from first query