Home » Other » Training & Certification » doubts in IZO-007 questions
| doubts in IZO-007 questions [message #120793] |
Mon, 23 May 2005 15:37  |
Rishi Mahajan
Messages: 29
Registered: April 2005
Location: India
|
Junior Member |
|
|
Hi all
I have doubts in answers of the following questions. Please help me.....
I am going for exam on 28th....
Thanks in advance...
QUESTION NO: 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 be created as read only object. However, it is possible to change data in the
underlying table(s) with some restrictions.A view also can be created as a join on two or more
tables. This type of view is called complex view. Complex views provide complicated data
models where many base tables are drawn together into one virtual table.
Incorrect Answers
C: Query operations containing ORDER BY clause are alsopermitted, so long as the ORDER
BY clause appearsoutside the parentheses. Thefollowing is an example of what I mean:
CREATE VIEWmy_viewAS (SELECT*FROM emp)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 statement.
Doubt:- The student guide says order by clause can't be used in select statement of view though I have used, it works efficiently.
So, what should be the best options for exam?????????
Examine the structure of the EMPLOYEES, DEPARTMENTS, and LOCATIONS
tables.
EMPLOYEES
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 column of the
DEPARTMENTS table
DEPARTMENTS
DEPARTMENT_ID NUMBER NOT NULL, Primary Key
DEPARTMENT_NAME VARCHAR2
(30)
MGR_ID NUMBER References NGR_ID column of the
EMPLOYEES table
LOCATION_ID NUMBER Foreign key to LOCATION_ID column of the
LOCATIONS table
LOCATIONS
LOCATION_ID NUMBER NOT NULL, Primary Key
CITY VARCHAR2 |30)
Which two SQL statements produce the name, department name, and the city of all the
employees who earn more then 10000? (Choose 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
Doubt:- The dump says B and D are correct. But i think A and D are the required options.
B is syntactically wrong...
What u say??????????????
QUESTION NO: 33
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 VARCHAR2(30)
SALARY NUMBER(8,2)
Which statement shows the department ID, minimum salary, and maximum salary paid
in that department, only of the minimum salary is 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) < 15000;
E. SELECT dept_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY dept_id, salary
HAVING MIN(salary) < 5000 AND MAX(salary) > 15000;
Answer: E
Doubt:- Wrong question, none of the option is right.E can't be right because it has included
salary in group by clause which is not required.
D can be right if max(sal)>15000
What do u say???????????
QUESTION NO: 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
course INT SQL and who completed 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 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)
WHERE ROWNUM <= 10
AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'
AND course_id = 'INT_SQL';
Answer: D
Doubt:- D can't be right. because inline view contains only student_id and marks, so how can
it access finish_date and course id in WHERE clause.
I think the query should be like this...................
select rownum as rank,student_id, marks from
(select student_id, marks from students where course_id='INT_SQL' and
finish_date between '01-JAN-99' AND '31-DEC-99'order by marks desc)
where rownum<=3
What do u say???????????
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
ON mary(EMP_DEPT_LOC_VU);
then he can prefix the columns with this synonym.
E. Scott cannot create a synonym because synonyms can be created only for tables.
F. Scott cannot create any synonym for Mary’s view. Mary should create a private
synonym for the view and grant SELECT privilege on that synonym to Scott.
Answer: B
Doubt:The answer is fine but what abt he has to prefix synonym to column.. I don't
think so. Is it so... Should i go for this option in exam?
QUESTION NO: 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, E
Doubt: Is this the correct answer?
QUESTION NO: 86
Which three statements correctly describe the functions and use of constraints? (Choose
three.)
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
Doubt:- D and E are correct. what abt C???? Not sure abt c.We use constraint in view only
to prevent updation of rows not included in view using WITH CHECK OPTION. SO is C also right option.
QUESTION NO: 97
You need to change the definition of an existing table. The COMMERCIALS table
needs its DESCRIPTION column changed to hold varying length characters up to 2000
bytes. The column can currently hold 1000 bytes per value. The table contains 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
Doubt:-Student guide says its not possible to change char to varchar2
unless the column is empty. or it can be done without change in size of column. but i have
done it with increase in column from char to varchar2.
What should i choose in exam????????
QUESTION NO: 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
without matching rows, to perform an outerjoin.
F. You cannot link a condition that is involved in an outerjoin to another condition by
using the OR operator.
Answer: D, E, F
Doubt:- Student guide says A is also true.
What should be the best option.
Which two statements complete a transaction? (Choose two)
A. DELETE employees;
B. DESCRIBE employees;
C. ROLLBACK TO SAVEPOINT C;
D. GRANT SELECT ON employees TO SCOTT;
E. ALTER TABLE employees
SET UNUSED COLUMN sal;
F. Select MAX(sal)
FROM employees
WHERE department_id = 20;
Answer: C, E
Doubt:-According to studnet guide D is also true.DCL statements such as grant and revoke also completes
the transaction. Although I have tested, I is fine...
So what should be the options?
Rishi
|
|
|
|
| Re: doubts in IZO-007 questions [message #120922 is a reply to message #120793] |
Tue, 24 May 2005 13:17  |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
Never trust any stolen materials like "testking" or any
others like that. To say nothing of it's a cheating they
contain a lot of mistakes. The best way to pass OCP exams is
to have reliable on-hand experience in Oracle. If you are
sure in your knowledge - count on it and don't hesitate to
check answers in the preparation materials.
Rgds.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 10:33:28 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
