Free Oracle 1Z0-071 Practice Questions

Question 1

What is true about non-equijoin statement performance? (Choose two.)

1. The BETWEEN condition always performs less well than using the >= and <= conditions.
2. The BETWEEN condition always performs better than using the >= and <= conditions.
3. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
4. Table aliases can improve performance.
5. The join syntax used makes no difference to performance.

Question 2

Which two are true? (Choose two.)

1. ADD_MONTHS adds a number of calendar months to a date.
2. CEIL requires an argument which is a numeric data type.
3. CEIL returns the largest integer less than or equal to a specified number.
4. LAST_DAY returns the date of the last day of the current month only.
5. LAST_DAY returns the date of the last day of the month for the date argument passed to the function.
6. LAST_DAY returns the date of the last day of the previous month only.

Question 3

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

1. A synonym cannot be created for a PL/SQL package.
2. A synonym can be available to all users.
3. A SEQUENCE can have a synonym.
4. Any user can drop a PUBLIC synonym.
5. A synonym created by one user can refer to an object belonging to another user.

Question 4

Which two are true? (Choose two.)

1. CONCAT joins two character strings together.
2. CONCAT joins two or more character strings together.
3. FLOOR returns the largest positive integer less than or equal to a specified number.
4. INSTR finds the offset within a character string, starting from position 0.
5. INSTR finds the offset within a string of a single character only.
6. FLOOR returns the largest integer less than or equal to a specified number.

Question 5

Which two statements are true about TRUNCATE and DELETE? (Choose two.)

1. DELETE can use a WHERE clause to determine which row(s) should be removed.
2. TRUNCATE can use a WHERE clause to determine which row(s) should be removed.
3. TRUNCATE leaves any indexes on the table in an UNUSABLE state.
4. The result of a TRUNCATE can be undone by issuing a ROLLBACK.
5. The result of a DELETE can be undone by issuing a ROLLBACK.

Question 6

The STORES table has a column START_DATE of data type DATE, containing the date the row was inserted. You only want to display details of rows where START_DATE is within the last 25 months. Which WHERE clause can be used?

1. WHERE TO_NUMBER(start_date - SYSDATE) <= 25
2. WHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25
3. WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25
4. WHERE ADD_MONTHS(start_date, 25) <= SYSDATE

Question 7

Which two are true about the precedence of operators and conditions? (Choose two.)

1. || has a higher order of precedence than + (addition).
2. + (addition) has a higher order of precedence than * (multiplication).
3. NOT has a higher order of precedence than AND and OR in a condition.
4. AND and OR have the same order of precedence in a condition.
5. Operators are evaluated before conditions.

Question 8

An Oracle Database session has an uncommitted transaction in progress which updated 5000 rows in a table. In which three situations does the transaction complete thereby committing the updates? (Choose three.)

1. when a CREATE TABLE AS SELECT statement is issued in the same session but fails with a syntax error
2. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT
3. when the session logs out successfully
4. when a CREATE INDEX statement is executed successfully in the same session
5. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT
6. when a COMMIT statement is issued by the same user from another session in the same database instance

Question 9

Which three statements are true about indexes and their administration in an Oracle database? (Choose three.)

1. The same table column can be part of a unique and non-unique index.
2. A descending index is a type of function-based index.
3. An INVINSIBLE index is not maintained when DML is performed on its underlying table.
4. If a query filters on an indexed column then it will always be used during execution of the query.
5. An index can be created as part of a CREATE TABLE statement.
6. An UNUSABLE index is maintained when DML is performed on its underlying table.

Question 10

Which two statements are true about the SET VERIFY ON command? (Choose two.)

1. It displays values for variables used only in the WHERE clause of a query.
2. It displays values for variables created by the DEFINE command.
3. It can be used only in SQL*Plus.
4. It displays values for variables prefixed with &&.
5. It can be used in SQL Developer and SQL*Plus.

Question 11

Which three actions can you perform on an existing table containing data? (Choose three.)

1. Increase the width of a numeric column.
2. Add a new column as the table's first column.
3. Define a default value that is automatically inserted into a column containing nulls.
4. Change a DATE column containing data to a NUMBER data type.
5. Change the default value of a column.
6. Add a new NOT NULL column with a DEFAULT value.

Question 12

Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)? (Choose two.)

1. Rows from unrelated tables cannot be joined.
2. Relating data from a table with data from the same table is implemented with a self join.
3. Implementing a relationship between two tables might require joining additional tables.
4. Every relationship between the two tables must be implemented in a join condition.
5. An inner join relates rows within the same table.

Question 13

Which two are true about virtual columns? (Choose two.)

1. They can be indexed.
2. They can be referenced in the column expression of another virtual column.
3. They cannot have a data type explicitly specified.
4. They can be referenced in the set clause of an update statement as the name of the column to be updated.
5. They can be referenced in the where clause of an update or delete statement.

Question 14

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

1. A sequence’s unallocated cached values are lost if the instance shuts down.
2. A sequence number that was allocated can be rolled back if a transaction fails.
3. A sequence can only be dropped by a DBA.
4. A sequence can issue duplicate values.
5. Sequences can always have gaps.
6. Two or more tables cannot have keys generated from the same sequence.

Question 15

You want to return the current date and time from the user session, with a data type of TIMESTAMP WITH TIME ZONE. Which function will do this?

1. SYSDATE
2. CURRENT_TIMESTAMP
3. LOCALTIMESTAMP
4. CURRENT_DATE

Question 16

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

1. It requires that data be contained in hierarchical data storage.
2. It best supports relational databases.
3. It provides independence for logical data structures being manipulated from the underlying physical data storage.
4. It is the only language that can be used for both relational and object-oriented databases.
5. It guarantees atomicity, consistency, isolation, and durability (ACID) features.
6. It is used to define encapsulation and polymorphism for a relational table.

Question 17

Which two are true about using constraints? (Choose two.)

1. NOT NULL can be specified at the column and at the table level.
2. A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.
3. A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parent table must have the same names.
4. PRIMARY KEY and FOREIGN KEY constraints can be specified at the column and at the table level.
5. A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.
6. A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.

Question 18

You create a table named 123. Which statement runs successfully?

1. SELECT * FROM TABLE(123);
2. SELECT * FROM "123";
3. SELECT * FROM \'123\';
4. SELECT * FROM '123';

Question 19

Which three statements are true about views in an Oracle Database? (Choose three.)

1. A SELECT statement cannot contain a WHERE clause when querying a view containing a WHERE clause in its defining query.
2. Views have no segment.
3. Views have no object number.
4. Views can join tables only if they belong to the same schema.
5. A view can be created that refers to a non-existent table in its defining query.
6. Rows inserted into a table using a view are retained in the table if the view is dropped.

Question 20

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

1. ORDER_ID
2. ORDER_TOTAL
3. ORDER_DATE
4. PRODUCT_ID
5. STATUS
6. SERIAL_NO

Question 21

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

1. Any user can create a PUBLIC synonym.
2. A synonym has an object number.
3. All private synonym names must be unique in the database.
4. A synonym can be created on an object in a package.
5. A synonym can have a synonym.

Question 22

Which two statements are true about the ORDER BY clause? (Choose two.)

1. In a character sort, the values are case-sensitive.
2. NULLS are not included in the sort operation.
3. Numeric values are displayed in descending order if they have decimal positions.
4. Column aliases can be used in the ORDER BY clause.
5. Only columns that are specified in the SELECT list can be used in the ORDER BY clause.

Question 23

Which two statements are true about *_TABLES views? (Choose two.)

1. USER_TABLES displays all tables owned by the current user.
2. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in USER_TABLES.
3. All users can query DBA_TABLES successfully.
4. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in DBA_TABLES.
5. ALL_TABLES displays all tables owned by the current user.
6. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in ALL_TABLES.

Question 24

In your session, the NLS_DATE_FORMAT is DD-MM-YYYY. There are 86400 seconds in a day. Examine this result: DATE ----------- 02-JAN-2020 Which statement returns this?

1. SELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '2' MONTH + INTERVAL '4' DAY - INTERVAL '120' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;
2. SELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '3' MONTH + INTERVAL '7' DAY - INTERVAL '360' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;
3. SELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '2' MONTH + INTERVAL '5' DAY - INTERVAL '120' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;
4. SELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '2' MONTH + INTERVAL '5' DAY - INTERVAL '86410' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;
5. SELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '2' MONTH + INTERVAL '6' DAY - INTERVAL '120' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;

Question 25

Examine this business rule: Each student can work on multiple projects and each project can have multiple students. You must design an Entity Relationship (ER) model for optimal data storage and allow for generating reports in this format: STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true? (Choose two.)

1. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
2. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the projects entity.
3. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the students and projects entities.
4. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into one-to-many relationships.
5. The ER must have a one-to-many relationship between the STUDENTS and PROJECTS entities.

Question 26

Which two are true about the NVL, NVL2, and COALESCE functions? (Choose two.)

1. NVL must have expressions of the same data type.
2. NVL can have any number of expressions in the list.
3. NVL2 can have any number of expressions in the list.
4. COALESCE stops evaluating the list of expressions when it finds the first non-null value.
5. The first expression in NVL2 is never returned.
6. COALESCE stops evaluating the list of expressions when it finds the first null value.

Question 27

Which two statements are true about Oracle databases and SQL? (Choose two.)

1. Updates performed by a database user can be rolled back by another user by using the ROLLBACK command.
2. A query can access only tables within the same schema.
3. The database guarantees read consistency at select level on user-created tables.
4. A user can be the owner of multiple schemas in the same database.
5. When you execute an update statement, the database instance locks each updated row.

Question 28

Which two are true about transactions in the Oracle Database? (Choose two.)

1. DML statements always start new transactions.
2. DDL statements automatically commit only data dictionary updates caused by executing the DDL.
3. A session can see uncommitted updates made by the same user in a different session.
4. A DDL statement issued by a session with an uncommitted transaction automatically commits that transaction.
5. An uncommitted transaction is automatically committed when the user exits SQL*Plus.

Question 29

Which two will execute successfully? (Choose two.)

1. SELECT COALESCE(0, SYSDATE) FROM DUAL;
2. SELECT NVL('DATE', SYSDATZ) FROM DUAL;
3. SELECT COALESCE('DATE', SYSDATE) FROM DUAL;
4. SELECT NVL('DATE', 200) FROM (SELECT NULL AS "DATE" FROM DUAL);
5. SELECT COALESCE('DATE', SYSDATE) FRCM (SELECT NULL AS "DATE" FROM DUAL);

Question 30

Which two are true about unused columns? (Choose two.)

1. A query can return data from unused columns, but no DML is possible on those columns.
2. Unused columns retain their data until they are dropped.
3. Once a column has been set to unused, a new column with the same name can be added to the table.
4. The DESCRIBE command displays unused columns.
5. A primary key column cannot be set to unused.
6. A foreign key column cannot be set to unused.

Question 31

Examine this list of requirements for a sequence: 1. Name: EMP_SEQ 2. First value returned: 1 3. Duplicates are never permitted. 4. Provide values to be inserted into the EMPLOYEES.EMPLOYEE_ID column. 5. Reduce the chances of gaps in the values. Which two statements will satisfy these requirements? (Choose two.)

1. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;
2. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;
3. CREATE SEQUENCE emp_seq;
4. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 NOCACHE;
5. CREATE SEQUENCE emp_seq NOCACHE;
6. CREATE SEQUENCE emp_seq START WITH 1 CACHE;

Question 32

Which two queries execute successfully? (Choose two.)

1. SELECT INTERVAL '1' DAY - INTERVAL '1' MINUTE FROM DUAL
2. SELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;
3. SELECT INTERVAL '1' DAY - SYSDATE FROM DUAL;
4. SELECT INTERVAL '1' DAY + INTERVAL '1' MONTH FROM DUAL;
5. SELECT SYSDATE * INTERVAL '1' DAY FROM DUAL;

Question 33

Examine this command: TRUNCATE TABLE test; Table truncated. Which two are true? (Choose two.)

1. The structure of the TEST table is removed.
2. All the indexes on the TEST table are dropped.
3. All the constraints on the TEST table are dropped.
4. Removed rows can not be recovered using the ROLLBACK command.
5. All the rows in the TEST table are removed.

Question 34

Which two statements are true about views? (Choose two.)

1. The WITH CHECK clause prevents certain rows from being updated or inserted in the underlying table through the view.
2. The WITH CHECK clause prevents certain rows from being displayed when querying the view.
3. Views can be indexed.
4. Views can be updated without the need to re-grant privileges on the view.
5. Tables in the defining query of a view must always exist in order to create the view.

Question 35

Which three statements are true about single-row functions? (Choose three.)

1. They return a single result row per table.
2. They can be nested to any level.
3. They can accept only one argument.
4. The argument can be a column name, variable, literal or an expression.
5. They can be used only in the WHERE clause of a SELECT statement.
6. The data type returned can be different from the data type of the argument.

Question 36

Which three statements are true about multiple row subqueries? (Choose three.)

1. Two or more values are always returned from the subquery.
2. They can contain HAVING clauses.
3. They can contain GROUP BY clauses.
4. They can return multiple columns.
5. They cannot contain a subquery.

Question 37

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

1. An update to a table can result in updates to any or all of the table's indexes.
2. An update to a table can result in no updates to any of the table's indexes.
3. A UNIQUE index can be altered to be non-unique.
4. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped.
5. A table belonging to one user cannot have an index that belongs to a different user.

Question 38

The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8,2). Evaluate this SQL statement: SELECT TO_CHAR(unit_price, '$9,999') FROM product_information; Which two statements are true about the output? (Choose two.)

1. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,0236.
2. A row whose UNIT_PRICE column contains the value 1023.95 will be displayed as $1,024.
3. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,023.
4. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as #######.
5. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.

Question 39

Which three statements about roles are true? (Choose three.)

1. Roles are assigned to users using the ALTER USER statement.
2. Privileges are assigned to a role using the GRANT statement.
3. A role is a named group of related privileges that can only be assigned to a user.
4. A single user can be assigned multiple roles.
5. Privileges are assigned to a role using the ALTER ROLE statement.
6. Roles are assigned to roles using the ALTER ROLE statement.
7. A single role can be assigned to multiple users.

Question 40

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

1. A table can have multiple primary keys.
2. A column definition can specify multiple data types.
3. A table can have multiple foreign keys.
4. A VARCHAR2 column without data has a NULL value.
5. A NUMBER column without data has a zero value.

Question 41

The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type number. Which two queries execute successfully? (Choose two.)

1. SELECT NVL2(cust_credit_limit * .15, 'Not Available') FROM customers;
2. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers
3. SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;
4. SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
5. SELECT TO_CHAR(NVL(cust_credit_iimit * .15, 'Not Available')) FROM customers;

Question 42

Which three statements are true about dropping and unused columns in an Oracle database? (Choose three.)

1. An UNUSED column's space is reclaimed automatically when the row containing that column is next queried.
2. A column that is set to UNUSED still counts towards the limit of 1000 columns per table.
3. A DROP COLUMN command can be rolled back.
4. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
5. An UNUSED column's space is reclaimed automatically when the block containing that column is next queried.
6. Partition key columns cannot be dropped.

Question 43

Which is true about the & and && prefixes with substitution variables? (Choose all that apply.)

1. Both & and && can prefix a substitution variable name in queries and DML statements.
2. An & prefix to an undefined substitution variable, which is referenced twice in the same query, will prompt for a value twice.
3. & can prefix a substitution variable name only in queries.
4. An && prefix to an undefined substitution variable, which is referenced multiple times in multiple queries, will prompt for a value once per query.
5. The && prefix will not prompt for a value even if the substitution variable is not previously defined in the session.

Question 44

Which three statements are true about the Oracle join and ANSI join syntax? (Choose three.)

1. The Oracle join syntax supports creation of a Cartesian product of two tables.
2. The Oracle join syntax only supports right outer joins.
3. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
4. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.
5. The Oracle join syntax supports natural joins.
6. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
7. The SQL:1999 compliant ANSI join syntax supports natural joins.

Question 45

You need to allow user ANDREW to: 1. Modify the TITLE and ADDRESS columns of your CUSTOMERS table. 2. GRANT that permission to other users. Which statement will do this?

1. GRANT UPDATE ON customers.title, customers.address TO andrew;
2. GRANT UPDATE (title, address) ON customers TO andrew;
3. GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION;
4. GRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION;
5. GRANT UPDATE ON customers.title, customers.address TO andrew WITH GRANT OPTION;
6. GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION;

Question 46

Which is true about the ROUND, TRUNC and MOD functions?

1. TRUNC(MOD(25,3),-1) is invalid.
2. ROUND(MOD(25,3),-1) is invalid.
3. ROUND(MOD(25,3),-1) and TRUNC(MOD(25,3),-1) are both valid and give the same result.
4. ROUND(MOD(25,3),-1) and TRUNC(MOD(25,3),-1) are both valid but give different results.

Question 47

Which two are true about granting object privileges on tables, views, and sequences? (Choose two.)

1. INSERT can be granted only on tables and sequences.
2. DELETE can be granted on tables, views, and sequences.
3. SELECT can be granted on tables, views, and sequences.
4. ALTER can be granted only on tables and sequences.
5. REFERENCES can be granted only on tables.

Question 48

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 and, therefore, Sunday is the first day of the week. Which query can be used?

1. SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY('MONDAY') FROM employees;
2. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;
3. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;
4. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') FROM employees;

Question 49

Which two statements are true about conditional INSERT ALL? (Choose two.)

1. Each row returned by the subquery can be inserted into only a single target table.
2. A single WHEN condition can be used for multiple INTO clauses.
3. Each WHEN condition is tested for each row returned by the subquery.
4. It cannot have an ELSE clause.
5. The total number of rows inserted is always equal to the number of rows returned by the subquery.

Question 50

Which two are true about queries using set operators such as UNION? (Choose two.)

1. In a query containing multiple set operators, INTERSECT always takes precedence over UNION and UNION ALL.
2. An expression in the first SELECT list must have a column alias for the expression.
3. All set operators are valid on columns of all data types.
4. CHAR columns of different lengths used with a set operator return a VARCHAR2 whose length equals the longest char value.
5. Queries using set operators do not perform implicit conversion across data type groups (e.g. character, numeric).

Question 51

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

1. It must be an equijoin.
2. The ON clause must be used.
3. It must be an inner join.
4. It can be an outer join.
5. The ON clause can be used.
6. The query must use two different aliases for the table.

Question 52

No user-defined locks are used in your database. Which three are true about Transaction Control Language (TCL)? (Choose three.)

1. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes, releases its locks, and erases all its savepoints.
2. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not release its locks.
3. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints.
4. ROLLBACK TO SAVEPOINT undoes the transaction's changes made since the named savepoint and then ends the transaction.
5. COMMIT ends the transaction and makes all its changes permanent.
6. COMMIT erases all the transaction's savepoints and releases its locks.

Question 53

Which two are true about external tables that use the ORACLE_DATAPUMP access driver? (Choose two.)

1. When creating an external table, data can be selected only from a table whose rows are stored in database blocks.
2. Creating an external table creates a directory object.
3. When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks.
4. Creating an external table creates a dump file that can be used by an external table in the same or a different database.
5. Creating an external table creates a dump file that can be used only by an external table in the same database.

Question 54

Which statement is true about TRUNCATE and DELETE?

1. For tables with multiple indexes and triggers, DELETE is faster than TRUNCATE.
2. You can never TRUNCATE a table if foreign key constraints would be violated.
3. You can DELETE rows from a table with referential integrity constraints.
4. For large tables, DELETE is faster than TRUNCATE.

Question 55

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

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

Question 56

Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)? (Choose two.)

1. The name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.
2. None of the set operators can be used when selecting CLOB columns.
3. There must be an equal number of columns in each SELECT list.
4. Each SELECT statement in the query can have an ORDER BY clause.
5. The FOR UPDATE clause cannot be specified.

Question 57

Which two are true about unused columns? (Choose two.)

1. Setting an indexed column to unused results in an error.
2. You can query the data dictionary to see the names of unused columns.
3. You can specify multiple column names in an ALTER TABLE...SET UNUSED statement.
4. If you set all the columns of a table to unused, the table is automatically dropped.
5. CASCADE CONSTRAINTS must be specified when setting a column to unused if that column is referenced in a constraint on another column.

Question 58

Which two statements are true? (Choose two.)

1. CASE is a function and DECODE is not.
2. Neither CASE nor DECODE is a function.
3. All conditions evaluated using CASE can also be evaluated using DECODE.
4. All conditions evaluated using DECODE can also be evaluated using CASE.
5. DECODE is a function and CASE is not.
6. Both CASE and DECODE are functions.

Question 59

Examine this query: SELECT TRUNC(ROUND(156.00,-2),-1) FROM DUAL; What is the result?

1. 150
2. 200
3. 160
4. 100
5. 16

Question 60

The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE. NLS_DATE_FORMAT is set to DD-MON-RR. Which two are true about data type conversions involving these columns in query expressions? (Choose two.)

1. invoice_date = '15-march-2019' : uses implicit conversion
2. qty_sold BETWEEN '101' AND '110' : uses implicit conversion
3. invoice_date > '01-02-2019' : uses implicit conversion
4. qty_sold = '0554982' : requires explicit conversion
5. CONCAT (qty_sold, invoice_date) : requires explicit conversion

Question 61

Which two actions can you perform with object privileges? (Choose two.)

1. Create roles.
2. Create FOREIGN KEY constraints that reference tables in other schemas.
3. Delete rows from tables in any schema except SYS.
4. Set default and temporary tablespaces for a user.
5. Execute a procedure or function in another schema.

Question 62

Which three are key components of an Entity Relationship Model? (Choose three.)

1. an activity
2. a table
3. a relationship
4. an attribute
5. a unique identifier
6. an entity

Question 63

Which three statements are true about inner and outer joins? (Choose three.)

1. A full outer join returns matched and unmatched rows.
2. Outer joins can be used when there are multiple join conditions on two tables.
3. A full outer join must use Oracle syntax.
4. Outer joins can only be used between two tables per query.
5. A left or right outer join returns only unmatched rows.
6. An inner join returns matched rows.

Question 64

Which three queries execute successfully? (Choose three.)

1. SELECT 1 - SYSDATE - DATE '2019-01-01' FROM DUAL;
2. SELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL;
3. SELECT SYSDATE / DATE '2019-01-01' - 1 FROM DUAL;
4. SELECT SYSDATE - 1 - DATE '2019-01-01' FROM DUAL;
5. SELECT (SYSDATE - DATE '2019-01-01') / 1 FROM DUAL;
6. SELECT 1 / SYSDATE - DATE '2019-01-01' FROM DUAL;

Question 65

Examine the description of the BOOKS table: The table has 100 rows. Examine this sequence of statements issued in a new session: INSERT INTO books VALUES ('ADV112', 'Adventures of Tom Sawyer', NULL, NULL); SAVEPOINT a; DELETE FROM books; ROLLBACK TO SAVEPOINT a; ROLLBACK; Which two statements are true? (Choose two.)

1. The second ROLLBACK command replays the delete.
2. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.
3. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.
4. The second ROLLBACK command undoes the insert.
5. The second ROLLBACK command does nothing.

Question 66

Which three are true about scalar subquery expressions? (Choose three.)

1. They can be nested.
2. They cannot be used in the VALUES clause of an INSERT statement.
3. A scalar subquery expression that returns zero rows evaluates to zero.
4. They can be used as default values for columns in a CREATE TABLE statement.
5. A scalar subquery expression that returns zero rows evaluates to NULL.
6. They cannot be used in GROUP BY clauses.

Question 67

You issued this command: DROP TABLE hr.employees; Which three statements are true? (Choose three.)

1. Sequences used to populate columns in the HR.EMPLOYEES table are dropped.
2. Synonyms for HR.EMPLOYEES are dropped.
3. Views referencing HR.EMPLOYEES are dropped.
4. All constraints defined on HR.EMPLOYEES are dropped.
5. The HR.EMPLOYEES table may be moved to the recycle bin.
6. All indexes defined on HR.EMPLOYEES are dropped

Question 68

Which two statements are true about the COUNT function? (Choose two.)

1. COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.
2. It can only be used for NUMBER data types.
3. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column.
4. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column
5. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.