[Nov-2021] 1z0-071 Pre-Exam Practice Tests | Exam Questions and Answers for Oracle PL/SQL Developer Certified Associate Study Guide
Oracle Database SQL Certification Sample Questions
Benefits in Obtaining Oracle 1Z0-071: Oracle Database SQL Exam Certification
By integrating a broad range of relevant data base functionality, roles and activities, Oracle 1Z0-071 Certification enhances your experience and expertise. Oracle Database qualification prepares you through research, laboratories and experience to complete challenging and practical tasks. With this certificate, the knowledge and familiarity with Oracle RAC and Grid Infrastructure will be improved. After receiving this credential, RAC database, Clusterware and ASM environment may be installed, maintained, tuned and retrieved. 1Z0-071 practice exams are the best way to start the preparation.
The attainment of this credential offers you a strategic edge by translating your know-how into a worldwide market. 80 percent of Oracle accredited people have indicated that qualification has helped them promote, raise salaries or boost their job. You’ll still be able to get a digital badge on your LinkedIn profiles. Although you are not sure which alternative is the right one, label the best choice as your response so that any query shouldn’t be left as unanswered at 1Z0-071 practice test.
For more info, read the reference:
Oracle Database SQL Exam Number: 1Z0-071
NEW QUESTION 123
View the Exhibit and examine the structure of the ORDERS table. (Choose the best answer.)
You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.
Which query would give you the desired result?
- A. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
- B. SELECT order_id, order_date FROM ordersWHERE order_date > ANY(SELECT order_date FROM orders WHERE customer_id = 101);
- C. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT order_date FROM orders WHERE customer_id = 101);
- D. SELECT order_id, order_date FROM ordersWHERE order_date > IN(SELECT order_date FROM orders WHERE customer_id = 101);
Answer: C
NEW QUESTION 124
Which two are true about dropping columns from a table?
- A. A column drop is implicitly committed.
- B. A primary key column cannot be dropped.
- C. A column must be set as unused before it is dropped from a table.
- D. Multiple columns can be dropped simultaneously using the ALTER TABLE command.
- E. A column can be removed only if it contains no data.
- F. A column that is referenced by another column in any other table cannot be dropped.
Answer: A,D,F
NEW QUESTION 125
Examine the structure proposed for the TRANSACTIONS table:
Which two statements are true regarding the storage of data in the above table structure? (Choose two.)
- A. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
- B. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.
- C. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
- D. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
Answer: A,C
NEW QUESTION 126
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 output?
- A. ORDER BY DEPT_NAME;
- B. ORDER BY DEPT_ID;
- C. ORDER BY 'b';
- D. ORDER BY 3;
Answer: B,D
NEW QUESTION 127
In which normal form is a table, if it has no multi-valued attributes and no partial dependencies?
- A. third normal form
- B. fourth normal form
- C. first normal form
- D. second normal form
Answer: D
Explanation:
Explanation
https://blog.udemy.com/database-normal-forms/
NEW QUESTION 128
Evaluate the following SQL statements that are issued in the given order: CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_FK?
- A. It would remain disabled and has to be enabled manually using the ALTER TABLE command.
- B. It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
- C. It would be automatically enabled and immediate.
- D. It would be automatically enabled and deferred.
Answer: B,C,D
NEW QUESTION 129
Examine the structure of the SALES table. (Choose two.)
Examine this statement:
SQL > CREATE TABLE sales1 (prod_id, cust_id, quantity_sold, price)
AS
SELECT product_id, customer_id, quantity_sold, price
FROM sales
WHERE 1 = 2;
Which two statements are true about the SALES1 table?
- A. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
- B. It will not be created because of the invalid WHERE clause.
- C. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
- D. It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
- E. It is created with no rows.
Answer: C,E
NEW QUESTION 130
You need to display the date 11-oct-2007 in words as 'Eleventh of October, Two Thousand Seven'.
Which SQL statement would give the required result?
- A. SELECT TO_CHAR ('11-oct-2007', 'fmDdspth "of" Month, Year')FROM DUAL
- B. SELECT TO_CHAR (TO_DATE ('11-oct-2007'), 'fmDdspth of month, year')FROM DUAL
- C. SELECT TO_CHAR (TO_DATE ('11-oct-2007'), 'fmDdthsp "of" Month, Year')FROM DUAL
- D. SELECT TO_DATE (TO_CHAR ('11-oct-2007'), 'fmDdspth "of" Month, Year'))FROM DUAL
Answer: B
NEW QUESTION 131
Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.)
- A. The output is sorted by the UNION ALL operator
- B. NULLS are not ignored during duplicate checking
- C. The names of columns selected in each SELECT statement must be identical
- D. The number of columns selected in each SELECT statement must be identical
- E. Duplicates are eliminated automatically by the UNION ALL operator
Answer: B,D
NEW QUESTION 132
View the exhibit and examine the structure of the STORES table.
You must display the NAME of stores along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.
The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?
- A. SELECT name, concat(address||', '||city||', ',country) AS full_address, start_date, property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,'01-JAN-2000') <=36;
- B. SELECT name, concat(address||', '||city||', ', country) AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
- C. SELECT name, address||', '||city||', '||country AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
- D. SELECT name, concat(address||', '||city||', ',country) AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
Answer: B
NEW QUESTION 133
Evaluate these commands which execute successfully:
Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?
- A. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.
- B. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.
- C. If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.
- D. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times.
- E. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.
Answer: A,E
NEW QUESTION 134
Examine the structure of the BOOKS_ TRANSACTIONS table:
Examine the SQL statement:
Which statement is true about the outcome?
- A. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
- B. It displays details for members who have borrowed before today's date with either RM as TRANSACTION_TYPE or MEMBER_ID as A101 and A102.
- C. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.
- D. It displays details for only members A101and A102 who have borrowed before today with RM as TRANSACTION_TYPE.
Answer: A
NEW QUESTION 135
Examine the SQL statement used to create the TRANSACTION table. (Choose the best answer.) SQL > CREATE TABLE transaction (trn_id char(2) primary key, Start_date date DEFAULT SYSDATE, End_date date NOT NULL); The value 'A1' does not exist for trn_id in this table.
Which SQL statement successfully inserts a row into the table with the default value for START_DATE?
- A. INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')
- B. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10))
- C. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))
- D. INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-2014')
Answer: A
NEW QUESTION 136
View the Exhibit and examine the structure of the PORDUCT_INFORMATION table. (Choose the best answer.)
PRODUCT_ID column is the primary key.
You create an index using this command:
SQL > CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table.
Which query would use the UPPER_NAME_IDX index?
- A. SELECT UPPER(product_name)FROM product_informationWHERE product_id = 2254;
- B. SELECT product_idFROM product_informationWHERE UPPER(product_name) IN ('LASERPRO',
'CABLE'); - C. SELECT product_id, UPPER(product_name)FROM product_informationWHERE
UPPER(product_name) = 'LASERPRO' OR list_price > 1000; - D. SELECT UPPER(product_name)FROM product_information;
Answer: B
NEW QUESTION 137
Which three statements are true? (Choose three.)
- A. Views with the same name but different prefixes, such as DBA, ALLand USER, reference the same base tables from the data dictionary.
- B. The data dictionary is created and maintained by the database administrator.
- C. The usernames of all the users including database administrators are stored in the data dictionary.
- D. The USER_CONS_COLUMNSview should be queried to find the names of columns to which constraints apply.
- E. Both USER_OBJECTSand CATviews provide the same information about all objects that are owned by the user.
- F. The data dictionary views consist of joins of dictionary base tables and user-defined tables.
Answer: A,C,D
Explanation:
Explanation
Explanation/Reference:
References:
https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm
NEW QUESTION 138
Examine the structure of the EMPLOYEES table.
You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output? (Choose two.)
- A. SELECT MIN(Salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365
GROUP BY MIN(salary), MAX(salary); - B. SELECT minsal, maxsal
FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365
GROUP BY MIN(salary), MAX(salary); - C. SELECT minsal, maxsal
FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365)
GROUP BY maxsal, minsal; - D. SELECT MIN(Salary), MAX(salary)
FROM (SELECT salary FROM
employees
WHERE hire_date < SYSDATE-365);
Answer: C,D
NEW QUESTION 139
Examine the structure proposed for the TRANSACTIONStable:
Which two statements are true regarding the storage of data in the above table structure? (Choose two.)
- A. The TRANS_VALIDITYcolumn would allow storage of a time interval in days, hours, minutes, and seconds.
- B. The CUST_STATUScolumn would allow storage of data up to the maximum VARCHAR2size of 4,000 characters.
- C. The TRANS_DATEcolumn would allow storage of dates only in the dd-mon-yyyy format.
- D. The CUST_CREDIT_VALUEcolumn would allow storage of positive and negative integers.
Answer: A,D
NEW QUESTION 140
View the Exhibit and examine the structure of the CUSTOMERS table.
Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
- A. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
- B. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
- C. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
- D. It executes successfully.
Answer: D
NEW QUESTION 141
......
Prerequisites
There are no official requirements for passing the Oracle 1Z0-071 exam. However, it is recommended that the students have a good grasp of SQL syntax rules. They also need to be able to use general SQL functions, identify the result of fundamental DDL operations, and know how to implement SQL statements and functions.
Oracle Exam Practice Test To Gain Brilliante Result: https://www.itpassleader.com/Oracle/1z0-071-dumps-pass-exam.html
Tested Material Used To 1z0-071: https://drive.google.com/open?id=1s6GBvvZNbk__xzteCApIsrbl_PGPZTDu