1.04 UTF-8 0 0 0 Which of the following clauses in a SELECT statement allow you to sort the output? 0 1 SELECT BY 0 ORDER BY 1 SORT BY 0 LIST BY 0 The EVENT table contains these columns: EVENT_ID NUMBER EVENT_NAME VARCHAR2(30) EVENT_DESC VARCHAR2(100) EVENT_TYPE NUMBER LOCATION_ID NUMBER You have been asked to provide a report of the number of different event types at each location. Which SELECT statement will produce the desired result? 0 Explanation: To produce the needed report, you should use the SELECT statement that includes 'COUNT(DISTINCT event_type)' in the select list. This statement will group the records by location and for each location will count the unique values of EVENT_TYPE. This will provide you the number of different event types at each location. The option that uses the UNIQUE function is incorrect because UNIQUE is not a valid function. The options that include 'SELECT COUNT(*), DISTINCT(location_id)' and SELECT DISTINCT (event_type) are invalid because they result in syntax errors. The DISTINCT keyword can be used at the beginning of a select list to eliminate duplicates in a result set or can be used nested inside an aggregate function to calculate on only unique values, but cannot be used as a stand-alone function. The option that includes 'MAX(DISTINCT event_type)' in the select list is syntactically correct. While it correctly uses the DISTINCT keyword, it returns the location and the maximum EVENT_TYPE at each location and this is not what you desired. 1 SELECT UNIQUE(location_id), COUNT(event_type) FROM event GROUP BY location_id; 0 SELECT COUNT(*), DISTINCT(location_id) FROM event; 0 SELECT DISTINCT (event_type) FROM event GROUP BY location_id; 0 SELECT location_id, COUNT(DISTINCT event_type) FROM event GROUP BY location_id; 1 SELECT location_id, MAX(DISTINCT event_type) FROM event GROUP BY location_id; 0 Which two statements about the evaluation of clauses in a SELECT statement are true? (Choose two.) 1 Explanation: The Oracle Server will evaluate a WHERE clause before a GROUP BY clause. The Oracle Server will evaluate a GROUP BY clause before a HAVING clause. The order of evaluation of clauses is: 1. WHERE clause 2. GROUP BY clause 3. HAVING clause 4. ORDER BY clause The WHERE clause establishes the candidate rows. From these rows, the Oracle Server identifies the groups in the GROUP BY clause. The HAVING clause further restricts the result groups. The data is then ordered based on the values in the ORDER BY clause. The statements indicating that the HAVING clause will be evaluated before the WHERE clause, the ORDER BY clause will be evaluated before the WHERE clause, or the ORDER BY clause will be evaluated before the HAVING clause are incorrect. 1 The Oracle Server will evaluate a HAVING clause before a WHERE clause 0 The Oracle Server will evaluate a WHERE clause before a GROUP BY clause 1 The Oracle Server will evaluate a GROUP BY clause before a HAVING clause. 1 The Oracle Server will evaluate an ORDER BY clause before a WHERE clause 0 The Oracle Server will evaluate an ORDER BY clause before a HAVING clause 0 The EMPLOYEE table contains these columns: EMPLOYEE_ID NUMBER LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) DEPT_ID NUMBER JOB_ID VARCHAR2(15) MGR_ID NUMBER SALARY NUMBER(9,2) COMMISSION NUMBER(7,2) HIRE_DATE DATE Which SELECT statement could be used to display the 10 lowest paid clerks that belong to department 70? 0 Explanation: Using Top-n Analysis when executing a SELECT statement allows the user to return the n largest or smallest values from a particular column. A Top-n query contains an outer or main query and a subquery or an inline view. The two Top-n query components of the outer query are the use of the ROWNUM pseudocolumn in the column list and the WHERE clause. The ROWNUM in the SELECT list, 'SELECT ROWNUM "Ranking", last_name||', '||first_name "Employee", salary "Salary" ' is used to assign a sequential value to each row returned from the subquery starting with the number 1. The WHERE clause specifies the number (n) of rows to be returned and must use the less than (<) or less than or equal to (<=) comparison operators: 'WHERE ROWNUM <= 10;'. The subquery or inline view (in this scenario a subquery is used) is used to query sorted data. The subquery contains an ORDER BY clause that sorts by the Top-n column: 'ORDER BY salary'. The default sort order is ascending (from lowest to highest). The restricting criteria, that the employee must be a Clerk that works in department 70 is achieved in the WHERE clause: 'AND job_id LIKE 'CLERK' AND dept_id = 70;'. The statement that does not contain the JOB_ID and DEPT_ID in the subquery SELECT list fails when executed. The subquery, which is a data source for the outer query, does not query the JOB_ID or DEPT_ID columns. The statement fails because of the WHERE condition 'AND dept_id = 70'. This condition cannot be met because the data source (subquery) does not access this column. The statement that contains "WHERE ROWNUM <= 10' in the subquery executes successfully but does not return the desired results. This query will limit the number of rows returned by the outer query to 10 which could eliminate some rows that meet the criteria of a JOB_ID value of 'CLERK' and a DEPT_ID value of '70'. When 'ROWNUM <= 10' resides in the outer query, the first 10 rows, if they exist, that meet this criteria will be returned. 1 SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary" FROM (SELECT last_name, first_name, salary FROM employee ORDER BY salary) WHERE ROWNUM <= 10 AND job_id LIKE 'CLERK' AND dept_id = 70; 0 SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary" FROM (SELECT last_name, first_name, salary, job_id FROM employee WHERE job_id LIKE 'CLERK' AND dept_id = 70 ORDER BY salary) WHERE ROWNUM <= 10; 1 SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary" FROM (SELECT last_name, first_name, salary, job_id, dept_id FROM employee WHERE ROWNUM <= 10 ORDER BY salary) WHERE job_id LIKE 'CLERK' AND dept_id = 70; 0 The desired result can only be achieved using a PL/SQL LOOP 0 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? 0 Explanation: In this example, all of the DML statements are committed to the database. When the ALTER TABLE statement is executed, an implicit commit occurs. This commits the updates performed in the both INSERT statements, the first UPDATE statement, and the DELETE statement. This implicit commit releases all held locks, erases all savepoints, and writes the changes permanently to the database. When the ROLLBACK TO B is issued, an error will occur stating that savepoint B was never established. Therefore, this rollback statement has no affect. Then, the final UPDATE statement is issued but never committed. Another implicit commit occurs when you exit iSQL*Plus and this commits the changes made by the last UPDATE statement. All options indicating that not all of the DML statements were committed are incorrect. 1 only the INSERT statements 0 only the INSERT statements and the first UPDATE statement 0 the INSERT statements, the first UPDATE statement, and the DELETE statement 0 all of the DML operations 1 none of the DML operations 0 Click the Exhibit(s) button to examine the structure of the EMPLOYEE table. You want to generate a list of employees are in department 30, have been promoted from clerk to associate by querying the EMPLOYEE and EMPLOYEE_HIST tables. The EMPLOYEE_HIST table has the same structure as the EMPLOYEE table. The JOB_ID value for clerks is 1 and the JOB_ID value for associates is 6. Which query should you use? 0 Explanation: A multi-column subquery is used to retrieve the employee ids and department ids of employees who are clerks (JOB_ID = 1) and who work in department 30 from the EMPLOYEE_HIST table. The IN operator is used to compare the list of employee ids retrieved from the subquery with the employee ids in the EMPLOYEE table (the outer query). This retrieved list of employees is further qualified with the use of the AND operator eliminating any employees from the list that are not currently associates (JOB_ID = 6). The result is a list of employees that are in department 30 who were promoted from clerk to associate. The SELECT statement that returns a single column in the subquery is incorrect because this statement will return a list of all employees who were previously clerks in department 30. The SELECT statement that includes 'WHERE dept_id = 30 AND job_id = 6)' as the condition for the inner query is incorrect because this statement returns all employees who at any time have been associates in department 30. Both of the statements that use the same table in the inner and outer query are incorrect. To produce a report of promotions, both tables must be included in the query. The EMPLOYEE table must be queried to check for each employee's current job, and the EMPLOYEE_HIST table must be queried to determine each employee's previous position. 1 SELECT employee_id, emp_lname, emp_fname, dept_id FROM employee WHERE (employee_id, dept_id) IN (SELECT employee_id, dept_id FROM employee_hist WHERE dept_id = 30 AND job_id = 1) AND job_id = 6; 1 SELECT employee_id, emp_lname, emp_fname, dept_id FROM employee WHERE (employee_id) IN (SELECT employee_id FROM employee_hist WHERE dept_id = 30 AND job_id = 1); 0 SELECT employee_id, emp_lname, emp_fname, dept_id FROM employee WHERE (employee_id, dept_id) = (SELECT employee_id, dept_id FROM employee_hist WHERE dept_id = 30 AND job_id = 6); 0 SELECT employee_id, emp_lname, emp_fname, dept_id FROM employee WHERE (employee_id, dept_id) = (SELECT employee_id, dept_id FROM employee_hist WHERE dept_id = 30 AND job_id = 6); 0 SELECT employee_id, emp_lname, emp_fname, dept_id FROM employee WHERE (employee_id, dept_id) IN (SELECT employee_id, dept_id FROM employee WHERE dept_id = 30) AND job_id = 6; 0 The EMPLOYEE table contains these columns: EMP_ID NUMBER(9) FNAME VARCHAR2(25) LNAME VARCHAR(30) SALARY NUMBER (7,2) BONUS NUMBER(5,2) DEPT_ID NUMBER(9) You need to calculate the average bonus for all the employees in each department. The average should be calculated based on all the rows in the table even if some employees do not receive a bonus. Which group function should you use to calculate this value? 0 Explanation: To calculate the average bonus, you should use the AVG function. The AVG group function can be used to calculate the average value for a group of values. When using AVG and other group functions, null values are ignored and not included in the group calculation. In this scenario, you wanted to include null values, so you should also use the NVL function to force the AVG function to include null values. In this scenario, use this SELECT statement to return the desired results: SELECT AVG(NVL(bonus, 0)) FROM employee GROUP by dept_id; While SUM, MAX, and COUNT are valid group functions, none of these functions will calculate an average for a group of values as you desired. Use the SUM group function to total a group of values, ignoring null values. Use the MAX group function to return the greatest value in a group of values, ignoring null values. Use the COUNT group function to return the number of rows in a group while ignoring null values. Use COUNT(*) to count all the selected rows, including duplicates and rows with nulls. MEAN and AVERAGE are not valid group functions. Therefore, these options are also incorrect. 1 AVG 1 SUM 0 MAX 0 MEAN 0 COUNT 0 AVERAGE 0 What would happen if you created an index on all six columns in the STUDENT table? 0 Explanation: If you created an index on all six columns in the STUDENT table, all DML operations on the table would be slower. For each DML operation that is committed on the STUDENT table, the indexes associated with the table are updated. When a column contains too many indexes, DML operations performed on the table will be slower because the indexes must also be updated. The speed of updates would not be increased because all DML operations are slowed by indexes. Some queries on the STUDENT table with a WHERE clause would be faster because full table scans would not be required to retrieve the required data, but this is not the case for all queries with a WHERE clause. The speed of queries with WHERE clauses on small tables may not be increased by using an index. Full table scans on small tables are usually faster than index scans. Not all SELECT statements on the STUDENT table will be faster because indexes only provide performance benefits to queries with a WHERE clause or join condition. 1 The speed of updates would be increased 0 All queries with a WHERE clause would be slower. 0 The delete operations on the table would be slower. 1 All the SELECT statements issued on the table would be faster 0 Which is a legal table name? 0 Explanation: NUMBER#1 is a legal table name because it begins with a letter, is 1-30 characters long and contains only characters A-Z, a-z, 0-9, _ (underscore), $, and #. Although NUMBER is an Oracle Server reserved word, NUMBER#1 is not considered to be a reserved word. COLUMN is not a legal table name because it is an Oracle Server reserved word. 1CUSTOMER is not a legal table name because it does not begin with a letter. Attempting to create a table named COLUMN or 1CUSTOMER will return an "ORA-00903: invalid table name" error. NEW_CUSTOMER_ENTERED_BEFORE_SEPT01 is not a legal table name because it is longer than 30 characters. Attempting to create a table whose table name is longer than 30 characters will return an "ORA-00972: identifier is too long" error. 1 COLUMN 0 NUMBER#1 1 1CUSTOMER 0 NEW_CUSTOMER_ENTERED_BEFORE_SEPT01 0 The INVENTORY table contains these columns: ID_NUMBER NUMBER PK DESCRIPTION VARCHAR2(30) SUPPLIER_ID NUMBER You want to create a query that for each session allows the user to input a value for DESCRIPTION each time the query runs. While the DESCRIPTION column is stored in upper case, you want the query to retrieve matching values regardless of the case used when inputting the substitution variable value. Which SELECT statement should you use? 0 Explanation: To create a query that allows the user to input a value, you can use substitution variables. Using the single ampersand (&), you can create a substitution variable used to temporarily store a value. Using the double ampersand (&&), you can create a substitution variable that is reusable without prompting the user for the value a second time. The option that uses 'WHERE LOWER(description) = LOWER('&description')' as the query condition is correct. The query will first prompt the user to enter a value for the '&description' substitution variable. Then, the input provided by the user will be converted to lowercase using the LOWER function and compared to the lowercase representation of the DESCRIPTION column of the INVENTORY table. Converting both columns in the condition to the same case ensures that the condition is met, regardless of how the user input the value. The option that uses 'WHERE description = UPPER(&description)' as the query condition is incorrect. When using a substitution variable in a WHERE clause, substitution variables that represent date and character values must be enclosed within single quotation marks. The option that uses 'WHERE LOWER(description) = '&description' ' as the query condition is incorrect because values on the left and right of the equal operator might be of different cases and a match would not be found. If when prompted, the user entered an upper or mixed case value for the substitution variable, then this query condition would not be met, even if the descriptions were otherwise identical. The option that uses 'WHERE description = UPPER('&&description') ' as the query condition is incorrect because the double ampersand (&&) was used to create the substitution variable. In this scenario, you wanted the user to input a value each time the query runs, so the single ampersand (&) should be used. When the double ampersand is used, the value is stored using an implicit DEFINE command, and remains available for use until the user ends the session or executes a UNDEFINE command to clear the variable. In addition to using the single and double ampersands, you can also predefine variables explicitly using the DEFINE command. Again, these variables remain defined until you clear the variable using the UNDEFINE command or you exit iSQL*Plus. The syntax of the DEFINE and UNDEFINE commands are: DEFINE = UNDEFINE 1 SELECT id_number, supplier_id FROM inventory WHERE description = UPPER(&description); 0 SELECT id_number, supplier_id FROM inventory WHERE LOWER(description) = LOWER('&description'); 1 SELECT id_number, supplier_id FROM inventory WHERE LOWER(description) = '&description'; 0 SELECT id_number, supplier_id FROM inventory WHERE description = UPPER('&&description'); 0 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? 0 Explanation: To correct the problem with this SELECT statement, you should use the table aliases, instead of the table names, in the WHERE clause. Table aliases are specified for all tables in the FROM clause of this SELECT statement. Once defined, these table aliases must be used. The first join predicate in the WHERE clause uses the full table name to qualify each column, and this will result in an error. The ORDER BY clause uses the table alias correctly. Therefore, the option stating you should use the table name in the ORDER BY clause is incorrect. You should not remove the table aliases from the WHERE clause, but rather use aliases throughout. The ORDER_AMOUNT column does not need to be included in the SELECT list. A column not included in the select list can be used for ordering. If no columns had identical names in both tables, you could remove the table alias from the ORDER BY clause and use only the column name. However, this would not correct the error in this SQL statement. While special rules exist for using table aliases, both with Oracle proprietary and SQL: 1999 syntax, using table aliases where possible is recommended. Using table aliases not only makes SQL statements easier to read, but provides additional performance enhancements. 1 Use the table name in the ORDER BY clause. 0 Remove the table aliases from the WHERE clause. 0 Include the ORDER_AMOUNT column in the SELECT list 0 Use the table aliases instead of the table names in the WHERE clause. 1 Remove the table alias from the ORDER BY clause and use only the column name. 0 Which SELECT statement implements a self join? 0 Explanation: A self join is used when a table must be joined to itself. When implementing a self join, a table is included twice in the FROM clause, each time with a different table alias. Then, these table aliases are used in the WHERE clause to join the table to itself. The option that lists the INVENTORY table twice in the FROM clause and uses 'WHERE i.manufacturer_id = m.id_number' as the join condition is correct. The SELECT statement that uses a NATURAL JOIN is incorrect because a natural join should not be used to join a table to itself. When joining a table to itself, qualifiers are necessary to avoid ambiguous column references. Columns used in a natural join cannot contain a table qualifier. Also, both options that include the manufacturer table in the FROM clause are incorrect because they do not join a table to itself, but rather join two different tables. 1 SELECT i.id_number, m.manufacturer_id FROM inventory i NATURAL JOIN inventory m; 0 SELECT i.id_number, m.manufacturer_id FROM inventory i, inventory m WHERE i.manufacturer_id = m.id_number; 1 SELECT i.id_number, m.manufacturer_id FROM inventory i, manufacturer m WHERE i.manufacturer_id = m.id_number; 0 SELECT i.id_number, m.manufacturer_id FROM inventory i, manufacturer m WHERE i.manufacturer_id <> m.id_number; 0 Click the Exhibit(s) button to examine the structures of the DONOR, DONATION, and DONOR_LEVEL tables. You want to produce a report of all donors, including each donor's giving level. The donor level should be determined based on the amount pledged by the donor. Which SELECT statement will join these three tables and implements a non-equijoin? 0 Explanation: A non-equijoin is represented by the use of an operator other than an equality operator (=). A non-equijoin is used when no corresponding columns exist between the tables in the query, but rather a relationship exists between two columns having compatible data types. Several conditions can be used to define a non-equijoin, including <, <=, >, >=, BETWEEN, and IN. In the given scenario, a non-equijoin relationship exists between the AMOUNT_PLEDGED column of the DONATION table and the MIN_DONATION and MAX_DONATION columns of the DONOR_LEVEL table. In addition, an equijoin relationship exists between the DONOR_ID column of the DONATION table and the DONOR_ID column of the DONOR table. To produce the report of all donors with their corresponding giving levels, you should use the SELECT statement that contains an ON clause and a USING clause. First, this statement joins the DONOR and DONATION tables using the commonly named column DONOR_ID. Then, this result is joined with the DONOR levels based on a non-equijoin condition in the ON clause, namely 'ON (dn.amount_pledged BETWEEN dl.min_donation AND dl.max_donation)'. The SELECT statement that only includes the DONOR and DONOR_LEVEL tables in the FROM clause is incorrect. The DONATION table must be included in the relationship because it contains the AMOUNT_PLEDGED column. The SELECT statement that uses traditional Oracle syntax to implement the join in the WHERE clause but only includes one WHERE clause condition, 'WHERE dn.amount_pledged BETWEEN dl.min_donation AND dl.max_donation', is also incorrect. This join condition provides for no relationship between the DONATION and DONOR tables, and it would be impossible to associate a donor name with each donation. The SELECT statement that specifies two ON clauses is incorrect because it has invalid syntax. When the JOIN...ON syntax is used, the ON clause must follow its corresponding JOIN. 1 SELECT d.donor_name, dl.level_description FROM donor d, donor_level dl WHERE amount_pledged BETWEEN dl.min_donation AND dl.max_donation; 0 SELECT d.donor_name, dl.level_description FROM donor d JOIN donation dn USING (donor_id) JOIN donor_level dl ON (dn.amount_pledged BETWEEN dl.min_donation AND dl.max_donation); 1 SELECT d.donor_name, dl.level_description FROM donor d, donation dn, donor_level dl WHERE dn.amount_pledged BETWEEN dl.min_donation AND dl.max_donation; 0 SELECT d.donor_name, dl.level_description FROM donor d JOIN donation dn JOIN donor_level dl ON (donor_id) AND ON (dn.amount_pledged BETWEEN dl.min_donation AND dl.max_donation); 0 This CANNOT be accomplished because the DONATION_LEVEL and DONATION tables have no common column. 0 Evaluate this CREATE TABLE statement: CREATE TABLE customer ( customer_id NUMBER, company_id 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 DEFAULT 25, phone VARCHAR2(20), fax VARCHAR2(20), credit_limit NUMBER (7,2)); Which three business requirements will this statement accomplish? (Choose three.) 1 Explanation: Defining the PHONE column in the CUSTOMER table as a VARCHAR2 data type provides for variable-length character data. In this example, phone number values can be up to 20 characters in length, but the size of the column will vary depending on each row value. Defining the COMPANY_ID column as a VARCHAR2 data type allows for character data, including A-Z, a-z, and 0-9. Defining the COUNTRY_ID column with the DEFAULT option with a value of 25 ensures that the value of 25 will be used in INSERT operations if no value is provided. The DEFAULT option will prevent a null value from being inserted into the COUNTRY_ID column if a row is inserted without a COUNTRY_ID value. Defining the CREDIT_LIMIT column of data type NUMBER(7,2) allows values up to 99,999.99. The column is defined with a precision of 7 and a scale of 2. If the user attempts to insert a credit limit value with more than 5 digits to the left of the decimal, an "ORA-01438: value larger than specified precision allows for this column" error would be returned. Defining the CUSTOMER_ID value of data type VARCHAR2(30) creates a variable-length character column of 30 bytes. Therefore, the business rule requiring a six-byte fixed-length column is not met. 1 Credit limit values can be up to $1,000,000. 0 Company identification values could be either numbers or characters, or a combination of both. 1 All customer identification values are only 6 digits so the column should be fixed in length. 0 Phone number values can range from 0 to 20 characters so the column should be variable in length. 1 The value 25 should be used if no value is provided for the country identification when a record is inserted. 1 You issued this statement: GRANT UPDATE ON inventory TO joe WITH GRANT OPTION; Which task was accomplished? 0 Explanation: This GRANT statement grants user Joe the UPDATE object privilege on the INVENTORY table. Object privileges allow users to perform particular actions on specific objects including tables, views, sequences, and procedures. The WITH GRANT OPTION in this GRANT statement allows user Joe to grant the UPDATE privilege to other users and roles. The options stating that only a system privilege was given to user Joe and both an object and a system privilege were given to user Joe are incorrect because UPDATE is an object privilege and not a system privilege. The option stating that user Joe was granted all privileges on the INVENTORY object is incorrect because user Joe was only granted the UPDATE privilege on the INVENTORY table. To grant all object privileges on the INVENTORY table to user Joe, use this statement: GRANT ALL ON inventory TO joe; 1 Only a system privilege was granted to user Joe. 0 Only an object privilege was granted to user Joe 1 User Joe was granted all privileges on the INVENTORY object. 0 Both an object privilege and a system privilege were granted to user Joe 0 Click the Exhibit(s) button to examine the data from the PO_HEADER and PO_DETAIL tables. Examine the structures of the PO_HEADER and PO_DETAIL tables: PO_HEADER -------------------- PO_NUM NUMBER NOT NULL PO_DATE DATE DEFAULT SYSDATE PO_TOTAL NUMBER(9,2) SUPPLIER_ID NUMBER(9) PO_TERMS VARCHAR2(25) PO_DETAIL ------------------ PO_NUM NUMBER NOT NULL PO_LINE_ID NUMBER NOT NULL PRODUCT_ID NUMBER NOT NULL, QUANTITY NUMBER(3) NOT NULL, UNIT_PRICE NUMBER (5,2) DEFAULT 0, The Primary Key of the PO_HEADER table is PO_NUM. The Primary Key of the PO_DETAIL table is the combination of PO_NUM and PO_LINE_ID. A FOREIGN KEY constraint is defined on the PO_NUM column of the PO_DETAIL table that references the PO_HEADER table. You want to update the purchase order total amount for a given purchase order. The PO_TOTAL column in the PO_HEADER table should equal the sum of the extended amounts of the corresponding PO_DETAIL records. You want the user to be prompted for the purchase order number when the query is executed. When a purchase order is updated, the PO_DATE column should be reset to the current date. Which UPDATE statement should you execute? 0 Explanation: To perform the desired updates, you should execute the UPDATE statement that returns the sum of the extended amount in the first subquery, returns the PO number and extended amount for each detail record in the innermost query, uses one SET statement, and uses the DEFAULT keyword to update the PO_DATE column. Subqueries are always evaluated from innermost to outermost. First, the innermost query executes and returns the PO_NUM and extended amount of each detail line of the specified purchase order. Then, the other subquery accepts this result and sums the extended amounts. The result is the sum of the extended amounts for each line item on the selected purchase order. The PO_TOTAL column is updated with this value. The PO_DATE column is updated using the DEFAULT keyword. When the DEFAULT keyword is used in an UPDATE or INSERT statement, the default value for the column being modified is used. In this scenario, the PO_DATE column in the PO_HEADER table has a default value of SYSDATE. Therefore, the PO_DATE is updated to the current date. The UPDATE statement that includes more than one SET keyword is incorrect. The correct UPDATE statement syntax includes the SET keyword one time followed by the columns to be updated separated by commas. The UPDATE statement that includes 'SUM(quantity * unit_price)' in the first subquery is incorrect. This subquery uses another subquery in its FROM clause, so only columns returned by the innermost query are available for use. The UPDATE statement that nests another UPDATE statement within it is incorrect because UPDATE statements cannot be nested. The UPDATE statement that returns both PO_NUM and SUM(ext) amount in the first subquery is incorrect because this subquery result is compared using the = operator. Therefore, this query must return only one value. The UPDATE statement that uses the NULL keyword to update the PO_DATE column is incorrect. In this scenario, you wanted to update PO_DATE to the current date. To do so, you could use SYSDATE or DEFAULT. Using the NULL keyword will substitute a NULL value instead. When the DEFAULT keyword is used and no default value is defined for a column, the column is assigned a NULL value. rsDocPageFrom.Close rsDocPageTo.Close 1 UPDATE po_header SET po_total = (SELECT SUM(ext) FROM (SELECT po_num, quantity * unit_price ext FROM po_detail WHERE po_num = &&ponum)), SET po_date = sysdate WHERE po_num = &&ponum; 0 UPDATE po_header SET po_total = (SELECT SUM(quantity * unit_price) FROM (SELECT po_num) FROM po_detail WHERE po_num = &&ponum)), po_date = DEFAULT WHERE po_num = &&ponum; 0 UPDATE po_header SET po_total = (SELECT SUM(ext) FROM (SELECT po_num, quantity * unit_price ext FROM po_detail WHERE po_num = &&ponum)), UPDATE po_header SET po_date = sysdate WHERE po_num = &&ponum; 0 UPDATE po_header SET po_total = (SELECT SUM(ext) FROM (SELECT po_num, quantity * unit_price ext FROM po_detail WHERE po_num = &&ponum)), po_date = DEFAULT WHERE po_num = &&ponum; 1 UPDATE po_header SET po_total = (SELECT po_num, SUM(ext) FROM (SELECT po_num, quantity * unit_price ext FROM po_detail WHERE po_num = &&ponum)), po_date = DEFAULT WHERE po_num = &&ponum; 0 UPDATE po_header SET po_total = (SELECT SUM(ext) FROM (SELECT po_num, quantity * unit_price ext FROM po_detail WHERE po_num = &&ponum)), po_date = NULL WHERE po_num = &&ponum; 0 The STUDENT 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 PT_STUDENT, with an identical structure. You want to insert all part-time students, who have a STU_TYPE_ID value of "P", into the new table. You execute this INSERT statement: INSERT INTO pt_student (SELECT stu_id, last_name, first_name, dob, sysdate FROM student WHERE UPPER(stu_type_id) = 'P'); What is the result of executing this INSERT statement? 0 Explanation: When executing the given INSERT statement, an "ORA-00947: not enough values" error occurs because the STU_TYPE_ID column is not included in the subquery select list. When using a subquery to insert rows from one table into another table, the number and data types of the columns being inserted must match the number and data types of the columns returned by the subquery. In the given INSERT statement, no column list was included. This implies that all columns in the table will be inserted. The subquery in the statement, returns values for the STU_ID, LAST_NAME, and DOB columns, and uses SYSDATE for the ENROLL_DATE column. The STU_TYPE_ID column, however, is not included, and an error occurs. All part-time students are not inserted into the PT_STUDENT table because this statement generates an error. If the select list of the subquery had included the STU_TYPE_ID column or a character constant had been included to give this column a value, all part-time students would have been inserted into the PT_STUDENT table. The option stating that an error occurs because the PT_STUDENT table already exists is incorrect. In fact, to use a table in the INTO portion of a SELECT statement, the table MUST exist. You can however, use a subquery in a CREATE TABLE statement to create the table and insert records if needed. The option stating that an error occurs because you cannot use a subquery in an INSERT statement is also incorrect. Subqueries can be used both in the INTO portion of a SELECT statement and as a substitute for a VALUES clause in a SELECT statement. The option stating that an error occurs because the INSERT statement does not contain a VALUES clause is incorrect. When including a subquery for the values to be inserted, the subquery replaces the VALUES clause. The option stating that an error occurs because both the STU_TYPE_ID and ENROLL_DATE columns are not included in the subquery select list is incorrect because a valid date value, namely SYSDATE, was provided for the ENROLL_DATE column. Therefore, the ENROLL_DATE column is not a problem in this INSERT statement. All part-time students are inserted into the PT_STUDENT table. 0 An error occurs because the PT_STUDENT table already exists 0 An error occurs because you CANNOT use a subquery in an INSERT statement 0 An error occurs because the INSERT statement does NOT contain a VALUES clause 0 An error occurs because the STU_TYPE_ID column is NOT included in the subquery select list. 1 An error occurs because both the STU_TYPE_ID and ENROLL_DATE columns are NOT included in the subquery select list. 0 Which statement is true concerning a new user that has only been granted the CREATE SESSION privilege? 0 Explanation: The CREATE SESSION privilege allows users to connect to the database. When a user is created and granted the privilege to connect, the user can alter their own password. The ALTER USER privilege is required to alter the password of another user. Users must be explicitly granted the privileges that allow them to create tables. A new user with only the CREATE SESSION privilege is unable to create a table. When a user with the CREATE TABLE privilege creates a table, the user automatically has the ability to select data from the table and to grant privileges on the table. 1 The user can create a table 0 The user can alter their password 1 The user can select from tables they have created. 0 The user can grant privileges on objects that they own 0 Click the Exhibit(s) button to examine the structure of the PRODUCT table. You need to reduce the LIST_PRICE column precision to 6 with a scale of 2 and ensure that when inserting a row into the PRODUCT table without a value for the LIST_PRICE column, a price of $5.00 will automatically be inserted. The PRODUCT table currently contains no records. Which statement should you use? 0 Explanation: Because the LIST_PRICE column is empty, you can decrease the precision and scale of the column. This is the proper syntax to reduce the precision or scale of a numeric column: ALTER TABLE table_name MODIFY (column_name NUMBER(p,s)); You can add a default value to any column, but the default value will only affect subsequent insertions into the table. This is the proper syntax to define a DEFAULT option for an existing column: ALTER TABLE table_name MODIFY (column_name DEFAULT default_value); Parentheses in the ALTER TABLE statement are optional when you are only modifying one column. The ALTER TABLE statement with the ADD OR REPLACE option returns an "ORA-00904: invalid column name" error because the Oracle Server expects a column name after the ADD keyword and recognizes OR as an Oracle Server reserved word. The ALTER TABLE statement with the MODIFY COLUMN option returns an "ORA-00905: missing keyword" error. The ALTER TABLE statement with the MODIFY and REPLACE options returns an "ORA-01735: invalid ALTER TABLE option" error. If the LIST_PRICE column contained data, you would not be able to decrease the precision or scale of the column 1 ALTER TABLE product MODIFY (list_price NUMBER(6,2) DEFAULT 5); 1 ALTER TABLE product ADD OR REPLACE (list_price NUMBER(8,2) DEFAULT 5); 0 ALTER TABLE product MODIFY COLUMN (list_price NUMBER(6,2) DEFAULT '$5.00'); 0 ALTER TABLE product MODIFY (list_price NUMBER(8,2) DEFAULT 5) REPLACE COLUMN (list_price NUMBER(6,2); 0 You CANNOT reduce the size of the LIST_PRICE column 0 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? 0 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); 1 Rows are inserted into the AR_TRX table 0 Rows are inserted into the AR_TRX_HY table 0 One or more rows are updated in the AR_TRX table 1 One or more rows are updated in the AR_TRX_HY table 0 An error occurs because you do NOT have MERGE privileges on the AR_TRX table 0 An error occurs because there are no rows in the second table that are not in the first table. 0 The PERSONNEL table contains these columns: ID NUMBER(9) LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) MANAGER_ID NUMBER(9) DEPT_ID NUMBER(9) Evaluate this SQL statement: SELECT p.dept_id, p.first_name|| ' ' ||p.last_name employee, c.first_name|| ' ' ||c.last_name coworker FROM personnel p, personnel c WHERE p.dept_id = c.dept_id AND p.id <> c.id; Which result will the statement provide? 0 Explanation: A self join allows you to join a table to itself. In this example, the query looks at the PERSONNEL table twice. The first time the query retrieves the department number and name of each employee in the PERSONNEL table. The second time the query retrieves the names of each of the employees working in the same department. So, the statement will display each employee's department number, name, and all their coworkers in the same department. The option stating that the statement will display each employee's department number, name, and their manager's name is incorrect. To do so would require a self join from the ID column to the MANAGER column. The option stating that the statement will display each department, the manager in each department, and all the employees in each department is also incorrect for the same reason. The statement is syntactically correct. Therefore, the option stating that it will return an error is also incorrect. It will display each employee's department number, name, and their manager's name 0 It will display each employee's department number, name, and all their coworkers in the same department 1 It will display each department, the manager in each department, and all the employees in each department 0 It will return a syntax error 0