2024 Oracle 1Z0-071 Exam Prep: Practice Tests & Study Materials, Oracle Database SQL | SPOTO

Prepare effectively for the Oracle Database SQL (1Z0-071) certification with SPOTO's 2024 updated practice tests and study materials. Our platform offers a variety of resources, including practice tests, free test sessions, and comprehensive exam practice tools. Access a wide selection of online exam questions, sample questions, and exam dumps that closely mimic the actual exam. Our exam questions and answers are meticulously crafted to ensure a thorough understanding of Oracle SQL and PL/SQL technology. Whether you're an IT student eager to expand your SQL database knowledge or a junior programmer aiming for Oracle certification, our mock exams provide invaluable insights into your readiness. Utilize our latest practice tests to build confidence and successfully pass the certification exam, laying a strong foundation for your future career progression.

Take other online exams

Question #1

Examine these statements and results SQL> SELECT COUNT(*) FROM emp COUNT(*) --------------------- 14 sQL> CREATE GLOBAL TEMPORARY TABLE t emp As SELECT * FROM emp; Table created SQL> INSERT INTo temp SELECT * FROM emp; 14 rows created SQL> COMMIT: Commit complete* SQL> INSERT INTo temp SELECT * EROM emp; 14. rows created SQL> SELECT COUNT(*) FROM t emp How many rows are retrieved by the last query?

A. 8

C. 4

D. 2

View answer

Correct Answer: C

Question #2

Examine the structure of the MEMBERS table:Name Null? Type------------------ --------------- ------------------------------MEMBER_ID NOT NULL VARCHAR2 (6)FIRST_NAME VARCHAR2 (50)LAST_NAME NOT NULL VARCHAR2 (50) ADDRESS VARCHAR2 (50)You execute the SQL statement:SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members;What is the outcome?

A. It fails because the alias name specified after the column names is invalid

B. It fails because the space specified in single quotation marks after the first two column names is invalid

C. It executes successfully and displays the column details in a single column with only the alias column heading

D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias

View answer

Correct Answer: D

Question #3

Which three statements are true reading subqueries? (Choose three.)

A. A Main query can have many subqueries

B. A subquery can have more than one main query

C. The subquery and main query must retrieve date from the same table

D. The subquery and main query can retrieve data from different tables

E. Only one column or expression can be compared between the subquery and main query

F. Multiple columns or expressions can be compared between the subquery and main query

View answer

Correct Answer: ADF

Question #4

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 iscreated with the ON DELETE CASCADE option. Which DELETE statement would execute successfully?

A. DELETE orders o, order_items i WHERE o

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;

View answer

Correct Answer: C

Question #5

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*

View answer

Correct Answer: AC

Question #6

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 departmentsWHERE departments_id=90UNIONSELECT department_id, department_name DEPT_NAME, 'a' FROM departmentsWHERE department_id=10Which 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;

View answer

Correct Answer: BD

Question #7

View the exhibit and examine the structure of the CUSTOMERS table.Which two tasks would require subqueries or joins to be executed in a single statement?

A. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

B. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'

C. listing of customers who do not have a credit limit and were born before 1980

D. finding the number of customers, in each city, whose marital status is 'married'

E. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'

View answer

Correct Answer: AE

Question #8

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. The names of columns selected in each SELECT statement must be identical

C. The number of columns selected in each SELECT statement must be identical

D. Duplicates are eliminated automatically by the UNION ALL operator

E. NULLS are not ignored during duplicate checking

View answer

Correct Answer: CE

Question #9

You must write a query that prompts users for column names and conditions every time it is executed.The user must be prompted only once for the table name.Which statement achieves those objectives?

A. SELECT &col1, '&col2' FROM &table WHERE &&condition = '&cond';

B. SELECT &col1, &col2 FROM "&table" WHERE &condition = &cond

C. SELECT &col1, &col2 FROM &&table WHERE &condition = &cond

D. SELECT &col1, &col2 FROM &&table WHERE &condition = &&cond

View answer

Correct Answer: C

Question #10

Which three statements are true regarding subqueries? (Choose three.)

A. The ORDER BY Clause can be used in a subquery

B. A subquery can be used in the FROM clause of a SELECT statement

C. If a subquery returns NULL, the main query may still return rows

D. A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause

E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery

View answer

Correct Answer: CE

Question #11

You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference. Which method or feature should you use?

A. Compare Period ADDM report

B. AWR Compare Period report

C. Active Session History (ASH) report

D. Taking a new snapshot and comparing it with a preserved snapshot

View answer

Correct Answer: ABC

Question #12

View the exhibit and examine the structure of ORDERS and CUSTOMERS tables. ORDERS Name Null? Type ORDER_ID NOT NULL NUMBER(4) ORDER_DATE NOT NULL DATE ORDER_MODE VARCHAR2(8) CUSTOMER_ID NOT NULL NUMBER(6) ORDER_TOTAL NUMBER(8, 2) CUSTOMERS Name Null? Type CUSTOMER_ID NOT NULL NUMBER(6) CUST_FIRST_NAME NOT NULL VARCHAR2(20) CUST_LAST_NAME NOT NULL VARCHAR2(20) CREDIT_LIMIT NUMBER(9,2) CUST_ADDRESS VARCHAR2(40) Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUS

A. INSERT INTO (SELECT o

B. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total);VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);

C. INSERT INTO ordersVALUES (1,'10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);

D. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total);VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);

View answer

Correct Answer: B

Question #13

Which statement correctly grants a system privilege?

A. GRANT CREATE VIEWON table1 TOuser1;

B. GRANT ALTER TABLETO PUBLIC;

C. GRANT CREATE TABLETO user1, user2;

D. GRANT CREATE SESSIONTO ALL;

View answer

Correct Answer: C

Question #14

Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?(Choose four.)

A. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted

B. Only the primary key can be defined at the column and table level

C. The foreign key columns and parent table primary key columns must have the same names

D. A table can have only one primary key and one foreign key

E. A table can have only one primary key but multiple foreign keys

F. Primary key and foreign key constraints can be defined at both the column and table level

G. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted

View answer

Correct Answer: CEFG

Question #15

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

View answer

Correct Answer: ABD

Question #16

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*

B. SELECT TO_CHAR(NVL(cust_credit_limit*

C. SELECT NVL(cust_credit_limit*

D. SELECT NVL(cust_credit_limit,Not Available)*

View answer

Correct Answer: A

Question #17

Examine the structure of the EMPLOYEES table. (Choose two.) You must display the maximum and minimum salaries of employees hired 1 year ago. Which two statements would provide the correct output?

A. SELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);

B. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE-365)GROUP BY maxsal, minsal;

C. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);

D. SELECT MIN(Salary), MAX(salary)FROM (SELECT salary FROM employeesWHERE hire_date < SYSDATE-365);

View answer

Correct Answer: B

Question #18

A non-correlated subquery can be defined as __________. (Choose the best answer.)

A. A set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query

B. A set of sequential queries, all of which must return values from the same table

C. A set of sequential queries, all of which must always return a single value

D. A SELECT statement that can be embedded in a clause of another SELECT statement only

View answer

Correct Answer: A

Question #19

Which two are SQL features?

A. roviding graphical capabilities

B. roviding variable definition capabilities

C. roviding database transaction control

D. rocessing sets of data

E. roviding update capabilities for data in external files

View answer

Correct Answer: CD

Question #20

Which two statements are true regarding roles? (Choose two.)

A. A role can be granted to itself

B. A role can be granted to PUBLIC

C. A user can be granted only one role at any point of time

D. The REVOKE command can be used to remove privileges but not roles from other users

E. Roles are named groups of related privileges that can be granted to users or other roles

View answer

Correct Answer: C

Question #21

Examine the description of the PRODUCT_DETAILS table:Which two statements are true? (Choose two.)

A. PRODUCT_PRICE contains the value zero by default if no value is assigned to it

B. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it

C. EXPIRY_DATE cannot be used in arithmetic expressions

D. PRODUCT_ID can be assigned the PRIMARY KEY constraint

E. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it

F. PRODUCT_NAME cannot contain duplicate values

View answer

Correct Answer: BD

Question #22

Which three statements are true about Structured Query Language (SQL)? (Choose three.)

A. It best supports relational databases

B. It is used to define encapsulation and polymorphism for a relational table

C. It is the only language that can be used for both relational and object-oriented databases

D. It guarantees atomicity, consistency, isolation, and durability (ACID) features

E. It provides independence for logical data structures being manipulated from the underlying physical data storage

F. It requires that data be contained in hierarchical data storage

View answer

Correct Answer: ADE

Question #23

View the Exhibit and examine the structure of CUSTOMERS table.Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for allcustomers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.Which SQL statement would produce the required result?

A. SELECT NVL (TO CHAR(cust_credit_limit *

B. SELECT TO_CHAR (NVL(cust_credit_limit *

C. SELECT NVL(cust_credit_limit *

D. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT" FROM customers;

View answer

Correct Answer: A

Question #24

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. (Choose two.) Evaluate this SQL statement: ALTER TABLE emp DROP COLUMN first_name; Which two statements are true?

A. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement

B. The FIRST_NAME column would be dropped provided at least one column remains in the table

C. The FIRST_NAME column would be dropped provided it does not contain any data

D. The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement

View answer

Correct Answer: D

Question #25

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. (Choose the best answer.) You executed this UPDATE statement: UPDATE ( SELECT order_date, order_total, customer_id FROM orders) Set order_date = '22-mar-2007' WHERE customer_id IN (SELECT customer_id FROM customers WHERE cust_last_name = 'Roberts' AND credit_limit = 600); Which statement is true regarding the execution?

A. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement

B. It would not execute because two tables cannot be referenced in a single UPDATE statement

C. It would execute and restrict modifications to the columns specified in the SELECT statement

D. It would not execute because a SELECT statement cannot be used in place of a table name

View answer

Correct Answer: AEF

View Answers after Submission

Please submit your email and WhatsApp to get the answers of questions.

Note: Please make sure your email ID and Whatsapp are valid so that you can get the correct exam results.

Email:

Whatsapp/phone number:

DON'T WANT TO MISS A THING?

100% Pass Cisco, PMP, CISA, CISM, AWS Practice test on SALE!
Get Now

2024 Oracle 1Z0-071 Exam Prep: Practice Tests & Study Materials, Oracle Database SQL | SPOTO

View Answers after Submission

DON'T WANT TO MISS A THING?

100% Pass Cisco, PMP, CISA, CISM, AWS Practice test on SALE! Get Now

2024 Oracle 1Z0-071 Exam Prep: Practice Tests & Study Materials, Oracle Database SQL | SPOTO

View Answers after Submission

100% Pass Cisco, PMP, CISA, CISM, AWS Practice test on SALE!
Get Now