 |
| Part -6 |
Question 1:
The CUSTOMERS table has these columns:
CUSTOMER_ID NUMBER(4) NOT NULL
CUSTOMER_NAME VARCHAR2(100) NOT NULL
STREET_ADDRESS VARCHAR2(150)
CITY_ADDRESS VARCHAR2(50)
STATE_ADDRESS VARCHAR2(50)
PROVINCE_ADDRESS VARCHAR2(50)
COUNTRY_ADDRESS VARCHAR2(50)
POSTAL_CODE VARCHAR2(12)
CUSTOMER_PHONE VARCHAR2(20)
The CUSTOMER_ID column is the primary key for the table.
Which two statements find the number of customers? (Choose two.)
A. SELECT TOTAL(*) FROM customer;
B. SELECT COUNT(*) FROM customer;
C. SELECT TOTAL(customer_id) FROM customer;
D. SELECT COUNT(customer_id) FROM customer;
E. SELECT COUNT(customers) FROM customer;
F. SELECT TOTAL(customer_name)
FROM customer;
Answer: B, D
Explanation:
These statements provide correct syntax and semantics to show the number of customers. Function COUNT() can be used with substitution symbol of all columns "*" or just with one column name. Last query will be processed a little bit faster.
Incorrect Answers
A: There is no TOTAL() function in Oracle. C: There is no TOTAL() function in Oracle.
E: You cannot table name as a parameter of COUNT() function.
F: There is no TOTAL() function in Oracle.
QUESTION 2:
Which two tasks can your perform by using the TO_CHAR function? (Choose two)
A. Convert 10 to 'TEN'
B. Convert '10' to 10
C. Convert '10' to '10'
D. Convert 'TEN' to 10
E. Convert a date to a character expression
F. Convert a character expression to a date
Answer: C, E
Explanation:
TO_CHAR(x) function is used to convert the value x to a character or converts a date to a character string using formatting conventions.
Incorrect Answers
A: This function cannot convert the number to the string representing number spelled out.
B: TO_CHAR() function cannot convert the character value to a number. TO_NUMBER() function does this.
D: This function is not able to convert the string representing number spelled out to the number itself.
F: TO_CHAR() function cannot convert a character expression to a date. TO_DATE() function does this.
QUESTION 3:
Which two statements are true regarding the ORDER BY clause? (Choose two)
A. The sort is in ascending by order by default.
B. The sort is in descending order by default.
C. The ORDER BY clause must precede the WHERE clause.
D. The ORDER BY clause is executed on the client side.
E. The ORDER BY clause comes last in the SELECT statement.
F. The ORDER BY clause is executed first in the query execution.
Answer: A, E
Explanation:
The ORDER BY clause does sort data in ascending order by default. And the ORDER BY clause comes last in the SELECT statement: after FROM or WHERE or GROUP BY clause.
Incorrect Answers
B: The ORDER BY clause does sort data in ascending, not descending order, by default.
C: The ORDER BY clause must be after the WHERE clause in the SQL statement.
D: The ORDER BY clause is executed on the server side as the whole SQL statement is.
F: The ORDER BY clause is executed last in the query execution, after results are limited with the WHERE and GROUP BY clause conditions.
Explanation:
You can find the percentage tax applicable for each employee by using SQL statement in answer A. Incorrect Answers
B: Syntax "WHERE e.salary > t.min_salary, tax_percent" is incorrect.
C: Functions, like MIN() and MAX(), cannot be used in the WHERE clause.
D: The SQL statement from the answer A will provide requested information. OCP Introduction to
QUESTION 4:
Which are DML statements? (Choose all that apply)
A. COMMIT...
B. MERGE...
C. UPDATE...
D. DELETE...
E. CREATE...
F. DROP...
Answer: B, C, D
Explanation:
MERGE, UPDATE and DELETE commands are data manipulation language (DML) statements. Incorrect Answers
A: COMMIT is not a DML command.
E: CREATE is a data definition language (DDL) command.
F: DROP is a data definition language (DDL) command.
QUESTION 5:
Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She granted SELECT privilege to Scott on this view. Which option enables Scott to eliminate the need to qualify the view with the name MARY
.EMP_DEP_LOC_VU each time the view is referenced?
A. Scott can create a synonym for the EMP_DEPT_LOC_VU bus using the command: CREATE PRIVATE SYNONYM EDL_VU
FOR mary. EMP DEPT_LOC_VU;
then he can prefix the columns with this synonymn.
B. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command: CREATE SYNONYM EDL_VU
FOR mary. EMP DEPT_LOC_VU;
then he can prefix the columns with this synonym.
C. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command: CREATE LOCAL SYNONYM EDL_VU
FOR mary. EMP DEPT_LOC_VU;
then he can prefix the columns with this synonym.
D. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command: CREATE SYNONYM EDL_VU
FOR mary. EMP DEPT_LOC_VU;
then he can prefix the columns with this synonym.
E. Scott cannot create a synonym because synonyms can be created only for tables.
F. Scott cannot create any synonym for Mary's view. Mary should create a private synonym for the view and grant SELECT privilege on that synonym to Scott.
Answer: B
Explanation:
Correct syntax to create a local synonym is CREATE SYNONYM synonym_name. With PUBLIC keyword you can create public synonym.
Incorrect Answers
A: There is no PRIVATE keyword for the CREATE SYNONYM command.
C: There is no LOCAL keyword for the CREATE SYNONYM command.
D: This SQL statement shows incorrect syntax to create a synonym.
E: Synonyms can be created not only for tables but for other objects also.
F: Scott can create synonym for Mary's view because she granted SELECT privilege to Scott on this view. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason
QUESTION 6
Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
You issue these statements:
CREATE table new_emp ( employe_id NUMBER, name VARCGAR2(30));
INSERT INTO new_emp SELECT employee_id, last_name from employees;
Savepoint s1;
UPDATE new_emp set name = UPPER(name);
Savepoint s2;
Delete from new_emp;
Rollback to s2;
Delete from new_emp where employee_id=180;
UPDATE new_emp set name = 'James';
Rollback to s2;
UPDATE new_emp sey name = 'James' Where employee_id=180;
Rollback;
At the end of this transaction, what is true?
A. You have no rows in the table.
B. You have an employee with the name of James.
C. You cannot roll back to the same savepoint more than once.
D. Your last update fails to update any rows because employee ID 180 was already deleted.
Answer: A
Explanation:
At the end of this transaction you will not have rows in the table.
Incorrect Answers
B: All transactions will be roll backed, so it will be no rows in the table.
C: It is possible to roll back to the same savepoint more than once.
D: Your last update will not fail because there is employee with ID 180 in the table
and transactions have been rolled back only to the savepoint s2 before issue this update.
QUESTION 7:
Which two are attributes of /SQL*Plus? (Choose two)
A. /SQL*Plus commands cannot be abbreviated.
B. /SQL*Plus commands are accesses from a browser.
C. /SQL*Plus commands are used to manipulate data in tables.
D. /SQL*Plus commands manipulate table definitions in the database.
E. /SQL*Plus is the Oracle proprietary interface for executing SQL statements.
Answer: D, E
Explanation:
D: SQL*Plus commands can be used to manipulate data in tables.
E: iSQL*Plus is a proprietary interface for executing SQL Statements Incorrect Answers
A: SQL*Plus commands can be abbreviated. Like command DESCRIBE can be abbreviated as DESC, or SELECT as SELE.
B: SQL*Plus commands are not accesses from a browser.
C: SQL DML not SQL Plus is used to manipulate data
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 38-48 Chapter 1: Overview of Oracle Databases
QUESTION 8:
In which scenario would index be most useful?
A. The indexed column is declared as NOT NULL.
B. The indexed columns are used in the FROM clause.
C. The indexed columns are part of an expression.
D. The indexed column contains a wide range of values.
Answer: D
Explanation:
Index will be useful if the indexed column contains a wide range of values. Especially B- tree indexes will work better for tables with a wide range of values. But for tables just with some distinct values bitmap indexes will be more helpful.
Incorrect Answers
A: Oracle automatically creates index for NOT NULL columns, so you don't need create an index yourself.
B: There are only table names in the FROM clause, not columns.
C: Index may be will not work if the indexed columns are part of an expression. You need to avoid expressions if you want to use index.
