Oracle 1Z0-071 Certification Sample Questions and Answers

1Z0-071 PDF, 1Z0-071 Dumps PDF Free Download, 1Z0-071 Latest Dumps Free PDF, Database SQL PDF DumpsThe Oracle Database SQL (1Z0-071) Sample Question Set is designed to help you prepare for the Oracle Database SQL Certified Associate certification exam. To become familiar with the actual Oracle Certification exam environment, we suggest you try our Sample Oracle 1Z0-071 Certification Practice Exam.

This Oracle Database SQL certification sample practice test and sample question set are designed for evaluation purposes only. If you want to test your Oracle 1Z0-071 knowledge to identify your areas of improvement and get familiar with the actual exam format, we suggest you prepare with the Premium Oracle Database SQL Certified Associate Certification Practice Exam. Our team of Oracle Database experts has designed Questions-Answers for this premium practice exam by collecting inputs from recently certified candidates. Our premium Oracle 1Z0-071 certification practice exam will boost your confidence as well as your actual Oracle Database SQL exam result.

Oracle 1Z0-071 Sample Questions:

01. Which two are true about transactions in the Oracle Database?
(Choose two.)
a) DML statements always start new transactions.
b) DDL statements automatically commit only data dictionary updates caused by executing the DDL.
c) A session can see uncommitted updates made by the same user in a different session.
d) A DDL statement issued by a session with an uncommitted transaction automatically commits that transaction.
e) An uncommitted transaction is automatically committed when the user exits SQL*Plus.
 
02. You need to display the date 11-oct-2017 in words as 'Eleventh of October, Two Thousand Seventeen'. Which SQL statement would give the required result?
a) SELECT TO_CHAR (TO_DATE ('11-oct-2017'), 'fmDdthsp of Month, Year') FROM DUAL;
b) SELECT TO_CHAR ('11-oct-2017', 'fmDdspth or Month, Year') FROM DUAL;
c) SELECT TO_CHAR (TO_DATE ('11-oct-2017'), 'fmDdspth "of" Month, Year') FROM DUAL;
d) SELECT TO_DATE (TO_CHAR ('11-oct-2017'), 'fmDdspth 'of Month, Year')) FROM DUAL;
 
03. Which comparison operators can be used with a multiple-row subquery?
(Choose two.)
a) =
b) >= ALL
c) LIKE
d) IN
 
04. Identify the missing word(s) in the following sentence within the context of Oracle SQL.
If any item in a list following a NOT IN operation evaluates to null, then all rows evaluate to [?] or [?] and no rows are returned.
(Choose two.)
a) ORA-00850
b) -1
c) ORA-00851
d) ERR
e) 0
f) FALSE
g) UNKNOWN
 
05. Which reserved word is not required to form a syntactically correct UPDATE statement?
a) UPDATE
b) SET
c) WHERE
d) None of the above
 
06. You executed the following CREATE TABLE statement that resulted in an error:
SQL> CREATE TABLE employees(emp_id NUMBER(10) PRIMARY KEY, ename VARCHAR2(20), email NUMBER(3) UNIQUE, address VARCHAR2(500), phone VARCHAR2(20), resume LONG, hire_date DATE, remarks LONG, dept_id NUMBER(3) CONSTRAINT emp_dept_id_fk REFERENCES departments (dept_id), CONSTRAINT ename_nn NOT NULL (ename));
Identify two reasons for the error.
(Choose two.)
a) Only one LONG column can be used per table
b) The PRIMARY KEY constraint on the EMP_ID column must have a name and must be defined at the table level only
c) The FOREIGN KEY keyword is missing in the constraint definition
d) The NOT NULL constraint on the ENAME column must be defined at the column level
e) The FOREIGN KEY defined on the DEPT_ID column must be at the table level only
 
07. Which two are true about queries using set operators such as UNION?
(Choose two.)
a) In a query containing multiple set operators, INTERSECT always takes precedence over UNION and UNION ALL.
b) An expression in the first SELECT list must have a column alias for the expression.
c) All set operators are valid on columns of all data types.
d) CHAR columns of different lengths used with a set operator return a VARCHAR2 whose length equals the longest char value.
e) Queries using set operators do not perform implicit conversion across data type groups (e.g. character, numeric).
 
08. When combining two SELECT statements, which set operator produces a different result depending on which SELECT statement precedes or follows the operator?
a) MINUS
b) UNION ALL
c) INTERSECT
d) UNION
 
09. Which two statements are true about a full outer join?
(Choose two.)
a) It includes rows that are returned by an inner join.
b) It returns only unmatched rows from both tables being joined.
c) It includes rows that are returned by a Cartesian product.
d) It returns matched and unmatched rows from both tables being joined.
e) The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.
 
10. 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, so Sunday is the first day of the week.
Which query can be used?
a) SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;
b) SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') FROM employees;
c) SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY('MONDAY') FROM employees;
d) SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;

Answers:

Question: 01

Answer: d, e

Question: 02

Answer: c

Question: 03

Answer: b, d

Question: 04

Answer: f, g

Question: 05

Answer: c

Question: 06

Answer: a, c

Question: 07

Answer: d, e

Question: 08

Answer: a

Question: 09

Answer: a, d

Question: 10

Answer: b

 

Rating: 4.7 / 5 (267 votes)