1Z0-006 SQL Fundamentals & Data Manipulation 4 — Questions and Answers
Question 1: Which SQL statement is used to permanently save changes made during a transaction?
- SAVE
- COMMIT (Correct answer)
- ROLLBACK
- SAVEPOINT
Correct answer: COMMIT
COMMIT permanently writes all pending DML changes to the database, making them visible to other sessions.
Question 2: Which SQL function concatenates two strings in Oracle?
- CONCAT(str1, str2) or str1 || str2 (Correct answer)
- MERGE(str1, str2)
- JOIN(str1, str2)
- APPEND(str1, str2)
Correct answer: CONCAT(str1, str2) or str1 || str2
Oracle supports both the CONCAT function and the || pipe operator to join strings together.
Question 3: What does SELECT * FROM employees WHERE last_name LIKE '_a%'; return?
- Rows where last_name starts with the letter 'a'
- Rows where the second character of last_name is 'a' (Correct answer)
- Rows where last_name contains exactly one character before 'a'
- Rows where last_name ends with 'a'
Correct answer: Rows where the second character of last_name is 'a'
The underscore (_) wildcard matches exactly one character, so _a% matches any name whose second character is 'a'.
Question 4: Which aggregate function returns the largest value in a column?
- GREATEST(column)
- TOP(column)
- MAX(column) (Correct answer)
- HIGH(column)
Correct answer: MAX(column)
MAX(column) is the standard SQL aggregate function that returns the highest value in a group.
Question 5: A subquery in a WHERE clause that returns exactly one value is called a:
- Multi-row subquery
- Correlated subquery
- Single-row subquery (Correct answer)
- Inline view
Correct answer: Single-row subquery
A single-row subquery returns one row and one column, and is used with single-row comparison operators like =, >, <.
Question 6: Which DML statement is used to change existing data in a table?
- MODIFY
- CHANGE
- UPDATE (Correct answer)
- ALTER
Correct answer: UPDATE
UPDATE is the DML command used to modify existing row values; ALTER is DDL used to change table structure.
Question 7: What is the effect of adding NOT NULL to a column definition in a CREATE TABLE statement?
- The column will automatically generate sequential values
- The column cannot store a NULL value; every row must supply a value (Correct answer)
- The column value must be unique across all rows
- The column can only store numeric data
Correct answer: The column cannot store a NULL value; every row must supply a value
NOT NULL is a column-level constraint that forces every inserted or updated row to supply a non-NULL value for that column.
1Z0-006 SQL Fundamentals & Data Manipulation 4
Which SQL statement is used to permanently save changes made during a transaction?