CIW CIW Database Design & SQL 2 — Questions and Answers
Question 1: Which SQL statement is used to add a new record to a database table?
- INSERT INTO (Correct answer)
- ADD RECORD
- UPDATE SET
- CREATE ROW
Correct answer: INSERT INTO
INSERT INTO specifies the target table and the values to insert, creating a new row in the database.
Question 2: What does the SQL GROUP BY clause do?
- Groups rows sharing a common value so aggregate functions can be applied to each group (Correct answer)
- Sorts the result set in ascending order by the specified column
- Joins multiple tables based on a matching column
- Filters rows based on an aggregate condition
Correct 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.
Question 3: Which data type is most appropriate for storing a web URL in a database column?
- VARCHAR (Correct answer)
- INT
- BOOLEAN
- DATE
Correct answer: VARCHAR
VARCHAR (variable-length character string) is used for text data like URLs, allowing flexible storage up to a defined maximum length.
Question 4: What is an SQL transaction used for?
- To group multiple SQL statements so they execute as a single atomic unit (Correct answer)
- To transfer data between two separate databases
- To schedule SQL queries to run at a future time
- To create a backup copy of a table before modifications
Correct 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.
Question 5: Which SQL command removes all rows from a table without deleting the table structure?
- TRUNCATE (Correct answer)
- DROP
- DELETE FROM with no WHERE clause
- REMOVE ALL
Correct answer: TRUNCATE
TRUNCATE quickly removes all rows from a table and resets auto-increment counters, but preserves the table's schema for future use.
Question 6: What is an index in a database and why is it used?
- A data structure that improves the speed of data retrieval on a column (Correct answer)
- A constraint that prevents duplicate values in a column
- A stored procedure that executes on a schedule
- A backup mechanism that mirrors table data
Correct 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.
CIW CIW Database Design & SQL 2
Which SQL statement is used to add a new record to a database table?