WGU C170 THE MOST IMPORTANT SQL COMMANDS (SQL STUDY GUIDE)
Core Domains
- Database fundamentals and relational theory
- Data definition language (DDL)
- Data manipulation language (DML)
- Query design and optimization
- Transactions, concurrency, and locking
- Security, access control, and compliance
- Backup, recovery, and integrity constraints
- Stored procedures, functions, and triggers
- Performance tuning and indexing
Introduction
* This exam evaluates practical and theoretical mastery of essential SQL concepts and commands used by database professionals. It assesses the
ability to design, query, modify, and protect relational data through multiple-choice and scenario-based questions. Test items measure
understanding of DDL and DML statements, transaction control, indexing, stored code, and security practices. Emphasis is placed on real-world
application, decision-making under constraints, and adherence to regulatory and ethical standards in data handling.*
,1. Which SQL statement is used to create a new table in a relational database?
A. INSERT INTO table (columns) VALUES (values)
B. ALTER TABLE table ADD column datatype
C. DROP TABLE table
D. CREATE TABLE table (column datatype)
🟢 D. CREATE TABLE table (column datatype)
🔴 RATIONALE: CREATE TABLE defines a new table and its column types; the other options insert data, modify an existing table, or remove a
table.
2. Which clause limits the rows returned by a SELECT query based on a condition?
A. ORDER BY
B. WHERE
C. GROUP BY
D. HAVING
🟢 B. WHERE
🔴 RATIONALE: WHERE filters rows before grouping/aggregation; HAVING filters after GROUP BY; ORDER BY sorts; GROUP BY groups rows.
3. Which SQL command permanently removes all rows from a table but preserves the table structure?
A. DELETE FROM table
B. DROP TABLE table
C. TRUNCATE TABLE table
D. REMOVE FROM table
🟢 C. TRUNCATE TABLE table
🔴 RATIONALE: TRUNCATE removes all rows efficiently and resets storage but keeps the table schema; DELETE can remove rows conditionally
and may log each deletion.
4. When should you use an INNER JOIN between two tables?
A. To return all rows from the left table regardless of matches
B. To return only rows that have matching values in both tables
C. To return unmatched rows from both tables
D. To combine rows and eliminate duplicates automatically
🟢 B. To return only rows that have matching values in both tables
🔴 RATIONALE: INNER JOIN returns intersecting rows where join conditions match; LEFT/RIGHT/FULL JOIN handle unmatched rows.
, 5. Which index type is most appropriate for speeding up equality searches on a unique identifier column?
A. Full-text index
B. B-tree (clustered or nonclustered) index
C. Hash index on large text column
D. Bitmap index on high-cardinality column
🟢 B. B-tree (clustered or nonclustered) index
🔴 RATIONALE: B-tree indexes efficiently support equality and range lookups on unique identifiers; full-text is for text search; bitmap suits low-
cardinality.
6. What does the ACID property "Isolation" ensure in database transactions?
A. Transactions are durable after commit
B. Concurrent transactions do not interfere with each other
C. Transactions always roll back on error
D. All transactions are atomic
🟢 B. Concurrent transactions do not interfere with each other
🔴 RATIONALE: Isolation ensures concurrently executing transactions behave as if sequential, preventing interference; Atomicity, Consistency,
Durability cover the other aspects.
7. Which SQL clause is used to aggregate rows based on column values?
A. WHERE
B. HAVING
C. GROUP BY
D. ORDER BY
🟢 C. GROUP BY
🔴 RATIONALE: GROUP BY groups rows to apply aggregate functions; HAVING filters groups; WHERE filters rows before grouping.
8. Which command changes the data type of an existing column in most SQL dialects?
A. ALTER TABLE table MODIFY column datatype
B. UPDATE table SET column = datatype
C. ALTER COLUMN table CHANGE column datatype
D. CHANGE TABLE table ALTER column datatype
🟢 A. ALTER TABLE table MODIFY column datatype
🔴 RATIONALE: ALTER TABLE ... MODIFY (or ALTER COLUMN depending on dialect) updates a column's datatype; UPDATE changes values,
not schema.
Core Domains
- Database fundamentals and relational theory
- Data definition language (DDL)
- Data manipulation language (DML)
- Query design and optimization
- Transactions, concurrency, and locking
- Security, access control, and compliance
- Backup, recovery, and integrity constraints
- Stored procedures, functions, and triggers
- Performance tuning and indexing
Introduction
* This exam evaluates practical and theoretical mastery of essential SQL concepts and commands used by database professionals. It assesses the
ability to design, query, modify, and protect relational data through multiple-choice and scenario-based questions. Test items measure
understanding of DDL and DML statements, transaction control, indexing, stored code, and security practices. Emphasis is placed on real-world
application, decision-making under constraints, and adherence to regulatory and ethical standards in data handling.*
,1. Which SQL statement is used to create a new table in a relational database?
A. INSERT INTO table (columns) VALUES (values)
B. ALTER TABLE table ADD column datatype
C. DROP TABLE table
D. CREATE TABLE table (column datatype)
🟢 D. CREATE TABLE table (column datatype)
🔴 RATIONALE: CREATE TABLE defines a new table and its column types; the other options insert data, modify an existing table, or remove a
table.
2. Which clause limits the rows returned by a SELECT query based on a condition?
A. ORDER BY
B. WHERE
C. GROUP BY
D. HAVING
🟢 B. WHERE
🔴 RATIONALE: WHERE filters rows before grouping/aggregation; HAVING filters after GROUP BY; ORDER BY sorts; GROUP BY groups rows.
3. Which SQL command permanently removes all rows from a table but preserves the table structure?
A. DELETE FROM table
B. DROP TABLE table
C. TRUNCATE TABLE table
D. REMOVE FROM table
🟢 C. TRUNCATE TABLE table
🔴 RATIONALE: TRUNCATE removes all rows efficiently and resets storage but keeps the table schema; DELETE can remove rows conditionally
and may log each deletion.
4. When should you use an INNER JOIN between two tables?
A. To return all rows from the left table regardless of matches
B. To return only rows that have matching values in both tables
C. To return unmatched rows from both tables
D. To combine rows and eliminate duplicates automatically
🟢 B. To return only rows that have matching values in both tables
🔴 RATIONALE: INNER JOIN returns intersecting rows where join conditions match; LEFT/RIGHT/FULL JOIN handle unmatched rows.
, 5. Which index type is most appropriate for speeding up equality searches on a unique identifier column?
A. Full-text index
B. B-tree (clustered or nonclustered) index
C. Hash index on large text column
D. Bitmap index on high-cardinality column
🟢 B. B-tree (clustered or nonclustered) index
🔴 RATIONALE: B-tree indexes efficiently support equality and range lookups on unique identifiers; full-text is for text search; bitmap suits low-
cardinality.
6. What does the ACID property "Isolation" ensure in database transactions?
A. Transactions are durable after commit
B. Concurrent transactions do not interfere with each other
C. Transactions always roll back on error
D. All transactions are atomic
🟢 B. Concurrent transactions do not interfere with each other
🔴 RATIONALE: Isolation ensures concurrently executing transactions behave as if sequential, preventing interference; Atomicity, Consistency,
Durability cover the other aspects.
7. Which SQL clause is used to aggregate rows based on column values?
A. WHERE
B. HAVING
C. GROUP BY
D. ORDER BY
🟢 C. GROUP BY
🔴 RATIONALE: GROUP BY groups rows to apply aggregate functions; HAVING filters groups; WHERE filters rows before grouping.
8. Which command changes the data type of an existing column in most SQL dialects?
A. ALTER TABLE table MODIFY column datatype
B. UPDATE table SET column = datatype
C. ALTER COLUMN table CHANGE column datatype
D. CHANGE TABLE table ALTER column datatype
🟢 A. ALTER TABLE table MODIFY column datatype
🔴 RATIONALE: ALTER TABLE ... MODIFY (or ALTER COLUMN depending on dialect) updates a column's datatype; UPDATE changes values,
not schema.
