Sandy’socp1z0-001Exam
添加时间: 2008-4-27 0:45:58 作者: Oracle指导 阅读次数:14 来源: http://www.d9soft.com
ANS: YOU NEED TO GIVE THE ARITHMETIC EXPRESSION THAT INVOKES THE SALARY INCRMENT IN THE SELECT CLAUSE OF THE SELECT STATEMENT.
68. EXAMINE THE TABLE INSTANCE CHART FOR THE EMPLOYEE TABLE
COL.NAME ID.NO NAME SALARY DEPTNO HIRE_DT
KEY TYPE PK FK
NULLS/UNIQ NN,UU NN
FKTABLE DEPT
FKCOLUMN DEPT_NO
DATATYPE NUM VARCHAR2 NUM NUM DATE
LENGTH 9 25 812 3
YOU NEED TO DISPLAY THE HIRE_DATE VALUES IN THIS FORMAT 10 of October 1999
WHICH SELECT STATEMENT CAN YOU USE?
SELECT TO_CHAR(HIRE_date, m dd f?Month yyyy? DATE HIRED FROM EMPLOYEE ;
69. EXAMINE THE TABLE INSTANCE CHART FOR THE EMPLOYEE TABLE
YOU WANT TO DISPLAY EMPLOYEE HIRE DATE FROM THE EARLIEST TO LATEST. WHICH SQL STATEMENT WOULD YOU USE?
ANS: SELECT HIRE_DATE FROM EMPLOYEE ORDER BY HIRE_DATE
70. EXAMINE THE TABLE INSTANCE CHART FOR THE PATIENT TABLE
YOU CREATED THE PATIENT_VU VIEW BASED ON THE ID_NUMBER AND LAST_NAME COLUMNS FROM THE PATEINT TABLE WHAT IS THE BEST WAY TO MODIFY THE VIEW TO CONTAIN ONLY THOSE PATIENTS BORN IN 1997
AND REPLACE THE VIEW ADDING A WHERE CLAUSE
71. EVALUATE THIS PL/SQL BLOCK
BEGIN
FROM I IN 1..5 LOOP
IF I=1 THEN NULL;
ELSIF I = 3 THEN COMMIT;
ELSIF I = 5 THEN ROLLBACK;
ELSIF INSERT INTO TEST(RESULTS);
VALUES (I);
ENDIF;
END LOOP;
COMMIT;
END;
HOW MANY VALUES WILL BE PARMANENTLY INSERTED INTO THE TEST TABLE
ANS 1
72. WHICH SCRIPT WOULD YOU USE TO QUERY THE DATA DICTIONARY TO VIEW ONLY THE NAMES OF THE PRIMARY KEY CONSTRAINTS USING A SUBSTITUTION PARAMETER FOR THE TABLE NAME ?
ANS:
ACCEPT TABLE PROMPT ABLE TO VIEW PRIMARY KEY CONSTRAINT?
SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS;
WHERE TABLE_NAME = UPPER(?TABLE? AND CONSTRAINT_TYPE = ?
73. DRAG AND DROP (MATCH DEFINATION TO CONSTRAINT)
CONSTRAINT NAME ANS DEFINATION
CHECK 2 TO COLUMN MUST BE CONTAIN A VALUE
IN EACH ROW
NOT NULL 3 EACH VALUE MUST BE DIFF.IN A COLUMN
IN COLUMNS
UNIQUE 4 THE VALUE MUST BE UNIQUE & PRESENT
PRIMARY KEY 1 IF DEFINES A CONDITION THAT EACH ROW
MUST SATISFY
FOREIGN KEY 5 IT ESTABLISH A RELAIONSHIP BETWEEN COLUMNS
74. YOU ATTEMPT TO CREATE THE SALARY TABLE WITH THIS COMMAND
1 CREATE TABLE SALARY
2 EMPLOYEE_ID NUMBER(9)
3 CONSTRAINT SALARY_PK PRIMARY KEY
4 1995_SALARY NUMBER(8,12),
5 NUMBER MANAGER_NAME VARCHAR2(25)
6 CONSTRAINT MGR_NAME_NN NOT NULL,
7 $SALARY_96 NUMBER (8,12);
WHICH TWO LINES OF THE STATEMENT WILL RETURN ERRORS (CHOSE TWO)
ANS 4,7
75. WHICH SELECT STATEMENT DISPLAYS THE ORD_ID, PRODUCT_ID AND QTY OF ITEMS IN THE ITEM TABLE THAT MATCHES BOTH THE PRDUCT ID AND QUANTITY OF AN ITEM ORDER (605). DO NOT DISPLAY THE DETAILS OF THE ODERS 605
SELECT ORDID, PRODUCT_ID, QTY FROM ITEM
WHERE (PRODUCT_ID, QTY) IN (SELECT PRODUCT_ID, QTY FROM ITEM
WHERE ORD_ID = 605)
AND ORDID<> 605;
76. WHICH SELECT STATEMENT DISPLAY ALL THE EMPLOYEES WHO DO NOT HAVE A SUBORDINATE?
SELECT E.ENAME FROM EMP E
WHERE E.EMPNO NOT IN (SELECT M.MGR FROM EMP M WHERE M.MGR IS NOT NULL);
77. GIVEN THE CURSOR STATEMENT
DECLARE
CURSOR QUERY_CURSOR (V_SALARY) IS SELECT LAST_NAME, SALARY, DEPTNO FROM EMPLOYEE
WHERE SALARY ? V_SALARY
WHY DOES THIS STATEMENT CAUSE AN ERROR ?
ANS: A SCALAR DATATYPE WAS NOT SPECIFIED FOR THE PARAMETER
78. EXAMINE THIS STRUCTURE
EMP TABLE
NAME NULL TYPE
EMP_NUMBER NOT NULL NUMBER(4)
NAME VARCHAR2
JOB NUMBER(2,9)
MGR NUMBER(4)
HIREDATE DATE
SALARY NUMBER(7,2)
COMM NUMBER (7,2)
DEPTNO NOT NULL NUMBER(2)
TAX TABLE
NAME NULL TYPE
TAXGRADE NUMBER
LOWSAL NUMBER
HIGHSAL NUMBER
YOU WANT TO CREATE A REPORT THAT DISPLAY THE EMPLOYEE DETAILS ALONG WITH THE TAX CATEGORY OF EACH EMPLOYEE.
THE TAX CATEGORY IS DETERMINED BY COMPARING THE SALARY OF THE EMPLOYEES THE TAX CATEGORY IS DETERIMED BY THE COMPANIES.
THE SALARY FROM THE EMP TABLE TO THE LOWER AND THE UPPER SALARY VALUES IN THE TAX TABLE.
WHICH SELECT STATEMENT PRODUCES THE REQUIRED RESULT?
ANS: SELECT E.NAME, E.SALARY, E.TAX GRADE FROM EMP E, TAX T
WHERE E.SALARY IN T.LOWSAL AND T.HIGSAL
79. EXAMINE THE STRUCTURE OF THE PRODUCT AND PART TABLES
PRODUCT
ID PK NAME
***
PART
***
ID PK NAME PRODUCT_ID COST
YOU ISSUE THIS SQL STATEMENT:
SELECT PR.NAME FROM PART PT, PRODUCT PRINTER WHERE PT.PRODUCT_ID(T) = PR.ID;
WHAT IS THE RESULT
ANS. AN ERRORT IS GENERATED
80. YOU NEED TO RETRIVE THE EMPLOYEE NAMES AND SALARIES FROM EMP TABLES ASSORTED BY THE SALARY IN DESCENDING ORDER IF TWO NAMES MATCH FOR A SALARY THEN TWO NAMES MUST BE DISPLAYED IN ALPHABETICS ORDER WHICH STATEMENT PRODUCES THE REQUIRED RESULT.
??
ANS SELECT ENAME,SAL FROM EMP ORDER BY SAL, DESC, PNAME;
81. EXAMINE THE STRUCTURE OF THE DEPARTMENT AND EMPLOYEE TABLE
DEPARTMENT
ID PK NAME
**
EMPLOYEE
**
ID PK LASTNAME FIRST NAME DEPTNO
EVAULATE THIS SQL STATEMENT
CREATE INDEX EMP_DEPT_ID_IDX
ON EMPLOYEE (DEPT_ID);
WHICH RESULT WILL THIS STATEMENT PROVIDE
ANS MAY REDUCE THE AMOUNT OF DISC I/O FOR SELECT STATEMENT
82. EXAMINE THE TABLE INSTANCE CHART FOR THE PATIENT TABLE
YOU NEED TO CREATE THE PATIENT_ID SEQ. SEQUENCE TO BE USED WITH THE PATIENT TABLE PRIMARY KEY COLUMN. THE SEQUENCE WILL BEGINI WITH 1000, HAVE A MAXIMUM VALUE OF 999999999 NEVER REUSE ANY NUMBER INCREMENT BY 1. WHICH STATEMENT WOULD YOU USE TO COMPLETE THIS TASK
ANS CREATE SEQUENCE PATIENT_ID_SEQ
START WITH 1000
MAXVALUE 999999999
NO CYCLE
83. EXAMINE THIS CODE
SELECT EMPLOYEE.ENAME
FROM EMPLOYEE
WHERE EMPLOYEE.ENAME NOT IN (SELECT MANAGER.MGR FROM EMP.MANAGER);
WHAT IS NOT AN OPERATOR FOR EQUIVALENT TO THE ABOVE PARA
ANS : !=
84. YOU WANT TO CREATE A REPORT THAT GIVES PER DEPARTMENT THE NUMBER OF EMPLOYES AND TOTAL SALARY AS A PERCENTAGE OF ALL THE DEPARTMENT EXAMINE THE RESULTS FROM THE REPORT
DEPARTMENT %EMPLOYEES %SALARY
10 21.4 30.15
20 35.71 37.47
30 42.86 32.31
WHICH SELECT STATEMENT PRODUCES THE REPORT
ANS: SELECT A.DEPTNO EPARTMENT?
ROUND(A.NUM_EMP/B.TOTAL - COUNT * 100,2) MPLOYEE?ROUND(A.SAL_SUM / B.TOTAL ?SAL* 100,2) ?SALARY?
FROM
(SELECT DEPTNO, COUNT(*) NUM_EMP, SUM(SAL) SAL_SUM
FROM SCOTT.EMP GROUP BY (DEPTNO) A,
(SELECT COUNT(*) TOTAL_COUNT, SUM(SAL) TOTAL_SAL
FROM SCOTT.EMP) B;
Sandy’socp1z0-001Exam(4) 第 [1] [2] [3] [4] [5] 下一页
上一篇文章: 深入了解Oracle10g中新型层次查询选项 下一篇文章: 案例学习Oracle错误：ORA-00904
相关文章:
相关软件: