Q1. 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 perform in a single step? (Choose three)
A. Deleting the records of employees who do not earn commission.
B. Increasing the commission of employee 3 by the average commission earned in department 20.
C. Finding the number of employees who do NOT earn commission and are working for department 20.
D. Inserting into the table a new employee 10 who works for department 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 COMMISSIONS 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 then 800.
Answer: B, D, F
Q2 . View the image below and examine the data from the EMP table.
Evaluate this SQL statement: SELECT * FROM emp WHERE commission = (SELECT commission FROM emp WHERE emp_id = 3); What is the result when the query is executed?
A. ===
B. ===
C. The query returns no rows.
D. The query fails because the outer query is retrieving more than one column.
E. The query fails because both the inner and outer queries are retrieving data from the same table.
Answer: C
Q3. Examine the data in the EMPLOYEES and DEPARTMENTS tables.
EMPLOYEES LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
Kochhar 5000
DEPARTMENTS DEPARTMENT_ID DEPARTMENT_NAME 10 Sales 20 Marketing 30 Accounts 40 Administration 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 , 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(+) , 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);
Answer: F
Q4. Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE
DATE Which UPDATE statement is valid?
A. UPDATE employees SET first_name = 'John' SET last_name='Smith' WHERE employee_id = 180;
B. UPDATE employees SET first_name = 'John', SET last_name ='Smith' WHERE employee_id = 180;
C. UPDATE employees SET first_name = 'John' AND last_name ='Smith' WHERE employee_id = 180;
D. UPDATE employees SET first_name = 'John', last_name ='Smith' WHERE employee_id = 180;
Answer: D
Q5.The user Sue issues this SQL statement: GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION; The user Alice issues this SQL statement: GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION; The user Reena issues this SQL statement: GRANT SELECT ON sue.EMP TO timber; The user Sue issues this SQL statement: REVOKE select on sue.EMP FROM alice; For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?
A. Alice only
B. Alice and Reena
C. Alice, Reena, and Timber
D. Sue, Alice, Reena, and Timber
Answer: C
Q6.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_';
Answer: A
Q7. Examine the structure of the EMPLOYEES table: Column name Data type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key LAST_NAME VARCHAR2(30) FIRST_NAME VARCHAR2(30) JOB_ID NUMBER SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER You need to create an index called NAME_IDX on the first name and last name fields of the EMPLOYEES table. Which SQL statement would you use to perform this task?
A. CREATE INDEX NAME_IDX (first_name, last_name);
B. CREATE INDEX NAME_IDX (first_name AND last_name);
C. CREATE INDEX NAME_IDX ON (first_name, last_name);
D. CREATE INDEX NAME_IDX ON employees (first_name AND last_name);
E. CREATE INDEX NAME_IDX ON employees(first_name, last_name);
F. CREATE INDEX NAME_IDX FOR employees(first_name, last_name);
Answer: E
Q8. The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL CUSTOMER_ADDRESS VARCHAR2(150) CUSTOMER_PHONE VARCHAR2(20) You need to produce output that states "Dear Customer customer_name, ". The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table. Which statement produces this output?
A. SELECT dear customer, customer_name, FROM customers;
B. SELECT "Dear Customer", customer_name || ',' FROM customers;
C. SELECT 'Dear Customer ' || customer_name ',' FROM customers;
D. SELECT 'Dear Customer ' || customer_name || ',' FROM customers;
E. SELECT "Dear Customer " || customer_name || "," FROM customers;
F. SELECT 'Dear Customer ' || customer_name || ',' || FROM customers;
Answer: D
Q9. Examine the SQL statement that creates ORDERS table: CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN ('CREDIT', 'CASH')), PROD_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order_id, order_date)); For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.)
A. SER_NO
B. ORDER_ID
C. STATUS
D. PROD_ID
E. ORD_TOTAL
F. composite index on ORDER_ID and ORDER_DATE
Answer: A,F
Q10. The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) Which statement finds the highest grade point average (GPA) per semester?
A. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;
B. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;
D. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades; E. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;
Answer: C
Q11. Examine this statement: SELECT student_id, gpa FROM student_grades WHERE gpa > &&value; You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time?
A. An error is returned.
B. You are prompted to enter a new value.
C. A report is produced that matches the first report produced.
D. You are asked whether you want a new value or if you want to run the report based on the previous value.
Answer: C
Q12.Examine the data of the EMPLOYEES table.
EMPLOYEES (EMPLOYEE_ID is the primary key.
MGR_ID is the ID of managers and refers to the EMPLOYEE_ID)
EMPLOYEE_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 HR_MGR 5000
106 Bryan 40 110 AD_ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EX_DIR 8000
120 Ravi 20 110 SA_DIR 6500
Evaluate this SQL statement:
SELECT e.employee_id "Emp_id", e.emp_name "Employee", e.salary,m.employee_id "Mgr_id", m.emp_name "Manager" FROM employees e, employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000; What is its output?
A. EMP_id EMPLOYEE SALARY Mgr_id Manager
---- --------- ------------- ---------------------- -------------
110 Bob 8000 Bob
120 Ravi 6500
110 Ravi
108 Jennifer 6500
110 Jennifer
103 Chris 4200 120 Chris 105 Diana 5000 108 Diana
B. EMP_id EMPLOYEE SALARY Mgr_id Manager ------- ------------- ---------------------------------------- 120 Ravi 6500 110 Bob 108 Jennifer 6500 110 Bob 103 Chris 4200 120 Ravi
105 Diana 5000 108 Jennifer
C. EMP_id EMPLOYEE SALARY Mgr_id Manager ------- ---------- --------- ------------- -------------- 110 Bob 8000 120 Ravi 6500 110 Bob 108 Jennifer 6500 110 Bob 103 Chris 4200
120 Ravi 105 Diana 5000 108 Jennifer
D. EMP_id EMPLOYEE SALARY Mgr_id Manager ------- ---------- --------- ------------- -------------- 110 Bob 8000 110 Bob 120 Ravi 6500 120 Ravi 108 Jennifer 6500 108 Jennifer
103 Chris 4200 103 Chris 105 Diana 5000 105 Dina
E. The SQL statement produces an error.
Answer: B
Q13. You need to write a SQL statement that returns 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. Which statement accomplishes this task?
A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;
B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id
= b.dept_id AND a.sal < b.maxsal;
C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);
D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;
Answer: A.............B
Q14. View the image below and examine the data from the ORDERS and CUSTOMERS tables.
Evaluate this SQL statement: SELECT cust_id, ord_total FROM orders WHERE ord_total > ANY(SELECT ord_total FROM orders WHERE cust_id IN (SELECT cust_id FROM customers WHERE city LIKE 'New York')); What is the result when the above query is executed?
A. **
B. **
C. **
D. **
A. The query returns no rows.
B. The query fails because ANY is not a valid operator with a subquery.
Answer: B
Q15. Mark for review You need to create a table named ORDERS that contains four columns: - an ORDER_ID column of number data type - a CUSTOMER_ID column of number data type - an ORDER_STATUS column that contains a character data type - a DATE_ORDERED column to contain the date the order was placed When a row is inserted into the table, if no value is provided when the order was placed, today's date should be used instead. Which statement accomplishes this?
A. CREATE TABLE orders (order_id NUMBER(10), customer_id NUMBER(8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);
B. CREATE TABLE orders (order_id NUMBER(10), customer_id NUMBER(8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);
C. CREATE OR REPLACE TABLE orders (order_id NUMBER(10), customer_id NUMBER(8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE);
D. CREATE OR REPLACE TABLE orders (order_id NUMBER(10), customer_id NUMBER(8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE);
E. CREATE TABLE orders (order_id NUMBER(10), customer_id NUMBER(8),order_status NUMBER (10),date_ordered DATE = SYSDATE);
F. CREATE TABLE orders (order_id NUMBER(10), customer_id NUMBER(8),order_status NUMBER (10),date_ordered DATE DEFAULT SYSDATE);
Answer: B
Q16. The CUSTOMERS table has these columns: 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) A promotional sale is being advertised to the customers in France. Which WHERE clause identifies customers that are located in France?
A. WHERE lower(country_address) = "france"
B. WHERE lower(country_address) = 'france'
C. WHERE lower(country_address) IS 'france'
D. WHERE lower(country_address) = '%france%'
E. WHERE lower(country_address) LIKE %france%
Answer: B
Q17. Mark for review 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');
Answer: C
Q18. The CUSTOMERS table has these columns:
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. You need to determine how dispersed your customer base is. Which expression finds the number of different countries represented in the CUSTOMERS table?
A. COUNT(UPPER(country_address))
B. COUNT(DIFF(UPPER(country_address)))
C. COUNT(UNIQUE(UPPER(country_address)))
D. COUNT DISTINCT UPPER(country_address)
E. COUNT(DISTINCT (UPPER(country_address)))
Answer: E
Q19. Mark for review View the image below and examine the data in the EMPLOYEES table.
Examine the subquery: SELECT last_name FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department_id); Which statement is true?
A. The SELECT statement is syntactically accurate.
B. The SELECT statement does not work because there is no HAVING clause.
C. The SELECT statement does not work because the column specified in the GROUP BY clause is not in the SELECT list.
D. The SELECT statement does not work because the GROUP BY clause should be in the main query and not in the subquery.
Answer: A
Q20. Mark for review You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns: CUST_ID NUMBER(4) NOT NULL CUST_NAME VARCHAR2(100) NOT NULL CUST_ADDRESS VARCHAR2(150) CUST_PHONE VARCHAR2(20) Which SELECT statement accomplishes this task?
A. SELECT *FROM customers;
B. SELECT name, address FROM customers;
C. SELECT id, name, address, phone FROM customers;
D. SELECT cust_name, cust_address FROM customers;
E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;
Answer: D
Get More Questions and Answers with Explanation at Oracle Forums.