NEW QUESTION 1
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 2
Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, ename VARCHAR2(15),
salary NUMBER (8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no)); ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE; ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_PK?

• A. It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.
• B. It would remain disabled and has to be enabled manually using the ALTER TABLE command.
• C. It would be automatically enabled and immediate.
• D. It would be automatically enabled and deferred.

Answer: B

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

You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.

• A. SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALL SELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INETRSECTSELECT last_name, hire_date, salaryFROM employees WHERE hire_date > SYSDATE- 180;
• B. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)UNION ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000);
• C. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000;
• D. (SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180;

Answer: C

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

You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.
Which query would produce the desired result?

• A. SELECT order_idFROM order_itemsGROUP BY order_idHAVING SUM(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity))FROM order_items GROUP BY order_id);
• B. SELECT order_idFROM order_itemsWHERE(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)FROM order_items) GROUP BY order_id);
• C. SELECT order_idFROM order_itemsWHERE(unit_price*quantity) = MAX(unit_price*quantity)GROUP BY order_id);
• D. SELECT order_idFROM order_itemsWHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_itemsGROUP BY order_id)

Answer: A

NEW QUESTION 5
View the Exhibit and examine the details of PRODUCT_INFORMATION table.
PRODUCT_NAME CATEGORY_ID SUPPLIER_ID
Inkjet C/8/HQ 12
102094
Inkjet C/4 12
102090
LaserPro 600/6/BW 12
102087
LaserPro 1200/8/BW 12
102099
Inkjet B/6 12
102096
Industrial 700/ID 12
102086
Industrial 600/DQ 12
102088
Compact 400/LQ 12
102087
Compact 400/DQ 12
102088
HD 12GB /R 13
102090
HD 10GB /I 13
102071
HD 12GB @7200 /SE 13
102057
HD 18.2GB @10000 /E 13
102078
HD 18.2GB @10000 /I 13
102050
HD 18GB /SE 13
102083
HD 6GB /I 13
102072
HD 8.2GB@5400 13
102093
You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name 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 same column has been used in both sides of the AND logical operator to form the condition.
• B. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
• C. It would execute and the output would display the desired result.
• D. It would execute but the output would return no rows.

Answer: D

NEW QUESTION 6
Which three statements are true regarding the SQL WHERE and HAVING clauses?

• A. The HAVING clause conditions can have aggregating functions.
• B. The HAVING clause conditions can use aliases for the columns.
• C. The WHERE and HAVING clauses cannot be used together in a SQL statement.
• D. The WHERE clause is used to exclude rows before grouping data.
• E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

Answer: ADE

NEW QUESTION 7
Which two statements best describe the benefits of using the WITH clause? (Choose two.)

• A. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.
• B. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
• C. It enables sessions to store a query block permanently in memory and use it to create complex queries.
• D. It enables sessions to store the results of a query permanently.

Answer: AB

NEW QUESTION 8
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

• A. SELECT TO_CHAR (1890.55, '$99G999D00') FROM DUAL
• B. SELECT TO_CHAR (1890.55, '$9,999V99') FROM DUAL;
• C. SELECT TO_CHAR (1890.55, '$0G000D00') FROM DUAL;
• D. SELECT TO_CHAR (1890.55, '$99,999D99') FROM DUAL;
• E. SELECT TO_CHAR (1890.55, '$99G999D99') FROM DUAL

Answer: ACE

NEW QUESTION 9
View the Exhibit and examine the structures of the employees and departments tables.

You must update the employees table according to these requirements::
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department id corresponding to London (locationid 2100).
-Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission In location_id 2100 to 1.5 times the average commission of their department. You issue this command:

What is the result?

• A. It executes successfully but does not produce the desired update.
• B. It executes successfully and produces the desired update.
• C. It generates an error because multiple columns cannot be specified together in an UPDATE statement.
• D. It generates an error because a subquery cannot have a join condition in an update statement.

Answer: A

NEW QUESTION 10
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 11
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 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. Each row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent when clauses.
• B. All rows are evaluated by all the three WHEN clauses.
• C. Each row is evaluated by the first WHEN clause and if the condition is true, then the row would be evaluated by the subsequent when clauses.
• D. The INSERT statement will return an error because the ELSE clause is missing.

Answer: B

NEW QUESTION 12
View the Exhibit and examine the structure of CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
Which SQL statement would produce the required result?

• A. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
• B. SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
• C. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"FROM customers;
• D. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT"FROM customers;

Answer: A

NEW QUESTION 13
n the customers table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'Abigail'.
Evaluate the following query:

What would be the outcome?

• A. Abigail PA
• B. Abigail Pa
• C. Abigail IS
• D. An error message

Answer: B

NEW QUESTION 14
The BOOKS_TRANSACTIONS table exists in your database. SQL>SELECT * FROM books_transactions ORDER BY 3; What is the outcome on execution?

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

Answer: D

NEW QUESTION 15
Examine the structure of the MEMBERS table: NameNull?Type
------------------ --------------- ------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
You execute the SQL statement:
SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members;
What is the outcome?

• A. It fails because the alias name specified after the column names is invalid.
• B. It fails because the space specified in single quotation marks after the first two column names is invalid.
• C. It executes successfully and displays the column details in a single column with only the alias column heading.
• D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

Answer: D

NEW QUESTION 16
Examine the structure of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions. Which SQL statement must you execute?

• A. SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE FROM BOOKS_TRANSACTIONS;
• B. SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM BOOKS_TRANSACTIONS;
• C. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE FEE" FROM BOOKS_TRANSACTIONS;
• D. SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE" FROM BOOKS_TRANSACTIONS;

Answer: C

NEW QUESTION 17
Which three statements are true about the ALTER TABLE....DROP COLUMN.... command?

• A. A column can be dropped only if it does not contain any data.
• B. A column can be dropped only if another column exists in the table.
• C. A dropped column can be rolled back.
• D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
• E. A parent key column in the table cannot be dropped.

Answer: BDE

NEW QUESTION 18
View the Exhibit and examine the description of the ORDERS table. (Choose two.)

Which two WHERE clause conditions demonstrate the correct usage of conversion functions?

• A. WHERE Order_date_IN ( TO_DATE('OCT 21 2003', 'MON DD YYYY'), TO_CHAR('NOV 21 2003', 'MON DD YYYY') )
• B. WHERE Order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
• C. WHERE TO_CHAR(Order_date, 'MON DD YYYY') = 'JAN 20 2003'
• D. WHERE Order_date > ( TO_DATE('JUL 10 2006', 'MON DD YYYY')

Answer: CD

NEW QUESTION 19
Which task can be performed by using a single Data Manipulation Language (DML) statement?

• A. adding a column constraint when inserting a row into a table
• B. adding a column with a default value when inserting a row into a table
• C. removing all data only from one single column on which a unique constraint is defined
• D. removing all data only from one single column on which a primary key constraint is defined

Answer: C

NEW QUESTION 20
Examine the structure of the MEMBERS table: (Choose the best answer.)

Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC;
What would be the result execution?

• A. It displays all cities in descending order, within which the last names are further sorted in descending order.
• B. It fails because a column alias cannot be used in the ORDER BY clause.
• C. It fails because a column number and a column alias cannot be used together in the ORDER BY clause.
• D. It displays all cities in ascending order, within which the last names are further sorted in descending order.

Answer: D

NEW QUESTION 21
View the exhibit for the structure of the STUDENT and FACULTY tables. STUDENT
NameNull?Type
-------------------------------------------------- STUDENT_IDNOT NULLNUMBER(2) STUDENT_NAMEVARCHAR2(20) FACULTY_IDVARCHAR2(2) LOCATION_IDNUMBER(2) FACULTY
NameNull?Type
-------------------------------------------------- FACULTY_IDNOT NULLNUMBER(2) FACULTY_NAMEVARCHAR2(20) LOCATION_IDNUMBER(2)
You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements: Statement 1
SQL>SELECT faculty_name, COUNT(student_id) FROM student JOIN faculty
USING (faculty_id, location_id) GROUP BY faculty_name; Statement 2
SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty GROUP BY faculty_name;
Which statement is true regarding the outcome?

• A. Only statement 2 executes successfully and gives the required result.
• B. Only statement 1 executes successfully and gives the required result.
• C. Both statements 1 and 2 execute successfully and give different results.
• D. Both statements 1 and 2 execute successfully and give the same required result.

Answer: B

NEW QUESTION 22
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 23
Which two statements are true regarding the COUNT function?

• A. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
• B. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
• C. COUNT (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
• D. COUNT (*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.
• E. The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.

Answer: BD

NEW QUESTION 24
......