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

• A. They can be used on columns or expressions.
• B. They can be passed as an argument to another group function.
• C. They can be used only with a SQL statement that has the GROUP BY clause.
• D. They can be used on only one column in the SELECT clause of a SQL statement.
• E. They can be used along with the single-row function in the SELECT clause of a SQL statement.

Answer: ABE

Explanation: References:
https://www.safaribooksonline.com/library/view/mastering-oracle-sql/0596006322/ch04.html

NEW QUESTION 2
Which two statements are true regarding constraints?

• A. A foreign key column cannot contain null values.
• B. A column with the UNIQUE constraint can contain null values.
• C. A constraint is enforced only for INSERT operation on the table.
• D. A constraint can be disabled even if the constraint column contains data.
• E. All constraints can be defined at the column level and at the table level.

Answer: BD

NEW QUESTION 3
View the exhibit and examine the ORDERS table. ORDERS
Name Null? Type
ORDER ID NOT NULL NUMBER(4) ORDATE DATE DATE CUSTOMER ID NUMBER(3) ORDER TOTAL NUMBER(7,2)
The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?

• A. ALTER TABLE ordersMODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
• B. ALTER TABLE ordersADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
• C. ALTER TABLE ordersMODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);
• D. ALTER TABLE ordersADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

Answer: C

NEW QUESTION 4
Which two statements are true regarding constraints? (Choose two.)

• A. All constraints can be defined at the column level and at the table level.
• B. A constraint can be disabled even if the constraint column contains data.
• C. A column with the UNIQUE constraint can contain NULLS.
• D. A foreign key column cannot contain NULLS.
• E. A constraint is enforced only for INSERT operations.

Answer: BC

NEW QUESTION 5
Examine the structure of the BOOKS_ TRANSACTIONS table:

Examine the SQL statement:

Which statement is true about the outcome?

• A. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
• B. It displays details for members who have borrowed before today's date with either RM asTRANSACTION_TYPE or MEMBER_ID as A101 and A102.
• C. It displays details for only members A101and A102 who have borrowed before today with RM as TRANSACTION_TYPE.
• D. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.

Answer: A

NEW QUESTION 6
You must create a table for a banking application. (Choose the best answer.) One of the columns in the table has these requirements:
1: A column to store the duration of a short team loan
2: The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.
3: The maximum loan period is 30 days.
4: Interest must be calculated based on the number of days for which the loan remains unpaid. Which data type would you use?

• A. Date
• B. Number
• C. Timestamp
• D. Interval day to second
• E. Interval year to month

Answer: D

NEW QUESTION 7
Which three statements are true about multiple-row subqueries?

• A. They can contain a subquery within a subquery.
• B. They can return multiple columns as well as rows.
• C. They cannot contain a subquery within a subquery.
• D. They can return only one column but multiple rows.
• E. They can contain group functions and GROUP BY and HAVING clauses.
• F. They can contain group functions and the GROUP BY clause, but not the HAVING clause.

Answer: ABE

NEW QUESTION 8
The BOOKS_TRANSACTIONS table exists in your schema in this database.
You execute this SQL statement when connected to your schema in your database instance. SQL> SELECT * FROM books_transactions ORDER BY 3;
What is the result?

• A. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
• B. All table rows are displayed sorted in ascending order of the values in the third column.
• C. The first three rows in the table are displayed in the order that they are stored.
• D. Only the three rows with the lowest values in the key column are displayed in the order that they are stored.

Answer: B

NEW QUESTION 9
Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

• A. The outer query stops evaluating the result set of the inner query when the first value is found.
• B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
• C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
• D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Answer: AC

Explanation: References:
http://www.techonthenet.com/oracle/exists.php

NEW QUESTION 10
Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)

• A. You can use column alias in the GROUP BY clause.
• B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
• C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
• D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
• E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY cause.

Answer: DE

NEW QUESTION 11
A subquery is called a single-row subquery when .

• A. There is only one subquery in the outer query and the inner query returns one or more values
• B. The inner query returns a single value to the outer query.
• C. The inner query uses an aggregating function and returns one or more values.
• D. The inner query returns one or more values and the outer query returns a single value.

Answer: B

NEW QUESTION 12
You issue this command which succeeds: SQL> DROP TABLE products;
Which three statements are true?

• A. All existing views and synonyms that refer to the table are invalidated but retained.
• B. Any uncommitted transaction in the session is committed.
• C. Table data and the table structure are deleted.
• D. All the table’s indexes if any exist, are invalidated but retained.
• E. Table data is deleted but the table structure is retained.

Answer: BCD

NEW QUESTION 13
View the Exhibit and examine the description of the EMPLOYEES table.

You want to calculate the total renumeration for each employee. Total renumeration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.
Which SQL statement would you execute to get the desired output?

• A. SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct, salary,salary+commission_pct))“Total”FROM EMPLOYEES;
• B. SELECT first_name, salary, salary*12+salary*commission_pct “Total”FROM EMPLOYEES;
• C. SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 “Total”FROM EMPLOYEES;
• D. SELECT first_name, salary*12 + NVL(salary,0)*commission_pct, “Total”FROM EMPLOYEES;

Answer: A

NEW QUESTION 14
View the Exhibit and examine the details of the PRODUCT_INFORMATION table.

You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name, list_price FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?

• A. It would not execute because the entire WHERE clause is not enclosed within parentheses.
• B. It would execute but would return no rows.
• C. It would not execute because the same column has been used twice with the AND logical operator.
• D. It would execute and return the desired.

Answer: B

NEW QUESTION 15
In the EMPLOYEES table there are 1000 rows and employees are working in the company for more than 10 years.
Evaluate the following SQL statement:

What would be the result?

• A. It executes successfully but no rows updated.
• B. It executes successfully and updates the records of those employees who have been working in the company for more than 600 days.
• C. It gives an error because multiple NVL functions are used in an expression.
• D. It gives an error because NVL function cannot be used with UPDATE.

Answer: B

NEW QUESTION 16
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. (Choose the best answer.)

You executed this UPDATE statement: UPDATE
( SELECT order_date, order_total, customer_id FROM orders) Set order_date = '22-mar-2007'
WHERE customer_id IN
(SELECT customer_id FROM customers
WHERE cust_last_name = 'Roberts' AND credit_limit = 600); Which statement is true regarding the execution?

• A. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
• B. It would not execute because two tables cannot be referenced in a single UPDATE statement.
• C. It would execute and restrict modifications to the columns specified in the SELECT statement.
• D. It would not execute because a SELECT statement cannot be used in place of a table name.

Answer: C

NEW QUESTION 17
Which two tasks can be performed by using Oracle SQL statements?

• A. changing the password for an existing database user
• B. connecting to a database instance
• C. querying data from tables across databases
• D. starting up a database instance
• E. executing operating system (OS) commands in a session

Answer: AC

Explanation: References:
http://www.techonthenet.com/oracle/password.php
https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm

NEW QUESTION 18
Examine the SQL statement used to create the TRANSACTION table. (Choose the best answer.)
SQL > CREATE TABLE transaction (trn_id char(2) primary key,
Start_date date DEFAULT SYSDATE, End_date date NOT NULL);
The value 'A1' does not exist for trn_id in this table.
Which SQL statement successfully inserts a row into the table with the default value for START_DATE?

• A. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10))
• B. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))
• C. INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')
• D. INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-2014')

Answer: C