NEW QUESTION 1

What are two reasons to create synonyms? (Choose two.)

• A. You have too many tables.
• B. Your tables names are too long.
• C. Your tables have difficult names.
• D. You want to work on your own tables.
• E. You want to use another schema's tables.
• F. You have too many columns in your tables.

Answer: BC

Explanation:
Create a synonyms when the names of the tables are too long or the table names are difficult.

NEW QUESTION 2

See the Exhibit and examine the structure and data in the INVOICE table: Exhibit:

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

• A. SELECT MAX(inv_date), MIN(cust_id) FROM invoice;
• B. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
• C. SELECT (AVG(inv_date) FROM invoice;
• D. SELECT AVG(inv_date - SYSDATE), AVG(inv_amt) FROM invoice;

Answer: AD

NEW QUESTION 3

You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be used in the
SELECT statement to get the required output?

• A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
• B. WHERE prod_id LIKE '%_D123%' ESCAPE ''
• C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
• D. WHERE prod_id LIKE '%_D123%' ESCAPE '_'

Answer: B

Explanation:
A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character. The second example in Figure 3-12 shows the SQL statement that retrieves the JOBS
table records with JOB_ID values equal to SA_MAN and SA_REP and which conforms to the original requirement:
select job_id from jobs
where job_id like 'SA_%' escape '';

NEW QUESTION 4

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 5

Which of these is a defining characteristic of a complex view, rather than a simple view? (Choose one or more correct answers.)

• A. Restricting the projection by selecting only some of the table's columns
• B. Naming the view's columns with column aliases
• C. Restricting the selection of rows with a WHERE clause
• D. Performing an aggregation
• E. Joining two tables

Answer: DE

NEW QUESTION 6

The PRODUCTS table has these columns:
PRODUCT_ID NUMBER(4) PRODUCT_NAME VARCHAR2(45) PRICE NUMBER(8, 2)
Evaluate this SQL statement:
SELECT *
FROM PRODUCTS
ORDER BY price, product_name; What is true about the SQL statement?

• A. The results are not sorted.
• B. The results are sorted numerically.
• C. The results are sorted alphabetically.
• D. The results are sorted numerically and then alphabetically.

Answer: D

Explanation:
the result is sort by price which is numeric and follow by product_name which is alphabetically.
Incorrect
A- the results are sorted
B- the results are sorted with alphabetically as well
C- the results are sorted with numerically as well
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-2

NEW QUESTION 7

Which is an iSQL*Plus command?

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

Answer: D

Explanation:
The only SQL*Plus command in this list: DESCRIBE. It cannot be used as SQL command. This command returns a description of tablename, including all columns in that table, the datatype for each column and an indication of whether the column permits storage of NULL values.
Incorrect:
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 8

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.)

• A. SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_subcategory = 'discount';
• B. SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_subcategory = 'discount';
• C. SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_subcategory <> 'discount';
• D. SELECT promo_categoryFROM promotionsINTERSECTSELECT promo_categoryFROM promotionsWHERE promo_subcategory <> 'discount';

Answer: AD

NEW QUESTION 9

Which two statements are true about WHERE and HAVING clauses? (Choose two)

• A. A WHERE clause can be used to restrict both rows and groups.
• B. A WHERE clause can be used to restrict rows only.
• C. A HAVING clause can be used to restrict both rows and groups.
• D. A HAVING clause can be used to restrict groups only.
• E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause.
• F. A HAVING clause CANNOT be used in sub queries.

Answer: BD

Explanation:
B: WHERE clause cannot be use to restrict groups
WHERE clause cannot be use when there is group functions. D: A HAVING clause can only e used to restrict GROUPS.
Note: HAVING clause to specify which groups are to be displayed and thus further restrict the groups on
the basis of aggregate information. The Oracle server performs the following steps when you use the Having clause
1. rows are grouped
2. the group function is applied to the group
3. the group that match the criteria in the Having clause are displayed.
Incorrect Answers :
A- Where clause cannot be use to restrict groups
C- A HAVING clause can only e used to restrict GROUPS.
E- WHERE clause cannot be use when there is group function, instead HAVING is to be use.
F- There is no constraint to use HAVING clause in a sub queries.
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Aggregating Data using Group Functions, p. 5-20

NEW QUESTION 10

Which four are types of functions available in SQL? (Choose 4)

• A. string
• B. character
• C. integer
• D. calendar
• E. numeric
• F. translation
• G. date
• H. conversion

Answer: BEGH

Explanation:
SQL have character, numeric, date, conversion function.
Incorrect
A- SQL have character, numeric, date, conversion function.
C- SQL have character, numeric, date, conversion function.
D- SQL have character, numeric, date, conversion function.
F- QL have character, numeric, date, conversion function.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-3

NEW QUESTION 11

The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query successfully?
SELECT * FROM ord;

• A. CREATE SYNONYM ord FOR orders; This command is issued by OE.
• B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
• C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
• D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

Answer: D

Explanation:
Creating a Synonym for an Object
To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects.
This method can be especially useful with lengthy object names, such as views. In the syntax:
PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created Guidelines
The object cannot be contained in a package.
A private synonym name must be distinct from all other objects that are owned by the same user.
If you try to execute the following command (alternative B, issued by OE):

NEW QUESTION 12

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
A- is not a schema object
B- sub query can contain GROUP BY clause as well.
C- does not necessary contains group functions
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-21

NEW QUESTION 13

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 table.
• B. The statement will fail because subquery cannot be used in the VALUES clause.
• C. The statement will fail because the VALUES clause is not required with subquery.
• D. The statement will fail because subquery in the VALUES clause is not enclosed with in single quotation marks.

Answer: A

NEW QUESTION 14

Which statement correctly describes SQL and /SQL*Plus?

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

Answer: A

NEW QUESTION 15

View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables.
SALE_VW is a view created using the following command: SQL>CREATE VIEW sale_vw AS
SELECT prod_id, SUM(quantity_sold) QTY_SOLD FROM sales GROUP BY prod_id;
You issue the following command to add a row to the SALE_SUMMARY table: SQL>INSERT INTO sale_summary
SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products USING (prod_id) WHERE prod_id = 16;
What is the outcome?

• A. It executes successfully.
• B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table.
• C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match.
• D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table.

Answer: D

NEW QUESTION 16

View the Exhibit and examine the structure of the product, component, and PDT_COMP tables.
In product table, PDTNO is the primary key.
In component table, COMPNO is the primary key.
In PDT_COMP table, <PDTNO, COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in product table and COMPNO is the foreign key referencing the COMPNO in component table.
You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist.
Evaluate the following query:
SQL>SELECT pdtno, pdtname, compno, compname FROM product pdt_comp
USING (pdtno) component USING (compno) WHERE compname IS NOT NULL;
Which combination of joins used in the blanks in the above query gives the correct output?

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

Answer: C

NEW QUESTION 17

View the Exhibit and examine the structure of the customers table.

Using the customers table, you need to generate a report that shows the average credit limit for customers in Washington and NEW YORK.
Which SQL statement would produce the required result?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: C

NEW QUESTION 18

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

Which MERGE statement is valid?

• A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name||', '||e.last_name);
• B. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name);
• C. MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
• D. name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
• E. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET name = e.first_name ||', '|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||', '||e.last_name);

Answer: A

Explanation:
this is the correct MERGE statement syntax
Incorrect
B- it should MERGE INTO table_name
C- it should be WHEN MATCHED THEN
D- it should MERGE INTO table_name
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29

NEW QUESTION 19

Examine the statement:
GRANT select, insert, update ON student_grades
TO manager
WITH GRANT OPTION;
Which two are true? (Choose two.)

• A. MANAGER must be a role.
• B. It allows the MANAGER to pass the specified privileges on to other users.
• C. It allows the MANAGER to create tables that refer to the STUDENT_GRADES table.
• D. It allows the MANAGER to apply all DML statements on the STUDENT_GRADES table.
• E. It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES table.
• F. It allows the MANAGER the ability to select from, delete from, and update the STUDENT_GRADES table.

Answer: BE

Explanation:
GRANT ROLE to ROLE/USER
Incorrect
A- Role can be grant to user
C- Create table privilege is not granted
D- Execute privilege is not granted
F- Delete privilege is not granted
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-15

NEW QUESTION 20

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. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE time_id - 3*365 = SYSDATE );
• B. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE SYSDATE >= time_id - 3*365 );
• C. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE SYSDATE - 3*365 >= time_id);
• D. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE time_id >= SYSDATE - 3*365 );

Answer: C

NEW QUESTION 21

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:
There are no privileges for the user Scott at this point. They are not added themselves to the user immediately after creation. The DBA needs to grant all privileges explicitly.
Incorrect Answers
B:. There are no privileges for the user Scott at this point. SELECT privilege needs to be added to the user Scott.
C:. There are no privileges for the user Scott at this point. CONNECT privilege needs to be added to the user Scott.
D:. There is no default user in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 348-351 Chapter 8: User Access in Oracle

NEW QUESTION 22

Examine the structure of the products table:

You want to display the names of the products that have the highest total value for UNIT_PRICE * QTY_IN_HAND.
Which SQL statement gives the required output?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: A

NEW QUESTION 23

Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
---------------------
Lex De Haan Renske Ladwig Jose Manuel Urman Jason Mallin
You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:
CUST NAME
---------------------
*** De Haan
**** Manuel Urman
Which two queries give the required output? (Choose two.)

• A. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name), '*') "CUST NAME" FROM customersWHERE INSTR(cust_name, ' ', 1, 2)<>0;
• B. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name), '*') "CUST NAME" FROM customersWHERE INSTR(cust_name, ' ', -1, 2)<>0;
• C. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name)- INSTR(cust_name, ''), '*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ', - 1, -2)<>0;
• D. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name)- INSTR(cust_name, ' '), '*') "CUST NAME"FROM customersWHERE INSTR(cust_name, ' ', 1, 2)<>0 ;

Answer: AB

NEW QUESTION 24

View the Exhibit and examine the description for the CUSTOMERS table.

You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where
CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task?

• A. UPDATE customersSET cust_credit_limit = NULLWHERE CUST_INCOME_LEVEL = NULL;
• B. UPDATE customersSET cust_credit_limit = NULLWHERE cust_income_level IS NULL;
• C. UPDATE customersSET cust_credit_limit = TO_NUMBER(NULL)WHERE cust_income_level = TO_NUMBER(NULL);
• D. UPDATE customersSET cust_credit_limit = TO_NUMBER(' ', 9999)WHERE cust_income_level IS NULL;

Answer: B

NEW QUESTION 25

Which three statements are true regarding subqueries? (Choose three.)

• A. Subqueries can contain GROUP BY and ORDER BY clauses.
• B. Main query and subquery can get data from different tables.
• C. Main query and subquery must get data from the same tables.
• D. Subqueries can contain ORDER BY but not the GROUP BY clause.
• E. Only one column or expression can be compared between the main query and subquery.
• F. Multiple columns or expressions can be compared between the main query and subquery.

Answer: ABF

Explanation:
SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.
A subquery can have any of the usual clauses for selection and projection. The following are required clauses:
A SELECT list A FROM clause
The following are optional clauses: WHERE
GROUP BY HAVING
The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.

NEW QUESTION 26

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 27

View the Exhibit and examine the structure of the 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 all products in the table.
• C. It shows the names of products whose list price is the second highest in the table.
• D. It shows the names of all products whose list price is less than the maximum list price.

Answer: C

NEW QUESTION 28
......