Database Management Flashcards

Which SQL statement is used to permanently remove a table and all its data from a database?

Answer: DROP TABLE permits

DROP TABLE permanently removes the table definition and all its data, while DELETE and TRUNCATE only remove rows.

A jurisdiction's code enforcement database must be recovered after a server crash. Which log type is essential for point-in-time recovery?

Answer: Transaction log

The transaction log records all database changes sequentially, enabling recovery to a specific point in time by replaying committed transactions.

In database design for building inspections, which relationship type describes one inspector assigned to many inspections?

Answer: One-to-many

A one-to-many relationship means one record in the parent table (inspector) is associated with multiple records in the child table (inspections).

Which SQL aggregate function would return the total number of permits issued in a calendar year?

Answer: COUNT(permit_id)

COUNT() returns the number of rows matching the query criteria, making it the appropriate function to tally issued permits.

What is the purpose of database connection pooling in a high-traffic permit portal?

Answer: Reusing existing connections to reduce overhead

Connection pooling maintains a pool of open connections that are reused by incoming requests, reducing the cost of repeatedly opening and closing connections.

A building department queries inspection records and notices response times degraded after adding 5 million rows. Which action most directly improves SELECT query performance?

Answer: Creating an index on the filtered column

Adding an index on frequently filtered columns allows the database engine to locate matching rows without scanning the entire table.

Which SQL clause is used to filter grouped results in a report of permits grouped by inspection type?

Answer: HAVING

The HAVING clause filters rows after GROUP BY aggregation, whereas WHERE filters individual rows before grouping.