E. You cannot increase the size of a column if the table has rows. Q: 55 In which case would you use a FULL OUTER JOIN? A. Both tables have NULL values.
B. You want all unmatched data from one table. C. You want all matched data from both tables. D. You want all unmatched data from both tables. E. One of the tables has more data than the other.
F. You want all matched and unmatched data from only one table. Q: 56 Which are DML statements? (Choose all that apply.) A. COMMIT B. MERGE C. UPDATE D. DELETE E. CREATE F. DROP...
Q: 57 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 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) > 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;
Q: 58 Which four statements correctly describe functions that are available in SQL? (Choose four.)
A. INSTR returns the numeric position of a named character.
B. NVL2 returns the first non-null expression in the expression list.
C. TRUNCATE rounds the column, expression, or value to n decimal places. D. DECODE translates an expression after comparing it to each search value. E. TRIM trims the heading or trailing characters (or both) from a character string. F. NVL compares two expressions and returns null if they are equal, or the first expression if they are not equal.
G. NULLIF compares two expressions and returns null if they are equal, or the first expression if they are not equal.
Q: 59 Which iSQL*Plus feature can be used to replace values in the WHERE clause?
A. substitution variables B. replacement variables C. prompt variables
www.CertificationKing.com - 36 -
D. instead-of variables
E. This feature cannot be implemented through iSQL*Plus. Q: 60 Which is an iSQL*Plus command? A. INSERT B. UPDATE C. SELECT D. DESCRIBE E. DELETE F. RENAME
Q: 61 The EMP table contains these columns: EMPLOYEE_ID NUMBER(4) EMPNAME VARCHAR2 (25) SALARY NUMBER(9,2) HIRE_DATE DATE
You query the database with this SQL statement: SELECT empname,hire_date HIREDATE, salary FROM EMP
ORDER BY hire_date;
How will the results be sorted? A. randomly
B. ascending by date C. descending by date
D. ascending alphabetically E. descending alphabetically
Answer: B
Q: 62 Click the Exhibit button and examine the data from the ORDERS and CUSTOMERS tables.
www.CertificationKing.com - 37 -
Evaluate the SQL statement: SELECT * FROM orders
WHERE cust_id = (SELECT cust_id FROM customers
WHERE cust_name = 'Smith');
What is the result when the query is executed? Q: 63 What is true about updates through a view? A. You cannot update a view with group functions.
B. When you update a view group functions are automatically computed.
C. When you update a view only the constraints on the underlying table will be in effect.
D. When you update a view the constraints on the views always override the constraints on the underlying tables.
Q: 64 What does the FORCE option for creating a view do? A. creates a view with constraints
B. creates a view even if the underlying parent table has constraints C. creates a view in another schema even if you don't have privileges D. creates a view regardless of whether or not the base tables exist Q: 65 The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) LAST_NAME VARCHAR2 (25) JOB_ID VARCHAR2(10)
You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?
A. SELECT employee_id, last_name, job_id FROM employees
WHERE job_id LIKE '%SA\\_%' ESCAPE '\\'; B. SELECT employee_id, last_name, job_id FROM employees
WHERE job_id LIKE '%SA_';
C. SELECT employee_id, last_name, job_id FROM employees
WHERE job_id LIKE '%SA_' ESCAPE \D. SELECT employee_id, last_name, job_id FROM employees
WHERE job_id = '%SA_';
Q: 66 Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER NOT NULL EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) DEFAULT 'SA_REP' SAL NUMBER
COMM_PCT NUMBER MGR_ID NUMBER
DEPARTMENT_ID NUMBER
You need to update the records of employees 103 and 115. The UPDATE statement you specify should
update the rows with the values specified below:
JOB_ID: Default value specified for this column definition. SAL: Maximum salary earned for the job ID SA_REP.
COMM_PCT: Default value specified for this commission percentage column, if any.
If no default value is specified for the column, the value should be NULL.
DEPARTMENT_ID: Supplied by the user during run time through substitution variable.
Which UPDATE statement meets the requirements? A. UPDATE employees SET job_id = DEFAULT
AND Sal = (SELECT MAX(sal) FROM employees
WHERE job_id = 'SA_REP') AND comm_pct = DEFAULT AND department_id = &did
WHERE employee_id IN (103,115); B. UPDATE employees SET job_id = DEFAULT AND Sal = MAX(sal)
AND comm_pct = DEFAULT OR NULL AND department_id = &did
WHERE employee_id IN (103,115) AND job_id = 'SA_REP';
C. UPDATE employeesC.UPDATE employees SET job_id = DEFAULT, Sal = (SELECT MAX(sal) FROM employees
WHERE job_id = 'SA_REP'), comm_pct = DEFAULT, department_id = &did
WHERE employee_id IN (103,115);
D. UPDATE employeesD.UPDATE employees
SET job_id = DEFAULT, Sal = MAX(sal),
comm_pct = DEFAULT, department_id = &did
WHERE employee_id IN (103,115) AND job_id = 'SA_REP'; E. UPDATE employees SET job_id = DEFAULT, Sal = (SELECT MAX(sal) FROM employees
WHERE job_id = 'SA_REP'),
comm_pct = DEFAULT OR NULL,
department_id = &did
WHERE employee_id IN (103,115);
Q: 67 Click the Exhibit button to examine the structures of the EMPLOYEES and
TAX tables.
You need to find the percentage tax applicable for each employee. Which SQL statement would you use?
A. SELECT employee_id, salary, tax_percent FROM employees e JOIN tax t
ON e.salary BETWEEN t.min_salary AND t.max_salary; B. SELECT employee_id, salary, tax_percent FROM employees e JOIN tax t
WHERE e.salary > t.min_salary AND < t.max_salary; C. SELECT employee_id, salary, tax_percent FROM employees e JOIN tax t ON (MIN(e.salary) = t.min_salary AND MAX(e.salary) = t.max_salary);
D. You cannot find the information because there is no common column between the two tables.
Q: 68 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