1Z0-071 Practice Exam — Free Oracle Certification Prep

Join Us Among the Stars

Sign Up & unlock 100% of Exam Questions

Log in / Sign up

No Strings Attached!

276Practice Questions

3Study Modes

Free

TopicSort

Question 1

Relational Database concepts

Which two are SQL features? (Choose two.)

A providing graphical capabilities
B processing sets of data
C providing database transaction control
D providing update capabilities for data in external files
E providing variable definition capabilities

Question 2

Relational Database concepts

Question 3

Managing Schema Objects

Question 4

Manipulating Data

Question 5

Controlling User Access

Question 6

Using Subqueries to Solve Queries

Question 7

Manipulating Data

Question 8

Restricting and sorting data

Question 9

Managing Schema Objects

Question 10

Displaying Data from Multiple Tables

Question 11

Reporting Aggregated Data Using the Group Functions

Question 12

Managing Objects with Data Dictionary Views

Question 13

Using Subqueries to Solve Queries

Question 14

Using Single-Row Functions to Customize Output

Question 15

Displaying Data from Multiple Tables

Question 16

Restricting and sorting data

Question 17

Restricting and sorting data

Question 18

Using Conversion Functions and Conditional Expressions

Question 19

Reporting Aggregated Data Using the Group Functions

Question 20

Using Subqueries to Solve Queries

Question 21

Controlling User Access

Question 22

Using Single-Row Functions to Customize Output

Question 23

Manipulating Data

Question 24

Managing Schema Objects

Question 25

Controlling User Access

Page 1 of 12 • Questions 1-25 of 276

1 2 3 4 5

→

Know a question that should be here? Contribute to this exam

Examine this statement:

Question Image

On which two columns of the table will an index be created automatically? (Choose two.)

A ORDER_ID
B ORDER_TOTAL
C ORDER_DATE
D PRODUCT_ID
E STATUS
F SERIAL_NO

Which two are true about creating tables in an Oracle database? (Choose two.)

A Creating an external table will automatically create a file using the specified directory and file name.
B A system privilege is required.
C The same table name can be used for tables in different schemas.
D A primary key constraint is mandatory.
E A CREATE TABLE statement can specify the maximum number of rows the table will contain.

Examine these statements executed in a single Oracle session:

Question Image

Which three statements are true? (Choose three.)

A The code for pen is 10.
B There is no row containing fountain pen.
C There is no row containing pen.
D There is no row containing pencil.
E The code for fountain pen is 3.
F The code for pen is 1.

Which two are true about granting privileges on objects? (Choose two.)

A An object privilege can be granted to other users only by the owner of that object.
B The owner of an object acquires all object privileges on that object by default.
C The WITH GRANT OPTION clause can be used only by DBA users.
D An object privilege can be granted to a role only by the owner of that object.
E A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table.

Question 6

Using Subqueries to Solve Queries

Question 7

Manipulating Data

Question 8

Restricting and sorting data

Question 9

Managing Schema Objects

Question 10

Displaying Data from Multiple Tables

Question 11

Reporting Aggregated Data Using the Group Functions

Question 12

Managing Objects with Data Dictionary Views

Question 13

Using Subqueries to Solve Queries

Question 14

Using Single-Row Functions to Customize Output

Question 15

Displaying Data from Multiple Tables

Question 16

Restricting and sorting data

Question 17

Restricting and sorting data

Question 18

Using Conversion Functions and Conditional Expressions

Question 19

Reporting Aggregated Data Using the Group Functions

Question 20

Using Subqueries to Solve Queries

Question 21

Controlling User Access

Question 22

Using Single-Row Functions to Customize Output

Question 23

Manipulating Data

Question 24

Managing Schema Objects

Question 25

Controlling User Access

Examine this partial statement:

Question Image

Which is true?

A Both the query and the subquery can select only zero rows or one row.
B Both the query and the subquery can select any number of rows.
C The query can select only zero rows or one row, but the subquery can select any number of rows.
D The query can select any number of rows, but the subquery can select only zero rows or one row.

Examine this statement which executes successfully:

Question Image

Which is true?

A Only if the salary is 20000 or less and the employee id is 125 or higher, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.
B Regardless of salary and employee id, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.
C Regardless of salary, only if the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.
D Only if the salary is 20000 or less and the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.

Examine the description of the BOOKS_TRANSACTIONS table:

Question Image

Examine this partial SQL statement:

SELECT * FROM books_transactions

Which two WHERE conditions give the same result? (Choose two.)

A WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');
B WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));
C WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN ('A101', 'A102');
D WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' OR member_id IN ('A101', 'A102');
E WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101', 'A102');

Which two statements are true about Oracle synonyms? (Choose two.)

A Users must have the required privileges on the underlying objects to use public synonyms.
B Synonyms can be created for roles.
C Synonyms cannot be created for sequences.
D Synonyms cannot be created for synonyms.
E Synonyms can be created for packages.
F Users must have the DBA role to create public synonyms.

Which two are true about self joins? (Choose two.)

A They can use INNER JOIN and LEFT JOIN.
B They require the EXISTS operator in the join condition.
C They require the NOT EXISTS operator in the join condition.
D They require table aliases.
E They have no join condition.
F They are always equijoins.

Examine the description of the EMPLOYEES table:

Question Image

You write this failing statement:

Question Image

Which clause causes the error?

A SELECT
B GROUP BY
C WHERE
D ORDER BY

Which two statements are true about the data dictionary?

A The data dictionary is accessible when the database is closed.
B The data dictionary does not store metadata in tables.
C Views with the prefix ALL_, DBA_ and USER_ are not all available for every type of metadata.
D Views with the prefix DBA_ display only metadata for objects in the SYS schema.
E Views with the prefix ALL_ display metadata for objects to which the current user has access.

Examine the description of the COUNTRIES table:

Question Image

Examine the description of the DEPARTMENTS table:

Question Image

Examine the description of the LOCATIONS table:

Question Image

Which two queries will return a list of countries with no departments? (Choose two.)

A
B
C
D

You execute this query:

SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'), 'dd "Monday for" fmMonth rrrr')
FROM DUAL;

What is the result?

A It executes successfully but does not return any result.
B It returns the date for the first Monday of the next month.
C It returns the date for the last Monday of the current month.
D It generates an error.

Which three statements are true about a self join? (Choose three.)

A It must be an equijoin.
B The ON clause must be used.
C It must be an inner join.
D It can be an outer join.
E The ON clause can be used.
F The query must use two different aliases for the table.

Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

A WHERE and HAVING clauses can be used in the same statement only if applied to different table columns.
B The WHERE clause can be used to exclude rows after dividing them into groups.
C The HAVING clause can be used with aggregating functions in subqueries.
D The WHERE clause can be used to exclude rows before dividing them into groups.
E Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query.

Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION?

A Column positions must be used in the ORDER BY clause.
B Only column names from the first SELECT statement in the compound query are recognized.
C Each SELECT statement in the compound query must have its own ORDER BY clause.
D The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause.
E Each SELECT statement in the compound query can have its own ORDER BY clause.

You need to calculate the number of days from 1st January 2019 until today.

Dates are stored in the default format of DD-MON-RR.

Which two queries give the required output? (Choose two.)

A SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2019' FROM
B SELECT ROUND(SYSDATE - '01-JAN-2019') FROM DUAL;
C SELECT ROUND(SYSDATE – TO_DATE('01/JANUARY/2019')) FROM DUAL;
D SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL;
E SELECT SYSDATE - TO_DATE('01-JANUARY-2019') FROM DUAL;

Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?

A
B
C
D

Examine these statements which execute successfully:

Question Image

Both statements display departments ordered by their average salaries.

Which two are true? (Choose two.)

A Both statements will execute successfully if you add E.AVG_SAL to the select list.
B Both statements will display departments with no employees.
C Only the second statement will execute successfully if you add E.AVG_SAL to the select list.
D Only the first statement will display departments with no employees.
E Only the second statement will display departments with no employees.
F Only the first statement will execute successfully if you add E.AVG_SAL to the select list.

Which three actions can you perform only with system privileges? (Choose three.)

A Query any table in a database.
B Execute a procedure in another schema.
C Log in to a database instance.
D Access flat files, which are stored in an operating system directory, via the UTL_FILE package.
E Use the WITH GRANT OPTION clause.

Which two statements will convert the string Hello World to ello world? (Choose two.)

A SELECT INITCAP(TRIM(‘H’ FROM ‘Hello World’)) FROM DUAL;
B SELECT SUBSTR(‘Hello World’, 2) FROM DUAL;
C SELECT LOWER(SUBSTR(‘Hello World’, 2, 1)) FROM DUAL;
D SELECT LOWER(TRIM(‘H’ FROM ‘Hello World’)) FROM DUAL;
E SELECT LOWER(SUBSTR(‘Hello World’, 2)) FROM DUAL;

Examine the description of the TRANSACTIONS table:

Question Image

Which two SQL statements execute successfully? (Choose two.)

A SELECT customer_id AS “CUSTOMER_ID”, transaction_date AS “DATE”, amount + 100 DUES FROM transactions;
B SELECT customer_id AS ‘CUSTOMER_ID’, transaction_date AS DATE, amount + 100 ‘DUES’ FROM transactions;
C SELECT customer_id AS “CUSTOMER_ID”, transaction_date AS DATE, amount + 100 “DUES” FROM transactions;
D SELECT customer_id AS CUSTOMER_ID, transaction_date AS TRANS_DATE, amount + 100 “DUES AMOUNT” FROM transactions;
E SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;

Which two statements are true about external tables? (Choose two.)

A DML statements cannot be used on them.
B You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command.
C Their data can be retrieved by using only SQL or PL/SQL.
D Indexes can be created on them.
E Their metadata and actual data are both stored outside the database.

Which three are true about system and object privileges? (Choose three.)

A WITH GRANT OPTTON cannot be used when granting an object privilege to PUBLIC.
B WITH GRANT OPTION can be used when granting an object privilege to both users and roles.
C Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect.C. Adding a primary key constraint to an existing table in another schema requires a system privilege.
D Adding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege.
E Revoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect.

Join Us Among the Stars

1Z0-071Preview

About the 1Z0-071 Exam

Mode Selection

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Question 11

Question 12

Question 13

Question 14

Question 15

Question 16

Question 17

Question 18

Question 19

Question 20

Question 21

Question 22

Question 23

Question 24

Question 25

Question 6

Question 7

Question 8

Question 9

Question 10

Question 11

Question 12

Question 13

Question 14

Question 15

Question 16

Question 17

Question 18

Question 19

Question 20

Question 21

Question 22

Question 23

Question 24

Question 25