NEW QUESTION 1
You need to display the date 11-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?

• A. SELECT TO_CHAR (TO_DATE (’11-oct-2007’), ‘fmDdthsp “of” Month, Year’)FROM DUAL
• B. SELECT TO_CHAR (‘11-oct-2007’, ‘fmDdspth “of” Month, Year’)FROM DUAL
• C. SELECT TO_CHAR (TO_DATE (‘11-oct-2007’), ‘fmDdspth of month, year’)FROM DUAL
• D. SELECT TO_DATE (TO_CHAR (’11-oct-2007’), ‘fmDdspth “of” Month, Year’))FROM DUAL

Answer: C

NEW QUESTION 2
You execute the SQL statement: SQL> CREATE TABLE citizens
(citizen_id CHAR (10) PRIMARY KEY, last_name VARCHAR2 (50) NOT NULL, first_name VARCHAR2 (50),
address VARCHAR2 (100),
city VARCHAR2 (30) DEFAULT ‘SEATTLE’ NOT NULL,
CONSTRAINT cnames CHECK (first_name<>last_name) ); What is the outcome?

• A. It fails because the NOT NULL and DEFAULT options cannot be combined for the same column.
• B. It succeeds and CITY can contain only ‘SEATTLE’ or null for all rows.
• C. It fails because the condition for the CANAMES constraint is not valid.
• D. It succeeds and an index is crated for CITIZEN_ID.

Answer: A

NEW QUESTION 3
Examine the structure of the EMPLOYEES table. (Choose two.)

You must display the maximum and minimum salaries of employees hired 1 year ago. Which two statements would provide the correct output?

• A. SELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);
• B. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE-365)GROUP BY maxsal, minsal;
• C. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);
• D. SELECT MIN(Salary), MAX(salary)FROM (SELECT salary FROM employeesWHERE hire_date < SYSDATE-365);

Answer: BD

NEW QUESTION 4
When does a transaction complete? (Choose all that apply.)

• A. When a PL/SQL anonymous block is executed
• B. When a DELETE statement is executed
• C. When a data definition language statement is executed
• D. When a TRUNCATE statement is executed after the pending transaction
• E. When a ROLLBACK command is executed

Answer: CDE

NEW QUESTION 5
Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);
SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));
You executed the following statement: SQL> DELETE from stores
WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully?

• A. Disable the primary key in the STORES table.
• B. Use CASCADE keyword with DELETE statement.
• C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
• D. Disable the FOREIGN KEY in SALES table and then delete the rows.
• E. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.

Answer: CDE

NEW QUESTION 6
Which three statements are true regarding single-row functions? (Choose three.)

• A. The data type returned, can be different from the data type of the argument that is referenced.
• B. They can return multiple values of more than one data type.
• C. They can accept only one argument.
• D. They can be nested up to only two levels.
• E. They can be used in SELECT, WHERE, and ORDER BY clauses.
• F. They can accept column names, expressions, variable names, or a user-supplied constants as arguments.

Answer: AEF

NEW QUESTION 7
View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator: SQL > SELECT p.prod_id, count(s.prod_id)
FROM products p sales s ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN operations can be used to obtain the required output?

• A. FULL OUTER JOIN
• B. JOIN
• C. LEFT OUETR JOIN
• D. RIGHT OUTER JOIN

Answer: AC

NEW QUESTION 8
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:

You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses.
Which SQL statement must you use?

• A. SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)
• B. SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN.department_details d ON (c.depatrment_id=d.department_id)
• C. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=
• D. department_id)
• E. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>
• F. department_id)

Answer: C

NEW QUESTION 9
Examine the structure proposed for the TRANSACTIONS table:

Which two statements are true regarding the storage of data in the above table structure? (Choose two.)

• A. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
• B. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
• C. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
• D. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.

Answer: AB

NEW QUESTION 10
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:
SQL>CREATE TABLE DEPARTMENT_DETAILS (DEPARTMENT_ID NUMBER PRIMARY KEY, DEPARTMENT_NAMEVARCHAR2(50), HODVARCHAR2(50));
SQL>CREATE TABLE COURSE_DETAILS (COURSE_IDNUMBER PRIMARY KEY, COURSE_NAMEVARCHAR2(50), DEPARTMENT_IDVARCHAR2(50));
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?

• A. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=
• B. department_id);
• C. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=
• D. department_id);
• E. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=
• F. department_id);
• G. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=
• H. department_id);

Answer: B

NEW QUESTION 11
Which three statements are true regarding subqueries?

• A. Multiple columns or expressions can be compared between the main query and subquery.
• B. Subqueries can contain ORDER BY but not the GROUP BY clause.
• C. Main query and subquery can get data from different tables.
• D. Subqueries can contain GROUP BY and ORDER BY clauses.
• E. Main query and subquery must get data from the same tables.
• F. Only one column or expression can be compared between the main query and subquery.

Answer: ACD

Explanation: References:
http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html

NEW QUESTION 12
View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?

• A. UPDATE dept dSET city = ALL (SELECT cityFROM locations lWHERE d.location_id = l.location_id);
• B. UPDATE dept dSET city = (SELECT cityFROM locations l)WHERE d.location_id = l.location_id;
• C. UPDATE dept dSET city = ANY (SELECT cityFROM locations l)
• D. UPDATE dept dSET city = (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

Answer: D

NEW QUESTION 13
View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.

You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the views successfully?

• A. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;
• B. CREATE OR REPLACE VIEW ord_vu (order_id, order_date)AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;
• C. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;
• D. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)||"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_dateWHITH CHECK OPTION;

Answer: C

NEW QUESTION 14
You must display details of all users whose username contains the string 'ch_'. (Choose the best answer.) Which query generates the required output?

• A. SELECT * FROM users Where user_name LIKE '%ch_';
• B. SELECT * FROM usersWhere user_name LIKE '%ch_%'ESCAPE'%';
• C. SELECT * FROM users Where user_name LIKE 'ch_%' ESCAPE '_';
• D. SELECT * FROM users Where user_name LIKE '%ch_%' ESCAPE '';

Answer: B

NEW QUESTION 15
View the Exhibit and examine the structure of the ORDERS table. (Choose the best answer.)

You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.
Which query would give you the desired result?

• A. SELECT order_id, order_date FROM ordersWHERE order_date > ANY(SELECT order_date FROM orders WHERE customer_id = 101);
• B. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
• C. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT order_date FROM orders WHERE customer_id = 101);
• D. SELECT order_id, order_date FROM ordersWHERE order_date > IN(SELECT order_date FROM orders WHERE customer_id = 101);

Answer: C

NEW QUESTION 16
Evaluate the following statement. INSERT ALL
WHEN order_total < 10000 THEN INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders
WHEN order_total > 200000 AND order_total < 20000 THEN INTO large_orders
SELECT order_id, order_total, customer_id FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

• A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
• B. They are evaluated by the first WHEN claus
• C. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
• D. They are evaluated by the first WHEN claus
• E. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
• F. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.

Answer: A

Explanation: References:
http://psoug.org/definition/WHEN.htm

NEW QUESTION 17
View the Exhibit and examine the data in the employees table.

You want to generate a report showing the total compensation paid to each employee to date. You issue the following query:

What is the outcome?

• A. It executes successfully but does not give the correct output.
• B. It generates an error because the concatenation operator can be used to combine only two items.
• C. It generates an error because the usage of the round function in the expression is not valid
• D. It generates an error because the alias is not valid.
• E. It executes successfully and gives the correct output.

Answer: A

NEW QUESTION 18
Which three statements are true regarding the data types?

• A. The minimum column width that can be specified for a VARCHAR2 data type column is one.
• B. Only one LONG column can be used per table.
• C. A TIMESTAMP data type column stores only time values with fractional seconds.
• D. The BLOB data type column is used to store binary data in an operating system file.
• E. The value for a CHAR data type column is blank-padded to the maximum defined column width.

Answer: ABE