Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 1)
View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.
You need to generate a report showing the promo name along with the customer name for all products that were sold during their promo campaign and before 30th October 2007.
You issue the following query:
Which statement is true regarding the above query?
A. It executes successfully and gives the required result.
B. It executes successfully but does not give the required result.
C. It produces an error because the join order of the tables is incorrect.
D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECT statement.
Answer: B
Q2. - (Topic 1)
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
Q3. - (Topic 2)
Which two statements are true regarding savepoints? (Choose two.)
A. Savepoints are effective only for COMMIT.
B. Savepoints may be used to ROLLBACK.
C. Savepoints can be used for only DML statements.
D. Savepoints are effective for both COMMIT and ROLLBACK.
E. Savepoints can be used for both DML and DDL statements.
Answer: B,C
Q4. - (Topic 1)
See the Exhibit and examine the structure of ORD table: Exhibit:
Evaluate the following SQL statements that are executed in a user session in the specified order:
CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval
FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, ’25-jan-2007,101);
UPDATE ord
SET ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?
A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.
B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.
D. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.
Answer: A
Q5. - (Topic 2)
You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table.
Which set of statements accomplishes this?
A. CREATE ROLE registrar; GRANT MODIFY ON student_grades TO registrar; GRANT registrar to user1, user2, user3
B. CREATE NEW ROLE registrar; GRANT ALL ON student_grades TO registrar; GRANT registrar to user1, user2, user3
C. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT ROLE registrar to user1, user2, user3
D. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;
E. CREATE registrar; GRANT CHANGE ON student_grades TO registrar; GRANT registrar;
Answer: D
Explanation:
this is the correct solution for the answer. GRANT role_name to users;
Incorrect Answer: Athere is no such MODIFY keyword Binvalid CREATE command, there is no such NEW keyword Cinvalid GRANT command, there is no such ROLE keyword Einvalid GRANT command, there is no such CHANGE keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-10
Q6. - (Topic 2)
View the Exhibit and examine the data in the EMPLOYEES table:
You want to display all the employee names and their corresponding manager names.
Evaluate the following query:
SQL> SELECT e.employee_name "EMP NAME", m.employee_name "MGR NAME"
FROM employees e ______________ employees m
ON e.manager_id = m.employee_id;
Which JOIN option can be used in the blank in the above query to get the required output?
Exhibit:
A. only inner JOIN
B. only FULL OUTER JOIN
C. only LEFT OUTER JOIN
D. only RIGHT OUTER JOIN
Answer: C
Q7. - (Topic 1)
You need to create a table with the following column specifications:
1.
Employee ID (numeric data type) for each employee
2.
Employee Name (character data type) that stores the employee name
3.
Hire date, which stores the date of joining the organization for each employee
4.
Status (character data type), that contains the value 'ACTIVE' if no data is entered
5.
Resume (character large object [CLOB] data type), which contains the resume submitted by the employee
Which is the correct syntax to create this table?
A. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
e_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB(200));
B. CREATE TABLE 1_EMP
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB);
C. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT "ACTIVE",
resume CLOB);
D. CREATE TABLE EMP_1
(emp_id NUMBER,
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT 'ACTIVE',
resume CLOB);
Answer: D
Explanation:
CLOB Character data (up to 4 GB)
NUMBER [(p,s)] Number having precision p and scale s (Precision is the total number of
decimal digits and scale is the number of digits to the right of the decimal point; precision
can range from 1 to 38, and scale can range from –84 to 127.)
Q8. - (Topic 2)
Examine the structure of the PROMOS table:
You want to display the list of promo names with the message 'Same Day' for promos that started and ended on the same day.
Which query gives the correct output?
A. SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day')
FROM promos;
B. SELECT promo_name, NVL(TRUNC(promo_end_date - promo_start_date), 'Same
Day') FROM promos;
C. SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)),
NULL,'Same Day')
FROM promos;
D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)),
NULL,'Same day') FROM promos;
Answer: D
Explanation:
The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned ANSWER A - date and String incompatibl;a datatypes for NVL function The Date TRUNC Function The date TRUNC function performs a truncation operation on a date value based on a specified date precision format. The date TRUNC function takes one mandatory and one optional parameter. Its syntax is TRUNC(source date, [date precision format]). The source date parameter represents any value that can be implicitly converted into a date item. The date precision format parameter specifies the degree of truncation and is optional. If it is absent, the default degree of truncation is day. This means that any time component
Q9. - (Topic 1)
You need to create a table for a banking application. One of the columns in the table has the following requirements:
You want a column in the table to store the duration of the credit period
The data in the column should be stored in a format such that it can be easily added and subtracted with DATE data type without using conversion
The maximum period of the credit provision in the application is 30 days
the interest has to be calculated for the number of days an individual has taken a credit for
Which data type would you use for such a column in the table?
A. INTERVAL YEAR TO MONTH
B. NUMBER
C. TIMESTAMP
D. DATE
E. INTERVAL DAY TO SECOND
Answer: E
Q10. - (Topic 2)
You need to create a table named ORDERS that contain four columns:
1.
an ORDER_ID column of number data type
2.
a CUSTOMER_ID column of number data type
3.
an ORDER_STATUS column that contains a character data type
4.
a DATE_ORDERED column to contain the date the order was placed.
When a row is inserted into the table, if no value is provided when the order was placed, today’s date should be used instead.
Which statement accomplishes this?
A. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);
B. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);
C. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);
D. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);
E. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE = SYSDATE);
F. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE DEFAULT SYSDATE);
Answer: B
Explanation: Requirement that Order_Status should be a character data type
Not E: Order_status must be a character data type. There is also a syntax error.
Q11. - (Topic 2)
What does the FORCE option for creating a view do?
A. creates a view with constraints
B. creates a view even if the underlying parent table has constraints
C. creates a view in another schema even if you don't have privileges
D. creates a view regardless of whether or not the base tables exist
Answer: D
Explanation:
create a view regardless of whether or not the base tables exist.
Incorrect Answer: Athe option is not valid Bthe option is not valid Cthe option is not valid
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-3
Q12. - (Topic 1)
Which two are true about aggregate functions? (Choose two.)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.
C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
D. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.
E. You can use aggregate functions on a table, only by grouping the whole table as one single group.
F. You cannot group the rows of a table by more than one column while using aggregate functions.
Answer: A,D
Q13. - (Topic 2)
View the exhibit and examine the description for the SALES and CHANNELS tables.
You issued the following SQL statement to insert a row in the SALES table:
INSERT INTO sales VALUES (23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel_desc='Direct Sales'), 12, 1, 500);
Which statement is true regarding the execution of the above statement?
A. The statement will execute and the new row will be inserted in the SALES table.
B. The statement will fail because subquery cannot be used in the VALUES clause.
C. The statement will fail because the VALUES clause is not required with subquery.
D. The statement will fail because subquery in the VALUES clause is not enclosed with in single quotation marks.
Answer: A
Q14. - (Topic 2)
Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?
A. SELECT last_name, department_name, location_id FROM employees , departments ;
B. SELECT employees.last_name, departments.department_name,
departments.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
C. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id =manager_id;
D. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;
Answer: D
Explanation:
Equijoins are also called simple joins or inner joins. Equijoin involve primary key and foreign key.
Incorrect Answer: Athere is no join B invalid syntax Cdoes not involve the join in the primary and foreign key
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-8
Q15. - (Topic 1)
Evaluate the following SQL statement:
SQL> SELECT cust_id. cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city='Singapore'):
Which statement is true regarding the above query if one of the values generated by the sub query is NULL?
A. It produces an error.
B. It executes but returns no rows.
C. It generates output for NULL as well as the other values produced by the sub query.
D. It ignores the NULL value and generates output for the other values produced by the sub query.
Answer: C