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 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. Q: 17 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
Q: 18 Click the Exhibit button to examine the data of the EMPLOYEES table. Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee's
manager, for all the employees who have a manager and earn more than 4000? A. SELECT employee_id \salary,
employee_id \FROM employees
WHERE salary > 4000;
B. SELECT e.employee_id \e.salary,
m.employee_id \FROM employees e JOIN employees m WHERE e.mgr_id = m.mgr_id AND e.salary > 4000;
C. SELECT e.employee_id \
e.salary,
m.employee_id \FROM employees e JOIN employees m ON (e.mgr_id = m.employee_id) AND e.salary > 4000;
D. SELECT e.employee_id \e.salary,
m.mgr_id \FROM employees e SELF JOIN employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000;
E. SELECT e.employee_id \e.salary,
m.mgr_id \FROM employees e JOIN employees m www.CertificationKing.com - 14 -
USING (e.employee_id = m.employee_id) AND e.salary > 4000;
Q: 19 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.
Q: 20 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)
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 statement returns the city address and the number of customers in the cities Los Angeles or San Francisco?
A. SELECT city_address, COUNT(*) FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco'); B. SELECT city_address, COUNT(*) FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco') GROUP BY city_address;
C. SELECT city_address, COUNT(customer_id) FROM customers
WHERE city_address IN ('Los Angeles', 'San Francisco') GROUP BY city_address, customer_id;
D. SELECT city_address, COUNT(customer_id) FROM customers
GROUP BY city_address IN ('Los Angeles', 'San Francisco');
Q: 21 Click the Exhibit button to examine the structures of the EMPLOYEES, DEPARTMENTS, and TAX tables.
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
Q: 22 The EMP table contains these columns: LAST_NAME VARCHAR2 (25) 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 DEPARTMENT_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.
Q: 23 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 from 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.
Q: 24 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 NULL values.
E. Group functions on columns include NULL values in calculations if you use the keyword INC_NULLS.
Q: 25 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 \FROM employees;
C. SELECT ename, salary*12 AS Annual Salary FROM employees;
D. SELECT ename, salary*12 AS INITCAP(\FROM employees
Q: 26 Click the Exhibit button and examine the data in the EMPLOYEES and DEPARTMENTS tables.
You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use? A. SELECT last_name, department_name
FROM employees NATURAL JOIN departments; B. SELECT last_name, department_name FROM employees JOIN departments ; C. SELECT last_name, department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id); 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 FULL JOIN 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);
Q: 27 Which SQL statement defines a FOREIGN KEY constraint on the DEPTNO 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. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno)); D. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35),
deptno NUMBER(7,2) FOREIGN KEY