Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 2)
Evaluate the following query:
SELECT INTERVAL '300' MONTH,
INTERVAL '54-2' YEAR TO MONTH,
INTERVAL '11:12:10.1234567' HOUR TO SECOND
FROM dual;
What is the correct output of the above query?
A. +25-00 , +54-02, +00 11:12:10.123457
B. +00-300, +54-02, +00 11:12:10.123457
C. +25-00 , +00-650, +00 11:12:10.123457
D. +00-300 , +00-650, +00 11:12:10.123457
Answer: A
Explanation:
Datetime Data Types You can use several datetime data types: INTERVAL YEAR TO MONTH Stored as an interval of years and months INTERVAL DAY TO SECOND Stored as an interval of days, hours, minutes, and seconds
Q2. - (Topic 2)
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
Q3. - (Topic 2)
Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. The column with a UNIQUE constraint can store NULLS .
C. A constraint is enforced only for an INSERT operation on a table.
D. You can have more than one column in a table as part of a primary key.
Answer: B,D
Q4. - (Topic 1)
Evaluate the following SQL statements: Exhibit:
You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization.
Exhibit:
Which two statements are true regarding the above view? (Choose two.)
A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table
B. It allows you to delete details of the existing sales staff from the EMPLOYEES table
C. It allows you to insert rows into the EMPLOYEES table
D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements
Answer: B,D
Q5. - (Topic 2)
Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?
A. DROP emp_dept_vu;
B. DELETE emp_dept_vu;
C. REMOVE emp_dept_vu;
D. DROP VIEW emp_dept_vu;
E. DELETE VIEW emp_dept_vu;
F. REMOVE VIEW emp_dept_vu;
Answer: D
Explanation:
DROP VIEW viewname;
Incorrect Answer: ANot a valid drop view statement BNot a valid drop view statement CNot a valid drop view statement ENot a valid drop view statement FNot a valid drop view statement
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-20
Q6. - (Topic 2)
View the Exhibits and examine the structures of the PRODUCTS and SALES tables. Which two SQL statements would give the same output? (Choose two.)
A.
SELECT prod_id FROM products INTERSECT SELECT prod_id FROM sales;
B.
SELECT prod_id FROM products MINUS SELECT prod_id FROM sales;
C.
SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id=s.prod_id;
D.
SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id <> s.prod_id;
Answer: A,C
Q7. - (Topic 2)
Which substitution variable would you use if you want to reuse the variable without prompting the user each time?
A. &
B. ACCEPT
C. PROMPT
D. &&
Answer: D
Explanation:
To reuse the variable without prompting the user each time you can use && substitution
variable.
Incorrect Answers
A:This substitution variable will prompt the user each time.
B:ACCEPT is command, not substitution variable. It used to define more accurate or
specific prompt or when you want more output to display as the values are defined.
C:PROMPT is part of the ACCEPT command, it is not a variable.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 165-173
Chapter 4: Sub queries
Q8. - (Topic 1)
View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.
The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?
A. INTERSECT
B. UNION ALL
C. MINUS
D. UNION
Answer: C
Q9. - (Topic 1)
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: B,C,D
Q10. - (Topic 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
Q11. - (Topic 1)
Evaluate the following SQL statements: Exhibit:
Which is the correct output of the above query?
A. +00-300, +54-02,+00 11:12:10.123457
B. +00-300,+00-650,+00 11:12:10.123457
C. +25-00, +54-02, +00 11:12:10.123457
D. +25-00,+00-650,+00 11:12:10.123457
Answer: C
Q12. - (Topic 1)
See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:
You issue the following query:
Which statement is true regarding the outcome of this query?
A. It produces an error because the NATURAL join can be used only with two tables
B. It produces an error because a column used in the NATURAL join cannot have a qualifier
C. It produces an error because all columns used in the NATURAL join should have a qualifier
D. It executes successfully
Answer: B
Explanation:
Creating Joins with the USING Clause
Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin.
The Natural JOIN USING Clause
The format of the syntax for the natural JOIN USING clause is as follows: SELECT table1.column, table2.column FROM table1 JOIN table2 USING (join_column1, join_column2…); While the pure natural join contains the NATURAL keyword in its syntax, the JOIN…USING syntax does not. An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on certain columns, and this format caters to this requirement.
Q13. - (Topic 1)
Which statements are correct regarding indexes? (Choose all that apply.)
A. For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated.
B. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.
C. A FOREIGN KEY constraint on a column in a table automatically creates a non unique key
D. When a table is dropped, the corresponding indexes are automatically dropped
Answer: A,B,D
Q14. - (Topic 1)
View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.
Evaluate the following SQL statements:
Which statement is true regarding the above compound query?
A. It shows products that have a cost recorded irrespective of sales
B. It shows products that were sold and have a cost recorded
C. It shows products that were sold but have no cost recorded
D. It reduces an error
Answer: C
Q15. - (Topic 1)
What is true regarding sub queries?
A. The inner query always sorts the results of the outer query
B. The outer query always sorts the results of the inner query
C. The outer query must return a value to the outer query
D. The inner query returns a value to the outer query
E. The inner query must always return a value or the outer query will give an error
Answer: D
Explanation: The inner query returns a value to the outer query. If the inner query does not return a value, the outer query does not return a result