NEW QUESTION 1
Which statement correctly describes SQL and /SQL*Plus?

• A. Both SQL and /SQL*plus allow manipulation of values in the databas
• B. /SQL*Plus recognizes SQL statements and sends them to the server; SQL is the Oracle proprietary interface for executing SQL statement
• C. /SQL*Plus is a language for communicating with the Oracle server to access data; SQL recognizes SQL statements and sends them to the serve
• D. SQL manipulates data and table definitions in the database; /SQL*Plus does not allow manipulation of values in the databas

Answer: A

NEW QUESTION 2
Exhibit contains the structure of PRODUCTS table:

Evaluate the following query:

What would be the outcome of executing the above SQL statement?

• A. It produces an error
• B. It shows the names of products whose list price is the second highest in the tabl
• C. It shown the names of all products whose list price is less than the maximum list price
• D. It shows the names of all products in the table

Answer: B

NEW QUESTION 3
View the Exhibit and examine the structure of the CUSTOMERS table. Exhibit:

you issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name 'king' and whose credit limit is less than the maximum credit limit in countries that have customers with the last name 'king'.

Which statement is true regarding the outcome of the above query?

• A. It produces an error and the < operator should be replaced by < ANY to get the required output
• B. It produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to get the required output
• C. It executes and shows the required result
• D. It produces an error and the < operator should be replaced by < ALL to get the required output

Answer: C

NEW QUESTION 4
View the Exhibits and examine the structures of the PRODUCTS and SALES tables. Which two SQL statements would give the same output? (Choose two.)

• A. SELECT prod_id FROM products INTERSECT SELECT prod_id FROM sales;
• B. SELECT prod_id FROM products MINUS SELECT prod_id FROM sales;
• C. SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id=s.prod_id;
• D. SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id <> s.prod_id;

Answer: AC

NEW QUESTION 5
What is true about sequences?

• A. Once created, a sequence belongs to a specific schem
• B. Once created, a sequence is linked to a specific tabl
• C. Once created, a sequence is automatically available to all user
• D. Only the DBA can control which sequence is used by a certain tabl
• E. Once created, a sequence is automatically used in all INSERT and UPDATE statement

Answer: A

NEW QUESTION 6
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 7
You created an ORDERS table with the following description: Exhibit:

You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column.
Which statement is true in this scenario?

• A. You cannot add a primary key constraint if data exists in the column
• B. You can add the primary key constraint even if data exists, provided that there are no duplicate values
• C. The primary key constraint can be created only a the time of table creation
• D. You cannot have two constraints on one column

Answer: B

NEW QUESTION 8
What is true about the WITH GRANT OPTION clause?

• A. It allows a grantee DBA privilege
• B. It is required syntax for object privilege
• C. It allows privileges on specified columns of table
• D. It is used to grant an object privilege on a foreign key colum
• E. It allows the grantee to grant object privileges to other users and role

Answer: AE

Explanation: The GRANT command with the WITH GRANT OPTION clause allows the grantee to grant
object privileges to other users and roles.
Incorrect Answers
A:The WITH GRANT OPTION does not allow a grantee DBA privileges.
B:It is not required syntax for object privileges. It is optional clause of GRANT command.
C:GRANT command does not allows privileges on columns of tables.
D:It is not used to grant an object privilege on a foreign key column.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access in Oracle

NEW QUESTION 9
The DBA issues this SQL command:
CREATE USER scott IDENTIFIED by tiger;
What privileges does the user Scott have at this point?

• A. no privileges
• B. only the SELECT privilege
• C. only the CONNECT privilege
• D. all the privileges of a default user

Answer: A

Explanation:
when a user is created, by default no privilege is granted
Incorrect Answer:
BSELECT is not grant
CCONNECT is not grant
Ddefault profile is grant by default not privilege.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-6

NEW QUESTION 10
View the Exhibit and examine the description for the PRODUCTS and SALES table.

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years. Which is the valid DELETE statement?

• A. DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE time_id - 3*365 = SYSDATE );
• B. DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE SYSDATE >= time_id - 3*365 );
• C. DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE SYSDATE - 3*365 >= time_id);
• D. DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE time_id >= SYSDATE - 3*365 );

Answer: C

NEW QUESTION 11
Which constraint can be defined only at the column level?

• A. UNIQUE
• B. NOT NULL
• C. CHECK
• D. PRIMARY KEY
• E. FOREIGN KEY

Answer: B

Explanation:
the NOT NULL constraint can be specified only at the column level, not at the table level.
Incorrect Answer: AUNIQUE can be define at table level CCHECK can be define at table level DPRIMARY KEY can be define at table level EFOREIGN KEY can be define at table level
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-8
New Questions

NEW QUESTION 12
You need to calculate the number of days from 1st January 2007 till date . Dates are stored in the default format of dd-mon-rr. Which two SQL statements would give the required output? (Choose two.)

• A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL:
• B. SELECT SYSDATE - TOJDATE(X)1/JANUARY/2007") FROM DUAL:
• C. SELECT SYSDATE - TOJDATE('01-JANUARY-2007') FROM DUAL:
• D. SELECT TO_CHAR(SYSDAT
• E. 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL:
• F. SELECT TO_DATE(SYSDAT
• G. *DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL:

Answer: BC

NEW QUESTION 13
View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.

Evaluate the following SQL statements:

Which statement is true regarding the above compound query?

• A. It shows products that have a cost recorded irrespective of sales
• B. It shows products that were sold and have a cost recorded
• C. It shows products that were sold but have no cost recorded
• D. It reduces an error

Answer: C

NEW QUESTION 14
View the Exhibit and examine the structure of CUSTOMERS and SALES tables.

Evaluate the following SQL statement:
UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id
FROM sales)
SET time_id = '22-MAR-2007'
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_last_name = 'Roberts' AND
credit_limit = 600);
Which statement is true regarding the execution of the above UPDATE statement?

• A. It would not execute because two tables cannot be used in a single UPDATE statemen
• B. It would not execute because the SELECT statement cannot be used in place of the table nam
• C. It would execute and restrict modifications to only the columns specified in the SELECT statemen
• D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statemen

Answer: C

Explanation:
One UPDATE statement can change rows in only one table, but it can change any number of rows in that table.

NEW QUESTION 15
Which tasks can be performed using SQL functions that are built into Oracle database? (Choose three.)

• A. finding the remainder of a division
• B. adding a number to a date for a resultant date value
• C. comparing two expressions to check whether they are equal
• D. checking whether a specified character exists in a given string
• E. removing trailing, leading, and embedded characters from a character string

Answer: ACD

NEW QUESTION 16
View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables.

Evaluate the following SQL statement:
SQL> SELECT prod_id FROM costs WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL (SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_datepromo_ begin_date)));
What would be the outcome of the above SQL statement?

• A. It displays prod IDs in the promo with the lowest cos
• B. It displays prod IDs in the promos with the lowest cost in the same time interva
• C. It displays prod IDs in the promos with the highest cost in the same time interva
• D. It displays prod IDs in the promos with cost less than the highest cost in the same time interva

Answer: D

NEW QUESTION 17
Which two statements are true regarding tables? (Choose two.)

• A. A table name can be of any lengt
• B. A table can have any number of column
• C. A column that has a DEFAULT value cannot store null value
• D. A table and a view can have the same name in the same schem
• E. A table and a synonym can have the same name in the same schem
• F. The same table name can be used in different schemas in the same databas

Answer: EF

Explanation:
Synonyms Synonyms are database objects that enable you to call a table by another name. You can create synonyms to give an alternative name to a table.