Q1. 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_DEPT_LOC_VU each time the view is referenced?
A. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE PRIVATE SYNONYM EDL_VU FOR mary.EMP DEPT_LOC_VU; then he can prefix the columns with this synonym
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 synoym for the EMP_DEPT_LOC_VU by using the command CREATE LOCAL SYNONYM EDL_VU FOR mary.emp dept_LOC_uv; then he can prefix the columns with the synonym.
D. Scott can create a synomym for the EMP_DEPT_LOC_VU by using the command CRETE LOCAL 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
Q2. Which SQL statement defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table?
A. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(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 VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));
C. CRETE TABLE EM (empno NUMBER(4), ename VARCHAR2(35) deptno NUMBER (7,2) NOT NULL, CONSTRAINT em_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));
D. CREATE TABLE EMP (empno NUMBER (4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp deptno fk REFERENCES dept (deptno));
Answer: B
Q3. Evaluate the set of SQL statements:
CREATE TABLE dept (deptbi NUMBER (2) dname VARCHAR2(14), Ioc VARCHAR2(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 occupied by the DEPT table.
C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist
D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.
Answer: A
Q4. Which are DML statements? (Choose all that apply)
A. COMMIT...
B. MERGE...
C. UPDATE...
D. DELETE...
E. CREATE...
F. DROP...
Answer: ABCD
Q5. Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
EMPLOYEES
Column 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 column of the DEPARTMENT table
DEPARTMENTS
Column name Data Type Remarks
DEPARTMENT_ID NUMBER NOT NULL, Primary key
DEPARTMENT_NAME VARCHAR2(30)
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_ud=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 d
ON e.department_id=d.department_id;
C. SELECT employee_id, 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
Q6. 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
Q7. In which scenario would an 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 columns contains a wide range of values.
Answer: D
Q8. Which two are attributes of /SQL* Plus? (Choose two).
A. /SQL * Plus commands cannot be abbreviated
B. /SQL* Plus commands are accessed from a browser.
C. /SQL*Plus commands are used to manipulate data in tables
D. /SQL* Plus command manipulate table definitions in the database
E. /SQL* Plus is the Oracle proprietary interface for executing SQL statements.
Answer: CE
Q9. 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 using the ">" operator
E. A single row subquery can use the IN operator
F. A multiple row subquery can use the "=" operator
Answer: BCD
Q10. 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: BD
Q11. Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater than 5000?
A. ORDER BY SALARY > 5000
B. GROUP BY SALARY > 5000
C. HAVING SALARY > 5000
D. WHERE SALARY > 5000
Answer: D
Q12. 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-9, _,*, and #
E. must contain only A-Z, a-z, 0-9, _, $, and #
F. must begin with a letter
Answer: BCEF
Q13. 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 be generated from a sequence, without actually retrieving the value.
B. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.
C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value form the sequence
D. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.
E. If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.
F. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.
Answer: BC
Q14. The EMP table contains these columns:
LAST NAME VARCHAR2(25)
SALARY NUMBER (6,2)
DEPARTMENT_ID NUMBER(6)
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
Q15. 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 us the result of the SELECT statement?
A. The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the 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
Q16. You want to display the titles of books that meet these criteria:
1. Purchased before January 21, 2001
2. Price is less than $ 500 or greater than $ 900
You want to sort the result by their date of purchase, starting with the most recently bought book. Which statement 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 boks WHERE price IN (500, 900) AND purchase_dae< '21-jan-2001' ORDER BY purchase date ASC;
C. SELECT book_title FROM books WHERE price < 500 OR>900 AND purchase_date DESC;
D. SELECT BOOK_title FROM books WHERE price < 500 OR>900 AND purchase_date<'21-JAN-2001' ORDER BY purchase date DESC;
E. SELECT book_title FROM books WHERE (price< 500 OR price> 900 AND purchase date> '21 - JAN-2001') ORDER BY purchase date ASC;
Answer: D
Q17. Click the Exhibit button to examine the structure of the EMPOLOYEES, DEPARTMENTS and TAX tables.
EMPLOYEES
EMPLOYEE_ID NUMBER NOT NULL primary key
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20)
SALARY NUMBER
MGR_ID NUMBER Reference EMPLOYEE_ID Column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID TO column of the DEPARTMENT table
DEPARTMENTS
DEPARTMENT_ID NUMBER NOT NULL primary key
DEPARTMENT_NAME VARCHAR2(30)
MGR_ID NUMBER Reference MGR_ID column of the EMPLOYEES 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 the department name of employees who are not working with Smith
D. to find the number of employees working for the Administrative department and earning less than 4000
E. to display name, salary, manager ID, and department name of all the employees, even if the employees do not have a department ID assigned
Answer: A
Q18. Which operator can be used with a multiple row subquery?
A. **
B. LIKE
C. BETWEEN
D. NOT IN
E. Is
F. <>
Answer: D
Q19. You need to perform certain data manipulation operations through a view called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the SELECT statement on which the view was created) How do you obtain the definition of the view?
A. Use the DESCRIBE command on 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 view
E. Query the USER_SOURCE data dictionary view to search for the EMP_DEPT_VU view
F. Query the USER_OBJECTS data dictionary view to search for the EMP_DEPT_VU view
Answer: D
Q20. 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 student(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)
F. ALTER TABLE student grades ADD NAME student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id)
Answer: C
Get More Questions and Answers with Explanation at Oracle Forums.