1z0-071: Oracle Database 12c SQL → 1z0-071: Oracle Database 12c SQL Topic 2

1z0-071: Oracle Database 12c SQL Topic 2

Question #: 51
Topic #: 1
Examine the description of the EMPLOYEES table:

Which two queries will result in an error? (Choose two.)
A.

Selected Answer: C E

Question #: 55
Topic #: 1
BOOK_SEQ is an existing sequence in your schema.
Which two CREATE TABLE commands are valid? (Choose two.)
A.

C. C.

Selected Answer: A D

Question #: 52
Topic #: 1
You create a table named 123.
Which statement runs successfully?

A. SELECT * FROM TABLE(123);
B. SELECT * FROM “123”;
C. SELECT * FROM \’123\’;
D. SELECT * FROM ‘123’;

Selected Answer: B

Question #: 53
Topic #: 1
Which two statements are true regarding indexes? (Choose two.)

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

Selected Answer: AB

Question #: 54
Topic #: 1
Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)? (Choose two.)

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

Selected Answer: BE

Question #: 56
Topic #: 1
Which three statements are true about multiple row subqueries? (Choose three.)

A. Two or more values are always returned from the subquery.
B. They can contain HAVING clauses.
C. They can contain GROUP BY clauses.
D. They can return multiple columns.
E. They cannot contain a subquery.

Selected Answer: BCD

Question #: 57
Topic #: 1
Which three actions can you perform on an existing table containing data? (Choose three.)

A. Increase the width of a numeric column.
B. Add a new column as the table’s first column.
C. Define a default value that is automatically inserted into a column containing nulls.
D. Change a DATE column containing data to a NUMBER data type.
E. Change the default value of a column.
F. Add a new NOT NULL column with a DEFAULT value.

Selected Answer: AEF

Question #: 58
Topic #: 1
Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)? (Choose two.)

A. Rows from unrelated tables cannot be joined.
B. Relating data from a table with data from the same table is implemented with a self join.
C. Implementing a relationship between two tables might require joining additional tables.
D. Every relationship between the two tables must be implemented in a join condition.
E. An inner join relates rows within the same table.

Selected Answer: B

Question #: 59
Topic #: 1
Which three statements about roles are true? (Choose three.)

A. Roles are assigned to users using the ALTER USER statement.
B. Privileges are assigned to a role using the GRANT statement.
C. A role is a named group of related privileges that can only be assigned to a user.
D. A single user can be assigned multiple roles.
E. Privileges are assigned to a role using the ALTER ROLE statement.
F. Roles are assigned to roles using the ALTER ROLE statement.
G. A single role can be assigned to multiple users.

Selected Answer: BDG

Question #: 60
Topic #: 1
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.)

A. invoice_date = ’15-march-2019′ : uses implicit conversion
B. qty_sold BETWEEN ‘101’ AND ‘110’ : uses implicit conversion
C. invoice_date > ’01-02-2019′ : uses implicit conversion
D. qty_sold = ‘0554982’ : requires explicit conversion
E. CONCAT (qty_sold, invoice_date) : requires explicit conversion

Selected Answer: BE

Question #: 61
Topic #: 1
Which three statements are true about inner and outer joins? (Choose three.)

A. A full outer join returns matched and unmatched rows.
B. Outer joins can be used when there are multiple join conditions on two tables.
C. A full outer join must use Oracle syntax.
D. Outer joins can only be used between two tables per query.
E. A left or right outer join returns only unmatched rows.
F. An inner join returns matched rows.

Selected Answer: ABF

Question #: 62
Topic #: 1
Which statement will execute successfully?
A.

Selected Answer: A

Question #: 63
Topic #: 1
Examine the description of the EMPLOYEES table:

Which two queries return all rows for employees whose salary is greater than the average salary in their department? (Choose two.)
A.

Selected Answer: B D

Question #: 64
Topic #: 1
Which three statements are true about the Oracle join and ANSI join syntax? (Choose three.)

A. The Oracle join syntax supports creation of a Cartesian product of two tables.
B. The Oracle join syntax only supports right outer joins.
C. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
D. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.
E. The Oracle join syntax supports natural joins.
F. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
G. The SQL:1999 compliant ANSI join syntax supports natural joins.

Selected Answer: ACG

Question #: 65
Topic #: 1
Which two are true about the NVL, NVL2, and COALESCE functions? (Choose two.)

A. NVL must have expressions of the same data type.
B. NVL can have any number of expressions in the list.
C. NVL2 can have any number of expressions in the list.
D. COALESCE stops evaluating the list of expressions when it finds the first non-null value.
E. The first expression in NVL2 is never returned.
F. COALESCE stops evaluating the list of expressions when it finds the first null value.

Selected Answer: DE

Question #: 66
Topic #: 1
Examine this statement:

What is returned upon execution?

A. an error
B. 2 rows
C. 0 rows
D. 1 row

Selected Answer: D

Question #: 67
Topic #: 1
Examine this statement:

What is returned upon execution?

A. an error
B. 2 rows
C. 0 rows
D. 1 row

Selected Answer: D

Question #: 68
Topic #: 1
Which two statements execute successfully? (Choose two.)
A.

Selected Answer: A B

Question #: 69
Topic #: 1
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.)

A. when a CREATE TABLE AS SELECT statement is issued in the same session but fails with a syntax error
B. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT
C. when the session logs out successfully
D. when a CREATE INDEX statement is executed successfully in the same session
E. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT
F. when a COMMIT statement is issued by the same user from another session in the same database instance

Selected Answer: BCD

Question #: 70
Topic #: 1
Which two are true about using constraints? (Choose two.)

A. NOT NULL can be specified at the column and at the table level.
B. A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.
C. A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parent table must have the same names.
D. PRIMARY KEY and FOREIGN KEY constraints can be specified at the column and at the table level.
E. A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.
F. A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.

Selected Answer: DF

Question #: 71
Topic #: 1
Examine this statement:

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

Selected Answer: AF

Question #: 72
Topic #: 1
Examine this partial query:

Examine this output:

Which GROUP BY clause must be added so the query returns the results shown?

A. GROUP BY ch.channel_type, ROLLUP(t.month, co.country_code);
B. GROUP BY ch.channel_type, t.month, ROLLUP(co.country_code);
C. GROUP BY CUBE(ch.channel_type, t.month, co.country_code);
D. GROUP BY ch.channel_type, t.month, co.country_code;

Selected Answer: A

Question #: 73
Topic #: 1
Examine the description of the EMPLOYEES table:

Which statement will execute successfully, returning distinct employees with non-null first names?

A. SELECT first_name, DISTINCT last_name FROM employees WHERE first_name <> NULL;
B. SELECT first_name, DISTINCT last_name FROM employees WHERE first_name IS NOT NULL;
C. SELECT DISTINCT * FROM employees WHERE first_name IS NOT NULL;
D. SELECT DISTINCT * FROM employees WHERE first_name <> NULL;

Selected Answer: C

Question #: 74
Topic #: 1
Examine the description of the BRICKS table:

Examine the description of the BRICKS_STAGE table:

Which two queries execute successfully? (Choose two.)
A.

Selected Answer: A D

Question #: 75
Topic #: 1
Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY.
Only the EMPLOYEE_ID column is indexed.
Rows exist for employees 100 and 200.
Examine this statement:

Which two statements are true? (Choose two.)

A. Employees 100 and 200 will have the same SALARY as before the update command.
B. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200.
C. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100.
D. Employees 100 and 200 will have the same JOB_ID as before the update command.
E. Employee 100 will have SALARY set to the same value as the SALARY of employee 200.
F. Employee 200 will have SALARY set to the same value as the SALARY of employee 100.

Selected Answer: BE

Question #: 76
Topic #: 1
Examine these two queries and their output:
SELECT deptno, dname FROM dept;

SELECT emame, job, deptno FROM emp ORDER BY deptno;

Now examine this query:

How many rows will be displayed?

A. 64
B. 6
C. 3
D. 12

Selected Answer: B

Question #: 77
Topic #: 1
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?

A. SYSDATE
B. CURRENT_TIMESTAMP
C. LOCALTIMESTAMP
D. CURRENT_DATE

Selected Answer: B

Question #: 78
Topic #: 1
You have been tasked to create a table for a banking application.
One of the columns must meet three requirements:
1) Be stored in a format supporting date arithmetic without using conversion functions
2) Store a loan period of up to 10 years
3) Be used for calculating interest for the number of days the loan remains unpaid
Which data type should you use?

A. INTERVAL YEAR TO MONTH
B. TIMESTAMP WITH TIMEZONE
C. INTERVAL DAY TO SECOND
D. TIMESTAMP WITH LOCAL TIMEZONE
E. TIMESTAMP

Selected Answer: A

Question #: 79
Topic #: 1
Which two are true about a SQL statement using SET operators such as UNION? (Choose two.)

A. The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query.
B. The names and number of columns must be identical for all select statements in the query.
C. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query.
D. The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.
E. The number, but not names, of columns must be identical for all select statements in the query.

Selected Answer: AE

Question #: 80
Topic #: 1
Which two are true about queries using set operators such as UNION? (Choose two.)

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

Selected Answer: DE

Question #: 81
Topic #: 1
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.)

A. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
B. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the projects entity.
C. 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.
D. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into one-to-many relationships.
E. The ER must have a one-to-many relationship between the STUDENTS and PROJECTS entities.

Selected Answer: CD

Question #: 82
Topic #: 1
Which three are key components of an Entity Relationship Model? (Choose three.)

A. an activity
B. a table
C. a relationship
D. an attribute
E. a unique identifier
F. an entity

Selected Answer: CDF

Question #: 83
Topic #: 1
Examine the data in the ORDERS table:

Examine the data in the INVOICES table:

Examine this query:

A. 2
B. 1
C. 3
D. 5 01-MAR-2019
E. 3 01-JAN-2015
F. 4 01-FEB-2015

Selected Answer: BF

Question #: 84
Topic #: 1
Which two will execute successfully? (Choose two.)

A. SELECT COALESCE(0, SYSDATE) FROM DUAL;
B. SELECT NVL(‘DATE’, SYSDATZ) FROM DUAL;
C. SELECT COALESCE(‘DATE’, SYSDATE) FROM DUAL;
D. SELECT NVL(‘DATE’, 200) FROM (SELECT NULL AS “DATE” FROM DUAL);
E. SELECT COALESCE(‘DATE’, SYSDATE) FRCM (SELECT NULL AS “DATE” FROM DUAL);

Selected Answer: BD

Question #: 85
Topic #: 1
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.

Selected Answer: DEF

Question #: 86
Topic #: 1
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.

Selected Answer: B

Question #: 87
Topic #: 1
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.

Selected Answer: CD

Question #: 88
Topic #: 1
Which two are true about global temporary tables? (Choose two.)

A. Indexes can be created on them.
B. Backup and recovery operations are available for these tables.
C. Their data is always stored in the default temporary tablespace of the user who created them.
D. If the ON COMMIT clause is transaction-specific, all rows in the table are deleted after each COMMIT OR ROLLBACK.
E. They can be created only by a user with the DBA role, but can be accessed by all users who can create a session.
F. If the ON COMMIT clause is session-specific, the table is dropped when the session is terminated.

Selected Answer: AD

Question #: 89
Topic #: 1
Which three are true about privileges? (Choose three.)

A. A combination of object and system privileges can be granted to a role.
B. All types of schema objects have associated object privileges.
C. Schema owners can grant object privileges on objects in their schema to any other user or role.
D. Only users with the DBA role can create roles.
E. Only users with the GRANT ANY PRIVILEGE privilege can grant and revoke system privileges from other users.
F. Object privileges granted on a table automatically apply to all synonyms for that table.

Selected Answer: ACF

Question #: 90
Topic #: 1
Examine the description of the EMPLOYEES table:

Which two statements will insert a row into the EMPLOYEES table? (Choose two.)

A. INSERT INTO employees VALUES (101, ‘John’, ‘Smith’, 12000, SYSDATE);
B. INSERT INTO employees VALUES (101, ‘John’, ‘Smith’, 10, 12000, SYSDATE);
C. INSERT INTO employees (employee_id, salary, first_name, hiredate, last_name) VALUES (101, 12100, ‘John’, SYSDATE, ‘Smith’);
D. INSERT INTO employees (employee_id, first_name, last_name, salary, hiresate)
VALUES ( (SELECT 101, ‘John’, ‘Smith’. 12000, SYSDATE FROM dual) );
E. INSERT INTO employees SELECT 101, ‘John’, ‘Smith’, 12000, (SELECT SYSDATE FROM dual), 10 FROM dual;
F. INSERT INTO employees VALUES (101, ‘John’, ‘ ‘, 12000, SYSDATE, 10);

Selected Answer: EF

Question #: 91
Topic #: 1
Examine this command:

TRUNCATE TABLE test;

Table truncated.

Which two are true? (Choose two.)

A. The structure of the TEST table is removed.
B. All the indexes on the TEST table are dropped.
C. All the constraints on the TEST table are dropped.
D. Removed rows can not be recovered using the ROLLBACK command.
E. All the rows in the TEST table are removed.

Selected Answer: DE

Question #: 92
Topic #: 1
You issued this command:

DROP TABLE hr.employees;

Which three statements are true? (Choose three.)

A. Sequences used to populate columns in the HR.EMPLOYEES table are dropped.
B. Synonyms for HR.EMPLOYEES are dropped.
C. Views referencing HR.EMPLOYEES are dropped.
D. All constraints defined on HR.EMPLOYEES are dropped.
E. The HR.EMPLOYEES table may be moved to the recycle bin.
F. All indexes defined on HR.EMPLOYEES are dropped

Selected Answer: DEF

Question #: 93
Topic #: 1
Examine this statement:

Identify three ORDER BY clauses, any one of which will complete the query successfully. (Choose three.)

A. ORDER BY CUST_NO
B. ORDER BY 2, cust_id
C. ORDER BY 2, 1
D. ORDER BY “Last Name”
E. ORDER BY “CUST_NO”

Selected Answer: BCD

Question #: 94
Topic #: 1
Which two statements are true about views? (Choose two.)

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

Selected Answer: AD

Question #: 95
Topic #: 1
Examine the description of the EMPLOYEES table:

Which two statements will run successfully? (Choose two.)

A. SELECT ‘The first_name is ‘‘ || first_name || ‘‘ FROM employees;
B. SELECT ‘The first_name is ‘‘‘ || first_name || ‘‘‘‘ FROM employees;
C. SELECT ‘The first_name is ‘‘‘ || first_name || ‘‘‘ FROM employees;
D. SELECT ‘The first_name is ‘ || first_name || ‘‘ FROM employees;
E. SELECT ‘The first_name is \‘‘ || first_name || ‘\‘‘ FROM employees;

Selected Answer: BD

Question #: 96
Topic #: 1
Which two are true about unused columns? (Choose two.)

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

Selected Answer: CE

Question #: 97
Topic #: 1
Examine the data in the CUST_NAME column of the CUSTOMERS table:

You want to display the CUST_NAME values where the last name starts with Mc or MC.

Which two WHERE clauses give the required result? (Choose two.)

A. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ‘ ‘) + 1)) LIKE ‘Mc%’
B. WHERE SUBSTR(cust_name, INSTR(cust_name, ‘ ‘) + 1) LIKE ‘Mc%’
C. WHERE SUBSTR(cust_name, INSTR(cust_name, ‘ ‘) + 1 ) LIKE ‘Mc%’ OR ‘MC%’
D. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ‘ ‘) + 1)) LIKE UPPER(‘MC%’)
E. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ‘ ’) + 1)) IN (‘MC%’, ‘Mc%)

Selected Answer: AD

Question #: 98
Topic #: 1
Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?

A. the first VARCHAR2 column in the first SELECT of the compound query
B. the first column in the first SELECT of the compound query
C. the first NUMBER column in the first SELECT of the compound query
D. the first NUMBER or VARCHAR2 column in the last SELECT of the compound query
E. the first column in the last SELECT of the compound query

Selected Answer: B

Question #: 99
Topic #: 1
Which two statements are true about the ORDER BY clause? (Choose two.)

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

Selected Answer: AD

Question #: 100
Topic #: 1
Examine the BRICKS table:

You write this query:

How many rows will the query return?

A. 4
B. 6
C. 16
D. 0
E. 1
F. 10

Selected Answer: B

Cart

Practice Exam

Protect Your System: Understanding CVE-2024-3400 Zero-Day Vulnerability

AWS Certified Cloud Practitioner CLF-C02 Exam Part 6

Cart