Top 50 Placement Questions - Part 4

1. What is the difference between ‘LIKE’ and ’=’ operators?

Answer: = checks for an exact match. LIKE checks for a pattern using wildcards.

The = operator: The value must be exactly the same.

SELECT * FROM users WHERE name = 'Ali';

Only the user named exactly “Ali” matches.

The LIKE operator: Matches a pattern.

Two wildcards:

• % — any number of characters (including none).
• _ — exactly one character.

SELECT * FROM users WHERE name LIKE 'A%';

Matches names that start with A: “Ali”, “Anna”, “Adam”.

SELECT * FROM users WHERE name LIKE '_li';

Matches any 3-letter name ending in “li”: “Ali”, “Eli”.

Key differences table:

	=	LIKE
Match type	Exact	Pattern
Wildcards	No	Yes (% and _)
Use for	Exact lookups	Searching/filtering text

Key takeaway: Use = for exact values. Use LIKE with % or _ when you need pattern matching.

2. What is a Database Transaction Isolation Level?

Answer: An isolation level controls how one transaction sees changes made by others running at the same time.

The problem: Two transactions running together can interfere.

The isolation level decides how much they see of each other’s unfinished work.

The four standard levels (from weakest to strongest):

1. Read Uncommitted: Transactions can see each other’s uncommitted changes.

Fastest, most problems.

2. Read Committed: Only committed changes are visible.

Prevents “dirty reads”.

3. Repeatable Read: Once a row is read, it stays the same during the transaction.

4. Serializable: Transactions run as if one after another.

Safest, slowest.

Key differences table:

Level	Sees uncommitted data?	Prevents dirty reads?	Speed
Read Uncommitted	Yes	No	Fastest
Read Committed	No	Yes	Fast
Repeatable Read	No	Yes	Slower
Serializable	No	Yes	Slowest

Key takeaway: Higher isolation = safer but slower. Lower isolation = faster but riskier. Pick based on how much concurrency your app can tolerate.

3. What is the purpose of the ‘GROUP BY’ clause?

Answer: GROUP BY groups rows that share the same values, usually so you can run an aggregate function on each group.

The idea: Instead of one number for the whole table, you get one number per group.

Example — count employees per department:

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

Employees table:

department_id
10
10
20

Result:

department_id	COUNT(*)
10	2
20	1

Rules:

• Every column in SELECT must either be in GROUP BY or be an aggregate.
• To filter groups, use HAVING, not WHERE.

Key takeaway: GROUP BY splits rows into groups, then aggregates run per group. Pair it with COUNT, SUM, AVG, MIN, or MAX.

4. What is a ‘Natural Join’?

Answer: A natural join joins two tables automatically using columns that share the same name.

The idea: You don’t write the ON condition.

The database matches columns with the same name in both tables.

Example: Both tables have department_id:

SELECT * FROM employees NATURAL JOIN departments;

The database joins on department_id automatically.

The risk: If both tables have other same-named columns, the join uses all of them.

If a column changes name, the join silently changes behavior.

Most developers prefer explicit joins with ON.

Key takeaway: A natural join auto-matches same-named columns. It’s short but fragile — explicit joins are safer.

5. What does the ‘LIMIT’ (or ‘TOP’) clause do?

Answer: LIMIT restricts the number of rows returned by a query.

The idea: You don’t always want all rows — sometimes just the first few.

MySQL / PostgreSQL:

SELECT * FROM employees LIMIT 10;

Returns only the first 10 rows.

SQL Server:

SELECT TOP 10 * FROM employees;

With OFFSET (pagination):

SELECT * FROM employees LIMIT 10 OFFSET 20;

Skips the first 20 rows, then returns 10. Great for page 3 of results.

Key takeaway: LIMIT/TOP caps the row count. Combine with OFFSET for pagination.

6. What is the difference between a ‘Hard Delete’ and a ‘Soft Delete’?

Answer: A hard delete removes the row permanently. A soft delete marks the row as inactive while keeping the data.

Hard delete: Uses the DELETE command.

The row is gone forever.

DELETE FROM users WHERE id = 5;

Soft delete: Uses an UPDATE to set a flag.

The row stays in the table but is treated as deleted.

UPDATE users SET is_deleted = 1 WHERE id = 5;

Queries must filter out deleted rows:

SELECT * FROM users WHERE is_deleted = 0;

Why soft delete?

• Audit trail — you can see what was deleted and when.
• Recovery — restoring is just setting the flag back.
• Data that other tables reference stays intact.

Key differences table:

	Hard delete	Soft delete
Command	DELETE	UPDATE
Data kept	No	Yes
Recoverable	No	Yes
Extra query filtering	No	Yes

Key takeaway: Hard delete removes data permanently; soft delete hides it with a flag. Use soft delete when you need an audit trail or recoverability.

7. What is a ‘Database Sequence’?

Answer: A sequence is a database object that generates a series of unique numbers.

The idea: It’s like a counter the database manages.

Each time you ask, it gives the next number.

Example:

CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;

Get the next value:

SELECT NEXT VALUE FOR order_seq;

Returns 1000, then 1001, then 1002…

How it differs from an identity column:

• An identity column is tied to one table’s column.
• A sequence is standalone — several tables can share one.
• A sequence gives more control: start value, step, and reuse.

Key takeaway: A sequence is a shared, controllable number generator. Use it for primary keys when you need more flexibility than an identity column.

8. What happens if you perform a SELECT on a table that has no data?

Answer: The query succeeds and returns an empty result set — zero rows.

The behavior: No error.

No NULL row.

Just an empty result.

SELECT * FROM employees;

If employees is empty, the result is an empty table.

Why this matters: Your code must handle “no rows found” as a normal case.

For example, a COUNT(*) returns 0, not an error.

Key takeaway: A SELECT on an empty table returns zero rows, not an error. Always handle the empty-result case in your code.

9. What is the difference between ‘ANY’ and ‘ALL’ operators in SQL subqueries?

Answer: ANY returns TRUE if the condition holds for at least one value from the subquery. ALL returns TRUE only if it holds for every value.

ANY — at least one:

SELECT name FROM employees
WHERE salary > ANY (SELECT salary FROM managers);

Returns employees earning more than at least one manager.

ALL — every single one:

SELECT name FROM employees
WHERE salary > ALL (SELECT salary FROM managers);

Returns employees earning more than every manager.

The difference in one example: If managers earn 100, 200, 300:

• > ANY → TRUE if the employee earns more than 100.
• > ALL → TRUE only if the employee earns more than 300.

Key takeaway: ANY = at least one value. ALL = every value. Choose based on whether one match is enough or all must match.

10. What is a ‘Database User’ vs. a ‘Database Role’?

Answer: A user is an individual account that logs in. A role is a collection of permissions that can be assigned to many users.

User: An account used to connect to the database.

Each user logs in and gets the permissions they’ve been given.

Role: A named bundle of privileges.

Instead of granting the same rights to 50 users one by one, you grant them once to a role, then assign users to the role.

Example:

CREATE ROLE analyst;
GRANT SELECT ON employees TO analyst;
GRANT analyst TO ali;
GRANT analyst TO bob;

Now both Ali and Bob can SELECT on employees — no repeated grants.

Why roles are better:

• Manage permissions in one place.
• Add or remove a whole group’s access at once.
• New users get the right access just by joining the role.

Key differences table:

	User	Role
What it is	An account	A permission bundle
Can log in	Yes	No
Assigned to	Individuals	Users or other roles

Key takeaway: Users are accounts; roles are reusable permission bundles. Use roles so you grant permissions once and assign many users to them.