Oracle 1Z0-071 Quiz 48 Topic 9 Questions 236-240

Question: 1

Evaluate the following query:

SQL> SELECT TRUNC (ROUND (156.00, -1),-1)

FROM DUAL;

What would be the outcome?

A150

B200

C160

D16

E100

Show Answer

Question: 2

Examine the data in the CUST_NAME column of the CUSTOMERS table.

CUST_NAME

-------------------

Renske Ladwig

Jason Mallin

Samuel McCain

Allan MCEwen

Irene Mikkilineni

Julia Nayer

You need to display customers' second names where the second name starts with "Mc" or "MC".

Which query gives the required output?

ASELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');

BSELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';

CSELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';

DSELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');

Show Answer

Question: 3

View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.

q3_1Z0-071

The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table.

Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

Evaluate the following CREATE TABLE command:

CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)

AS

SELECT prod_id, cust_id, time_id

FROM sales;

Which statement is true regarding the above command?

AThe NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.

BThe NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.

CThe NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

DThe NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.

Show Answer

Question: 4

Which two statements are true regarding the USING and ON clauses in table joins?

ABoth USING and ON clauses can be used for equijoins and nonequijoins.

BA maximum of one pair of columns can be joined between two tables using the ON clause.

CThe ON clause can be used to join tables on columns that have different names but compatible data types.

DThe WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.

Show Answer

Question: 5

Which three statements are true regarding group functions? (Choose three.)

AThey can be used on columns or expressions.

BThey can be passed as an argument to another group function.

CThey can be used with a SQL statement that has a GROUP BY clause.

DThey can be used only on one column in the SELECT clause.

EThey can be used together with the single-row functions in the SELECT clause.

Show Answer

Oracle 1Z0-071 Quiz:48 Topic:9 Questions:236-240