Basic SELECT: 1Z0-071 practice questions
Questions in the style of exam 1Z0-071 on this topic. Answer each one first, then open its answer and explanation.
What this topic covers
- Theoretical and physical aspects of a relational database
- Relating SELECT clauses to components of an ERD
- The relationship between a database and SQL
- The SQL SELECT statement
- Column aliases
- Concatenation, literal character strings and the alternative quote operator
- The DISTINCT keyword
- Arithmetic expressions and NULL values
Practice questions
1. Which statement about this query is true?
SELECT e.salary * 12 AS annual
FROM employees e
WHERE annual > 100000
ORDER BY annual;- It runs and returns employees with annual salary above 100000.
- It fails because a column alias cannot be used in the WHERE clause.
- It fails because a column alias cannot be used in the ORDER BY clause.
- It fails because AS is not allowed before a column alias.
Show the answer
Answer: B
WHERE is evaluated before the SELECT list, so the alias does not exist yet (ORA-00904: invalid identifier). ORDER BY is evaluated last and can use the alias. Repeat the expression in WHERE or use an inline view.
2. employees.department_id is a foreign key that references departments.department_id, and no department 99 exists. What happens when this statement runs?
INSERT INTO employees (employee_id, last_name, hire_date, department_id)
VALUES (900, 'Novak', DATE '2024-01-10', 99);- It fails with ORA-02291 (parent key not found) because department 99 does not exist.
- It succeeds, and department 99 is created automatically.
- It succeeds, because foreign keys are only checked at COMMIT.
- It fails with ORA-01400 because department_id cannot be NULL.
Show the answer
Answer: A
A foreign key value must match an existing parent key or be NULL. Department 99 does not exist, so ORA-02291 (integrity constraint violated - parent key not found) is raised immediately; constraints are not deferred by default. Inserting NULL into department_id would succeed, as it did for employee 108.
3. The table employees was created with an unquoted name, and it is the only table of that name in your schema. How many rows does this query return?
SELECT table_name
FROM user_tables
WHERE table_name IN ('employees', 'Employees', 'EMPLOYEES');- 1
- 0
- 3
- An error, because the table name must not be in quotes
Show the answer
Answer: A
An unquoted name is stored in the data dictionary in uppercase, so the table is recorded as EMPLOYEES. The three values in quotes are data, and data is compared exactly, so only 'EMPLOYEES' matches: 1 row. 0 is what you get when you look up the name as you typed it in lowercase, and 3 would need the comparison to ignore case. table_name is an ordinary column, so comparing it with a text literal is not an error.
4. employees.department_id is a foreign key to departments, declared without any ON DELETE clause, and four employees belong to department 30. What happens when this statement runs?
DELETE FROM departments
WHERE department_id = 30;- It fails with ORA-02292 (child record found), and nothing is deleted.
- It succeeds, and the four employees get a NULL department_id.
- It succeeds, and the four employees are deleted as well.
- It fails with ORA-02291 (parent key not found).
Show the answer
Answer: A
A foreign key protects both directions: a child value needs a parent, and a parent with children cannot be deleted, so Oracle raises ORA-02292 and the statement changes nothing. Deleting or nulling the children automatically happens only when the key is declared ON DELETE CASCADE or ON DELETE SET NULL. ORA-02291 is the error for a child value with no parent, raised by INSERT or UPDATE on the child table.