NEW QUESTION 1
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 2
You work as a database administrator at ABC.com. You study the exhibit carefully.
Exhibit:

and examine the structure of CUSTOMRS AND SALES tables:
Evaluate the following SQL statement:
Exhibit:

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

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

Answer: B

NEW QUESTION 3
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 4
Examine the description of the EMPLOYEES table:
EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees with last name Smith?

• A. SELECT COUNT(*) FROM employees WHERE last_name='Smith';
• B. SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith';
• C. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';
• D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';
• E. SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';

Answer: D

NEW QUESTION 5
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 6
View the Exhibit and examine the structure of the PRODUCTS table.
You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this
condition.
Evaluate the following SQL statement:
SQL>SELECT prod_name,prod_list_price FROM products WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output?
(Choose all that apply.)

• A. ORDER BY prod_list_price DESC, prod_name;
• B. ORDER BY (2*prod_min_price)DESC, prod_name;
• C. ORDER BY prod_name, (2*prod_min_price)DESC;
• D. ORDER BY prod_name DESC, prod_list_price DESC;
• E. ORDER BY prod_list_price DESC, prod_name DESC;

Answer: AE

Explanation:
Using the ORDER BY Clause The order of rows that are returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. However, if you use the ORDER BY clause, it must be the last clause of the SQL statement. Further, you can specify an expression, an alias, or a column position as the sort condition. Syntax SELECT expr FROM table [WHERE condition(s)] [ORDER BY {column, expr, numeric_position} [ASC|DESC]]; In the syntax: ORDER BY specifies the order in which the retrieved rows are displayed ASC orders the rows in ascending order (This is the default order.)
DESC orders the rows in descending order If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order. Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence.

NEW QUESTION 7
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 8
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

• A. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
• B. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
• C. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
• D. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;
• E. SELECT TO_CHAR(1890.55,'$9,999D99') FROM DUAL;

Answer: ACD

NEW QUESTION 9
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 10
The COMMISSION column shows the monthly commission earned by the employee. Exhibit

Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)

• A. listing the employees who earn the same amount of commission as employee 3
• B. finding the total commission earned by the employees in department 10
• C. finding the number of employees who earn a commission that is higher than the average commission of the company
• D. listing the departments whose average commission is more that 600
• E. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
• F. listing the employees whose annual commission is more than 6000

Answer: AC

NEW QUESTION 11
Examine the structure of the EMPLOYEES table:

Which UPDATE statement is valid?

• A. UPDATE employees SET first_name = ‘John’ SET last_name = ‘Smith’ WHERE employee_id = 180;
• B. UPDATE employees SET first_name = ‘John’, SET last_name = ‘Smoth’ WHERE employee_id = 180;
• C. UPDATE employee SET first_name = ‘John’ AND last_name = ‘Smith’ WHERE employee_id = 180;
• D. UPDATE employee SET first_name = ‘John’, last_name = ‘Smith’ WHERE employee_id = 180;

Answer: D

NEW QUESTION 12
Evaluate the following two queries: Exhibit:

Exhibit:

Which statement is true regarding the above two queries?

• A. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column
• B. Performance would degrade in query 2
• C. There would be no change in performance
• D. Performance would improve in query 2

Answer: C

Explanation:
Note: The IN operator is internally evaluated by the Oracle server as a set of OR conditions, such as a=value1 or a=value2 or a=value3. Therefore, using the IN operator
has no performance benefits and is used only for logical simplicity.

NEW QUESTION 13
Examine the structure of the TRANSACTIONS table:
Name Null Type
TRANS_ID NOT NULL NUMBER(3)
CUST_NAME VARCHAR2(30) TRANS_DATE DATE TRANS_AMT NUMBER(10,2)
You want to display the transaction date and specify whether it is a weekday or weekend. Evaluate the following two queries:

Which statement is true regarding the above queries?

• A. Both give wrong result
• B. Both give the correct resul
• C. Only the first query gives the correct resul
• D. Only the second query gives the correct resul

Answer: C

Explanation:
Range Conditions Using the BETWEEN Operator Use the BETWEEN operator to display rows based on a range of values: SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500; Range Conditions Using the BETWEEN Operator You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower limit and an upper limit. The SELECT statement in the slide returns rows from the EMPLOYEES table for any employee whose salary is between $2,500 and $3,500. Values that are specified with the BETWEEN operator are inclusive. However, you must specify the lower limit first. You can also use the BETWEEN operator on character values: SELECT last_name FROM employees WHERE last_name BETWEEN 'King' AND 'Smith';

NEW QUESTION 14
View the Exhibits and examine PRODUCTS and SALES tables.

You issue the following query to display product name and the number of times the product has been sold:
SQL>SELECT p.prod_name, i.item_cnt FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i RIGHT OUTER JOIN products p
ON i.prod_id = p.prod_id;
What happens when the above statement is executed?

• A. The statement executes successfully and produces the required outpu
• B. The statement produces an error because ITEM_CNT cannot be displayed in the outer quer
• C. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used togethe
• D. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM claus

Answer: A

NEW QUESTION 15
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 16
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 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