NEW QUESTION 1
Which view should a user query to display the columns associated with the constraints on a table owned by the user?

• A. USER_CONSTRAINTS
• B. USER_OBJECTS
• C. ALL_CONSTRAINTS
• D. USER_CONS_COLUMNS
• E. USER_COLUMNS

Answer: D

Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view.
Incorrect:
A- table to view all constraints definition and names
B- show all object name belong to user
C- does not display column associated
E- no such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25

NEW QUESTION 2
You have created an index with this statement:
create index ename_i on employees(last_name,first_name);
How can you adjust the index to include the employees' birthdays, which is a date type column called DOB?

• A. Use ALTER INDEX ENAME_I ADD COLUMN DOB;.
• B. You can't do this because of the data type mismatch.
• C. You must drop the index and re-create it.
• D. This can only be done if the column DOB is NULL in all existing rows.

Answer: C

NEW QUESTION 3
Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1 START WITH 100
INCREMENT BY 10
MAXVALUE 200 CYCLE NOCACHE;
The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?

• A. 1
• B. 10
• C. 100
• D. an error

Answer: A

Explanation: But why the answer is not "C" ?
Because you didn't specify the MINVALUE for the sequence. If you check the sequence definition that you created it will have the default value of 1, which it reverts to when cycling.
If you wanted to keep the minimum value you would need to specify it in the sequence creation.
sequence Is the name of the sequence generator
INCREMENT BY n Specifies the interval between sequence numbers, where n is an integer (If this clause is omitted, the sequence increments by 1.)
START WITH n Specifies the first sequence number to be generated (If this clause is omitted, the sequence starts with 1.)
MAXVALUE n Specifies the maximum value the sequence can generate
NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and –1 for a descending sequence (This is the default option.)
MINVALUE n Specifies the minimum sequence value
NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and –(10^26) for a descending sequence (This is the default option.)
CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching its maximum or minimum value
(NOCYCLE is the default option.)
CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and keeps in memory (By default, the Oracle server caches 20 values.)

NEW QUESTION 4
Examine the structure and data in the PRIC E_LIST table: Name Null? Type
------------ ------------- -------------------
PROD_D NOT NULL NUMBER(3) PROD_PRICE VARCHAR2(10) PROD_ID PROD PRICE
----------- ------------------------ 100 $234.55
101 $6, 509.75
102 $1, 234
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 PRICEJLIST:
• B. . SELECT TO_CHAR(TO_NUMBER(prod_price)* .25.'$99.999.00') FROM PRICE_LIST;
• C. SELECT TO_CRAR(TO_NUMBER(prod_price.'S99.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: C

NEW QUESTION 5
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:

Which join option can be used in the blank in the above query to get the required output?

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

Answer: C

NEW QUESTION 6
View the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:

Using the PROMOTIONS table, you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:

What would be the outcome?

• A. It generates an error because multiple conditions cannot be specified for the WHEN clause
• B. It executes successfully and gives the required result
• C. It generates an error because CASE cannot be used with group functions
• D. It generates an error because NULL cannot be specified as a return value

Answer: B

Explanation: CASE Expression
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn ELSE else_expr]
END

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

• A. A table can have only one primary key and one foreign key.
• B. A table can have only one primary key but multiple foreign keys.
• C. Only the primary key can be defined at the column and table levels.
• D. The foreign key and parent table primary key must have the same name.
• E. Both primary key and foreign key constraints can be defined at both column and table levels.

Answer: BE

NEW QUESTION 8
Examine the structure of the PROMOS table:

You want to generate a report showing promo names and their duration (number of days).
If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed. Which queries give the correct output? (Choose all that apply.)

• A. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date, 'ONGOING')) FROM promos;
• B. SELECT promo_name, COALESCE(TO_CHAR(promo_end_date - promo_start_date), 'ONGOING') FROM promos;
• C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date), 'ONGOING') FROM promos;
• D. SELECT promo_name, DECODE(promo_end_date-promo_start_date, NULL, 'ONGOING', promo_end_date - promo_start_date) FROM promos;
• E. SELECT promo_name, ecode(coalesce(promo_end_date, promo_start_date), null, 'ONGOING', promo_end_date - promo_start_date)FROM promos;

Answer: BCD

NEW QUESTION 9
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 generates an error because the alias is not valid.
• B. It executes successfully and gives the correct output.
• C. It executes successfully but does not give the correct output.
• D. It generates an error because the usage of the ROUND function in the expression is not valid.
• E. It generates an error because the concatenation operator can be used to combine only two items.

Answer: C

Explanation: ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.)

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

• A. A foreign key cannot contain NULL values.
• B. The column with a UNIQUE constraint can store NULLS.
• C. A constraint is enforced only for an INSERT operation on a table.
• D. You can have more than one column in a table as part of a primary key.

Answer: BD

NEW QUESTION 11
Evaluate the following SQL statements: DELETE FROM sales;
There are no other uncommitted transactions on the SALES table. Which statement is true about the DELETE statement?

• A. It removes all the rows as well as the structure of the table
• B. It removes all the rows in the table and deleted rows cannot be rolled back
• C. It removes all the rows in the table and deleted rows can be rolled back
• D. It would not remove the rows if the table has a primary key

Answer: C

NEW QUESTION 12
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 Singapore data and time.
• B. An error because the ROUND function specified is invalid
• C. An error because the WHERE condition specified is invalid
• D. Number of days since the promo started based on the current Chicago data and time

Answer: D

NEW QUESTION 13
SLS is a private synonym for the SH.SALES table. The user SH issues the following command: DROP SYNONYM sls;
Which statement is true regarding the above SQL statement?

• A. Only the synonym would be dropped.
• B. The synonym would be dropped and the corresponding table would become invalid.
• C. The synonym would be dropped and the packages referring to the synonym would be dropped.
• D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.

Answer: A

Explanation: A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points.
Private synonyms are schema objects. Either they must be in your own schema, or they must be qualified with the schema name. Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name.
Private synonyms must be a unique name within their schema. Public synonyms can have the same name as schema objects. When executing statements that address objects without a schema qualifier, Oracle will first look for the object in the local schema, and only if it cannot be found will it look for a public synonym.

NEW QUESTION 14
A data manipulation language statement .

• A. completes a transaction on a table
• B. modifies the structure and data in a table
• C. modifies the data but not the structure of a table
• D. modifies the structure but not the data of a table

Answer: C

Explanation: modifies the data but not the structure of a table
Incorrect
A- DML does not complete a transaction
B- DDL modifies the structure and data in the table
D- DML does not modified table structure.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-3

NEW QUESTION 15
Examine the structure of the TRANSACTIONS table:
Name Null Type
TRANS_ID NOT NULL NUMBER (3) CUST_NAME VARCHAR2(30) TRANS_DATE DATE
TRANS_AMT NUMBER(10, 2)
You want to display the transaction date and specify whether it is a weekday or weekend. Evaluate the following two queries:

Which statement is true regarding the above queries?

• A. Both give wrong results.
• B. Both give the correct result.
• C. Only the first query gives the correct result.
• D. Only the second query gives the correct result.

Answer: C

Explanation: Range Conditions Using the BETWEEN Operator
Use the BETWEEN operator to display rows based on a range of values: SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
Range Conditions Using the BETWEEN Operator
You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower limit and an upper limit.
The SELECT statement in the slide returns rows from the EMPLOYEES table for any employee whose salary is between $2, 500 and $3, 500.
Values that are specified with the BETWEEN operator are inclusive. However, you must specify the lower limit first.
You can also use the BETWEEN operator on character values: SELECT last_name
FROM employees
WHERE last_name BETWEEN 'King' AND 'Smith';

NEW QUESTION 16
Which four are attributes of single row functions? (Choose four.)

• A. cannot be nested
• B. manipulate data items
• C. act on each row returned
• D. return one result per row
• E. accept only one argument and return only one value
• F. accept arguments which can be a column or an expression

Answer: BCDF

Explanation: Manipulate data items, act on each row returned, return one result per row, and accept arguments that can be a column or expression.
Incorrect
A- is not single row attributes
E- functions can accept more than one argument, e.g NVL2
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-5

NEW QUESTION 17
You need to perform these tasks:
Which set of SQL statements achieves the desired results?

• A. CREATE ROLE manager;GRANT create table, create viewTO manager;GRANT manager TO BLAKE, CLARK;
• B. CREATE ROLE manager;GRANT create table, create voewTO manager;GRANT manager ROLE TO BLAKE, CLARK;
• C. GRANT manager ROLE TO BLAKE, CLARK;GRANT create table, create voewTO BLAKE CLARK;***MISSING***

Answer: A

Explanation: Result of commands:

NEW QUESTION 18
Which three statements/commands would cause a transaction to end? (Choose three.)

• A. COMMIT
• B. SELECT
• C. CREATE
• D. ROLLBACK
• E. SAVEPOINT

Answer: ACD

NEW QUESTION 19
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?

• A. INSERT INTO ordersVALUES (1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
• B. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES(1, '10-mar-2007', 'direct', &&customer_id, 1000);
• C. INSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND c.cust_last_name='Roberts' ANDc.credit_limit=600 )VALUES (1, '10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), 1000);
• D. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' ANDcredit_limit=600), order_total)VALUES(1, '10-mar-2007', 'direct', &customer_id, 1000);

Answer: A