Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 1)
View the Exhibit for the structure of the STUDENT and FACULTY tables.
You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements:
Which statement is true regarding the outcome?
A. Only statement 1 executes successfully and gives the required result.
B. Only statement 2 executes successfully and gives the required result.
C. Both statements 1 and 2 execute successfully and give different results.
D. Both statements 1 and 2 execute successfully and give the same required result.
Answer: D
Q2. - (Topic 1)
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: A,D
Q3. - (Topic 1)
You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit
Using the PROMOTIONS table, you need to display the names of all promos done after
January 1, 2001 starting with the latest promo.
Which query would give the required result? (Choose all that apply.)
A. SELECT promo_name,promo_begin_date
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY 1 DESC;
B. SELECT promo_name,promo_begin_date "START DATE"
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY "START DATE" DESC;
C. SELECT promo_name,promo_begin_date
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY 2 DESC;
D. SELECT promo_name,promo_begin_date
FROM promotions
WHERE promo_begin_date > '01-JAN-01'
ORDER BY promo_name DESC;
Answer: B,C
Q4. - (Topic 1)
The PRODUCTS table has the following structure:
Evaluate the following two SQL statements:
Which statement is true regarding the outcome?
A. Both the statements execute and give the same result
B. Both the statements execute and give different results
C. Only the second SQL statement executes successfully
D. Only the first SQL statement executes successfully
Answer: B
Explanation:
Using the NVL2 Function The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third expression is returned.
Syntax NVL2(expr1, expr2, expr3) In the syntax: expr1 is the source value or expression that may contain a null expr2 is the value that is returned if expr1 is not null expr3 is the value that is returned if expr1 is null
Q5. - (Topic 2)
A data manipulation language statement _____.
A. completes a transaction on a table
B. modifies the structure and data in a table
C. modifies the data but not the structure of a table
D. modifies the structure but not the data of a table
Answer: C
Explanation:
modifies the data but not the structure of a table
Incorrect Answer:
ADML does not complete a transaction
BDDL modifies the structure and data in the table
DDML does not modified table structure.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-3
Q6. - (Topic 2)
View the Exhibit and examine the structure of the SALES and PRODUCTS tables.
In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You want to list each product ID and the number of times it has been sold.
Evaluate the following query:
SQL>SELECT p.prod_id, COUNT(s.prod_id)
FROM products p _____________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN options can be used in the blank in the above query to get the required output? (Choose two.)
A. JOIN
B. FULL OUTER JOIN
C. LEFT OUTER JOIN
D. RIGHT OUTER JOIN
Answer: B,C
Q7. - (Topic 1)
The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query successfully?
SELECT * FROM ord;
A. CREATE SYNONYM ord FOR orders; This command is issued by OE.
B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by OE.
C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.
Answer: D
Explanation:
Creating a Synonym for an Object To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views. In the syntax: PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created Guidelines The object cannot be contained in a package. A private synonym name must be distinct from all other objects that are owned by the same user. If you try to execute the following command (alternative B, issued by OE):
Q8. - (Topic 1)
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
There is only one customer with the CUST_LAST_NAME column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?
A.
INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
B.
INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600),order_total)
VALUES(1,'10-mar-2007', 'direct', &&customer_id, 1000);
C.
INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' ANDc.credit_limit=600 ) VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
D.
INSERT INTO orders (order_id,order_date,order_mode,
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600),order_total)
VALUES(1,'10-mar-2007', 'direct', &customer_id, 1000);
Answer: A
Q9. - (Topic 2)
In which four clauses can a sub query be used? (Choose four.)
A. in the INTO clause of an INSERT statement
B. in the FROM clause of a SELECT statement
C. in the GROUP BY clause of a SELECT statement
D. in the WHERE clause of a SELECT statement
E. in the SET clause of an UPDATE statement
F. in the VALUES clause of an INSERT statement
Answer: A,B,D,E
Explanation:
A: a sub query is valid on the INTO clause of an ISERT Statement
B: a sub query can be used in the FROM clause of a SELECT statement
D: a sub query can be used in the WHERE clause of a SELECT statement,
E: a sub query can be used in the SET clauses of an UPDATE statement,
Incorrect Answer:
Csub query cannot be used
F: is incorrect.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 6-5
Q10. - (Topic 1)
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 error.
B. It displays all values.
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 '\'
Q11. - (Topic 2)
View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables.
SALE_VW is a view created using the following command:
SQL>CREATE VIEW sale_vw AS
SELECT prod_id, SUM(quantity_sold) QTY_SOLD
FROM sales GROUP BY prod_id;
You issue the following command to add a row to the SALE_SUMMARY table:
SQL>INSERT INTO sale_summary
SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products
USING (prod_id) WHERE prod_id = 16;
What is the outcome?
A. It executes successfully.
B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table.
C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match.
D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table.
Answer: D
Q12. - (Topic 1)
View the Exhibit to examine the description for the SALES table. Which views can have all DML operations performed on it? (Choose all that apply.)
A. CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id = 2034 WITH CHECK OPTION;
B. CREATE VIEW v1 AS SELECT * FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION;
C. CREATE VIEW v2 AS SELECT prod_id, cust_id, time_id FROM SALES WHERE time_id <= SYSDATE - 2*365 WITH CHECK OPTION;
D. CREATE VIEW v4 AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES WHERE time_id <= SYSDATE - 2*365 GROUP BY prod_id, cust_id WITH CHECK OPTION;
Answer: A,B
Explanation:
Creating a View You can create a view by embedding a subquery in the CREATE VIEW statement. In the syntax: CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; OR REPLACE Re-creates the view if it already exists FORCE Creates the view regardless of whether or not the base tables exist NOFORCE Creates the view only if the base tables exist (This is the default.) View Is the name of the view alias Specifies names for the expressions selected by the view’s query (The number of aliases must match the number of expressions selected by the view.) subquery Is a complete SELECT statement (You can use aliases for the columns in the SELECT list.) WITH CHECK OPTION Specifies that only those rows that are accessible to the view can be inserted or updated ANSWER D constraint Is the name assigned to the CHECK OPTION constraint WITH READ ONLY Ensures that no DML operations can be performed on this view Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view – ANSWER C
Q13. - (Topic 2)
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
Q14. - (Topic 1)
Which two statements are true regarding sub queries? (Choose two.)
A. A sub query can retrieve zero or more rows.
B. Only two sub queries can be placed at one level.
C. A sub query can be used only in SQL query statements.
D. A sub query can appeal* on either side of a comparison operator.
E. There is no limit on the number of sub query levels in the WHERE clause of a SELECT statement.
Answer: A,D
Q15. - (Topic 2)
Which two statements about creating constraints are true? (Choose two)
A. Constraint names must start with SYS_C.
B. All constraints must be defines at the column level.
C. Constraints can be created after the table is created.
D. Constraints can be created at the same time the table is created.
E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view.
Answer: C,D
Explanation:
Constraints can be created after the table is created. Use ALTER TABLE command for
that.
Constraints can be created at the same time the table is created (CREATE TABLE
command).
Incorrect Answers
A:There is no requirements in Oracle that constraint names must start with SYS_C. Oracle
can use prefix “SYS” to build indexes for UNIQUE and NOT NULL constraints, but it is not
required for user to follow this naming rule.
B:Not all constraints must be defines at the column level. Only NOT NULL constraint must
be.
E:There is no VIEW_CONSTRAINTS dictionary view in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232
Chapter 5: Creating Oracle Database Objects