1Z0-006 SQL Fundamentals & Data Manipulation 5 — Questions and Answers
Question 1: Which clause in a SELECT statement is used to combine rows from two or more tables based on a related column?
- UNION
- JOIN ... ON (Correct answer)
- MERGE
- COMBINE
Correct answer: JOIN ... ON
JOIN ... ON links two tables by matching rows where the ON condition is true, producing a combined result set.
Question 2: What does ROLLBACK do in Oracle SQL?
- Saves all pending changes permanently
- Creates a named restore point in the current transaction
- Undoes all uncommitted DML changes since the last COMMIT or SAVEPOINT (Correct answer)
- Closes the current database connection
Correct answer: Undoes all uncommitted DML changes since the last COMMIT or SAVEPOINT
ROLLBACK reverses all uncommitted DML operations back to the last COMMIT or to a named SAVEPOINT.
Question 3: Which SQL operator returns rows that exist in the first SELECT result but NOT in the second?
- UNION
- INTERSECT
- MINUS (Correct answer)
- EXCEPT ALL
Correct answer: MINUS
Oracle's MINUS operator returns distinct rows from the first query that do not appear in the second query's result.
Question 4: Which of the following correctly adds a new column named phone_num to an existing table?
- UPDATE employees ADD phone_num VARCHAR2(20);
- ALTER TABLE employees ADD phone_num VARCHAR2(20); (Correct answer)
- MODIFY TABLE employees ADD COLUMN phone_num VARCHAR2(20);
- INSERT COLUMN phone_num VARCHAR2(20) INTO employees;
Correct answer: ALTER TABLE employees ADD phone_num VARCHAR2(20);
ALTER TABLE ... ADD is the DDL syntax used to add a new column to an existing table.
Question 5: What value does AVG(salary) return if a group contains salaries 1000, 2000, NULL, and 3000?
- 1500
- 2000 (Correct answer)
- 1750
- An error because of the NULL
Correct answer: 2000
AVG ignores NULL values, so it divides the sum (6000) by the count of non-NULL values (3), returning 2000.
Question 6: Which SELECT clause is evaluated FIRST by the Oracle SQL engine during query processing?
- SELECT
- WHERE
- FROM (Correct answer)
- ORDER BY
Correct answer: FROM
Oracle logically processes FROM first to identify the data source(s), then WHERE, then SELECT, then ORDER BY.
Question 7: Which statement about the CHAR and VARCHAR2 data types in Oracle is correct?
- CHAR stores variable-length strings; VARCHAR2 stores fixed-length strings
- CHAR stores fixed-length strings padded with spaces; VARCHAR2 stores variable-length strings (Correct answer)
- Both types pad short values with zeros to fill the declared length
- VARCHAR2 and CHAR are completely interchangeable with no behavioral differences
Correct answer: CHAR stores fixed-length strings padded with spaces; VARCHAR2 stores variable-length strings
CHAR is fixed-length and pads with trailing spaces, while VARCHAR2 stores only the actual characters entered.
1Z0-006 SQL Fundamentals & Data Manipulation 5
Which clause in a SELECT statement is used to combine rows from two or more tables based on a related column?