NEW QUESTION 1
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 2
In 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 3
Evaluate the following SQL statements: Exhibit:

Which is the correct output of the above query?

• A. +00-300, +54-02,+00 11:12:10.123457
• B. +00-300,+00-650,+00 11:12:10.123457
• C. +25-00, +54-02, +00 11:12:10.123457
• D. +25-00,+00-650,+00 11:12:10.123457

Answer: C

NEW QUESTION 4
View the Exhibit and examine the structure of the CUSTOMERS table.
You want to generate a report showing the last names and credit limits of all customers
whose last names start with A, B, or C, and credit limit is below 10, 000.
Evaluate the following two queries:

Which statement is true regarding the execution of the above queries?

• A. Only the first query gives the correct resul
• B. Only the second query gives the correct resul
• C. Both execute successfully and give the same resul
• D. Both execute successfully but do not give the required resul

Answer: A

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

Using the PROMOTIONS table, you need to display the names of all promos done after
January 1, 2001 starting with the latest promo.
Which query would give the required result? (Choose all that apply.)

• A. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 1 DESC;
• B. SELECT promo_name,promo_begin_date "START DATE" FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;
• C. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 2 DESC;
• D. SELECT promo_name,promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC;

Answer: BC

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 Singapore data and tim
• 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 7
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which DELETE statement is valid?

• A. DELETE FROM employeesWHERE employee_id = (SELECT employee_id FROM employees);
• B. DELETE * FROM employeesWHERE employee_id=(SELECT employee_id FROM new_employees);
• C. DELETE FROM employeesWHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ‘Carrey’);
• D. DELETE * FROM employeesWHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ‘Carrey’);

Answer: C

NEW QUESTION 8
Examine this statement:
SELECT student_id, gpa FROM student_grades WHERE gpa > &&value;
You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time?

• A. An error is returne
• B. You are prompted to enter a new valu
• C. A report is produced that matches the first report produce
• D. You are asked whether you want a new value or if you want to run the report based on the previous valu

Answer: C

Explanation:
use the double-ampersand if you want to reuse the variable value without prompting the user each time.
Incorrect Answer: Ais not an error
B&& will not prompt user for second time D&& will not ask the user for new value
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7-13

NEW QUESTION 9
Evaluate the SQL statement:
TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)

• A. It releases the storage space used by the tabl
• B. It does not release the storage space used by the tabl
• C. You can roll back the deletion of rows after the statement execute
• D. You can NOT roll back the deletion of rows after the statement execute
• E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an erro
• F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table

Answer: ADF

Explanation:
A: The TRUNCATE TABLE Statement releases storage space used by the table,
D: Can not rollback the deletion of rows after the statement executes,
F: You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the DEPT table.
Incorrect Answer: Cis not true Dis not true Eis not true
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-18

NEW QUESTION 10
Evaluate the following SQL statements: Exhibit:

Exhibit:

The above command fails when executed. What could be the reason?

• A. The BETWEEN clause cannot be used for the CHECK constraint
• B. SYSDATE cannot be used with the CHECK constraint
• C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY
• D. The CHECK constraint cannot be placed on columns having the DATE data type

Answer: B

Explanation:
CHECK Constraint The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level. CREATE TABLE employees (... salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary > 0),

NEW QUESTION 11
You issue 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 in the table are deleted but the table structure will remain
• B. All data along with the table structure is deleted
• C. All views and synonyms will remain but they are invalidated
• D. The pending transaction in the session is committed
• E. All indexes on the table will remain but they are invalidated

Answer: BCD

NEW QUESTION 12
A SELECT statement can be used to perform these three functions:
Choose rows from a table.
Choose columns from a table
Bring together data that is stored in different tables by creating a link between
them.
Which set of keywords describes these capabilities?

• A. difference, projection, join
• B. selection, projection, join
• C. selection, intersection, join
• D. intersection, projection, join
• E. difference, projection, product

Answer: B

Explanation: Explanation: choose rows from a table is SELECTION,
Choose column from a table is PROJECTION
Bring together data in different table by creating a link between them is JOIN.
Incorrect Answer:
Aanswer should have SELECTION, PROJECTION and JOIN.
Canswer should have SELECTION, PROJECTION and JOIN.
Danswer should have SELECTION, PROJECTION and JOIN.
Eanswer should have SELECTION, PROJECTION and JOIN.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-6

NEW QUESTION 13
Which CREATE TABLE statement is valid?

• A. CREATE TABLE ord_details (ord_no NUMBER(2) PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY, ord_date DATE NOT NULL);
• B. CREATE TABLE ord_details (ord_no NUMBER(2) UNIQUE, NOT NULL, item_no NUMBER(3), ord_date DATE DEFAULT SYSDATE NOT NULL);
• C. CREATE TABLE ord_details (ord_no NUMBER(2) , item_no NUMBER(3), ord_date DATE DEFAULT NOT NULL, CONSTRAINT ord_uq UNIQUE (ord_no), CONSTRAINT ord_pk PRIMARY KEY (ord_no));
• D. CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(3), ord_date DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

Answer: D

Explanation:
PRIMARY KEY Constraint
A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value. Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns.

NEW QUESTION 14
Examine the structure of the EMPLOYEES table:

You want to create a SQL script file that contains an INSERT statement. When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below:

Which INSERT statement meets the above requirements?

• A. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
• B. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did IN (20,50));
• C. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50)) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
• D. INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECK OPTION) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
• E. INSERT INTO (SELECT * FROM employees WHERE (department_id = 20 AND department_id = 50) WITH CHECK OPTION ) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);

Answer: D

NEW QUESTION 15
View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement:

The above query generates an error on execution.
Which clause in the above SQL statement causes the error?

• A. WHERE
• B. SELECT
• C. GROUP BY
• D. ORDER BY

Answer: C

NEW QUESTION 16
Which two statements are true regarding views? (Choose two.)

• A. A simple view in which column aliases have been used cannot be update
• B. Rows cannot be deleted through a view if the view definition contains the DISTINCT keywor
• C. Rows added through a view are deleted from the table automatically when the view is droppe
• D. The OR REPLACE option is used to change the definition of an existing view without dropping and recreating i
• E. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the vie

Answer: BD

NEW QUESTION 17
Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME
Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?

• A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
• B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
• C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
• D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');

Answer: B