Back-End Development Development Technical (SQL) 3

Question 1

What type of SQL index is best suited for columns with very few distinct values, such as a boolean status field?

Accepted Answer

Bitmap index

Answer

Bitmap indexes are efficient for low-cardinality columns because they store a bit per row per distinct value, enabling fast bitwise operations.

Question 2

Which SQL statement correctly adds a foreign key constraint to an existing table?

Accepted Answer

ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

Answer

ALTER TABLE with ADD FOREIGN KEY is the correct DDL syntax to add a referential integrity constraint to an existing table.

Question 3

What does the SQL COALESCE function return?

Accepted Answer

The first non-NULL value from its argument list

Answer

COALESCE evaluates arguments left to right and returns the first one that is not NULL, making it useful for providing default values.

Question 4

Which type of SQL subquery is executed once for the entire outer query?

Accepted Answer

Non-correlated subquery

Answer

A non-correlated subquery runs independently of the outer query and its result is computed once, then reused for every row of the outer query.

Question 5

In a B-tree index, what is the typical time complexity for a point lookup?

Accepted Answer

O(log n)

Answer

B-tree indexes are balanced trees where a point lookup traverses from root to leaf in O(log n) time proportional to the number of index entries.

Back-End Development Practice Test