Oracle 12c: SQL - Chapters 7 - 11 (Exam)

Which of the following SQL statements will assign the DBA role as the default role for user RTHOMAS?

ALTER USER rthomas DEFAULT ROLE dba;​

Which of the following SQL commands will require the user RTHOMAS to change the account password the next time the database is accessed?

ALTER USER rthomas PASSWORD EXPIRE ;

A role can be removed from the database with the *DELETE ROLE* command.

False

A role can only be granted to other roles.

False

A table alias is assigned to a table in the WHERE clause.

False

A(n) *clause* identifies what must exist or a requirement that must be met for a record to be included in the results of a query.

False

A(n) *group* is a collection of privileges that can be granted to users.

False

An outer join only lists rows that contain a match in both tables.

False

An outer join operator can be included in a FROM clause to list all rows from one table that do not have a corresponding row in the other table.

False

By default, the COUNT function uses the *DISTINCT* keyword when counting the values contained in a column.

False

Columns used to group data in the GROUP BY clause must also be listed in the SELECT clause.

False

In a WHERE clause, logical operators are evaluated before comparison operators.

False

NOT, ALL, and OR are all considered logical operators.

False

NOT, AND, and OR are *comparison* operators.

False

Once a privilege has been granted to a role, it cannot be revoked without re-creating the role.

False

The *ON* clause can be used only if the tables being joined have a common column with the same name.

False

The *ORDER BY* clause is used to group data.

False

The *ROUND* function can be used to truncate numeric data.

False

The *WITH ADMIN OPTION* is required to enable the user to grant the specified object privileges to other users.

False

The ALTER USER command can be used to change the name assigned to a user account.

False

The GROUP BY clause can only be used when a SELECT statement includes a group function.

False

The INSTR function is used to return a portion of a character string.

False

The NATURAL JOIN keywords can be used to create non-equality joins.

False

The TRUNC function can be used to add insignificant zeros to the right of a decimal point for numeric data.

False

The UPDATE ANY TABLE privilege is a system privilege.

False

The only group function that includes NULL values is the *NVL* function.

False

The percent sign (%) is used in a search pattern to indicate "exactly one character in this position".

False

To indicate that the numeric value of the day of the year for a date is being specified, include DAY in format argument of the TO_DATE function.

False

Use MON in the format argument to spell out the specified month.

False

When using a search pattern, a(n) *?* symbol is used to indicate exactly one character in that position.

False

Which of the following commands will revoke the SELECT privilege for the ORDERS table previously granted to user RTHOMAS?

REVOKE SELECT ON orders FROM rthomas;

Based upon the contents of the CUSTOMERS table, which of the following will display the shipping location as: City, State Zip Note: The ST column name is truncated, this represents the STATE column.

SELECT INITCAP(CONCAT(city, CONCAT(', ', CONCAT(state, CONCAT(' ', zip))))) FROM customers;

Which of the following SQL statements will return the names of all customers who placed an order on April 12, 2003?

SELECT lastname, firstname FROM customers NATURAL JOIN orders WHERE orderdate = '12-APR-03';

Which of the following SQL statements will list the name of each customer stored in the CUSTOMERS table, and, if the customer has placed an order that is contained in the ORDERS table, the order# of any order each customer has placed?

SELECT lastname, firstname, order# FROM customers, orders WHERE orders.customer# (+) = customers.customer#;

A(n) *non-equality* join is used when there are no equivalent rows of data in the tables that are being joined.

True

A(n) *percent* sign is used in a search pattern to indicate zero, one, or more characters in a position.

True

Authentication is the process of ensuring that the individuals trying to access the system are who they claim to be.

True

By default, the *JOIN* keyword creates an inner join.

True

Group functions return one result per group of rows processed.

True

If you are joining four tables in a SELECT statement, *three* joining conditions will be required.

True

Including the keywords NULLS FIRST in the ORDER BY clause will result in NULL values appearing first in the results, regardless of the specified sort sequence.

True

Set operators are used to combine the results of multiple queries.

True

The *AVG* function only includes non-NULL values in its calculations.

True

The *MIN* function can be used with the DISTINCT or ALL keywords.

True

The *NVL2* function allows different options to be selected, based upon whether a NULL value exists.

True

The *REVOKE* command can be used to revoke a role from a user or another role.

True

The *RTRIM* function can be used to remove a specific set of characters from the right side of a set of data values.

True

The HAVING clause is used to restrict the groups returned by a query.

True

The IN logical operator is similar to using *OR*.

True

The INSERT, UPDATE, and DELETE object privileges can be applied to tables and views.

True

The LOWER function can be used to display upper-case characters in lower-case.

True

The NVL function can be used to include records containing null values in calculations.

True

The TO_CHAR function can be used to add a dollar sign ($) to a numeric value.

True

The VARIANCE function is used to determine the variance in a group of numeric data.

True

The first column listed in an ORDER BY clause is considered the primary sort.

True

The number of joining conditions required to join tables is always one less than the number of tables being joined.

True

Which of the following search patterns could be used to find the word HELLO in Oracle12c?

​%H%

Which of the following functions can be used to determine the date one month after a specified date?

​ADD_MONTHS

If the DISTINCT keyword is not included in the STDDEV function, the ____ keyword will be assumed.

​ALL

The ____ command will assign a default role to a user.

​ALTER USER username DEFAULT ROLE rolename;

To instruct Oracle12cto sort data in ascending order, enter ____ after the column name in the ORDER BY clause.

​ASC

Which of the following is a correct statement?

​An asterisk can be used as the argument for the COUNT function to include NULL values in the results.

Which of the following functions is similar to using the (| |) symbol to combine the contents of two character strings?

​CONCAT

Which of the following SQL statements will create a new role named PRCLERK?

​CREATE ROLE prclerk;

Which of the following search conditions can be used to identify records that have data stored in a column named ColB?

​ColB IS NOT NULL

Which of the following format elements will display the value of Friday in a specified date as a 6?

​D

To instruct Oracle12c to sort data in descending order, enter ____ after the column name in the WHERE clause.

​DESC

If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed last.

​HAVING

If the output is to be restricted based upon a group function, a(n) ____ clause must be included in the SQL statement.

​HAVING

The ____ clause is used to restrict the groups returned by a query.

​HAVING

Which of the following keywords can be used to join two tables that do not contain a commonly named and defined column?

​JOIN...ON

Which of the following represents the number of days that have passed since January 1, 4712 B.C.?

​Julian date

Which of the following functions can be used to convert a character string to lower-case letters?

​LOWER

Which of the following lines of the SQL statement contains an error? 1 SELECT title, MAX(retail) 2 FROM books 3 WHERE retail > 30 4 AND pubid = 1 5 GROUP BY retail

​Line 5

What clause offers many options to identify measures and patterns to analyze in a query?

​MATCH_RECOGNIZE

The ____ function returns the smallest value in a specified column.

​MIN

Which of the following can only be used to link tables that have a common column?

​NATURAL JOIN

Which of the following privileges will allow a user to reference a table when creating a FOREIGN KEY constraint?

​REFERENCES

Which of the following can be used to replace a specific set of characters with another set of characters?

​REPLACE

Based upon the contents of the BOOKS table in the accompanying figure, which of the following SQL statements will retrieve all books published by the publisher assigned Pubid 1?

​SELECT * FROM books WHERE pubid = 1;

Based upon the contents of the ORDERS table in the accompanying figure, which of the following queries will display all orders that were not shipped for at least three days after the order was received?

​SELECT * FROM orders WHERE shipdate-orderdate >= 3;

Which of the following will display the name of each customer in lower-case characters based upon the contents of the CUSTOMERS table? Note: The ST column name is truncated, this represents the STATE column.

​SELECT LOWER(firstname) || ' ' || LOWER(lastname) FROM customers;

Which of the following is a valid SQL statement based upon the contents of the CUSTOMERS table? Note: The ST column name is truncated, this represents the STATE column.

​SELECT customer#, NVL2(referred, 'Referred', 'Not Referred') FROM customers;

Which of the following SQL statements would most likely be used to generate the partial output shown above?

​SELECT name FROM SYSTEM_PRIVILEGE_MAP;

Based upon the contents of the ORDERS table in the accompanying figure, which of the following SQL statements will display how long it took to ship order # 1007 (based upon when the order was originally placed)?

​SELECT order#, shipdate-orderdate FROM orders WHERE order# = 1007;

Which of the following SQL statements will display books that have not been ordered recently?

​SELECT title FROM books MINUS SELECT title FROM books NATURAL JOIN orderitems;

Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will list the title and retail price of each book stored in the BOOKS table, sorted in order of the book titles?

​SELECT title, retail FROM books ORDER BY title;

Which of the following format argument elements will display the number of seconds past midnight?

​SSSS

All the Oracle12c system privileges can be viewed through the data dictionary view ____.

​SYSTEM_PRIVILEGE_MAP

Which of the following statements about privileges is incorrect?

​There are five types of privileges, including DATATYPE, ROLETYPE, and USERNAME privileges.

Which of the following set operators will display only the unique results of the combined SQL statements?

​UNION

Which of the following can be used with date columns?

​all of the above

Which of the following is a valid comparison operator?

​all of the above

Which of the following queries will display the first and last name of the individual who referred another customer, along with the customer# of the referred customer?

​both a and b

Based upon the contents of the ORDERS table in the accompanying figure, which of the following queries will display all orders shipped between April 4, 2003 and April 5, 2003?

​both b and c

Which of the following SQL statements will display the gift that should be sent to any customer who orders the book titled THE WOK WAY TO COOK?

​none of the above

Which of the following SQL statements will remove the SELECT ANY TABLE privilege from the database so it can never be granted to another user?

​none of the above

The SUM function can only be used with ____ data.

​numeric

Which of the following types of joins refers to joining a table to itself?

​self-join

A(n) ____ is used to combine the results of two queries.

​set operator