Database Programming & SQL Flashcards

Which approach is the most effective way to prevent SQL injection attacks in application code?

Answer: Using parameterized queries (prepared statements)

Parameterized queries separate SQL code from data, so user input is always treated as a literal value and can never be interpreted as SQL syntax.

In the CAP theorem for distributed databases, which two properties can be simultaneously guaranteed during a network partition?

Answer: Consistency and Partition Tolerance, or Availability and Partition Tolerance — but not all three

The CAP theorem states that during a network partition, a distributed system can guarantee either Consistency or Availability but not both; Partition Tolerance is mandatory in distributed systems.

What is the key difference between DELETE and TRUNCATE in SQL?

Answer: DELETE can be rolled back and supports WHERE; TRUNCATE is typically faster and non-recoverable without a transaction

DELETE removes rows one by one with full transaction log entries and supports a WHERE clause; TRUNCATE removes all rows rapidly (often by deallocating pages) and in most databases cannot be filtered by WHERE.

Which normal form eliminates multi-valued dependencies, where one attribute independently determines two or more other attributes?

Answer: Fourth Normal Form (4NF)

4NF extends BCNF by requiring that a table have no non-trivial multi-valued dependencies — cases where one key independently determines two separate sets of values.

What does the SQL keyword DISTINCT do when used in a SELECT statement?

Answer: Removes duplicate rows from the query result

DISTINCT instructs the database to return only unique rows, eliminating any duplicate combinations of the selected columns from the result set.

In a relational database, what does a composite key mean?

Answer: A primary key consisting of two or more columns combined to uniquely identify a row

A composite key uses two or more columns together as the primary key, where neither column alone is sufficient to uniquely identify a row but the combination is.

Which of the following best explains why using SELECT * in production queries is generally discouraged?

Answer: It retrieves all columns including unused ones, increasing network payload and breaking code if the schema changes

SELECT * retrieves every column even if most are unused, wastes bandwidth and memory, and silently breaks application code if columns are added, removed, or reordered.