NEW QUESTION 1
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 2
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 3
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. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products);
• B. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);
• C. SELECT prod_name FROM products GROUP BY prod_name HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);
• D. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand)) FROM products) GROUP BY prod_name;

Answer: A

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

You want to generate a report showing the total compensation paid to each employee to date.
You issue the following query:

What is the outcome?

• A. It generates an error because the alias is not vali
• B. It executes successfully and gives the correct outpu
• C. It executes successfully but does not give the correct outpu
• D. It generates an error because the usage of the ROUND function in the expression is not vali
• E. It generates an error because the concatenation operator can be used to combine only two item

Answer: C

Explanation:
ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.)

NEW QUESTION 5
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 6
See the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:

Using the PROMOTIONS table, you need to find out the average cost for all promos in the
range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
Exhibit:

What would be the outcome?

• A. It generates an error because multiple conditions cannot be specified for the WHEN clause
• B. It executes successfully and gives the required result
• C. It generates an error because CASE cannot be used with group functions
• D. It generates an error because NULL cannot be specified as a return value

Answer: B

Explanation:
CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END

NEW QUESTION 7
The STUDENT_GRADES table has these columns:

Which statement finds students who have a grade point average (GPA) greater than 3.0 for the calendar year 2001?

• A. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ OR gpa > 3.;
• B. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ AND gpa gt 3.0;
• C. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ AND gpa > 3.0;
• D. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN ’01-JAN-2001’ AND ’31-DEC-2001’ OR gpa > 3.0;
• E. SELECT student_id, gpa FROM student_grades WHERE semester_end > ’01-JAN-2001’ OR semester_end < ’31-DEC-2001’ AND gpa >= 3.0;

Answer: C

NEW QUESTION 8
Examine the following SQL commands:

Which statement is true regarding the execution of the above SQL commands?

• A. Both commands execute successfull
• B. The first CREATE TABLE command generates an error because the NULL constraint is not vali
• C. The second CREATE TABLE command generates an error because the CHECK constraint is not vali
• D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same colum
• E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraint

Answer: B

Explanation:
Defining Constraints The slide gives the syntax for defining constraints when creating a table. You can create
constraints at either the column level or table level. Constraints defined at the column level
are included when the column is defined. Table-level constraints are defined at the end of
the table definition and must refer to the column or columns on which the constraint
pertains in a set of parentheses. It is mainly the syntax that differentiates the two;
otherwise, functionally, a columnlevel constraint is the same as a table-level constraint.
NOT NULL constraints must be defined at the column level.
Constraints that apply to more than one column must be defined at the table level.

NEW QUESTION 9
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 10
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 11
Which statement is true regarding the UNION operator?

• A. The number of columns selected in all SELECT statements need to be the same
• B. Names of all columns must be identical across all SELECT statements
• C. By default, the output is not sorted
• D. NULL values are not ignored during duplicate checking

Answer: A

Explanation:
The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.

NEW QUESTION 12
View the Exhibit and examine the structure of the PROMOTIONS table.

Evaluate the following SQL statement:
SQL>SELECT promo_name,CASE
WHEN promo_cost >=(SELECT AVG(promo_cost)
FROM promotions
WHERE promo_category='TV')
then 'HIGH'
else 'LOW'
END COST_REMARK
FROM promotions;
Which statement is true regarding the outcome of the above query?

• A. It shows COST_REMARK for all the promos in the tabl
• B. It produces an error because the subquery gives an erro
• C. It shows COST_REMARK for all the promos in the promo category 'TV'.
• D. It produces an error because subqueries cannot be used with the CASE expressio

Answer: A

NEW QUESTION 13
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 14
Examine the structure of the MARKS table:
Exhibit:

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

• A. SELECT student_name,subject1 FROM marks WHERE subject1 > AVG(subject1);
• B. SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE 'R%';
• C. SELECT SUM(subject1+subject2+subject3) FROM marks WHERE student_name IS NULL;
• D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) FROM marks WHERE subject1 > subject2;

Answer: CD

NEW QUESTION 15
Evaluate these two SQL statements:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC;
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;
What is true about them?

• A. The two statements produce identical result
• B. The second statement returns a syntax erro
• C. There is no need to specify DESC because the results are sorted in descending order by defaul
• D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statemen

Answer: A

Explanation: Explanation: the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.
Incorrect Answer: Bthere is no syntax error Cresult are sorted in ascending order by default DORDER BY 2 will take the second column as sorting column. Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22

NEW QUESTION 16
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 17
Examine the data in the CUSTOMERS table:

You want to list all cities that have more than one customer along with the customer details. Evaluate the following query:
SQL>SELECT c1.custname, c1.city FROM Customers c1 __________________ Customers c2 ON (c1.city=c2.city AND c1.custname<>c2.custname);
Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)

• A. JOIN
• B. NATURAL JOIN
• C. LEFT OUTER JOIN
• D. FULL OUTER JOIN
• E. RIGHT OUTER JOIN

Answer: AE