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