Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Which create table statement is valid?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Explanation:
PRIMARY KEY Constraint A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value. Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns.
Q2. You want to display 5 percent of the employees with the highest salaries in the EMPLOYEES table.
Which query will generate the required result?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q3. View the Exhibit and examine the structures of the employees and departments tables.
You want to update the employees table as follows:
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department_id corresponding to London (location_id 2100).
-Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission in iocation_id 2100 to 1.5 times the average commission of their department.
You issue the following command:
What is the outcome?
A. It executes successfully and gives the correct result.
B. It executes successfully but does not give the correct result.
C. It generates an error because a subquery cannot have a join condition in an update statement.
D. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an update statement.
Answer: B
Q4. Examine the structure of the employees table.
You want to display the maximum and minimum salaries of employees hired 1 year ago. Which two statements would get the correct output?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B,D
Explanation:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z1 0.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_subselectexamples.htm.
Q5. You need to generate a list of all customer last names with their credit limits from the customers table.
Those customers who do not have a credit limit should appear last in the list.
Which two queries would achieve the required result?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B,C
Explanation:
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. ANSWER C Sorting The default sort order is ascending:
. Numeric values are displayed with the lowest values first (for example, 1 to 999).
. Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01-JAN-95).
. Character values are displayed in the alphabetical order (for example, “A” first and “Z” last).
. Null values are displayed last for ascending sequences and first for descending sequences.
-ANSWER B
. You can also sort by a column that is not in the SELECT list.
Q6. Examine the structure of the customers table:
CUSTNO is the primary key in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names.
Which two methods can you use to get the required result?
A. Self-join
B. Subquery
C. Full outer-join with self-join
D. Left outer-join with self-join
E. Right outer-join with self-join
Answer: A,B
Q7. View the Exhibit and examine the structure of the customers table.
NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the customers table.
Evaluate the following insert statement: The insert statement fails when executed.
What could be the reason?
A. The values clause cannot be used in an INSERT with a subquery.
B. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.
C. The where clause cannot be used in a subquery embedded in an INSERT statement.
D. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table.
Answer: A
Explanation:
Copying Rows from Another Table
Write your INSERT statement with a subquery:
Do not use the VALUES clause.
Match the number of columns in the INSERT clause to those in the subquery.
Inserts all the rows returned by the subquery in the table, sales_reps.
Q8. 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
Q9. View the Exhibit and examine the structure of the CUSTOMERS table.
You have been asked to produce a report on the customers table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city.
Which query would accomplish this task?
A. Option A
B. Option B
C. Option C D. Option D
Answer: A
Q10. 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:
What happens when the above statement is executed?
A. The statement executes successfully and produces the required output.
B. The statement produces an error because item_cnt cannot be displayed in the outer query.
C. The statement produces an error because a subquery in the from clause and outer-joins cannot be used together.
D. The statement produces an error because the group by clause cannot be used in a subquery in the from clause.
Answer: A
Q11. Which statement is true regarding the default behavior of the order by clause?
A. In a character sort, the values are case-sensitive.
B. NULL values are not considered at all by the sort operation.
C. Only those columns that are specified in the select list can be used in the order by clause.
D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.
Answer: A
Explanation:
Character Strings and Dates
Character strings and date values are enclosed with single quotation marks.
Character values are case-sensitive and date values are format-sensitive.
The default date display format is DD-MON-RR.
Q12. In the customers table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'Abigail'.
Evaluate the following query:
What would be the outcome?
A. Abigail PA
B. Abigail Pa
C. Abigail IS
D. An error message
Answer: B
Q13. 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
Q14. Examine the data in the PROMO_BEGIN_DATE column of the promotions table:
You want to display the number of promotions started in 1999 and 2000. Which query gives the correct output?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Q15. Which two statements are true regarding constraints?
A. A foreign key cannot contain null values.
B. A column with the unique constraint can contain null values.
C. A constraint is enforced only for the insert operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All constraints can be defined at the column level as well as the table level.
Answer: B,D