Junior SQL & Databases Interview Questions (50 MCQs)

Question 1

Which properties always hold for a primary key?

Accepted Answer

It is unique and cannot be `NULL`

Answer

It is unique but may be `NULL`

Answer

It must always be an auto-increment number

Answer

A table may have several independent primary keys

Question 2

What is the main job of a foreign key?

Accepted Answer

Enforce referential integrity by referencing another table's key

Answer

Automatically speed up every query

Answer

Guarantee the column value is unique

Answer

Encrypt sensitive columns

Question 3

If `orders.user_id` references `users.id` with `ON DELETE CASCADE` and a user is deleted, what happens?

Accepted Answer

That user's orders are deleted automatically

Answer

Deleting the user fails with an error

Answer

The orders' `user_id` is set to `NULL`

Answer

The orders stay unchanged as orphans

Question 4

In PostgreSQL, can a `UNIQUE` column contain multiple rows with `NULL`?

Accepted Answer

Yes, because `NULL`s are not considered equal to each other

Answer

No, only one `NULL` is allowed

Answer

No, `UNIQUE` implies `NOT NULL` as well

Answer

Only if the column is a text type

Question 5

What does `CHECK (price >= 0)` on a table do?

Accepted Answer

Rejects inserts or updates with a negative price

Answer

Automatically converts negative prices to zero

Answer

Only warns but still stores the data

Answer

Makes `WHERE price < 0` queries faster

Question 6

What rows does an `INNER JOIN` between `users` and `orders` on `users.id = orders.user_id` return?

Accepted Answer

Only users that have at least one matching order

Answer

All users, even those without orders

Answer

All orders and all users

Answer

Every possible combination of user and order

Question 7

In `users LEFT JOIN orders`, what is returned for a user with no orders?

Accepted Answer

One row with the `orders` columns set to `NULL`

Answer

No row for that user

Answer

One row with `orders` columns as zero or empty string

Answer

A query execution error

Question 8

Which query finds users that have no orders?

Accepted Answer

`SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL`

Answer

`SELECT u.* FROM users u INNER JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL`

Answer

`SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id = NULL`

Answer

`SELECT u.* FROM users u CROSS JOIN orders o WHERE o.user_id <> u.id`

Question 9

`A RIGHT JOIN B` is equivalent to which?

Accepted Answer

`B LEFT JOIN A`

Answer

`A LEFT JOIN B`

Answer

`A INNER JOIN B`

Answer

`A FULL JOIN B`

Question 10

What does a `FULL OUTER JOIN` return?

Accepted Answer

Matched rows plus unmatched rows from both sides padded with `NULL`

Answer

Only rows that match in both tables

Answer

The Cartesian product of both tables

Answer

Only the unmatched rows from both sides

Question 11

If `sizes` has 3 rows and `colors` has 4, how many rows does `SELECT * FROM sizes CROSS JOIN colors` return?

Accepted Answer

12

Answer

7

Answer

4

Answer

3

Question 12

`employees` has `manager_id` pointing to `id` in the same table. What do you need to show each employee's name next to their manager's name?

Accepted Answer

A self join of the table with itself using two aliases

Answer

A `CROSS JOIN` with another table

Answer

Just `GROUP BY manager_id`

Answer

Creating a separate `managers` table is mandatory

Question 13

What is the main difference between `WHERE` and `HAVING`?

Accepted Answer

`WHERE` filters rows before grouping; `HAVING` filters groups after it

Answer

`HAVING` is always faster than `WHERE`

Answer

`WHERE` only works with aggregates

Answer

They are identical and interchangeable

Question 14

Why does `SELECT dept, COUNT(*) FROM emp WHERE COUNT(*) > 5 GROUP BY dept` fail?

Accepted Answer

Aggregates aren't allowed in `WHERE`; `HAVING` must be used

Answer

`COUNT(*)` must be `COUNT(dept)`

Answer

`GROUP BY` must come before `FROM`

Answer

`COUNT` can't appear in `SELECT`

Question 15

In PostgreSQL, what happens with `SELECT dept, name, COUNT(*) FROM emp GROUP BY dept` (when `dept` is not a key)?

Accepted Answer

Error: `name` must appear in `GROUP BY` or inside an aggregate

Answer

Returns a random `name` for each dept

Answer

Returns the alphabetically first `name` for each dept

Answer

Concatenates all names with commas

Question 16

What is the result of `NULL = NULL` in SQL?

Accepted Answer

`NULL` (unknown)

Answer

`TRUE`

Answer

`FALSE`

Answer

A type error

Question 17

A table has 10 rows and `email` is `NULL` in 3 of them. What does `SELECT COUNT(*), COUNT(email)` return?

Accepted Answer

`10, 7`

Answer

`10, 10`

Answer

`7, 7`

Answer

`7, 10`

Question 18

Column `score` has values `10`, `20`, and `NULL`. What is `AVG(score)`?

Accepted Answer

`15`

Answer

`10`

Answer

`NULL`

Answer

`30`

Question 19

If the subquery returns at least one `NULL`, what does `WHERE id NOT IN (SELECT user_id FROM orders)` do?

Accepted Answer

Returns no rows

Answer

Ignores the `NULL` and works correctly

Answer

Returns all rows

Answer

Raises a runtime error

Question 20

What does `COALESCE(nickname, first_name, 'Guest')` return?

Accepted Answer

The first non-`NULL` argument from the left

Answer

The last non-`NULL` argument

Answer

Concatenates all arguments

Answer

Always returns `'Guest'`

Question 21

What does `SELECT DISTINCT city, country FROM users` return?

Accepted Answer

Unique `(city, country)` combinations

Answer

Unique cities, with the country of each city's first row

Answer

Only unique countries

Answer

An error, because `DISTINCT` only works on one column

Question 22

To count the number of distinct customers who placed orders, which is correct?

Accepted Answer

`SELECT COUNT(DISTINCT customer_id) FROM orders`

Answer

`SELECT DISTINCT COUNT(customer_id) FROM orders`

Answer

`SELECT COUNT(*) FROM orders GROUP BY customer_id`

Answer

`SELECT COUNT(customer_id) FROM orders`

Question 23

Without `ORDER BY`, what is the order of rows from `SELECT * FROM users`?

Accepted Answer

Not guaranteed and may change between runs

Answer

Always ascending by primary key

Answer

Always in insertion order

Answer

Always alphabetical by the first column

Question 24

Which rows does `SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 20` return?

Accepted Answer

Rows 21 to 30 of the newest posts

Answer

Rows 20 to 30 of the newest posts

Answer

Rows 11 to 20 of the newest posts

Answer

The first 20 rows except the first 10

Question 25

In PostgreSQL, with `ORDER BY last_login DESC`, where do rows with `NULL` `last_login` appear?

Accepted Answer

At the start, unless you write `NULLS LAST`

Answer

At the end, always

Answer

They are excluded from the result

Answer

Randomly among the other rows

Question 26

What is the difference between `UNION` and `UNION ALL`?

Accepted Answer

`UNION` removes duplicates; `UNION ALL` keeps all rows

Answer

`UNION ALL` removes duplicates; `UNION` doesn't

Answer

`UNION` puts columns side by side; `UNION ALL` stacks rows

Answer

`UNION ALL` sorts the result

Question 27

What is required to combine two queries with `UNION`?

Accepted Answer

Same number of columns with compatible data types

Answer

Column names must be exactly identical

Answer

Both queries must read from the same table

Answer

Both queries must have `ORDER BY`

Question 28

What does `SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products)` return?

Accepted Answer

Products priced above the overall average

Answer

The most expensive product

Answer

The average price of each product

Answer

An error, aggregates can't be used in subqueries

Question 29

When is `EXISTS (subquery)` `TRUE`?

Accepted Answer

When the subquery returns at least one row

Answer

When the subquery returns exactly one row

Answer

When the subquery's returned value is `TRUE`

Answer

When the subquery contains no `NULL`s

Question 30

What is the main goal of normalization?

Accepted Answer

Reduce redundancy and prevent update anomalies

Answer

Speed up every read query

Answer

Reduce the number of tables

Answer

Remove the need for indexes

Question 31

Storing `'red,green,blue'` in a single `colors` column violates which normal form?

Accepted Answer

1NF (atomic values)

Answer

2NF

Answer

3NF

Answer

BCNF

Question 32

How is a many-to-many relationship between `students` and `courses` usually modeled?

Accepted Answer

With a junction table like `enrollments` holding two foreign keys

Answer

With one foreign key in `students`

Answer

With an array column in both tables

Answer

By merging both tables into one

Question 33

What does ACID stand for?

Accepted Answer

Atomicity, Consistency, Isolation, Durability

Answer

Availability, Consistency, Isolation, Distribution

Answer

Atomicity, Concurrency, Integrity, Durability

Answer

Accuracy, Consistency, Indexing, Durability

Question 34

In a money transfer, if the server crashes after debiting account A but before crediting B, which ACID property prevents inconsistency?

Accepted Answer

Atomicity

Answer

Isolation

Answer

Durability

Answer

Consistency alone

Question 35

What does Durability in ACID guarantee?

Accepted Answer

Committed data survives crashes or power loss

Answer

Data can never be deleted

Answer

Transactions never interfere with each other

Answer

All replicas are updated instantly

Question 36

After `BEGIN; UPDATE accounts SET balance = 0; ROLLBACK;`, what is `balance`?

Accepted Answer

The same value as before `BEGIN`

Answer

`0` for all accounts

Answer

`NULL`

Answer

`0` only for the first row

Question 37

In PostgreSQL, what happens if you run an `UPDATE` without `BEGIN`?

Accepted Answer

It runs in its own implicit transaction and auto-commits

Answer

It stays pending until an explicit `COMMIT`

Answer

It runs with no atomicity guarantee at all

Answer

It errors because a transaction is required

Question 38

What is the main cost of adding an index to a table?

Accepted Answer

More storage and slower `INSERT`/`UPDATE`/`DELETE`

Answer

All `SELECT`s become slower

Answer

Losing the table's constraints

Answer

No longer being able to use `ORDER BY`

Question 39

What index type does `CREATE INDEX idx ON users(email)` create by default in PostgreSQL?

Accepted Answer

B-tree

Answer

Hash

Answer

GIN

Answer

Bitmap

Question 40

In PostgreSQL, which constraints automatically create an index?

Accepted Answer

`PRIMARY KEY` and `UNIQUE`

Answer

`PRIMARY KEY` and `FOREIGN KEY`

Answer

All constraints including `CHECK`

Answer

Only `FOREIGN KEY`

Question 41

With a B-tree index on `name`, which condition usually cannot use the index?

Accepted Answer

`WHERE name LIKE '%son'`

Answer

`WHERE name = 'Ali'`

Answer

`WHERE name > 'M'`

Answer

`WHERE name BETWEEN 'A' AND 'C'`

Question 42

What's wrong with `db.query("SELECT * FROM users WHERE name = '" + input + "'")`?

Accepted Answer

It is vulnerable to SQL injection

Answer

It is just slightly slower than normal

Answer

It doesn't work with non-English names

Answer

Nothing, as long as HTTPS is used

Question 43

Which part of a query cannot be parameterized with a placeholder like `$1`?

Accepted Answer

Table or column names (identifiers)

Answer

A value in `WHERE`

Answer

A value in `INSERT ... VALUES`

Answer

The `LIMIT` value

Question 44

What is the N+1 query problem?

Accepted Answer

One query for a list, then a separate query per item

Answer

A query returning N+1 rows instead of N

Answer

Joining N+1 tables in one query

Answer

An off-by-one error in `LIMIT`

Question 45

What is a regular (non-materialized) view in SQL?

Accepted Answer

A stored query that runs each time it's used

Answer

A physical copy of data refreshed periodically

Answer

An index across several tables

Answer

A temp table dropped after the session

Question 46

What is a key difference between `TRUNCATE users` and `DELETE FROM users`?

Accepted Answer

`TRUNCATE` removes all rows quickly without row-by-row scanning and skips row triggers

Answer

`DELETE` also drops the table structure

Answer

`TRUNCATE` can take a `WHERE` clause

Answer

`DELETE` cannot be rolled back

Question 47

What does `UPDATE users SET active = false;` without `WHERE` do?

Accepted Answer

Updates every row in the table

Answer

Updates only the first row

Answer

Fails because `WHERE` is required

Answer

Changes no rows

Question 48

What does `INSERT ... ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name` do in PostgreSQL?

Accepted Answer

If the email already exists, updates the existing row with the new value (upsert)

Answer

Deletes the duplicate and inserts the new row

Answer

Always inserts a new row

Answer

Silently ignores the duplicate

Question 49

Which data type is most appropriate for storing money amounts in SQL?

Accepted Answer

`NUMERIC(12, 2)` or an integer in minor units (e.g. cents)

Answer

`FLOAT`

Answer

`REAL`

Answer

`VARCHAR`

Question 50

What is the logical evaluation order of SQL query clauses?

Accepted Answer

`FROM` → `WHERE` → `GROUP BY` → `HAVING` → `SELECT` → `ORDER BY` → `LIMIT`

Answer

`SELECT` → `FROM` → `WHERE` → `GROUP BY` → `HAVING` → `ORDER BY` → `LIMIT`

Answer

`FROM` → `GROUP BY` → `WHERE` → `SELECT` → `HAVING` → `ORDER BY` → `LIMIT`

Answer

`FROM` → `WHERE` → `SELECT` → `GROUP BY` → `HAVING` → `LIMIT` → `ORDER BY`