OCP的一份题目！
OCP-----PL/SQL
1. Find Net Rev.
Select id, sale_price *1.25 - cost *1.1
From product
2. Dept_id = null ---- > change to is null
3. Select (.15*e.salary) + (.25 * e.bonus) + (s.sale_amount *(.15 * e.commission_pct)
If you remove all the parenthesis what is the result?
4. Single row functions can use in Where clause
a. User_supplied literal
b. Arithmetic express
c. Column name
d. Column alias
e. Column position
5. Delete from emp
Where dept_id = ANY
(Select dept_id from emp
where upper(last_name) = ‘BROWN’
and upper(first_name)= ‘JIM’
a. Delete one the employees name Jim Brown
b. Delete some of employee in the same department as Jim Brown
c. All the employees in and department with an employee name Jim
d. All the employees in any department with an employee name Jim Brown
6. PRAGMA EXCEPTION __ INIT (s_ error, -1234);
a. An exception is associated
b. An exception is declared
c. An exception is referred
d. An exception is created
7. Delete from dept where dept_id = 901, if you have child records
a. Add force keyword to the command
b. Add constrains cascade option to the command
c. You can not make the command
d. Delete the child record first
8. After each FETCH statement in PL/SQL block
a. Test the row using a cursor attribute
b. Open cursor
c. Close cursor
d. Initialize the loop
9. Value could be 1, 12, or 12.35
Which data type would be most appropriate for the is?
a. LONG
b. NUMBER
c. NUMBER(p,s)
10. Evaluate this command
1. CREATE TABLE sale_price
2. (id_number$ NUMBER(35)
3. CONSTRAINT sale_proce_pk PRIMARY KEY,
4. description# VARCHAR2(200),
5. price& NUMBER(8,2),
6. 1955_abc NUMBER(10));
Which lines contain error?
11. You attempt to create a view with this command
CREATE VIEW parts_3456_vu
AS SELECT id_number, description
FROM inventory
WHERE id_number = 3456
ORDER BY description;
Which clause causes an error?
12. Evaluate this command:
SELECT id_number, description
FROM inventory
WHERE id_number=
(SELECT id_number
FROM inventory
WHERE quantity> 500 OR price> 5.00);
Which clause will cause an error?
13.Acme Sports Supply keeps tack of customer,order,supply and contacts.
Customers may have multiple phone number and address; Order has sales
id and sales rep. id; Supply has order date and quantity; Customer must
have name and at least one phone number. So which is mandatory attribute:
a. customer
b. sales rep id
c. customer name
d. quantity
14. Which operator could be used to compare a known value to a NULL value?
15. If you did not specity precision on ID(ID is NUMBER(9). What is precision on ID?
a. 1
b. 9
c. 38
d. 64
e. 255
16. Need to create a sequence to use on s __ emp table emp __ id column as primary key. Start with 1000. Increment by 1. Maximum value 999999999. Sequence number not be reused.
a. CREARTE SEQUENCE emp __id
START WITH 1000
MAXVALUE 999999999
INCREMENT BY 1;
b. CREATE SEQUENCE emp __id
START WITH 1000
MAXVALUE 999999999
NOCACHE
NOCYCLE;
c. CREATE SEQUENCE emp __id
ON s__emp(emp__id)
START WITH 1000
MAXVALUE 999999999
INCREMENT BY 1
NOCACHE
NOCYCLE;
17.When would you create an index?
a. The table is small
b. The table is updated frequently
c. Most queries to the table are expected to 2-4%
d. The columns are not often used as a condition in the query
e. Has a small number of null values
18. What kind of loop would need EXIT statement?
a. FOR
b. WHILE
c.Basic loop
d.NONE
19. End of Transaction
a. rollback
b. alter
c. update
d. delete
e. insert
f. grant
20. What is the command to reinstall disable primary key constraints |
