Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 2)
The STUDENT_GRADES table has these columns:
STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
The registrar requested a report listing the students' grade point averages (GPA) sorted from highest grade point average to lowest.
Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar?
A. SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC;
B. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC;
C. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa;
D. SELECT student_id, gpa FROM student_grades ORDER BY gpa;
E. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC;
F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;
Answer: F
Explanation:
sorted by highest to lowest is DESCENDING order
Incorrect Answer: Aresult in ascending order Bwrong syntax with SORT keyword Cwrong syntax with SORT keyword Ddefault value for ORDER by is in ascending order Ewrong syntax with SORT keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 2-22
Q2. - (Topic 2)
Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose all that apply.)
A. The HAVING clause can be used with aggregate functions in subqueries.
B. The WHERE clause can be used to exclude rows after dividing them into groups.
C. The WHERE clause can be used to exclude rows before dividing them into groups.
D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
E. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
Answer: A,C
Q3. - (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
Q4. - (Topic 2)
Which statements are true regarding single row functions? (Choose all that apply.)
A. MOD : returns the quotient of a division
B. TRUNC : can be used with NUMBER and DATE values
C. CONCAT : can be used to combine any number of values
D. SYSDATE : returns the database server current date and time
E. INSTR : can be used to find only the first occurrence of a character in a string
F. TRIM : can be used to remove all the occurrences of a character from a string
Answer: B,D
Explanation:
ROUND: Rounds value to a specified decimal TRUNC: Truncates value to a specified decimal MOD: Returns remainder of division SYSDATE is a date function that returns the current database server date and time.
Date-Manipulation Functions
Date functions operate on Oracle dates. All date functions return a value of the DATE data type except MONTHS_BETWEEN, which returns a numeric value. MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month. ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative. NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string. LAST_DAY(date): Finds the date of the last day of the month that contains date The above list is a subset of the available date functions. ROUND and TRUNC number functions can also be used to manipulate the date values as shown below: ROUND(date[,'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day. TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.
The CONCAT Function
The CONCAT function joins two character literals, columns, or expressions to yield one larger character expression. Numeric and date literals are implicitly cast as characters when they occur as parameters to the CONCAT function. Numeric or date expressions are evaluated before being converted to strings ready to be concatenated. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or expressions resulting in character values. The INSTR(source string, search item, [start position],[nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins: instr('http://www.domain.com','.',1,2) = 18 The TRIM function literally trims off leading or trailing (or both) character strings from a given source string:
Q5. - (Topic 2)
View the Exhibit and examine the structure and data in the INVOICE table.
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT AVG(inv_date ) FROM invoice;
B. SELECT MAX(inv_date),MIN(cust_id) FROM invoice;
C. SELECT MAX(AVG(SYSDATE - inv_date)) FROM invoice;
D. SELECT AVG( inv_date - SYSDATE), AVG(inv_amt) FROM invoice;
Answer: B,D
Explanation:
Using the AVG and SUM Functions You can use the AVG, SUM, MIN, and MAX functions against the columns that can store numeric data. The example in the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives Using the MIN and MAX Functions You can use the MAX and MIN functions for numeric, character, and date data types. The example in the slide displays the most junior and most senior employees.
Q6. - (Topic 1)
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID NUMBER\ SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of
theDEPARTMENTS table
You created a sequence called EMP_ID_SEQ in order to populate sequential values for the EMPLOYEE_ID column of the EMPLOYEES table.
Which two statements regarding the EMP_ID_SEQ sequence are true? (Choose two.)
A. You cannot use the EMP_ID_SEQ sequence to populate the JOB_ID column.
B. The EMP_ID_SEQ sequence is invalidated when you modify the EMPLOYEE_ID column.
C. The EMP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES table.
D. Any other column of NUMBER data type in your schema can use the EMP_ID_SEQ sequence.
E. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEES table.
F. The EMP_ID_SEQ sequence is dropped automatically when you drop the EMPLOYEE_ID column.
Answer: C,D
Explanation: the EMP_ID_SEQ sequence is not affected by modification to the
EMPLOYEES table. Any other column of NUMBER data type in your schema can use the
EMP_ID_SEQ sequence.
Incorrect Answer:
AEMP_ID_SEQ sequence can be use to populate JOB_ID
BEMP_ID_SEQ sequence will not be invalidate when column in EMPLOYEE_ID is modify.
EEMP_ID_SEQ sequence will be dropped automatically when you drop the EMPLOYEES
table.
FEMP_ID_SEQ sequence will be dropped automatically when you drop the
EMPLOYEE_ID column.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 12-4
Q7. - (Topic 1)
Evaluate the following two queries: Exhibit:
Exhibit:
Which statement is true regarding the above two queries?
A. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column
B. Performance would degrade in query 2
C. There would be no change in performance
D. Performance would improve in query 2
Answer: C
Explanation:
Note: The IN operator is internally evaluated by the Oracle server as a set of OR conditions, such as a=value1 or a=value2 or a=value3. Therefore, using the IN operator
has no performance benefits and is used only for logical simplicity.
Q8. - (Topic 2)
Which statement describes the ROWID data type?
A. Binary data up to 4 gigabytes.
B. Character data up to 4 gigabytes.
C. Raw binary data of variable length up to 2 gigabytes.
D. Binary data stored in an external file, up to 4 gigabytes.
E. A hexadecimal string representing the unique address of a row in its table.
Answer: E
Explanation:
The ROWID datatype stores information related to the disk location of table rows. They
also uniquely identify the rows in your table. The ROWID datatype is stored as a
hexadecimal string.
Incorrect Answers
A:It is not a binary data. The ROWID datatype is a hexadecimal string.
B:It is not a character data. The ROWID datatype is a hexadecimal string.
C:It is not a raw binary data. The ROWID datatype is a hexadecimal string.
D:It is not binary data stored in an external file. The ROWID datatype is a hexadecimal
string.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 216
Chapter 5: Creating Oracle Database Objects
Q9. - (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.)
Q10. - (Topic 1)
Which two statements are true about sequences created in a single instance database? (Choose two.)
A. CURRVAL is used to refer to the last sequence number that has been generated
B. DELETE <sequencename> would remove a sequence from the database
C. The numbers generated by a sequence can be used only for one table
D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement
E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted
Answer: A,D
Explanation:
Gaps in the Sequence
Although sequence generators issue sequential numbers without gaps, this action occurs
independent of a commit or rollback. Therefore, if you roll back a statement containing a
sequence, the number is lost.
Another event that can cause gaps in the sequence is a system crash. If the sequence
caches values in memory, those values are lost if the system crashes.
Because sequences are not tied directly to tables, the same sequence can be used for
multiple tables.
However, if you do so, each table can contain gaps in the sequential numbers.
Modifying a Sequence
If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement To remove a sequence, use the DROP statement:
DROP SEQUENCE dept_deptid_seq;