Online Tutorials & Training Materials | STechies.com
Register Login

Master 20 Toughest Oracle FAQs

|| 0

Master 20 Toughest Oracle FAQs
Stechies

Q1. What are two reasons to create synonyms? (Choose two.)

A. You have too many tables.

B. Your tables are too long.

C. Your tables have difficult names.

D. You want to work on your own tables.

E. You want to use another schema's tables.

F. You have too many columns in your tables.

Q2. Which four are attributes of single row functions? (Choose four.)

A. cannot be nested

B. manipulate data items

C. act on each row returned

D. return one result per row

E. accept only one argument and return only one value

F. accept arguments which can be a column or an expression

Q3. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4)
ENAME VARCHAR2 (25)
JOB_ID VARCHAR2(10)

Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"?

A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEESWHERE SUBSTR(ENAME, -1, 1) = 'n';

B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEESWHERE SUBSTR(ENAME, -1, 1) = 'n';

C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEESWHERE INSTR(ENAME, 1, 1) = 'n';

D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEESWHERE INSTR(ENAME, -1, 1) = 'n';

Q4. 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

Q5. Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?

A. ALTER TABLE customersADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

B. ALTER TABLE customersMODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

C. ALTER TABLE customersMODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;

D. ALTER TABLE customersMODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;

E. ALTER TABLE customersMODIFY name CONSTRAINT cust_name_nn NOT NULL;

F. ALTER TABLE customersADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;

Q6. Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER  Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements insert a row into the table? (Choose three.)

A. INSERT INTO employees VALUES (NULL.'John','Smith');

B. INSERT INTO employees (first_name, last_name) VALUES ('John', 'Smith');

C. INSERT INTO employees VALUES (1000, 'John', 'Smith');

E. INSERT INTO employees (employee_id) VALUES (1000);

F. INSERT INTO emloyees (employee_id_first_name,last_name) VALUES (1000,'John', 'Smith');

Answer: CE F

Q7. Click the Exhibit button and examine the data in the EMPLOYEES table
  LAST_NAME  DEPARTMENT_ID SALARY
 Getz   10   3000
 Davis   20   1500
 King   20   2200
 Davis   30   5000
 ...
Which three subqueries work? (Choose three)

A. SELECT * FROM employees where salary > (SELECT MIN(salary)FROM employees.GROUP BY department_id);

B. SELECT * FROM employees WHERE salary = (SELECT AVG (salary) FROM employees GROUP BY department_id);

C. SELECT distinct department_id FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department _id);

D. SELECT department_id FROM employees WHERE salary > ANY (SELECT MAX (salary) FROM employees GROUP BY department_id);

E. SELECT last_name FROM employees WHERE salary > ANY (SELECT MAX (salary) FROM employees GROUP BY department_id);

F. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary). FROM employees GROUP BY AVG (SALARY);

Answer: CDE

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  VARCHAR2(30)
SALARY  NUMBER(8,2)

Which statement shows the maximum salary paid in each job category of each department?

A. SELECT dept_id, job_cat, MAX (salary) FROM employees WHERE salary > MAX (salary);

B. SELECT dept_id, job_cat, MAX (salary) FROM employees GROUP BY dept_id, job_cat

C. SELECT dept_id, job_cat, MAX(salary) FROM employees;

D. SELECT dept_id, job_cat, MAX (salary) FROM employees GROUP BY dept_id;

E.  SELECT dept_id, job_cat, MAX (salary) FROM employees GROUP BY dept_id, job_cat, salary;

Answer: B

Q9. Which SELECT statement will get the result 'elloworld' fromt the string 'HelloWorld'?

A. SELECT SUBSTR ('HelloWorld',1) FROM dual;

B. SELECT INITCAP(TRIM('HellowWorld', 1,1) FROM dual

C. SELECT LOWER (SUBSTR ('HellowWorld', 2,1) FROM dual

D. SELECT LOWER (SUBSTR('HellowWorld', 2,1) FROM dual

E. SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual

Answer:  E

Q10.  Management has asked you to calculate the value 12* salary* commission_pct for all the employees in the EMP table. The EMP table contains these columns:

LAST NAME VARCHAR2(35) NOT NULL
SALARY  NUMBER(9,2)   NOT NULL
COMMISSION_PCT NUMBER(4,2)

Which statement ensures that a value is displayed in the calculated column for all employees?

A. SELECT last_name, 12 * salary* commission_pct FROM emp;

B. SELECT last_name, 12 * salary* (commission_pct,0) FROM emp;

C. SELECT last_name, 12 * salary* (nvl(commission_pct,0) FROM emp;

D. SELECT last_name, 12 * salary* (decode(commission_pct,0)) FROM emp;

Answer: C

Q11. Examine the description of the STUDENTS table:
STD_ID   NUMBER(4)
COURSE_ID  VARCHAR2(10)
START_DATE  DATE
END_DATE  DATE

Which two aggregate functions are valid on the START_DATE column? (Choose Two)

A. SUM(start_date)

B. AVG (start_date)

C. COUNT (start_date)

D. AVG(start_date, end_date)

E. MIN (start_date)

F. MAXIMUM (start_date)

Answer: CE

Q12.  From SQL*Plus, you issue this SELECT statement:
SELECT * FROM orders; You use this statement to retrieve data from a database table for _______________. (Choose all that apply)

A. updating

B. viewing

C. deleting

D. inserting

E. truncating

Answer: BD

Q13.  Click the Exhibit button examine the data from the EMP table.
EMP_ID  DEPT_ID  COMMISSION
1    10   500
2    20   1000 
3    10   
4    10   600
5    30   800
6    30   200 
7    10   
8    20   300

The COMMISSION column shows the monthly commission earned by the employee.Which three tasks would require subqueries or joins in order to be performed in a single step? (Choose three)

A. deleting the records of employees who do notearn commission

B. increasing the commission of employee 3 by the average commission earned in department 20

C. finding the number ofemployees who do NOT  earn commission and are working fordepartment 20

D. inserting into the table a new employee 10 who works for deartment 20 and earns a commission that is equal to the commission earned by employee 3

E. creating a table called COMMISSION that has the same structure and data as the columns EMP_ID and COMMISSION  of the EMP table

F. decreasing the commission by 150 for the employees who are working in department 30 and earning a commission of more than 800.

Answer:  BDE

Q14. Which four statements correctly describe functions that are available in SQL? (Choose four)

A. INSTR returns the numeric position of a named character

B. NVL 2 returns the first non-null expression in theexpression 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 leading 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.

Answer : ADEG

Q15. The EMPLOYEES table has these columns:
LAST_NAME VARCHAR2(35)
SALARY  NUMBER(8,2)
COMMISSION_PCT NUMBER (5,2)

You want todisplay the name and annual salary multiplied by the commission_pct for all employees.  For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?

A. SELECT last_name, (salary*12)* commission_Pct FROM EMPLOYEES;

B. SELECT last_name, (salary*12)* IFNULL(commission_pct,0) FROM EMPLOYEES;

C. SELECT last_name, (salary*12)* Which three tasks (commission_pct,0) FROM EMPLOYEES;

D. SELECT last_name, (salary*12)* NVL(commission_pct,0) FROM EMPLOYEES;

Answer: D

Q16. Which two statements are true regarding the ORDER BY clause? (Choose two)

A. The sort is in ascending order by default

B. The sort is in descending order by default 

C. The ORDER BY clause must precede the WHERE clause.

D. The ORDER BY clause is executed on the client side

E. The ORDER BY clause comes last in the SELECT statement

F. The ORDER BY clause is executed first in the query execution.

Answer: AE

Q17. Click the Exhibit button and examine the data from the ORDERS and CUSTOMERS tables.
ORDERS
ORD_ID  ORD_DATE  CUST_ID  ORD_TOTAL
100    12.JAN.2000  15   10000
101    09.MAR.2000  40   8000 
102    09.MAR.2000  35   12500
103    15.MAR.2000  15   12000
104    25.JUN.2000  15   6000
105    18.JUL.2000  20   5000
106    18.JUL.2000  35   7000
107    21.JUL.2000  20   6500
108    04.AUG.2000  10   8000

CUSTOMERS
CUST_ID CUST_NAME  CITY
10   Smith   Los Angeles
15   Bob   San Francisco 
20   Martin   Chicago
25   Mary   New York
30   Rina   Chicago
35   Smith   New York
40   Linda   New York

Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the same day that Martin paced his orders?

A. SELECT ord_id, cust_id, ord_total FROM orders, customers WHERE  cust_name='Martin' AND ord_date IN ('18-JUL-2000'; 21-JUL-2000');

B. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date  IN (SELECT ord_date FROM orders WHERE cust_id=(SELECT cust_id FROM customers WHERE cust_name= 'Martin'));

C. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders, customers WHERE cst_name='Martin');

D. SELECT ord_id, cust_id, ord_total FROM orders WHERE cust_id IN (SELECT cust_id FROM customers WHERE cust name = 'Martin')

Answer: B

Q18.  Evaluate the SQL statement:

1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal
2 FROM employees a,
3 (SELECT dept_id, MAX(sal) maxsal
4 FROM employees
5 GROUP BY dept_id)b
6 WHERE a.dept_id = b.dept_id
7 AND a.sal

What is the result of the statement?

A. The statement produces an error at line1.

B. The statement produces an error at line3.

C. The statement produces an error at line6.

D. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary than the maximm salary aid in the company.

E. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.

Answer:  E

Q19. Which two tasks can you perform using only the TO_CHAR  function? (Choose two).

A. convert 10 to 'TEN'

B. convert '10' to 10

C. convert '10' to '10'

D. convert'TEN' to 10

E. Convert a date to a character expression

F. convert a character expression to a date

Answer: BE

Q20.  Click the Exhibit button and examine the data in the EMPLOYEES  and DEPARTMENTS tables.

EMPLOYEES
EMP_ID EMP_NAME  DEPT_ID MGR_ID JOB_ID  SALARY 
101   Smith   20  120  SA_REP 4000 
102   Martin   10  105  CLERK  2500
103   Chris   20  120  IT ADMIN 4200
104   John   30  108  HR_CLERK 2500
105   Diana   30  108  IT_ADMIN 5000
106   Smith   40  110  AD_ASST 3000
108   Jennifer   30  110  HR_DIR 6500
110   Bob   40    EX_DIR  8000
120   Ravi   20  110  SI_DIR  6500

DEPARTMENTS
DEPARTMENT_ID  DEPARTMENT NAME
10     Admin
20     Education
30     IT
40     Human Resources

Also examine the SQL statements that create the EMPLOYEES and  DEPARTMENTS tables:
CREATE TABLE departments
(department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(30));
CREATE TABLE employees
(EMPLOEE_ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(20),
DEPT_ID NUMBER   REFERENCES
departments (department_id)
MGR_ID NUMBER REFERENCES
employees(employee id),
JOB_ID VARCHAR2(15).
SALARY NUMBER);

On the EMPLOYEES  table, EMPLOYEE_ID is the primary key MGR_ID is the ID of mangers and refers to the EMPLOYEE_ID DEPT_ID is foreign key to DEPARTMENT_ID column of the DEPARTMENTS table On the DEPARTMENTS table, DEPARTMENT_ID  is the primary key. Examine this DELETE statement: DELETE FROM departments WHERE department id=40; What happens when you execute the DELETE statement?

A. Only the row with department ID 40 is deleted in the DEPARTMENTS table.

B. The statement fails because there are child records in the EMPLOYEES table with department ID 40.

C. The row with department ID 40 is deleted in the DEPARTMENTS table.  Also the rows with employee IDs 110 and 106 are deleted from the EMPLOYEES table.

D. The row with department ID 40 is deleted in the DEPARTMENTS table.  Also the rows with employee IDs 106 and 110 and the employees working under employee 110 are deleted from the EMPLOYEES table.

E. The row with department ID 40 is deleted in the DEPARTMENTS table.  Also all the rows in the EMPLOYEES table are deleted.

F. The statement fails because there are no columns specified in the DELETE clause of the DELETE statement.

Answer: B