1Z0-006 SQL Fundamentals & Data Manipulation 2 — Questions and Answers
Question 1: Which UPDATE statement correctly increases every employee's salary by 10%?
- UPDATE employees SET salary = salary * 1.10; (Correct answer)
- UPDATE employees SET salary + salary * 0.10;
- UPDATE employees MODIFY salary = salary * 1.10;
- ALTER employees SET salary = salary * 1.10;
Correct answer: UPDATE employees SET salary = salary * 1.10;
UPDATE uses SET clause with assignment; multiplying by 1.10 adds 10% to the existing value.
Question 2: What happens when you execute DELETE FROM orders; with no WHERE clause?
- An error is raised because WHERE is mandatory
- All rows in the orders table are deleted but the table structure remains (Correct answer)
- The orders table itself is dropped from the database
- Only the first row is deleted
Correct answer: All rows in the orders table are deleted but the table structure remains
DELETE without WHERE removes all rows while leaving the table structure, indexes, and constraints intact.
Question 3: Which SQL clause filters rows AFTER a GROUP BY aggregation has been applied?
- WHERE
- FILTER
- HAVING (Correct answer)
- ORDER BY
Correct answer: HAVING
HAVING filters the result of grouped rows, whereas WHERE filters individual rows before grouping.
Question 4: A UNIQUE constraint on a column differs from a PRIMARY KEY constraint because a UNIQUE column:
- Cannot be referenced by a FOREIGN KEY
- Allows NULL values (Correct answer)
- Cannot have an index created on it
- Enforces referential integrity automatically
Correct answer: Allows NULL values
A UNIQUE constraint permits NULL (multiple NULLs are allowed in Oracle), whereas PRIMARY KEY prohibits NULLs.
Question 5: Which function returns the number of non-NULL values in a column?
- SUM(column)
- COUNT(column) (Correct answer)
- COUNT(*)
- TOTAL(column)
Correct answer: COUNT(column)
COUNT(column) counts only non-NULL values in that column, while COUNT(*) counts all rows including NULLs.
Question 6: In a SELECT statement, what is the correct order of these clauses: WHERE, FROM, SELECT, ORDER BY?
- SELECT, WHERE, FROM, ORDER BY
- FROM, WHERE, SELECT, ORDER BY
- SELECT, FROM, WHERE, ORDER BY (Correct answer)
- WHERE, FROM, SELECT, ORDER BY
Correct answer: SELECT, FROM, WHERE, ORDER BY
Standard SQL clause order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
Question 7: Which INSERT syntax adds multiple rows in a single statement using Oracle's multi-table insert shorthand for a single table?
- INSERT ALL INTO emp VALUES(...) INTO emp VALUES(...) SELECT 1 FROM dual; (Correct answer)
- INSERT INTO emp VALUES(...),(...);
- INSERT MULTIPLE INTO emp VALUES(...);
- ADD ROWS INTO emp VALUES(...)(...)
Correct answer: INSERT ALL INTO emp VALUES(...) INTO emp VALUES(...) SELECT 1 FROM dual;
Oracle's INSERT ALL ... SELECT 1 FROM DUAL syntax inserts multiple rows in one DML statement.
1Z0-006 SQL Fundamentals & Data Manipulation 2
Which UPDATE statement correctly increases every employee's salary by 10%?