CIW Database Design & SQL Flashcards

Which SQL statement is used to add a new record to a database table?

Answer: INSERT INTO

INSERT INTO specifies the target table and the values to insert, creating a new row in the database.

What does the SQL GROUP BY clause do?

Answer: Groups rows sharing a common value so aggregate functions can be applied to each group

GROUP BY arranges identical data into groups, enabling functions like COUNT, SUM, and AVG to operate on each group independently.

Which data type is most appropriate for storing a web URL in a database column?

Answer: VARCHAR

VARCHAR (variable-length character string) is used for text data like URLs, allowing flexible storage up to a defined maximum length.

What is an SQL transaction used for?

Answer: To group multiple SQL statements so they execute as a single atomic unit

A transaction ensures that a series of SQL operations either all succeed (COMMIT) or all fail (ROLLBACK), maintaining data consistency.

Which SQL command removes all rows from a table without deleting the table structure?

Answer: TRUNCATE

TRUNCATE quickly removes all rows from a table and resets auto-increment counters, but preserves the table's schema for future use.

What is an index in a database and why is it used?

Answer: A data structure that improves the speed of data retrieval on a column

An index allows the database engine to locate rows quickly without scanning every row in the table, significantly improving query performance.