SQL Basics, Operators & Constraints

1. What are Primary and Foreign Keys?

A Primary Key uniquely identifies each row in a table. A Foreign Key is a column that points to a primary key in another table, keeping the relationship between tables valid.

Primary Key: It must be unique and can never be empty.

No two rows can have the same value.

A table has only one primary key.

It identifies each row, like an ID card identifies a person.

Foreign Key: It’s a column in one table that references the primary key of another table.

It makes sure every reference points to a row that really exists.

So you can’t create an order for a customer who isn’t there.

Example — two linked tables:

Departments table:

department_id	department_name
10	Sales
20	IT

Employees table:

employee_id	name	department_id
1	Ali	10
2	Bob	20

Here departments.department_id is the primary key of the Departments table.

employees.department_id is a foreign key pointing to it.

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,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

What the foreign key prevents: You cannot insert an employee with department_id = 99 because no such department exists.

The database rejects the insert and keeps the relationship consistent.

Key differences table:

	Primary Key	Foreign Key
Purpose	Uniquely identifies a row	Links to another table
Unique	Yes	No
Can be NULL	No	Yes
Per table	One	Many

Key takeaway: Primary keys make each row addressable. Foreign keys make the relationships between tables trustworthy.

2. What is a Primary Key, and how does it differ from a Unique Key?

A Primary Key uniquely identifies each row and can never be NULL. A Unique Key also ensures uniqueness but allows one NULL value.

Primary Key: It’s the main identifier of a row, like an ID card.

It must be unique.

It can never be empty.

A table has only one primary key.

Unique Key: It also makes sure values don’t repeat.

But it allows one NULL.

A table can have many unique keys.

Example:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,          -- primary key
  email VARCHAR(255) UNIQUE,            -- unique key
  phone VARCHAR(20) UNIQUE              -- another unique key
);

Here employee_id can never be NULL and never repeats.

email can’t repeat, but one employee could have no email (NULL).

Key differences table:

	Primary Key	Unique Key
Purpose	Main row identifier	Prevent duplicate values
Can be NULL	No	Yes (one NULL)
Per table	One	Many
Automatically indexed	Yes	Yes

Key takeaway: Both prevent duplicates, but the primary key is special — it’s the table’s main identifier, never NULL, and only one per table.

3. What is a Foreign Key?

A foreign key is a column that references the primary key of another table. It links two tables and keeps the relationship valid.

The idea: One table’s column “points” to another table’s primary key.

This makes sure every reference exists.

Example: Departments:

department_id	department_name
10	Sales
20	IT

Employees:

employee_id	name	department_id
1	Ali	10
2	Bob	20

employees.department_id is a foreign key pointing to departments.department_id.

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

What it prevents: You can’t insert an employee with department_id = 99 if no such department exists.

The database rejects it, keeping data consistent.

Key takeaway: A foreign key is a reference from one table to another’s primary key. It stops orphan rows — data that points to nothing.

4. What are Aggregate Functions in SQL?

Aggregate functions take many values and return a single result. Common ones are SUM, AVG, COUNT, MIN, and MAX.

The functions:

Function	What it does	Example
SUM()	Adds values	Total salary
AVG()	Average	Average salary
COUNT()	Number of rows	Total employees
MIN()	Smallest value	Lowest salary
MAX()	Largest value	Highest salary

Example:

SELECT AVG(salary) AS average_salary,
       MAX(salary) AS highest_salary
FROM employees;

If salaries are 50000, 60000, 70000:

• average = 60000
• highest = 70000

With GROUP BY: Aggregates are often used per group.

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

Key takeaway: Aggregate functions summarize many rows into one value. Pair them with GROUP BY to summarize per group.

5. What is a NULL value?

NULL means the absence of a value. It is not zero and not an empty string.

The confusion: Zero is a number.

An empty string '' is a value that happens to be empty.

NULL means “no value / unknown”.

Example:

employee_id	name	phone
1	Ali	12345
2	Bob	(NULL)

Bob’s phone is NULL — the number is unknown, not zero.

How to check for NULL: You can’t use = NULL. That never matches.

SELECT * FROM employees WHERE phone IS NULL;

Key takeaway: NULL means unknown or missing, distinct from 0 and ''. Always test it with IS NULL, not = NULL.

6. What is the purpose of the COALESCE() function?

COALESCE() returns the first non-NULL value from a list of arguments.

The problem it solves: Columns often contain NULL, meaning missing data.

You want to show a fallback value instead.

Example:

SELECT name, COALESCE(phone, 'No phone') FROM customers;

Customers table:

name	phone
Ali	12345
Bob	(NULL)

Result:

name	phone
Ali	12345
Bob	No phone

Bob’s NULL became the fallback “No phone”.

Key takeaway: COALESCE() checks its arguments left to right and returns the first one that isn’t NULL. Perfect for defaulting missing values.

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

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.

8. What is a CHECK constraint?

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.

9. What is the purpose of the DISTINCT keyword?

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.

10. What is an Alias in SQL?

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.

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

= 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.

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

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.

13. What is a ‘Natural Join’?

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.

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

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.

15. What is a ‘Database Sequence’?

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.

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

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.

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

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.

18. What is the CASE expression and how do you use it?

CASE is SQL’s if-then-else — it returns a value based on conditions, without needing a separate statement. Two syntaxes:

-- Simple CASE (compare one expression to values)
SELECT name,
  CASE grade
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Good'
    ELSE 'Needs improvement'
  END AS feedback
FROM students;

-- Searched CASE (any conditions)
SELECT name, salary,
  CASE
    WHEN salary > 100000 THEN 'High'
    WHEN salary > 50000  THEN 'Medium'
    ELSE 'Low'
  END AS band
FROM employees;

Key points for interviews:

• CASE is an expression, not a statement — it can appear anywhere an expression can: in SELECT, WHERE, ORDER BY, and even inside GROUP BY and JOIN conditions.
• Conditions are evaluated top to bottom; the first match wins, ELSE is the fallback (defaults to NULL if omitted).
• It’s the standard way to pivot/flag data — e.g. SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) to count paid orders.

The interview answer: CASE lets you embed conditional logic inside a query, which is essential for labels, bands, pivots, and conditional aggregation — the kind of thing you’d otherwise need application code for.