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

Discussions

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

Discussions

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

Discussions

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

Discussions

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

Discussions

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

Discussions

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

Discussions

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

Discussions

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

Discussions

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

Discussions
To page 2