| 1. | Which Oracle product contains its own command language and can be used to create script files? |
| a. | | PL/SQL |
| b. | | SQL |
| c. | | iSQL*PLUS |
| d. | | JAVA*SQL
|
|
|
|
|
|
|
| 2. | Examine the following SQL statement:
SELECT CONCAT(LOWER(SUBSTR(description, 1, 3)), product_id) "Product Description"
FROM Product;
In which order are the functions evaluated? |
| a. | | SUBSTR, LOWER, CONCAT |
| b. | | CONCAT, LOWER, SUBSTR |
| c. | | LOWER, SUBSTR, CONCAT |
| d. | | All three will be evaluated simultaneously
|
|
|
|
|
|
|
| 3. | Which of the following statements are correct with regard to NULL values? |
| a. | | Only =and !=operator can be used to search for NULL values in a column |
| b. | | In an ascending order sort, NULL values appear at the bottom of the result set |
| c. | | In descending order sort, NULL values appear at the bottom of the result set |
| d. | | None of the above
|
|
|
|
|
|
|
| 4. | Which of the following functions can return a non-NULL value if NULL argument is passed through it? |
| a. | | NULLIF |
| b. | | LENGTH |
| c. | | CONCAT |
| d. | | INSTR
|
|
|
|
|
|
|
| 5. | The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(4) ENAME VARCHAR2 (25) JOB_ID VARCHAR2(10)
Which of the following SQL statements will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column for the employees whose ENAME ends with the letter "n"? |
| a. | | SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n'; |
| b. | | SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n'; |
| c. | | SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = 'n'; |
| d. | | SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = 'n';
|
|
|
|
|
|
|
| 6. | Examine the data in the EMPLOYEES table given below:
| LAST_NAME | DEPARTMENT_ID | SALARY |
| ALLEN | 10 | 3000 |
| MILLER | 20 | 1500 |
| King | 20 | 2200 |
| Davis | 30 | 5000 |
Which of the following Subqueries work? |
| 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 > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id); |
| e. | | SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));
|
|
|
|
|
|
|
| 7. | Which of the following constitute the attributes of /SQL*PLUS? |
| 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 commands manipulate table definitions in the database |
| e. | | /SQL*PLUS is the Oracle proprietary interface for executing SQL statements
|
|
|
|
|
|
|
| 8. | Evaluate the following SELECT statement:
SELECT Item_num, &order_date
FROM &&ordertbl
WHERE order_date = '&order_date';
Which statement regarding the execution of this statement is true? |
| a. | | The user will be prompted for the table name each time the statement is executed in a session |
| b. | | An error will occur when executing this statement because substitution variables are NOT allowed in a WHERE clause |
| c. | | The user will be prompted for the table name only the first time the statement is executed in a session |
| d. | | The user will be prompted for all values in the select list each time the statement is executed in a session
|
|
|
|
|
|
|
| 9. | Which SELECT statement will return a numeric value? |
| a. | | SELECT (14 + hire_date) + 30.5 * 9
FROM employee; |
| b. | | SELECT (SYSDATE - hire_date) + 30.5 * 9
FROM employee; |
| c. | | SELECT SYSDATE - hire_date + TO_DATE('29-MAY-02')
FROM employee; |
| d. | | SELECT (SYSDATE+ hire_date) + TO_DATE('29-MAY-02')
FROM employee;
|
|
|
|
|
|
|
| 10. | The EVENT table contains the following columns:
EVENT_ID NUMBER
EVENT_NAME VARCHAR2(30)
EVENT_DESC VARCHAR2(100)
EVENT_TYPE NUMBER
LOCATION_ID NUMBER
A report of the number of different event types at each location has to be prepared. Which SELECT statement will produce the desired result? |
| a. | | SELECT COUNT(*), DISTINCT(location_id)
FROM event; |
| b. | | SELECT UNIQUE(location_id), COUNT(event_type)
FROM event
GROUP BY location_id; |
| c. | | SELECT DISTINCT (event_type)
FROM event
GROUP BY location_id; |
| d. | | SELECT location_id, COUNT(DISTINCT event_type)
FROM event
GROUP BY location_id;
|
|
|
|
|
|
|
| 11. | Consider the following SQL statement:
SELECT * FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Smith')
What will be the result when the query is executed? |
| a. | | ORD_ID ORD_DATE CUST_ID ORD_TOTAL 102 09-MAR-2000 35 12500 106 18-JUL-2000 35 7000 108 04-AUG-2000 10 8000 |
| b. | | ORD_ID ORD_DATE CUST_ID ORD_TOTAL 102 09-MAR-2000 35 12500 106 18-JUL-2000 35 7000 |
| c. | | ORD_ID ORD_DATE CUST_ID ORD_TOTAL 108 04-AUG-2000 10 8000 |
| d. | | The query fails because the subquery returns more than one row
|
|
|
|
|
|
|
| 12. | Each employees salary must be increased by 10% and their commission by 20% of their newly increased salary. Display the new salary and commission plus each employee's total compensation.
Evaluate the following SQL statements:
1. SELECT sal * 1.1 "Salary", sal * 1.1 * .2 "Commission", sal * 1.1 + sal * 1.1 * .2 "Compensation"
FROM emp;
2. SELECT sal * 1.1 "Salary", sal * 1.1 * .2 "Commission", (sal * 1.1) + (sal * .2) "Compensation"
FROM emp;
3. SELECT sal * 1.1 "Salary", sal * .2 "Commission", sal * 1.1 + sal * 1.1 * .2 "Compensation"
FROM emp;
What are the results of these statements? |
| a. | | Only statement one returns the desired result |
| b. | | Only statement two returns the desired result |
| c. | | Only statement three returns the desired result |
| d. | | Two statements return the desired result
|
|
|
|
|
|
|
| 13. | Which Oracle product contains its own command language and can be used to create script files? |
| a. | | A |
| b. | | B |
| c. | | C |
| d. | | D
|
|
|
|
|
|
|
