Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 1)
Evaluate the following SQL statements: Exhibit:
Exhibit:
The above command fails when executed. What could be the reason?
A. The BETWEEN clause cannot be used for the CHECK constraint
B. SYSDATE cannot be used with the CHECK constraint
C. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY
D. The CHECK constraint cannot be placed on columns having the DATE data type
Answer: B
Explanation:
CHECK Constraint The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level. CREATE TABLE employees (... salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary > 0),
Q2. - (Topic 2)
Examine the following SQL commands:
Which statement is true regarding the execution of the above SQL commands?
A. Both commands execute successfully.
B. The first CREATE TABLE command generates an error because the NULL constraint is not valid.
C. The second CREATE TABLE command generates an error because the CHECK constraint is not valid.
D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column.
E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints.
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.
Q3. - (Topic 1)
View the Exhibit and examine the structure of the CUSTOMERS table. Exhibit:
you issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name 'king' and whose credit limit is less than the maximum credit limit in countries that have customers with the last name 'king'.
Which statement is true regarding the outcome of the above query?
A. It produces an error and the < operator should be replaced by < ANY to get the required output
B. It produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to get the required output
C. It executes and shows the required result
D. It produces an error and the < operator should be replaced by < ALL to get the required output
Answer: C
Q4. - (Topic 2)
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 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
Q5. - (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
Q6. - (Topic 1)
View the Exhibit and examine the data in the COSTS table.
You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query:
Which statement is true regarding the above query?
A. It executes and produces the required result.
B. It produces an error because an expression cannot be used in the ORDER BY clause.
C. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.
D. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause.
Answer: A
Q7. - (Topic 1)
View the Exhibit and examine the structure of the PROMOTIONS table. Which SQL statements are valid? (Choose all that apply.)
A. SELECT promo_id. DECODE(NVL(promo_cost.O).promo_cost * 0.25. 100) "Discount"
FROM promotions;
B. SELECT promo id. DECODE(promo_cost. 10000.
DECODE(promo_category. 'Gl\ promo_cost * 25. NULL). NULL) "Catcost" FROM
promotions;
C. SELECT promo_id. DECODE(NULLIF(promo_cost. 10000). NULL. promo_cost*.25,
*N/A') "Catcost"
FROM promotions;
D. SELECT promo_id. DECODE(promo_cost. >10000. 'High'. <10000. 'Low')
"Range"FROM promotions;
Answer: A,B
Explanation:
Note: there are some syntax issues in this question.
Q8. - (Topic 1)
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: A,C,D
Q9. - (Topic 2)
The user Sue issues this SQL statement:
GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION;
The user Alice issues this SQL statement:
GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION;
The user Reena issues this SQL statement:
GRANT SELECT ON sue.EMP TO timber;
The user Sue issues this SQL statement:
REVOKE select on sue.EMP FROM alice;
For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?
A. Alice only
B. Alice and Reena
C. Alice, Reena, and Timber
D. Sue, Alice, Reena, and Timber
Answer: C
Explanation: use the REVOKE statement to revoke privileges granted to other users. Privilege granted to others through the WITH GRANT OPTION clause are also revoked. Alice, Reena and Timber will be revoke.
Incorrect Answer: Athe correct answer should be Alice, Reena and Timber Bthe correct answer should be Alice, Reena and Timber Dthe correct answer should be Alice, Reena and Timber
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-17
Q10. - (Topic 1)
You need to calculate the number of days from 1st Jan 2007 till date:
Dates are stored in the default format of dd-mm-rr.
Which two SQL statements would give the required output? (Choose two.)
A. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
B. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;
C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL
D. SELECT SYSDATE - '01-JAN-2007' FROM DUAL
E. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;
Answer: A,C
Q11. - (Topic 2)
Which two statements are true regarding subqueries? (Choose two.)
A. A subquery can retrieve zero or more rows.
B. Only two subqueries can be placed at one level.
C. A subquery can be used only in SQL query statements.
D. A subquery can appear on either side of a comparison operator.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.
Answer: A,D
Explanation:
Using a Subquery to Solve a Problem Suppose you want to write a query to find out who earns a salary greater than Abel’s salary. To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount. You can solve this problem by combining the two queries, placing one query inside the other query. The inner query (or subquery) returns a value that is used by the outer query (or main query). Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query. Subquery Syntax A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself. You can place the subquery in a number of SQL clauses, including the following: WHERE clause HAVING clause FROM clause In the syntax: operator includes a comparison condition such as >, =, or IN Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) and multiple-row operators (IN, ANY, ALL, EXISTS). The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query. Guidelines for Using Subqueries Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition for readability. (However, the subquery can appear on either side of the comparison operator.) Use single-row operators with single-row subqueries and multiple-row operators with multiple-row subqueries.
Subqueries can be nested to an unlimited depth in a FROM clause but to “only” 255 levels in a WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.
Q12. - (Topic 2)
View the Exhibit and examine the data in the PROJ_TASK_DETAILS table.
The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relation between them.
The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of any other tasks.
You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and the name of the employee in charge of the task it depends on.
Which query would give the required result?
A.
SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.based_on = d.task_id);
B.
SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on =
d.task_id);
C.
SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on =
d.task_id);
D.
SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.task_id = d.task_id);
Answer: B
Q13. - (Topic 2)
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements insert a row into the table? (Choose three.)
A. INSERT INTO employees VALUES ( NULL, 'John', 'Smith');
B. INSERT INTO employees( first_name, last_name) VALUES( 'John', 'Smith');
C. INSERT INTO employees VALUES ( 1000, 'John', NULL);
D. INSERT INTO employees (first_name, last_name, employee_id) VALUES ( 1000, 'John', 'Smith');
E. INSERT INTO employees (employee_id) VALUES (1000);
F. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'John', ' ');
Answer: C,E,F
Explanation: EMPLOYEE_ID is a primary key. Incorrect Answer: AEMPLOYEE_ID cannot be null BEMPLOYEE_ID cannot be null Dmismatch of field_name with datatype
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-11
Q14. - (Topic 2)
What is true about the WITH GRANT OPTION clause?
A. It allows a grantee DBA privileges.
B. It is required syntax for object privileges.
C. It allows privileges on specified columns of tables.
D. It is used to grant an object privilege on a foreign key column.
E. It allows the grantee to grant object privileges to other users and roles.
Answer: E Explanation:
The GRANT command with the WITH GRANT OPTION clause allows the grantee to grant
object privileges to other users and roles.
Incorrect Answers
A:The WITH GRANT OPTION does not allow a grantee DBA privileges.
B:It is not required syntax for object privileges. It is optional clause of GRANT command.
C:GRANT command does not allows privileges on columns of tables.
D:It is not used to grant an object privilege on a foreign key column.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access in Oracle
Q15. - (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