QUESTION 141 You want to display the date for the first Monday of the next month and issue the following command: SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),’MON’), ‘dd “is the first Monday for” fmmonth rrrr’) FROM DUAL; What is the outcome? A.    It executes successfully and returns the correct result. B.    It executes successfully but does not return the […]

QUESTION 131 In the CUSTOMERS table, the CUST_CITY column contains the value ‘Paris’ for the CUST_FIRST_NAME ‘ABIGAIL’. Evaluate the following query: SQL> SELECT INITCAP(cust_first_name || ‘ ‘ || UPPER(SUBSTR(cust_city,-LENGTH(cust_city),2))) FROM customers WHERE cust_first_name = ‘ABIGAIL’; What would be the outcome? A.    Abigail PA B.    Abigail Pa C.    Abigail IS D.    an error message Answer: B

QUESTION 121 Which two statements are true regarding the ORDER BY clause? (Choose two.) A.    It is executed first in the query execution. B.    It must be the last clause in the SELECT statement. C.    It cannot be used in a SELECT statement containin g a HAVING clause. D.    You cannot specify a column name […]

QUESTION 111 The CUSTOMERS table has the following structure: Name                     Null            Type ————–        ———–    ———– ———- CUST_ID                 NOT NULL       NUMBER CUST_FIRST_NAME       NOT NULL       VARCHAR2(20) CUST_LAST_NAME        NOT NULL       VARCHAR2(30) CUST_INCOME_LEVEL                     VARCHAR2(30) CUST_CREDIT_LIMIT                     NUMBER You need to write a query that does the following tasks: 1. Display the first name and tax amount of the customers. Tax […]

QUESTION 101 View the Exhibit and examine the structure of ORD and ORD_ITEMS tables. The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO columns are composite PRIMARY KEY in the ORD_ITEMS table. Which two CREATE INDEX statements are valid? (Choose two.)

QUESTION 91 You issued the following command to drop the PRODUCTS table: SQL> DROP TABLE products; What is the implication of this command? (Choose all that apply.) A.    All data along with the table structure is deleted. B.    The pending transaction in the session is committed. C.    All indexes on the table will remain but […]

QUESTION 81 You issue the following query: SQL> SELECT AVG(MAX(qty)) FROM ord_items GROUP BY item_no HAVING AVG(MAX(qty))>50; Which statement is true regarding the outcome of this query? A.    It executes successfully and gives the correct output. B.    It gives an error because the HAVING clause is not valid. C.    It executes successfully but does not […]

QUESTION 71 View the Exhibit and examine the description of SALES and PROMOTIONS tables. You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values. Which DELETE statements are valid? (Choose all that apply.)

QUESTION 61 View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cust_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

QUESTION 51 View the Exhibit and examine the data in the PROMOTIONS table. You need to display all promo categories that do not have ‘discount’ in their subcategory. Which two SQL statements give the required result? (Choose two.)

QUESTION 41 View the Exhibit and examine the structure of CUSTOMERS and GRADES tables. You need to display names and grades of customers who have the highest credit limit. Which two SQL statements would accomplish the task? (Choose two.) A.    SELECT custname, grade FROM customers, grades WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval; […]

QUESTION 31 View the Exhibit and examine the data in the EMPLOYEES table: You want to display all the employee names and their corresponding manager names. Evaluate the following query: SQL> SELECT e.employee_name “EMP NAME”, m.employee_name “MGR NAME” FROM employees e ______________ employees m ON e.manager_id = m.employee_id; Which JOIN option can be used in […]