Institute Exit Exam Section 1 1. You issue this SQL statement: SELECT ROUND (1282.

248, -2) FROM dual; What value does this statement produce? Mar for Review (1) Points

2. What will the following SQL statemtent display? SELECT last_name, LPAD(salary, 15, '$')SALARY FROM employees; Mar for Review (1) Points The last name of employees that have a salary that includes a $ in the value , size of 15 and the column labeled SALARY. The last name and the format of the salary limited to 15 digits to the left of the decimal and the column labeled SALARY. The last name and salary for all employees with the format of the salary 15 characters long, left-padded with the $ and the column labeled SALARY. (*) The query will result in an error: "ORA-00923: FROM eyword not found where expected."

3. You need to return a portion of each employee's last name, beginning with the first character up to the fifth character. Which character function should y ou use? Mar for Review (1) Points INSTR TRUNC SUBSTR (*) CONCAT Incorrect. Refer to Section 1 Lesson 1. 4. You need to display the number of months between today's date and each emp

1200 1282 1282.25 1300 (*)

ROUND BETWEEN ADD_MONTHS MONTHS_BETWEEN (*) Incorrect. Refer to Section 1 Lesson 3. 5. Evaluate this SELECT statement: SELECT SYSDATE + 30 FROM dual; Which value is returned by the query? Mar for Review (1) Points

The current date plus 30 hours. The current date plus 30 days. (*) The current date plus 30 months. No value is returned because the SELECT statement generates an error. Incorrect. Refer to Section 1 Lesson 3.

Section 2 6. You need to replace null values in the DEPT_ID column with the text "N/A". Which functions should you use? Mar for Review (1) Points TO CHAR and NVL (*) TO_CHAR and NULL TO_CHAR and NULLIF TO_NUMBER and NULLIF Incorrect. Refer to Section 2 Lesson 2. 7. Which arithmetic operation will return a numeric value? Mar for Review (1) Points TO_DATE('01-JUN-2004') - TO_DATE('01-OCT-2004') (*)

loyee's hiredate. Which function should you use? Mar (1) Points

for Review

NEXT_DAY(hire_date) + 5 SYSDATE - 6 SYSDATE + 30 / 24 Incorrect. Refer to Section 2 Lesson 1. 8. All Human Resources data is stored in a table named EMPLOYEES. You have be en as ed to create a report that displays each employee's name and salary. Each employee's salary must be displayed in the following format: $000,000.00. Which function should you include in a SELECT statement to achieve the desired result? Mar for Review (1) Points TO_CHAR (*) TO_DATE TO_NUMBER CHARTOROWID Incorrect. Refer to Section 2 Lesson 1.

Section 3 9. Which of the following statements is the simplest description of a nonequi join? Mar for Review (1) Points A join condition containing something other than an equality operator (*) A join condition that is not equal to other joins. A join condition that includes the (+) on the left hand side. A join that joins a table to itself Incorrect. Refer to Section 3 Lesson 2. 10. Below find the structure of the CUSTOMERS and SALES_ORDER tables: CUSTOMERS CUSTOMER_ID NUMBER NOT NULL, Primary Key CUSTOMER_NAME VARCHAR2 (30) CONTACT_NAME VARCHAR2 (30) CONTACT_TITLE VARCHAR2 (20) ADDRESS VARCHAR2 (30) CITY VARCHAR2 (25) REGION VARCHAR2 (10) POSTAL_CODE VARCHAR2 (20) COUNTRY_ID NUMBER Foreign ey to COUNTRY_ID column of the COUNTRY table

PHONE VARCHAR2 (20) FAX VARCHAR2 (20) CREDIT_LIMIT NUMBER(7,2) SALES_ORDER ORDER_ID NUMBER NOT NULL, Primary Key CUSTOMER_ID NUMBER Foreign ey to CUSTOMER_ID column of the CUSTOMER table ORDER_DT DATE ORDER_AMT NUMBER (7,2) SHIP_METHOD VARCHAR2 (5) You need to create a report that displays customers without a sales order. Which statement could you use? Mar for Review (1) Points SELECT c.customer_name FROM customers c WHERE c.customer_id not in (SELECT s.customer_id FROM sales_order s) (*) SELECT c.customer_name FROM customers c, sales_order s WHERE c.customer_id = s.customer_id(+); SELECT c.customer_name FROM customers c, sales_order s WHERE c.customer_id (+) = s.customer_id; SELECT c.customer_name FROM customers c RIGHT OUTER JOIN sales_order s ON (c.customer_id = s.customer_id);

Incorrect. Refer to Section 3 Lesson 2.

Page 1 of 4

Test: Institute Exit Exam

Section 3

Review your answers, feedbac , and question scores below. An asteris tes a correct answer.

(*) indica

11. Which SELECT statement implements a self join? Mar for Review (1) Points SELECT p.part_id, t.product_id FROM part p, part t WHERE p.part_id = t.product_id; (*) SELECT p.part_id, t.product_id FROM part p, product t WHERE p.part_id = t.product_id; SELECT p.part_id, t.product_id FROM part p, product t WHERE p.part_id = t.product_id (+); SELECT p.part_id, t.product_id FROM part p, product t WHERE p.part_id =! t.product_id;

Incorrect. Refer to Section 3 Lesson 4.

Section 4 12. Evaluate this SELECT statement: SELECT COUNT(*) FROM employees WHERE salary > 30000; Which results will the query display? Mar for Review (1) Points

The number of employees that have a salary less than 30000. The total of the SALARY column for all employees that have a salary greater than 30000. The number of rows in the EMPLOYEES table that have a salary greater than 30 000. (*) The query generates an error and returns no results. Incorrect. Refer to Section 4 Lesson 3. 13. Which group function would you use to display the highest salary value in the EMPLOYEES table? Mar for Review (1) Points AVG

COUNT MAX (*) MIN Incorrect. Refer to Section 4 Lesson 2.

Section 5 14. What will the following SQL Statement do? SELECT job_id, COUNT(*) FROM employees GROUP BY job_id; Mar for Review (1) Points

Section 6 15. Examine the structures of the CUSTOMER and ORDER_HISTORY tables: CUSTOMER CUSTOMER_ID NUMBER(5) NAME VARCHAR2(25) CREDIT_LIMIT NUMBER(8,2) OPEN_DATE DATE ORDER_HISTORY ORDER_ID NUMBER(5) CUSTOMER_ID NUMBER(5) ORDER_DATE DATE TOTAL NUMBER(8,2) Which of the following scenarios would require a subquery to return the desired results? Mar for Review (1) Points

Displays all the employees and groups them by job. Displays each job id and the number of people assigned to that job id. (*) Displays only the number of job_ids. Displays all the jobs with as many people as there are jobs. Incorrect. Refer to Section 5 Lesson 1.

You need to display the date each customer account was opened. You need to display each date that a customer placed an order.

You need to display all the orders that were placed on a certain date. You need to display all the orders that were placed on the same day as order number 25950. (*) Incorrect. Refer to Section 6 Lesson 1. 16. You are loo ing for Executive information using a subquery. What will the following SQL statement display? SELECT department_id, last_name, job_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Executive'); Mar for Review (1) Points The department ID, department name and last name for every employee in the E xecutive department. The department ID, last name, department name for every Executive in the emp loyees table. The department ID, last name, job ID from departments for Executive employee s. The department ID, last name, job ID for every employee in the Executive dep artment. (*) Incorrect. Refer to Section 6 Lesson 3. 17. Examine the structure of the EMPLOYEE, DEPARTMENT, and ORDERS tables. EMPLOYEE: EMPLOYEE_ID NUMBER(9) LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) DEPARTMENT_ID NUMBER(9) DEPARTMENT: DEPARTMENT_ID NUMBER(9) DEPARTMENT_NAME VARCHAR2(25) CREATION_DATE DATE ORDERS: ORDER_ID NUMBER(9) EMPLOYEE_ID NUMBER(9) DATE DATE CUSTOMER_ID NUMBER(9) You want to display all employees who had an order after the Sales department wa s established. Which of the following constructs would you use? Mar for Review (1) Points

A group function A single-row subquery (*)

The HAVING clause A MERGE statement Incorrect. Refer to Section 6 Lesson 2.

Section 7 18. You need to update both the DEPARTMENT_ID and LOCATION_ID columns in the EMPLOYEES table using one UPDATE statement. Which clause should you include in t he UPDATE statement to update multiple columns? Mar for Review (1) Points The USING clause The ON clause The WHERE clause The SET clause (*) Incorrect. Refer to Section 7 Lesson 2. 19. Examine the structures of the PRODUCTS and SUPPLIERS tables: SUPPLIERS SUPPLIER_ID NUMBER NOT NULL, Primary Key SUPPLIER_NAME VARCHAR2 (25) ADDRESS VARCHAR2 (30) CITY VARCHAR2 (25) REGION VARCHAR2 (10) POSTAL_CODE VARCHAR2 (11) PRODUCTS PRODUCT_ID NUMBER NOT NULL, Primary Key PRODUCT_NAME VARCHAR2 (25) SUPPLIER_ID NUMBER Foreign ey to SUPPLIER_ID of the SUPPLIERS table CATEGORY_ID NUMBER QTY_PER_UNIT NUMBER UNIT_PRICE NUMBER (7,2) QTY_IN_STOCK NUMBER QTY_ON_ORDER NUMBER REORDER_LEVEL NUMBER You want to delete any products supplied by the five suppliers located in Atlant a. Which script should you use? Mar for Review (1) Points DELETE FROM products WHERE supplier_id IN (SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ATLANTA'); (*)

DELETE FROM products WHERE UPPER(city) = 'ATLANTA'; DELETE FROM products WHERE supplier_id = (SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ATLANTA'); DELETE FROM suppliers WHERE supplier_id IN (SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ALANTA');

Incorrect. Refer to Section 7 Lesson 2. 20. The STUDENTS table contains these columns: STU_ID NUMBER(9) NOT NULL LAST_NAME VARCHAR2 (30) NOT NULL FIRST_NAME VARCHAR2 (25) NOT NULL DOB DATE STU_TYPE_ID VARCHAR2(1) NOT NULL ENROLL_DATE DATE You create another table, named FT_STUDENTS, with an identical structure.You wan t to insert all full-time students, who have a STU_TYPE_ID value of "F", into th e new table. You execute this INSERT statement: INSERT INTO ft_students (SELECT stu_id, last_name, first_name, dob, stu_type_id, enroll_date FROM students WHERE UPPER(stu_type_id) = 'F'); What is the result of executing this INSERT statement? Mar for Review (1) Points

e. Incorrect. Refer to Section 7 Lesson 1.

Page 2 of 4 Test: Institute Exit Exam

All full-time students are inserted into the FT_STUDENTS table. (*) An error occurs because the FT_STUDENTS table already exists. An error occurs because you CANNOT use a subquery in an INSERT statement. An error occurs because the INSERT statement does NOT contain a VALUES claus

Section 8 21. The SPEED_TIME column should store a fractional second value. Which data type should you use? Mar for Review (1) Points DATE DATETIME TIMESTAMP (*) INTERVAL DAY TO SECOND Incorrect. Refer to Section 8 Lesson 2. 22. The ELEMENTS column is defined as: NUMBER(6,4) How many digits to the right of the decimal point are allowed for the ELEMENTS c olumn? Mar for Review (1) Points

Zero Two Four (*) Six Incorrect. Refer to Section 8 Lesson 2. 23. You are creating the EMPLOYEES table. This table should contain the COMMI SSION_PCT column and use a value of 10 percent if no commission value is provide d when a record is inserted. Which line should you include in the CREATE TABLE s tatement to accomplish this tas ? Mar for Review (1) Points commission_pct NUMBER(4,2) DEFAULT 0.10 (*) commission_pct NUMBER(4,2) DEFAULT = 0.10 commission_pct NUMBER(4,2) DEFAULT (0.10) commission_pct NUMBER(4,2) (DEFAULT, 0.10) Incorrect. Refer to Section 8 Lesson 1. 24. Examine the structure of the DONATIONS table.

Review your answers, feedbac , and question scores below. An asteris tes a correct answer.

(*) indica

DONATIONS: PLEDGE_ID NUMBER DONOR_ID NUMBER PLEDGE_DT DATE AMOUNT_PLEDGED NUMBER (7,2) AMOUNT_PAID NUMBER (7,2) PAYMENT_DT DATE You need to reduce the precision of the AMOUNT_PLEDGED column to 5 with a scale of 2 and ensure that when inserting a row into the DONATIONS table without a val ue for the AMOUNT_PLEDGED column, a price of $10.00 will automatically be insert ed. The DONATIONS table currently contains NO records. Which statement is true? Mar for Review (1) Points

25. You need to remove all the data in the SCHEDULE table, the structure of t he table, and the indexes associated with the table. Which statement should you use? Mar for Review (1) Points DROP TABLE (*) TRUNCATE TABLE ALTER TABLE DELETE TABLE Incorrect. Refer to Section 8 Lesson 3.

Section 10 26. Primary Key, Foreign Key, Unique Key and Chec Constraints can be added a t which two levels? (Choose two) Mar for Review (1) Points (Choose all correct answers) Null Field Table (*) Row

You CANNOT decrease the width of the AMOUNT_PLEDGED column. Both changes can be accomplished with one ALTER TABLE statement. (*) You must drop and recreate the DONATIONS table to achieve these results. You must use the ADD OR REPLACE option to achieve these results. Incorrect. Refer to Section 8 Lesson 3.

Dictionary Column (*) Incorrect. Refer to Section 10 Lesson 1. 27. When creating the EMPLOYEES table, which clause could you use to ensure t hat salary values are 1000.00 or more? Mar for Review (1) Points CONSTRAINT CHECK salary > 1000 CHECK CONSTRAINT (salary > 1000) CONSTRAINT employee_salary_min CHECK salary > 1000 CONSTRAINT employee_salary_min CHECK (salary >= 1000) (*) CHECK CONSTRAINT employee_salary_min (salary > 1000) Incorrect. Refer to Section 10 Lesson 2. 28. You need to create a composite primary ey constraint on the EMPLOYEES ta ble. Which statement is true? Mar for Review (1) Points The PRIMARY KEY constraint must be defined at the table level. (*) A PRIMARY KEY constraint must be defined for each column in the composite pr imary ey. The PRIMARY KEY constraint must be defined for the first column of the compo site primary ey. The PRIMARY KEY constraint must be defined at the table level and for each c olumn in the composite primary ey. Incorrect. Refer to Section 10 Lesson 2. 29. You need to display the names and definitions of constraints only in your schema. Which data dictionary view should you query? Mar for Review (1) Points DBA_CONSTRAINTS USER_CONSTRAINTS (*) ALL_CONS_COLUMNS USER_CONS_COLUMNS Incorrect. Refer to Section 10 Lesson 3.

30. When dropping a constraint, which eyword(s) specifies that all the refer ential integrity constraints that refer to the primary and unique eys defined o n the dropped columns are dropped as well? Mar for Review (1) Points FOREIGN KEY REFERENCES CASCADE (*) ON DELETE SET NULL Incorrect. Refer to Section 10 Lesson 3.

Page 3 of 4

Test: Institute Exit Exam

Section 11 31. Evaluate this CREATE VIEW statement: CREATE VIEW sales_view AS SELECT customer_id, region, SUM(sales_amount) FROM sales WHERE region IN (10, 20, 30, 40) GROUP BY region, customer_id; Which statement is true? Mar for Review (1) Points

You can modify data in the SALES table using the SALES_VIEW view. You cannot modify data in the SALES table using the SALES_VIEW view. (*) You can only insert records into the SALES table using the SALES_VIEW view. The CREATE VIEW statement generates an error. Incorrect. Refer to Section 11 Lesson 3. 32. You need to create a view that when queried will display the name, employ ee identification number, first and last name, salary, and department identifica

Review your answers, feedbac , and question scores below. An asteris tes a correct answer.

(*) indica

tion number. When queried, the display should be sorted by salary from lowest to highest, then by last name and first name alphabetically. The view definition s hould be created regardless of the existence of the EMPLOYEES table. No DML may be performed when using this view. Evaluate these statements: CREATE OR REPLACE NOFORCE VIEW EMP_SALARY_V AS SELECT employee_id, last_name, first_name, salary, department_id FROM employees WITH READ ONLY; SELECT * FROM emp_salary_v ORDER BY salary, last_name, first_name; Which statement is true? Mar for Review (1) Points

*) The statements will NOT return all of the desired results because the WITH C HECK OPTION clause is NOT included in the CREATE VIEW statement. To achieve all of the desired results this ORDER ON clause should be added t o the CREATE VIEW statement: 'ORDER ON salary, last_name, first_name?. Incorrect. Refer to Section 11 Lesson 1. 33. Which statement would you use to alter a view? Mar for Review (1) Points ALTER VIEW MODIFY VIEW ALTER TABLE CREATE OR REPLACE VIEW (*) Incorrect. Refer to Section 11 Lesson 1.

You can delete data in a view if the view contains the DISTINCT eyword. You cannot modify data in a view if the view contains a WHERE clause. You cannot modify data in a view if the view contains a group function. (*) You can modify data in a view if the view contains a GROUP BY clause. Incorrect. Refer to Section 11 Lesson 2.

34. Which statement about performing DML operations on a view is true? Mar for Review (1) Points

When both statements are executed all of the desired results are achieved. The CREATE VIEW statement will fail if the EMPLOYEES table does not exist. (

Section 12 35. You want to create a composite index on the FIRST_NAME and LAST_NAME colu mns of the EMPLOYEES table. Which SQL statement will accomplish this tas ? Mar for Review (1) Points CREATE INDEX fl_idx ON employees(first_name || last_name); CREATE INDEX fl_idx ON employees(first_name), employees(last_name); CREATE INDEX fl_idx ON employees(first_name,last_name); (*) CREATE INDEX fl_idx ON employees(first_name); CREATE INDEX fl_idx ON employees(last_name);

Incorrect. Refer to Section 12 Lesson 2.

Section 13 36. When granting an object privilege, which option would you include to allo w the grantee to grant the privilege to another user? Mar for Review (1) Points WITH GRANT OPTION (*) WITH ADMIN OPTION PUBLIC FORCE Incorrect. Refer to Section 13 Lesson 2. 37. Which of the following privileges must be assigned to a user account in o rder for that user to connect to an Oracle database? Mar for Review (1) Points ALTER SESSION CREATE SESSION (*) OPEN SESSION

RESTRICTED SESSION Incorrect. Refer to Section 13 Lesson 1.

Section 15 38. The CUSTOMERS and SALES tables contain these columns: CUSTOMERS CUST_ID NUMBER(10) PRIMARY KEY COMPANY VARCHAR2(30) LOCATION VARCHAR2(20) SALES SALES_ID NUMBER(5) PRIMARY KEY CUST_ID NUMBER(10) FOREIGN KEY TOTAL_SALES NUMBER(30) Which SELECT statement will return the customer ID, the company and the total sa les? Mar for Review (1) Points SELECT c.cust_id, c.company, s.total_sales FROM customers c, sales s WHERE c.cust_id = s.cust_id (+); SELECT cust_id, company, total_sales FROM customers, sales WHERE cust_id = cust_id; SELECT c.cust_id, c.company, s.total_sales FROM customers c, sales s WHERE c.cust_id = s.cust_id; (*) SELECT cust_id, company, total_sales FROM customers c, sales s WHERE c.cust_id = s.cust_id;

Page 4 of 4

Incorrect. Refer to Section 15 Lesson 1.