Q1. You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME
The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_Id of NUMBER data type from the EMPLOYEES table. How can you accomplish this task?
A. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);
B. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);
C. ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name Department_name, manager_id
FROM employees e, departments d WHERE department_id = d.department_id;
D. MODIFY VIEW emp_depat_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 emplouee_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 CRATE VIEW command with a new column list to modify a view.
Answer: E
Q2. Which three SELECT statements display 2000 in the format “$2,000.00”? (Choose Three).
A. SELECT TO_CHAR (2000, ‘$#,###.##’) FROM dual;
B. SELECT TO_CHAR (2000, ‘$0,000.00’) FROM dual
C. SELECT TO_CHAR (2000, ‘$9,999.00’) FROM dual;
D. SELECT TO_CHAR (2000, ‘$9,999.99’) FROM dual;
E. SELECT TO_CHAR (2000, ‘$2,000.00’) FROM dual;
F. SELECT TO_CHAR (2000, ’$N, NNN.NN’) FROM dual
Answer: BCD
Q3. 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: ABDE
Q4. 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
Q5. 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 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 WHERE employee_id FROM new_employees))
WHERE employee_id = 180,
Answer: A
Q6. You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMER table has these columns:
CUST_ID NUMBER(4) NOT NULL
CUST_NAME VARCHAR2(100) NOT NULL
CUST_ADDRESS VARCHAR2(150)
CUST_PHONE VARCHAR(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
Q7. Which two statements complete a transaction? (Choose two)
A. DELETE employees;
B. DESCRIBE employees
C. ROLLBACK TO SAVEPOINT C;
D. GRANT TABLE employees
E. ALTER TABLE employees SET UNUSED COLUMN sal;
F. SELECT MAX (sal) FROM employees
WHERE department_id = 20;
Answer: CE
Q8. 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 VARCHAR(30)
SALARY NUMBER(8,2)
Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only if the minimum salary is less than 5000 and 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)
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: D
Q9. The DBA issues this SQL command:
CREATE USER scott INDENTIFIED 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
Q10. 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 the table by using this SQL statement:
ALTER TABLE EMPLOYEES
MODIFY (SALARY DEFAULT 5000);
Which 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 to add DEFAULT values for columns with a NUMBER data type.
D. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.
Answer: B
Q11. Which substitution variable would you use if you want to reuse the variable value without prompting the user each time?
A. &
B. ACCEPT
C. PROMPT
D. &&
Answer: D
Q12. Examine the structure of the EMPLOYEES table:
Column name Data type Remarks
EMPOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) NOT NULL
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_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 users to insert rows through the view.
Which SQL statement, when used to create the EMP_VU view, allows the users 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) TOTAL SAL 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
Q13. 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
Q14. 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) for students enrolled during semesters that end in the year 2000. Which statement accomplishes 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 MID (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 > ’01-JAN-2000’ and semester end < ’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
Q15. Which constraint can be defined only at the column level?
A. UNIQUE
B. NOT NULL
C. CHECK
D. PRIMARY KEY
E. FOREIGN KEY
Answer: B
Q16. 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 of all employees
D. You want to rank the top three sales representatives who have sold the maximum number of products
Answer: D
Q17. Examine the structure of the EMPLOYEES and NEW EMPOYEES tables:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW EMPLYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which MERGE statement is valid?
A. MERGE INTO new_employees e USING employees e ON (e.employee_id = e.employee_id)
WHEN MATCHED THEN UPDATE SET e.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_employee 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 c USING employees e ON (c.employee_id = e.employee_id)
WHEN EXISTS THEN UPDATE SET e.name = e.fist ||’,’|| e.last_name
WHEN NOT MATCHES THEN INSERT VALUES (e.employee_id, e.first _name||’,‘||e.last_name);
D. MERGE new_employees c FROM employees c ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN UPDATE SET e.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
Q18. 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: AEF
Q19. Which statement creates a new user?
A. CREATE USER susan
B. CREATE OR REPLACE USER susan
C. CREATE NEW USER susan DEFAULT,
D. CREATE USER susan INDENTIFIED BY blue
E. CREATE NEW USER susan IDENTIFIED BY blue
F. CREATE OR REPLACE USER susan IDENTIFIED BY blue;
Answer: D
Q20. The STUDENT_GRADES table has these columns
STUDENT_ID NUMBER (12)
SEMESTER_END DATE
GPA NUMBER (4,3)
The registrar has requested a report listing the students’ grade point averages (GPA), stored from highest grade point average to lowest within each semester, starting from the earliest date. Which statement accomplishes 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 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
ORDER BY gpa DESC, semester_end ASC;
Answer: C
Get More Questions and Answers with Explanation at Oracle Forums.