Database Administration Flashcards

Which SQL statement is used to add a new column to an existing table?

Answer: ALTER TABLE ... ADD

ALTER TABLE followed by ADD allows you to add one or more new columns to an existing table without recreating it.

What is a deadlock in a database context?

Answer: Two or more transactions each waiting for the other to release a lock

A deadlock occurs when two or more transactions are mutually blocking each other, each waiting for a lock held by the other to be released.

Which SQL function returns the current date and time on the database server?

Answer: NOW()

NOW() is the standard SQL function that returns the current date and time as reported by the database server at query execution.

In database design, what does ERD stand for?

Answer: Entity Relationship Diagram

An Entity Relationship Diagram visually models the entities in a system, their attributes, and the relationships between them.

What is the difference between TRUNCATE and DELETE in SQL?

Answer: TRUNCATE removes all rows without logging individual deletions; DELETE logs each row removal

TRUNCATE is a DDL operation that quickly removes all rows by deallocating data pages, while DELETE is a DML operation that logs each row deletion individually.

Which type of JOIN returns all rows from both tables, filling NULLs where there is no match?

Answer: FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables, placing NULLs in columns where no matching row exists in the opposite table.

What is the purpose of a database trigger?

Answer: To automatically execute a set of SQL statements in response to a data event

A trigger is a stored procedure that fires automatically when a specified event (INSERT, UPDATE, or DELETE) occurs on a table.