oracle 1Z0-071 Exam Questions

Questions for the 1Z0-071 were updated on : Jun 07 ,2024

Page 1 out of 26. Viewing questions 1-15 out of 318

Question 1

Choose the best answer.
Examine the description of the EMPLOYEES table:

Which query is valid?

  • A. SELECT * FROM MEMBERS WHERE state LIKE '%A_';
  • A. SELECT dept_id, join_date,SUM(salary) FROM employees GROUP BY dept_id, join_date;
  • B. SELECT * FROM MEMBERS WHERE state LIKE 'A_';
  • B. SELECT depe_id,join_date,SUM(salary) FROM employees GROUP BY dept_id:
  • C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
  • C. SELECT dept_id,MAX(AVG(salary)) FROM employees GROUP BY dept_id;
  • D. SELECT * FROM MEMBERS WHERE state LIKE 'A%';
  • D. SELECT dept_id,AVG(MAX(salary)) FROM employees GROUP BY dapt_id;
Answer:

A

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
0 / 1000

Question 2

Which three are true about the CREATE TABLE command?

  • A. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
  • A. It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint.
  • B. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
  • B. The owner of the table should have space quota available on the tablespace where the table is defined.
  • C. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
  • C. It implicitly executes a commit.
  • D. It executes successfully.
  • D. It implicitly rolls back any pending transactions.
  • E. A user must have the CREATE ANY TABLE privilege to create tables.
  • F. The owner of the table must have the UNLIMITED TABLESPACE system privilege.
Answer:

A, B, C

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
F
0 / 1000

Question 3

The CUSTOMERS table has a CUST_CREDT_LIMIT column of data type number.
Which two queries execute successfully?

  • A. SELECT TO_DATE(‘2019-01-01’) FROM DUAL;
  • A. SELECT TO_CHAR(NVL(cust_credit_limit * .15,'Not Available')) FROM customers;
  • B. SELECT DATE ‘2019-01-01’ FROM DUAL;
  • B. SELECT NVL2(cust_credit_limit * .15,'Not Available') FROM customers;
  • C. SELECT ‘2019-01-01’ FROM DUAL;
  • C. SELECT NVL(cust_credit_limit * .15,'Not Available') FROM customers;
  • D. SELECT TO_CHAR(‘2019-01-01’) FROM DUAL;
  • D. SLECT NVL(TO_CHAR(cust_credit_limit * .15),'Not available') from customers;
  • E. SELECT TO_DATE(‘2019-01-01’, ‘YYYY-MM-DD’) FROM DUAL;
  • E. SELECT NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),'NOT Available') FROM customers;
Answer:

D, E

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
0 / 1000

Question 4

Choose two
Examine the description of the PRODUCT DETALS table:

  • A. It produces an error because subqueries cannot be used with the CASE expression.
  • A. PRODUCT_ID can be assigned the PEIMARY KEY constraint.
  • B. It shows COST_REMARK for all the promos in the promo category ‘TV’.
  • B. EXPIRY_DATE cannot be used in arithmetic expressions.
  • C. It shows COST_REMARK for all the promos in the table.
  • C. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it
  • D. It produces an error because the subquery gives an error.
  • D. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it
  • E. PRODUCT_PRICE contains the value zero by default if no value is assigned to it.
  • F. PRODUCT_NAME cannot contain duplicate values.
Answer:

A, D

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
F
0 / 1000

Question 5

The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.
The table has two rows whose COST_LAST_MANE values are Anderson and Ausson.
Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the
second?

  • A. Data selected by a view's defining query is deleted from its underlying tables when the view is dropped.
  • A. SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;
  • B. Read only views cannot be dropped.
  • B. SELECT REPLACE (TRIM(TRALING'son' FROM cust_last_name),'An','O') FROM customers;
  • C. The creator of a view to be dropped must have the DROP ANY VIEW privilege.
  • C. SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM customers;
  • D. CASCADE CONSTRAINTS must be specified when referential integrity constraints on other objects refer to primary or unique keys in the view to be dropped.
  • D. SELECT REPLACE (SUBSTR(cust_last_name,-3),'An','O') FROM customers;
  • E. Views referencing a dropped view become invalid.
Answer:

A

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
0 / 1000

Question 6

Which three statements are true about indexes and their administration in an Oracle database?

  • A. CEIL: can be used for positive and negative numbers
  • A. An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table.
  • B. FLOOR: returns the smallest integer greater than or equal to a specified number
  • B. An index can be created as part of a CREATE TABLE statement.
  • C. TRUNC: can be used with NUMBER and DATE values
  • C. A DROP INDEX statement always prevents updates to the table during the drop operation
  • D. CONCAT: can be used to combine any number of values
  • D. A UNIQUE and non-unique index can be created on the same table column
  • E. MOD: returns the quotient of a division operation
  • E. A descending index is a type of function-based index
  • F. If a query filters on an indexed column then it will always be used during execution of the query
Answer:

B, C, E

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
0 / 1000

Question 7

Which three are true about granting object privileges on tables, views, and sequences?

  • A. SELECT TO_DATE(SYSDATE, ‘FMDAY, DD MONTH, YYYY’) FROM DUAL;
  • A. UPDATE can be granted only on tables and views.
  • B. SELECT TO_CHAR(SYSDATE, ‘FMDD, DAY MONTH, YYYY’) FROM DUAL;
  • B. DELETE can be granted on tables, views, and sequences.
  • C. SELECT TO_CHAR(SYSDATE, ‘FMDAY, DDTH MONTH, YYYY’) FROM DUAL;
  • C. REFERENCES can be granted only on tables and views.
  • D. SELECT TO_CHAR(SYSDATE, ‘FMDAY, DD MONTH, YYYY’) FROM DUAL;
  • D. INSERT can be granted on tables, views, and sequences.
  • E. SELECT can be granted only on tables and views.
  • F. ALTER can be granted only on tables and sequences.
Answer:

A, C, F

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
F
0 / 1000

Question 8

The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type
DATE
You want to display the date of the first Monday after the completion of six months since hiring.
The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first
day of the week Which query can be used?

  • A. The ORDER BY Clause can be used in a subquery.
  • A. SELECT emp_id,NEXT_DAY(ADD_MONTHS(hite_date,6),'MONDAY') FROM employees;
  • B. A subquery can be used in the FROM clause of a SELECT statement.
  • B. SELECT emp_id,ADD_MONTHS(hire_date,6), NEXT_DAY('MONDAY') FROM employees;
  • C. If a subquery returns NULL, the main query may still return rows.
  • C. SELECT emp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6) FROM employees;
  • D. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
  • D. SELECT emp_id,NEXT_DAY(ADD_MONTHS(hire_date,6),1) FROM employees;
  • E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.
Answer:

A

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
0 / 1000

Question 9

Which three statements are true about views in an Oracle database?

  • A. There is only one subquery in the outer query and the inner query returns one or more values
  • A. A SELECT statement cannot contain a where clause when querying a view containing a WHERE clause in its defining query
  • B. The inner query returns a single value to the outer query.
  • B. Rows inserted into a table using a view are retained in the table if the view is dropped
  • C. The inner query uses an aggregating function and returns one or more values.
  • C. Views can join tables only if they belong to the same schema.
  • D. The inner query returns one or more values and the outer query returns a single value.
  • D. Views have no segment.
  • E. Views have no object number.
  • F. A view can be created that refers to a non-existent table in its defining query.
Answer:

B, D, F

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
F
0 / 1000

Question 10

Which two statements are true about Oracle synonyms?

  • A. Option A
  • A. A synonym can have a synonym.
  • B. Option B
  • B. All private synonym names must be unique in the database.
  • C. Option C
  • C. Any user can create a PUBLIC synonym.
  • D. Option D
  • D. A synonym can be created on an object in a package.
  • E. Option E
  • E. A synonym has an object number.
Answer:

A, E

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
0 / 1000

Question 11

Which two statements are true about a self join?

  • A. The NOT IN operator is equivalent to IS NULL.
  • A. The join key column must have an index.
  • B. The
  • B. It can be a left outer join.
  • C. =ANY and =ALL operators have the same functionality.
  • C. It must be a full outer join.
  • D. The IN operator cannot be used in single-row subqueries.
  • D. It can be an inner join.
  • E. The NOT operator can be used with IN, ANY and ALL operators.
  • E. It must be an equijoin.
Answer:

B, D

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
0 / 1000

Question 12

Which three statements are true about dropping and unused columns in an Oracle database?

  • A. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
  • A. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
  • B. Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column.
  • B. A DROP COLUMN command can be rolled back.
  • C. Rows are displayed in the order that they are stored in the table only for the first three rows.
  • C. An UNUSED column's space is remained automatically when the block containing that column is next queried.
  • D. Rows are displayed sorted in ascending order of the values in the third column in the table.
  • D. An UNUSED column's space is remained automatically when the row containing that column is next queried.
  • E. Partition key columns cannot be dropped.
  • F. A column that is set to NNUSED still counts towards the limit of 1000 columns per table.
Answer:

A, E, F

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
F
0 / 1000

Question 13

Examine this query:
SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL; What is the result?

  • A. The statement would execute successfully to produce the required output.
  • A. 16
  • B. The statement would not execute because inline views and outer joins cannot be used together.
  • B. 160
  • C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
  • C. 150
  • D. The statement would not execute because the GROUP BY clause cannot be used in the inline.
  • D. 200
  • E. 100
Answer:

D

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
0 / 1000

Question 14

Examine this SQL statement:

Which two are true?

  • A. No updates can be made to the data in the DEPARTMENT_ID column.
  • A. The subquery is executed before the UPDATE statement is executed.
  • B. A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.
  • B. All existing rows in the ORDERS table are updated.
  • C. A query can be display data from the DEPARTMENT_ID column.
  • C. The subquery is executed for every updated row in the ORDERS table.
  • D. The DEPARTMENT_ID column is set to null for all rows in the table.
  • D. The UPDATE statement executes successfully even if the subquery selects multiple rows.
  • E. The DEPARTMENT_ID column can be recovered from the recycle bin.
  • E. The subquery is not a correlated subquery.
  • F. The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.
Answer:

B, C

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
E
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
E
F
0 / 1000

Question 15

Examine the description of the PRODUCTS table:

Which three queries use valid expressions?

  • A. It generates an error because = is not valid and should be replaced by the IN operator.
  • A. SELECT produet_id, unit_pricer, 5 "Discount",unit_price+surcharge-discount FROM products;
  • B. It executes successfully but does not give the correct output.
  • B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
  • C. It executes successfully and gives the correct output.
  • C. SELECT ptoduct_id, (expiry_date-delivery_date) * 2 FROM products;
  • D. It generate an error because the subquery does not have a GROUP BY clause.
  • D. SPLECT product_id, expiry_date * 2 FROM products;
  • E. SELEGT product_id, unit_price, unit_price + surcharge FROM products;
  • F. SELECT product_id,unit_price || "Discount", unit_price + surcharge-discount FROM products;
Answer:

B, C, E

User Votes:
A
50%
A
50%
B
50%
B
50%
C
50%
C
50%
D
50%
D
50%
E
50%
F
50%
Discussions
vote your answer:
A
A
B
B
C
C
D
D
E
F
0 / 1000
To page 2