Amazon
Avaya
Checkpoint
Cisco®
CompTIA
CrowdStrike
EC-Council
EXIN
Fortinet
Google
ISC
ISC2
ITIL®
Juniper
Microsoft
Okta
Oracle
Palo Alto Networks
All Vendors
  1. Home
  2. Oracle
  3. 1Z0-071

Oracle 1Z0-071 Exam (page: 7)
Oracle Database SQL
Updated on: 28-Nov-2025

Viewing Page 7 of 49
1Z0-071 PDF 380 Questions

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:


You want to generate a list of all department IDs along with any course IDs that may have been assigned to them. Which SQL statement must you use?

  1. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id);
  2. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id);
  3. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d. department_id);
  4. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d. department_id);

Answer(s): B



Which two tasks can be performed by using Oracle SQL statements? (Choose two.)

  1. changing the password for an existing database user
  2. connecting to a database instance
  3. querying data from tables in different databases
  4. starting up a database instance
  5. executing operating system (OS) commands in a session

Answer(s): A,C


Reference:

http://www.techonthenet.com/oracle/password.php https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm



View the exhibit for the structure of the STUDENT and FACULTY tables.


You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:

Statement 1

SQL>SELECT faculty_name, COUNT(student_id)
FROM student JOIN faculty
USING (faculty_id, location_id)
GROUP BY faculty_name;

Statement 2

SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty
GROUP BY faculty_name;

Which statement is true regarding the outcome?

  1. Only statement 2 executes successfully and gives the required result.
  2. Only statement 1 executes successfully and gives the required result.
  3. Both statements 1 and 2 execute successfully and give different results.
  4. Both statements 1 and 2 execute successfully and give the same required result.

Answer(s): B



Which statement correctly grants a system privilege?

  1. GRANT CREATE VIEW
    ON table1 TO
    user1;
  2. GRANT ALTER TABLE
    TO PUBLIC;
  3. GRANT CREATE TABLE
    TO user1, user2;
  4. GRANT CREATE SESSION
    TO ALL;

Answer(s): C



View the exhibit and examine the structure of ORDERS and CUSTOMERS tables.


Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

  1. Option A
  2. Option B
  3. Option C
  4. Option D

Answer(s): C



Which three statements are correct regarding indexes? (Choose three.)

  1. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to create a unique index.
  2. Indexes should be created on columns that are frequently referenced as part of any expression.
  3. When a table is dropped, corresponding indexes are automatically dropped.
  4. For each DML operation performed on a table, the corresponding indexes are automatically updated if required.

Answer(s): A,C,D


Reference:

http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/



View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.


You wrote this SQL statement to retrieve EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, for all employees:

SELECT employee_id, first_name, department_name
FROM employees
NATURAL JOIN departments;

The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

  1. The table prefix is missing for the column names in the SELECT clause.
  2. The NATURAL JOIN clause is missing the USING clause.
  3. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
  4. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

Answer(s): D

Explanation:

Natural join needs only one column to be the same in each table. The EMPLOYEES and DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)



Which two statements are true about sequences created in a single instance Oracle database? (Choose two.)

  1. When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.
  2. DELETE <sequencename> would remove a sequence from the database.
  3. The numbers generated by an explicitly defined sequence can only be used to insert data in one table.
  4. CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.
  5. When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.

Answer(s): A,D


Reference:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2012.htm#SQLRF00817 https://docs.oracle.com/cd/A84870_01/doc/server.816/a76989/ch26.htm



Viewing Page 7 of 49



Share your comments for Oracle 1Z0-071 exam with other users:

Saravana Kumar TS 12/8/2023 9:49:00 AM

question: 93 which statement is true regarding the result? sales contain 6 columns and values contain 7 columns so c is not right answer.
INDIA