oracle 1z0-071 practice test
Oracle Database SQL Exam
Question 1
Which two statements are true about Oracle synonyms? (Choose two.)
- A. A synonym can have a synonym.
- B. All private synonym names must be unique in the database.
- C. Any user can create a PUBLIC synonym.
- D. A synonym can be created on an object in a package.
- E. A synonym has an object number.
Answer:
B D
Explanation:
Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm
Question 2
You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and LOGIN_ID must be unique
and not null. Which two SQL statements would create the required table? (Choose two.)
- A. CREATE TABLE employees (employee_id NUMBER, login_id NUMBER, employee_name VARCHAR2(100), hire_date DATE, CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id));
- B. CREATE TABLE employees (employee_id NUMBER, login_id NUMBER, employee_name VARCHAR2(25), hire_date DATE, CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));
- C. CREATE TABLE employees (employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY, login_id NUMBER UNIQUE, employee_name VARCHAR2(25), hire_date DATE);
- D. CREATE TABLE employees (employee_id NUMBER, login_id NUMBER, employee_name VARCHAR2(100), hire_date DATE, CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id); CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id));
- E. CREATE TABLE employees (employee_id NUMBER CONSTRAINT emp_id_nn NOT NULL, login_id NUMBER CONSTRAINT login_id_nn NOT NULL, employee_name VARCHAR2(100), hire_date DATE, CONSTRAINT emp_num_id_uk UNIQUE (employee_id, login_id));
Answer:
B E
Question 3
Which two statements are true regarding constraints? (Choose two.)
- A. All constraints can be defined at the table or column level.
- B. A constraint can be disabled even if the constrained column contains data.
- C. A column with a UNIQUE constraint can contain a NULL value.
- D. A column with a FOREIGN KEY constraint can never contain a NULL value.
- E. Constraints are enforced only during INSERT operations.
Answer:
B C
Question 4
View the Exhibit and examine the structure of the ORDERS table. The columns ORDER_MODE and ORDER_TOTAL have
the default values direct and 0 respectively.
Which two INSERT statements are valid? (Choose two.)
- A. INSERT INTO orders VALUES (1,09-mar-2007, online,, 1000);
- B. INSERT INTO orders (order_id,order_date,order_mode, (customer_id,order_total) VALUES (1,TO_DATE(NULL), online, 101, NULL);
- C. INSERT INTO (SELECT order_id,order_date,customer_id FROM orders) VALUES (1,09-mar-2007, 101);
- D. INSERT INTO orders VALUES (1,09-mar-2007, DEFAULT, 101, DEFAULT);
- E. INSERT INTO orders (order_id,order_date,order_mode,order_total) VALUES (1,10-mar-2007,online,1000);
Answer:
C D
Question 5
You want to write a query that prompts for two column names and the WHERE condition each time it is executed in a
session but only prompts for the table name the first time it is executed.
The variables used in your query are never undefined in your session.
Which query can be used?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
- E. Option E
Answer:
D
Question 6
View the Exhibit and examine the structure of the CUSTOMERS table.
You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or
C, and credit limit is below 10,000. Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE A% OR
UPPER (cust_last_name) LIKE B% OR UPPER (cust_last_name) LIKE C%)
AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER (cust_last_name) BETWEEN A AND C AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?
- A. Only the second query gives the correct result
- B. Both execute successfully but do not give the required result
- C. Only the first query gives the correct result
- D. Both execute successfully and give the same result
Answer:
C
Question 7
View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
You want to update EMPLOYEES table as follows:
Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
Set department_id for these employees to the department_id corresponding to London (location_id 2100).
Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.
Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.
You issue the following command:
What is outcome?
- A. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.
- B. It generates an error because a subquery cannot have a join condition in a UPDATE statement.
- C. It executes successfully and gives the desired update
- D. It executes successfully but does not give the desired update
Answer:
D
Question 8
Which statement is true regarding the UNION operator?
- A. By default, the output is not sorted.
- B. Null values are not ignored during duplicate checking.
- C. Names of all columns must be identical across all select statements.
- D. The number of columns selected in all select statements need not be the same.
Answer:
B
Question 9
Which two statement are true regarding table joins available in the Oracle Database server? (Choose two.)
- A. You can use the ON clause to specify multiple conditions while joining tables.
- B. You can explicitly provide the join condition with a NATURAL JOIN.
- C. You can use the JOIN clause to join only two tables.
- D. You can use the USING clause to join tables on more than one column.
Answer:
A D
Question 10
View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
Which two tasks would require subqueries? (Choose two.)
- A. displaying all the products whose minimum list prices are more than average list price of products having the status orderable
- B. displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE
- C. displaying the number of products whose list prices are more than the average list price
- D. displaying all supplier IDs whose average list price is more than 500
- E. displaying the minimum list price for each product status
Answer:
A C
Question 11
Which three statements are true about single-row functions? (Choose three.)
- A. They can be nested to any level
- B. The data type returned can be different from the data type of the argument
- C. They can accept only one argument
- D. The argument can be a column name, variable, literal or an expression
- E. They can be used only in the WHERE clause of a SELECT statement
- F. They return a single result row per table
Answer:
A B D
Question 12
View the Exhibit and examine the structure of the CUSTOMERS table.
Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.
Customers with no credit limit should have Not Available displayed.
Which SQL statement would produce the required result?
- A. SELECT NVL(TO_CHAR(cust_credit_limit*.15),‘Not Available’) “NEW CREDIT” FROM customers;
- B. SELECT TO_CHAR(NVL(cust_credit_limit*.15,‘Not Available’)) “NEW CREDIT” FROM customers;
- C. SELECT NVL(cust_credit_limit*.15,‘Not Available’) “NEW CREDIT” FROM customers;
- D. SELECT NVL(cust_credit_limit,Not Available)*.15 NEW CREDIT FROM customers;
Answer:
A
Question 13
Examine the structure of the INVOICE table.
Which two SQL statements would execute successfully?
- A. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete') FROM invoice;
- B. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available') FROM invoice;
- C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate) FROM invoice;
- D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available') FROM invoice;
Answer:
A C
Question 14
View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is
created with the ON DELETE CASCADE option. Which DELETE statement would execute successfully?
- A. DELETE orders o, order_items i WHERE o.order_id = i.order_id;
- B. DELETE FROM orders WHERE (SELECT order_id FROM order_items);
- C. DELETE orders WHERE order_total < 1000;
- D. DELETE order_id FROM orders WHERE order_total < 1000;
Answer:
C
Question 15
View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)
Examine this SQL statement:
SELECT department_id "DEPT_ID", department_name, 'b' FROM departments
WHERE departments_id=90
UNION
SELECT department_id, department_name DEPT_NAME, 'a' FROM departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output?
- A. ORDER BY DEPT_NAME;
- B. ORDER BY DEPT_ID;
- C. ORDER BY 'b';
- D. ORDER BY 3;
Answer:
B D
A is correct because - CREATE SYNONYM statement creates a synonym, which is an alternative name for a table, view, sequence, operator, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.
A, E looks valid. Each objects in the Oracle database has an object_id (reffer to dba_objects). A is obvious.
Each objects in the Oracle database has an object_id (reffer to dba_objects). A is obvious.