George Washington UniversityCSCI 6441Quiz 04_All in one.

Document Content and Description Below

Question 1 Selected Answer: Correct Answer: Which set operator would you use to display the employee IDs of employees hired after January 10, 2007 in the EMP table and employee IDs of employees who... have held more than one position in the EMP_HIST table, eliminating any duplicate IDs? INTERSECT UNION Question 2 Selected Answers: Correct Answers: Which three statements concerning explicit datatype conversions are TRUE? (Choose three.) A character value may be converted to a date value using the TO_DATE function. A date value may be converted to a character value using the TO_DATE function. A date value may be converted to a character string using the TO_CHAR function. A character value may be converted to a date value using the TO_DATE function. A date value may be converted to a character string using the TO_CHAR function. A number value may be converted to a character string using the TO_CHAR function. Question 3 Selected Answer: b. Correct Answer: b. The _____ data type is considered compatible with VARCHAR(35). CHAR(15) CHAR(15) Question 4 Courses 0 out of 1 points 0 out of 1 points 1 out of 1 points 1 out of 1 points Tianyu GeSelected Answer: b. Correct Answer: b. Which of the following queries will use the given columns and column aliases from the PRODUCT table to determine the total value of inventory held on hand? SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE FROM PRODUCT; SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE FROM PRODUCT; Question 5 Selected Answers: Correct Answers: Response Feedback: Which two DML statements could you use to modify the contents of the PRODUCT_NAME column of the existing PRODUCT table? (Choose two.) MERGE UPDATE MERGE UPDATE The MERGE and UPDATE statements can be used to update a value in an existing table. The MERGE statement will update rows conditionally, based on whether a row already exists or not. The UPDATE statement will modify the contents of a column based on the updated values specified in the SET clause. Both of these Data Manipulation Language (DML) statements modify the data, not the structure of the table. The ALTER statement is not a DML statement, but rather a Data Definition Language (DDL) statement. The ALTER statement is used to modify the structure of a table or add new columns to an existing table. The COMMIT statement is not a DML statement. The COMMIT statement is a Transaction Control Language (TCL) statement that ends the current transaction and saves all pending changes to the database. Although the INSERT statement is a DML statement, it is only used to add new rows to an existing table, not to update existing rows. MODIFY is not a valid DML statement. Valid DML statements include INSERT, UPDATE, DELETE, and MERGE. Question 6 Click the Exhibit(s) button to examine the structures of the PATIENT, PHYSICIAN, and ADMISSION tables. Which SQL statement will produce a list of all patients who have more than one physician? 1 out of 1 points 0 out of 1 pointsSelected Answer: Correct Answer: SELECT patient_id FROM admission WHERE COUNT(physician_id) > 1; SELECT DISTINCT a.patient_id FROM admission a, admission a2 WHERE a.patient_id = a2.patient_id AND a.physician_id <> a2.physician_id; Question 7 Selected Answer: Correct Answer: Response Feedback: Examine the structures of the CUSTOMER and ORDER tables. You want to create a report showing each customer and all orders placed by that customer. Specifically, you want your report to contain the columns custid, custname, and custcreditlimit from the CUSTOMER table as well as the columns ordid, orddate, and ordamount from the ORDER table. The output should be limited to customers who are located in Dallas, and should omit customers who have never placed an order. You issue the following SELECT statement to accomplish this: SELECT c.custid, c.custname, c.custcreditlimit, o.ordid, o.orddate, o.ordamount FROM CUSTOMER c, ORDER o WHERE UPPER(c.custlocation) = 'DALLAS' Which of the following statements is true regarding the results of this statement? The SELECT statement will return each customer in Dallas from the CUSTOMER table matched with every order in the ORDER table, as well as each order in the ORDER table matched with every Dallas­based customer in the CUSTOMER table. The SELECT statement will return each customer in Dallas from the CUSTOMER table matched with every order in the ORDER table, as well as each order in the ORDER table matched with every Dallas­based customer in the CUSTOMER table. The SELECT statement listed will execute; however, it will return each customer in Dallas from the CUSTOMER table matched with every order in the ORDER table, as well as each order in the ORDER table matched with every Dallas­based customer in the CUSTOMER table. The reason for this is the absence of a WHERE clause which links together the parent (the custid in the CUSTOMER table) and child (the ordcustid in the ORDER table). Without that condition in the WHERE clause, the results will be a Cartesian cross product which joins each row from the first table with every one of the rows in the second table, and each row from the second table with every one of the rows in the first table. The SELECT statement will not return the results as specified in the scenario since the expectation was that the only orders which would appear for a given customer are the orders which were placed by that customer. It is not a requirement that the location must be inserted into the customer table in all capital letters. The SELECT will take the name as it appears in the database column, convert it into all caps, and then make the comparison to see if it is equal to 'DALLAS'. This logic is performing correctly. Even though one of the conditions of the WHERE clause references the column called custlocation, it is not necessary that that column also appear in the list of column names in the SELECT clause. 1 out of 1 pointsSince the column in the WHERE clause called custlocation only appears in the CUSTOMER table and not the ORDER table, and since those are the only two tables in the FROM clause (the only two tables being joined), then Oracle is able to deal with the unqualified name since there is no possibility of ambiguity in this case. [Show More]

Last updated: 2 years ago

Preview 1 out of 231 pages