Web Development Database Basics 5 — Questions and Answers
Question 1: What is the difference between a clustered and a non-clustered index?
- A clustered index is faster to create
- A clustered index sorts and stores data rows physically, while a non-clustered index is a separate structure (Correct answer)
- A non-clustered index can only be on one column
- A clustered index only works on primary keys
Correct 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.
Question 2: Which SQL set operation returns rows that appear in the first query but NOT in the second?
- UNION
- INTERSECT
- EXCEPT (Correct answer)
- MINUS ALL
Correct answer: EXCEPT
EXCEPT (or MINUS in some databases) returns rows from the first SELECT that are not present in the second SELECT result.
Question 3: What is denormalization in database design?
- Removing all foreign keys from a table
- Intentionally introducing redundancy to improve read performance (Correct answer)
- Converting a relational database to NoSQL
- Splitting a large table into smaller ones
Correct 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.
Question 4: What does ORM stand for in web development?
- Object Relational Mapping (Correct answer)
- Online Resource Manager
- Open Relational Model
- Object Request Module
Correct 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.
Question 5: Which SQL function would you use to return the current date and time?
- GETTIME()
- NOW() (Correct answer)
- CURRENT()
- DATETIME()
Correct answer: NOW()
NOW() returns the current date and time in most SQL databases, though some databases use GETDATE() or CURRENT_TIMESTAMP.
Question 6: What is a database transaction?
- A type of database backup
- A unit of work that is executed as a whole and can be committed or rolled back (Correct answer)
- A query that returns a single row
- A trigger that fires on data changes
Correct 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.
Question 7: In SQL, what is the purpose of the COALESCE() function?
- To combine two strings
- To return the first non-NULL value from a list of arguments (Correct answer)
- To count non-null values in a column
- To convert data types
Correct 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.
Web Development Database Basics 5
What is the difference between a clustered and a non-clustered index?