Top 50 Placement Questions - Part 3

1. What is the difference between DDL, DML, and DCL?

Answer: DDL defines the structure of the database. DML manages the data inside it. DCL manages access and permissions.

DDL — Data Definition Language: Deals with the structure: tables, schemas, indexes.

Examples: CREATE, ALTER, DROP.

CREATE TABLE employees (id INT PRIMARY KEY);

DML — Data Manipulation Language: Deals with the data inside tables.

Examples: INSERT, UPDATE, DELETE, SELECT.

INSERT INTO employees (id) VALUES (1);

DCL — Data Control Language: Deals with permissions.

Examples: GRANT, REVOKE.

GRANT SELECT ON employees TO analyst;

Key differences table:

	DDL	DML	DCL
Works on	Structure	Data	Access
Examples	CREATE, ALTER, DROP	INSERT, UPDATE, DELETE	GRANT, REVOKE
Common	Schema design	Everyday work	Admin work

Key takeaway: DDL shapes the tables, DML works the data, DCL controls who can do what.

2. What is a ‘Cartesian Product’ in SQL?

Answer: A Cartesian product is the result of a join without a condition — every row of one table combined with every row of another.

How it happens: When a JOIN (or comma-separated tables) has no ON condition, every possible pair is produced.

Example:

Employees: {Ali, Bob}

Departments: {Sales, IT, HR}

SELECT * FROM employees, departments;

Result: 2 × 3 = 6 rows — every employee paired with every department.

Why it’s dangerous: The result grows as a product.

1,000 × 1,000 = 1,000,000 rows.

Usually a mistake that floods the output.

Key takeaway: A Cartesian product combines every row with every row. Without a join condition, the row count multiplies fast.

3. What are the possible values for a BOOLEAN field in SQL?

Answer: A boolean field can hold TRUE, FALSE, and NULL.

The three states:

• TRUE
• FALSE
• NULL — meaning unknown

Why NULL? A boolean often represents an unknown state, not just yes/no.

For example, “is the user verified?” — a new user may be neither verified nor unverified, just unknown.

Database differences:

• Some databases use 0 and 1 for FALSE and TRUE.
• PostgreSQL uses the SQL standard TRUE/FALSE.
• MySQL uses 0/1 internally but accepts TRUE/FALSE.

Key takeaway: A boolean has three possible values: TRUE, FALSE, and NULL for unknown. Some databases store them as 0 and 1.

4. What is a CHECK constraint?

Answer: A CHECK constraint is a rule that limits the values allowed in a column.

The idea: You define a condition.

The database rejects any insert or update that breaks it.

Example — age must be 18 or older:

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT CHECK (age >= 18)
);

Now inserting age 15 fails.

Inserting age 20 works.

Why it’s better than app checks: The rule lives in the database.

Every app and every query obeys it — no one can bypass it.

Key takeaway: A CHECK constraint enforces a column rule at the database level. It’s the strongest way to guarantee valid data.

5. What is the purpose of the DISTINCT keyword?

Answer: DISTINCT removes duplicate values from the result of a SELECT.

The problem: A column can contain repeating values.

Sometimes you want only the unique ones.

Example: Employees table:

department_id
10
20
10

Without DISTINCT:

SELECT department_id FROM employees;

Result: 10, 20, 10 (with duplicates).

With DISTINCT:

SELECT DISTINCT department_id FROM employees;

Result: 10, 20 (unique only).

Key takeaway: DISTINCT returns only unique values, dropping duplicates from the output.

6. What is a ‘Database Schema’?

Answer: A database schema is the blueprint of the database — its tables, columns, relationships, and constraints.

The idea: It’s the design document that defines how data is organized.

It doesn’t hold the data itself; it describes the structure.

What a schema defines:

• Tables
• Columns and their types
• Primary and foreign keys
• Constraints and indexes

Example of a tiny schema:

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

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100),
  department_id INT REFERENCES departments(department_id)
);

Key takeaway: A schema is the structure of the database: what tables exist, what’s in them, and how they connect.

7. What is an Alias in SQL?

Answer: An alias is a temporary name given to a table or column to make queries clearer.

The idea: You rename something just for the query’s output or for writing the query.

The original name in the database doesn’t change.

Column alias:

SELECT salary * 1.1 AS bonus FROM employees;

The output column is called bonus.

Table alias:

SELECT e.name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

e and d are short names for the two tables. The AS keyword is optional for table aliases.

Why use aliases:

• Shorter, cleaner queries.
• Required when a query uses the same table twice (self-join).
• Rename output columns for readability.

Key takeaway: Aliases are temporary names for columns or tables — clearer output and cleaner queries, especially in joins and self-joins.

8. What is the difference between a Clustered Index and a Table?

Answer: A table is the actual data. A clustered index is how that data is physically ordered.

The table: Holds the real rows and columns.

Think of it as the content.

The clustered index: Determines the physical order the rows are stored in on disk.

Think of it as the arrangement.

The analogy: A dictionary’s content is the table.

Its alphabetical arrangement is the clustered index.

If you change the arrangement, the content stays the same — just stored differently.

Key point: The clustered index is built on the table. A table can have only one physical order, so only one clustered index.

Key takeaway: The table stores data; the clustered index orders that data. They’re separate concepts tied to the same physical storage.

9. What is a ‘Full Table Scan’?

Answer: A full table scan is when the database reads every row in a table to find the requested data.

When it happens:

• No useful index exists on the column.
• The query condition doesn’t match any index.
• The database decides scanning everything is faster than using an index.

Why it’s slow: On a table with millions of rows, reading every row takes time.

How to fix it: Add an index on the filtered column.

CREATE INDEX idx_emp_dept ON employees(department_id);

Now a lookup on department_id uses the index instead of scanning.

Key takeaway: A full table scan reads everything. Indexes let the database skip it — that’s their whole purpose.

10. What is an ‘Identity’ or ‘Auto-Increment’ column?

Answer: An identity column automatically generates a unique number for each new row.

The idea: You don’t provide the value.

The database assigns the next number automatically.

Example:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

Inserts:

INSERT INTO users (name) VALUES ('Ali');
INSERT INTO users (name) VALUES ('Bob');

Ali gets id 1, Bob gets id 2 — automatically.

Why use it:

• No manual numbering.
• Always unique.
• Great as a surrogate key.

Database names:

• MySQL: AUTO_INCREMENT
• PostgreSQL: SERIAL or IDENTITY
• SQL Server: IDENTITY

Key takeaway: An identity column numbers rows automatically — perfect for primary keys where you never want to pick the number yourself.