CPP Database Programming & SQL 2 — Questions and Answers
Question 1: What is the primary benefit of using a stored procedure in a database application?
- It stores data more efficiently than regular tables
- It encapsulates SQL logic on the server, reducing network traffic (Correct answer)
- It automatically indexes all referenced columns
- It prevents any SQL injection by design
Correct answer: It encapsulates SQL logic on the server, reducing network traffic
Stored procedures reside on the database server, so complex SQL logic executes server-side, reducing the amount of data and round-trips over the network.
Question 2: A database VIEW is best described as:
- A physical copy of a table stored on disk for fast access
- A named, saved SQL query that behaves like a virtual table (Correct answer)
- A type of index that spans multiple tables
- A database trigger that fires on SELECT statements
Correct answer: A named, saved SQL query that behaves like a virtual table
A view is a stored query that presents data from one or more underlying tables as if it were a table itself, without storing the data redundantly.
Question 3: Which database isolation level prevents dirty reads but still allows non-repeatable reads?
- READ UNCOMMITTED
- READ COMMITTED (Correct answer)
- REPEATABLE READ
- SERIALIZABLE
Correct answer: READ COMMITTED
READ COMMITTED ensures a transaction only reads data that has been committed, preventing dirty reads, but another transaction can still modify and commit rows between reads within the same transaction.
Question 4: What does an ORM (Object-Relational Mapper) primarily accomplish in application development?
- Optimizes SQL query execution plans automatically
- Maps database tables to programming language objects, eliminating manual SQL (Correct answer)
- Provides a caching layer in front of the database
- Converts SQL schemas to NoSQL document formats
Correct answer: Maps database tables to programming language objects, eliminating manual SQL
An ORM bridges the gap between object-oriented code and relational databases by automatically translating between objects/classes and database rows/tables.
Question 5: Which SQL statement type is classified as DDL (Data Definition Language)?
- SELECT
- UPDATE
- CREATE TABLE (Correct answer)
- INSERT
Correct answer: CREATE TABLE
DDL statements define or modify database structure; CREATE TABLE creates a new table schema and is therefore DDL, whereas SELECT/INSERT/UPDATE are DML.
Question 6: When using a database connection pool, what happens when all connections in the pool are in use and a new request arrives?
- A new permanent connection is created immediately
- The request waits in a queue until a connection becomes available (Correct answer)
- The request is silently dropped without error
- The pool automatically doubles its maximum size
Correct answer: The request waits in a queue until a connection becomes available
Connection pools have a maximum size; when all connections are busy, incoming requests are queued and wait until a connection is released back to the pool.
Question 7: A correlated subquery differs from a regular subquery in that it:
- Returns multiple result sets instead of one
- References a column from the outer query and re-executes for each outer row (Correct answer)
- Can only be used in the SELECT clause
- Always executes faster than a JOIN
Correct answer: References a column from the outer query and re-executes for each outer row
A correlated subquery references a column from the outer query, causing it to be re-evaluated once per row of the outer query rather than once total.
CPP Database Programming & SQL 2
What is the primary benefit of using a stored procedure in a database application?