NEW QUESTION 1
View the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements:

Which statement is true regarding the above two SQL statements?

• A. statement 1 gives an error, statement 2 executes successfully
• B. statement 2 gives an error, statement 1 executes successfully
• C. statement 1 and statement 2 execute successfully and give the same output
• D. statement 1 and statement 2 execute successfully and give a different output

Answer: D

NEW QUESTION 2
View the Exhibit and examine the data in the PROMOTIONS table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000.
Which SQL statement would you use?

• A. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category = 'post' AND promo_begin_date < '01-01-00';
• B. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';
• C. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';
• D. SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';

Answer: D

NEW QUESTION 3
Which two statements are true regarding the ORDER BY clause? (Choose two.)

• A. It is executed first in the query executio
• B. It must be the last clause in the SELECT statemen
• C. It cannot be used in a SELECT statement containing a HAVING claus
• D. You cannot specify a column name followed by an expression in this claus
• E. You can specify a combination of numeric positions and column names in this claus

Answer: BE

NEW QUESTION 4
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cus_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?

• A. INSERT INTO orders VALUES (l.'10-mar-2007 'direct'. (SELECT customerid FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000);
• B. INSERT INTO orders (order_id.order_date.order_mod
• C. (SELECT customer id FROM customers WHERE cust_last_iiame='Roberts' AND redit_limit=600).order_total) VALUES(L'10-mar-2007'. 'direct', &&customer_id, 1000):
• D. INSERT INTO(SELECT o.order_i
• E. o.order_date.o.order_modex.customer_i
• F. o.ordertotal FROM orders
• G. customers c WHERE o.customer_id = c.customerid AND c.cust_la$t_name-RoberTs' ANDc.credit_liinit=600) VALUES (L'10-mar-2007 'direct'.( SELECT customer_id FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000);
• H. INSERT INTO orders (order_id.order_date.order_mod
• I. (SELECT customer_id FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600).order_total) VALUES(l.'10-mar-2007 'direct'. &customer_i
• J. 1000):

Answer: A

NEW QUESTION 5
Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose all that apply.)

• A. The HAVING clause can be used with aggregate functions in subquerie
• B. The WHERE clause can be used to exclude rows after dividing them into group
• C. The WHERE clause can be used to exclude rows before dividing them into group
• D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the quer
• E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the tabl

Answer: AC

NEW QUESTION 6
You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command:
Exhibit:

PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.
What is the outcome?

• A. Number of days since the promo started based on the current Chicago data and time
• B. Number of days since the promo started based on the current Singapore data and tim
• C. An error because the WHERE condition specified is invalid
• D. An error because the ROUND function specified is invalid

Answer: A

NEW QUESTION 7
Examine the structure of the MARKS table:
Exhibit:

Which two statements would execute successfully? (Choose two.)

• A. SELECT student_name,subject1 FROM marks WHERE subject1 > AVG(subject1);
• B. SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE 'R%';
• C. SELECT SUM(subject1+subject2+subject3) FROM marks WHERE student_name IS NULL;
• D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) FROM marks WHERE subject1 > subject2;

Answer: CD

NEW QUESTION 8
Which is an iSQL*Plus command?

• A. INSERT
• B. UPDATE
• C. SELECT
• D. DESCRIBE
• E. DELETE
• F. RENAME

Answer: D

Explanation: Explanation: The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL command. This command returns a description of table name, including all columns in that table, the datatype for each column and an indication of whether the column permits storage of NULL values. Incorrect Answer: A INSERT is not a SQL*PLUS command B UPDATE is not a SQL*PLUS command C SELECT is not a SQL*PLUS command E DELETE is not a SQL*PLUS command F RENAME is not a SQL*PLUS command
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7

NEW QUESTION 9
View the exhibit and examine the description for the SALES and CHANNELS tables.

You issued the following SQL statement to insert a row in the SALES table:
INSERT INTO sales VALUES (23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel_desc='Direct Sales'), 12, 1, 500);
Which statement is true regarding the execution of the above statement?

• A. The statement will execute and the new row will be inserted in the SALES tabl
• B. The statement will fail because subquery cannot be used in the VALUES claus
• C. The statement will fail because the VALUES clause is not required with subquer
• D. The statement will fail because subquery in the VALUES clause is not enclosed with in single quotation mark

Answer: A

NEW QUESTION 10
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city ='Singapore');
Which statement is true regarding the above query if one of the values generated by the subquery is NULL?

• A. It produces an erro
• B. It executes but returns no row
• C. It generates output for NULL as well as the other values produced by the subquer
• D. It ignores the NULL value and generates output for the other values produced by the subquer

Answer: C

NEW QUESTION 11
Which best describes an inline view?

• A. a schema object
• B. a sub query that can contain an ORDER BY clause
• C. another name for a view that contains group functions
• D. a sub query that is part of the FROM clause of another query

Answer: D

Explanation:
a sub query that is part of the FROM clause of another query
Incorrect Answer:
Ais not a schema object
Bsub query can contain GROUP BY clause as well.
Cdoes not necessary contains group functions
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-21

NEW QUESTION 12
You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table.
Which set of statements accomplishes this?

• A. CREATE ROLE registrar; GRANT MODIFY ON student_grades TO registrar; GRANT registrar to user1, user2, user3
• B. CREATE NEW ROLE registrar; GRANT ALL ON student_grades TO registrar; GRANT registrar to user1, user2, user3
• C. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT ROLE registrar to user1, user2, user3
• D. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;
• E. CREATE registrar; GRANT CHANGE ON student_grades TO registrar; GRANT registrar;

Answer: D

Explanation:
this is the correct solution for the answer. GRANT role_name to users;
Incorrect Answer: Athere is no such MODIFY keyword Binvalid CREATE command, there is no such NEW keyword Cinvalid GRANT command, there is no such ROLE keyword Einvalid GRANT command, there is no such CHANGE keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-10

NEW QUESTION 13
Which two statements are true about sequences created in a single instance database? (Choose two.)

• A. CURRVAL is used to refer to the last sequence number that has been generated
• B. DELETE <sequencename> would remove a sequence from the database
• C. The numbers generated by a sequence can be used only for one table
• D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement
• E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted

Answer: AD

Explanation:
Gaps in the Sequence
Although sequence generators issue sequential numbers without gaps, this action occurs
independent of a commit or rollback. Therefore, if you roll back a statement containing a
sequence, the number is lost.
Another event that can cause gaps in the sequence is a system crash. If the sequence
caches values in memory, those values are lost if the system crashes.
Because sequences are not tied directly to tables, the same sequence can be used for
multiple tables.
However, if you do so, each table can contain gaps in the sequential numbers.
Modifying a Sequence
If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement To remove a sequence, use the DROP statement:
DROP SEQUENCE dept_deptid_seq;

NEW QUESTION 14
Examine the structure and data in the PRICE_LIST table:
Name Null Type
PROD_ID NOT NULL NUMBER(3) PROD_PRICE VARCHAR2(10) PROD_ID PROD_PRICE
100 $234.55 101 $6,509.75 102 $1,234
You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.
Which SQL statement would give the required result?

• A. SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST;
• B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST;
• C. SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;
• D. SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;

Answer: B

Explanation: Use TO_NUMBER on the prod_price column to convert from char to number
to be able to multiply it with 0.25. Then use the TO_CHAR function (with
formatting'$99,999.00') to convert the number back to char.
Incorrect:
Not C: Use the formatting'$99,999.00' with the TO_CHAR function, not with the
TO_NUMBER function.
Note:
*
Using the TO_CHAR Function The TO_CHAR function returns an item of data type VARCHAR2. When applied to items of type NUMBER, several formatting options are available. The syntax is as follows: TO_CHAR(number1, [format], [nls_parameter]), The number1 parameter is mandatory and must be a value that either is or can be implicitly converted into a number. The optional format parameter may be used to specify numeric formatting information like width, currency symbol, the position of a decimal point, and group (or thousands) separators and must be enclosed in single
*
Syntax of Explicit Data Type Conversion Functions TO_NUMBER(char1, [format mask], [nls_parameters]) = num1 TO_CHAR(num1, [format mask], [nls_parameters]) = char1 TO_DATE(char1, [format mask], [nls_parameters]) = date1 TO_CHAR(date1, [format mask], [nls_parameters]) = char1

NEW QUESTION 15
View the Exhibit and examine the structure of the PRODUCTS table.

Which two tasks would require subqueries? (Choose two.)

• A. Display the minimum list price for each product statu
• B. Display all suppliers whose list price is less than 1000.
• C. Display the number of products whose list price is more than the average list pric
• D. Display the total number of products supplied by supplier 102 and have product status as 'obsolete'.
• E. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'.

Answer: CE

NEW QUESTION 16
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit:

Evaluate the following query: Exhibit:

The above query produces an error on execution. What is the reason for the error?

• A. An alias cannot be used in an expression
• B. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression
• C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values
• D. The alias NAME should not be enclosed within double quotation marks

Answer: A

NEW QUESTION 17
What is true about sequences?

• A. The start value of the sequence is always 1.
• B. A sequence always increments by 1.
• C. The minimum value of an ascending sequence defaults to 1.
• D. The maximum value of descending sequence defaults to 1.

Answer: C