NEW QUESTION 1
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 2
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 3
Examine the structure and data of the CUSTJTRANS table:
CUSTJRANS
Name Null? Type
CUSTNO NOT NULL CHAR(2) TRANSDATE DATE TRANSAMT NUMBER(6.2) CUSTNO TRANSDATE TRANSAMT
11 01-JAN-07 1000
22 01-FEB-07 2000
33 01-MAR-07 3000
Dates are stored in the default date format dd-mon-rr in the CUSTJTRANS table. Which three SQL statements would execute successfully? (Choose three.)

• A. SELECT transdate + '10' FROM custjrans;
• B. SELECT * FROM custjrans WHERE transdate = '01-01-07':
• C. SELECT transamt FROM custjrans WHERE custno > '11':
• D. SELECT * FROM custjrans WHERE transdate='01-JANUARY-07':
• E. SELECT custno - 'A' FROM custjrans WHERE transamt > 2000:

Answer: ACD

NEW QUESTION 4
View the Exhibit and examine the data in the COSTS table.

You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query:

Which statement is true regarding the above query?

• A. It executes and produces the required resul
• B. It produces an error because an expression cannot be used in the ORDER BY claus
• C. It produces an error because the DESC option cannot be used with an expression in the ORDER BY claus
• D. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT claus

Answer: A

NEW QUESTION 5
The SQL statements executed in a user session as follows: Exhibit:

Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)

• A. Both the DELETE statements and the UPDATE statement are rolled back
• B. The rollback generates an error
• C. Only the DELETE statements are rolled back
• D. Only the seconds DELETE statement is rolled back
• E. No SQL statements are rolled back

Answer: BE

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

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

Answer: BE

NEW QUESTION 7
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 8
Which SQL statement displays the date March 19, 2001 in a format that appears as “Nineteenth of March 2001 12:00:00 AM”?

• A. SELECT TO_CHAR(TO_DATE('19-Mar-2001’, ‘DD-Mon-YYYY’), ‘fmDdspth “of” Month YYYY fmHH:MI:SS AM’) NEW_DATE FROM dual;
• B. SELECT TO_CHAR(TO_DATE(’19-Mar-2001’, ‘DD-Mon-YYYY’), ‘Ddspth “of” Month YYYY fmHH:MI:SS AM’) NEW_DATE FROM dual;
• C. SELECT TO_CHAR(TO_DATE(’19-Mar-2001’, ‘DD-Mon-YYYY’), ‘fmDdspth “of” Month YYYY HH:MI:SS AM’) NEW_DATE FROM dual;
• D. SELECT TO_CHAR(TO_DATE(’19-Mar-2001’, ‘DD-Mon-YYYY), ‘fmDdspth “of” Month YYYYfmtHH:HI:SS AM') NEW_DATE FROM dual;

Answer: A

NEW QUESTION 9
Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

• A. addition
• B. subtraction
• C. raising to a power
• D. finding the quotient
• E. finding the lowest value

Answer: ACE

NEW QUESTION 10
Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?

• A. SELECT last_name, department_name, location_id FROM employees , departments ;
• B. SELECT employees.last_name, departments.department_name, departments.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
• C. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id =manager_id;
• D. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;

Answer: D

Explanation:
Equijoins are also called simple joins or inner joins. Equijoin involve primary key and foreign key.
Incorrect Answer: Athere is no join B invalid syntax Cdoes not involve the join in the primary and foreign key
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-8

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

You want to display the category with the maximum number of items. You issue the following query:
SQL>SELECT COUNT(*),prod_category_id FROM products GROUP BY prod_category_id HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);
What is the outcome?

• A. It executes successfully and gives the correct outpu
• B. It executes successfully but does not give the correct outpu
• C. It generates an error because the subquery does not have a GROUP BY claus
• D. It generates an error because = is not valid and should be replaced by the IN operato

Answer: C

NEW QUESTION 12
Which object privileges can be granted on a view?

• A. none
• B. DELETE, INSERT,SELECT
• C. ALTER, DELETE, INSERT, SELECT
• D. DELETE, INSERT, SELECT, UPDATE

Answer: D

Explanation: Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE.
Incorrect Answer: AObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE BObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE CObject privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-12

NEW QUESTION 13
Which statement is true regarding the INTERSECT operator?

• A. It ignores NULL values
• B. The number of columns and data types must be identical for all SELECT statements in the query
• C. The names of columns in all SELECT statements must be identical
• D. Reversing the order of the intersected tables the result

Answer: B

Explanation:
INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and
removing duplicates.
The columns in the queries that make up a compound query can have different names, but
the output result set will use the names of the columns in the first query.

NEW QUESTION 14
View the Exhibit and examine the structure of the SALES table.
The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.

Which statement is true regarding this SQL statement?

• A. It executes successfully and generates the required resul
• B. It produces an error because COUNT(*) should be specified in the SELECT clause als
• C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE claus
• D. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).

Answer: C

Explanation:
Restricting Group Results with the HAVING Clause
You use the HAVING clause to specify the groups that are to be displayed, thus further
restricting the groups on the basis of aggregate information.
In the syntax, group_condition restricts the groups of rows returned to those groups for
which the specified condition is true.
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 groups that match the criteria in the HAVING clause are displayed.
The HAVING clause can precede the GROUP BY clause, but it is recommended that you
place the GROUP BY clause first because it is more logical. Groups are formed and group
functions are calculated before the HAVING clause is applied to the groups in the SELECT
list.
Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.

NEW QUESTION 15
The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL
CUSTOMER_NAME VARCHAR2(100) NOT NULL
CUSTOMER_ADDRESS VARCHAR2(150)
CUSTOMER_PHONE VARCHAR2(20)
You need to produce output that states "Dear Customer customer_name, ".
The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table.
Which statement produces this output?

• A. SELECT dear customer, customer_name, FROM customers;
• B. SELECT "Dear Customer", customer_name || ',' FROM customers;
• C. SELECT 'Dear Customer ' || customer_name ',' FROM customers;
• D. SELECT 'Dear Customer ' || customer_name || ',' FROM customers;
• E. SELECT "Dear Customer " || customer_name || "," FROM customers;
• F. SELECT 'Dear Customer ' || customer_name || ',' || FROM customers;

Answer: D

Explanation: Concatenation operator to create a resultant column that is a character expression.
Incorrect Answer: Ano such dear customer column Binvalid syntax Cinvalid syntax Einvalid syntax Finvalid syntax
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-18

NEW QUESTION 16
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 17
The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
Which statement finds the highest grade point average (GPA) per semester?

• A. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;
• B. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
• C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;
• D. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;
• E. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

Answer: C

Explanation: Explanation: For highest gpa value MAX function is needed, for result with per semester GROUP BY clause is needed
Incorrect Answer: Aper semester condition is not included Bresult would not display the highest gpa value Dinvalid syntax error Einvalid syntax error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7

NEW QUESTION 18
Which is the valid CREATE [TABLE statement?

• A. CREATE TABLE emp9$# (emp_no NUMBER(4));
• B. CREATE TABLE 9emp$# (emp_no NUMBER(4));
• C. CREATE TABLE emp*123 (emp_no NUMBER(4));
• D. CREATE TABLE emp9$# (emp_no NUMBER(4). date DATE);

Answer: A

Explanation:
Schema Object Naming Rules
Every database object has a name. In a SQL statement, you represent the name of an
object with a quoted identifier or a nonquoted identifier.
A quoted identifier begins and ends with double quotation marks ("). If you name a schema
object using a quoted identifier, then you must use the double quotation marks whenever
you refer to that object.
A nonquoted identifier is not surrounded by any punctuation.
The following list of rules applies to both quoted and nonquoted identifiers unless otherwise
indicated:
Names must be from 1 to 30 bytes long with these exceptions:
Names of databases are limited to 8 bytes.
Names of database links can be as long as 128 bytes.
If an identifier includes multiple parts separated by periods, then each attribute can be up to
30 bytes long.
Each period separator, as well as any surrounding double quotation marks, counts as one
byte. For example, suppose you identify a column like this:
"schema"."table"."column"
Nonquoted identifiers cannot be Oracle Database reserved words (ANSWER D). Quoted identifiers can be reserved words, although this is not recommended. Depending on the Oracle product you plan to use to access a database object, names might be further restricted by other product-specific reserved words. The Oracle SQL language contains other words that have special meanings. These words include datatypes, schema names, function names, the dummy system table DUAL, and keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT, ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them internally in specific ways. Therefore, if you use these words as names for objects and object parts, then your SQL statements may be more difficult to read and may lead to unpredictable results. In particular, do not use words beginning with SYS_ as schema object names, and do not use the names of SQL built-in functions for the names of schema objects or user-defined functions. You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems. Nonquoted identifiers must begin with an alphabetic character (ANSWER B - begins with 9) from your database character set. Quoted identifiers can begin with any character. Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers. Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (). Within a namespace, no two objects can have the same name. Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive. By enclosing names in double quotation marks, you can give the following names to different objects in the same namespace: employees "employees" "Employees" "EMPLOYEES"
Note that Oracle interprets the following names the same, so they cannot be used for different objects in the same namespace: employees EMPLOYEES "EMPLOYEES" Columns in the same table or view cannot have the same name. However, columns in different tables or views can have the same name. Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. Creating multiple procedures or functions with the same name in the same package with different arguments is called overloading the procedure or function.

NEW QUESTION 19
Which two statements are true about constraints? (Choose two.)

• A. The UNIQUE constraint does not permit a null value for the colum
• B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraint
• C. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE inde
• D. The NOT NULL constraint ensures that null values are not permitted for the colum

Answer: BD

Explanation:
B: A unique constraint can contain null values because null values cannot be compared to anything.
D: The NOT NULL constraint ensure that null value are not permitted for the column
Incorrect Answer: Astatement is not true Cstatement is not true
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-9