Question 1. When could an index decrease the speed of a query? A. The table is small. B. The column is used in a WHERE clause. C. The column contains a
... [Show More] wide range of values. D. The column contain s a large number of null values. Ans: A Question 2. What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints? A. ALTER TABLE table_name DROP PRIMARY KEY CASCADE; B. ALTER TABLE table_name REMOVE CONSTRAINT PRIMARY KEY CASCADE; C. ALTER TABLE table_name DISABLE CONSTRAINT PRIMARY KEY CASCADE; D. A PRIMARY KEY constraint CANNOT be removed if it has dependent constraints. Ans: A Question 3. You need to add a NOT NULL constraint to the QUANTITY column in the PO_DETAIL table. Which statement should you use to complete this task? A. ALTER TABLE po_detail MODIFY (quantity NOT NULL); B. ALTER TABLE po_detail MODIFY quantity CONSTRAINT NOT NULL; C. ALTER TABLE po_detail ADD CONSTRAINT quantity_nn NOT NULL(quantity); D. ALTER TABLE po_detail ADD CONSTRAINT NOT NULL (quantity); Ans: A Question 4. Which clause could you use to limit the price values to 100.00 or less? A. CONSTRAINT inventory_price_ck CHECK (price < 100.00) B. CONSTRAINT CHECK inventory_price_ck (price < 100.00) C. CONSTRAINT inventory_price_ck CHECK (price <= 100.00) D. CONSTRAINT CHECK inventory_price_ck (price <= 100.00) E. CONSTRAINT inventory_price_ck CHECK (price IN (100.00)) Ans: CExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 2 of 2 Question 5. Evaluate this CREATE TABLE statement: 1. CREATE TABLE order*1 ( 2. order# NUMBER(9), 3. cust_id NUMBER(9), 4. date_1 DATE DEFAULT SYSDATE); Which line of this statement will cause an error? A. 1 B. 2 C. 3 D. 4 Ans: A Question 6. Which privilege do you need for database access? A. ALTER USER B. CREATE USER C. ALTER SYSTEM D. ALTER SESSION E. CREATE SESSION Ans: E Question 7. Which system privilege may be granted to a role? A. ALTER B. EXECUTE C. REFERENCES D. BACKUP ANY TABLE Ans: DExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 3 of 3 Question 8. You need to create user Marcia and allow this user to create and drop tables in any schema. She should be able to create procedures and sequences only in her schema. Which script should you use to achieve these results? A. CREATE USER marcia IDENTIFIED BY sue123; GRANT DROP ANY TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO marcia / B. CREATE USER marcia IDENTIFIED BY sue123; GRANT CREATE TABLE, DROP TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO marcia / C. CREATE USER marcia IDENTIFIED BY sue123; GRANT CREATE SESSION, DROP ANY TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO marcia / D. CREATE USER marcia IDENTIFIED BY sue123; GRANT CREATE SESSION, CREATE ANY TABLE, DROP ANY TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO marcia / Ans: D Question 9. Evaluate this SQL script: CREATE USER hr IDENTIFIED BY hr01; CREATE ROLE hr_director; GRANT hr_director TO hr; GRANT SELECT ON teacher TO hr_director; CREATE OR REPLACE ROLE hr_director / How many users are granted the HR_DIRECTOR role, and how many privileges are granted to the HR_DIRECTOR role? A. 1 user and 1 privilege B. 1 user and no privileges C. no users and 1 privilege D. no users and no privileges Ans: AExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 4 of 4 Question 10. User Dave cannot remember his password and would like to create a new one: 'dave500'. He is not currently connected to the Oracle Server. User Dave has only been granted the CREATE SESSION system privilege. Which statement should he execute? A. ALTER USER dave NEW PASSWORD dave500; B. ALTER USER dave IDENTIFIED BY dave500; C. ALTER USER dave IDENTIFIED BY PASSWORD dave500; D. ALTER USER dave IDENTIFIED BY NEW PASSWORD dave500; E. User Dave CANNOT change his password. Ans: E Question 11. The STUDENT table contains these columns: ID NUMBER(9) Primary Key LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) ENROLL_DATE DATE You created a report that displays the column headings and data results in this format: Student Name Enrolled ------------------------- ----------------- Jones, Jennifer 12-SEP-01 Which two statements concerning this report's SELECT statement are true? (Choose two.) A. The display lengths of the LAST_NAME and FIRST_NAME columns are increased. B. Separate column aliases are used for the LAST_NAME, FIRST_NAME, and ENROLL_DATE columns. C. The LAST_NAME and FIRST_NAME columns are concatenated together. D. One column alias is used for the LAST_NAME and FIRST_NAME columns. E. A format mask must have been used on the ENROLL_DATE column. Ans: C & DExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 5 of 5 Question 12. 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 they are responsible for teaching? A. SELECT last_name, first_name, subject_id FROM teacher; B. SELECT last_name, first_name, id FROM teacher; C. SELECT * FROM teacher; D. SELECT last_name, subject_id FROM teacher; Ans: A Question 13. The STUDENT table contains these columns: LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) EMAIL VARCHAR2(50) You are writing a SELECT statement to retrieve the names of students that do NOT have an email address. SELECT last_name||' , ' ||first_name "Student Name" FROM student Which WHERE clause should you use to complete this statement? A. WHERE email = NULL; B. WHERE email != NULL; C. WHERE email IS NULL; D. WHERE email IS NOT NULL; Ans: CExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 6 of 6 Question 14. You query the database with this SQL statement: SELECT * FROM transaction; For which purpose was this statement created? A. to view the data in the TRANSACTION table B. to insert data into the TRANSACTION table C. to review the structure of the TRANSACTION table D. to delete selected data from the TRANSACTION table Ans: A Question 15. Which statements pertaining to SQL and iSQL*Plus are true? (Choose all that apply.) A. SQL and iSQL*Plus are command languages. B. iSQL*Plus can format query results. C. SQL includes a continuation character. D. iSQL*Plus can send SQL statements to the server. E. SQL runs on a browser. Ans: B & D Question 16. Which Oracle product contains its own command language and can be used to create script files? A. iSQL*Plus B. SQL C. PL/SQL D. JAVA*SQL? Ans: AExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 7 of 7 Question 17. The EMPLOYEE_HIST table contains these columns: EMPLOYEE_ID NUMBER Primary Key LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) DEPARTMENT_ID NUMBER(3) POSITION VARCHAR2(30) MANAGER_ID NUMBER SALARY NUMBER(6,2) Evaluate this statement: SELECT DISTINCT department_id, manager_id FROM employee_hist; Which statement is true? A. The query will fail because the DISTINCT keyword may only be used in a single column SELECT list. B. A particular MANAGER_ID can be displayed more than once. C. A particular DEPARTMENT_ID can only be displayed once. D. A unique combination of MANAGER_ID and DEPARTMENT_ID may displayed more than once. Ans: B Question 18. 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 ACCOUNT table records. Which query should you use? A. SELECT * FROM account; B. SELECT account_id, new_balance, prev_balance, finance_charge FROM account; C. SELECT all FROM account; C. SELECT any FROM account; Ans: AExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 8 of 8 Question 19. Examine the structure of the LINE_ITEM table as shown below: LINE_ITEM_ID NUMBER(9) NOT NULL, Primary Key ORDER_ID NUMBER(9) NOT NULL, Primary Key, Foreign Key to ORDER_ID column of the CURR_ORDER table PRODUCT_ID NUMBER(9) NOT NULL, Foreign Key to PRODUCT_ID column of the PRODUCT table QUANTITY NUMBER(9) You query the database with this SQL statement: SELECT order_id||'-' ||line_item_id||' ' ||product_id||' ' ||quantity "Purchase" FROM line_item; Which component of the SELECT statement is a literal? A. - B. || C. Purchase D. quantity Ans: A Question 20. 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 .9 percent. Payments must be deducted from this amount. The customer' s account number is 543842. Which SELECT statement will you use? A. SELECT account_id, new_purchases + (prev_balance * .009) - payments FROM account WHERE account_id = 543842; B. SELECT new_balance + finance_charge - payments FROM account WHERE account_id = 543842; C. SELECT account_id, new_purchases + prev_balance * 1.009 - payments FROM account WHERE account_id = 543842; D. SELECT account_id, new_purchases + (prev_balance * 1.009) + finance_charge - payments FROM account WHERE account_id = 543842; Ans: CExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 9 of 9 Question 21. The ACCOUNT table contains these columns: ACCOUNT_ID NUMBER(12) FINANCE_CHARGE NUMBER(7,2) PREV_BALANCE NUMBER(7,2) PAYMENTS NUMBER(7,2) NEW_PURCHASES NUMBER(7,2) You created the ACCOUNT_ID_SEQ sequence to generate sequential values for the ACCOUNT_ID column. You issue this statement: ALTER TABLE account MODIFY (finance_charge NUMBER(8,2)); Which statement about the ACCOUNT_ID_SEQ sequence is true? A. The sequence is dropped. B. The precision of the sequence is changed. C. The sequence is reverted to its minimum value. D. The sequence is unchanged. Ans: D Question 22. Which SELECT statement will display the next value of the PARTS_ID_SEQ sequence by actually retrieving the value from the sequence? A. SELECT NEXTVAL(parts_id_seq) FROM SYS.DUAL; B. SELECT parts_id_seq.NEXTVAL FROM inventory; C. SELECT parts_id_seq.NEXTVAL FROM SYS.DUAL; D. SELECT NEXTVAL(parts_id_seq) FROM inventory; E. SELECT parts_id_seq NEXTVAL FROM inventory; Ans: CExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 10 of 10 Question 23. You issue this statement: CREATE PUBLIC SYNONYM part FOR linda.product; Which task was accomplished by this statement? A. A new segment object was created. B. A new object privilege was assigned. C. A new system privilege was assigned. D. The need to qualify an object name with its schema was eliminated. Ans: D Question 24. User Mark wants to eliminate the need to type the full table name when querying the TRANSACTION_HISTORY table existing in her schema. All other database users should use the schema and full table name when referencing this table. Which statement should user Marilyn execute? A. CREATE PUBLIC SYNONYM trans_hist FOR mark; B. CREATE SYNONYM trans_hist FOR transaction_history; C. CREATE PRIVATE SYNONYM trans_hist FOR mark.transaction_history; D. CREATE PUBLIC trans_hist SYNONYM FOR mark.transaction_history; Ans: B Question 25. The TEACHER table in your schema contains these columns: ID NUMBER(9) NOT NULL, Primary Key LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) SUBJECT_ID NUMBER(9) You execute this statement: CREATE INDEX teacher_name_idx ON teacher(first_name, last_name); Which statement is true? A. The statement creates a composite non-unique index. B. The statement creates a composite unique index. C. You must have the CREATE ANY INDEX privilege for the statement to succeed. D. The statement will fail because it contains a syntax error. Ans: AExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 11 of 11 Question 26. Examine the structure of the CURR_ORDER table as shown below: CURR_ORDER Table ORDER_ID NUMBER NOT NULL, Primary Key CUSTOMER_ID NUMBER NOT NULL Foreign Key to CUSTOMER_ID column of the CUSTOMERS table EMP_ID NUMBER NOT NULL, Foreign Key to EMP_ID column of the EMPLOYEES table ORDER_DT DATE NOT NULL ORDER_AMT NUMBER (7,2) SHIP_METHOD VARCHAR2 (5) You created the ORDER_V view selecting all rows and columns from the ORDER table where the amount of the order was over $250 and the date of the order was after January 1, 2000. The CREATE VIEW statement included the WITH CHECK OPTION clause. Which statement will execute successfully? A. INSERT INTO order_v (order_id, customer_id, emp_id, order_dt, order_amt) VALUES (840, 292, 104, ' 10-OCT-2001' , 318); B. INSERT INTO order_v (order_id, customer_id, emp_id, order_amt, ship_method) VALUES (936, 292, 104, 256.3, ' UPXS' ); C. INSERT INTO order_v (order_id, customer_id, emp_id, order_dt, order_amt, ship_method) VALUES (164, 292, 104, ' 10-MAY-2001' , 3.56, ' UPXS' ); D. INSERT INTO order_v (order_id, customer_id, emp_id, order_dt, order_amt, ship_method) VALUES (203, 292, 104, ' 10-OCT-1999' , 298.4, ' UPXS' ); Ans: A Question 27. An inline view is a SELECT statement that is given an alias and is embedded in the ______ clause of another SELECT statement. A. FROM B. WHERE C. SELECT D. CASE Ans: AExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 12 of 12 Question 28. Examine the structures of the EMPLOYEE and CURR_ORDER tables as shown below: EMPLOYEE Table EMPLOYEE_ID NUMBER NOT NULL, Primary Key LAST_NAME VARCHAR2 (25) FIRST NAME VARCHAR2 (25) DEPT_ID NUMBER Foreign key to DEPT_ID column of the DEPARTMENTS table JOB_ID NUMBER Foreign key to JOB_ID column of the JOBS table MGR_ID NUMBER References EMPLOYEE_ID column SALARY NUMBER (9,2) COMMISSION NUMBER (7,2) HIRE_DATE DATE CURR_ORDER Table ORDER_ID NUMBER NOT NULL, Primary Key CUSTOMER_ID NUMBER Foreign key to CUSTOMER_ID column of the CUSTOMERS table EMPLOYEE_ID NUMBER Foreign key to EMP_ID column of the EMPLOYEES table ORDER_DATE DATE ORDER_AMT NUMBER (7,2) SHIP_METHOD NUMBER (5) You queried the database with this SQL statement: SELECT a.last_name, a.first_name, a.job_id, NVL(a.commission, 0), b.avgcomm FROM employee a, (SELECT job_id, AVG(commission) AVGCOMM FROM employee WHERE commission IS NOT NULL GROUP BY job_id) b WHERE a.job_id = b.job_id AND a.commission < b.avgcomm; Which is a result of this query? A. The AVG function' s DISTINCT keyword must be used in the inner query or the statement will fail when executed. B. The employee information displayed will be for employees that have a commission that is less than the average commission of all employees who have the same job. C. The employee information displayed will be displayed in numeric order by the JOB_ID and in alphabetical order by the LAST_NAME where the JOB_IDs are the same. D. A self join CANNOT be used in an outer query when the inner query is an inline view. The self join must be placed in the inner query for the statement to execute successfully. Ans: BExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 13 of 13 Question 29. Examine the structures of the EMPLOYEE and CURR_ORDER tables. EMPLOYEE Table EMPLOYEE_ID NUMBER NOT NULL, Primary Key LAST_NAME VARCHAR2 (25) FIRST NAME VARCHAR2 (25) DEPT_ID NUMBER Foreign key to DEPT_ID column of the DEPARTMENTS table JOB_ID NUMBER \ Foreign key to JOB_ID column of the JOBS table MGR_ID NUMBER References EMPLOYEE_ID column SALARY NUMBER (9,2) COMMISSION NUMBER (7,2) HIRE_DATE DATE CURR_ORDER Table ORDER_ID NUMBER NOT NULL, Primary Key CUSTOMER_ID NUMBER Foreign key to CUSTOMER_ID column of the CUSTOMERS table EMPLOYEE_ID NUMBER Foreign key to EMP_ID column of the EMPLOYEES table ORDER_DATE DATE ORDER_AMT NUMBER (7,2) SHIP_METHOD NUMBER (5) To keep your top sales representatives motivated, your company plans to increase the bonuses of employees. You need to create a SELECT statement that returns the name, bonus, and maximum order amount associated with each employee for all employees whose bonus is less than 8 percent of their maximum order amount. Which SELECT statement should you use? A. SELECT e.last_name, e.first_name, e.commission, o.maxamt FROM employee e, (SELECT employee_id, MAX(order_amt) MAXAMT FROM curr_order GROUP BY employee_id) o WHERE e.employee_id = o.employee_id AND e.commission < .08 * o.maxamt; B. SELECT e.last_name, e.first_name, e.commission, MAX(o.order_amount) MAXAMT FROM employee e, curr_order o WHERE e.employee_id = o.employee_id AND e.commission < .08 * o.maxamt GROUP BY e.last_name, e.first_name, e.commission; C. SELECT e.last_name, e.first_name, e.commission, MAX(o.order_amt) FROM employee e, curr_order WHERE e.employee_id = o.employee_id AND e.commission < .08 * (SELECT employee_id, MAX(order_amt) MAXAMT FROM curr_order GROUP BY employee_id); D. SELECT last_name, first_name, commission, max_amt FROM employee, (SELECT employee_id, MAX(order_amt) MAXAMT FROM curr_order GROUP BY employee_id) WHERE employee.employee_id = curr_order.employee_id AND commission < .08 * maxamt; Ans: AExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 14 of 14 Question 30. Which statements concerning the creation of a view are true? (Choose all that apply.) A. A constraint name must be provided when using the WITH CHECK OPTION clause or the statement will fail. B. View columns that are the result of derived values must be given a column alias. C. When the view already exists, using the OR REPLACE option requires the re-granting of the object privileges previously granted on the view. D. A view may have column names that are different than the actual base table(s) column names by using column aliases. Ans: B & D Question 31. You need to create the CURRENTPR table. The table must meet these requirements: 1. The table must contain the EMPLOYEE_ID and HRS_WORKED columns for numeric data. 2. The table must contain the PAY_DATE column for date values. 3. The table must contain the PAY_RATE and PAY_AMT columns for numeric data with precision and scale of 5,2 and 10,2 respectively. 4. The table must have a composite primary key on the EMPLOYEE_ID and PAY_RATE columns. Which CREATE TABLE statement will satisfy these requirements? A. CREATE TABLE currentpr ( employee_id NUMBER CONSTRAINT employee_id_pk PRIMARY KEY(employee_id), pay_date DATE CONSTRAINT pay_date_pk PRIMARY KEY(pay_date), hrs_worked NUMBER, pay_rate NUMBER(5,2), pay_amt NUMBER(10,2)); B. CREATE TABLE currentpr ( employee_id NUMBER CONSTRAINT currentpr_pk PRIMARY KEY(employee_id, pay_date), pay_date DATE, hrs_worked NUMBER, pay_rate NUMBER(5,2), pay_amt NUMBER(10,2)); C. CREATE TABLE currentpr ( employee_id NUMBER, pay_date DATE, hrs_worked NUMBER, pay_rate NUMBER(5,2), pay_amt NUMBER(10,2), CONSTRAINT employee_id_pk PRIMARY KEY(employee_id), CONSTRAINT pay_date_pk PRIMARY KEY(pay_date)); D. CREATE TABLE currentpr ( employee_id NUMBER, pay_date DATE, hrs_worked NUMBER, pay_rate NUMBER(5,2), pay_amt NUMBER(10,2), CONSTRAINT currentpr_pk PRIMARY KEY(employee_id, pay_date)); Ans: DExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 15 of 15 Question 32. For which two types of constraints will a unique index be automatically created? (Choose two.) A. UNIQUE B. PRIMARY KEY C. NOT NULL D. FOREIGN KEY E. CHECK Ans: A & B Question 33. You disabled the PRIMARY KEY constraint on the ID column in the INVENTORY table and updated all the values in the INVENTORY table. You need to enable the constraint and verify that the new ID column values do not violate the constraint. If any of the ID column values do not conform to the constraint, an error message should be returned. valuate this statement: ALTER TABLE inventory ENABLE CONSTRAINT inventory_id_pk; Which statement is true? A. The statement will achieve the desired results. B. The statement will execute, but will NOT enable the PRIMARY KEY constraint. C. The statement will execute, but will NOT verify that values in the ID column do NOT violate the constraint. D. The statement will return a syntax error. Ans: AExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 16 of 16 Question 34. Examine the structures of the PRODUCT and SUPPLIER tables. PRODUCT Table PRODUCT_ID NUMBER NOT NULL, Primary Key PRODUCT_NAME NUMBER (25) SUPPLIER_ID NUMBER Foreign key to SUPPLIER_ID of the SUPPLIER table LIST_PRICE NUMBER (7,2) COST NUMBER (7,2) QTY_IN_STOCK NUMBER QTY_ON_ORDER NUMBER REORDER_LEVEL NUMBER REORDER_QTY NUMBER SUPPLIER Table SUPPLIER_ID NUMBER NOT NULL, Primary Key SUPPLIER_NAME VARCHAR2 (25) ADDRESS VARCHAR2 (30) CITY VARCHAR2 (25) PEGION VARCHAR2 (10) POSTAL_CODE VARCHAR2 (11) Evaluate this statement: ALTER TABLE product DISABLE CONSTRAINT supplier_id_fk; For which task would you issue this statement? A. to drop the FOREIGN KEY constraint on the PRODUCT table B. to remove any constraint references on the SUPPLIER_ID column in the PRODUCT table C. to disable any dependent integrity constraints on the SUPPLIER_ID column in the PRODUCT table D. to deactivate the FOREIGN KEY constraint on the PRODUCT table Ans: D Question 35. Which four statements about Oracle constraints are true? (Choose four.) A. A UNIQUE constraint specifies a column or combination of columns whose values must be unique for all rows in a table. B. A CHECK constraint specifies a condition that must be true. C. A PRIMARY KEY constraint uniquely identifies each row of a table. D. A NOT NULL constraint ensures that null values are NOT allowed in a column. E. A UNIQUE constraint prohibits the input of nulls because nulls do NOT satisfy the constraint conditions. F. A PRIMARY KEY constraint allows null values in a column when the column is part of a set of columns that uniquely identifies each row. Ans: A, B, C & DExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 17 of 17 Question 36. Which statement about a table is true? A. A table can have up to 10,000 columns. B. The size of a table does NOT need to be specified. C. A table CANNOT be created while users are using the database. D. The structure of a table CANNOT be modified while the table is online. Ans: B Question 37. Examine the structure of the EMPLOYEE table. EMPLOYEE Table EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_LNAME VARCHAR2 (25) EMP_FNAME VARCHAR2 (25) DEPT_ID NUMBER Foreign key to DEPT_ID column of the DEPARTMENT table JOB_ID NUMBER Foreign key to JOB_ID column of the JOB table MGR_ID NUMBER References EMPLOYEE_ID column SALARY NUMBER (9,2) HIRE_DATE DATE DPB DATE Which statement should you use to increase the EMP_LNAME column length to 25 if the column currently contains 3000 records? A. You CANNOT increase the width of the EMP_LNAME column. B. ALTER TABLE employee MODIFY emp_lname VARCHAR2(25); C. ALTER TABLE employee RENAME emp_lname VARCHAR2(25); D. ALTER employee TABLE MODIFY COLUMN emp_lname VARCHAR2(25); E. ALTER employee TABLE MODIFY COLUMN (emp_lname VARCHAR2(25)); Ans: BExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 18 of 18 Question 38. Examine the structure of the PO_DETAIL table. PO_DETAIL Table PO-NUM PO_LINE IN-PRODUCT ID-PRODUCT UNIT_PRICE 10052 1 1 100 10.30 10052 2 2 100 10.00 10054 1 1 50 72.00 10054 2 2 10 10.00 10054 3 3 10 10.00 Which statement will permanently remove all the data in, the indexes on, and the structure of the PO_DETAIL table? A. DELETE TABLE po_detail; B. TRUNCATE TABLE po_detail; C. ALTER TABLE po_detail SET UNUSED (po_num, po_line_id, product_id, quantity, unit_price); D. DROP TABLE po_detail; Ans: D Question 39. Examine the structure of the EMPLOYEE table. EMPLOYEE Table EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_LNAME VARCHAR2 (25) EMP_FNAME VARCHAR2 (25) DEPT_ID NUMBER Foreign key to DEPT_ID column of the DEPARTMENT table JOB_ID NUMBER Foreign key to JOB_ID column of the JOB table MGR_ID NUMBER References EMPLOYEE_ID column SALARY NUMBER (9,2) HIRE_DATE DATE DPB DATE The MGR_ID column currently contains employee identification numbers, and you need to allow users to include text characters in the identification values. Which statement should you use to implement this? A. ALTER employee MODIFY (mgr_id VARCHAR2(15)); B. ALTER TABLE employee MODIFY (mgr_id VARCHAR2(15)); C. ALTER employee TABLE MODIFY COLUMN (mgr_id VARCHAR2(15)); D. ALTER TABLE employee REPLACE (mgr_id VARCHAR2(15)); E. You CANNOT modify the data type of the MGR_ID column. Ans: EExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 19 of 19 Question 40. Which CREATE TABLE statements will fail? (Choose all that apply.) A. CREATE TABLE time1 (time1 NUMBER(9)); B. CREATE TABLE date (time_id NUMBER(9)); C. CREATE TABLE time (time_id NUMBER(9)); D. CREATE TABLE time* (time_id NUMBER(9)); E. CREATE TABLE $time (time_id NUMBER(9)); F. CREATE TABLE datetime (time_id NUMBER(9)); Ans: B, D & E Question 41. Evaluate this statement: DELETE FROM workorder; What does this statement accomplish? A. discards only the structure of the WORKORDER table B. deletes the WORKORDER column C. deletes all the rows from the WORKORDER table D. deletes all the values in the columns that do NOT have NOT NULL constraints E. generates an error because the FROM keyword should NOT be included F. deletes all rows from the WORKORDER table and permanently discards the table' s structure Ans: C Question 42. The PRODUCT table contains these columns: PRODUCT_ID NUMBER PK NAME VARCHAR2(30) LIST_PRICE NUMBER(7,2) COST NUMBER(7,2) You logged on to the database to update the PRODUCT table. After your session began, you issued these statements: INSERT INTO product VALUES(4,' Ceiling Fan' ,59.99, 32.45); INSERT INTO product VALUES(5,' Ceiling Fan' ,69.99,37.20); SAVEPOINT A; UPDATE product SET cost = 0; SAVEPOINT B; DELETE FROM product WHERE UPPER(name) = ' CEILING FAN' ; ALTER TABLE product ADD qoh NUMBER DEFAULT 10; ROLLBACK TO B; UPDATE product SET name = ' CEILING FAN KIT' WHERE product_id = 4; Then you exit iSQL*Plus. Which of the statements you issued were committed? A. only the INSERT statements B. only the INSERT statements and the first UPDATE statement C. the INSERT statements, the first UPDATE statement, and the DELETE statement D. all of the DML operations E. none of the DML operations Ans: DExam Name: Exam Type: Exam Code: Introduction to Oracle9i: SQL Oracle 1Z0-007 Total Questions: 178 Page 20 of 20 Question 43. Which statement type would be used to remove transactions more than one year old from the TRX table? A. TCL B. DCL C. DDL D. DML E. DRL Ans: D Question 44. Which two DML statements could you use to modify the contents of the PRODUCT_NAME column of the existing PRODUCT table? (Choose two.) A. INSERT B. MODIFY C. UPDATE D. COMMIT E. ALTER F. MERGE [Show Less]