Q1
You query the database with this SQL statement:
SELECT CONCAT(LOWER(SUBSTR(description, 1, 3)), subject_id) "Subject Description" FROM subject;
In which order are the functions evaluated? CONCAT, LOWER, SUBSTR SUBSTR, LOWER, CONCAT LOWER, SUBSTR, CONCAT All three will be evaluated sim ultaneously. Q2
You query the database with this SQL statement: SELECT bonus FROM salary WHERE bonus BETWEEN 1 AND 250 OR (bonus IN(190, 500, 600) AND bonus BETWEEN 250 AND 500);
Which BONUS value could the statement return? 100 260 400 600 Q3
Seniority is based on the number of years a student has been enrolled at the university. You must create a report that displays each student's name, ID number, and the number of years enrolled. The years enrolled must be rounded to a whole number, based on the number of months from the date enrolled until today. Which statement produces the required results? SELECT first_name||', '||last_name "Student Name", id "Id", enroll_date, ROUND(SYSDATE) - ROUND(enroll_date) "Seniority" FROM student; SELECT first_name||', '||last_name "Student Name", id "Id", enroll_date, (ROUND(SYSDATE) - ROUND(enroll_date)) / 12 "Seniority" FROM student;
SELECT first_name||', '||last_name "Student Name", id "Id", enroll_date, TRUNC(SYSDATE, 'YY') - TRUNC(enroll_date, 'YY') "Seniority" FROM student; SELECT first_name||', '||last_name "Student Name", id "Id", enroll_date, ROUND(MONTHS_BETWEEN(SYSDATE, enroll_date) / 12) "Seniority" FROM student; Q4 The
transaction table contains these columns:
SQL> DESCRIBE transaction TRANSACTION_ID NUMBER(9) TRANS_CODE VARCHAR2(5) CUST_ACCOUNT VARCHAR2(12)
A new standard was adopted in your department affecting reports produced by querying the transaction table. When creating reports, a dash ( -) followed by the three characters 'ANI' must be appended to all transaction codes that contain only three characters. Any leading 'W' in a transaction code must be removed from the resulting data display. Which query will return the desired results?
SELECT TRIM('W' (RPAD(trans_code, 7, '-ANI')))FROM transaction WHERE LENGTH(trans_code) = 3; SELECT TRIM('W' FROM (RPAD(trans_code, 3, '-ANI')))FROM transaction WHERE LENGTH(trans_code) = 3; SELECT TRIM(LEADING 'W' FROM (RPAD(trans_code, 7, '-ANI')))FROM transaction WHERE LENGTH(trans_code) = 3; SELECT TRIM(LEADING 'W' FROM (RPAD(trans_code, 3, '-ANI'))) FROM transaction WHERE LENGTH(trans_code) = 3; Q5
Evaluate the following SELECT statement:
SELECT DISTINCT emp_id FROM emp e JOIN emp_hist h ON e.emp_id = h.emp_id;
Which SELECT statement will return the same result as the given statement?
SELECT emp_id FROM emp UNION SELECT emp_id FROM emp_hist; SELECT e.emp_id FROM emp e, emp_hist h; WHERE e.emp_id <> h.emp_id;
SELECT emp_id FROM emp MINUS SELECT emp_id FROM emp_hist; SELECT emp_id FROM emp INTERSECT SELECT emp_id FROM emp_hist; Q6
Examine the structure of the LINE_ITEM table.
You want to display order id numbers, product id numbers, and the quantities ordered for each of the products ordered with these desired results:
The volume of the item ordered must be 50 or greater. The displayed results must be sorted from lowest to the highest by order id number, and then by product id number. The items must belong to order numbers ranging from 1800 to 1900.
Evaluate this SQL statement: SELECT order_id, product_id, quantity FROM line_item WHERE quantity >= 50 AND order_id IN(1800, 1900) ORDER BY order_id, product_id;
Which statement about using this query as the proposed solution is true? One of the desired results is achieved. Two of the desired results are achieved. All of the desired results ar e achieved. The statement generates an error.
Q7
Evaluate this compound query statement: SELECT emp_id, last_name || ', '|| first_name FROM emp INTERSECT SELECT emp_id FROM emp_hist;
Which statement is TRUE regarding the given SELECT statement? Duplicate EMP_ID values will be included. The output will be sorted by the EMP_ID values in ascending order. The results will contain the distinct EMP_ID values return by either query. The statement will return an error . Q8
Examine the data in the TEACHER table.
Assume the user enters the following SELECT statement to retrieve data from the TEACHER table: SQL> SELECT * FROM teacher WHERE INSTR(subject_id, '&1') = 4 AND LOWER(subject_id) LIKE 'HST%';
When prompted for the WHERE clause value, you enter an underscore (_). Which result will this statement provide? It will execute, but it will not retrieve any data. It will display information on all teachers whose SUBJECT_ID begins with 'HST_'.
It will return a syntax error because the TO_CHAR function was not used in the WHERE clause. It will display information on all teachers whose SUBJECT_ID begins with 'HST_', regardless of the case in which the SUBJECT_ID is stored. Q9
The SERVICE table contains these columns: ID NUMBER PK SERVICE_DATE DATE TECHNICIAN_ID NUMBER DESCRIPTION VARCHAR2(50)
Which SELECT statement could you use to display the number of times each technician performed a service between January 1, 2008 and June 30, 2008? SELECT COUNT(*) FROM service WHERE service_date BETWEEN '01-JAN-2008' AND '30-JUN-2008' GROUP BY service_date; SELECT COUNT(service_date) FROM service WHERE service_date BETWEEN '01-JAN-2008' AND '30-JUN-2008' GROUP BY service_date; SELECT technician_id, service_date, COUNT(*) FROM service WHERE service_date BETWEEN '01-JAN-2008' AND '30-JUN-2008' ORDER BY technician_id, service_date; SELECT technician_id, COUNT(technician_id) FROM service WHERE service_date BETWEEN '01-JAN-2008' AND '30-JUN-2008' GROUP BY technician_id; Q10
Examine the data in the TEACHER table.
You want to query the TEACHER table and display the following results:
Name Subject ------------------------------------- ------------------Jones, Karen HST_REVOL Hopewell, Mary Elizabeth HST_RELIG
What should you use to query the TEACHER table? SELECT last_name||', '||first_name "Name", subject_id "Subject" FROM teacher WHERE subject_id = 'HST\_R%'; SELECT last_name||', '||first_name "Name", subject_id "Subject" FROM teacher WHERE subject_id LIKE 'HST_%'; SELECT last_name||', '||first_name "Name", subject_id "Subject" FROM teacher WHERE subject_id LIKE '%HST\_R%' ESC '\'; SELECT last_name||', '||first_name "Name", subject_id "Subject" FROM teacher WHERE subject_id LIKE 'HST\_R%' ESCAPE '\'; Q11
Which arithmetic expression will return a numeric value?
'14-FEB-2002' + 25 '03-JAN-2000' - 30 '17-JUN-1999' * (480/24) TO_DATE('01-JAN-2001') - TO_DATE('01-DEC-2000') Q12
Which set operator would you use to display the employee IDs of employees hired after January 10, 2007 in the EMP table and employee IDs of employees who have held more than one position in the EMP_HISTtable, eliminating any duplicate IDs? UNION UNION ALL INTERSECT MINUS Q 13 Evaluate this SQL statement:
SELECT c.customer_id, o.order_id, o.order_date, p.product_name FROM customer c, curr_order o, product p WHERE customer.customer_id = curr_order.customer_id AND o.product_id = p.product_id
ORDER BY o.order_amount; This statement fails when executed. Which change will correct the problem? Use the table name in the ORDER BY clause. Remove the table aliases from the WHERE clause. Include the ORDER_AMOUNT column in the SELECT list. Use the table aliases instead of the table names in the WHERE clause. Remove the table alias from the ORDER BY clause and use only the column name. Q14
Which SELECT statement should you use if you want to display unique combinations of the POSITION and MANAGER values from the EMPLOYEE table? SELECT position, manager DISTINCT FROM employee; SELECT position, manager FROM employee; SELECT DISTINCT position, manager FROM employee; SELECT position, DISTINCT manager FROM employee; Q15
Examine the data in the TEACHER table.
Evaluate this SQL statement: SELECT last_name||', '||first_name FROM teacher WHERE subject_id != NULL ORDER BY last_name;
Which value is displayed FIRST when executing this query? Tsu, Ming Hann, Jeff Smith, Ellen No value is displayed. Q16
The ACCOUNT table contains these columns:
ACCOUNT_ID NUMBER(12) NEW_PURCHASES NUMBER(7,2) PREV_BALANCE NUMBER(7,2) FINANCE_CHARGE NUMBER(7,2) PAYMENTS NUMBER(7,2)
You must print a report that contains the account number and the current balance for a particular customer. The current balance consists of the sum of an account's previous balance, new purchases, and finance charge. You must calculate the finance charge based on a rate of 1.5 percent of the previous balance. Payments must be deducted from this amount. The customer's account number is 543842. Which SELECT statement should you use? A SELECT new_balance + finance_charge - payments FROM account WHERE account_id = 543842; b SELECT account_id, new_purchases + prev_balance * 1.015 - payments FROM account WHERE account_id = 543842; c
SELECT account_id, new_purchases + (prev_balance * .015) - payments FROM account WHERE account_id = 543842;
d SELECT account_id, new_purchases + (prev_balance * 1.015) + finance_charge payments FROM account WHERE account_id = 543842;
Q17.
Examine the structure of the LINE_ITEM table.
You must display the order number, line item number, product identification number, and quantity of each item where the quantity ranges from 10 through 100. The order numbers must be in the range of 1500 through 1575. The results must be sorted by order number from lowest to highest, and then further sorted by quantity from highest to lowest. Which statement should you use to display the desired results? a SELECT order_id, line_item_id, product_id, quantity FROM line_item WHERE quantity BETWEEN 9 AND 101 AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id DESC, quantity DESC; b SELECT order_id, line_item_id, product_id, quantity FROM line_item WHERE (quantity > 10 AND quantity < 100) AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id ASC, quantity; c
SELECT order_id, line_item_id, product_id, quantity FROM line_item WHERE (quantity > 9 OR quantity < 101) AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id, quantity;
d SELECT order_id, line_item_id, product_id, quantity FROM line_item WHERE quantity BETWEEN 10 AND 100 AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id, quantity DESC; Q18.
The LINE_ITEM table contains these columns
LINE_ITEM_ID NUMBER(9) Primary Key ORDER_ID NUMBER(9) PRODUCT_ID VARCHAR2(9) QUANTITY NUMBER(5)
Evaluate this SQL statement: SELECT quantity, product_id
FROM line_item ORDER BY quantity, product_id;
Which statement is true concerning the results of executing this statement? a The results are sorted numerically only. b The results are sorted alphabetically only. c The results are sorted numerically and then alphabetically. d The results are sorted alphabetically and then numerically. Q19.
Review the structure and the data contained in the EMPLOYEE_MASTER table.
The structure of the EMPLOYEE_MASTER table is as follows:
The data in the EMPLOYEE_MASTER table is as follows:
Employees are given performance reviews once a year and that results in a rating between 1 (lowest) and 5 (highest). This rating and the current salary are stored in the EMPLOYEE_MASTER table. Proposed new salaries are based upon the current salary and the performance rating. If an employee received a performance rating of 5, their proposed new salary would increase the current salary by 5%. Performance ratings of 4 would result in a 3% proposed increase over the current salary, and a rating of 3 would increase the current salary by 2%. Employees with a rating of 2 or 1 would not receive an increase. The finance department has been requested to print a report showing each employee_ id, name, current_salary, proposed_salary, job_code, and mgr_id. This report
should NOT include employees who work in sales, nor should it include the CEO due to privacy concerns. Sales employees can be identified by a job_code of either 25 or 27. The CEO does not have a manager, so for the purpose of storing data into the table it appears the CEO?s manager is himself. While all other employees have a mgr_id of another employee in the company, the CEO has a mgr_id value identical to his employee_id. Which one of the following SELECT statements will produce the report as it has been defined? a SQL> SELECT employee_id, name, current_salary, CASE performance_rating WHEN 5 THEN 1.05*current_salary WHEN 4 THEN 1.03*current_salary WHEN 3 then 1.02*current_salary ELSE current_salary END "Proposed Salary", job_code, mgr_id FROM employee_master WHERE job_code NOT IN (25,27) AND NULLIF(employee_id,mgr_id) IS NOT NULL; b SQL> SELECT employee_id, name, current_salary, CASE performance_rating WHEN 5 THEN 1.05*current_salary WHEN 4 THEN 1.03*current_salary WHEN 3 then 1.02*current_salary ELSE current_salary END "Proposed Salary", job_code, mgr_id FROM employee_master WHERE job_code NOT BETWEEN (25,27) OR employee_id = mgr_id; c
SQL> SELECT employee_id, name, current_salary, performance_rating CASE WHEN 5 THEN 1.05*current_salary WHEN 4 THEN 1.03*current_salary WHEN 3 then 1.02*current_salary ELSE current_salary END "Proposed Salary", job_code, mgr_id FROM employee_master WHERE job_code <> 25 and job_code <> 27 OR employee_id = mgr_id;
d SQL> SELECT employee_id, name, current_salary, CASE WHEN performance_rating = 5 THEN 1.05*current_salary WHEN = 4 THEN 1.03*current_salary WHEN = 3 then 1.02*current_salary WHEN <= 2 THEN 1.00*current_salary END "Proposed Salary", job_code, mgr_id FROM employee_master WHERE job_code <> 25 and job_code != 27 OR employee_id = mgr_id; Q20. The ACCOUNT table contains these columns:
ACCOUNT_ID NUMBER(12) NEW_BALANCE NUMBER(7,2) PREV_BALANCE NUMBER(7,2) FINANCE_CHARGE NUMBER(7,2)
With the least amount of effort, you want to display all of the rows in the ACCOUNT table. Which query should you use? a SELECT * FROM account; b SELECT all FROM account; c SELECT any FROM account; d SELECT account_id, new_balance, prev_balance, finance_charge FROM account; Q21. The
employee table contains a column called first_name and a column called last_name. Both columns have been defined as VARCHAR2(25). You want to create a username for the employee that is comprised of all lowercase characters. It will be derived by taking the first character of the first name followed by the full last name. As an example, if first_name='jOhN' and last_name='sMitH', the username would be 'jsmith'. However, there is an 8-character maximum for usernames. Which one of the following SELECT statements will correctly produce the username for each employee? (Choose all that apply.) a SELECT first_name, last_name, LOWER(SUBSTR(first_name,1,1))||LOWER(SUBSTR(last_name,1,7)) username FROM employee e1 b SELECT first_name, last_name,LOWER(CONCAT( LTRIM(first_name,1),SUBSTR(last_name,1,7) username FROM employee e2 c SELECT first_name, last_name, LOWER(CONCAT(SUBSTR(first_name,1,1),SUBSTR(last_name,1,7))) username FROM employee e3 d SELECT first_name, last_name,CONCAT(LOWER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(last_name,1,7)) ) username FROM employee e4 e SELECT first_name, last_name, LOWER(CONCAT(SUBSTR(first_name,1,1)||SUBSTR(last_name,1,7))) username FROM employee e5 Q22. Examine
the structures of the EMPLOYEE and DEPARTMENT tables:
EMPLOYEE -----------------EMP_ID NUMBER NOT NULL PK NAME VARCHAR(30) NOT NULL FNAME VARCHAR(25) NOT NULL DEPT_NO NUMBER TITLE VARCHAR2(25)
DEPARTMENT -----------------------DEPT_ID NUMBER NOT NULL PK DEPT_NAME VARCHAR2(25)
You need to produce a list of departments, including the department name, which have more than three administrative assistants. Which SELECT statement will produce the desired result? a SELECT dept_name FROM employee JOIN department ON employee.dept_id = department.dept_id WHERE UPPER(title) = 'ADMINISTRATIVE ASSISTANT' GROUP BY dept_name HAVING emp_id > 3; b SELECT dept_name FROM employee GROUP BY dept_no HAVING LOWER(title) = 'administrative assistant' AND COUNT(*) > 3; c
SELECT dept_name FROM employee NATURAL JOIN department WHERE LOWER(title) = 'administrative assistant' GROUP BY dept_name HAVING COUNT(emp_id) > 3;
d
SELECT dept_name FROM employee e JOIN department d ON (e.dept_no = d.dept_id) WHERE LOWER(title) = 'administrative assistant' AND COUNT(*) > 3;
e SELECT d.dept_name FROM employee e JOIN department d ON (e.dept_no = d.dept_id) WHERE LOWER(title) = 'administrative assistant' GROUP BY dept_name HAVING COUNT(emp_id) > 3; f
SELECT d.dept_name FROM e.employee JOIN d.department ON (e.dept_no = d.dept_id) WHERE LOWER(title) = 'administrative assistant' GROUP BY dept_name HAVING COUNT(emp_id) > 3; Q23.
Which three functions can be used to manipulate character column values? (Choose three.) a RPAD b TRUNC c ROUND d INSTR e CONCAT
Q24. Examine
the data in the PRODUCT table.
You execute the following query: SELECT description, quantity, cost FROM product WHERE manufacturer_id LIKE 'NF10032' AND NVL(cost, 0) < 5.00 ORDER BY quantity DESC, cost;
Which result will the query provide? a DESCRIPTION QUANTITY COST ------------------------- ------------------- --------AA 2pk-battery 2513 AAA 6pk-battery 546 3 b DESCRIPTION QUANTITY COST ------------------------- ------------------- --------AAA 8pk-battery 4.2 AA 2pk-battery 2513 AAA 6pk-battery 546 3 c
DESCRIPTION QUANTITY COST ------------------------- ------------------- --------AAA 6pk-battery 546 3 AAA 8pk-battery 4.2 AA 2pk-battery 2513
d DESCRIPTION QUANTITY COST ------------------------- ------------------- --------AA 2pk-battery 2513 AAA 6pk-battery 546 3 AAA 8pk-battery 4.2 Q25. Examine
the data from the PO_HEADER and PO_DETAIL tables.
You need to produce a report to identify any PO_HEADER rows that have no matching PO_DETAIL rows and any PO_DETAIL rows that have no matching PO_HEADER record. Which SELECT statement should you execute? A
SELECT h.po_num, d.po_num, d.po_line_id FROM po_header h FULL OUTER JOIN po_detail d ON (h.po_num = d.po_num) WHERE h.po_num IS NULL OR d.po_line_id IS NULL;
B
SELECT h.po_num, d.po_num, d.po_line_id FROM po_header h LEFT OUTER JOIN po_detail d ON (h.po_num = d.po_num) WHERE d.po_num IS NULL;
C
SELECT h.po_num, d.po_num, d.po_line_id FROM po_header h FULL OUTER JOIN po_detail d ON (h.po_num = d.po_num) WHERE h.po_num IS NULL AND d.po_line_id IS NULL;
D
SELECT h.po_num, d.po_num, d.po_line_id FROM po_header h RIGHT OUTER JOIN po_detail d ON (h.po_num = d.po_num) WHERE h.po_num IS NULL; Q26. Examine
the structure of the PRODUCT table.
You want to display the product identification numbers of all products with 500 or more units available for immediate sale. You want the product identification numbers displayed numerically by supplier identification number, then by product identification number from lowest to highest. Which statement should you use to achieve the required results? a SELECT product_id FROM product WHERE qty_per_unit >= 500 ORDER BY supplier_id, product_id; b SELECT product_id FROM product WHERE qty_per_unit >= 500 SORT BY supplier_id, product_id; c
SELECT product_id FROM product WHERE qty_per_unit >= 500 ORDER BY supplier_id, product_id DESC;
d SELECT product_id FROM product WHERE qty_per_unit > 500 SORT BY supplier_id, product_id; Q27. Calculate
the value returned by this SELECT statement:
SQL> SELECT ROUND(16.9) - TRUNC(4.8) - MOD (41,14) FROM dual; a 2 b -2 c 0 d 13 e -1 Q28. The
table called customer contains a column called city, which is defined as VARCHAR2(15). The application developer believes that a couple of cities actually are stored in a truncated manner because the name of the city, when written out in English, is more than 15 characters. Which SELECT statement will find the customer_name and city where all 15 characters are being used to store the name
of the city? a SELECT customer_name, city FROM customer WHERE city(LENGTH) = 15 b SELECT customer_name, city FROM customer WHERE LENGTH(city) > 15 c SELECT customer_name, city FROM customer WHERE LENGTH(city) = 15 d SELECT customer_name, city FROM customer WHERE city(LENGTH) = 15 e It is impossible to answer this question because the column city is of variable length due to the VARCHAR2 column definition. Q29.
The TEACHER table contains these columns:
ID NUMBER(9) Primary Key LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) SUBJECT_ID NUMBER(9)
Which query should you use to display only the full name of each teacher along with the identification number of the subject each teacher is responsible for teaching? a SELECT * FROM teacher; b SELECT last_name, subject_id FROM teacher; c SELECT last_name, first_name, id FROM teacher; d SELECT last_name, first_name, subject_id FROM teacher; Q30. Examine
the data in the PRODUCT table.
Evaluate this SELECT statement: SELECT description, cost FROM product ORDER BY cost, quantity;
Which statements are true? (Choose all that apply.) a The PRODUCT_ID value for the first record displayed is 220. b The PRODUCT_ID values for the last two rows displayed are 140 and 126. c The DESCRIPTION value for the first two rows displayed is C 2pk-battery. d The DESCRIPTION value for the first two rows displayed is AA 2pk-battery. e No row with a PRODUCT_ID of 220 is displayed.
