Sandy’socp1z0-001Exam
添加时间: 2008-4-27 0:45:58 作者: Oracle指导 阅读次数:14 来源: http://www.d9soft.com
42. YOU NEED TO UPDATE EMPLOYEE SALARIES IF THE SALARY OF AN EMPLOYEE IS LESS THAN 100, THE SALARY NEEDS TO BE INCREMENTED BY 10% USE THE SQL* PLUS SUBSTITUTION VARIABLE TO ACCENT THE EMPLOYEE. NUMBER WHICH PL/SQL BLOCK SUCCESSFULLY UPDATES THE SALARIES.
ANS:
DECLARE
V- SAL EMP.SAL % TYPE ;
BEGIN
SELECT SAL INTO V-SAL FROM EMP
WHERE EMPNO = &&P_EMPNO;
IF V_SAL <1000 THEN
UPDATE EMP
SET SAL: = SAL * 1.1
WHERE EMPNO = &P_EMPNO;
ENDIF;
END;
43. EXAMINE THIS CODE
DECLARE
I NUMBER:= 0;
V_DATE DATE;
BEGIN
I:= I+1;
LOOP
I:= V_DATE + 5;
I:= I+1;
LOOP
I:=V_DATE + 5;
I:= I + 1;
EXIT WHEN I:= 5;
END LOOP;
END
YOU HAVE ENCOUNTERED THE UNEXPECTED RESULTS WHEN ABOVE BLOCK OF CODE IS EXECUTED HOW CAN YOU TRACE THE VALUES OF COUNTER VARIABLE I AND DATE VARIABLE V_DATE IN SQL *PLUS ENVIRONMENT?
ANS: BY INSERTING THE SATEMENT
DMBS_OUTPUT.PUT_LINE ( I|| || TO_CHAR(V_DATE));
BETWEEN LINE 8 AND 9
44. EXAMINE THIS CODE
SET SERVEROUTPUT ON
DECLARE
V_NAME EMP.ENAME%TYPE
V_NUM NUMBER
V_SAL NUMBER (8,12);
BEGIN
-THIS CODE DISPLAYS SALARIES IF LARGER THAN 10,000
SELECT ENAME,SAL INTO V_NAME,V_SAL FROM EMP
WHERE EMPNO = 101;
IF (V_SAL .GT. 10000) THEN
DBMS_OUTPUT.PUT_LINE(ALAR IS?||V_SAL||OR EMPLOYEE|V_NAME);
ENDIF
END
SET SERVER OUTPUT OFF
THIS STATEMENT PRODUCES A COMPILATION ERROR WHEN ABOVE PL/SQL BLOCK IS EXECUTED WHICH SATEMENT CAUSES THE ERROR?
ANS: IF(V_SAL .GT. 10000) THEN
45. WITHIN A PL/SQL LOOP YOU NEED TO TEST IF THE CURRENT FETCH WAS SUCCESSFUL WHICH SQL CURSOR ATTRIBUTE. WHAT WOULD YOU USE TO ACCOMPLISH THIS TASK ?
ANS SQL%FOUND
46. YOU QUERY THE DATABASE WITH THIS COMMAND
SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEE
WHERE SALARY IN (SELECT SALARY FROM EMPLOYEE
WHERE DEPT_NO = 3 OR DEPT_NO = 5);
WHICH VALUES DISPLAYED
ANS: LAST NAME AND FIRST NAME OF ALL THE EMPLOYEE WITH THE SAME SALARY AS EMPLOYEES IN THE DEPARTMENT 3 OR 5.
47. WHICH OPERATOR IS NOT APPROPRIATE IN THE JOINED CONDITION OF A NON-EQUIJOIN SELECT STATEMENT
EQUAL OPERATORS
48. THE EMPLOYEE TABLE HAS 10 COLUMNS SINCE YOU OFTEN QUERY THE TABLE WITH CONDITIONS BASED ON FOUR OR MORE COLUMNS. YOU CREATED AN INDEX ON ALL THE COLUMNS IN THE TABLE, WHICH RESULT WILL OCCUR?
ANS UPDATES ON THE TABLE WILL BE SLOWER
49. YOU NEED TO EXECUTE A SCRIPT FILE NAMED QUERYEMP SQL FROM YOU SQL*PLUS ENVIRONMENT WHICH COMMAND DO YOU USE?
START QUERYEMP
50. WHICH DATA TYPE SHOULD YOU USE FOR INTEREST RATES WITH VARYING AND UNPREDICATBLE DECIMAL PLACES SUCH AS 1.234, 3.4 AND 1.23 ?
ANS: NUMBER
51. WHICH STATEMENT IS TRUE WHEN A DROP TABLE COMMAND IS EXECUTED ON A TABLE ?
ANS: THE TABLE STRUCTURE AND IS DELETED DATA CAN NOT BE ROLLED BACK AND RESTORED ONCE THE DROP TABLE COMMAND EXECUTED
52. EXAMINE THE STRUCTURE OF STUDENT TABLE
WHAT STATEMENT ADDS A NEW COLUMN ALFTER NAME COLUMN TO HOLD PHONE NUMBER?
ANS: YOU CAN NOT SPECIFY POSITION WHEN NEW COLUMN HAS TO BE ADDED
53. WHICH THREE SQL ARITHMETIC EXPRESSION RETURN A DATE? (CHOOSE THREE)
ANS ?3-JUL-96?+ 7
?3-JUL-96?12
?3-JUL-96?12/24
54. WHICH STATEMENT WOULD YOU USE TO ADD A PRIMARY KEY CONSTRAINT TO THE PATIENT TABLE USING THE ID_NUMBER COLUMN, IMMEDIATELY ENABLING THE CONSTRAINT ?
ALTER TABLE PATIENT
ADD CONSTRAINT PAT_ID_PK
PRIMARY KEY (ID_NUMBER);
55. WHICH SELECT STATEMENT WOULD YOU USE IN A PL/SQL BLOCK TO QUERY THE EMPLOYEE TABLE AND RETRIVE THE LAST NAME AND SALARY OF THE EMPLOYEE WHOSE ID IS 3?
SELECT LAST_NAME, SALARY FROM EMPLOYEE
INTO V_LAST_NAME, V_SALARY WHERE ID = 3;
56. THE STRUCTURE OF THE DEPT TABLE IS DEPT. EXAMINE THE DECLARATION SECTION
DECLARE
TYPE DEPT_RECORD_TYPE IS RECORD (DNO NUMBER,
NAME VARCHAR(20));
DEPT_REC DEPT_RECORD_TYPE;
HOW DO YOU RETRIVE AN ENTIRE ROW OF THE DEPT TABLE USING THE DEPT_REC VARIABLE?
ANS. YOU CAN NOT RETRIVE THE ENTIRE ROW USING THE DEPT_REC_VARIABLE DECLARED IN THE CODE.
57. EXAMINE THIS CODE
DECLARE
CURSOR EMP_CURSOR IS
SELECT ENAME,DEPTNO FROM EMP;
EMP_REC EMP_CURSOR%ROWTYPE
BEGIN
OPEN EMP_CURSOR
LOOP
FETCH EMP_CURSOR INTO EMP_REC
EXIT WHEN EMP_CURSOR NOTFOUND;
INSERT INTO TEMP_EMP (NAME,DNO)
VALUES (EMP_REC.ENAME,EMP_REC.DEPTNO);
END LOOP;
CLOSE EMP_CURSOR;
END;
USING A CURSOR FOR LOOP, WHICH PL/SQL BLOCK IS EQUIVALENT TO THE ABOVE CODE
ANS:
DECLARE
CUSROR EMP_CURSOR IS
SELECT ENAME,DEPTNO FROM EMP;
BEGIN
FOR EMP_REC IN EMP_CURSOR
LOOP
INSERT INTO TEMP_EMP(NAME,DNO) VALUES (EMP_REC.ENAME, EMP_REC.DEPTNO)
END LOOP
END;
58. UNDER WHICH SITUATION IT IS NECESSARY TO USE AN EXPLICIT CURSOR ?
WHEN A SELECT STATEMENT IN A PL/SQL BLOCK IS MORE THAN ONE ROW
59. USING SQL*PLUS YOU CREATED A USER WITH THIS COMMAND
CREATE USER JENNIFER IDENTIFIED BY JBW122;
WHAT SHOULD YOU DO TO ALLOW HER UDER DATABASE ACCESS?
ANS GRANT THE USER CREATE SESSION PRIVILAGES
60. A DBA HAS UPDATED SMITH ACCOUNT BY ADDING THE PRIVILAGES; CREATE ANY TABLE AND CREATE PROCEDURE WHICH TASK CAN SMITH SUCCESSFULLY PERFORM
ANS
SMITH CAN CREATE A TABLE IN HIS OWN SCHEME OF THE DATABASE BUT CAN DROP TABLES FROM AND CREATE A PROCEDURE ONLY IN HIS SCHEME
61. FOR WHICH THREE TASKS WOULD YOU USE THE WHERE CALUSE (CHOOSE THREE)
1) RESTRICT THE ROWS DISPLYED
2) ONLY DISPLAY DATA GRETER THAN A SPECIFIED VALUE
3) TO COMPARE TWO VALUES WHERE CLAUSE IS USED
62. WHICH DATA DICTIONARY VIEW CONTAINS THE DEFINITION OF A VIEW
USER_VIEWS
63. YOU HAVE THE EMPLOYEE TABLE YOU CREATE A VIEW WITH THIS COMMAND
CREATE VIEW DEPT_SALARY_VU
AS SELECT DEPT_NO, SALARY, LAST_NAME FROM EMPLOYEE
WHERE SALARY > 45000
WITH CHECK OPTION;
FOR WHICH EMPLOYEE CAN YOU UPDATE THE DEPTNO COLUMN USING
ANS: NONE
64. WHAT SHOULD YOU DO AFTER EACH FETCH STAT. IN THE PL/SQL BLOCK ?
INITIALIZE THE LOOP
65. WHICH STATEMENT ABOUT USING A SUBQUERY IN THE FROM CLAUSE IS TRUE?
YOU ELIMINATE THE NEED TO CREATE A NEW VIEW OR TABLE BY PLACING A SUBQUERY IN THE FROM CLAUSE
66. EXAMINE THE TABLE INSTANCE CHART FOR THE PATIENT TABLE
COLNAME IDNO LASTNAME FIRST_NAME BDATE DOCTOR
ID
KEY TYPE PK
NULL/UNIQUE NN NN NN
FK TABLE DOCTOR
DATA TYPE NUM VARCHAR2 VARCHAR2 DATE NUM
LENGTH 10 25 25 10
YOU CREATED THE PATIENT_ID_SEQ
SEQUENCE TO BE USED WITH THE PATIENT TABLES PRIMARY KEY COLUMN
THE SEQUENCE BEGINS AT 1000 HAS A MAXIMUM VALUE OF 99999999 AND INCREMENT BY 1. YOU NEED TO WRITE A SCRIPT TO INSERT A ROW INTO THE PATIENT TABLE AND USE THE SEQUENCE YOU CREATED WHICH SCRIPT WOULD YOU USE TO COMPLETE THE TASK.
ANS.
INSERT INTO PATIENT (ID_NUMBER, LAST_NAME,FIRST_NUMBER,
BIRTH_DATE) VALUES (PATIENT_ID_SEQ.NEXTVAL, &LAST_NAME,
&FIRST_NAME, &BIRTH_DATE)
Sandy’socp1z0-001Exam(3) 第 [1] [2] [3] [4] [5] 下一页
上一篇文章: 深入了解Oracle10g中新型层次查询选项 下一篇文章: 案例学习Oracle错误：ORA-00904
相关文章:
相关软件: