CIW CIW Database Design Specialist 1 — Questions and Answers
Question 1: What is the primary purpose of a primary key in a relational database table?
- Sort rows alphabetically
- Uniquely identify each row in the table (Correct answer)
- Encrypt the table data
- Define the table's default sort order
Correct answer: Uniquely identify each row in the table
A primary key uniquely identifies every record in a database table and cannot contain NULL values.
Question 2: Which SQL statement is used to retrieve data from a database?
- INSERT
- UPDATE
- SELECT (Correct answer)
- FETCH
Correct answer: SELECT
The SQL `SELECT` statement queries a database and returns a result set of matching rows.
Question 3: What does normalization in database design primarily aim to eliminate?
- Indexes
- NULL values
- Data redundancy and update anomalies (Correct answer)
- Foreign keys
Correct answer: Data redundancy and update anomalies
Normalization restructures tables to minimize data redundancy and prevent insertion, update, and deletion anomalies.
Question 4: Which type of SQL JOIN returns only rows where there is a match in both tables?
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
- INNER JOIN (Correct answer)
Correct answer: INNER JOIN
An `INNER JOIN` returns only the rows that have matching values in both joined tables.
Question 5: In relational database terminology, what is a foreign key?
- An encrypted primary key
- A column that references the primary key of another table (Correct answer)
- A key used for remote database access
- A composite key with two columns
Correct answer: A column that references the primary key of another table
A foreign key is a column (or set of columns) that establishes a link between data in two tables by referencing the primary key of another table.
Question 6: What does the SQL `GROUP BY` clause do?
- Sorts the result set in ascending order
- Filters rows before aggregation
- Groups rows with the same values so aggregate functions can be applied per group (Correct answer)
- Joins two tables together
Correct answer: Groups rows with the same values so aggregate functions can be applied per group
The `GROUP BY` clause groups rows sharing the same values in specified columns so aggregate functions like COUNT or SUM operate per group.
CIW CIW Database Design Specialist 1
What is the primary purpose of a primary key in a relational database table?