George Washington University CS 6441 ( QUESTIONS WITH 100% CORRECT ANSWERS AND EXPLANATIONS) 226 PAGES

Document Content and Description Below

Click the Exhibit(s) button to examine the structure of the PRODUCT table. Evaluate this SQL statement: SELECT supplier_id, AVG(cost) FROM product WHERE AVG(list_price) > 60.00 GROUP BY supplier_... id ORDER BY AVG(cost) DESC; Which clause will cause an error? SELECT WHERE GROUP BY ORDER BY Answer: WHEREExplanation: The WHERE clause will cause an error when this SELECT statement is executed. Groups can only be restricted with a HAVING clause. Including a group, or aggregate, function in a WHERE clause is invalid. All of the other clauses are correctly specified and do not generate errors. Objective: Include or exclude grouped rows by using the HAVING clause Sub-Objective: Aggregating Data using Group Functions References: 1. Introduction to Oracle9i: SQL - Aggregating Data Using Group FunctionsItem: 2 (Ref:1z0-007e.8.5.4) Click the Exhibit(s) button to examine the data from the AR_TRX and AR_TRX_HY tables. You have been granted SELECT privileges on the AR_TRX_HY table and INSERT, UPDATE, and SELECT privileges on the AR_TRX table. You execute this statement: MERGE INTO ar_trx a USING ar_trx_hy h ON (a.trx_id = h.trx_id) WHEN MATCHED THEN UPDATE SET a.quantity = h.quantity, a.unit_price = h.unit_price, a.ext_amt = h.ext_amt, a.tax_amt = h.tax_amt WHEN NOT MATCHED THEN INSERT (trx_id, trx_type, quantity, unit_price, ext_amt, tax_amt) VALUES(h.trx_id, h.trx_type, h.quantity, h.unit_price, h.ext_amt, h.tax_amt); What is the result? Rows are inserted into the AR_TRX table. Rows are inserted into the AR_TRX_HY table. One or more rows are updated in the AR_TRX table. One or more rows are updated in the AR_TRX_HY table. An error occurs because you do NOT have MERGE privileges on the AR_TRX table. An error occurs because there are no rows in the second table that are not in the first table. Answer: One or more rows are updated in the AR_TRX table.Explanation: When executing the given MERGE statement, the records in the AR_TRX_HY table are merged into the AR_TRX table. The AR_TRX table contains one row with a TRX_ID value of 2. A record already exists in the AR_TRX_HY table with this value for TRX_ID, so no inserts occur. The MERGE statement only updates the QUANTITY, UNIT_PRICE, EXT_AMT, and TAX_AMT values in the AR_TRX table for the record with TRX_ID equal to 2. Because there are no rows in the AR_TRX_HY table that do not already exist in the AR_TRX table, no rows are inserted into the AR_TRX table. The options stating that rows are inserted into or updated in the AR_TRX_HY table are incorrect because the AR_TRX_HY table is not the target table of the MERGE statement. The option stating that an error occurs because you do not have MERGE privileges on the AR_TRX table is incorrect because there is not a MERGE object privilege. To successfully execute a merge, the user must have SELECT privileges on the source table and INSERT and UPDATE privileges on the target table. The option stating that an error occurs because there are no rows in the second table that are not in the first table is also incorrect. The statement will execute regardless of whether the INSERT or UPDATE will process rows. If either of the clauses has no rows to process, that portion of the MERGE statement simply will not execute. Valid MERGE statement syntax is: MERGE INTO target_table AS table_alias USING (source_table | source_view | source_subquery) AS alias ON (join condition) WHEN MATCHED THEN UPDATE SET target_col1 = value,target_col2 = value WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values); Objective: Merge rows in a table Sub-Objective: Manipulating Data References: 1. Introduction to Oracle9i: SQL - Manipulating DataItem: 3 (Ref:1z0-007e.4.2.7) Click the Exhibit(s) button to examine the structures of the EMPLOYEE and TASK tables. You need to produce a report containing all employees and all tasks. An employee must be included on the report even if he has no tasks assigned. And all tasks, whether assigned to an employee or not, must also be included on the report. Which SELECT statement should you use? SELECT e.emp_lname, e.emp_fname, t.task_description, t.est_compl_date FROM employee e, task t WHERE e.employee_id = t.employee_id; SELECT e.emp_lname, e.emp_fname, t.task_description, t.est_compl_date FROM employee e, task t WHERE e.employee_id (+) = t.employee_id; SELECT e.emp_lname, e.emp_fname, t.task_description, t.est_compl_date FROM employee e, task t WHERE e.employee_id = t.employee_id (+); SELECT e.emp_lname, e.emp_fname, t.task_description, t.est_compl_date FROM employee e, task t WHERE e.employee_id (+) = t.employee_id (+); None of the options will produce the desired result. Answer: None of the options will produce the desired result.Explanation: For the given scenario, none of the options will produce the desired result. Because you needed to include all rows from both tables, a full outer join must be used, and none of the given options correctly implement a full outer join. Outer joins may be created in one of two ways. First, you can create a full outer join using the SQL: 1999 syntax or use the FULL OUTER JOIN syntax as in this SELECT statement: SELECT e.emp_lname, e.emp_fname, t.task_description, t.est_compl_date FROM employee e FULL OUTER JOIN task t ON (e.employee_id = t.employee_id ); Using Oracle proprietary syntax, you cannot include the outer join operator, (+), on both sides of the join condition. To implement an outer join you must use two SELECT statements, one performing a left outer join and the other performing a right outer join, and combine the results of these SELECT statements using the UNION operator. For example: SELECT e.emp_lname, e.emp_fname, t.task_description, t.est_compl_date FROM employee e, task t WHERE e.employee_id (+) = t.employee_id UNION SELECT e.emp_lname, e.emp_fname, t.task_description, t.est_compl_date FROM employee e, task t WHERE e.employee_id = t.employee_id (+);The option including 'WHERE e.employee_id = t.employee_id' as the join condition is incorrect because this implements an equijoin, or inner join, and will only return matching rows from the two tables. Both of the options that use the outer join operator on one side of the join condition are incorrect. Neither would return unmatched rows from both tables. The option that includes the outer join operator on both sides of the join condition is also incorrect because, as stated earlier, it is not valid to use the outer join operator on both sides of a join condition. Objective: View data that generally does not meet a join condition by using outer joins Sub-Objective: Displaying Data from Multiple Tables References: 1. Introduction to Oracle9i: SQL - Displaying Data from Multiple TablesItem: 4 (Ref:1z0-007e.13.2.1) Which privilege can only be granted to a user and NOT to a role? ALTER DELETE INSERT EXECUTE REFERENCES Answer: REFERENCES Explanation: The REFERENCES object privilege allows a user to create FOREIGN KEY constraints that reference the specified object. This privilege can be granted to a user but not to a role. The remaining options can be granted to both a user and to a role. The ALTER object privilege can be granted on a table or a sequence and allows the user to modify the structure of the object. The DELETE object privilege can be granted on a table and allows the user to delete data from a table. The DROP privilege allows a user to delete a table, a view, or a procedure. The INSERT object privilege can only be granted on a table and allows a user to insert data into a table. The EXECUTE object privilege can only be granted on a procedure and allows a user to execute the specified procedure. Objective: Create Roles to ease setup and maintenance of the security model Sub-Objective: Controlling User Access References: 1. Introduction to Oracle9i: SQL - Controlling User AccessItem: 5 (Ref:1z0-007e.5.1.2) You want to produce a report containing the total number of orders placed for a particular time period, including the total value of the orders. Which two aggregate functions should you use in your SQL statement? (Choose two.) SUM STOT TSUM VALUE COUNT STDDEV Answer: SUM COUNT Explanation: To produce a report of the total number of orders placed for a particular time period, including the total value of the orders, you should use the COUNT and SUM group functions. The COUNT function should be used to calculate the total number of orders placed, and the SUM function should be used to calculate the total value of the orders. STOT, TSUM, and VALUE are not valid group functions. Therefore, these options are incorrect. While STDDEV is a valid group function, it will not return the desired result. The STDDEV function returns the statistical standard deviation of all values in a given group. Objective: Identify the available group functions Sub-Objective: Aggregating Data using Group FunctionsReferences: 1. Introduction to Oracle9i: SQL - Aggregating Data Using Group FunctionsItem: 6 (Ref:1z0-007e.8.5.1) Click the Exhibit(s) button to examine the structures of the AR_TRX and AR_TRX_HY tables. You are loading historical accounts receivable data from the AR_TRX table into the AR_TRX_HY table. During the load, you want to transform the data so that the GRAND_TOTAL column of the AR_TRX_HY table is equal to the sum of the EXT_AMT and TAX_AMT columns in the AR_TRX table. You want to set LOAD_DATE to the current date. If the record already exists in the target table, all values, except TRX_ID and TRX_TYPE, should be refreshed with the most recent data. Which MERGE statement should you execute? MERGE INTO ar_trx_hy h USING ar_trx a ON (h.trx_id = a.trx_id) WHEN MATCHED THEN UPDATE SET a.quantity = h.quantity, a.unit_price = h.unit_price, a.ext_amt = h.ext_amt, a.tax_amt = h.tax_amt, a.grand_total = h.ext_amt + h.tax_amt, a.load_date = sysdate WHEN NOT MATCHED THEN INSERT VALUES(a.trx_id, a.trx_type, a.quantity, a.unit_price, a.ext_amt, a.tax_amt, a.ext_amt + a.tax_amt, sysdate); MERGE INTO ar_trx_hy USING ar_trx USING (trx_id) WHEN MATCHED THEN UPDATE SET quantity = quantity, unit_price = unit_price, ext_amt = ext_amt, tax_amt = tax_amt, grand_total = ext_amt + tax_amt, load_date = sysdate WHEN NOT MATCHED THEN INSERT VALUES(trx_id, trx_type, quantity, unit_price, ext_amt, tax_amt, ext_amt + tax_amt, sysdate);MERGE INTO ar_trx_hy h USING ar_trx a ON (h.trx_id = a.trx_id) WHEN MATCHED THEN UPDATE ar_trx_hy SET h.quantity = a.quantity, h.unit_price = a.unit_price, h.ext_amt = a.ext_amt, h.tax_amt = a.tax_amt, h.grand_total = a.ext_amt + a.tax_amt, h.load_date = sysdate WHEN NOT MATCHED THEN INSERT VALUES(a.trx_id, a.trx_type, a.quantity, a.unit_price, a.ext_amt, a.tax_amt, a.ext_amt + a.tax_amt, sysdate); MERGE INTO ar_trx_hy h USING ar_trx a ON (h.trx_id = a.trx_id) WHEN MATCHED THEN UPDATE SET h.quantity = a.quantity, h.unit_price = a.unit_price, h.ext_amt = a.ext_amt, h.tax_amt = a.tax_amt, h.grand_total = a.ext_amt + a.tax_amt, h.load_date = sysdate WHEN NOT MATCHED THEN INSERT VALUES(a.trx_id, a.trx_type, a.quantity, a.unit_price, a.ext_amt, a.tax_amt, a.ext_amt + a.tax_amt, sysdate); MERGE INTO ar_trx_hy h USING ar_trx a ON (h.trx_id = a.trx_id) WHEN MATCHED UPDATE SET h.quantity = a.quantity, h.unit_price = a.unit_price, h.ext_amt = a.ext_amt, h.tax_amt = a.tax_amt, h.grand_total = a.ext_amt + a.tax_amt, h.load_date = sysdate WHEN NOT MATCHED INSERT VALUES(a.trx_id, a.trx_type, a.quantity, a.unit_price, a.ext_amt, a.tax_amt, a.ext_amt + a.tax_amt, sysdate); MERGE INTO ar_trx_hy h USING ar_trx a ON (h.trx_id = a.trx_id) WHEN EXISTSUPDATE SET h.quantity = a.quantity, h.unit_price = a.unit_price, h.ext_amt = a.ext_amt, h.tax_amt = a.tax_amt, h.grand_total = a.ext_amt + a.tax_amt, h.load_date = sysdate WHEN NOT EXISTS INSERT VALUES(a.trx_id, a.trx_type, a.quantity, a.unit_price, a.ext_amt, a.tax_amt, a.ext_amt + a.tax_amt, sysdate); Answer: MERGE INTO ar_trx_hy h USING ar_trx a ON (h.trx_id = a.trx_id)WHEN MATCHED THEN UPDATE SET h.quantity = a.quantity, h.unit_price = a.unit_price, h.ext_amt = a.ext_amt, h.tax_amt = a.tax_amt, h.grand_total = a.ext_amt + a.tax_amt, h.load_date = sysdateWHEN NOT MATCHED THEN INSERT VALUES(a.trx_id, a.trx_type, a.quantity, a.unit_price, a.ext_amt, a.tax_amt, a.ext_amt + a.tax_amt, sysdate); Explanation: To perform the needed inserts and updates, you should use the MERGE statement that includes an ON clause, specifies values from the AR_TRX_HY table on the left side of the assignment statements in the update, and uses WHEN MATCHED THEN, and WHEN NOT MATCHED THEN clauses. This statement correctly specifies MERGE statement syntax. TheMERGE statement is new to Oracle 9i and combines an insert and an update. In a merge, a source table and target table are specified. If records already exist in the target table, the records are updated, and if records do not exist in the target table, they are inserted. The statement that specifies columns from the AR_TRX table, with alias 'a', on the left side of the assignment statements in the UPDATE clause is incorrect. The AR_TRX_HY table, with alias 'h', is the table specified in the INTO clause and is the table that is updated. The statement that includes two USING clauses is incorrect. The MERGE statement syntax requires the use of an ON clause and table aliases to distinguish the columns in each table. The statement that includes a table name in the UPDATE portion of the statement is incorrect and will generate an error. The table to be updated is implicitly specified in the INTO clause of the MERGE statement and not in the UPDATE clause. The statement that does not include the THEN keyword with the WHEN MATCHED and WHEN NOT MATCHED keywords generates an error. Proper syntax requires the THEN keyword be specified. The statement that uses EXISTS and NOT EXISTS is incorrect and generates an error. Proper syntax requires the WHEN MATCHED THEN and WHEN NOT MATCHED THEN clauses. The MERGE statement is a new DML statement available with Oracle 9i. The MERGE statement is used to conditionally insert or update rows in a table. For rows that already exist in the table, the UPDATE portion of the statement is executed and, for rows that do not exist in the target table, the INSERT portion of the statement is executed. Valid MERGE statement syntax is: MERGE INTO target_table AS table_alias USING (source_table | source_view | source_subquery) AS alias ON (join condition) WHEN MATCHED THEN UPDATE SET target_col1 = value, target_col2 = value WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values); Objective: Merge rows in a table Sub-Objective: Manipulating DataReferences: 1. Introduction to Oracle9i: SQL - Manipulating DataItem: 7 (Ref:1z0-007e.10.2.11) Click the Exhibit(s) button to examine the structure of the PRODUCT table. How many constraints must be explicity created? 0 1 2 3 Answer: 2 Explanation: When creating the PRODUCT table, you must explicitly create two constraints, one PRIMARY KEY constraint on the PRODUCT_ID column and one FOREIGN KEY constraint on the SUPPLIER_ID column. It is not necessary to create a NOT NULL constraint on the PRODUCT_ID column because a column defined as a primary key cannot contain null values. Because two constraints are created, the remaining options are incorrect. Objective: Create and maintain constraintsSub-Objective: Including Constraints References: 1. Introduction to Oracle9i: SQL - Including ConstraintsItem: 8 (Ref:1z0-007e.10.2.4) Evaluate this statement: SELECT * FROM USER_CONS_COLUMNS; Logged on as user Ann, which task would you accomplish using this statement? count the number of UNIQUE constraints in your schema display the names of the constraints on the tables you can access display the columns associated with the constraints on the tables you own determine the type of constraint on the DEPT_ID column of the DEPARTMENT table in the user Chan schema Answer: display the columns associated with the constraints on the tables you own Explanation: The USER_CONS_COLUMNS data dictionary view will display the owner, constraint name, table name, column name, and position of column constraints in your schema. In this scenario, the query will return information about the columns associated with the constraints in user Ann's schema. To count the number of UNIQUE constraints in your schema, query the USER_CONSTRAINTS data dictionary view. To display the names of the constraints on the tables that you can access, query the ALL_CONSTRAINTS or the ALL_CONS_COLUMNS data dictionary views. To determine the type of constraint on the DEPT_ID column of the DEPARTMENT table in the user Chan schema, you must either have access to the table or have SYSDBA privileges. If you have access to the table, you can query the ALL_CONSTRAINTS data dictionary view. If you have SYSDBA privileges, you can query the DBA_CONSTRAINTS data dictionary view.Objective: Create and maintain constraints Sub-Objective: Including Constraints References: 1. Introduction to Oracle9i: SQL - Including ConstraintsItem: 9 (Ref:1z0-007e.5.2.5) For which situation would you use a group function? to display the order date of orders in 'DD MON YYYY' format to convert the character string 'January 28, 2002' to a date format to eliminate duplicate values of the ORDER_ID column of the LINE_ITEM table to produce a total of all the values in the COST column of the PRODUCT table to display all the values in the CATEGORY column of the PRODUCT table in uppercase Answer: to produce a total of all the values in the COST column of the PRODUCT table Explanation: You would use a group function, namely the SUM function, to produce a total of all the values in the COST column of the PRODUCT table. Group functions are used to perform calculations over groups of rows. The SQL statement required to produce such a result is: SELECT SUM(cost) FROM product; To display the order date of orders in 'DD MON YYYY' format, use the TO_CHAR conversion function. To convert the character string 'January 28, 2002' to a date format, use the TO_DATE conversion function. To display values in the CATEGORY column in uppercase, use the UPPER character function. While TO_DATE, TO_CHAR, and UPPER are valid functions, they are considered single-row functions and cannot be used over groups. Single-row functions are used with individual rows. Therefore, these options are incorrect. The option stating that a group function could be used to eliminate duplicate values of the ORDER_ID column is also incorrect. To eliminate duplicates in a result set, use the DISTINCT keyword. Objective:Use group functions Sub-Objective: Aggregating Data using Group Functions References: 1. Introduction to Oracle9i: SQL - Aggregating Data Using Group FunctionsItem: 10 (Ref:1z0-007e.10.2.3) Click the Exhibit(s) button to examine the structure of the EMPLOYEE table. Which CREATE TABLE statement should you use to create the EMPLOYEE table? CREATE TABLE employee ( employee_id NUMBER, emp_lname VARCHAR2(25), emp_fname VARCHAR2(25), dept_id NUMBER, job_id NUMBER, mgr_id NUMBER, salary NUMBER(9,2), hire_date DATE, CONSTRAINT employee_id_pk PRIMARY KEY(employee_id)); CREATE TABLE employee ( employee_id NUMBER, emp_lname VARCHAR2(25), emp_fname VARCHAR2(25), dept_id NUMBER, job_id NUMBER, mgr_id NUMBER, salary NUMBER(9,2), hire_date DATE, CONSTRAINT employee_id_pk PRIMARY KEY(employee_id), CONSTRAINT mgr_id_fk FOREIGN KEY(mgr_id) REFERENCES employee(employee_id)); CREATE TABLE employee ( employee_id NUMBER, emp_lname VARCHAR2(25), emp_fname VARCHAR2(25), dept_id NUMBER, job_id NUMBER, mgr_id NUMBER, salary NUMBER(9,2), hire_date DATE, CONSTRAINT employee_id_pk PRIMARY KEY(employee_id), CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(dept_id), CONSTRAINT job_id_fk FOREIGN KEY(job_id) REFERENCESjob(job_id)); CREATE TABLE employee ( employee_id NUMBER, emp_lname VARCHAR2(25), emp_fname VARCHAR2(25), dept_id NUMBER, job_id NUMBER, mgr_id NUMBER, salary NUMBER(9,2), hire_date DATE, CONSTRAINT employee_id_pk PRIMARY KEY(employee_id), CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(dept_id), CONSTRAINT job_id_fk FOREIGN KEY(job_id) REFERENCES job(job_id), CONSTRAINT mgr_id_fk FOREIGN KEY(mgr_id) REFERENCES employee(employee_id)); Answer: CREATE TABLE employee (employee_id NUMBER,emp_lname VARCHAR2(25),emp_fname VARCHAR2(25),dept_id NUMBER,job_id NUMBER,mgr_id NUMBER,salary NUMBER(9,2),hire_date DATE,CONSTRAINT employee_id_pk PRIMARY KEY(employee_id),CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(dept_id),CONSTRAINT job_id_fk FOREIGN KEY(job_id) REFERENCES job(job_id),CONSTRAINT mgr_id_fk FOREIGN KEY(mgr_id) REFERENCES employee(employee_id)); Explanation:The statement required to create the EMPLOYEE table contains four CONSTRAINT clauses. A PRIMARY KEY constraint and three FOREIGN KEY constraints are required. The clause 'CONSTRAINT employee_id_pk PRIMARY KEY(employee_id)' creates a PRIMARY KEY constraint on the EMPLOYEE_ID column. This PRIMARY KEY constraint enforces uniqueness of the values in the EMPLOYEE_ID column. You do not need to specify a NOT NULL constraint on this column because a PRIMARY KEY constraint ensures that no part of the primary key can contain a null value. The clause 'CONSTRAINT dept_id_fk FOREIGN KEY(dept_id) REFERENCES department(dept_id)' creates a FOREIGN KEY constraint on the DEPT_ID column in the EMPLOYEE table that references the DEPT_ID column in the DEPARTMENT table. The clause 'CONSTRAINT job_id_fk FOREIGN KEY(job_id) REFERENCES job(job_id)' creates a FOREIGN KEY constraint on the JOB_ID column in the EMPLOYEE table that references the JOB_ID column in the DEPARTMENT table. The constraint 'CONSTRAINT mgr_id_fk FOREIGN KEY(mgr_id) REFERENCES employee(employee_id)' creates a FOREIGN KEY constraint on the MGR_ID column in the EMPLOYEE table that references the EMPLOYEE_ID column in the EMPLOYEE table. These FOREIGN KEY constraints ensure that all values in the DEPT_ID, JOB_ID, and MGR_ID columns in the EMPLOYEE table match an existing value in the parent table or have a null value. The statements containing one, two, and three CONSTRAINT clauses are incorrect because they do not satisfy the EMPLOYEE table requirements. These statements do not create all of the necessary constraints. Objective: Create and maintain constraints Sub-Objective: Including Constraints References: 1. Introduction to Oracle9i: SQL - Including ConstraintsItem: 11 (Ref:1z0-007e.5.3.2) Evaluate this SQL statement: SELECT id "Event", SUM(reg_amt) "Registration Amt" FROM event WHERE reg_amt > 1000.00 GROUP BY "Event" ORDER BY 2; Which clause will cause an error? SELECT id "Event", SUM(reg_amt) "Registration Amt" FROM event WHERE reg_amt > 1000.00 GROUP BY "Event" ORDER BY 2; Answer: GROUP BY "Event" Explanation: This SQL statement fails because the GROUP BY "Event" clause uses a column alias to identify a column. A GROUP BY clause must use the actual column name from the SELECT list. All other clauses in the SELECT statement are syntactically correct. Therefore, all other options are incorrect. Objective: Group data using the GROUP BY clause Sub-Objective: Aggregating Data using Group Functions References: 1. Introduction to Oracle9i: SQL - Aggregating Data Using Group FunctionsItem: 12 (Ref:1z0-007e.12.2.5) Why would you NOT create an index on a column in the CLASS_SCHEDULE table? to reduce disk I/O to speed up row retrieval to speed up queries if the table is small to speed up queries that return less than 3 percent of the rows to speed up queries that include a foreign key reference to the STUDENT table Answer: to speed up queries if the table is small Explanation: You would NOT need to create an index on a column in the CLASS_SCHEDULE table if the table is small. For most small tables, full table scans are faster than index searches. Should the index provide any query performance, the benefit is outweighed by the cost of updating the index in DML operations. For each DML operation that is committed on a table, the indexes associated with that table are updated. The remaining options are benefits of indexes. Indexes reduce disk I/O and speed up row retrieval by eliminating full table scans. An index provides an indexed path to locate data quickly and prevents full table scans. You can manually create a non-unique index on a FOREIGN KEY constraint to improve the performance of join queries. It is beneficial to create an index on a column when the table is large and most queries return less than 2 to 4 percent of the rows. Because a small number of rows are returned, the index would be beneficial in that it will prevent a full table scan of a large table. If the query is expected to return more than 2 to 4 percent of the rows in the table, the cost of maintaining the index is greater than the benefit of eliminating the full table scan. Objective: Create and maintain indexesSub-Objective: Creating Other Database Objects References: 1. Introduction to Oracle9i: SQL - Other Database ObjectsItem: 13 (Ref:1z0-007e.9.5.4) Evaluate this statement: TRUNCATE TABLE inventory; Which two users could successfully issue this statement? (Choose two.) the owner of the INVENTORY table any user with access to the PUBLIC schema any member of the CONNECT and RESOURCE roles any user with the DROP ANY TABLE system privilege Answer: the owner of the INVENTORY table any user with the DROP ANY TABLE system privilege Explanation: To issue this TRUNCATE TABLE statement successfully, you must own the INVENTORY table or have the DROP ANY TABLE system privilege. The 'TRUNCATE TABLE inventory' statement removes all rows from the INVENTORY table and resets the storage parameters to the values with which the table was defined. There is no PUBLIC schema. Roles and privileges can be granted to the PUBLIC role and a public synonym can be created for user objects, but no items are owned by PUBLIC. The CONNECT and RESOURCE roles are provided for backward compatibility to earlier versions of Oracle, but do not have the privileges required to successfully issue the TRUNCATE TABLE statement. http://downloadwest.oracle.com/docs/cd/B10501_01/server.920/a96521/general.htm#20176 Objective: Drop, rename, and truncate tables Sub-Objective:Creating and Managing Tables References: 1. Introduction to Oracle9i: SQL - Creating and Managing Tables [Show More]

Last updated: 1 year ago

Preview 1 out of 226 pages