?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 800;
?QUESTION NO: 66
Mary has a view called EMP_DEPT_LOC_VU that was created based on the
EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She granted SELECT
privilege to Scott on this view.
Which option enables Scott to eliminate the need to qualify the view with the name
MARY .EMP_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
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
Explanation:
Correct syntax to create a local synonym is CREATE SYNONYM synonym_name. With
PUBLIC keyword you can create public synonym.
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 Scott
on this view.
Question: who has the right to create a synonym?
? QUESTION NO: 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
Explanation:
SQL*Plus commands can be used to manipulate data in tables and SQL*Plus commands
manipulate table definitions in the database.
Incorrect Answers
A: SQL*Plus commands can be abbreviated. Like command DESCRIBE can be abbreviated
as DESC, or SELECT as SELE.
B: SQL*Plus commands are not accesses from a browser.
E: SQL*Plus is not only the Oracle proprietary interface for executing SQL statements.
My answer is B E
? QUESTION NO: 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
Answer: B
Explanation:
This SQL statement provides correct syntax to generate the alias Annual Salary for the
calculated column SALARY*12.
Incorrect Answers
A: Alias can be surrounded with double quotation marks, not with single. Oracle error will be
generated in this case.
C: Alias needs to be surrounded with double quotation marks, it cannot just follow by the AS
keyword.
D: You cannot use any function as alias, so this SQL statement will fail.
My answer is B, too. But the explanation for C is not right, C will generate a column alias ANNUAL SALARY so it is not the answer.
? QUESTION NO: 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
FROM employees a
WHERE (a.department_id, a.salary) IN
(SELECT department_id, a.salary) IN
(SELECT department_id max(salary)
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 query
text for the inline view in parentheses and also give a label for the inline view so that columns in it can be referenced later. In answer A inline view is marked as B.
Incorrect Answers
B: This SQL statement will fail because it is not correct syntax for inline views. There is no a
label for this inline view also.
C: This SQL statement will fail because it is not correct syntax for inline views. There is no a
label for this inline view also.
D: This SQL statement will fail because it is not correct syntax for inline views. There is no a
label for this inline view also.
Why B C D is wrong.
?QUESTION NO: 73
When should you create a role? (Choose two)
A. To simplify the process of creating new users using the CREATE USER xxx
IDENTIFIED by yyy statement.
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
privileges to the role and after that grant this role to specific users. By granting to or revoking
privileges from the role you can simplify procedure of users privileges maintainance: you
don’t need to grant/revoke privileges to/from each user. It’s especially very helpful when you
are experiencing a high number of people using the database.
Incorrect Answers
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 at all.
E: Roles have nothing to do with user who is constantly traveling.
But: in the book : A role is a named group of related privileges that can be granted to the user. This method makes it easier to revoke and maintain privileges.
My answer is: B D |
