Database Basics Flashcards

What is the difference between a clustered and a non-clustered index?

Answer: A clustered index sorts and stores data rows physically, while a non-clustered index is a separate structure

A clustered index determines the physical order of data in the table, while a non-clustered index is a separate lookup structure pointing to data rows.

Which SQL set operation returns rows that appear in the first query but NOT in the second?

Answer: EXCEPT

EXCEPT (or MINUS in some databases) returns rows from the first SELECT that are not present in the second SELECT result.

What is denormalization in database design?

Answer: Intentionally introducing redundancy to improve read performance

Denormalization adds redundant data to a database to reduce complex joins and speed up read queries, at the cost of more storage and harder updates.

What does ORM stand for in web development?

Answer: Object Relational Mapping

ORM (Object Relational Mapping) is a technique that lets you interact with a relational database using object-oriented code instead of raw SQL.

Which SQL function would you use to return the current date and time?

Answer: NOW()

NOW() returns the current date and time in most SQL databases, though some databases use GETDATE() or CURRENT_TIMESTAMP.

What is a database transaction?

Answer: A unit of work that is executed as a whole and can be committed or rolled back

A transaction is a sequence of operations treated as a single unit; it either commits all changes or rolls them all back to maintain data integrity.

In SQL, what is the purpose of the COALESCE() function?

Answer: To return the first non-NULL value from a list of arguments

COALESCE() evaluates its arguments in order and returns the first non-NULL value, making it useful for handling NULL defaults.