NYC Certified IT Administrator (Database) Exam — 230 Practice Questions

NYC Certified IT Administrator (Database)
Exam — 230 Practice Questions

SECTION 1: Database Fundamentals
1. What does DBMS stand for?

 A) Data Backup Management System
 B) Database Management System ✅ (correct answer)
 C) Distributed Binary Management System
 D) Data Block Monitoring Service

Rationale: A DBMS is software that manages databases, providing tools for storing, retrieving,
and manipulating data efficiently.



2. Which of the following is a characteristic of a relational database?

 A) Data is stored in hierarchical trees
 B) Data is stored in key-value pairs only
 C) Data is organized into tables with rows and columns ✅ (correct answer)
 D) Data is stored as unstructured documents

Rationale: Relational databases organize data in tables (relations) consisting of rows (records)
and columns (attributes), forming the basis of SQL-based systems.



3. What is a primary key?

 A) A key used to encrypt database records
 B) A field that uniquely identifies each record in a table ✅ (correct answer)
 C) The first column in any database table
 D) A key shared between two tables

Rationale: A primary key uniquely identifies each row in a table. It cannot contain NULL values
and must be unique across all records.

,4. Which SQL command is used to retrieve data from a database?

 A) FETCH
 B) PULL
 C) SELECT ✅ (correct answer)
 D) GET

Rationale: The SELECT statement is the foundational SQL command for querying and
retrieving data from one or more tables.



5. What is a foreign key?

 A) A key that encrypts data in another table
 B) A key that is imported from an external database
 C) A field in one table that refers to the primary key of another table ✅ (correct answer)
 D) A backup primary key used when the main key fails

Rationale: Foreign keys establish referential integrity between tables by linking a column in one
table to the primary key of another table.



6. Which of the following is NOT a type of SQL command category?

 A) DDL (Data Definition Language)
 B) DML (Data Manipulation Language)
 C) DSL (Data Streaming Language) ✅ (correct answer)
 D) DCL (Data Control Language)

Rationale: SQL is divided into DDL, DML, DCL, and TCL. DSL (Data Streaming Language) is
not a recognized SQL command category.



7. What does normalization in a database achieve?

 A) Increases data redundancy for backup purposes
 B) Reduces data redundancy and improves data integrity ✅ (correct answer)
 C) Encrypts data for security compliance
 D) Converts unstructured data to structured formats

Rationale: Normalization organizes data to minimize redundancy and dependency, improving
the efficiency and consistency of the database.

,8. Which normal form eliminates partial dependencies?

 A) First Normal Form (1NF)
 B) Second Normal Form (2NF) ✅ (correct answer)
 C) Third Normal Form (3NF)
 D) Boyce-Codd Normal Form (BCNF)

Rationale: 2NF requires that a table is in 1NF and that all non-key attributes are fully dependent
on the entire primary key, eliminating partial dependencies.



9. What is a view in a relational database?

 A) A physical copy of a table stored separately
 B) A virtual table based on the result of a SQL query ✅ (correct answer)
 C) A diagram showing the database schema
 D) A stored snapshot of data taken at a specific time

Rationale: A view is a virtual table derived from a query. It does not store data physically but
presents data from underlying tables dynamically.



10. Which of the following SQL clauses filters records AFTER grouping?

 A) WHERE
 B) FILTER
 C) HAVING ✅ (correct answer)
 D) GROUP FILTER

Rationale: The HAVING clause filters groups after the GROUP BY operation, whereas
WHERE filters individual rows before grouping.



11. What is a stored procedure?

 A) A backup script executed automatically during failures
 B) A set of precompiled SQL statements stored in the database ✅ (correct answer)
 C) A method of encrypting database transactions
 D) A visual representation of database tables

, Rationale: Stored procedures are named, precompiled groups of SQL statements that can accept
parameters and be reused, improving performance and maintainability.



12. Which JOIN type returns only matching rows from both tables?

 A) LEFT JOIN
 B) RIGHT JOIN
 C) FULL OUTER JOIN
 D) INNER JOIN ✅ (correct answer)

Rationale: INNER JOIN returns only the rows where there is a matching value in both joined
tables, excluding non-matching rows from either table.



13. What does ACID stand for in database transactions?

 A) Atomicity, Consistency, Isolation, Durability ✅ (correct answer)
 B) Access, Control, Integrity, Data
 C) Automation, Concurrency, Indexing, Distribution
 D) Aggregation, Compression, Integration, Dependency

Rationale: ACID properties ensure reliable database transactions: Atomicity (all or nothing),
Consistency (valid state), Isolation (concurrent transaction safety), Durability (committed data
persists).



14. What is a database index used for?

 A) To enforce security policies on tables
 B) To speed up data retrieval operations ✅ (correct answer)
 C) To store backup copies of critical records
 D) To define relationships between tables

Rationale: An index creates a data structure (like a B-tree) that allows the database engine to
find rows faster without scanning the entire table.



15. Which SQL command is used to add a new column to an existing table?

 A) MODIFY TABLE
 B) UPDATE TABLE