Oracle 1Z0-007 : Introduction to Oracle9i: SQL

The ORDERS table has these columns:

The ORDERS table tracks the Order number, the order total, and the customer to whom the Order belongs. Which two statements retrieve orders with an inclusive total that ranges between 100.00 and 2000.00 dollars? (Choose two.)

• A. SELECT customer_id, order_id, order_total FROM orders WHERE order_total >= 100 and <= 2000;
• B. SELECT customer_id, order_id, order_total FROM orders WHERE order_total BETWEEN 100 and 2000;
• C. SELECT customer_id, order_id, order_total FROM orders RANGE ON order_total (100 AND 2000) INCLUSIVE;
• D. SELECT customer_id, order_id, order_total FROM orders HAVING order_total BETWEEN 100 and 2000;
• E. SELECT customer_id, order_id, order_total FROM orders WHERE order_total >= 100 and order_total <= 2000;

Examine the structures of the EMPLOYEES and TAX tables.

You need to find the percentage tax applicable for each employee. Which SQL statement would you use?

• A. SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE e.salary > t.min_salary, tax_percent
• B. You cannot find the information because there is no common column between the two tables.
• C. SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE MIN(e.salary) = t.min_salary AND MAX(e.salary) = t.max_salary
• D. SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE e.salary BETWEEN t.min_salary AND t.max_salary;

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;

Examine the data in the EMPLOYEES and EMP_HIST tables:

The EMP_HIST table is updated at the end of every year. The employee ID, name, job ID, and salary of each existing employee are modified with the latest data. New employee details are added to the table.
Which statement accomplishes this task?

• A. job id, e.salary);
• B. MERGE INTO emp_hist eh USING employees e ON (eh.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE emp hist SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT INTO emp_hist VALUES (e.employee_id, e.name, e.job_id, e.salary);
• C. UPDATE emp_hist SET employee_id, name, job_id, salary = (SELECT employee_id, name, job_id, salary FROM employees) WHERE employee_id IN (SELECT employee_id FROM employees);
• D. MERGE INTO emp_hist eh USING employees e WHEN MATCHED THEN UPDATE emp_hist SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT INTO emp_hist VALUES (e.employee_id, e.name, e.job_id, e.salary);
• E. MERGE INTO emp_hist eh USING employees e ON (eh.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT VALUES (e.employee id, e.name,

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

• A. INSERT INTO employees VALUES ('1000','John',NULL);
• B. INSERT INTO employees (employee_id) VALUES (1000);
• C. INSERT INTO employees( first_name, last_name) VALUES('John','Smith');
• D. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'John','');
• E. INSERT INTO employees VALUES ( NULL, 'John','Smith');
• F. INSERT INTO employees(first_name,last_name, employee_id) VALUES ( 1000, 'John','Smith');