NEW QUESTION 1
Which statement is true regarding synonyms?

• A. Synonyms can be created only for a table
• B. Synonyms are used to reference only those tables that are owned by another user
• C. The DROP SYNONYM statement removes the synonym and the table on which the synonym has been created becomes invalid
• D. A public synonym and a private synonym can exist with the same name for the same table

Answer: D

NEW QUESTION 2
Which two statements are true regarding sub queries? (Choose two.)

• A. A sub query can retrieve zero or more row
• B. Only two sub queries can be placed at one leve
• C. A sub query can be used only in SQL query statement
• D. A sub query can appeal* on either side of a comparison operato
• E. There is no limit on the number of sub query levels in the WHERE clause of a SELECT statemen

Answer: AD

NEW QUESTION 3
Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose all that apply.)

• A. The HAVING clause can be used with aggregate functions in subquerie
• B. The WHERE clause can be used to exclude rows after dividing them into group
• C. The WHERE clause can be used to exclude rows before dividing them into group
• D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the quer
• E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the tabl

Answer: AC

NEW QUESTION 4
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 Answer: Ais not single row attributes Efunctions can accept more than one argument, e.g NVL2
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-5

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

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

Answer: ABDE

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 6
You need to perform these tasks:
. Create and assign a MANAGER role to Blake and Clark . Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark
Which set of SQL statements achieves the desired results?

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

Answer: A

Explanation: Result of commands:

NEW QUESTION 7
Examine the structure of the TRANSACTIONS table:
Name Null Type
TRANS_ID NOT NULL NUMBER(3)
CUST_NAME VARCHAR2(30)
TRANS_DATE TIMESTAMP
TRANS_AMT NUMBER(10,2)
You want to display the date, time, and transaction amount of transactions that where done before 12 noon.
The value zero should be displayed for transactions where the transaction amount has not been entered.
Which query gives the required result?

• A. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), TO_CHAR(trans_amt,'$99999999D99') FROM transactions WHERE TO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 AND COALESCE(trans_amt,NULL)<>NULL;
• B. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), NVL(TO_CHAR(trans_amt,'$99999999D99'),0) FROM transactions WHERE TO_CHAR(trans_date,'hh24') < 12;
• C. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0) FROM transactions WHERE TO_DATE(trans_date,'hh24') < 12;
• D. SELECT TO_DATE (trans_date,'dd-mon-yyyy hh24:mi:ss'), NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'), 0) FROM transactions WHERE TO_DATE(trans_date,'hh24') < 12;

Answer: B

NEW QUESTION 8
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:
SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME"
FROM employees e ______________ employees m
ON e.manager_id = m.employee_id;
Which JOIN option can be used in the blank in the above query to get the required output?
Exhibit:

• A. only inner JOIN
• B. only FULL OUTER JOIN
• C. only LEFT OUTER JOIN
• D. only RIGHT OUTER JOIN

Answer: C

NEW QUESTION 9
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 customers WHERE INSTR(cust_name, ' ',1,2)<>0;
• B. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',-1,2)<>0;
• C. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,''),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',-1,-2)<>0;
• D. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-INSTR(cust_name,' '),'*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ',1,2)<>0 ;

Answer: AB

NEW QUESTION 10
View the Exhibit and examine the structure of the PROMOTIONS table.
Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on 'TV' and 'internet' that ended in the time interval 15th March '00 to 15th October '00.
Which two queries would give the required result? (Choose two.)

• A. SELECT promo_name, promo_cost FROM promotions WHERE promo_category IN ('TV', 'internet') AND promo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00';
• B. SELECT promo_name, promo_cost FROM promotions WHERE promo_category = 'TV' OR promo_category ='internet' AND promo_end_date >='15-MAR-00' OR promo_end_date <='15-OCT-00';
• C. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category BETWEEN 'TV' AND 'internet') AND (promo_end_date IN ('15-MAR-00','15-OCT-00'));
• D. SELECT promo_name, promo_cost FROM promotions WHERE (promo_category = 'TV' OR promo_category ='internet') AND (promo_end_date >='15-MAR-00' AND promo_end_date <='15-OCT-00');

Answer: AD

NEW QUESTION 11
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 12
See the Exhibit and Examine the structure of SALES and PROMOTIONS tables: Exhibit:

You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values.
Which DELETE statements are valid? (Choose all that apply.)

• A. DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale') AND promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'everyday low price');
• B. DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale') OR promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'everyday low price');
• C. DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale' OR promo_name = 'everyday low price');
• D. D DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name IN ('blowout sale','everyday low price'));

Answer: BCD

NEW QUESTION 13
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 14
The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4)
ENAME VARCHAR2 (25)
JOB_ID VARCHAR2(10)
Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"?

• A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';
• B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';
• C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = 'n';
• D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = 'n';

Answer: A

Explanation:
INSTR is a character function return the numeric position of a named string.
INSTR(NAMED,’a’)
Incorrect Answer:
BDid not return a numeric position for ‘a’.
CDid not return a numeric position for ‘a’.
DDid not return a numeric position for ‘a’.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-8

NEW QUESTION 15
The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
The registrar requested a report listing the students' grade point averages (GPA) sorted from highest grade point average to lowest.
Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar?

• A. SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC;
• B. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC;
• C. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa;
• D. SELECT student_id, gpa FROM student_grades ORDER BY gpa;
• E. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC;
• F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;

Answer: F

Explanation:
sorted by highest to lowest is DESCENDING order
Incorrect Answer: Aresult in ascending order Bwrong syntax with SORT keyword Cwrong syntax with SORT keyword Ddefault value for ORDER by is in ascending order Ewrong syntax with SORT keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22

NEW QUESTION 16
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 17
The PART_CODE column in the SPARES table contains the following list of values:

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

• A. It produces an erro
• B. It displays all value
• C. It displays only the values A%_WQ123 and AB_WQ123 .
• D. It displays only the values A%_WQ123 and A%BWQ123 .
• E. It displays only the values A%BWQ123 and AB_WQ123.

Answer: D

Explanation:
Combining Wildcard Characters
The % and _ symbols can be used in any combination with literal characters. The example in the slide displays the names of all employees whose last names have the letter “o” as the second character.
ESCAPE Identifier
When you need to have an exact match for the actual % and _ characters, use the ESCAPE identifier. This option specifies what the escape character is. If you want to search for strings that contain SA_, you can use the following SQL statement: SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_%' ESCAPE '';

NEW QUESTION 18
Examine the structure of the EMPLOYEES table:

Which INSERT statement is valid?

• A. INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ‘01/01/01’);
• B. INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ’01 January 01’);
• C. INSERT INTO employees(employee_id, first_name, last_name, Hire_date) VALUES ( 1000, ‘John’, ‘Smith’, To_date(‘01/01/01’));
• D. INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, 01-Jan-01);

Answer: D

Explanation: It is the only statement that has a valid date; all other will result in an error. Answer A is incorrect, syntax error, invalid date format

NEW QUESTION 19
You issue the following query:
SQL> SELECT AVG(MAX(qty))
FROM ord_items
GROUP BY item_no
HAVING AVG(MAX(qty))>50;
Which statement is true regarding the outcome of this query?

• A. It executes successfully and gives the correct outpu
• B. It gives an error because the HAVING clause is not vali
• C. It executes successfully but does not give the correct outpu
• D. It gives an error because the GROUP BY expression is not vali

Answer: B

Explanation:
The general form of the SELECT statement is further enhanced by the addition of the
HAVING clause and becomes:
SELECT column|expression|group_function(column|expression [alias]),…}
FROM table
[WHERE condition(s)]
[GROUP BY {col(s)|expr}]
[HAVING group_condition(s)]
[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];
An important difference between the HAVING clause and the other SELECT statement
clauses is that it may only be specified if a GROUP BY clause is present. This dependency
is sensible since group-level rows must exist before they can be restricted. The HAVING
clause can occur before the GROUP BY clause in the SELECT statement. However, it is
more common to place the HAVING clause after the GROUP BY clause. All grouping is
performed and group functions are executed prior to evaluating the HAVING clause.