QUESTION 1:
Examine the data in the EMPLOYEES and DEPARTMENTS tables. EMPLOYEES
LAST_NAME DEPARTMENT_ID SALARY Getz 10 3000
Davis 20 1500
Bill 20 2200
Davis 30 5000
Kochhar 5000
DEPARTMENTS
DEPARTMENT_ID DEPARTMENT_NAME
10 Sales
20 Marketing
30 Accounts
40 Administration
You want to retrieve all employees, whether or not they have matching departments in tWhehidcehpaqrutemryenwtsotualbdley.ou use?
A. SELECT last_name, department_name
FROM employees , departments(+);
B. SELECT last_name, department_name FROM employees JOIN departments(+); C. SELECT last_name, department_name
ON (e. department_ id = d. departments_id); FROM employees(+) e JOIN departments d D. SELECT last_name, department_name FROM employees e
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id); E. SELECT last_name, department_name
FROM employees(+) , departments
ON (e. department _ id = d. department _id); F. SELECT last_name, department_name FROM employees e LEFT OUTER
JOIN departments d ON (e. department _ id = d. department _id);
Amnatscwheinr:gFFdisepcaorrtrmecetn.tTshinistqhueedreypsahrotmwesnctotrarbelcet. sOyrnatcalxe9toi erexttreinedvse iatlsl ceommpploliyaenecse, with
EwAxhNpeSltahIn/eIarStoOiornbn:yotstuhpepyohrtaivneg
that standard's requirements for outer join syntax and semantics. Incorrect Answers
A: This query uses "+" to create outer join as it was in Oracle8i, but it requires also uSsEaLgEe CofTWstHatEemReEnct.lause in
B: The JOIN clause cannot be used with in conjunction with "+": syntax is incorrect. C: The JOIN clause cannot be used with in conjunction with "+": syntax is incorrect. D: This statement requires LEFT OUTER JOIN, not RIGHT OUTER JOIN.
E: This query uses incorrect syntax with "+" and ON to create outer join.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 112-114
Chapter 3: Advanced Data Selection in Oracle
QUESTION 2:
Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements inserts a row into the table? (Choose three)
A. INSERT INTO employees
VALUES (NULL, 'JOHN','Smith');
B. INSERT INTO employees( first_name, last_name) VALUES ('JOHN','Smith');
C. INSERT INTO employees
VALUES ('1000','JOHN','NULL');
D. INSERT INTO employees(first_name,last_name, employee_id) VALUES ('1000, 'john','Smith');
E. INSERT INTO employees (employee_id) VALUES (1000);
F. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'john',");
Answer: C, E, F Explanation:
Since EMPLOYEE_ID column is used as primary key, it cannot be NULL, so only
IaNndSEFRaTresctaotrermecetn. Ytsoiun cCa,nEinsert the row with NULL LAST_NAME as in answer C, or oEnMlyPtLhOe YroEwEw_IiDthas in answer E, or the row with empty LAST_NAME column. Incorrect Answers
A: This answer is incorrect because a primary key cannot be NULL.
B: INSERT statement does not contain primary key value at all, so this answer needs ooneb.e eliminated as correct
D: This statement shows incorrect order of columns of row which needs to be inserted
COnhCtoaPpthtIenertrt6ao:bdMluec.atnioipnutloatOinrgacOlera9cil:eSDQaLtaExam Guide, Jason Couchman, p. 260-267
QUESTION 3:
You need to give the MANAGER role the ability to select from, insert into, and
mth
dSiTfyUeDxEisNtinTg_GroRwAs DinES table. Anyone given this MANAGER role should be able to
prisvsiltehgoesse on to others.
Which statement accomplishes this?
A. GRANT select, insert, update
ON student_grades
TO manager
B. GRANT select, insert, update
ON student_grades
TO ROLE manager
C. GRANT select, insert, modify
ON student_grades
TO manager
WITH GRANT OPTION;
D. GRANT select, insert, update
ON student_grades
TO manager
WITH GRANT OPTION;
E. GRANT select, insert, update
ON student_grades
TO ROLE manager
WITH GRANT OPTION;
F. F.GRANT select, insert, modify
ON student_grades
TO ROLE manager
WITH GRANT OPTION; Answer: D
Explanation:
This answer provides correct syntax of GRANT command to give the MANAGER
rCollaeuaslel WaskITedHpGriRviAleNgTesO. oInthceorr.ect Answers
PTION will allow this role to pass those privileges on to
A: This statement would be correct if it included WITH GRANT OPTION clause to athllooswe pthrivsirloegleestoopnatsos others.
B: This statement uses incorrect clause TO ROLE.
C: There is no option with name MODIFY in the GRANT command. E: This statement uses incorrect clause TO ROLE.
|
F: There is no option with name MODIFY in the GRANT command. And this st tement also uses incorrect
ChCaPptIenrtr8o:dUucsteiroAn ctoceOssraCcolent9rio:lSiQn LOrEaxcalem Guide, Jason Couchman, p. 356-365
QUESTION 4:
Examine the data in the EMPLOYEES table: LAST_NAME DEPARTMENT_ID SALARY Getz 10 3000
Davis 20 1500
Bill 20 2200
Davis 30 5000
...
Which three subqueries work? (Choose three)
A. SELECT * FROM employees
where salary > (SELECT MIN(salary) FROM employees
GROUP BY department _ id); B. SELECT *
FROM employees
WHERE salary = (SELECT AVG(salary) FROM employees
GROUP BY department _ id);
C. SELECT distinct department_id
FROM employees
Where salary > ANY (SELECT AVG(salary) FROM employees
GROUP BY department _ id); D. SELECT department_id FROM employees
WHERE SALARY > ALL (SELECT AVG(salary) FROM employees
GROUP BY department _ id); E. SELECT last_name
FROM employees
Where salary > ANY (SELECT MAX(salary) FROM employees
GROUP BY department _ id); F. SELECT department_id FROM employees
WHERE salary > ALL (SELECT AVG(salary) FROM employees
GARnsOwUerP: BCY, DA, NEG (SALARY));
Explanation:
These answers show correct syntax, because they use ANY and ALL keywords for csuobn-vqeuret rmyutoltio-rnoew-roowutpreustuoltf.
Incorrect Answers
A: This SELECT statement is incorrect because of multi-row return of sub-query: it foirllErAetCurHn dmeipnairmtmalesnatl.ary
B: This SELECT statement is incorrect because of multi-row return of sub-query: it foirllErAetCurHn daveperaargtme esnatl.ary
F: This SELECT statement is incorrect because GROUP BY clause cannot contain fMunINct(i)o,nMs,AliXke()AaVnGd (s)o, on.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 145-156
Chapter 4: Subqueries
QUESTION 5:
The database administrator of your company created a public synonym called HR for tHhUe MAN_RESOURCES table of the GENERAL schema, because many users fAresqauuensetlryoufstehethdisattaabbalese. , you created a table called HR in your schema. What happens wthhisenquyeoruy?execute
SELECT * FROM HR;
A. You obtain the results retrieved from the public synonym HR created by the dBa.tYaboauseobatdamininthisetrraetsourl.ts retrieved from the HR table that belongs to your schema.
C. You get an error message because you cannot retrieve from a table that has the same nsyanmoenyasma. public
D. You obtain the results retrieved from both the public synonym HR and the HR table tshcahtebmealo, nasgsatCoayroteusrian product.
E. You obtain the results retrieved from both the public synonym HR and the HR table tshcahtebmealo, nasgsatFoUyLouLrJOIN.
Answer: B Explanation:
By executing this query you will extract data from the HR table in your own schema, it wsyinllonytmwfoorrkthweitHh UHMRAN_RESOURCES table of the GENERAL schema.
Incorrect Answers
A: The results will be retrieved from the table in your own schema, not from the
GsyEnNonEyRmAHLRsc. hema, using
C: There is no error: data from the table in your own schema will be retrieved by this qDu:eTryh.is query will not generate Cartesian product from both tables.
E: This query will not retrieve data from both tables as a FULL JOIN. ChCaPptIenrtr7o:dCucretiaotnintgoOOtrhaecrleD9ait:abSaQsLe OEbxjaemctsGiunidOer,aJcalseon Couchman, p. 331-335
QUESTION 6:
Which two statements about views are true? (Choose two.) A. A view can be created as read only.
B. A view can be created as a join on two or more tables.
C. A view cannot have an ORDER BY clause in the SELECT statement.
D. A view cannot be created with a GROUP BY clause in the SELECT statement.
E. A view must have aliases defined for the column names in the SELECT statement. Answer: A, B
Explanation:
A view can becreated as read only object. However, it is possible to change data in the usonmdeerlryeisntrgicttaibolnes(.sA) wviiethw also can be created as a join on two or more tables. This type ocof mvipelwexisvcieawll.edComplex views provide complicated data models where many base
it btoleosnaerevidrrtauwalntatboglee.ther
Incorrect Answers
C: Query operations containing ORDER BY clause are also permitted, so long as the OapRpDeaErsRouBtYsidcelatuhseeparentheses. The following is an example of what I mean: CREATE (SIEELWECmTy*_FvRieOwMASemp) ORDER BYempno.
D: A view can be created with a GROUP BY clause in the SELECT statement.
E: It is not required to have aliases defined for the column names in the SELECT
sOtaCtePmInetnrto.duction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 292-309
Chapter 7: Creating Other Database Objects in Oracle
QUESTION 7:
Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2) JOB_CAT VARCHARD2(30) SALARY NUMBER(8,2)
Which statement shows the maximum salary paid in each job category of each department?
A. SELECT dept_id, job_cat, MAX(salary) FROM employees
WHERE salary > MAX (salary);
B. SELECT dept_id, job_cat, MAX(salary) FROM employees
GDCR.R. SOOEMULPEemCBYTpldodeyeepepett_si;id,,jjoobb_ccaatt;, MAX(salary)
FROM employees
GROUP BY dept_id;
E. SELECT dept_id, job_cat, MAX(salary) FROM employees
GROUP BY dept _ id job _ cat salary; Answer: B
Explanation:
This answer provides correct syntax and semantics to show the maximum salary paid ienacehacdhepjoabrtmcaetnegt.ory of
Incorrect Answers
A: This query will not return any row because condition SALARY > MAX(SALARY) Cs:FTAhLisSqEu.ery will return error because you cannot show maximum salary with DwEithPoTu_tIgDroaunpdinJOg Bby_CthAeTse columns.
D: The GROUP BY clause is missing JOB_ID column.
E: You don't need to group results of query by SALARY in the GROUP BY column. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access Control in Oracle
QUESTION 8:
Management has asked you to calculate the value 12*salary* commission_pct for all tEhMe ePmtapbloley.eTehseinEtMheP table contains these columns:
LAST NAME VARCNAR2(35) NOT NULL SALARY NUMBER(9,2) NOT NULL COMMISION_PCT NUMBER(4,2)
Which statement ensures that a value is displayed in the calculated columns for all employees?
A. SELECT last_name, 12*salary* commission_pct
FROM emp;
B. SELECT last_name, 12*salary* (commission_pct,0) FROM emp;
C. SELECT last_name, 12*salary*(nvl(commission_pct,0))
FROM emp;
D. SELECT last_name, 12*salary*(decode(commission_pct,0)) FROM emp;
Answer: C
Explanation:
|
Trehis SELECT statement provides correct usage of NVL function to calculate columns
f tvluuerey_soi;fu_wpnhoueslnls)i.bhNieliotcytoictleoumtshunabtisthiNteuUtceoLalLuvm, atnhluesepsipenceicpfiliaelcdeitnroinNgNVUisLL(e)Ltc.uoTrnheteadib.nTasshicen vsayacnltutaeaxlsfoaelrcuiefi.ed
|
or all employees. Oracle
ohVlbauLetm(vr)aitlsvuareNnluieVsedLwis(fhcNatohteuOmLrLanc_mlne
Incorrect Answers
A: This SELECT statement will return NULL value for rows with NULL BO: IMt iMs iInScIoOrrNec_tPsCyTntcaoxliunmtnh.is query: NVL function needs to be used for correct result. D: The DECODE function is used as substitution of IF-THEN-ELSE PL/SQL
cSoEnLsEtrCucTtisotnatienmSeQntLpqrouveridieess. iTnhceorrect syntax of it cannot have only two parameters.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 31-32
Chapter 1: Overview of Oracle Databases
QUESTION 9:
Which syntax turns an existing constraint on? A. ALTER TABLE table_name
ENABLE constraint_name;
B. ALTER TABLE table_name
STATUS = ENABLE CONSTRAINT constraint _ name; C. ALTER TABLE table_name
ENABLE CONSTRAINT constraint _ name; D. ALTER TABLE table_name
STATUS = ENABLE CONSTRAINT constraint _ name; E. ALTER TABLE table_name
TURN ON CONSTRAINT constraint _ name; F. ALTER TABLE table_name
TURN ON CONSTRAINT constraint _ name;
Answer: C Explanation:
ALTER TABLE statement with ENABLE CONSTRAINT keywords is correct answer tcoonesntarbailneta. n existing
Incorrect Answers
A: This statement is missing CONSTRAINT keyword.
B: "STATUS =" is incorrect syntax to enable constraint for the table.
D: There is no STATUS keyword in the command to enable constraint.
E: There is no TURN ON keywords in the command to enable constraint. F: There is no TURN ON keywords in the command to enable constraint.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 239-240
Chapter 5: Creating Oracle Database Objects
QUESTION 10:
Examine the description of the STUDENTS table: SCESTNOADDUR__RITDDS_AEDNT_AUIEDTMDEVBAADETRARETC(E4H)ARD2(10)
Which two aggregate functions are valid on the START_DATE column? (Choose two)
A. SUM(start_date) B. AVG(start_date)
C. COUNT(start_date)
D. AVG(start_date, end_date) E. MIN(start_date)
F. MAXIMUM(start_date) Answer: C, E
Explanation:
It is possible to apply COUNT() and MIN() functions on the column with DATE data
Iynpceo.rrect Answers
A: Function SUM() cannot be used with DATE data type column. B: Function AVG() cannot be used with DATE data type column.
D: Function AVG() cannot be used with DATE data type column. And function AVG()
jXu,stnhoat stwoon.eIpt aarvaemraegters all X column values returned by the SELECT statement. F: There is no MAXIMUM() function in Oracle, only MAX() function exists. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 81-85
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 11:
The EMPLOYEE tables has these columns: LAST_NAME VARCHAR2(35)
SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(5,2)
You want to display the name and annual salary multiplied by the commission_pct for
rellcoemrdpsltohyaetehsa. vFeoar NULL commission_pct, a zero must be displayed against the cWalhciuclhatSeQd Lcosltuamtenm.ent displays the desired results?
A. SELECT last_name, (salary * 12) * commission_pct
FROM EMPLOYEES;
B. SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES;
C. SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES;
D. SELECT last_name, (salary * 12) * NVL(commission_pct, 0)
FROM EMPLOYEES;
gAnswer: DCsTsibsitlaiteymtoenstupbrsotivtuidtesacvoarlrueectinuspalgaeceoof fNNVULLfLun. cTthioenbtaosicaslcyunltatxe fcoorlumns ihvies ySoEuLpEo
ENfoxVrpaLlla(ln)eaimstiopNnlVo:yLe(ecso.lOumranc_lename,
value_if_null). Notice that the column specified in NVL() contains an actual value. rehtuartnvsa;lwuehiesnwthheatcOolruamclne in NULL, the special string is returned. the value specified to bcoelruemtunrnveadluief tihseNULL must be the same datatype as the column specified.
Incorrect Answers
A: This SELECT statement will return NULL value for rows with NULL BO: TMhMereISisIOnNo _IFPNCUTLcoLl(u)mfunn.ction in Oracle.
C: The NVL2() function requires 3 parameters, not 2. Function NVL2(expr1, expr2,
expr
)isrentuortnNsUexLpLr.2Iiffexpr1 is NULL, it returns expr3.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 31-32
Chapter 1: Overview of Oracle Databases
QUESTION 12:
Examine the data from the ORDERS and CUSTOMERS table. ORDERS
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
100 12-JAN-2000 15 10000
101 09-MAR-
2000 40 8000
102 09-MAR-
2000 35 12500
103 15-MAR-
2000 15 12000
104 25-JUN-2000 15 6000
105 18-JUL-2000 20 5000
106 18-JUL-2000 35 7000
107 21-JUL-2000 20 6500
108 04-AUG-
2000 10 8000
CUSTOMERS
CUST_ID CUST_NAME CITY
10 Smith Los Angeles
15 Bob San Francisco
20 Martin Chicago
25 Mary New York
30 Rina Chicago
35 Smith New York
4foor0drhetihcLrshei?noSdrQadLerNstetahwtaetmYaoerrnektprleatcrieedvoesn theosradmeer IdDa,yctuhsatoMmeartIinDp, laancdesorhdiser total
A. SELECT ord_id, cust_id, ord_total
FROM orders, customers
WHERE cust_name='Mating'
AND ord _ date IN ('18-JUL-2000','21-JUL-2000'); B. SELECT ord_id, cust_id, ord_total
FROM orders
Where ord_date IN (SELECT ord_date
FROM orders
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_name =
'MARTIN'));
C. SELECT ord_id, cust_id, ord_total
FROM orders
Where ord_date IN (SELECT ord_date
FROM orders, customers
Where cust _ name = 'Martin');
D. SELECT ord_id, cust_id, ord_total
FROM orders
WHERE cust_id IN (SELECT cust_id
FROM customers
WHERE cust name = 'Martin'); Answer: B
Explanation:
This query will return the order ID, customer ID, and order total for the orders that are
pthla
eMdaorntinthpelascaemsehdisayorders.
Incorrect Answers
A: This query returns only Martin's orders for July 18, 2000 and July 21, 2002, not oprladceerds oofnotthheerssamtheatdwayertehat Martin placed his orders.
C: This query uses incorrect sub-query to extract dates when Martin placed his orders. D: This query will return only Martin's orders.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 145-156
Chapter 4: Subqueries
QUESTION 13:
You need to modify the STUDENTS table to add a primary key on the STUDENT_ID
cisocluumrrnen. tTlyheemtapbtlye.
Which statement accomplishes this task?
BA..DADLTPERERIMTARBYLEKEstYudsetnuttdsent_id;
ADD CONSTRAINT PRIMARY KEY (student _ id); C. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY (student _ id); D. ALTER TABLE students
ADD CONSTRAINT stud _ id _pk PRIMARY KEY (student _ id); E. ALTER TABLE students
MODIFY CONSTRAINT stud _ id _pk PRIMARY KEY (student _ id); Answer: D
Explanation:
This statement provides correct syntax to add a primary key on the STUDENT_ID
ctaoblluem. n of the STUDENT Incorrect Answers
A: This ALTER TABLE statement is missing CONSTRAINT keyword and the name oBf: tThheicsoAnLstTraEiRnt.TABLE statement is missing the name of the constraint.
C: It's incorrect syntax in the ALTER TABLE command: STUDENT_ID must be used wE:itWh ebrnaecekdettso. add constraint, not to modify existing one. Usage of the MODIFY kcaesyew. ord is incorrect in this
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 239-240
Chapter 5: Creating Oracle Database Objects
QUESTION 14:
Evaluate the SQL statement:
1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal
2 FROM employees a,
3 (SELECT dept_id, MAX(sal) maxsal
4. FROM employees
5 GROUP BY dept_id) b
6 WHERE a.dept_id = b.dept_id
7 AND a. asl < b. maxsal;
What is the result of the statement?
A. The statement produces an error at line 1. B. The statement produces an error at line 3. C. The statement produces an error at line 6.
D. The statement returns the employee name, salary, department ID, and maximum daelpaarrytmeaernnteodfinthtehemployee for all departments that pay less salary then the maximum scaolmarpyapnayi.d in the
E. The statement returns the employee name, salary, department ID, and maximum daelpaarrytmeaernnteodfinthtehemployee for all employees who earn less than the maximum salary AiEn thlweir department.
xnps anera:tiEon:
The statement returns the employee name, salary, department ID, and maximum salary oafrtnheedeimnpthloeydeepfaorrtmalelnetmployees who earn less than the maximum salary in their
dexepamarptmleeonft.aTnhiinsliqnueevryieiws
which is the sub-query in the FROM clause of the main
qauSeEryL.ETChTe ssutabt-eqmuenryt tchaant buetilizes joins, the GROUP BY clause, or the ORDER BY Inlacuosrere. ct Answers
A: The statement does not produce an error at line 1. B: The statement does not produce an error at line 3. C: The statement does not produce an error at line 6.
D: The statement returns the employee name, salary, department ID, and maximum
daelpaarrytmeaernnteodfinthtehemployee for all EMPLOYEES, NOT DEPARTMENTS, who earn lseaslsartyhain ttheirmdaexpiamrtumment.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 161-165
Chapter 4: Subqueries
QUESTION 15:
Examine the data in the EMPLOYEES and DEPARTMENTS tables: EMPLOYEES
EMPLOYEE_ID
EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 IT_ADMIN 5000
106 Smith 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA*DIR 6500
DEPARTMENTS
DEPARTMENT_ID DEPARTMENT_NAME
10 Admin
20 Education
30 IT
40 Human Resources
Also examine the SQL statements that create the EMPLOYEES and DEPARTMENTS
C(dadeRbeplpEaearsAtr:mtTmEeenTntAt_BindLaNmEUedMVepABaRrEtCmRHePnARtsRIM2(A30R)Y); KEY,
CREATE TABLE employees
(EMPLOYEE_ID NUMBER PRIMARY KEY, EMP_NAME VARCHAR2(20),
DEPT_ID NUMBER REFERENCES
departments(department_id), MGR_ID NUMBER REFERENCES employees(employee id),
MGR_ID NUMBER REFERENCES
employees(employee id), JOB_ID VARCHAR2(15). SALARY NUMBER);
ON the EMPLOYEES,
On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID.
DEPT_ID is foreign key to DEPARTMENT_ID column of the DEPARTMENTS table. On the DEPARTMENTS table, DEPARTMENT_ID is the primary key.
Examine this DELETE statement: DELETE
FROM departments
WHERE department id = 40;
What happens when you execute the DELETE statement?
A. Only the row with department ID 40 is deleted in the DEPARTMENTS table.
B. The statement fails because there are child records in the EMPLOYEES table with dCe.pTahrtemreonwt IwDith40d.epartment ID 40 is deleted in the DEPARTMENTS table. Also the r1o1w0sanwdit1h0e6maprelodyeeleetIeDdsfrom the EMPLOYEES table.
D. The row with department ID 40 is deleted in the DEPARTMENTS table. Also the r1o0w6sawndit1h1e0mapnldoythee eIDmsployees working under employee 110 are deleted from the
EM. TPhLeOroYwEwESithtadbelep.artment ID 40 is deleted in the DEPARTMENTS table. Also all
tEhMe rPoLwOsYinEtEhSe table are deleted.
F. The statement fails because there are no columns specifies in the DELETE clause of the DELETE statement.
Answer: B
Explanation:
It will be error generated because there are 2 child records in the EMPLOYEES table wyoiuthtrdyeptoardtmelentet fnruombtehre DEPARTMENTS table.
Incorrect Answers
A: The row with department ID 40 will not be deleted because of the child records in Ch:eNEeMithPeLrOthYeErEowS twabitlhe.department ID 40 will not be deleted not child records in the beMdPeLleOteYd.EES table will
|
D: It will be error when you try to execute the DELETE statement, no rows will be
|
: Ithwe isltlabtemerernotrfaihlsenbeycoau treyotfoceoxnesctruatienthveioDlaEtLioEnTnEotsbtaetceamuesnett,hneoreroawresnwoicllobluem
|
EsdpeMelePteLdOsiYn nEEthEteSe or the DEPARTMENTS tables.
DELETE clause of the DELETE statement.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 240-245
Chapter 5: Creating Oracle Database Objects
QUESTION 16:
Which three are DATETIME data types that can be used when specifying column definitions? (Choose three.)
A. TIMESTAMP
B. INTERVAL MONTH TO DAY C. INTERVAL DAY TO SECOND D. INTERVAL YEAR TO MONTH
E. TIMESTAMP WITH DATABASE TIMEZONE Answer: A, C, D
Explanation:
TIMESTAMP, INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH
c nlubmenudsedfintoitisopne.cify
Incorrect Answers
B: The INTERVAL MONTH TO DAY data type cannot be used when specifying oonlluymINnTdEefRinViAtioLnDs AthYerTe OareSECOND and INTERVAL YEAR TO MONTH data types. E: The TIMESTAMP WITH DATABASE TIMEZONE data type cannot be used when
dpeeficnifityiionngs,cboelucmaunse there are only TIMESTAMP WITH TIME ZONE and
TIMESZTOANMEPdWataITtHypLesO. CAL
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 215-217
Chapter 5: Creating Oracle Database Objects
QUESTION 17:
Which SQL statement defines the FOREIGN KEY constraint on the DEPTNO column of the EMP table?
A. CREATE TABLE EMP (empno NUMBER(4), ename VARCNAR2(35),
deptno NUMBER(7,2) NOT NULL
CONSTRAINT emp_deptno_fk FOREIGN KEY deptno
REFERENCES dept deptno); B. CREATE TABLE EMP (empno NUMBER(4),
ename VARCNAR2(35), deptno NUMBER(7,2)
(CenO.maCNmpRnSeEoTVARNATAUREIMCNTHBATAEBeRmL(2Ep4(_)3Ed5Me)p,Ptno_fk REFERENCES dept (deptno));
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));
D. CREATE TABLE EMP (empno NUMBER(4),
ename VARCNAR2(35),
deptno NUMBER(7,2) FOREIGN KEY
CONSTRAINT emp deptno fk REFERENCES dept (deptno)); Answer: B
Explanation:
This statement provides correct syntax to define the FOREIGN KEY constraint on the
DEMEPPTtNabOlec. olumn of the
Incorrect Answers
A: There is incorrect syntax, because list of columns and column for the constraint ntheeedbrtaocbketsu. rrounded with
C: It is incorrect to use FOREIGN KEY keywords to define constraint on the table. It cinatnegbreituysceodntsotraadindt to existing table.
D: It is incorrect to use FOREIGN KEY keywords to define constraint on the table. It cinatnegbreituysceodntsotraadindt to existing table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 238-245
Chapter 5: Creating Oracle Database Objects
QUESTION 18:
Evaluate the set of SQL statements: CREATE TABLE dept
(deptno NUMBER(2), dname VARCNAR2(14),
1oc VARCNAR2 (13)); ROLLBACK; DESCRIBE DEPT
What is true about the set?
A. The DESCRIBE DEPT statement displays the structure of the DEPT table.
B. The ROLLBACK statement frees the storage space occupies by the DEPT table. C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does
nDo.tTehxeisDt.ESCRIBE DEPT statement displays the structure of the DEPT table only if
tshtaetreemisenatCinOtrModMucITed before the ROLLBACK statement.
Answer: A
Taisnhsdueeiststrcauacdntanutoraet-dboeffirtnhoieltlieDodnEbPlaTcnkgtaubbaelgceeawu(DsilelDibmLe)pdclioscpmitlamcyoaemnddmbsietuccoahcucsauesrtsahoecnrCetaRhteEAdtaaTbtEalebTasAtsaeBtewLmhEennt,a
EsutsxaeptrelmaetonxeaicnttrsiteoSiansQt:eDLaD*dPLaltouapsbeoarsraetion
object, or an alter table statement, used to alter a database object. Incorrect Answers
B: The ROLLBACK statement has nothing to do with the storage space of the DEPT Ca:bTleh. e DESCRIBE DEPT statement does not produce the error. It displays the sDtr:uTchtuerCe OofMthMe IDTEsPtaTtetmabelnet. does not need to be introduced because implicit commit oafctceurrcsreoantitohne odfattahbeatsaeble.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 281-283
Chapter 6: Manipulating Oracle Data
QUESTION 19:
Which data dictionary table should you query to view the object privileges granted to tchoeluumsenrso?n specific
A. USER_TAB_PRIVS_MADE B. USER_TAB_PRIVS
C. USER_COL_PRIVS_MADE D. USER_COL_PRIVS
Answer: D
Explanation:
The USER_COL_PRIVS data dictionary view will show the object privileges granted tcooltuhme nse. r on specific
Incorrect Answers
A: There is no USER_TAB_PRIVS_MADE view in Oracle.
B: The USER_TAB_PRIVS data dictionary view is used to show the object privileges gthreantatebdletso, tnhoet uspserciofnic columns.
C: There is no USER_COL_PRIVS_MADE view in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access Control in Oracle
QUESTION 20:
Examine the structure of the EMPLOYEES and DEPARTMENTS tables: EMPLOYEESColumn name Data type Remarks
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2 (30) JOB_ID VARCHAR2 (20) SALARY NUMBER
MGR_ID NUMBER References EMPLOYEE_ID COLUMN DEPARTMENT ID NUMBER Foreign key to DEPARTMENT ID
cDDoEluPmAnRToMf thEeNDTE__NIPCDAoAlRNMuTmUEMMnVEnBANaEmRTRCeSHNDtaAOabtRTlae2tNy(3pU0eL)RLe, mPrairmksary Key
MGR_ID NUMBER References MGR_ID column of the
EMPLOYEES table
Evaluate this SQL statement:
SELECT employee_id, e.department_id, department_name, salary
FROM employees e, departments d
WHERE e. department_id = d.department_id;
Which SQL statement is equivalent to the above SQL statement?
A. SELECT employee_id, department_id, department_name, salary
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments);
B. SELECT employee_id, department_id, department_name, salary
FROM employees
NATURAL JOIN departments;
C. SELECT employee_id, d.department_id, department_name, salary
FROM employees e
JOIN departments d
ON e.department _ id = d. department_id;
D. SELECT employee_id, department_id, department_name, Salary
FROM employees
JOIN departments
USING (e.department_id, d.department_id); Answer: C
Explanation:
This query shows correct JOIN ON clause syntax and provides equivalent to the above
InQcoLrsretactteAmnesnwt.ers
A: This statement will show data only for the EMPLOYEES table with records that hDaEvPeAdRepTaMrtmENenTtSIDtabfrloe,mnot join result of two tables.
B: NATURAL join selects rows from the tables that have equal values in all matched
oalmu
sn).sI(fstahmeecocloulmumnsn having the same names have different datatypes, an error is
rDe:tuTrhnerde. is incorrect usage of JOIN clause with USING keyword.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 112-114
Chapter 3: Advanced Data Selection in Oracle
QTLhAUeSEETSMTNPIAOtMaNbEl2eV1cA:onRtCaiHnsAtRhe2s(e25c)olumns:
SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6)
You need to display the employees who have not been assigned to any department. You write the SELECT statement:
SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP
WHERE DEPARMENT_ID = NULL; What is true about this SQL statement?
A. The SQL statement displays the desired results.
B. The column in the WHERE clause should be changed to display the desired results. C. The operator in the WHERE clause should be changed to display the desired results. D. The WHERE clause should be changed to use an outer join to display the desired
results.
Answer: C
Explanation:
The operator in the WHERE clause should be changed to display the desired results. Twhaenrtetoarseutbismtietus twe haevnayluoeuin place of NULL. Oracle provides this functionality with a sNpVecLia()l.fYuonuctcioanncoatlulesde operation equal with NULL, but you can achieve desired raefsteurlttsheusWinHg ENRVEL(c)lafusnec.tion
Incorrect Answers
A: The SQL statement will generate an error because you cannot use operation equal B:itThhNeUcoLlLu.mn in the WHERE clause should not be changed to display the desired rDe:suSlitnsc. e there is only one table used in this query you don't need to use outer join to dOiCspPlaIynttrhoedduecstiiorendtoreOsurlatcsl.e 9i: SQL Exam Guide, Jason Couchman, p. 31-32
Chapter 1: Overview of Oracle Databases
QUESTION 22:
Evaluate the SQL statement:
SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual;
What will be displayed?
A. 0
B. 1
C. 0.00
D. An error statement
Answer: A
Rreemsualint dweirllwbhee0n. xMiOs dDi(vxid,ye)dfubnycytiountcilanlcouflautrethsethr ewmhooldeunlumobfexr ,cdanefbineepdriondluocnegd. dxTixRtvopUisltaiNhonenCadt(ai)eoscfntiuh:mnecaitlnioptenregtcerirusniocnatoefs y. ROUND(x,y) rounds x to the decimal precision of y.
Incorrect Answers
B: Result will be 0, not 1.
C: Result will be 0, not 0.00 because MOD(1600,10) return 0 and all other functions
(rTetRurUnN0Calasnod.
ROUND)
D: There is no error in this statement.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 69-71
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 23:
Examine the description of the MARKS table: STD_ID NUMBER(4)
STUDENT_NAME VARCHAR2(30) SUBJ1 NUMBER(3)
SUBJ2 NUMBER(3)
SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects. Examine this SELECT statement based on the MARKS table:
SELECT subj1+subj2 total_marks, std_id
FROM marks
WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2) ORDER BY total_ marks;
What is the result of the SELECT statement?
A. The statement executes successfully and returns the student ID and sum of all marks foobrtaeiancehdsmtuodreentthwanhothe average mark in each subject.
B. The statement returns an error at the SELECT clause. C. The statement returns an error at the WHERE clause.
D. The statement returns an error at the ORDER BY clause. Answer: C
Explanation:
The statement returns an error at the WHERE clause because group function AVG() cWanHnEoRt bEeculasuedsei.nGthroeup functions can be used in SELECT clause and GROUP BY plearufoserm. Tdhaetya aolploewratyionuston several values in a column of data as though the column ofedreatoan. e collective group
Incorrect Answers
A: The statement does not execute successfully because an error will be generated. B: The statement returns an error at the WHERE, not at the SELECT clause.
D: The statement returns an error at the WHERE, not at the ORDER BY clause. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 122-125
Chapter 3: Advanced Data Selection in Oracle
QWUhiEchST/SIQOLN*2P4lu:s feature can be used to replace values in the WHERE
clause?
A. Substitution variables B. Replacement variables C. Prompt variables
D. Instead-of variables
E. This feature cannot be implemented through /SQL*Plus. Answer: A
Explanation:
Lexical substitution variables can be used to replace values in the WHERE clause. Incorrect Answers
B: There is no replacement variables SQL*Plus feature in Oracle.
C: There is no prompt variables SQL*Plus feature in Oracle.
D: There is no instead-of variables SQL*Plus feature in Oracle.
E: This feature is implemented in the SQL*Plus with lexical substitution variables. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 165-173
Chapter 4: Subqueries
QUESTION 25:
You want to display the titles of books that meet these criteria:
1. Purchased before January 21, 2001
2. Price is less then $500 or greater than $900
You want to sort the results by their data of purchase, starting with the most recently bWohuigchht sbtoaotekm. ent should you use?
A. SELECT book_title
FROM books
WHERE price between 500 and 900
AND purchase_date < '21-JAN-2001' ORDER BY purchase_date;
B. SELECT book_title
FROM books
WHERE price IN (500,900)
AND purchase_date < '21-JAN-2001' ORDER BY purchase date ASC;
C. SELECT book_title
FROM books
WHERE price < 500 or > 900
AND purchase_date < '21-JAN-2001'
OFWDRR.HSODEMERLREboCB(opTYkrisbpcoeuor<ckh_5at0ist0eleOdaRtepDriEceS>C;900)
AND purchase_date < '21-JAN-2001' ORDER BY purchase date DESC;
Answer: D
Explanation:
This statement provides required results. Incorrect Answers
A: This query will show books with price in range $500 and $900, not less then $500
oBr: gTrheiasteqrutehraynw$i9ll0s0h.ow books with prices exactly $500 or $900, not less then $500 or gCr:eTatheirstohradner$9w0i0ll. not show correct rows because of incorrect syntax in the WHERE
l uPseIn..troduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 56-66
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 26:
Which statement explicitly names a constraint? A. ALTER TABLE student_grades
ADD
FOREIGN KEY (student_id) REFERENCES students (student_id); B. ALTER TABLE student_grades
ADD CONSTRAINT NAME = student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id); C. ALTER TABLE student_grades
ADD CONSTRAINT student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id); D. ALTER TABLE student grades
ADD NAMED CONSTRAINT student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id); E. ALTER TABLE student grades
ADD NAME student_id_fk
FOREIGN KEY (student_id) REFERENCES students (student_id); Answer: C
Explanation:
This statement provides correct syntax to add a foreign key constraint to the existing
Ianbcloer.rect Answers
A: The ADD FOREIGN KEY is wrong construction to add a foreign key constraint to
Bh:eTehxeisAtinDgDtaCbOleN. STRAINT NAME is wrong construction to add a foreign key
cDEo:nTsthreaiAntDtDo tNheAeMxiEstDinCg OtaNblSeT. RAINT is wrong construction to add a foreign key
|
: The ADD NAME is wrong construction to add a foreign key constraint to the
|
COaobCnlssett.raint dto cthe nexistingclleeD9ai:taSbQasLeEOxbajmecGtsuide, Jason Couchman, p. 238-239
QUESTION 27:
Examine the SQL statements that creates ORDERS table: CREATE TABLE orders
(SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL STATUS VARCHARD2(10)
CHECK (status IN ('CREDIT','CASH')), PROD_ID_NUMBER
REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER,
PRIMARY KEY (order id, order date));
For which columns would an index be automatically created when you execute the
(CbohvoeoSseQtLwsot)atement?
A. SER_NO
B. ORDER_ID C. STATUS
D. PROD_ID
E. ORD_TOTAL
F. Composite index on ORDER_ID and ORDER_DATE Answer: A, F
Explanation:
Indexes are created automatically by Oracle to support integrity constraints that etynpfeosrcoefuintieqgureintyescso.nTshtreaitnwtos that enforce uniqueness are PRIMARY KEY and prNimIQarUyEkecyonosrtUraNinItQs.UWEhceonntshteraint is declared, a unique index to support the column's ucrneiaqtueedn,easnsdisalallvsoalues in all columns that were defined as part of the primary key or plNacIeQdUinEtocothnestirnadinetxa. re
Incorrect Answers
B: There will not be index for ORDER_ID column. C: There will not be index for STATUS column.
D: There will not be index for PROD_ID column.
E: There will not be index for ORD_TOTAL column.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 237-238
Chapter 5: Creating Oracle Database Objects
QUESTION 28:
YThoeu DcrEeaPtAedRTa MviEewNTca_lIlDedcEoMlumP_nDoEf PthTe_EVMUPtLhaOt YcoEnEtaSintasbtlhereise tchoelufomrneisgfnrokmeythtoe the
pDrMEimPAaLrROyTYkMeEyEN
ITaDSn,dtEaMblePsL:OYEE_NAME AND DEPARTMENT_NAME.
DEPARTMENT_ID column of the DEPARTMENTS table.
You want to modify the view by adding a fourth column, MANAGER_ID of
NthUe MEMBPELROdYatEaEtySpteabfrloesm.
How can you accomplish this task?
A. ALTER VIEW EMP_dept_vu (ADD manger_id NUMBER);
B. MODIFY VIEW EMP_dept_vu (ADD manger_id NUMBER); C. ALTER VIEW emp_dept_vu AS
SELECT employee_id, employee_name,
department_name, manager_id
FROM employee e, departments d
WHERE e.department _ id = d.department_id; D. MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id
FROM employees e, departments d
WHERE e.department _ id = d.department_id;
E. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id
FROM employees e, departments d
WHERE e.department _ id = d.department_id;
F. You must remove the existing view first, and then run the CREATE VIEW
ctoommodainfyd awvitihewa.new column list
Answer: E
Explanation:
When we want to alter the underlying data used in the definition of a view, we use the CVRIEEWATsEtatOemReRnEt.PWLhAeCnEa CREATE OR REPLACE VIEW statement is issued, Oracle wthialtl adriissreesgawrhdetnheiteernrocrounters the view that already exists with that name, and it will othveerowldrivteietwhewdiethfinthiteiodnefionrition for the new one.
Incorrect Answers
A: There is no ALTER VIEW command in Oracle.
B: There is no MODIFY VIEW command in Oracle. C: There is no ALTER VIEW command in Oracle.
D: There is no MODIFY VIEW command in Oracle.
F: You don't need to remove the existing view to create modified view. You are able to dOoRthRaEt PwLitAhCCERcEoAmTmEand.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 310-313
Chapter 7: Creating Other Database Objects in Oracle
For which two constraints does the Oracle Server implicitly create a unique index? (CUhoEoSsTeItOwNo.)29:
A. NOT NULL
B. PRIMARY KEY C. FOREIGN KEY D. CHECK
E. UNIQUE Answer: B, E
Explanation:
Indexes are created automatically by Oracle to support integrity constraints that etynpfeosrcoefuintieqgureintyescso.nTshtreaitnwtos that enforce uniqueness are PRIMARY KEY and prNimIQarUyEkecyonosrtUraNinItQs.UWEhceonntshteraint is declared, a unique index to support the column's ucrneiaqtueedn,easnsdisalallvsoalues in all columns that were defined as part of the primary key or plNacIeQdUinEtocothnestirnadinetxa. re
Incorrect Answers
A: Oracle will not implicitly create an unique index for the NOT NULL constraint.
C: Oracle will not implicitly create an unique index for the FOREIGN KEY constraint. D: Oracle will not implicitly create an unique index for the FOREIGN KEY constraint. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 237-238
Chapter 5: Creating Oracle Database Objects
QUESTION 30:
Which three SELECT statements displays 2000 in the format "$2,000.00"? (Choose three)
A. SELECT TO CNAR(2000, '$#,###.##') FROM dual;
B. SELECT TO CNAR(2000, '$0,00 0.00') FROM dual;
C. SELECT TO CNAR(2000, '$9,99 9.00') FROM dual;
D. SELECT TO CNAR(2000, '$9,999.99') FROM dual;
E. SELECT TO CNAR(2000, '$2,000.00')
FROM dual;
F. SELECT TO CNAR(2000, '$N,NNN.NN') FROM dual;
Answer: B, C, D
Explanation:
Only queries in answers B, C and D will show result as in the format "$2,000.00".
IAFEn:cOoraeccleteAernrrrsowr e"rOOsRA-0011448811::innvvaalid nnuummber ffoormat mooddel" will bbeeggeenneerraated..
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access Control in Oracle
QUESTION 31:
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEESEMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHARD2(25) LAST_NAME VARCHARD2(25) HIRE_DATE DATE
NEW EMPLOYEESEMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which UPDATE statement is valid?
A. UPDATE new_employees SET name = (Select last_name||
first_name
FROM employees
Where employee_id
=180)
WHERE employee_id = 180;
B. UPDATE new_employees SET name = (SELECT
last_name||first_name
FROM employees)
WHERE employee_id = 180;
C. UPDATE new_employees SET name = (SELECT last_name||
first_name
FROM employees
WHERE employee_id
=180)
WHERE employee_id =(SELECT employee_id
FROM new employees
D. UPDATE new_employees SET name = (SELECT last name||
first_name
FROM employees WHERE employee_id= (SELECT employee_id FROM new_employees)) WHERE employee_id
=180;
Answer: A
18u0b,-qsoueurpydiantethwisilalnbsewseurcwceilslsrfeutlu. rWn hoenne sruobw-qvuaelurie,scaorneclaitnekneadtetdo ftihrestpaanrdenltasbtyname qfoquxrueptralhyalienteyaextmcpioepcml:tospyaoerneislwyoniotshn,etIhDreowpaorefndtata from the sub-query.
Incorrect Answers
B: Sub-query will return concatenated first and last name for ALL records from the tcaabulseeEaMn ePrLroOrYfoErEtShe. Iptawreinllt query, because it expects one only one row.
C: Last WHERE statement in this query will generate error because sub-query returns mD:uSltEi-LroEwCrTessutaltt.ement for the NEW_EMPLOYEES table in this query will generate rertruornbsemcauulstie-rsouwb-rqeuseurlyt.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 150-156
Chapter 4: Subqueries
QUESTION 32:
Examine the structure of the EMPLOYEES, DEPARTMENTS, and LOCATIONS
tEaMblePsL.OYEES
EMPLOYEE_ID NUMBER NOT NULL,
Primary Key
EMP_NAME VAR CHAR2
(30)
JOB_ID VAR CHAR2 (20) SALARY NUMBER
MGR_ID NUMBER References
column
DEPARTMENT_ID NUMBER Foreign key to
EMPLOYEE_ID
DEPARTMENT_ID column of the DEPARTMENTS table
DEPARTMENTS
DEPARTMENT_ID NUMBER NOT NULL, Primary
Key
DEPARTMENT_NAME VARCHAR2 (30)
MGR_ID NUMBER References NGR_ID
the EMPLOYEES table
LOCATION_ID NUMBER Foreign key to column of the
LOCATION_SItDableNUMBER NOT NULL, PrimKareyy
column of
LOCATION_ID
CITY VARCHAR2
|30)
Which two SQL statements produce the name, department name, and the city of all the emmoprelotyheens1w0h0o00e?ar(nChoose two)
A. SELECT emp_name, department_name, city
FROM employees e JOIN departments d USING (department_id) JOIN locations 1
USING (location_id) WHERE salary > 10000;
B. SELECT emp_name, department_name, city
FROM employees e, departments d, locations 1
JOIN ON (e.department_id = d.department id) AND (d.location_id =1.location_id)
AND salary > 10000;
C. SELECT emp_name, department_name, city
FROM employees e, departments d, locations 1
WHERE salary > 10000;
D. SELECT emp_name, department_name, city
FROM employees e, departments d, locations 1
WHERE e.department_id = d.department_id
AND d.location_id = 1.location_id
AND salary > 10000;
E. SELECT emp_name, department_name, city
FROM employees e
NATURAL JOIN departments, locations
WHERE salary > 10000; Answer: B, D
Explanation:
These statements show correct syntax and semantics to receive correct results. Incorrect Answers
A: JOIN ON keywords need to be used to build correct query.
C: This query will built Cartesian product because there is no join conditions in
WE:HNEARTEURclAauLseJOtoINjoiins atajboliens.between two where Oracle joins the tables according to
thabelceoslsuhmarni(nsg) ithnethseamtweoname. It is required to add one more NATURAL JOIN clause
tOoCjoPinInatrdoddituioctnioalntatoblOe.racle 9i: SQL Exam Guide, Jason Couchman, p. 98-118
Chapter 3: Advanced Data Selection in Oracle
QEExMUaPEm_SiInTDeIOtNhNeUdM3e3Bs:cEriRp(ti4o)nNoOf TtheNEUMLLPLOYEES table:
LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2) JOB_CAT VARCHAR2(30) SALARY NUMBER(8,2)
Which statement shows the department ID, minimum salary, and maximum salary paid onf tthaetmdeinpiamrtummensta,loarnylyis less then 5000 and the maximum salary is more than 15000?
A. SELECT dept_id, MIN(salary(, MAX(salary) FROM employees
WHERE MIN(salary) <5000 AND MAX (salary) > 15000; B. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
WHERE MIN(salary) < 5000 AND MAX(salary) > 15000
GROUP BY dept_id;
C. SELECT dept_id, MIN(salary), MAX(salary) FROM employees
HAVING MIN(salary) <5000 AND MAX (salary) > 15000; D. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id
HAVING MIN (salary) < 5000 AND MAX(salary) E. SELECT dept_id, MIN(salary), MAX(salary) FROM employees
GROUP BY dept_id, salary
HAVING MIN (salary) <5000 AND MAX (salary) > 15000; Answer: D
Explanation:
This SELECT statement shows correct result. Incorrect Answers
A: To provide correct data statement needs also GROUP BY clause. B: This statement will not provide correct results.
C: HAVING clause can be used only in conjunction with GROUP BY clause. E: You need only grouping by department, not by salary.
QUESTION 34:
Examine the structure if the EMPLOYEES table: Column name Data Type Remarks
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMSJOAMGBLPR__N_NDIUDAMVMNABUREEMCRVHABARERC2HR(A2e0fRe)2rNe(n3Oc0Te)sNEUMLPLLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID
column of the DEPARTMENTS table
You need to create a view called EMP_VU that allows the user to insert rows through tshtaetevmieewn.tW, whhicehn SusQeLd to create the EMP_VU view, allows the user to insert rows?
A. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, department_id
FROM employees
WHERE mgr_id IN (102, 120); B. CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name, job_id department_id
FROM employees
WHERE mgr_id IN (102, 120); C. CREATE VIEW emp_Vu AS
SELECT department_id, SUM(sal) TOTALSAL FROM employees
WHERE mgr_id IN (102, 120)
GROUP BY department_ id;
D. CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name, job_id, DISTINCT department_id
FROM employees;
Answer: B Explanation:
This statement will create view that can be used to change tables in underlying table
tihn
rI
rcoluudgehsspimrimplaeryvikeewys,.NItOT NULL column and foreign key to avoid constraint sctorircreticotnAs.nswers
A: This statement does not include JOB_ID column that cannot be NULL. In general, dlelfcinoendstoraninthtereusntrdiecrtiloyninsg table also apply to modifying data via the view. For uxnadmerplylein, gyotaubcleanv'tiaadadvdieawtatthoatanviolates the table's primary key constraint.
C: You cannot update a column of an underlying table if the simple view use a single- rcoowlumfunn.ction to define the
D: You may not insert, update, or delete records data on the table underlying the simple vstiaetwemifetnhtecSreEaLtinEgCtThe view contains a GROUP BY clause, GROUP function, or DOICSPTInNtrCoTducclatiuosne.to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 298-299
Chapter 7: Creating Other Database Objects in Oracle
QUESTION 35:
TSSEThe
SDETESUNTDETER_NI_DETN_GDURMDAABDTEERS(1ta2b)le has these columns:
GPA NUMBER(4,3)
The registrar has asked for a report on the average grade point average (GPA) for steumdesnttesrsenthroaltledndduinritnhge year 2000. Which statement accomplish this?
A. SELECT AVERAGE(gpa) FROM student_grades
WHERE semester _ end > '01-JAN-2000' and semester end <31-DEC-2000'; B. SELECT COUNT(gpa)
FROM student grades
WHERE semester _ end > '01-JAN-2000' and semester end <31-DEC-2000'; C. SELECT MIN(gpa)
FROM student grades
WHERE semester _ end > '01-JAN-2000' and semester end <31-DEC-2000'; D. SELECT AVG(gpa)
FROM student_grades
WHERE semester _ end BETWEEN '01-JAN-2000' and '31-DEC-2000'; E. SELECT SUM(gpa)
FROM student grades
WHERE semester _ end > '01-JAN-2000' and semester end <31-DEC-2000'; F. SELECT MEDIAN(gpa)
FROM student_grades
WHERE semester _ end > '01-JAN-2000' and semester end <31-DEC-2000'; Answer: D
Explanation:
Statement in this answer will show correct result, using function AVG(). This function tsainkgeslethceolvuamluneosnfoarllarows returned by the query and calculates the average value for IhnactocrroelcutmAnn.swers
A: There is no AVERAGE() function in Oracle.
B: COUNT() will calculate number of row, not an average grade point.
C: MIN() function will calculate minimum grade for all students enrolled during
2e0m00e.sters that end in the year
E: SUM() will calculate sum of all grade points for all students enrolled during
2e0m00e.sters that end in the year
F: There is no MEDIAN() function in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 123-125
Chapter 3: Advanced Data Selection in Oracle
QUESTION 36:
Examine the structure if the EMPLOYEES and NEW EMPLOYEES tables: EFLEIMARSPSTLT_O_NNYAAEMME_SEEIDVVAANRRUCCMHHBAAERRR22((P22r55i)m) ary Key
HIRE_DATE DATE NEW EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which MERGE statement is valid?
A. MERGE INTO new_employees c
USING employees e
ON (c.employee_id = e.employee_id) WHEN MATCHED THEN
UPDATE SET
c.name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name ||',
'||e.last_name);
B. MERGE new_employees c
USING employees e
ON (c.employee_id = e.employee_id) WHEN EXIST THEN
UPDATE SET
c.name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name ||',
'||e.last_name);
C. MERGE INTO new employees c
USING employees e
ON (c.employee_id = e.employee_id) WHEN EXISTS THEN
UPDATE SET
c.name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name ||',
'||e.last_name);
D. MERGE new_employees c
FROM employees e
ON (c.employee_id = e.employee_id) WHEN MATCHED THEN
UPDATE SET
c.name = e.first_name ||','|| e.last_name
WHEN NOT MATCHED THEN
INSERT INTO new _ employees VALUES (e.employees_id, e.first_name ||','||
e.last_name);
Correct syntax for the MERGE command is MERGE INTO table1 USING table2 on
AE(joxnipsnlw_anecroa:tniAdoint:ion) WHEN
MATCHED UPDATE SET col1 = value WHEN NOT MATCHED INSERT (column_viastl)uevsa)l.ues
Incorrect Answers
B: WHEN EXIST THEN clause cannot be used in the MERGE statement. C: WHEN EXIST THEN clause cannot be used in the MERGE statement. D: FROM clause cannot be used in the MERGE statement.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 273-275
Chapter 6: Manipulating Oracle Data
QUESTION 37:
Which two are true about aggregate functions? (Choose two.)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the SELECT clause and ianStEheLEWCHTEsRtaEtecmlaeunste. of
C. You can mix single row columns with aggregate functions in the column list of a grEoLuEpiCnTg ostnattehme esnintgblye row columns.
D. You can pass column names, expressions, constants, or functions as parameters to aEn. Yagogurceagnatuesfeunagctgiroeng.ate functions on a table, only by grouping the whole table as one sFi.nYgoleugcraonunpo.t group the rows of a table by more than one column while using aggregate functions.
Answer: C, D
Explanation:
It is possible to mix single row columns with aggregate functions in the column list of aSELECT statement by grouping on the single row columns. Also it is acceptable to pcoaslusmn names, expressions, constraints, or other functions as parameters to an fugngcrteigoant.e
Incorrect Answers
A: You cannot use aggregate functions in any clause of a SELECT statement. For ewxiathmaplWe,HthEeNy csatantneomtebnet.used
B: It is not possible to use aggregate functions in the WHERE clause of a SELECT
sutsaetdemweintht.aBHutAtVheINy Gcacnlabuese used after the GROUP BY clause, for example.
E: You don't need to group the whole table as one single group.
F: It is possible to group more than one column while using aggregate functions. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 122-135
Chapter 3: Advanced Data Selection in Oracle
QUESTION 38:
Examine the data of the EMPLOYEES table.
EMPLOYEES (EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers
aEnMdPreLfOerYs EtoEt_hIeD)
EMPLOYEE_ID
EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 HR_MGR 5000
106 Bryan 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
Which statement lists the ID, name, and salary of the employee, and the ID and name omfatnhaegeemr, pfoloryaelel 'tshe employees who have a manager and earn more than 4000?
A. SELECT employee_id "Emp_id", emp_name "Employee", salary,
employee_id "Mgr_id", emp_name "Manager"
FROM employees
WHERE salary > 4000;
B. SELECT e.employee_id "Emp_id", e.emp_name "Employee", e.salary,
m.employee_id "Mgr_id", m.emp_name "Manager"
FROM employees e, employees m WHERE e.mgr_id = m.mgr_id AND e.salary > 4000;
C. SELECT e.employee_id "Emp_id", e.emp_name "Employee", e.salary,
m.employee_id "Mgr_id", m.emp_name "Manager" FROM employees e, employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000;
D. SELECT e.employee_id "Emp_id", e.emp_name "Employee", e.salary,
m.mgr_id "Mgr_id", m.emp_name "manager" FROM employees e, employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000;
EemF.R.s.maSOlEagMrLry_E,eidCmT"pMleo.gyerem_eipsdl"eo,ymeme.e_pmildopy"_Eenemasmpm_ei"dM", aen.eamgepr_"name "Employee",
WHERE e.employee_id = m.employee_id
AND e.salary > 4000; Answer: C
Explanation:
This statement lists the ID, name, and salary of the employee, and the ID and name of fhoer aelml tphleoyeeme'pslomyaeneasgwerh,o have a manager and earn more than 4000
Incorrect Answers
A: This statement does not check does employee have a manager or not, so it will not
r:oUvisdaegecoorfre"cet.mregsru_litd.
= m.mgr_id" condition is wrong to achieve required result.
D: This statement uses "m.mgr_id" to show manager's manager, not employ's manager. E: Usage of "WHERE e.employee_id = m.employee_id" condition is wrong to achieve
rOeCquPirIendtrroedsuuclt.ion to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 118-122
Chapter 3: Advanced Data Selection in Oracle
QUESTION 39:
In a SELECT statement that includes a WHERE clause, where is the GROUP BY
cstlatuesme epnlat?ced in the SELECT
A. Immediately after the SELECT clause
B. Before the WHERE clause
C. Before the FROM clause
D. After the ORDER BY clause
E. After the WHERE clause
Answer: E Explanation:
The GROUP BY clause can be place only after the WHERE clause, or after FROM
cWlaHusEeRiEf tchlearuesiesinoththeestatement. Incorrect Answers
A: It is not possible to place the GROUP BY clause immediately after the SELECT
Bl:auItseis. not possible to place the GROUP BY clause before the WHERE clause, it can bCe: Idtoinsenont lpyoasfstiebrleitt.o place the GROUP BY clause before the FROM clause.
D: It is not possible to place the GROUP BY clause after the ORDER BY clause. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 56-67
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 40:
EThPeMUASDENTESUNTMDETEBR_NI_EDERTN_(4GDU,3RMD)AABDTEEERS(1ta2b)le has these columns:
The register has requested a report listing the students' grade point averages (GPA), pooritnetdafvreormagheigtoheloswt gersatdweithin each semester, starting from the earliest date. Which sthtaiste?ment accomplishes
A. SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY semester_end DESC, gpa DESC; B. SELECT student_id, semester_end, gpa FROM student_grades
ORDER BY semester _end, ASC,gpa ASC; C. SELECT student_id, semester_end, gpa FROM student_grades
ORDER BY semester _end, gpa DESC;
D. SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY gpa DESC,semester_end DESC; E. SELECT student_id, semester_end, gpa FROM student_grades
Answer: C
Explanation:
This answer shows correct syntax and semantics to receive desired result. Incorrect Answers
A: Semesters will be sorted started from the oldest date, not the earliest.
B: GPA data will be sorted in ascending order, what is opposite to our task.
D: Semesters will be sorted started from the oldest date, not the earliest. Only doifffceorleunmcneswiniththaenOswReDr AERisBoYrdcelrause.
E: This query has wrong order of columns to sort: results need to be sorted first by peominetsatevre,rtahgaen. by grade
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 56-61
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 41:
The ORDERS table has these columns: ORDER_ID NUMBER(4) NOT NULL CUSTOMER_ID NUMBER(12) NOT NULL ORDER_TOTAL NUMBER(10,2)
The ORDERS table tracks the Order number, the order total, and the customer to wWhhoicmhtthweoOsrtdateermbenlotsngrest.rieve orders with an inclusive total that ranges between
1(C0h0o.0o0seantwd o2.0)00.00 dollars?
AFRRA. SONEMGLEorCOdTeNrcsoursdtoerm_erto_tidal, (o1r0d0erA_iNd,Dor2d0e0r0_)toItNalCLUSIVE;
B. SELECT customer_id, order_id, order_total
FROM orders
HAVING order _ total BETWEEN 100 and 2000; C. SELECT customer_id, order_id, order_total FROM orders
WHERE order _ total BETWEEN 100 and 2000; D. SELECT customer_id, order_id, order_total FROM orders
WHERE order_total>= 100 and <= 2000;
E. SELECT customer_id, order_id, order_total
FROM orders
WHERE order_total>= 100 and order_total <= 2000; Answer: C, E
Explanation:
Answers C and E provide correct resu lts to show. You can use BETWEEN or doatma.parison operations to retrieve
Incorrect Answers
A: There is no RANGE ON or INCLUSIVE keyword in Oracle.
B: HAVING clause can be use only in conjunction with the GROUP BY clause. D: Syntax "order_total >= 100 and <= 2000" is incorrect.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 61-67
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 42:
Examine the data in the EMPLOYEES and EMP_HIST tables: EMPLOYEES
EMPLOYEE_ID
NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 IT_ADMIN 5000
106 Smith 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
1E112M0P BRHoaISvbiT 4200 11E0X_SDAIR_DI8R0006500
EMPLOYEE_ID NAME JOB_ID SALARY
101 Smith SA_CLERK 2000
103 Chris IT_CLERK 2200
104 John HR_CLERK 2000
106 Smith AD_ASST 3000
108 Jennifer HR_MGR 4500
The EMP_HIST table is updated at the end of every year. The employee ID, name, job IeDxi,satinndgseamlaprylooyfeeeaacrhe modified with the latest data. New employee details are added to tWhehitcahblset.atement accomplishes this task?
A. UPDATE emp_hist
SET employee_id, name, job_id, salary = (SELECT employee_id, name, job_id, salary FROM employees)
WHERE employee_id IN (SELECT employee_id FROM employees);
B. MERGE INTO emp_hist eh
USING employees e
ON (eh.employee_id = e.employee_id) WHEN MATCHED THEN
UPDATE SET eh.name = e.name, eh.job_id = e.job_id,
eh.salary = e.salary
WHEN NOT MATCHED THEN
INSERT VALUES (e.employee id, e.name, e.job id, e.salary);
C. MERGE INTO emp_hist eh
USING employees e
ON (eh.employee_id = e.employee_id) WHEN MATCHED THEN
UPDATE emp hist SET eh.name = e.name, eh.job_id = e.job_id,
eh.salary = e.salary
WHEN NOT MATCHED THEN INSERT INTO emp_hist
VALUES (e.employees_id, e.name, e.job_id, e.salary); D. MERGE INTO emp_hist eh
|
hSING employees e
P.HjsDoaEbAlNa_TriM=eAmeTe..psCjao_HlbhaE_irsyiDtdS,
WHEN NOT MATCHED THEN INSERT INTO emp_hist
VALUES (e.employees_id, e.name, e.job_id, e.salary); Answer: B
Explanation:
This task can be done using the MERGE command. Correct syntax for the MERGE ctaobmlem1aUnSdIiNs GMtEaRblGe2EoInNT(jOoin_condition) WHEN MATCHED UPDATE SET col1 = vMaAluTeCWHHEEDNINNSOETRT (column_list) values (column_values).
Incorrect Answers
A: MERGE command can handle this task, not UPDATE: new employee details will nCo:tTbheisadstdaetedmtoentht ewtoaubllde by correct if UPDATE SET is syntax used, not UPDATE
it bulesu_anlaUmPeDSAETEsycnotmaxmaasnd.
D: "ON condition" clause of the MERGE command is absent.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 273-275
Chapter 6: Manipulating Oracle Data
QUESTION 43:
Which two statements about subqueries are true? (Choose two.) A. A single row subquery can retrieve data from only one table.
B. A SQL query statement cannot display data from table B that is referred to in its
sinucblquudeerdy,inunthleesms taaibnlequBerisy's FROM clause.
C. A SQL query statement can display data from table B that is referred to in its staubblqeuBeriyn, witsitohwount FinRcOluMdincglause.
D. A single row subquery can retrieve data from more than one table.
E. A single row subquery cannot be used in a condition where the LIKE operator is uF.seAdmfourltciopmle-praorwisosnu.bquery cannot be used in a condition where the LIKE operator is used for comparison.
Answer: B, D
Explanation:
A SQL query statement cannot display data from table B that is referred to in its sub- qinucelruyd,eudnilnestshteabmleaiBn qisuery's FROM clause. And a single row sub-query can retrieve dtaabtlaef.rom more than one
Incorrect Answers
A: A single row sub-query can retrieve data from more than one table.
C: A SQL query statement cannot display data from table B that is referred to in its sinucbl-uqdueedryi,nutnhleesmsatianblqeuBeryis's FROM clause.
E: A single row sub-query can be used in a condition where the LIKE operator is used
F: A multiple-row sub-query can be used in a condition where the LIKE operator is
for dcomparison. iunseotronie.Os racle 9i: SQL Exam Guide, Jason Couchman, p. 144-150
OshCeaPpftIeonrtr4co:odmSuucpbtaiqor
QUESTION 44:
Examine the data of the EMPLOYEES table.
EMPLOYEES (EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers aEnMdPreLfOerYs EtoEt_hIeD)
EMPLOYEE_ID
EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 HR_MGR 5000
106 Bryan 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
Evaluate this SQL statement:
SELECT e.employee_id "Emp_id", e.emp_name "Employee", e.salary, m.employee_id "Mgr_id", m.emp_name "Manager"
FROM employees e, employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000;
What is its output? A.
EMP_id EMPLOYEE SALARY Mgr_id Manager
------- ---------- --------- ------------- --------------
110 Bob 8000 Bob
120 Ravi 6500 110 Ravi
108 Jennifer 6500 110 Jennifer
103 Chris 4200 120 Chris
105 Diana 5000 108 Diana
B.
EMP_id EMPLOYEE SALARY Mgr_id Manager
------- ---------- --------- ------------- --------------
120 Ravi 6500 110 Bob
108 Jennifer 6500 110 Bob
103 Chris 4200 120 Ravi
1-CE-0M-.-5-P-D-_i-ad-n--Ea-M-5-0-P-0-L0-O-1-Y-0-8E--EJ-e-Sn--nA-i-Lf-e-Ar--R--Y--M- -g--r-_-i-d--M---a--n-ager
110 Bob 8000
120 Ravi 6500 110 Bob
108 Jennifer 6500 110 Bob
103 Chris 4200 120 Ravi
105 Diana 5000 108 Jennifer
D
EMP_id EMPLOYEE SALARY Mgr_id Manager
------- ---------- --------- ------------- --------------
110 Bob 8000 110 Bob
120 Ravi 6500 120 Ravi
108 Jennifer 6500 108 Jennifer
103 Chris 4200 103 Chris
105 Diana 5000 105 Dina
E. The SQL statement produces an error. Answer: B
Explanation:
This statement lists the ID, name, and salary of the employee, and the ID and name of fhoer aelml tphleoyeeme'pslomyaeneasgwerh,o have a manager and earn more than 4000
Incorrect Answers
A: This output will be provided by different query. C: This output will be provided by different query. D: This output will be provided by different query.
E: This SQL query will not produce error, it will show results as in answer B. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 118-122
Chapter 3: Advanced Data Selection in Oracle
QUESTION 45:
You added a PHONE_NUMBER column of NUMBER data type to an existing EMPLOYEES table.aTlrheeady contains records of 100 employees. Now, you want to nnutmerbtehres opfhoenaceh of the 100 employees into the table.
Some of the employees may not have a phone number available. Which data manipulation operation do you perform?
A. MERGE B. INSERT C. UPDATE D. ADD
E. ENTER
F. You cannot enter the phone numbers for the existing employee records.
AExnpslwanera:tiCon:
To update information you need to use UPDATE command. Incorrect Answers
A: Since you don't have to add any records to the table you don't need the MERGE Bo:mSminacnedy.ou don't have to add any records to the table you don't need the MERGE cDo:mTmhearnedi.s no ADD command in Oracle.
E: There is no ENTER command in Oracle.
F: You can enter the phone numbers for the existing employee records by using
OU DPAInTtErocdoumctmioanntdo.Oracle 9i: SQL Exam Guide, Jason Couchman, p. 267-270
Chapter 3: Manipulating Oracle Data
QUESTION 46:
In which case would you use a FULL OUTER JOIN? A. Both tables have NULL values.
B. You want all unmatched data from one table. C. You want all matched data from both tables.
D. You want all unmatched data from both tables. E. One of the tables has more data than the other.
F. You want all matched and unmatched data from only one table. Answer: D
Explanation:
Oracle9i also makes it possible for you to easily execute a full outer join, including all rthecaot rwdosuflrdomhatvheebtaebenledsisplayed if you had used both LEFT OUTER JOIN or RIGTH InUcoTrEreRctJAOnIsNwcelrasuses.
A: You will not use a FULL OUTER JOIN if both tables have NULL values.
B: You will not use a FULL OUTER JOIN if you want all unmatched data from one tJaObIlNe. oOrnRlyIGLTEHFTOOUUTTEERRJOIN needs to be used.
C: You want all unmatched, not matched, data from both tables.
E: It is not a criterion to use a FULL OUTER JOIN if one of the tables has more data tFh:aYnotuhewoatnhtear.ll unmatched, not matched data or a combination of matched and
utanbmleast,cnhoetdodnaetat,abfrloem.
both
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 109-118
Chapter 3: Advanced Data Selection in Oracle
QUESTION 47:
Which two statements accurately describe a role? (Choose two.) A. A role can be given to a maximum of 1000 users.
BCED. APrrriovslieelreicgcsaeaansnnahharaaemvvegeidavacegmcnreaotsxuosipmatourfoamlremeolbafyxt1eiud0ms0puinrpmigrviiovtlhfielege1egC0seRrstohEclaeAotsnT.ctEaninRbeOedLginEraisnt.taetdemtoetnhte. user.
F. A user can have access to several roles, and several users can be assigned the same role.
Answer: D, F
Explanation:
Privileges can be given to a role by using the CREATE ROLE statement. A user can hroalvees,aacncdesssetvoersaelvuesraelrs can be assigned the same role.
Incorrect Answers
A: There is no limitation to a 1000 users for one role in Oracle. B: There is no limitation to a 10 roles for one user in Oracle.
C: There is no limitation to a 1000 privileges for one role in Oracle.
E: The role can act as a named group of privileges to execute certain tasks. But palrli.vileges can be not related at
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 365-373
Chapter 8: User Access Control in Oracle
QUESTION 48:
What is necessary for your query on an existing view to execute successfully?
A. The underlying tables must have data.
B. You need SELECT privileges on the view.
C. The underlying tables must be in the same schema.
D. You need SELECT privileges only on the underlying tables. Answer: B
Explanation:
To query an existing view you need to have SELECT privileges on the view. Incorrect Answers
A: The underlying tables can be without data: view will work correctly in that case. C: The underlying tables can be in different user schema.
D: You need SELECT privileges not only on the underlying tables, but on the view
aOlsCoP. Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-359
Chapter 8: User Access Control in Oracle
QUESTION 49:
The EMP table has these columns: ENAME VARCHAR2(35)
SALARY NUMBER(8,2)
Management wants a list of names of employees who have been with the company for
HmWIohRrieEcht_hDSaQnATLfiEvsetDaytAemTarEesn. t displays the required results?
A. SELECT ENAME FROM EMP
WHERE SYSDATE-HIRE_DATE >5; B. SELECT ENAME
FROM EMP
WHERE HIRE_DATE-SYSDATE >5; C. SELECT ENAME
FROM EMP
WHERE (SYSDATE_HIRE_DATE)/365 >5; D. SELECT ENAME
FROM EMP
WHERE (SYSDATE_HIRE_DATE)*/365 >5; Answer: C
Explanation:
Expression SYSDATE-HIRE_DATE will show number of days after date of hiring dmivpidloeyrees,usltooyfoeuxpnreeesdsitoon on 365 and this compare result with 5.
Incorrect Answers
A: This query returns all employees who were hired more than 5 days ago.
B: This query will not return any record because result of HIRE_DATE-SYSDATE
nxupmrebsesri.on will be negative
D: You need to divide, not to multiply, SYSDATE-HIRE_DATE on the number of days iOnC1PyIenatrr.oduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 81-85
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 50:
Examine the data in the EMPLOYEES table. EMPLOYEES
EMPLOYEE_ID
EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 IT_ADMIN 5000
106 Smith 40 110 AD.ASST 3000
1120
BReoenvbniif4e20r0 3101E0K11_S0DAIR_HDRI8_R0D0I06R5060500
On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID
oreffmerasntoagtehresEanMdPLOYEE_ID. The JOB_ID column is a NOT NULL column. Evaluate this DELETE statement:
DELETE employee_id, salary, job_id
FROM employees
WHERE dept_id = 90;
Why does the DELETE statement fail when you execute it?
A. There is no row with dept_id 90 in the EMPLOYEES table.
B. You cannot delete the JOB_ID column because it is a NOT NULL column. C. You cannot specify column names in the DELETE clause of the DELETE sDta. tYeomuencat.nnot delete the EMPLOYEE_ID column because it is the primary key of the
table. Answer: C
Explanation:
You cannot specify column names in the DELETE clause of the DELETE statement. Sisy: nDtaExLoEfTtEhisFRcoOmMmatanbdle_name WHERE column_name = value.
Incorrect Answers
A: Error in this statement is related with list of columns in the DELETE statement, not
with
bEsPenTc_eIDof=ro9w0 in the EMPLOYEES table.
B: Error in this statement is related with list of columns in the DELETE statement, not wcointhstNraOinTt oNnUthLeLJOB_ID column.
D: Error in this statement is related with list of columns in the DELETE statement, not wcointhsttrhaeinptroimn athrye EkeMyPLOYEE_ID column.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 270-273
Chapter 6: Manipulating Oracle Data
QUESTION 51:
Evaluate these two SQL statements: SELECT last_name, salary , hire_date FROM EMPLOYEES
ORDER BY salary DESC;
SELECT last_name, salary, hire_date
FROM EMPLOYEES ORDER BY 2 DESC; What is true about them?
A. The two statements produce identical results. B. The second statement returns a syntax error.
C. There is no need to specify DESC because the results are sorted in descending order
D. The two statements can be made to produce identical results by adding a column
abAyndefault.ndsaSlaQrLy cstoaltuemnent. inl atshswefeosrer:ctAhoe
Explanation:
These two statements produce identical results, because it is possible even to use
ncoulmumbenrsptoosiitniodnic
tehetrheeOracle should order the output from a statement.
Incorrect Answers
B: Second statement is correct and it will not return a syntax error. C: The results are sorted in ascending order by default.
D: There is no corrections need to be made for the statements. They will return iOdCenPtiIcnatlroredsuuclttiso.n to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 56-61
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 52:
You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?
A. SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY') FROM dual;
B. SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYYY')
FROM dual;
C. SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;
D. SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
E. SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
Answer: C Explanation:
This answer is correct: "Day" shows the day spelled out, "DD" shows the two-digit
dmaoten,th"Mspoenlltehd" opurot,v"idYeYs YthYe " shows the four-digit year. "FMDay" is special format mspaasckestobesutwppeerenssthees nthaemeextorfathe day and the number of the date.
Incorrect Answers
A: This statement will return an error because of inappropriate usage of the
BO: I_nDcoArTreEc(t)ffournmcatitomn.ask "DY" is used to show the number of the day.
D: Incorrect format mask "DY" is used to show the name of the day and format mask
"thDeDnDum" ibseur soefdthtoe sdhaoyw.
E: Incorrect format mask "DY" is used to show the name of the day and format mask "thDeDnDum" ibseur soefdthtoe sdhaoyw. Also this statement will return an error because of inappropriate uTsOa_gDe oAfTtEh(e) function.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 87-88
CQhUaEptSeTr I2O: LNim53it:ing, Sorting, and Manipulating Return Data
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)
Which statement finds the rows in the CUSTOMERS table that do not have a postal code?
A. SELECT customer_id, customer_name
FROM customers
WHERE postal_code CONTAINS NULL; B. SELECT customer_id, customer_name FROM customers
WHER postal_code = ' ';
C. SELECT customer_id, customer_name
FROM customers
WHERE postal _ code IS NULL;
D. SELECT customer_id, customer_name
FROM customers
WHERE postal code IS NVL;
E. SELECT customer_id, customer_name
FROM customers
WHERE postal_code = NULL; Answer: C
Explanation:
This statement returns the rows in the CUSTOMERS table that do not have a postal
c decek. TNhUe LcoLrrveacltuesysnistauxstaoge of "IS NULL" clause.
Incorrect Answers
A: "CONTAINS NULL" is incorrect clause in Oracle.
B: This satement will just check if postal code equals to string' ';
D: Usage of "IS NVL" is incorrect in Oracle. But there is a function NVL() you can vsaeluteos.process NULL
E: You can not use equal comparison to check whether value is NULL or not. Use
"IoSnsNtrOucTtiNonU"LILS"NtoULdoL"thoart.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 31-32
Chapter 1: Overview of Oracle Databases
QWUhiEchSTSIEOLNEC5T4:statement will the result 'ello world' from the string 'Hello
World'?
A. SELECT SUBSTR ('Hello',) FROM dual;
B. SELECT INITCAP (TRIM ('Hello World',1,1) FROM dual; C. SELECT LOWER (SUBSTR ('Hello World',1,1) FROM dual; D. SELECT LOWER (SUBSTR ('Hello World',2,1) FROM dual;
E. SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual; Answer: E
Explanation:
This statement will return correct result because function TRIM() will trim letter 'H' in fhuenc'HtioelnloLWOWorlEdR' a(n) dwill return data in string in lowercase..
Incorrect Answers
A: This statement will return sub-string starting at the character in position number Y Bo:tThheeernedi.s incorrect syntax in the TRIM() function.
C: There is incorrect syntax in that statement because of absence right bracket. D: There is incorrect syntax in that statement because of absence right bracket. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 67-69
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 55:
Evaluate this SQL statement:
SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct)
+ (s.sales amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s
WHERE e.employee_id = s.emp_id;
What will happen if you remove all the parentheses from the calculation?
A. The value displayed in the CALC_VALUE column will be lower. B. The value displayed in the CALC_VALUE column will be higher.
C. There will be no difference in the value displayed in the CALC_VALUE column. D. An error will be reported.
Answer: C Explanation:
There will be no difference in the value displayed in the CALC_VALUE column
cearautsieonnsotoraruitshamgeetoifc alias for the calculated expression in the SELECT clause will
napopt ecaruasnecceh. ange the value
Incorrect Answers
BD:: There wisiinlllobeerrnoordinifftehrriesensctaeteinmtehnet.vallue diispllayed in the CALC_VALUE collumn..
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 29-34
Chapter 1: Overview of Oracle Databases
QUESTION 56:
From SQL*Plus, you issue this SELECT statement: SELECT*
FROM order;
You use this statement to retrieve data from a data table for . (Choose all that apply)
A. Updating B. Viewing C. Deleting D. Inserting
E. Truncating
Answer: B, D Explanation:
You can use SELECT statement to display and to insert data into different table. Incorrect Answers
A: You cannot update data with SELECT statement. Update command is used for this pCu:rYpousec.annot delete data with SELECT statement. Delete command is used for this pEu: rYpoousec.annot truncate data with SELECT statement. Truncate command is used for tOhCisPpIunrtprosdeu.ction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 20-21
Chapter 1: Overview of Oracle Databases
QUESTION 57:
You need to create a view EMP_VU. The view should allow the users to manipulate the reemcoprlodyseoefsotnhlayt are working for departments 10 or 20.
Which SQL statement would you use to create the view EMP_VU?
A. CREATE VIEW emp_vu AS SELECT *
FROM employees
WHERE department _ id IN (10,20); B. CREATE VIEW emp_vu AS SELECT *
FROM employees
WHERE department_id IN (10,20) WITH READ ONLY;
CSWFER.HLCOERMRCEETeAmTd*peEploVayrIteEmeWsenet_midp_INvu(A10S,20)
WITH CHECK OPTION;
D. CREATE FORCE VIEW emp_vu AS SELECT *
FROM employees
WITH department_id IN (10,20);
E. CREATE FORCE VIEW emp_vu AS SELECT *
FROM employees
WHERE department_id IN (10,20) NO UPDATE;
Answer: C Explanation:
Tables that underlie views often have constraints that limit the data that can be added
tcoanthnoteadtadbldeast.aVtoietwhse underlying table that would violate the table's constraints. Ha voiwewevteor,ryeosturicatnthaelsuosedre'sfianbeility to change underlying table data even further, ecfofnescttriavienltyfpolradcaintagma asnpiepcuialaltion through the view. This additional constraint says that IsNtaSteEmReTntosrisUsPueDdAaTgEainst the view are cannot create rows that the view cannot scuobnsetrqauinetntilsycsoenlfeicgtu. rTehdiswhen the view is defined by adding the WITH CHECK OVIETWIOsNtatoemtheentC. REATE
Incorrect Answers
A: This view does not provide correct semantic of question task.
B: WITH READ ONLY clause is incorrect syntax in the CREATE VIEW command. D: This view does not provide correct semantic of question task.
E: NO UPDATE clause is incorrect syntax in the CREATE VIEW command. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 301-303
Chapter 7: Creating Other Database Objects in Oracle
QUESTION 58:
Examine the structure of the STUDENTS table: STUDENT_ID NUMBER NOT NULL, Primary Key STUDENT_NAME VARCHAR2(30)
COURSE_ID VARCHAR2(10) NOT NULL MARKS NUMBER
START_DATE DATE FINISH_DATE DATE
You need to create a report of the 10 students who achieved the highest ranking in the
rwsehIoNcTomSQplLeted the course in the year 1999.
Which SQL statement accomplishes this task?
AWFR.NHSODEMRLfiEnstiCusRhdTO_esndWtatustNdeeUBnMtE_T<iWd=, Em1E0aNrks'0,1R-OJAWNN-9U9M' A"NRDan'3k1"-DEC-99'
AND course_id = 'INT_SQL' ORDER BY marks DESC;
B. SELECT student_id, marks, ROWID "Rank" FROM students
WHERE ROWID <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL'
ORDER BY marks;
C. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks
FROM students
WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND
'31-DEC-99'
AND course_id = 'INT_SQL' ORDER BY marks DESC);
D. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks
FROM students
ORDER BY marks DESC) WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course _ id ='INT _ SQL';
Answer: D
Explanation:
This statement using inline sub-query will provide correct results to show 10 students
rahnokiancghiinevtehde tchoeuhrsieghINesTt
Incorrect Answers
SQL and who completed the course in the year 1999.
A: This view will just show first 10 students from the STUDENTS table with lainmditwathio comn tphletceodutrhseecIoNuTrsSeQinLthe year 1999.
B: It's wrong to use here ROWID to achieve desired results.
C: This statement will provide wrong result due to wrong conditions in the inline sub- qOuCePryI.ntroduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 162-165
Chapter 4: Subqueries
QUESTION 59:
Which four statements correctly describe functions that are available in SQL? (Choose four)
A. INSTR returns the numeric position of a named character. BDCE.. TNDRVEIUCLMNO2 tCDrreiAEmtuTsrtErntashnreotshulhaenetdfeaisdrsiatnthngneeoocxnofp-ltnrureuamslislnieno,xgnepxcarphfertsaesrsiasocinotemnirn,spot(ahroreirvnabeglxouiptethrt)etosfsrenoiaomcdnhelacisisecmtah.raclrhapclvtaaecrleuset..ring.
F. NVL compares two expressions and returns null if they are equal, or the first
e rael.ssion of they are not
G. NULLIF compares twp expressions and returns null if they are equal, or the first
e rael.ssion if they are not
Answer: A, D, E, G
Explanation:
INSTR returns the numeric position of a named character. DECODE translates an etoxperaecshsisoenaracfhtevracloume.pTaRriInMg ittrims the heading of trailing characters (or both) from a
c amr
catreerssttwripnge.xNprUesLsLioInFs and returns null if they are equal, or the first expression if
IhnecyorarreectnAotnesqwuearls.
B: This statement is not correct. The following is the Syntax for NVL2 function: NexVprL12i(sexnport1n,uelxl,pNr2V, eLx2pre3t)u.rInfs expr2. If expr1 is null, NVL2 returns expr3.
C: Command TRUNCATE is used to remove all row data from the table, while leaving thabeldeeifnitnaictito, ninocflutdhieng the definition of constraints and any associated database objects
asndintdriegxgeesr,sconstthreaitnatbsl,e.
F: NVL returns second parameter value if first one is NULL.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 67-73
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 60:
Examine the data from the EMP table: EMP_ID DEPT_ID COMMISSION
1 10 500
2 20 1000
3 10
4 10 600
5 30 800
6 30 200
7 10
8 20 300
The COMMISSION column shows the monthly commission earned by the employee. Which three tasks would require subqueries or joins in order to perform in a single
step? (Choose three)
A. Deleting the records of employees who do not earn commission.
B. Increasing the commission of employee 3 by the average commission earned in dECDe...pCFIanirrnsetemdartiteininngggt2ahi0net.atnobultemhecbaetalrlbeoldef CeamOnpeMlwoMyeeImSespSlwIoOyhNeoedt1ho0aNtwhOhaTsotewhaeornrskacsmofmeorsmtdrieuspsciatourntrmeaaennddt a2dr0eatawanoadsrketaihnrengs cefaCoqoOcrulouMdamleMptmnoasIirtsStEhsmSeiMoIecOnnPotN_tmh2ISDam0t.oiaisfsnstdihoenEeMarnPetdabblye.employee 3.
F. Decreasing the commission by 150 for the employees who are working in dcoepmamrtmisseinotn3o0famnoreeatrhnein 8a00.
Answer: B, D, E Explanation:
Increasing the commission of employee 3 by the average commission earned in
dsuebp-aqrtumereinest 2o0r jwoinllsrienquoirrdeer to perform in a single step. Inserting into the table a new fomrpdleopyaeretm10enwt h2o0 wanodrkesarns a commission that is equal to the commission earned by eamnspwloeryeaels3o.isTococrrreeactte a table that has the same structure as subset of columns from aunseotahesur btaqbuleriyt'sinreoqrdueirretdotpoerform in a single step.
Incorrect Answers
A: This query can be done without using sub-queries or joins in order to perform in a
Cin: gItleissnteopt.required to use sub-queries or joins to create SQL statement for that query.
F: This query can be done without using sub-queries or joins in order to perform in a scionngdleitisotneps:inonthlye tWwoHERE clause should be specified.
QUESTION 61:
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;
FAF.RnSOsEwMLeErc:CuBsT,toDTmOeTrA; L(customer_name)
Explanation:
These statements provide correct syntax and semantics to show the number of
cuanstobme uersse.dFwunitchtisounbsCtiOtuUtiNonT(s)ymbol of all columns "*" or just with one column name.
praoscteqsuserdyawliitltllebebit 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.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 123-125
Chapter 3: Advanced Data Selection in Oracle
QUESTION 62:
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
foramcahtatirnagctceornsvtreintgiounssi.ng
Incorrect Answers
A: This function cannot convert the number to the string representing number spelled oBu:tT. O_CHAR() function cannot convert the character value to a number. TDO: T_NhiUs fMunBcEtiRon()ifsunnoctiaobnledotoescothnivs.ert the string representing number spelled out to the nFu: mTObe_rCiHtseAlfR. () function cannot convert a character expression to a date. TO_DATE() fOuCncPtiIonntrdoodeuscttihoins.to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 86-87
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 63:
Which two statements are true regarding the ORDER BY clause? (Choose two) A. The sort is in ascending by order by default.
BCED. Thee OsOOoRrtDisEiRn BdBeYsccelnaaudusisneegcmisoumredsxteeesprcrlbuaeytcseteddienoefnathuhtelhete.SWEclHLieEnCRt TEsidscetla.tuesme.ent.
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 BthYe ScElaLuEseCcTomstaetselmasetnitn: after FROM or WHERE or GROUP BY clause.
Incorrect Answers
B: The ORDER BY clause does sort data in ascending, not descending order, by dCe:fTauhlet.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 iFs:. The ORDER BY clause is executed last in the query execution, after results are lainmditGedRwOiUthPtBheYWclHauEsRe Econditions.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 56-61
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 64:
Examine the structures of the EMPLOYEES and TAX tables. EMPLOYEES
EMPLOYEE_ID NUMBER NOT NULL, Primary
EMP_NAME VARCHAR2 (30) JOB_ID VARCHAR2 (20) SALARY NUMBER
MGR_ID NUMBER References
column
DEPARTMENT_ID NUMBER Foreign key to
Key
EMPLOYEE_ID DEPARTMENT _ID
column of the
DEPARTMENT table
TAX
MIN_SALARY NUMBER MAX_SALARY NUMBER
TAX_PERCENT NUMBER Percentage tax for given
salary range
You need to find the percentage tax applicable for each employee. Which SQL
sFWAtRa.HtSOeEmMRLeEnemtCew.TpsolaoeulmyalrdepyeylsoBoeyEu,eTteua_Wsxeid?Et,EsaNlatr.ym, itnax__spaelracreynAt ND t.max_salary
B. SELECT employee_id, salary, tax_percent
FROM employees e, tax t
WHERE e.salary > t.min_salary, tax_percent C. SELECT employee_id, salary, tax_percent FROM employees e, tax t
WHERE MIN(e.salary) = t.min_salary
AND MAX(e.salary) = t.max_salary
D. You cannot find the information because there is no common column between the two tables.
Answer: A
Explanation:
You can find the percentage tax applicable for each employee by using SQL statement
IncaonrsrwecetrAAn.swers
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 Oracle 9i: SQL Exam Guide, Jason Couchman, p. 98-103
Chapter 3: Advanced Data Selection in Oracle
QUESTION 65:
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)
IntactoermreecnttAs.nswers
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.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 267-279
Chapter 6: Manipulating Oracle Data
MDEarPyAhRaTsMa EviNewTSc,aallned LEOMCPA_TDIEOPNTS_LtaOblCes_.VSUhethgartanwtaesd cSrEeaLtEedCbTapseridviolnegteheto Scott
QEonMUtPEhiLSsOTvYIieOEwNE. S6,6:
Which option enables Scott to eliminate the need to qualify the view with the name
.EMARPY_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
gyrannotnSyEmLfEoCr tTheprviiveiwlegaendon that synonym to Scott.
Answer: B Explanation:
Correct syntax to create a local synonym is CREATE SYNONYM synonym_name.
WyoiuthcaPnUcBreLaItCe pkueybwlicorsdynonym. 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
SOcCoPtt Ionntrtohdisucvtiieown.to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 331-335
Chapter 7: Creating Other Database Objects in Oracle
QUESTION 67:
Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25)
LYCHAoRIRuESEiTAs__sTDuNEeAAtTahMbEelsEeDeVnAseAtTawRtEe_CmemHenAptsR(:e2m(2p5l)oye_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 rroalnlesdacbtaiocnksohnalvyetobetheen savepoint s2 before issue this update.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 279-285
Chapter 6: Manipulating Oracle Data
QUESTION 68:
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: C, D
SQL*Plus commands can be used to manipulate data in tables and SQL*Plus
EcdInoexcfmpionlmrairnteaiaocntndiAosninmn:sawtnheirpsdualatatebatasbe.le
A: SQL*Plus commands can be abbreviated. Like command DESCRIBE can be aSbEbLreEvCiaTteads aSsEDLES. C, or
B: SQL*Plus commands are not accesses from a browser.
E: SQL*Plus is not only the Oracle proprietary interface for executing SQL statements. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 38-48
Chapter 1: Overview of Oracle Databases
QUESTION 69:
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
Bbe-tttreerefionrdteaxbelseswwililthwaorwkide range of values. But for tables just with some distinct
aolureeshbeliptmfualp.
indexes will be
Incorrect Answers
A: Oracle automatically creates index for NOT NULL columns, so you don't need
Br:eTatheearen ainredeoxnlyyotuarbsleelfn. ames in the FROM clause, not columns.
C: Index may be will not work if the indexed columns are part of an expression. You nifeyeodutowaavnotitdoeuxsperiensdsieoxn.s
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 322-331
Chapter 3: Creating Other Database Objects in Oracle
QUESTION 70:
Which SQL statement generates the alias Annual Salary for the calculated column
SALARY*12?
A. SELECT ename, salary*12 'Annual Salary' FROM employees;
B. SELECT ename, salary*12 "Annual Salary" FROM employees;
C. SELECT ename, salary*12 AS Annual Salary
FROM employees;
D. SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY") FROM employees
AExnpslwanera:tiBon:
This SQL statement provides correct syntax to generate the alias Annual Salary for the cSaAlcLuAlaRteYd*c1o2l.umn
Incorrect Answers
A: Alias can be surrounded with double quotation marks, not with single. Oracle error wcaislle.be generated in this
C: Alias needs to be surrounded with double quotation marks, it cannot just follow by tDh:eYAoSu kceaynwnootrdu.se any function as alias, so this SQL statement will fail.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 33-35
Chapter 1: Overview of Oracle Databases
QUESTION 71:
Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25) DEPARTMENT_ID NUMBER SALARY NUMBER
What is the correct syntax for an inline view?
A. SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a,
(SELECT department_id, max(salary)maxsal
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary < b.maxsal;
B. SELECT a.last name, a.salary, a.department_id
FROM employees a
WHERE a.department_id IN (SELECT department_id FROM employees b
GROUP BY department_id having salary =
(SELECT max(salary) from employees))
C. SELECT a.last_name, a.salary, a.department_id
FROM employees a WHERE a.salary = (SELECT max(salary) FROM employees b
WHERE a.department _ id = b.department _ id);
D. SELECT a.last_name, a.salary, a.department_id
((SREHOLEMERCEeTm(adp.eldopeyaperatermstmeanetn_ti_di,dma,.asx.as(laslaraylra)yrI)yN)IN
FROM employees b GROUP BY department_id ORDER BY department _ id);
Answer: A
Explanation:
This SQL statement shows correct syntax to build inline views. You must enclose the qvuieewryintepxat rfeonrtthheeseinslaind also give a label for the inline view so that columns in it can banesrwefeerrAenicneldinleatveire.wInis marked as B.
Incorrect Answers
B: This SQL statement will fail because it is not correct syntax for inline views. There isnlninoea label for this
view also.
C: This SQL statement will fail because it is not correct syntax for inline views. There isnlninoeavliaebwelaflosor .this
D: This SQL statement will fail because it is not correct syntax for inline views. There isnlninoeavliaebwelaflosor .this
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 162-165
Chapter 4: Subqueries
QUESTION 72:
Which clause would you use in a SELECT statement to limit the display to those gmrepalteoryetheesnw5h0o0s0e?salary is
A. ORDER BY SALARY > 5000
B. GROUP BY SALARY > 5000
C. HAVING SALARY > 5000
D. WHERE SALARY > 5000
Answer: D Explanation:
You need to use the WHERE clause to limit the display to those employees whose
InalcaorryreicstgArenastweretrhsen 5000.
A: The ORDER BY clause will just sort data, but it will not limit them.
B: You cannot use the GROUP BY clause with conditions. Oracle error will be gCe:nTehraeteHdA. VING clause may be used only in conjunction with the GROUP BY clause.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 61-67
Chapter 2: Limiting, Sorting, and Manipulating Return Data
UhenSsThIoOulNd 7y3o:u create a role? (Choose two)
A. To simplify the process of creating new users using the CREATE USER xxx
IsDtaEteNmTeInFt.IED by yyy
B. To grant a group of related privileges to a user.
C. When the number of people using the database is very high. D. To simplify the process of granting and revoking privileges.
E. To simplify profile maintenance for a user who is constantly traveling. Answer: C, D
Explanation:
You should use roles to grant a group of privileges to a user. You grant the appropriate panridvialfetgeersthtoatthgerarnotlethis role to specific users. By granting to or revoking privileges fsriommpltihfye proroleceydouurecaonf users privileges maintainance: you don't need to grant/revoke pusrievri.lIetg'seesstpoe/fcrioamllyevaecrhy helpful when you are experiencing a high number of people Inscinogrrtehcet Adantsawbaesres.
A: Roles have nothing to do with simplifying the process of creating new users.
B: You can use roles to grant a group of privileges to a user, but they can be not related aEt:aRllo.les have nothing to do with user who is constantly traveling.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 365-372
Chapter 8: User Control in Oracle
QUESTION 74:
Which three statements about subqueries are true? (Choose three)
A. A single row subquery can retrieve only one column and one row.
B. A single row subquery can retrieve only one row but many columns.
C. A multiple row subquery can retrieve multiple rows and multiple columns. D. A multiple row subquery can be compared by using the ">" operator.
E. A single row subquery can use the IN operator.
F. A multiple row subquery can use the "=" operator. Answer: B, C, D
Explanation:
A single row sub-query can retrieve only one row but many columns. A multiple row ounbe-rqouweroyrcmanulrteitprlievreows and multiple columns. A multiple row sub-query can be oopmerpataorre.d by using the ">"
Incorrect Answers
A: A single row sub-query can retrieve only one row, but many columns.. E: A single row sub-query cannot use the IN operator.
F: A multiple row sub-query cannot use the "=" operator.
ChCaPptIenrtr4o:dSuucbtiqounetroieOs racle 9i: SQL Exam Guide, Jason Couchman, p. 150-165
QUESTION 75:
Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER NOT NULL EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) DEFAULT 'SA_REP' SAL NUMBER
COMM_PCT NUMBER MGR_ID NUMBER DEPARTMENT_ID NUMBER
You need to update the records of employees 103 and 115. The UPDATE statement ythoeursopwesciwfyitshhtohueldvaulpudeastsepecified below:
JOB_ID: Default value specified for this column definition. SAL: Maximum salary earned for the job ID SA_REP.
COMM_PCT: Default value specified for this commission percentage column, if any. If no default value is specified for the column, the value should be NULL. DEPARTMENT_ID: Supplied by the user during run time through substitution
vWarhiiacbhleU. PDATE statement meets the requirements?
A. UPDATE employees
SET job_id = DEFAULT
AND Sal = (SELECT MAX(sal) FROM employees
WHERE job_id = 'SA_REP')
AND comm_pct = DEFAULT AND department_id = &did
WHERE employee _id IN (103,115); B. UPDATE employees
SET job_id = DEFAULT
AND Sal = MAX(sal)
AND comm_pct = DEFAULT OR NULL AND department_id = &did
WHERE employee_id IN (103,115) AND job _ id = 'SA_ REP';
C. UPDATE employees SET job_id = DEFAULT, Sal = (SELECT MAX(sal) FROM employees
WHERE job_id = 'SA_REP'),
comm_pct = DEFAULT, department_id = &did
WHERE employee_id IN (103,115);
DScSoaE.mlTU=mPjoM_DbpA_ciTXtdE=(=sDeaDmlE)E,pFFlAoAyUUeLeLTsT,,
department_id = &did
WHERE employee_id IN (103,115) AND job _ id = 'SA_ REP';
E. UPDATE employees
SET job_id = DEFAULT, Sal = (SELECT MAX(sal) FROM employees
WHERE job_id = 'SA_REP') comm_pct = DEFAULT OR NULL, department_id = &did
WHERE employee_id IN (103,115); Answer: C
Explanation:
This UPDATE statement is correct to receive desired results. Correct syntax is UcoPluDmAnT_Enatambele1_=navmaleuSe,EcTolumn_name2 = value2. You can also use the DEFAULT kvealyuweotroditsosspeetcaifcioedludmenfault value in update statements as well.
Incorrect Answers
A: You cannot use syntax like UPDATE table_name SET column_name1 = value AND voaluem2n._Cnoarmreec2t s=yntax is UPDATE table_name SET column_name1 = value, Bo:lYumoun_cannanmoet2u=sevsaylnutea2x. like UPDATE table_name SET column_name1 = value AND voaluem2n._Cnoarmreec2t s=yntax is UPDATE table_name SET column_name1 = value, cDo:luGmronu_pnafumnec2tio=nviasluneo2t.allowed to use in the SET clause of the UPDATE command.
E: You cannot set column to value DEFAULT OR NULL: this is wrong syntax to use. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 267-270
Chapter 6: Manipulating Oracle Data
QUESTION 76:
Which two statements about sequences are true? (Choose two)
A. You use a NEXTVAL pseudo column to look at the next possible value that would bseeqgueennecrea,tewditfhroumt actually retrieving the value.
B. You use a CURRVAL pseudo column to look at the current value just generated farfofemctainsgeqthueenfucret,hweritvhaolutes to be generated from the sequence.
C. You use a NEXTVAL pseudo column to obtain the next possible value from a retqruieevnicnegbthyeacvtaulualelyfrom the sequence.
D. You use a CURRVAL pseudo column to generate a value from a sequence that daotaubldasbeecuosleudmfno.r a specified
E. If a sequence starting from a value 100 and incremented by 1 is used by more then
Fof ethaepspelaicpaptliiocna,titohnesn caolluld have a value of 105 assigned to their column whose value is
|
. You use REUSE clause when creating a sequence to restart the sequence once it
eeqinugengceen.erated by the
nlueeradtefsintheedmfoarxtihmeusmequence.
Answer: B, C
Explanation:
You use a CURRVAL pseudo column to look at the current value just generated from a saeffqeucetinncge,thweitfhuortuhter values to be generated from the sequence. You use a NEXTVAL pthseeundeoxtcpoolusmsibnletovoablutaeinfrom a sequence by actually retrieving the value from the Ineqcourerneccet.Answers
A: You use a NEXTVAL pseudo column to obtain the next possible value from a retqruieevnicnegbthyeacvtaulualelyfrom the sequence.
D: You use a CURRVAL pseudo column to look at the current value just generated farfofemctainsgeqthueenfucret,hweritvhaolutes to be generated from the sequence.
E: This statement is not correct. There is no limitation like that in Oracle.
F: You use CYCLE clause, not REUSE, when creating a sequence to restart the smeaqxuiemncuemovnacleueit dgeefninereadtefsorthtehe sequence.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 315-322
Chapter 7: Creating Other Database Objects in Oracle
QUESTION 77:
Which four are correct guidelines for naming database tables? (Choose four) A. Must begin with either a number or a letter.
B. Must be 1-30 characters long.
C. Should not be an Oracle Server reserved word. D. Must contain only A-Z, a-z, 0-+, _, *, and #.
E. Must contain only A-Z, a-z, 0-9, _, $, and #. F. Must begin with a letter.
Answer: B, C, E, F
Explanation:
Oracle database object must begin with a letter and can usually be between 1 and 30 dhataarbacatseerss(lwonhgic,hexhcaevpetafomr aximu m of eight characters) and database links (with a mNamxiemcuamnnooft1b2e8acnhOarraacctleersS)e.rver reserved word. Name must contain only A-Z, a-z, 0-
9In,c_o,r$re, catnAdn#s.wers
A: Database tables may not begin with number. D: It cannot contain symbols "+" or "*".
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 208-211
Chapter 5: Creating Oracle Database Objects
QUESTION 78:
xAMoalSuPmTmLi_nOnNeYnAtahEmMeEes_EtIDrDVuacAtNtauRUrtCyeMpNoeBfARtERheRe2m(EN3aM0rOk)PTsLNOUYLELE,SPrtaimblaer:y Key
FIRST_NAME VARCNAR2(30) JOB_ID NUMBER
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER
You need to create an index called NAME_IDX on the first name and last name fields otafbtlhee. WEMhiPchLOSQYLEEstSatement would you use to perform this task?
A. CREATE INDEX NAME _IDX (first_name, last_name);
B. CREATE INDEX NAME _IDX (first_name, AND last_name) C. CREATE INDEX NAME_IDX
ON (First_name, last_name);
D. CREATE INDEX NAME_IDX
ON employees (First_name, AND last_name); E. CREATE INDEX NAME_IDX
ON employees (First_name, last_name); F. CREATE INDEX NAME_IDX
FOR employees (First_name, last_name); Answer: E
Explanation:
Answer E provides correct syntax to create index: CREATE INDEX index_name ON
tcaoblluem_nsm). e(list of
Incorrect Answers
A: You need to use keyword ON also to create index.
B: You cannot use keyword AND to build a list of columns for index. Also this ntaamteemoenntwishimchistshinegintdhextaibslcereating.
C: This statement is missing the table name on which the index is creating. D: You cannot use keyword AND to build a list of columns for index.
F: You cannot use keyword FOR to create an index.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 322-331
Chapter 7: Creating Other Database Objects in Oracle
QUESTION 79:
Which operator can be used with a multiple-row subquery? A. =
B. LIKE
C. BETWEEN D. NOT IN
EFA..n<IsS>wer: D
Explanation:
Only NOT IN operator can be used with a multi-row sub-query. All others may be used wsuibth-qsuinergyleo-rnolyw.
Incorrect Answers
A: When sub-queries are linked to the parent by equality comparisons, the parent qduatearyfreoxmpethctessounbl-yqounereyr,oswo "o=f" operator cannot be used with a multiple-row sub-query. B: Operator LIKE will work only with single-row sub-queries.
C: Operator BETWEEN will not work with a multi-row sub-queries. E: Operator LIKE is used only for single-row sub-queries.
F: When sub-queries are linked to the parent by equality comparisons, the parent query dxatpaecfrtosmontlhyeosnuebr-oqwueroyf, so "<>" operator cannot be used with a multiple-row sub- qOuCePryI.ntroduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 150-165
Chapter 4: Subqueries
QUESTION 80:
Examine the structure of the EMPLOYEES, DEPARTMENTS, and TAX tables. EMPLOYEES
EMPLOYEE_ID NUMBER NOT NULL, Primary
Key
EMP_NAME VAR CHAR2
(30)
JOB_ID VAR CHAR2
SALARY NUMBER
(20)
MGR_ID NUMBER References column
DEPARTMENT_ID NUMBER Foreign key to
DEPARTMENT_ID
the DEPARTMENTS DEPARTMENTS
DEPARTMENT_ID NUMBER NOT NULL,
EMPLOYEE_ID
column of table
Primary Key
DEPARTMENT_NAME VARCHAR2
|30|
MGR_ID NUMBER
References
MGR_ID column oEfMtPhLe OYEES table
TAX
MIN_SALARY NUMBER MAX_SALARY NUMBER TAX_PERCENT NUMBER
For which situation would you use a nonequijoin query?
A. To find the tax percentage for each of the employees.
B. To list the name, job id, and manager name for all the employees.
C. To find the name, salary, and department name of employees who are not working wD.itThoSfminidtht.he number of employees working for the Administrative department and eEa.rTnoindgislepslasythneanm4e0,0s0a.lary, manager ID, and department name of all the employees, nvoet nhaifvethae deemppalrotmyeeenst dIDo assigned.
Answer: A Explanation:
You will use a nonequijoin query to find the tax percentage for each of the employees braencgaeusoef ysoaluarwieisllwchitehcokut usage of equality operations in a comparison joining data fIrnocmorrtewcot Atanbslwese.rs
B: You will use equijoin query because EMPLOYEES and DEPARTMENTS tables weqiullablietyjooipnerdatwori.th
C: It will be equijoin query because EMPLOYEES and DEPARTMENTS tables will boepejroaitnoerd. with equality
D: You will use equijoin query because EMPLOYEES and DEPARTMENTS tables weqiullablietyjooipnerdatwori.th
E: It will be equijoin query because of equality operator in join condition of two tables. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 99-107
Chapter 3: Advanced Data Selection in Oracle
QUESTION 81:
Examine the data from the ORDERS and CUSTOMERS tables. ORDERS
ORD_ID ORD_DATE CUST_ID ORD_TOTAÖ
100 12-JAN-
2000 15 10000
101 09-MAR-
2000 40 8000
102 09-MAR-
2000 35 12500
10 125-JUANR- -2000 15
20000
105 18-JUL-
2000 20 5000
106 18-JUL- 2000 35 7000
107 21-JUL-
2000 20 6500
109 04-AUG-
2000 10 8000
CUSTOMERS
CUST_ID CUST_NAME CITY
10 Smith Los Angeles
15 Bob San Francisco
20 Martin Chicago
25 Mary New York
30 Rina Chicago
35 Smith New York
40 Lind New York
Evaluate the SQL statement: SELECT *
FROM orders
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_name = 'Smith');
What is the result when the query is executed? A.
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
|
102 09-MAR- 2000 35 |
12500 |
|
|
106 18-JUL- 2000 35 |
7000 |
|
|
108 04-AUG- 2000 10
B. |
8000 |
|
|
ORD_ID ORD_DATE |
CUST_ID |
ORD_TOTAL |
|
102 09-MAR- 2000 35 |
12500 |
106 18-JUL-
2000 35 7000
CO10.R8D_0I4D-AUOGR-D1_0DA8T0E00 CUST_ID ORD_TOTAL
2000
D. The query fails because the subquery returns more than one row.
E. The query fails because the outer query and the inner query are using different tables.
Answer: D
Explanation:
The query fails because the sub-query returns more than one row: it is possible sciutustaotmioenrswwheitnhtnhaemreeaSremsiothm.e
Incorrect Answers
A: The query fails because the sub-query returns more than one row B: The query fails because the sub-query returns more than one row C: The query fails because the sub-query returns more than one row
E: The query fails because the sub-query returns more than one row, not because the oquteerryqaureruysainngd dthifefeinrennetrtables.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 150-165
Chapter 4: Subqueries
QUESTION 82:
You need to display the last names of those employees who have the letter "A" as the stheeciornndacmheasr.acter in
Which SQL statement displays the required results?
A. SELECT last_name
FROM EMP
WHERE last_ name LIKE '_A%'; B. SELECT last_name
FROM EMP
WHERE last name ='*A%' C. SELECT last_name FROM EMP
WHERE last name ='_A%'; D. SELECT last_name FROM EMP
WHERE last name LIKE '*A%' Answer: A
Explanation:
Statement in this answer will show correct results because usage of operator LIKE and feBCox:rtmTUrahsaciatsgmtshetaeaostlfkeaems'_qteAunnia%tmyw'oeisplleorrfeatuhorornsheoenreelmyipsnlnaomoyteaesspspwtraohrtpoirnhigatvfereointmhtehsiylsemtctbearosel":A'*q"'u. aeItsryctahwneinlsoletlcobooenkduesxeadcats cSIfsnouhycrbamosrfairtbrirctoseutlectsrt'i_oyAi'mninnsbtshywofemoleirr'br_msno',ali.tmtmwesai.lslk substitute exactly one symbol and cannot be NULL.
not be considered as substitution symbol.
D: This statement will return only names starting from symbol '*'. It cannot be used as sOuCbsPtiItnuttriodnuscytmiobnotlo. Oracle 9i: SQL Exam Guide, Jason Couchman, p. 61-67
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 83:
You need to perform certain data manipulation operations through a view called
EwMhiPch_DyoEuPpTr_eVviUou, sly created.
You want to look at the definition of the view (the SELECT statement on which the vHioew wdoasyocrueoatbet.a)in the definition of the view?
A. Use the DESCRIBE command in the EMP_DEPT VU view.
B. Use the DEFINE VIEW command on the EMP_DEPT VU view.
C. Use the DESCRIBE VIEW command on the EMP_DEPT VU view.
D. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU vEi.eQwu. ery the USER_SOURCE data dictionary view to search for the EMP_DEPT_VU vF.ieQwu.ery the USER_OBJECTS data dictionary view to search for the EMP_DEPT_VU view.
Answer: D
Explanation:
To look on the view definition you need to query the USER_VIEWS data dictionary vEiMewP_aDndEsPeTa_rcVhUfovriethwe.
Incorrect Answers
A: You cannot see the definition of the view using the DESCRIBE command. B: There is no DEFINE VIEW command in Oracle.
C: There is no DESCRIBE VIEW command in Oracle.
E: You cannot use the USER_SOURCE data dictionary view to see the definition of ystoourer vsiyeswte:mit oisbjuescetds dtoefinitions only.
F: You can find record about view in the USER_OBJECTS, but it does not contain the ditesefilnf.ition of the view
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 291-301
Chapter 7: Creating Other Database Objects in Oracle
QUESTION 84:
Evaluate this SQL statement:
SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, dF.RDOEMPAERMTMP eE,NDTE_PNAARMTME ENT d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
In the statement, which capabilities of a SELECT statement are
pABe..rDSfoeirflmfeecerteidon?nc,e,pprorojejcetcitoionn, ,jojoinin
C. Selection, intersection, join
D. Intersection, projection, join
E. Difference, projection, product
Answer: A Explanation:
Selection, projection and join capabilities of a SELECT statement are performed in this
vIniecwor.rect Answers
B: Selection is performed in this query, not difference. There is no capability with nSaEmLeECdiTffesrtaetnecmeefnotr eaxists.
C: There is no intersection in this SELECT statement used. D: There is no intersection in this SELECT statement used.
E: There is no difference or product capabilities exist for a SELECT statement.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 20-21
Chapter 1: Overview of Oracle Databases
QUESTION 85:
Which two are character manipulation functions? (Choose two.) A. TRIM
B. REPLACE
C. TRUNC
D. TO_DATE E. MOD
F. CASE Answer: A, B
Explanation:
TRIM() and REPLACE() are character manipulation functions. Incorrect Answers
C: TRUNC(x,y) is arithmetic function, it truncates x to the decimal precision of y. If y
ti nyenguamtivbee,ritoftrpulnaccaetsesto the left of the decimal point. This can also be used on DATE Dco:luTmOn_sD. ATE(x,[y]) function converts the non-date value x to a date using the format
sEp:eMciOfieDd(xb,yy)y.is arithmetic function, the modulus of x, defined in long division as the dniveigderdrbeymyaiunndteirl nwohefunrtxher whole number can be produced.
F: There is no character manipulation function CASE in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 67-93
Chapter 2: Limiting, Sorting, and Manipulating Return Data
Which three statements correctly describe the functions and use of constraints? (CUhoEoSsTeItOhrNee8.)6:
A. Constraints provide data independence. B. Constraints make complex queries easy.
C. Constraints enforce rules at the view level. D. Constraints enforce rules at the table level.
E. Constraints prevent the deletion of a table if there are dependencies.
F. Constraints prevent the deletion of an index if there are dependencies. Answer: C, D, E
Explanation:
Constraints have functions to enforce rules at the view and table levels and to prevent dheepednedleetniocnieosfedxaistat bifetween tables.
Incorrect Answers
A: Constraints are used to put dependencies on data.
B: Constrains are not used to make complex queries more easy.
F: Constraints does not prevent the deletion of an index if there are dependencies. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-248
Chapter 5: Creating Oracle Database Objects
QUESTION 87:
You define a multiple-row subquery in the WHERE clause of an SQL query with a c"=om".parison operator
What happens when the main query is executed?
A. The main query executes with the first value returned by the subquery. B. The main query executes with the last value returned by the subquery. C. The main query executes with all the values returned by the subquery.
D. The main query fails because the multiple-row subquery cannot be used with the
cEo.mYopuarcisaonnnotpdereaftioner. a multiple-row subquery in the WHERE clause of a SQL query. Answer: D
Explanation:
The main query fails because the multiple-row sub-query cannot be used with the csionmglpea-roswonqoupeeryractoarn. uOsnelycomparison operators, like =, <, >, <=, >, and <>. Incorrect Answers
A: The main query fails because the multiple-row sub-query cannot be used with the Bo:mTphaermisoaninoqpueerraytofra.ils because the multiple-row sub-query cannot be used with the Co:mTphaermisoaninoqpueerraytofra.ils because the multiple-row sub-query cannot be used with the cEo:mYopuarcisaonndoepfienreataorm. ultiple-row sub-query in the WHERE clause of a SQL query, but ebryrodrifwfeirlel nbtergeeansoenra.ted
ChCaPptIenrtr4o:dSuucbtiqounetroieOs racle 9i: SQL Exam Guide, Jason Couchman, p. 150-165
QUESTION 88:
You need to calculate the total of all salaries in the accounting department. Which gusroe?up function should you
A. MAX B. MIN C. SUM
D. COUNT
E. TOTAL
F. LARGEST Answer: C
Explanation:
Function SUM(x) calculates the sum of all values in column x in all rows returned by
IhnecoSrEreLcEt ACnTsswtaetresment.
A: Function MAX(x) determines the maximum value in column x for all rows returned bstyattehme eSnEt.LECT
B: Function MIN(x) determines the minimum value in column x for all rows returned bstyattehme eSnEt.LECT
D: Function COUNT(x) counts the number of non-NULL values returned by the x.ELECT statement for column
E: There is no TOTAL() function in Oracle.
F: There is no LARGEST() function in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 122-126
Chapter 3: Advanced Data Selection in Oracle
QUESTION 89:
What is true about joining tables through an equijoin?
A. You can join a maximum of two tables through an equijoin.
B. You can join a maximum of two columns through an equijoin.
C. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT sDta. tTeomjeonint. two tables through an equijoin, the columns in the join condition must be pkreiymcaorlyumkenys.and foreign
E. You can join n tables (all having single column primary keys) in a SQL statement by opfenc-i1fyjionign acomnidniitmiounms.
Answer: E
FinotrhNe WjoiHnEedRtEabcleasuusesionfgyOoruarcSleEoLrEACNTSsIt/aItSeOmesnynt toarxNfo-1r tJaObIlNe jtoaibnlse,_ynoaumneeOedNat least
ENtjox-ian1pv_leaocqnioduanitadjioiCtinin:oarnctoecnslidaauintsioepsnrosinduocrtd,errespectively.
Incorrect Answers
A: There is no maximum on two tables to join them through an equijoin.
B: There is no maximum on two columns to join them through an equijoin.
C: You can specify an equijoin condition only in the FROM clauses of a SELECT sDta: tTemheerneti.s no limitation about primary key and foreign key for the columns to use tOhCemP Ifnotrroanduecqtuioijnoiton.Oracle 9i: SQL Exam Guide, Jason Couchman, p. 99-107
Chapter 3: Advanced Data Selection in Oracle
QUESTION 90:
Scott issues the SQL statements: CREATE TABLE dept
(deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)}; GRANT SELECT
ON DEPT T0 SUE;
If Sue needs to select from Scott's DEPT table, which command should
she use?
A. SELECT * FROM DEPT; B. SELECT *
FROM SCOTT. DEPT; C. SELECT *
FROM DBA.SCOTT DEPT; D. SELECT *
FROM ALL_USERS
WHERE USER_NAME = 'SCOTT' AND TABLE NAME = 'DEPT';
Answer: B Explanation:
If the table doesn't exist in your schema, you must prefix the table name with the
s earmatainingftohremoawtionne,r from the table name with a period.
Incorrect Answers
A: If you don't have DEPT table in your schema, this statement will fail. C: This statement is incorrect.
D: This statement is incorrect. ALL_USERS view is used to extract information about uOsCerPs.Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 28-29
CQhUaEptSeTr I1O: ONv9e1rv:iew of Oracle Databases
Which clause should you use to exclude group results?
A. WHERE B. HAVING
C. RESTRICT D. GROUP BY E. ORDER BY
Answer: B
Explanation:
HAVING clause is used to weed out unwanted data once the data is grouped using the
InRcoOrrUePctBAYnsswtaetresment.
A: WHERE clause cannot be used for this purpose. C: There is no RESTRICT command in Oracle.
D: GROUP BY cannot be used itself to exclude group results.
E: ORDER BY clause may be used only to sort final results, not to exclude group rOeCsuPltIsn. troduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 133-134
Chapter 3: Advanced Data Selection in Oracle
QUESTION 92:
A subquery can be used to _. A. Create groups of data
B. Sort data in a specific order
C. Convert data to a different format
D. Retrieve data based on an unknown condition
Answer: D Explanation:
A sub-query can be used to retrieve data based on an unknown condition
Incorrect Answers
A: A sub-query cannot be used to create groups of data, GROUP BY clause is used for Bh:aAt. sub-query cannot be used to sort data in a specific order, ORDER BY clause is Cs:eAd sfourb-thqaute.ry cannot convert data to a different format.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 150-165
Chapter 4: Subqueries
QUESTION 93:
TCLSAhAOeLSMEATM_RNIYPSALSNMOIUOYEMNEV_BEAPSECRRtTCab(HN6le,AU2c)RMo2nBt(aE2i5nR)s(t6h)ese columns:
You need to write a query that will produce these results:
1. Display the salary multiplied by the commission_pct.
2. Exclude employees with a zero commission_pct.
3. Display a zero for employees with a null commission value. Evaluate the SQL statement:
SELECT LAST_NAME, SALARY*COMMISSION_PCT FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL; What does the statement provide?
A. All of the desired results B. Two of the desired results C. One of the desired results D. An error statement
Answer: C Explanation:
This statement will provide only one of the desired results: display the salary
mcoumltmipilsiesidonb_yptchte. It will not exclude employees with a zero commission_pct and dwiistphlaynaulzlecromfomriesmsiopnlovyaeleuse.
Incorrect Answers
A: This statement will provide only one of the desired results, not all. B: This statement will provide only one of the desired results, not two. D: This statement will not generate an error, it is correct .
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 25-38
Chapter 1: Overview of Oracle Databases
QUESTION 94:
Which SELECT statement should you use to extract the year from the system date and d"1is9p9l8ay"?it in the format
A. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;
B. SELECT TO_DATE(SYSDATE,'yyyy') FROM dual;
C. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;
D. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;
E. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy')
FARnOswMerd: Aual;
Explanation:
Function TO_CHAR(x, y) converts the value x to a character or converts a date to a fohramraacttteinrgstcrionngveunsitniogns.
Incorrect Answers
B: Function TO_DATE(x,[y]) converts the non-date value x to a date using the format Cp:eTcihfeieDd EbCy Ox.DE function is used as substitution of IF-THEN-ELSE PL/SQL cSoEnLsEtrCucTtisotnatienmSeQntLpqrouveridieess. iTnhceorrect syntax of it because it cannot have only two pDa:rTamheetDerEsC. ODE function is used as substitution of IF-THEN-ELSE PL/SQL cSoEnLsEtrCucTtisotnatienmSeQntLpqrouveridieess. iTnhceorrect syntax of it because it cannot have only two pEa:rTamhiestsetrast.ement provide incorrect syntax of TO_CHAR() function: it requires only oOnCePpIanratrmodeutecrt,ionnottotwOor.acle 9i: SQL Exam Guide, Jason Couchman, p. 86-87
Chapter 2: Limiting, Sorting, and Manipulating Return Data
QUESTION 95:
Which is an /SQL*Plus command? A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE E. DELETE
F. RENAME Answer: D
Explanation:
There is only one SQL*Plus command in this list: DESCRIBE. It cannot be used as ScoQmLmcaonmdmreatnudr.nTs haids escription of tablename, including all columns in that table, the danatdataynpienfdoicraetaiochn coof lwumhent,her the column permits storage of NULL values.
Incorrect Answers
A: INSERT is not a SQL*Plus command. It's data-manipulation language (DML) Bo:mUmPDanAdT. E is not a SQL*Plus command. It's data-manipulation language (DML) Co:mSmELanEdC.T is not a SQL*Plus command.
E: DELETE is not a SQL*Plus command. It's data-manipulation language (DML)
o: mRmENanAdM.
E is not a SQL*Plus command.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 43
Chapter 1: Overview of Oracle Databases
QUESTION 96:
What does the TRUNCATE statement do? BC. SRRheomrotoevnesstahthlelertatoabwblelsetforo1m0 arotwabsle
D. Removes all columns from a table
E. Removes foreign keys from a table
Answer: B Explanation:
Command TRUNCATE is used to remove all row data from the table, while leaving
it etadcte,fincitliuodninogf tthe tdaebflienition of constraints and any associated database objects as
itnridgegxeers,ocnonthsetratainbtlse,. and
Incorrect Answers
A: Command TRUNCATE does not remove the table. DROP TABLE command does Ch:aTt.here is no command in Oracle to shortens the table to 10 rows: but you can do this wDi:tThhqiusecroiems.mand does not remove all columns from a table.
E: This command does not remove all foreign keys from a table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 225
Chapter 5: Creating Oracle Database Objects
QUESTION 97:
You need to change the definition of an existing table. The COMMERCIALS table ncoeeludms nitschDaEnSgCedRtIoPThoIOldNvarying length characters up to 2000 bytes. The column can puerrveanltulye.hTohlde 1ta0b0l0e bcoyntetsains 20000 rows.
Which statement is valid?
A. ALTER TABLE commercials MODIFY (description CHAR2(2000)); B. ALTER TABLE commercials CHANGE (description CHAR2(2000)); C. ALTER TABLE commercials
CHANGE (description VARCHAR2 (2000)); D. ALTER TABLE commercials
MODIFY (description VARCHAR2 (2000));
E. You cannot increase the size of a column if the table has rows. Answer: D
Explanation: Incorrect Answers
A: There is no CHAR2 datatype in Oracle. Also 200 symbols is not enough to store up
Bo:2T0h0e0rebiystenso. CHAR2 datatype in Oracle. There is no ALTER TABLE table_name
COHraAclNe.GE command in
C: There is no ALTER TABLE table_name CHANGE command in Oracle.
E: You can increase the size of a column if the table has rows. It can be done with
CMOhCLOaTPDpEtIIeRnFrtYrT5oA:dcCuoBcrlLuetiamEotnintna.tgbolOeO_rranacaclmleeeD9ai:taSbQasLeEOxbajmecGtsuide, Jason Couchman, p. 219-224
QUESTION 98:
Which statement creates a new user? A. CREATIVE USER susan;
B. CREATIVE OR REPLACE USER susan;
C. CREATE NEW USER susan
DEFAULT
D. CREATE USER susan
IDENTIFIED BY blue;
E. CREATE NEW USER susan
IDENTIFIED BY blue;
F. CREATE OR REPLACE USER susan
IDENTIFIED BY blue; Answer: D
Explanation:
The correct statement to create user is: CREATE USER user_name IDENTIFIED BY
pInacsoswrreocrdt .Answers
A: This syntax is incorrect to create user: you need to provide also password for the
Bs:eTr.here is no CREATE OR REPLACE USER command in Oracle.
C: There is no CREATE NEW USER user_name DEFAULT command in Oracle. E: There is no CREATE NEW USER user_name IDENTIFIED BY command in
OF:raTchler.e is no CREATE OR REPLACE USER user_name IDENTIFIED BY
cOoCmPmInantrdodinucOtiroanclteo. Oracle 9i: SQL Exam Guide, Jason Couchman, p. 348-351
Chapter 8: User Access in Oracle
QUESTION 99:
Which three are true regarding the use of outer joins? (Choose three.)
A. You cannot use IN operator in a condition that involves an outerjoin.
B. You use (+) on both sides of the WHERE condition to perform an outerjoin. C. You use (*) on both sides of the WHERE condition to perform an outerjoin. D. You use an outerjoin to see only the rows that do not meet the join condition.
E. In the WHERE condition, you use (+) following the name of the column in the table wtoitpheorufotrmmaatcnhoinugterrojowins,.
F. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator.
YAu nswer: D, E, F utheerjonianmteo osefethoenclyoltuhme nroiwnsththeattabdloenwoitthmoeuettmthaetcjhoingcoronwdist,ioton. In the soeu(c+a)nfoulsleowaninog
EWpexrHpfolEarRnmaEtaicononon:udtietirojoni,ny.oYuou
cannot link a condition that is involved in an outerjoin to another condition by using
IhnecoOrrRecotpAenrastwore.rs
A: You can use IN operator in a condition that involves an outerjoin.
B: You use (+) following the name of the column in the table without matching rows, bthuet WnoHt oEnRbEocthonsdiditeisonofto perform an outerjoin.
C: You don't use (*) to define outerjoin operation.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 109-118
Chapter 3: Advanced Data Selection in Oracle
QUESTION 100:
In which scenario would TOP N analysis be the best solution?
A. You want to identify the most senior employee in the company.
B. You want to find the manager supervising the largest number of employees.
C. You want to identify the person who makes the highest salary for all employees. D. You want to rank the top three sales representatives who have sold the maximum
number of products.
Answer: D
Explanation:
If you want to rank the top three sales representatives who have sold the maximum nquerbyewr iollf bperotdhuecbtsesTtOsoPl-uNtion. TON-N queries use inline views and are handy for dtaibspleladyaitnag, baassheodrtolnis"tgorfeatest" or "least" criteria.
Incorrect Answers
A: To complete this request you don't need to use TOP-N query. B: It is not necessary to use TOP-N query in this case.
C: To complete this request you don't need to use TOP-N query.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 162-165
Chapter 4: Subqueries
QUESTION 101:
Which constraint can be defines only at the column level? A. UNIQUE
B. NOT NULL
C. CHECK
D. PRIMARY KEY E. FOREIGN KEY
Answer: B
The NOT NULL constraint can be defined only at the column level. It enforces that a
vthaxilspulceaonmlauutmisotnb:seudcheftihnaetdthfoercolumn may not be NULL for any row.
Incorrect Answers
A: The UNIQUE constraint enforces uniqueness on values in the constrained column. Iatt cthane cboeludmefninleedvenlo.t only
C: The CHECK constraint enforces that values added to the constrained column must boef vparelusesntpienrma istttaetdicfolirsthe column.
D: The PRIMARY KEY constraint stipulates that values in the constrained column(s) mNUusLt Lb.eIufnthiqeuperaimndarnyokt ey applies to multiple columns, then the combination of values unnitqhueecoanludmnnost mNuUsLt Lbe.
E: The FOREIGN KEY constraint enforces that only values in the primary key of a pinacrleundtetdabalsevmalauyebsein the constrained column(s) of the child table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232
Chapter 5: Creating Oracle Database Objects
QUESTION 102:
What is true about the WITH GRANT OPTION clause? A. It allows a grantee DBA privileges.
B. It is required syntax for object privileges.
C. It allows privileges on specified columns of tables.
D. It is used to grant an object privilege on a foreign key column.
E. It allows the grantee to grant object privileges to other users and roles. Answer: E
Explanation:
The GRANT command with the WITH GRANT OPTION clause allows the grantee to gorthaenrt uosbejersctapnrdivriolleegse.s to
Incorrect Answers
A: The WITH GRANT OPTION does not allow a grantee DBA privileges.
B: It is not required syntax for object privileges. It is optional clause of GRANT Co:mGmRaAnNd.T command does not allows privileges on columns of tables.
D: It is not used to grant an object privilege on a foreign key column.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 356-365
Chapter 8: User Access in Oracle
QUESTION 103:
Which substitution variable would you use if you want to reuse the variable without ptirmoem?pting the user each
A. &
BCD. AP&RC&OCMEPPTT
Answer: D
Explanation:
To reuse the variable without prompting the user each time you can use && InucbosrtirteucttioAnnvsawreiarbs le.
A: This substitution variable will prompt the user each time.
B: ACCEPT is command, not substitution variable. It used to define more accurate or sypoeucwifiacnpt rmomorpetoourtpwuhtetno display as the values are defined.
C: PROMPT is part of the ACCEPT command, it is not a variable.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 165-173
Chapter 4: Subqueries
QUESTION 104:
The EMPLOYEES table has these columns: LAST NAME VARCHAR2(35)
SALARY NUMBER(8,2) HIRE_DATE DATE
Management wants to add a default value to the SALARY column. You plan to alter
tShQeLtasbtlaetebmyeunsti:ng this
ALTER TABLE EMPLOYEES MODIFY ( SALARY DEFAULT 5000); What is true about your ALTER statement?
A. Column definitions cannot be altered to add DEFAULT values.
B. A change to the DEFAULT value affects only subsequent insertions to the table.
C. Column definitions cannot be altered at add DEFAULT values for columns with a NDU. AMllBthEeRrodwatsa tthyapteh. ave a NULL value for the SALARY column will be updated with the value 5000.
Answer: B
Explanation:
A change to the DEFAULT value affects only subsequent insertions to the table. Eafxfeiscttiendg. rows will not be
Incorrect Answers
A: Column definitions can be altered to add DEFAULT values.
C: Column definitions can be altered to add DEFAULT values. It works for columns wtyipteh alsNoU. MBER data
D: A change to the DEFAULT value affects only subsequent insertions to the table. Eafxfeiscttiendg. rows will not be
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 219-224
Chapter 5: Creating Oracle Database Objects
QThUeEDSBTAIOisNsu1e0s5th: is SQL command:
CREATE USER scott
IDENTIFIES by tiger;
What privileges does the user Scott have at this point?
A. No privileges.
B. Only the SELECT privilege.
C. Only the CONNECT privilege.
D. All the privileges of a default user. Answer: A
Explanation:
There are no privileges for the user Scott at this point. They are not added themselves taofttehrecuresaetrioimn.mTehdeiaDteBlyA needs to grant all privileges explicitly.
Incorrect Answers
B: There are no privileges for the user Scott at this point. SELECT privilege needs to bCe: Tadhdeered atorethneo upsreivriSlecgoetts. for the user Scott at this point. CONNECT privilege needs tSocobtet.added to the user
D: There is no default user in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 348-351
Chapter 8: User Access in Oracle
QUESTION 106:
Which two statements complete a transaction? (Choose two) A. DELETE employees;
B. DESCRIBE employees;
C. ROLLBACK TO SAVE POINT C;
D. GRANT SELECT ON employees TO SCOTH E. ALTER TABLE employees
SET UNUSED COLUMN sal; F. Select MAX(sal)
FROM employees
WHERE department _ id 20; Answer: C, E
Explanation:
It is important to understand that an implicit COMMIT occurs on the database when a uissseureesxaitdsaStaQ-dLe*fPinluitsioonr language (DDL) command such as a CREATE TABLE
dtaattaebmaesento, bujseecdt,torcarneaAteLaTER TABLE statement, used to alter a database object. Also
|
aAco: The DELETE command is data-manipulation language (DML) command and it tmermthaendRiOsLisLsBueAdC, aKnew transaction is started implicitly by the database session.
B: The DESCRIBE command is internal SQL*Plus command and it has nothing to do wtraitnhsaccotmiopnl.etion a
D: The GRANT command is data-control language (DCL) command to control user
ca cmepsslettoe daattraa.nIstawctiilolnn.ot
F: SELECT command is used to retrieve data. It does not complete a transaction. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 281-282
Chapter 3: Advanced Data Selection in Oracle
QUESTION 107:
You need to produce a report for mailing labels for all customers. The mailing label mcuusstot mhaevrenoanmlye athned address. The CUSTOMERS table has these columns:
CUST_ID NUMBER(4) NOT NULL CUST_NAME VARCHAR2(100) NOT NULL CUST_ADDRESS VARCHAR2(150) CUST_PHONE VARCHAR2(20)
Which SELECT statement accomplishes this task?
A. SELECT* FROM customers;
B. SELECT name, address
FROM customers;
C. SELECT id, name, address, phone
FROM customers;
D. SELECT cust_name, cust_address
FROM customers;
E. SELECT cust_id, cust_name, cust_address, cust_phone
FROM customers; Answer: D
Explanation:
This answer provides correct list of columns for the output. Incorrect Answers
A: This answer does not provide correct list of columns for the output. It is not rtheqeutairbelde.toSysmhobwola"l*l "coisluumsends ionf the SELECT command to substitute a list of all Bo:lTumhinssanofswtheer tdaobeles.not provide correct list of columns for the output. There are not NcoAluMmEnsainndtAheDCDURSETSOSMERS table.
C: This answer does not provide correct list of columns for the output. There are not
IPDH,ONNAEMcEo,luAmDnDs RinEtShSe CorUSTOMERS table.
E: This answer does not provide correct list of columns for the output. It is not required toheshtaobwlea. ll columns of
ChCaPptIenrtr1o:dOucvteiorvnietowOorfaOclreac9lie: SDQatLabEaxsaems
Guide, Jason Couchman, p. 20-24
QUESTION 108:
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. Tthheeryoawlsoinunyioquuretlaybildee. nTthifeyROWID 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 ROWI D datatype is a
hOeCxPadIenctrimodaulcsttiroingt.o Oracle 9i: SQL Exam Guide, Jason Couchman, p. 216
Chapter 5: Creating Oracle Database Objects
QUESTION 109:
Evaluate the SQL statement
DROP TABLE DEPT:
Which four statements are true of the SQL statement? (Choose four)
A. You cannot roll back this statement.
B. All pending transactions are committed.
C. All views based on the DEPT table are deleted.
D. All indexes based on the DEPT table are dropped.
E. All data in the table is deleted, and the table structure is also deleted.
F. All data in the table is deleted, but the structure of the table is retained. G. All synonyms based on the DEPT table are deleted.
Answer: A, B, D, E Explanation:
You cannot roll back DROP TABLE statement. All pending transactions related on this
t belteaabrle cisodmrmopiptted,. OIfracle automatically drops any index, trigger and constraint
|
F: All data in the table is deleted, and the table structure is also deleted. Command
|
TICseslol.ciatlledAwawtaitihnetthhsdeettaabbllleedaiDstadEePlTetetadb, laenbdetchoemtaebilnevsatlriudc,tbuuret tihseaylsaoredenloettedde.leted.
in the table, but does not delete the structure of the table.
G: All synonyms based on the DEPT table are not deleted after dropping the table. OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 225
Chapter 5: Creating Oracle Database Objects
QUESTION 110:
You are granted the CREATE VIEW privilege. What does this allow you to do?
A. Create a table view.
B. Create a view in any schema. C. Create a view in your schema.
D. Create a sequence view in any schema.
E. Create a view that is accessible by everyone.
F. Create a view only of it is based on tables that you created. Answer: C
Explanation:
You can create a view in your own schema only if you are granted the CREATE VIEW
pInricvoirlreegcet.Answers
A: You can create a view in your own schema only.
B: You can create a view in your own schema only, not in any schema. D: There is no sequence view in Oracle.
E: You cannot create a view that is accessible by everyone. You will need specially gornatnhtisSEviLeEwCfTorperviveirlyeognese.
F: You can create a view in your own schema, but not only for tables in your schema. otohuercuanseursescohbejemcatsfriof myou have privileges to retrieve data from them.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 292-301
Chapter 7: Creating Other Database Objects in Oracle
QUESTION 111:
Which two statements about creating constraints are true? (Choose two) A. Constraint names must start with SYS_C.
B. All constraints must be defines at the column level.
C. Constraints can be created after the table is created.
D. Constraints can be created at the same time the table is created.
E. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view.
Constraints can be created after the table is created. Use ALTER TABLE command for
AEthxnapts.lwanera:tiCo,nD:
Constraints can be created at the same time the table is created (CREATE TABLE InocmomrreacntdA).nswers
A: There is no requirements in Oracle that constraint names must start with SYS_C. "SrYacSle" tcoanbuuisled pinredfeixes for UNIQUE and NOT NULL constraints, but it is not required fnoarmuisnegr rtuolfeo. llow this
B: Not all constraints must be defines at the column level. Only NOT NULL constraint mE:uTsthberee. is no VIEW_CONSTRAINTS dictionary view in Oracle.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232
Chapter 5: Creating Oracle Database Objects
QUESTION 112:
Which two statements are true about WHERE and HAVING clauses? (Choose two) A. A WHERE clause can be used to restrict both rows and groups.
B. A WHERE clause can be used to restrict rows only.
C. A HAVING clause can be used to restrict both rows and groups. D. A HAVING clause can be used to restrict groups only.
E. A WHERE clause CANNOT be used in a query of the query uses a HAVING
cFl.aAusHe.AVING clause CANNOT be used in subqueries. Answer: B, C
Explanation:
HAVING clause to specify which groups are to be displayed and thus further restrict
the
raosuisposfoanggregate information.The Oracle server performs the following steps
wclhauesneyou use the Having
1. rows are grouped
2. the group function is applied to the group
3. the group that match the criteria in the Having clause are displayed. WHERE clause cannot be use to restrict groups
HAVING clause use to restrict groups
WHERE clause cannot be use when there is group functions. Incorrect Answers :
A. Where clause cannot be use to restrict groups
D. When HAVING clause is use rows are grouped as well.
E. WHERE clause cannot be use when there is group function, instead HAVING is to bF.eTuhser.e is no constraint to use HAVING clause in a subqueries.
Refer : Introduction to Oracle9i : SQL, Oracle University Student Guide, Aggregating
DFuantactuiosinnsg, pG.r5o-u2p0
QEEMUPELSOTYIOENES11a3n:d DEPARTMENTS data:
EMPLOYEE_I
DEMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 IT_ADMIN 5000
106 Smith 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
DEPARTMENTS
DEPARTMENT_ID DEPARTMENT_NAME
10 Admin
20 Education
30 IT
40 Human Resources
On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID
refaenrasgteorsthaenEdMPLOYEE_ID.
On the DEPARTMENTS table DEPARTMENT_ID is the primary key. Evaluate this UPDATE statement.
UPDATE employees SET mgr_id = (SELECT mgr_id FROM employees WHERE dept_id=
(SELECT department_id
FROM departments
WHERE department_name = 'Administration')), Salary = (SELECT salary
FROM employees
WHERE emp_name = 'Smith') WHERE job_id = 'IT_ADMIN';
What happens when the statement is executed?
B.. The statement executes successfully, clehaavnegsetshtehemmanaangaegreIrDIDastothNe UexLiLst,inagndvalue, ac4thnh0eda0ne0cgmhfeoapsnrlotgthyeesesetashmlewaprsiyltaohltyaoIerDye4s0t1o0w003itfhaonIrDd 110053. and 105.
C. The statement executes successfully, changes the manager ID to NULL, and cthheanegmepslothyeeesaslwariythtoID3010003faonrd 105.
D. The statement fails because there is more than one row matching the employee
a.mTheeSsmtaitehm.
ent fails because there is more than one row matching the IT_ADMIN
jEoMb PIDLOinYtEheES table.
F. The statement fails because there is no 'Administration' department in the
DEPARTMENTS table. Answer: D
Explanation:
'=' is use in the statement and subquery will return more than one row. Employees table has 2 row matching the employee name Smith.
The update statement will fail. Incorrect Answers :
A. The Update statement will fail no update was done. B. The update statement will fail no update was done. C. The update statement will fail no update was done.
E. The update statement will fail but not due to job_it='IT_ADMIN'
F. The update statement will fail but not due to department_id='Administration'
Refer : Introduction to Oracle9i : SQL, Oracle University Student Guide, Subqueries, p. 6-12
QUESTION 114:
Which SQL statement accepts user input for the columns to be displayed, the table ncoanmdei,tiaonnd? WHERE
A. SELECT &1, "&2" FROM &3
WHERE last_name = '&8'; B. SELECT &1, '&2' FROM &3
WHERE '& last_name = '&8'; C. SELECT &1, &2
FROM &3
WHERE last_name = '&8'; D. SELECT &1, '&2' FROM EMP
WHERE last_name = '&8';
Answer: C
In a WHERE clause, date and characters values must be enclosed within single qSuEaxompLtlaEpatlnCieoaTontifoEmtnMha:erPkcLso.OrrYecEtEsy_nIDta,x&COLUMN_NAME
FROM EMPLOYEES Incorrect Answers :
A. Incorrect use of " symbol
B. Incorrect use of ' symbol
D. No input for table name as EMP has been use in the statement.
Refer : Introduction to Oracle9i : SQL, Oracle University Student Guide, Producing
RiSeQadLa*bPlLe UOSu,tppu.t7w-8ith
QUESTION 115:
Evaluate the SQL statement:
SELECT ROUND(45.953, -1), TRUNC(45.936, 2) FROM dual;
Which values are displayed?
A. 46 and 45
B. 46 and 45.93
C. 50 and 45.93
D. 50 and 45.9
E. 45 and 45.93
F. 45.95 and 45.93
Answer: C Explanation:
ROUND (45.953,-1) will round value to 1 decimal places to the left.
TRUNC (45.936,2) will truncate value to 2 decimal
The answer will be 50 and 45.93
Incorrect Answers :
A. Does not meet round and truncate functions
B. Does not meet round functions
D. Does not meet truncate functions
E. Does not meet round functions
F. Does not meet round functions
Refer : Introduction to Oracle9i : SQL, Oracle University Student Guide, Single-Row functions, p. 3-13
QUESTION 116:
The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER (4) NOT NULL
CSCTUITARSYTETE_EOA_TMAD_DEADRDR_RDENESRASEMSVSVAEARVVRAHACRCRHCACAHRHRA2A2R(R52(205()0(11)5000)) NOT NULL
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. You need to determine how dispersed your customer base is.
Which expression finds the number of different countries represented in the
CUSTOMERS table?
A. COUNT(UPPER(country_address))
B. COUNT(DIFF(UPPER(country_address)))
C. COUNT(UNIQUE(UPPER(country_address))) D. COUNT DISTINTC UPPER(country_address)
E. COUNT(DISTINTC (UPPER(country_address))) Answer: E
QUESTION 117:
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2 (25) LAST_NAME VARCHAR2 (25) HIRE_DATE DATE
NEW EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2 (60)
Which DELETE statement is valid?
A. DELETE FROM employees
WHERE employee_id = (SELECT employee_id
FROM employees);
B. DELETE * FROM employees
WHERE employee_id = (SELECT employee_id
FROM new_ employees);
C. DELETE FROM employees
WHERE employee_id IN (SELECT employee_id
FROM new_employees
WHERE name = ( 'Carrey')'
D. DELETE * FROM employees
WHERE employee_id IN (SELECT employee_id
FROM new_employees
WAnHswERerE: Clast_ name = ('Carrey')'
Explanation:
The correct syntax for DELETE statement
DELETE [ FROM ] table [WHERE condition]; Incorrect Answers :
A. '=' is use in the statement and subquery will return more than one row. Error Ora-01427 : single-row subquery returns more than one row.
B. Incorrect DELETE statement
D. Incorrect DELETE statement
Refer : Introduction to Oracle9i : SQL, Oracle University Student Guide, Manipulating
Data, p. 8-19
QUESTION 118:
Examine the structure of the EMP_DEPT_VU view: Column Name Type Remarks
EMPLOYEE_ID NUMBER From the EMPLOYEES table EMP_NAME VARCHAR2(30) From the EMPLOYEES table JOB_ID VARCHAR2(20) From the EMPLOYEES table SALARY NUMBER From the EMPLOYEES table DEPARTMENT_ID NUMBER Fro m the DEPARTMENTS table
DEPT_NAME VARCHAR2(30) From the DEPARTMENTS table
Which SQL statement produces an error?
A. SELECT *
FROM emp_dept_vu;
B. SELECT department_id, SUM(salary) FROM emp_dept_vu
GROUP BY department _ id;
C. SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu
GROUP BY department _ id, job_id; D. SELECT job_id, SUM(salary) FROM emp_dept_vu
WHERE department_id IN (10,20) GROUP BY job_id
HAVING SUM (salary) > 20000
E. None of the statements produce an error; all are valid. Answer: E
Explanation :
NAIBnocSnotearrtoecmfttehanentsswtaietlrelm:noetncctsaupsreeodeurrcoeran error.
C Statement will not cause error
D Statement will not cause error
QUESTION 119:
You own a table called EMPLOYEES with this table structure: EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE
What happens when you execute this DELETE statement? DELETE employees;
A. You get an error because of a primary key violation.
B. The data and structure of the EMPLOYEES table are deleted.
C. The data in the EMPLOYEES table is deleted but not the structure. D. You get an error because the statement is not syntactically correct.
Answer : C
Explanation :
You can remove existing rows from a table by using the DELETE statement. DELETE [FROM] table
[WHERE condition];
Incorrect answer :
A Statement will not cause error
B Delete statement will not delete the table structure
D Statement will not cause error
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 8-19
QUESTION 120:
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
|
AWMhichRMERGE statement is valid? .nUaSmIeN=Gee.fmirpslto_yneaemsee||O','|N| e(.cla.estm_npalomyeeW_iHd E=N
eNIN.eOSmTEpRMloTAyVeTeAC_LHidUE) EDWSTH(HeE.ENemNployees_id, e.first_name ||','|| e.last_name);
B. MERGE new_employees c USING employees e ON (c.employee_id = eT.HemEpNloUyPeeD_AidT)EWSHEETNc.EnaXmISeT=Se.first_name ||','|| e.last_name WHEN NOT MVAALTUCEHSE(De.TemHpElNoyIeNe_SiEdR, eT.first_name ||','|| e.last_name);
C. MERGE INTO new_employees c USING employees e ON (c.employee_id = eE.XemISpTloSyTeeH_EidN) WUPHDEANTE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MINASTECRHTEVDALTUHESN(e.employees_id, e.first_name ||','|| e.last_name);
D. MERGE new_employees c FROM employees e ON (c.employee_id = eT.HemEpNloUyPeeD_AidT)EWSHEETNc.MnaAmTeC=HeE.fDirst_name ||','|| e.last_name WHEN NOT neAwT_CeHmEpDloTyeHeEsNVAINLSUEERST(eIN.emTOployees_id, e.first_name ||','|| e.last_name);
Answer: A Explanation:
The correct statement for MERGE is
MERGE INTO table_name
Incorrect answer :
B Wrong statement with the keyword EXISTS C Wrong statement with the keyword EXISTS
D Wrong statement on the MERGE new_employees
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 8-29
QUESTION 121:
The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4)
ENAME VARCHAR2 (25) JOB_ID VARCHAR2(10)
Which SQL statement will return the ENAME, length of the ENAME, and the numeric pleottseitrio"an"oifnththee ENAME column, for those employees whose ENAME ends with a the letter "n"?
A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM ESUMBPSLTORY(EEENSAWMHEE, R-1E,1) = 'n';
B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM ESUMBPSLTORY(EEENSAWMHEE, R-1E,1) = 'n';
C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EINMSPTLRO(YENEEASMWE,H1E,1R)E= 'n';
D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EINMSPTLRO(YENEEASMWE,H-E1,R1E) = 'n';
Answer: A Explanation:
INSTR is a character function return the numeric position of a named string.
ICIBNncDSoTirdrRenc(oNttaArneMstwuErenDr a,:'an')umeric position for 'a'.
D Did not return a numeric position for 'a'.
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 3-8
QUESTION 122:
Which four are valid Oracle constraint types? (Choose four.) A. CASCADE
B. UNIQUE
C. NONUNIQUE D. CHECK
E. PRIMARY KEY F. CONSTANT
G. NOT NULL
Answer: B, D, E, G Explanation:
Oracle constraint type is Not Null, Check, Primary Key, Foreign Key and
Unique
Incorrect answer:
A Is not Oracle constraint C Is not Oracle constraint F Is not Oracle constraint
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 10-3
QUESTION 123:
Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?
A. DROP emp_dept_uv;
B. DELETE emp_dept_uv; C. REMOVE emp_dept_uv;
D. DROP VIEW emp_dept_uv;
E. DELETE VIEW emp_dept_uv; F. REMOVE VIEW emp_dept_uv;
Answer: D Explanation:
DROP VIEW viewname;
Incorrect answer:
ACBE Nott a vallid drop view statementt
F Not a valid drop view statement
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 11-20
QUESTION 124:
Which is an iSQL*Plus command? A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE E. DELETE
F. RENAME Answer: D
Explanation :
The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL cTohmismcoanmdm. and returns a description of tablename, including all columns in that table, tchoeludmatnataynpdeafnorinedacichation of whether the column permits storage of NULL values.
Incorrect answer:
A INSERT is not a SQL*PLUS command B UPDATE is not a SQL*PLUS command C SELECT is not a SQL*PLUS command E DELETE is not a SQL*PLUS command
F RENAME is not a SQL*PLUS command
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 7
QUESTION 125:
Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
Which statement produces the number of different departments that have employees wSmithithla?st name
A. SELECT COUNT (*) FROM employees WHERE last _name='smith';
B. SELECT COUNT (dept_id) FROM employees WHERE last _name='smith'; C. SELECT DISTINCT (COUNT (dept_id) FROM employees WHERE last
n.aSmEeL=E'sCmTitCh'O;
UNT (DISTINCT dept_id) FROM employees WHERE last
|
_En. aSmEeL=E'sCmTitUh'N; IQE (dept_id) FROM employees WHERE last _name='smith';
QUESTION 126:
Top N analysis requires and _. (Choose two.) A. the use of rowid
B. a GROUP BY clause C. an ORDER BY clause D. only an inline view
E. an inline view and an outer query
Answer: C, E Explanation:
The correct statement for Top-N Analysis
SELECT [coloumn_list], ROWNUM FROM (SELECT [coloumn_list] FROM table
ORDER BY Top-N_coloumn) WHERE ROWNUM <=N; Incorrect answer :
A ROWID is not require
B GROUP BY clause is not require
D Must have inline view and outer query.
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 11-23
QUESTION 127:
Which statement adds a constraint that ensures the CUSTOMER_NAME column of thabelCe UhoSlTdsOaMvEaRluSe?
A. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK Bu.sAtoLmTeErR_nTaAmBeLISENcuOsTtomNUerLs LM; ODIFY CONSTRAINT cust_name_nn CHECK cNuUstLoLm;er_name IS NOT
C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cDu.sAt_LnTaEmRe_TnAnBNLOETcuNstUoLmLe;rs MODIFY customer_name CONSTRAINT cEu.sAt_LnTamERe_TnAn BISLNEOcuTstNomULerLs;MODIFY name CONSTRAINT cust_name_nn NOT NF.UALLLT;ER TABLE customers ADD CONSTRAI NT cust_name_nn CHECK customer_name NOT NULL;
Answer: C
QUESTION 128:
TsyhnetaSxAtLo caolcuumlantes tohreesanthneuaml ocnotmhlpyensaslaatiroynoafsth"emeomntphlloyyseael.aWryhpicluhscahmanognethmlyusbtobneus omSfEva$aLdl1eEu0aCt0oteT, tmhtehenuisalatmbiSpoQelvi,Lesdaslt,a1te2m*esnatl:+100 FROM emp;
by 12"?
A. No change is required to achieve the desired results. B. SELECT ename, sal, 12* (sal+100) FROM emp;
C. SELECT ename, sal, (12* sal)+100 FROM emp; D. SELECT ename, sal +100,*12 FROM emp;
Answer: B Explanation:
to achieve the result you must add 100 to sal before multiply with 12.
SELECT ename, sal, 12* (sal+100) FROM EMP; Incorrect answer :
A Multiplication and division has priority over addition and subtraction in Operator
pCreGcievdeenwcreo.ng results
D Wrong syntax
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 1-11
QUESTION 129:
You are the DBA for an academic database. You need to create a role that allows a gmroduipfyofexuissetrinsgtorows in the STUDENT_GRADES table.
Which set of statements accomplishes this?
A. CREATE ROLL registrar; GRANT MODIFY ON student_grant TO registrar; GusRerA2N, uTserre3gistrar to user 1,
B. CREATE NEW ROLE registrar; GRANT ALL ON student_grant TO registrar; GusRerA2N, uTserre3gistrar to user 1,
C. CREATE ROLL registrar; GRANT UPDATE ON student_grant TO registrar; GusRerA1N, uTseRr2O,LuEsetro3
D. CREATE ROLL registrar; GRANT UPDATE ON student_grant TO registrar; GusRerA2N, uTserre3g;istrar to user 1,
E. CREATE registrar; GRANT CHANGE ON student_grant TO registrar; GRANT
registrar; Answer: D
Explanation:
this is the correct solution for the answer. GRANT role _name to user;
Incorrect answer :
A there is no such MODIFY keyword
B invalid CREATE command, there is no such NEW keyword
CEReifnevra:lliiIdntGroRdAucNtiToncotomOmraancdle,,9thi e:rSeQiisLn, oOsruuachlheCRUHOnALivENerGksieEtywkSeotyurwddyorGduide, 13-10
QUESTION 130:
You need to modify the STUDENTS table to add a primary key on the STUDENT_ID
cisocluumrrnen. tTlyheemtapbtlye.
Which statement accomplishes this task?
A. ALTER TABLE students ADD PRIMARY KEY _ id;
B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id); C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY
sDtu. dAeLnTt_EiRd;TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY
(Es.tuAdLeTnEt_RidT);ABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
Answer: D
Explanation:
ALTER TABLE table_name
ADD [CONSTRAINT constraint] type (coloumn); Incorrect answer :
A wrong syntax
B wrong syntax
C wrong syntax
E no such MODIFY keyword
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 10-17
QUESTION 131:
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 fgrroamdehpigohinetsat verage to lowest.
Which statement produces a report that displays the student ID and GPA in the sorted obrydtehrerereqguiesstrteadr?
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;
AEsoxnrptselwadnebra:ytiFohnig: hest to lowest is DESCENDING order
Incorrect answer :
A result in ascending order
B wrong syntax with SORT keyword
C wrong syntax with SORT keyword
D default value for ORDER by is in ascending order
E wrong syntax with SORT keyword
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 2-22
QUESTION 132:
Which describes the default behavior when you create a table? A. The table is accessible to all users.
B. Tables are created in the public schema.
C. Tables are created in your schema.
D. Tables are created in the DBA schema.
E. You must specify the schema when the table is created. Answer: C
Explanation:
sorted by highest to lowest is DESCENDING order
Incorrect answer :
A grant the table privilege to PUBLIC B login as sysoper
D login as DBA or sysdba
E no such option is allow.
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 9-9
QUESTION 133:
Which four are attributes of single row functions? (Choose four.) A. cannot be nested
B. manipulate data items
C. act on each row returned
D. return one result per row
E. accept only one argument and return only one value
F. accept arguments which can be a column or an expression
Answer: B, C, D, E
manipulate data items, act on each row returned, return one result per row, accept only oIonnxcelpoylaraornegnacuetmivoaaenlns:utwea.enrd: return
A is not single row attributes
F is not single row attributes
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 3-5
QUESTION 134:
You need to create a table named ORDERS that contain four columns:
1. an ORDER_ID column of number data type
2. aCUSTOMER_ID column of number data type
3. an ORDER_STATUS column that contains a character data type
4. aDATE_ORDERED column to contain the date the order was placed.
When a row is inserted into the table, if no value is provided when the order was pshlaocueld btoeduasye'ds dinastteead.
Which statement accomplishes this?
A. CREATE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE); B. CREATE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10),
date_ordered DATE DEFAULT SYSDATE); C. CREATE OR REPLACE TABLE orders ( order_id NUMBER (10),
customer_id NUMBER (8), order_status VARCHAR2 (10),
date_ordered DATE DEFAULT SYSDATE); D. CREATE OR REPLACE TABLE orders ( order_id NUMBER (10),
customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE); E. CREATE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE = SYSDATE); F. CREATE TABLE orders ( order_id NUMBER (10),
douradtseto_r_mosredtare_truiedsdNDUAMTEBEDREF(18A0)U,),LT SYSDATE);
Answer: E
QUESTION 135:
Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements insert a row into the table? (Choose three.)
A. INSERT INTO employees VALUES (NULL, 'John', 'smith');
B. INSERT INTO employees (first_name, last_name) VALUES ('John', 'smith'); C. INSERT INTO employees VALUES ('1000, 'John', 'smith');
D. INSERT INTO employees (first_name, last_name, employee_id) VALUES (1000,
'EJo. hINn'S, E'smRTithIN');TO employees (employee_id) VALUES (1000);
F. INSERT INTO employees ( employee_id, first_name, last_name, ) VALUES (1000,
'John',''); Answer: C, E, F
Explanation :
EMPLOYEE_ID is a primary key. Incorrect answer :
A EMPLOYEE_ID cannot be null
B EMPLOYEE_ID cannot be null
D mismatch of field_name with datatype
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 10-11
QUESTION 136:
The user Sue issues this SQL statement:
GRANT SELECT ON sue. EMP TO alice WITH GRANT OPTION; The user Alice issues this SQL statement:
GRANT SELECT ON sue. EMP TO reena WITH GRANT OPTION;
The user Reena issues this SQL statement: GRANT SELECT ON sue. EMP TO timber; The user Sue issues this SQL statement: REVOKE select on sue. EMP FROM alice;
For which users does the revoke command revoke SELECT privileges on the
SUE.EMP table? A. Alice only
B. Alice and Reena
CAD.nASswuliec,re:A, CRliecen, aR,eaennda,TainmdbTerimber
Explanation :
use the REVOKE statement to revoke privileges granted to other users. Privilege gorthaenrtsedthtroough the WITH GRANT OPTION clause are also revoked.
Alice, Reena and Timber will be revoke. Incorrect answer :
A the correct answer should be Alice, Reena and Timber B the correct answer should be Alice, Reena and Timber D the correct answer should be Alice, Reena and Timber
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 13-17
QUESTION 137:
The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4)
LAST_NAME VARCHAR2 (25) JOB_ID VARCHAR2(10)
You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL
sutsaet?ement do you
A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE B%.SSAE\L_E%C'ETSeCmApPloEy'\e';e_id, last_name, job_id FROM employees WHERE job_id LIKE C%.SSAE_L'E; CT employee_id, last_name, job_id FROM employees WHERE job_id LIKE
'D%.SSAE_L'ESCCTAePmEp'l\o';yee_id, last_name, job_id FROM employees WHERE job_id
'%SA_'; Answer: A
Explanation :
ESCAPE identifier to search for the actual % and _ symbol
Incorrect answer :
B ESCAPE identifier must be use
C wrong syntax
D wrong syntax
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 2-13
QUESTION 138:
The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL CUSTOMER_ADDRESS VARCHAR2(150)
CUSTOMER_PHONE VARCHAR2(20)
The customer_name data values come from the CUSTOMER_NAME column in the
YtWCaoUbhulSiecn.TheOesdMtatEoemRpSerondt upcreodouuctepsutththisatousttaptuets?"Dear Customer customer_name, ".
A. SELECT dear customer, customer_name, FROM customer;
B. SELECT "Dear Customer", customer_name, ||',' FROM customer; C. SELECT 'Dear Customer' || customer_name ',' FROM customer; D. SELECT 'Dear Customer' || customer_name || ',' FROM customer;
E. SELECT "Dear Customer" || customer_name || "," FROM customer; F SELECT 'Dear Customer' || customer_name || ',' FROM customer;
Answer: D
Explanation :
Concatenation operator to create a resultant column that is a character expression. Incorrect answer :
A no such dear customer column
B invalid syntax C invalid syntax E invalid syntax F invalid syntax
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 1-18
QUESTION 139:
What is true about sequences?
A. Once created, a sequence belongs to a specific schema. B. Once created, a sequence is linked to a specific table.
C. Once created, a sequence is automatically available to all users.
D. Only the DBA can control which sequence is used by a certain table.
E. Once created, a sequence is automatically used in all INSERT and UPDATE
statements. Answer: A
QUESTION 140:
Which object privileges can be granted on a view? A. none
B. DELETE, INSERT,SELECT
C. ALTER, DELETE, INSERT, SELECT D. DELETE, INSERT, SELECT, UPDATE
Answer: D
Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and
EUInxPcpDolraArneTacEtio.ans:wer :
A Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UBPODbAjeTcEt privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UCPODbAjeTcEt privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and RePfDerA:TIEntroduction to Oracle9i : SQL, Oracle University Study Guide, 13-12
QUESTION 141:
Examine the SQL statement that creates ORDERS table:
CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER,
DTENRU_LDLA,TSETADTAUTSEVARCHAR2(10) CHECK (status IN ('CREDIT', 'CASH')),
RREOFEDR_EIDNCNEUSMPBREORDUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY
orEdYer_(odradtee)r)_;id,
For which columns would an index be automatically created when you execute the
(CbohvoeosSeQtwLos.t)atement?
A. SER_NO
B. ORDER_ID C. STATUS
D. PROD_ID
E. ORD_TOTAL
F. composite index on ORDER_ID and ORDER_DATE Answer: A, F
Explanation :
Index exist for UNIQUE and PRIMARY KEY constraints
Incorrect answer :
B ORDER_ID is neither UNIQUE nor PRIMARY KEY C STATUS is neither UNIQUE nor PRIMARY KEY
D PROD_ID is neither UNIQUE nor PRIMARY KEY
E ORD_TOTAL is neither UNIQUE nor PRIMARY KEY
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 10-15
QUESTION 142:
What is true of using group functions on columns that contain NULL values? A. Group functions on columns ignore NULL values.
B. Group functions on columns returning dates include NULL values.
C. Group functions on columns returning numbers include NULL values.
D. Group functions on columns cannot be accurately used on columns that contain
NE.UGLrLouvpalfuuensc.tions on columns include NULL values in calculations if you use the kEeywlwordiINC_NULLS.
xnps anera:tAon :
group functions on column ignore NULL values
Incorrect answer :
B group functions on column ignore NULL values
C group functions on column ignore NULL values
D NVL function can be use for column with NULL values
E no such INC_NULLS keyword
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 5-12
QUESTION 143:
The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12)
SEMESTER_END DATE GPA NUMBER(4,3)
Which statement finds the highest grade point average (GPA) per semester?
A. SELECT MAX (gpa) FROM student _ grades WHERE gpa IS NOT NULL;
B. SELECT (gpa) FROM student _ grades GROUP BY semester_end WHERE gpa IS C.OSTENLEUCLTL;MAX (gpa) FROM student _ grades WHERE gpa IS NOT NULL GDR. SOEULPECBYT MseAmXest(egrp_ae)nGd;ROUP BY semester_end WHERE gpa IS NOT NULL FER. SOEMLEstCuTdeMntA_Xgr(agdpeas); FROM student _ grades GROUP BY semester_end WHERE gpa IS NOT NULL;
Answer: C
Explanation:
for highest gpa value MAX function is needed,
for result with per semester GROUP BY clause is needed
Incorrect answer :
A per semester condition is not included
B result would not display the highest gpa value
D invalid syntax error
E invalid syntax error
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 5-7
QUESTION 144:
In which four clauses can a subquery be used? (Choose four.) A. in the INTO clause of an INSERT statement
B. in the FROM clause of a SELECT statement
C. in the GROUP BY clause of a SELECT statement
D. in the WHERE clause of a SELECT statement
EFA..niisnnwtthehere:VSBAE, LTDU,cElEa,SuFscelaoufsaenoUf aPnDIANTSEEsRtTatesmtateenmt ent
Explanation :
subquery can be use in the FROM clause of a SELECT statement, in the WHERE cSlEauLsEeCoTf astatement, in the SET clauses of an UPDATE statement, in the VALUES cstlatuesme eonfta. n INSERT
Incorrect answer :
A subquery cannot be used
C subquery cannot be used
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 6-5
QUESTION 145:
Examine this statement:
SELECT student_id, gpa FROM student_grades WHERE gpa >&&value;
You run the statement once, and when prompted you enter a value of 2.0. A report is phraopdpuecnesdw. Whehnaytou run the statement a second time?
A. An error is returned.
B. You are prompted to enter a new value.
C. A report is produced that matches the first report produced.
D. You are asked whether you want a new value or if you want to run the report based on the previous value.
Answer: C
Explanation:
use the double-ampersand if you want to reuse the variable value without prompting
Ihnecoursrercteacnhswtiemr e:. A is not an error
B && will not prompt user for second time
D && will not ask the user for new value
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 7-13
QUESTION 146:
Which SQL statement returns a numeric value?
A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
B. SELECT ROUND(hire_date)
FROM EMP;
C. SELECT sysdate-hire_date
FROM EMP;
DFAR
SOswEMeLrE: CMPT; O_NUMBER(hire_date + 7)
Explanation:
DATE value subtract DATE value will return numeric value. Incorrect answer :
A does not return numeric value B does not return numeric value D does not return numeric value
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 9-13
QUESTION 147:
What are two reasons to create synonyms? (Choose two.) A. You have too many tables.
B. Your tables are too long.
C. Your tables have difficult names.
D. You want to work on your own tables.
E. You want to use another schema's tables.
F. You have too many columns in your tables. Answer: A, C
Explanation:
create a synonyms when the table name is difficult or you have too many tables
Incorrect answer :
B create a synonyms when the table name is difficult or you have too many tables D create a synonyms when the table name is difficult or you have too many tables E create a synonyms when the table name is difficult or you have too many tables F create a synonyms when the table name is difficult or you have too many tables Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 12-24
QUESTION 148:
What is true about updates through a view?
A. You cannot update a view with group functions.
B. When you update a view group functions are automatically computed.
C. When you update a view only the constraints on the underlying table will be in eDf.feWcth. en you update a view the constraints on the views always override the ctaobnlsetsr.aints on the underlying
Answer: A
You need to write a SQL statement that returns employee name, salary, department ID,
QanUdEmSaTxIimOuNm149:
salary earned in the department of the employee for all employees who earn less than tshaelamryaixnimthuemir department.
Which statement accomplishes this task?
A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, dbe.dpeaprt_midenAtsNbDWa.HsaElR<EMaA.dXep(sta_li)dG=ROUP BY b.dept_id;
B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dFeRpOt_Mid,eMmpAloXy(eseasl)GmRaOxsUalP BY dept_ id ) b WHERE a.dept_id =b.dept_id AND a.sal C.bS.mELaxEsCalT; a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal
<ma(SxEsaLlEFCRTOMAemX(pslaoly)ees b GROUP BY dept_id;
D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MemApXlo(yseael)smGaRxOsaUl PFRBOYMdept_id) WHERE a.sal < maxsal;
Answer: B Explanation :
function MAX(column_name)
Incorrect answer :
A invalid statement
C inner query return more than one line
D column maxsal does not exists.
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 5-7
QUESTION 150:
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)
A promotional sale is being advertised to the customers in France. Which WHERE
cluasutosemiedresntthifaiteasre located in France?
A. WHERE lower(country_address) = "france" B. WHERE lower(country_address) = 'france' C. WHERE lower(country_address) IS 'france'
D. WHERE lower(country_address) = '%france%'
EA.nWswHeEr:RBE lower(country_address) LIKE %france%
Explanation:
WHERE lower(country_address)='france' Incorrect answer :
A invalid use of symbol ""
C invalid use of IS keyword
D invalid use of % in condition
E invalid use of condition
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 2-12
QUESTION 151:
Which are iSQL*Plus commands? (Choose all that apply.) A. INSERT
B. UPDATE C. SELECT
D. DESCRIBE E. DELETE
F. RENAME
Answer: D Explanation:
The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL reotmurmnsanadd.eTshcrisipctioomnmofantadblename, including all columns in that table, the datatype for einadcihcactoiolunmonf wanhdetahner the column permits storage of NULL values.
Incorrect answer:
A INSERT is not a SQL*PLUS command B UPDATE is not a SQL*PLUS command C SELECT is not a SQL*PLUS command E DELETE is not a SQL*PLUS command
F RENAME is not a SQL*PLUS command
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 7
QUESTION 152:
Examine the description of the CUSTOMERS table: CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL STREET_ADDRESS VARCHAR2(150) CITY_ADDRESS VARCHAR2(50)
STATE_ADDRESS VARCHAR2(50)
PCCOR
OTVTNAOITNLMR_CYECER_O_A_ADPDDEHDDVORRANEERESSCSVSHVAVARARCR2CHC(H1AH2ARA)R2R(22(055)00))
The CUSTOMER_ID column is the primary key for the table.
Which statement returns the city address and the number of customers in the cities Los
AFrnagnecliesscoo?r San
A. SELECT city_address, COUNT(*) FROM customers
WHERE city _ address IN ('Los Angeles','San Fransisco'); B. SELECT city_address, COUNT (*)
FROM customers
WHERE city address IN ( 'Los Angeles', 'San Fransisco') GROUP BY city_address;
C. SELECT city_address, COUNT(customer_id) FROM customers
WHERE city_address IN ( 'Los Angeles', 'San Fransisco')
GROUP BY city_address, customer_ id;
D. SELECT city_address, COUNT (customer_id) FROM customers
GROUP BY city_ address IN ('Los Angeles','San Fransisco'); Answer: C
QUESTION 153:
What does the FORCE option for creating a view do? A. creates a view with constraints
B. creates a view even if the underlying parent table has constraints
C. creates a view in another schema even if you don't have privileges
D. creates a view regardless of whether or not the base tables exist
Answer: D Explanation:
create a view regardless of whether or not the base tables exist. Incorrect answer :
A the option is not valid B the option is not valid C the option is not valid
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 11-3
QUESTION 154:
B..dmcaotoamdmipfliaentseipsthuaelatsrttaironunscatlucatnriegounanaogdnedasattaatbeinlmeaentat ble .
C. modifies the data but not the structure of a table
D. modifies the structure but not the data of a table
Answer: C Explanation:
modifies the data but not the structure of a table
Incorrect answer :
A DML does not complete a transaction
B DDL modifies the structure and data in the table
D DML does not modified table structure.
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 8-3
QUESTION 155:
Which two tasks can you perform using only 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:
converting data type into characters.
Incorrect answer :
A does not serve TO_CHAR function B does not serve TO_CHAR function D does not serve TO_CHAR function F does not serve TO_CHAR function
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 3-31
QUESTION 156:
The DBA issues this SQL command: CREATE USER scott IDENTIFIED by tiger;
What privileges does the user Scott have at this point?
A. no privileges
BDC. oanlllythtehperSCivEOilLNeEgNeCsETCopfTraipvdrielievfgailueelgteuser
Answer: A
Explanation:
when a user is created, by default no privilege is granted
Incorrect answer :
B SELECT is not grant
C CONNECT is not grant
D default profile is grant by default not privilege.
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 13-6
QUESTION 157:
Exhibit:
Examine the data in the EMPLOYEES table. Examine the subquery:
SELECT last_name
FROM employees
WHERE salary IN (SELECT MAX(salary) FROM employees
GROUP BY department_id); Which statement is true?
A. The SELECT statement is syntactically accurate.
B. The SELECT statement does not work because there is no HAVING clause.
C. The SELECT statement does not work because the column specified in the GROUP BSEYLcElaCuTseliisst.not in the
D. The SELECT statement does not work because the GROUP BY clause should be in
it ethmeasiunbqquueerryy.and not
Answer: A
QUESTION 158:
Examine the statement: GRANT select, insert, update ON student_grades
TWWOIhTimcHhanGtwaRgoeAarNreTtrOueP?T(ICOhNo;ose two.)
A. MANAGER must be a role.
B. It allows the MANAGER to pass the specified privileges on to other users.
C. It allows the MANAGER to create tables that refer to the STUDENT_GRADES tDab. lIet .allows the MANAGER to apply all DML statements on the SET. IUt DalEloNwTs_tGheRMAADNESAtGabElRe.the ability to select from, insert into, and update the SF.TIUt aDllEoNwTs _thGeRMAADNEAS GtaEblRe.the ability to select from, delete from, and update the STUDENT_ GRADES table.
Answer: B, E
Explanation:
GRANT ROLE to ROLE/USER Incorrect answer :
A Role can be grant to user
C Create table privilege is not granted
D Execute privilege is not granted
F Delete privilege is not granted
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 13-15
QUESTION 159:
Which best describes an inline view? A. a schema object
B. a subquery that can contain an ORDER BY clause
C. another name for a view that contains group functions
D. a subquery that is part of the FROM clause of another query
Answer: D Explanation:
a subquery that is part of the FROM clause of another query
Incorrect answer :
A is not a schema object
B subquery can contain GROUP BY clause as well. C does not necessary contains group functions
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 11-21
QUESTION 160:
Examine the structure of the EMPLOYEES and DEPARTMENTS tables: EMPLOYEES
EMPLOYEE_ID NUMBER
DLDMAEAPSNATA_RNGTAMERME_NEIDTV_ANIDRUCMNHUBAMERB2E(2R5)
DEPARTMENT_ID NUMBER MANAGER_ID NUMBER DEPARTMENT_NAME VARCHAR2(35) LOCATION_ID NUMBER
You want to create a report displaying employee last names, department names, and qouceartyiosnhso.uWldhyicohu use to create an equi-join?
A. SELECT last_name, department_name, location_id
FROM employees , department ;
B. SELECT employees.last_name, departments.department_name, dFeRpOarMtmeemntpsl.oloyceaetsioen,_diedpartments D
WHERE e.department_id = d.department_id;
C. SELECT e.last_name, d.department_name, d.location_id
FROM employees e, departments D WHERE manager_id = manager_id;
D. SELECT e.last_name, d.department_name, d.location_id
FROM employees e, departments D
WHERE e.department_id = d.department_id; Answer: D
Explanation:
Equijoins are also called simple joins or inner joins. Equijoin involve primary key and
If rceoirgrneckteayn.swer :
A there is no join
B invalid syntax
C does not involve the join in the primary and foreign key
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 4-8
QUESTION 161:
The PRODUCTS table has these columns: PRODUCT_ID NUMBER(4) PRODUCT_NAME VARCHAR2(45) PRICE NUMBER(8,2)
Evaluate this SQL statement: SELECT *
FROM PRODUCTS
ORDER BY price, product _ name; What is true about the SQL statement?
A. The results are not sorted.
BCD. The results are sortted nanlupmhaebriiectaiclllayl.layn. d then alphabetically.
Answer: D
Explanation:
the result is sort by price which is numeric and follow by product_name which is
Inlpchoarrbeectticaanlslyw. er :
A the results are sorted
B the results are sorted with alphabetically as well
C the results are sorted with numerically as well
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 2-2
QUESTION 162:
In which two cases would you use an outer join? (Choose two.) A. The tables being joined have NOT NULL columns.
B. The tables being joined have only matched data. C. The columns being joined have NULL values.
D. The tables being joined have only unmatched data.
E. The tables being joined have both matched and unmatched data.
F. Only when the tables have a primary key/foreign key relationship. Answer: C, E
Explanation:
You use an outer join to also see rows that do not meet the join condition. Incorrect answer :
A meet a join condition
B meet a join condition
D meet non join condition only
F does not take into consideration of primary key and foreign key relationship
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 4-17
QUESTION 163:
Which constraint can be defined only at the column level? A. UNIQUE
B. NOT NULL
C. CHECK
D. PRIMARY KEY E. FOREIGN KEY
the NOT NULL constraint can be specified only at the column level, not at the table
AElexvnpeslwla. nera:tiBon:
Incorrect answer :
A UNIQUE can be define at table level
C CHECK can be define at table level
D PRIMARY KEY can be define at table level
E FOREIGN KEY can be define at table level
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 10-8
QUESTION 164:
Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER NOT NULL EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) SAL NUMBER
MGR_ID NUMBER DEPARTMENT_ID NUMBER
You want to create a SQL script file that contains an INSERT statement. When the sINcrSipEtRisTrsutna,tethmeent should insert a row with the specified values into the EINMSPELROTYstEaEteSmteanbtles.hTouhled pass values to the table columns as specified below:
EMPLOYEE_ID: Next value from the sequence
EMP_ID_SEQEMP_NAME and JOB_ID: As specified by the user during run time, vharroiaubglhessubstitution
SAL: 2000
MGR_ID: No value
DEPARTMENT_ID: Supplied by the user during run time through substitution vstaartieambleen.tTshheouINldSfEaRilTif the user supplies a value other than 20 or 50.
Which INSERT statement meets the above requirements?
A. INSERT INTO employees
VALUES (emp_id_seq.NEXTVAL, &ename','&jobid', 2000, NULL, &did); B. INSERT INTO employees
VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid',
2000, NULL, &did IN (20,50)); C. INSERT INTO (SELECT * FROM employees
WHERE department_id IN (20,50))
VALUES (emp_id_seq.NEXTVAL, &ename','&jobid', 2000, NULL, &did); D. INSERT INTO (SELECT *
FROM employees
WHERE department_id IN (20,50) WITH CHECK OPTION)
VALUES (emp_id_seq.NEXTVAL, &ename','&jobid', 2000, NULL, &did);
dFWeR.pHIONaErMStRmEEeRmn(Tdtp_eIliopNdayT=retOem5s0(eS)nEt_LidEC=T20*AND
WITH CHECK OPTION )
VALUES (emp_id_seq.NEXTVAL, &ename','&jobid', 2000, NULL, &did); Answer: D
QUESTION 165:
The user Alice wants to grant all users query privileges on her DEPT table. Which
SacQcLomstpaltiesmheesnthis?
A. GRANT select ON dept
TO ALL_ USER;
B. GRANT select ON dept
TO ALL;
C. GRANT QUERY ON dept
TO ALL_USERS
D. GRANT select ON dept
TO PUBLIC; Answer: D
Explanation :
view the columns associated with the constraint names in the
InScoErRre_cCt OanNsSw_eCr O: LUMNS view.
A table to view all constraints definition and names
B show all object name belong to user C does not display column associated E no such view
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 10-25
QUESTION 166:
Which view should a user query to display the columns associated with the constraints obny tahteabulseero?wned
A. USER_CONSTRAINTS B. USER_OBJECTS
C. ALL_CONSTRAINTS
D. USER_CONS_COLUMNS E. USER_COLUMNS
Answer: D
view the columns associated with the constraint names in the
EUInxScpoElrRarne_acCtiOoanNs:Sw_eCr O: LUMNS view.
A table to view all constraints definition and names
B show all object name belong to user C does not display column associated E no such view
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 10-25
QUESTION 167:
Examine these statements: CREATE ROLE registrar
GRANT UPDATE ON dtudent_grades TO registrar; GRANT registrar to user1, user2, user3;
What does this set of SQL statements do?
A. The set of statements contains an error and does not work.
B. It creates a role called REGISTRAR, adds the MODIFY privilege on the roTleU,DanEdNgTiv_eGsRthAeDREESGoIbSjTecRtAtoRthroele to three users.
C. It creates a role called REGISTRAR, adds the UPDATE privilege on the roTleU,DanEdNgTiv_eGsRthAeDREESGoIbSjTecRtAtoRthroele to three users.
D. It creates a role called REGISTRAR, adds the UPDATE privilege on the roTleU,DanEdNcTre_aGteRsAthDreEeSuosbejrescwt tiothththee role.
E. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, ronlde gtoivtehsetSheTURDEGEINSTT_RGARRADES object.
F. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three uUsPeDrsA, aTnEd rgoilveetsoththee registrar.
Answer: C Explanation :
the statement will create a role call REGISTRAR, grant UPDATE on student_grades to rgergainsttrtahre, role to user1,user2 and user3.
Incorrect answer :
A the statement does not contain error
B there is no MODIFY privilege
D statement does not create 3 users with the role
E privilege is grant to role then grant to user
F privilege is grant to role then grant to user
QUESTION 168:
Which two statements about subqueries are true? (Choose two.)
.. ASusbuqbuqeureeierys CcshaAnoNurleNedtrOuriesTevtrdeibeeozvennerleoyosnotienlrydSmobQonyLreemrqrouorewe.rsyth. satnattewmoenletvs.els.
E. A subquery CANNOT be used in an SQL query statement that uses group functions.
F. When a subquery is used with an inequality comparison operator in the outer SQL
sintattheemSe
tL, EthCeTcoclluamusne loisftthe subquery should contain only one column.
Answer: B, F
Explanation :
subquery can retrieve zero or more rows, subquery is used with an inequality oapmerpaatorirsionnthe outer SQL statement, the column list in the SELECT clause of the ounbeqcuoelruymsnh.ould contain only
Incorrect answer :
A subquery can retrieve zero or more rows
C subquery is not SQL query statement
D subquery can be nested
E group function can be use with subquery
QUESTION 169:
You need to design a student registration database that contains several tables storing aincfaodremmaitcion.
The STUDENTS table stores information about a student. The STUDENT_GRADES
it bfolermstaotrieosn about the student's grades. Both of the tables have a column named
STUDENT_ID.cTohluemn in the STUDENTS table is a primary key.
You need to create a foreign key on the STUDENT_ID column of the poTiUntDs EtoNtThe_GSTRUADENSTta_bIlDe tchoaltumn of the STUDENTS table. Which statement creates the foreign key?
A. CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gCpOaNNSUTMRABIENRT(4s,t3u)d, ent_id_fk REFERENCES (student_id) FOREIGN KEY student Bs.tuCdRenEtA_iTdE));TABLE student_grades(student_id NUMBER(12),semester_end DATE, gstpuadeNnUt_MidB_fEkRF(O4,R3)E, IGN KEY (student_id) REFERENCES students (student_id));
C. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gCpOaNNSUTMRABIENRT(4F,3O)R, EIGN KEY (student_id) REFERENCES student (student_id)); D. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE,
gCpOaNNSUTMRABIENRT(4s,t3u)d, ent_id_fk FOREIGN KEY (student_id) REFERENCES students
(student_id)); Answer: D
Explanation :
CONSTRAINT name FOREIGN KEY (column_name) REFERENCES table_name
(colomn_name); Incorrect answer : A invalid syntax
BCReifnevra:liIdntsryondtuacxtion to Oracle9i : SQL, Oracle University Study Guide, 10-14
QUESTION 170:
Evaluate the SQL statement: TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)
A. It releases the storage space used by the table.
B. It does not release the storage space used by the table.
C. You can roll back the deletion of rows after the statement executes.
D. You can NOT roll back the deletion of rows after the statement executes.
E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement exrreocru. tes will display an
F. You must be the owner of the table or have DELETE ANY TABLE system ptarbivleileges to truncate the DEPT
Answer: B, D, F
Explanation :
does not release the storage space used by the table,
Can not rollback the deletion of rows after the statement executes, You must be the owner of the table or have DELETE ANY TABLE system privilege to truncate the DEPT table.
Incorrect answer : A is not true
C is not true
E is not true
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 8-18
QUESTION 171:
Which two statements are true about constraints? (Choose two.)
A. The UNIQUE constraint does not permit a null value for the column.
B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE Co.nTshtreaiPnRtsI.MARY KEY and FOREIGN KEY constraints create a UNIQUE index.
D. The NOT NULL constraint ensures that null values are not permitted for the column.
Answer: A, D
Explanation :
the UNIQUE constraint does not permit a null value for the column
The NOT NULL constraint ensure that null value are not permitted for the
IBnocsluotamrtrenmcteanntsiws nerot: true
C statement is not true
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 10-9
QUESTION 172:
Which three are true? (Choose three.)
A. A MERGE statement is used to merge the data of one table with data from another. B. A MERGE statement replaces the data of one table with that of another.
C. A MERGE statement can be used to insert new rows into a table.
D. A MERGE statement can be used to update existing rows in a table. Answer: A, B, C
Explanation :
Merge is used to merge the data of one table with data from another, replaces the data otafbolenewith that of another, used to insert new rows into a table.
Incorrect answer :
D MERGE statement can be used to update existing rows in a table if the condition match
QUESTION 173:
Which is a valid CREATE TABLE statement?
A. CREATE TABLE EMP9$# AS (empid number(2)); B. CREATE TABLE EMP*123 AS (empid number(2)); C. CREATE TABLE PACKAGE AS (packid number(2));
D. CREATE TABLE 1EMP_TEST AS (empid number(2)); Answer: A
Explanation :
Table names and column names must begin with a letter and be 1-30 characters long. CAh-Zar,a-czte, r0s-9, _, $ and # (legal characters but their use is discouraged).
Incorrect answer :
B Non alphanumeric character such as "*" is discourage in Oracle table name. D Table name must begin with a letter.
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 9-4
QUESTION 174:
A SELECT statement can be used to perform these three functions:
1W23. hCBCihrhciohnogssetetorcogoofewltkuhsemeyfrnrwdosoamftrradoastmhtdaaebatslitcesa.rbisblteoersetdheinsedcifafpearebniltitiaebsl?es by creating a link between them.
A. difference, projection, join B. selection, projection, join C. selection, intersection, join
D. intersection, projection, join
E. difference, projection, product
Answer: B Explanation :
choose rows from a table is SELECTION, Choose column from a table is PROJECTION
Bring together data in different table by creating a link between them is JOIN. Incorrect answer :
A answer should have SELECTION, PROJECTION and JOIN. C answer should have SELECTION, PROJECTION and JOIN. D answer should have SELECTION, PROJECTION and JOIN. E answer should have SELECTION, PROJECTION and JOIN.
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 1-6
QUESTION 175:
Which four are types of functions available in SQL? (Choose 4) A. string
B. character
C. integer D. calendar E. numeric
F. translation
G. date
H. conversion
Answer: B, E, G, H Explanation :
SQL have character, numeric, date, conversion function.
Incorrect answer :
A SQL have character, numeric, date, conversion function. C SQL have character, numeric, date, conversion function. D SQL have character, numeric, date, conversion function. F SQL have character, numeric, date, conversion function.
RQeUfErS:TInIOtroNdu1c7t6io:n to Oracle9i : SQL, Oracle University Study Guide, 3-3
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 the
DYoEuPAcrReaTtMedEaNsTeqSuteanbcle called EMP_ID_SEQ in
orderto populate sequential values for the EMPLOYEE_ID column of the
EWMhiPcLhOtwYoEsEtaStetambelen.ts 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 Co.lTumhenE. MP_ID_SEQ sequence is not affected by modifications to the EMPLOYEES tDab. Alen. y other column of NUMBER data type in your schema can use the
EM. TPh_eIDEM_SPE_QIDs_eSqEueQncse.quence is dropped automatically when you drop the
EF.MTPhLe OEMYEPE_ISDt_abSlEeQ.
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 oatbhleer. cAonluymn of NUMBER data type in your schema can use the EMP_ID_SEQ Ineqcourerneccet.answer :
A EMP_ID_SEQ sequence can be use to populate JOB_ID
B EMP_ID_SEQ sequence will not be invalidate when column in EMPLOYEE_ID is mE oEdMifPy._ID_SEQ sequence will be dropped automatically when you drop the EFMEMPLPO_IYDE_ESSEQtabsleeq.uence will be dropped automatically when you drop the RMefePrL:OInYtrEoEd_uIcDtiocnoltuomOnr.acle9i : SQL, Oracle University Study Guide, 12-4
QUESTION 177:
Which object privileges can be granted on a view? A. none
B. DELETE, INSERT,SELECT
C. ALTER, DELETE, INSERT, SELECT D. DELETE, INSERT, SELECT, UPDATE
Answer: D
OEAxplanation v:iglegoenoVnIVEIWEWis DisEDLEELTEET,EIN, ISNESRETR, TR,ERFEEFREERNECNECSE, S,ESLEELCETCaTnadnd
BbOjebcjtecptripvriile
IUnPcDorAreTcEt .answer :
C Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and
RePfDerA:TIEntroduction to Oracle9i : SQL, Oracle University Study Guide, 13-12
QUESTION 178:
Which is an iSQL*Plus command? A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE E. DELETE
F. RENAME Answer: D
Explanation :
The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL reotmurmnsanadd.eTshcrisipctioomnmofantadblename, including all columns in that table, the datatype for einadcihcactoiolunmonf wanhdetahner the column permits storage of NULL values.
Incorrect answer:
A INSERT is not a SQL*PLUS command B UPDATE is not a SQL*PLUS command C SELECT is not a SQL*PLUS command E DELETE is not a SQL*PLUS command
F RENAME is not a SQL*PLUS command
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 7
QUESTION 179:
Evaluate these two SQL statements:
SELECT last_name, salary, hire_dateFROM EMPLOYEES ORDRE BY salary DSEELSECC; T last_name, salary, hire_dateFROM EMPOLYEES ORDER BY 2 DESC; What is true about them?
A. The two statements produce identical results. B. The second statement returns a syntax error.
C. There is no need to specify DESC because the results are sorted in descending order
y. Tdehfeautwlto.
statements can be made to produce identical results by adding a column
ainliathsefosercthoendsaSlaQrLy cstoaltuemnent.
Answer: A
the two statement produce identical results as ORDER BY 2 will take the second
Ecsoxrlptuilmnagnnacatoisolunm: n.
Incorrect answer :
B there is no syntax error
C result are sorted in ascending order by default
D ORDER BY 2 will take the second column as sorting column.
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 2-22
QUESTION 180:
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which MERGE statement is valid?
A. MERGE INTO new_employees c
USING employees e
ON (c.employee_id = e.employee_id) WHEN MATCHED THEN
UPDATE SET
C.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT
value
S(e.employee_id, e.first_name ||',
'||e.last _ name);
B. MERGE new_employees c
USING employees e
ON (c.employee_id = e.employee_id) WHEN EXISTS THEN
UPDATE SET
C.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||',
'||e.last _ name);
C. MERGE INTO new_employees cUSING employees e
ON (c.employee_id = e.employee_id) WHEN EXISTS THEN
UICN.PnHSDaEmARNTeTEN=OSe.ETfiTrMst_AnTaCmHeE||D','||TeH.lEasNt_name
value
S(e.employee_id, e.first_name ||',
'||e.last _ name);
D. MERGE new_employees c
FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN
UPDATE SET
c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO
new_employees valueS(e.employee_id, e.first_name ||',
'||e.last _ name); Answer: A
Explanation :
this is the correct MERGE statement syntax
Incorrect answer :
B it should MERGE INTO table_name
C it should be WHEN MATCHED THEN D it should MERGE INTO table_name
Refer : Introduction to Oracle9i : SQL, Oracle University Study Guide, 8-29
QUESTION 181:
Which SQL statement displays the date March 19, 2001 in a format that appears as
"MNairncehte2e0n0th1 o1f2:00:00 AM"?
A. SELECT
TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth
"of" Month YYYY fmHH:MI:SS AM') NEW_ DATE FROM dual;
B. SELECT
TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth
"of" Month YYYY fmHH:MI:SS AM') NEW_ DATE HH:MI:SS AM') NEW _ DATE FROM dual;
C. SELECT
TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month
YNYEWYY_ DATE FROM dual;
D. SELECT
TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY), 'fmDdspth "of" Month
YYYYfmtHH:HI:SS AM')NEW_DATE FROM dual;
Answer: A
QUESTION 182:
Which one is a system privilege? A. SELECT
B. DELETE
C. EXECUTE
D. ALTER TABLE E. CREATE TABLE
Answer: E
QUESTION 183:
You need to perform these tasks:
1. Create and assign a MANAGER role to Blake and Clark
2. Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark
Which set of SQL statements achieves the desired results?
A. CREATE ROLE manager; GRANT create table, create view TO manager;
GRANT manager TO BLACK, CLARK; B. CREATE ROLE manager;
GRANT create table, create voew
TO manager;
GRANT manager ROLE TO BLACK, CLARK;
C. GRANT manager ROLE TO BLACK, CLARK; GRANT create table, create voew
TO BLACK CLARK;
***MISSING*** Answer: A Explanation:
Result of commands:
QUESTION 184:
Which two are true about aggregate functions? (Choose two.)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the select clause and in tShEeLWECHTERstEatcelmauesnet.of a
C. You can mix single row columns with aggregate functions in the column list of a grEoLuEpiCnTg ostnattehme esnintgblye row columns.
D. You can pass column names, expressions, constants, or functions as parameter to an aEg.gYroeugactaenfuunsectaiogng.regate functions on a table, only by grouping the whole table as one sFi.nYgoleugcraonunpo.t group the rows of a table by more than one column while using aggregate functions.
Answer: A, E
QUESTION 185:
Which statement correctly describes SQL and /SQL*Plus? CAB. Both SQLiand /ogSugQanL*plus allow manipulation of values in;the database.dsiodseatshtane;oSt QL
D. /SQL*mPalnuspluaelncates dgizaeetafsoSarnQcdoLmtasbmalteumndiecfnaitniaintngiodwnsieitnhdthsheethdOeamrtabctolaestehser/Svs eQrrvLteo*r;aPSclcuQesLs
OsfreotlarlcroatoecwgmxlneemeicpznauretntosipnapSrgnuiQedlSatLaQstireoLynndisnsotatftetherefmamceentots.the server.
values in the database. Answer: A QUESTION 186:
Examine the structure of the STUDENTS table: STUDENT_ID NUMBER NOT NULL, Primary Key STUDENT_NAME VARCHAR2(30)
COURSE_ID VARCHAR2(10) NOT NULL MARKS NUMBER
START_DATE DATE FINISH_DATE DATE
You need to create a report of the 10 students who achieved the highest ranking in the
rwsehIoNcTomSQplLeted the course in the year 1999.
Which SQL statement accomplishes this task?
A. SELECT student_ id, marks, ROWNUM "Rank" FROM students
WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99
AND course_id = 'INT_SQL' ORDER BY mark DESC;
B. SELECT student_id, marks, ROWID "Rank"
FROM students
WHERE ROWID <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL'
ORDER BY mark;
C. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks
FROM students
WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-
99'
AND course_id = 'INT_SQL' ORDER BY mark DESC;
D. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks
FROM students
WHERE (finish_date BETWEEN '0 1-JAN-99 AND '31-DEC-99' AND course_id = 'INT_SQL'
OEFWR.RHSODEMELRRE(SCBRETYOLsEWmtuCaNdrTekUnsMtDuidE<,eS=mnCt1a_)0rikd;s, ,mRaOrkWs NUM "Rank"
FROM students ORDER BY marks) WHERE ROWNUM <= 10
AND finish date BETWEEN '01-JAN-99' AND '31-DEC-99'
AND course _ id 'INT_SQL'; Answer: D
QUESTION 187:
Exhibit
Examine the data in the EMPLOYEES and DEPARTMENTS tables.
You want to retrieve all employees' last names, along with their manager's last names dnepdatrhtemirent names. Which query would you use?
A. SELECT last_name, manager_id, department_name
FROM employees e
FULL OUTER JOIN department d ON (e.department_id = d.department_id); B. SELECT e.last_name, m.last_name, department_name
FROM employees e
LEFT OUTER JOIN employees m on ( e.managaer_id = m.employee_id) LEFT OUTER JOIN department d ON (e.department_id = d.department_id); C. SELECT e.last_name, m.last_name, department_name
FROM employees e
RIGT OUTER JOIN employees m on ( e.manager_id = m.employee_id) FULL OUTER JOIN department d ON (e.department_id = d.department_id); D. SELECT e.last_name, m.last_name, department_name
FROM employees e
LEFT OUTER JOIN employees m on ( e.manager_id = m.employee_id) RIGT OUTER JOIN department d ON (e.department_id = d.department_id); E. SELECT e.last_name, m.last_name, department_name
FRRFROM employees e Ne, dmmeappnalarotgmyere_nsitdsm,ddoeOnpNa( ret(.mem.deanenpta_agnretamrm_eiednt=_imd =.emd.pdleopyaeretm_iedn)t_id)
.RISGOEHMLTEeOCmUTpTTloaEsyRte_enJsaOemI
JOIN department d ON (e.department_id = d.department_id); Answer: B
QUESTION 188:
The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12)
SEMESTER_END DATE
GPA NUMBER(4,3)
The registrar has asked for a report on the average grade point average (GPA), sorted fprooimnt tahveehraigheetsot egarcahdesemester, starting from the earliest date.
Which statement accomplish this?
A. SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY semester_end DESC, gpa DESC; B. SELECT student_id, semester_end, gpa FROM student_grades
ORDER BY semester_end, gpa ASC
ORDER BY semester_end DESC, gpa DESC; C. SELECT student_id, semester_end, gpa FROM student_grades
ORDER BY semester_end DESC, gpa DESC;
D. SELECT student_id, semester_end, gpa
FROM student_grades
ORDER BY semester_end DESC, gpa DESC; E. SELECT student_id, semester_end, gpa FROM student_grades
ORDER BY gpa DESC, semester _ end ASC; Answer: C
QUESTION 189:
Which are /SQL*Plus commands? (Choose all that apply.) A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE E. DELETE
FA.nRswENerA: AM,EB, C, D, E
QUESTION 190:
Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25) HIRE_DATE DATE
Which INSERT statement is valid?
A. INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES (1000, 'John', 'smith','01/01/01);
B. INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES (1000, 'John', 'smith','01 january 01');
C. INSERT INTO employees(employee_id, first_name, last_name, Hire_date)
VALUES (1000, 'John', 'smith', To_ date ('01/01/01));
D. INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES (1000, 'John', 'smith','01-Jan-01');
Answer: A
QUESTION 191:
Which two statements about subqueries are true? (Choose two.) A. A single row subquery can retrieve data from only one table.
B. A SQL query statement cannot display data from table B that is referred to in its sinucblquudeerdy,inunthleesms taaibnlequBerisy's FROM clause.
C. A SQL query statement can display data from table B that is referred to in its sinubitqsuoewryn, wFRithOoMut cinlaculused.ing B
D. A single row subquery can retrieve data from more than one table.
E. A single row subquery cannot be used in a condition where the LIKE operator is uF.seAdmfourltciopmle-praorwisosnu.bquery cannot be used in an INSERT statement to insert multiple rows at a time.
Answer: D, F
QUESTION 192:
User Mary has a view called EMP_DEPT_LOC_VU that was created based on the EDMEPPALROTYMEENS,TS, and LOCATIONS tables. She has the privilege to create a public sliykneotnoycmre,aatnedawsyonuoldnym for this view that can be used by all users of the database.
Which SQL statement can Mary use to accomplish that task?
O. CReRmEpaAr_TyTdEEe(epPmt_UploB_cdL_eIvpCut_SloYcN_vOuN);YM EDL:_VVUU
C. CREATE PUBLIC SYNONYM EDL_VU FOR emp _dept_loc_vu;
D. CREATE SYNONYM EDL_VU ON emp_dept_loc_vu
FOR EACH USER;
E. CREATE SYNONYM EDL_VU FOR EACH USER
ON emp_dept_loc_vu
F. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu
FOR ALL USERS; Answer: A QUESTION 193: Exhibit
The COMMISSION column shows the monthly commission earned by the employee. Which two tasks would require subqueries or joins in order to be performed in a single
stwteop.?) (Choose
A. listing the employees who earn the same amount of commission as employee 3
B. finding the total commission earned by the employees in department 10
C. finding the number of employees who earn a commission that is higher than the
ca emr
gaenycommission of the
D. listing the departments whose average commission is more that 600
E. listing the employees who do not earn commission and who are working for doerdpearrtomfethnet 2e0mipnlodyeeseceInDding
F. listing the employees whose annual commission is more than 6000
Answer: A, C
UhiEchSTstIaOteNme1n9t4a:ccomplish this?
A. CREATE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE); B. CREATE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10),
date_ordered DATE DEFAULT SYSDATE); C. CREATE OR REPLACE TABLE orders ( order_id NUMBER (10),
customer_id NUMBER (8), order_status VARCHAR2 (10),
date_ordered DATE DEFAULT SYSDATE); D. CREATE OR REPLACE TABLE orders ( order_id NUMBER (10),
customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE = SYSDATE); E. CREATE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE = SYSDATE); F. CREATE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10),
date_ordered DATE DEFAULT SYSDATE); Answer: B
QUESTION 195:
Examine the statement:
Create synonym emp for hr. employees;
What happens when you issue the statement?
A. An error is generated.
B. You will have two identical tables in the HR schema with different names.
C. You create a table called employees in the HR schema based on you EMP table. D. You create an alternative name for the employees table in the HR schema in your
oAwnnswsecrh:eDma.
QUESTION 196:
Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2 (25)
LAST_NAME VARCHAR2 (25) HIRE_DATE DATE
Which UPDATE statement is valid?
A. UPDATE employees SET first_name = 'John' SET last_name = 'Smith'
WHERE employee_id = 180; B. UPDATE employees
SET first_name = 'John', SET last_name = 'Smoth' WHERE employee_id = 180; C. UPDATE employee
SET first_name = 'John'
AND last_name = 'Smith' WHERE employee_id = 180; D. UPDATE employee
SET first_name = 'John', last_name = 'Smith' WHERE employee_id = 180;
Answer: D
QUESTION 197:
What is true about sequences?
A. The start value of the sequence is always 1. B. A sequence always increments by 1.
C. The minimum value of an ascending sequence defaults to 1.
D. The maximum value of descending sequence defaults to 1. Answer: A
QUESTION 198:
The STUDENT_GRADES table has these columns
STUDENT_ID NUMBER(12)
Which statement finds students who have a grade point average (GPA) greater than 3.0
SyfoeEPraMArth2ENe0SUc0Ta1MlE?eBRnd_EaERrN(4D,3D) ATE
A. SELECT student_id, gpa
FROM student_grades
WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' OR gpa > 3.;
B. SELECT student_id, gpa
FROM student_grades
WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa gt 3.0;
C. SELECT student_id, gpa
FROM student_grades
WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa > 3.0;
D. SELECT student_id, gpa
FROM student_grades
WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa > 3.0;
E. SELECT student_id, gpa
FROM student_grades
WHERE semester_end > '01-JAN-2001' OR semester_end < '31-DEC-2001' AND gpa >=s 3.0;
Answer: C
QUESTION 199:
Exhibit
ORDERS
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
100 12-JAB-
2000 15 10000
101 09-MAR-
2000 40 8000
102 09-MAR-
2000 35 12500
103 15-MAR-
2000 15 12500
104 25-JUN-
2000 15 6000
105 18-JUL-
2000 20 5000
10 84-AUULG- -2000
78000
CUSTOMERS
CUST_ID CUST_NAME CITY
10 Smith Los Angeles
15 Bob San Francisco
20 Martin Chicago
25 Mary New York
30 Rina Chicago
35 Smith New York
40 Linda New York Evaluate this SQL statement: SELECT cust_id, ord_total
FROM orders
WHERE ord_total > ANY (SELEC T ord_total
FROM orders
WHERE cust_id IN (SELECT cust_id
FROM customers
WHERE city LIKE
'New York'));
What is the result when the above query is executed? A.
CUST_ID ORD_TOTAL
ORD_TOTAL
CUST_ID ORD_TOTAL
|
|||||||||||||||||||||||
15 10000
411
816020000
|
20 |
5000 |
|
35 |
7000 |
|
20 |
6500 |
|
10
D. |
8000 |
CUST_ID ORD_TOTAL
15 6000
20 5000
20 6500
E. The query returns no rows.
F. The query fails because ANY is not a valid operator with a subquery. Answer: A
QUESTION 200:
What is true regarding subqueries?
A. The inner query always sorts the results of the outer query B. The outer query always sorts the results of the inner query C. The outer query must return a value to the outer query
D. The inner query returns a value to the outer query
E. The inner query must always return a value or the outer query will give an error
Answer: E
QUESTION 201:
For which action can you use the TO_DATE function? A. Convert any date literal to a date
B. Convert any numeric literal to a date C. Convert any character literal to a date D. Convert any date to a character literal
E. Format '10-JAN-99' to 'January 10 1999'
Answer: C
QUESTION 202:
EXHIBIT, Emp Table
Exhibit A
Exhibit B
Examine the data from the EMP table. Evaluate this SQL statement:
SELECT * FROM emp
WHERE emp _ id = 3);
WHERE commission = (SELECT commission
FROM emp
What is the result when the query is executed?
A. Exhibit A B. Exhibit B
C. The query returns no rows
D. The query fails because the outer query is retrieving more than one column
E. The query fails because both the inner and outer queries are retrieving data from the same table.
Answer: C
QUESTION 203:
Which three statements about subqueries are true? (Choose three.) A. A main query can have more than one subquery.
B. A subquery can have more than one main query.
C. The subquery and main query must retrieve data from the same table. D. The subquery and main query can retrieve data from different tables.
EF. MOnulytipolneeccoolulummnns or expression can be compared between the subquery and main qAunesrwy.er: A, C, E
QUESTION 204:
Evaluate this SQL statement: SELECT e.emp_name, d.dept_name FROM employees e
JOIN departments d
USING (department_id)
WHERE d.department_id NOT IN (10,40) ORSER BY dept_name;
The statement fails when executed. Which change fixes the error?
A. remove the ORDER BY clause
B. remove the table alias prefix from the WHERE clause
C. remove the table alias from the SELECT clause
D. prefix the column in the USING clause with the table alias
E. prefix the column in the ORDER BY clause with the table alias
F. replace the condition "d.department_id NOT IN (10,40)" in the WHERE clause with
"d.department_id <> 10 AND d.department_id <> 40" Answer: C
QUESTION 205:
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2 (60)
Which DELETE statement is valid?
A. DELETE FROM employees
WHERE employee_id = (SELECT employee_id FROM employees); B. DELETE * FROM employees
WHERE employee_id = (SELECT employee_id FROM new_ employees); C. DELETE FROM employees
WHERE employee_id IN (SELECT employee_id
FDWR.HDOEMERLEnEeTenwmaE_mp*eelmoF=ypRel'cOeoa_yMrierdeesyImN');p(lSoEyLeeEsCT employee_id
FROM new_employees
WHERE name = 'carrey'); Answer: C
QUESTION 206:
Evaluate the SQL statement: SELECT LPAD (salary,10,*) FROM EMP
WHERE EMP _ ID = 1001;
If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?
A. 17000.00
B. 17000***** C. ****170.00
D. **17000.00
E. an error statement
Answer: D
0
861