Database Management Systems_CSCI_6441 George Washington University Quiz 4_merged 231 PAGES < BEST LAYOUT AND DISPLAY> HIGH GRADES

Document Content and Description Below

ALL CORRECT ANSWERS ARE SHOWN 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 an... d 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. Question 8 Selected Answer: b. Correct Answer: b. Which of the following is a feature of a correlated subquery? The outer subquery initiates the process of execution in a subquery. The outer subquery initiates the process of execution in a subquery. Question 9 Selected Answer: Correct Answer: PLAYER ­­­­­­­­­­­­­ PLAYER_ID NUMBER(9) PK LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) TEAM_ID NUMBER MANAGER_ID NUMBER(9) TEAM ­­­­­­­­­­ TEAM_ID NUMBER PK TEAM_NAME VARCHAR2(30) Examine the structures of the PLAYER and TEAM tables: For this example, team managers are also players, and the MANAGER_ID column references the PLAYER_ID column. For players who are managers, MANAGER_ID is NULL. Which SELECT statement will provide a list of all players, including the player's name, the team name, and the player's manager's name? SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name FROM player p JOIN player m ON (p.manager_id = m.player_id) RIGHT OUTER JOIN team t ON (p.team_id = t.team_id); SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name FROM player p LEFT OUTER JOIN player m ON (p.player_id = m.player_id) LEFT OUTER JOIN team t ON (p.team_id = t.team_id); Question 10 Selected Answer: Correct Answer: Response Feedback: Examine the structure of the PRODUCT table. You want to display the product identification numbers of all products with 500 or more units available for immediate sale. You want the product identification numbers displayed numerically by supplier identification number, then by product identification number from lowest to highest. Which statement should you use to achieve the required results? SELECT product_id FROM product WHERE qty_per_unit >= 500 ORDER BY supplier_id, product_id; SELECT product_id FROM product WHERE qty_per_unit >= 500 ORDER BY supplier_id, product_id; You should use the following statement to achieve the desired results: 1 out of 1 points 0 out of 1 points 1 out of 1 pointsSELECT product_id FROM product WHERE qty_per_unit >= 500 ORDER BY supplier_id, product_id; With this statement, all identification numbers of products that have a quantity greater than or equal to 500 are displayed because the greater than or equal to (>=) comparison operator is used. The ORDER BY clause must contain the SUPPLIER_ID and PRODUCT_ID columns as requested. The product identification numbers are displayed by supplier identification number in ascending order, and then within supplier identification number by product identification number, orderedfromlowesttohighest.ThedefaultsortorderwhenusinganORDER BYclauseisascending(lowesttohighestfor numeric data, earliest to latest for date data, and alphabetically for character data). Because this is the default sort order, no sortorderkeyword,ASCorDESC,isrequiredintheORDER BYclause. You should not use either of the statements that contain a SORT BY clause. Both of these statements will fail to execute because SORT BY is not a valid SELECT statement clause. If SORT BY were replaced with ORDER BY, one of the statements would execute returning the desired results. The other would execute and return undesired results. You should not use the statement that contains the DESC keyword. This statement executes successfully, but does not return the desired results. The DESC keyword of the ORDER BY clause orders the values in ascending order by supplier identification number, but then within supplier identification number the values are sorted from the highest to the lowest by product identification number. In this scenario, you wanted the rows to be sorted in ascending order by product identification number. Question 11 Selected Answer: Correct Answer: Evaluate this SELECT statement: SELECT emp_id "Employee", dept_id "Department" FROM emp INTERSECT SELECT emp_id employee, dept_id department FROM emp WHERE dept_id >100 MINUS SELECT emp_id "Employee", dept_id "Department" FROM emp WHERE dept_id <> 200 ORDER BY 2; Which of the following statements is true? The statement will return the results sorted by the DEPT_ID values in the second query. The statement will return the results sorted by the DEPT_ID values in the first query. Question 12 Selected Answer: Correct Answer: Response Feedback: You query the database with this SQL statement: SELECT bonus FROM salary WHERE bonus BETWEEN 1 AND 250 OR (bonus IN(190, 500, 600) AND bonus BETWEEN 250 AND 500); Which BONUS value could the statement return? 100 100 Theonlylistedbonus valuethatthisSELECTstatementcouldreturnis100.ThefirstconditionintheWHEREclause,WHERE bonus BETWEEN 1 AND 250, returns rows with bonus values between 1 and 250 inclusive. In the second and third conditions in the WHERE clause, the combination of the two clauses allows only a value of 500 to be returned. The OR operator joining the two clauses allows either a value between 1 and 250 or a value of 500 to be returned. The value of 100 is the only value that meets these criteria. TheBETWEENconditionhasprecedenceovertheANDandOR conditions.TheANDconditionhasprecedenceovertheOR condition. Conditions with higher precedence are evaluated first. 0 out of 1 points 1 out of 1 pointsAll of the other options are incorrect because these values could not be returned by the given statement. Question 13 Selected Answer: b. Correct Answer: d. The SQL command that allows a user to permanently save data changes is _____. UPDATE COMMIT Question 14 Selected Answer: False Correct Answer: False MySQL allows multiple triggering conditions per trigger. Question 15 Selected Answer: Correct Answer: You query the database with this SQL statement: SELECT AVG(LENGTH(name)) COLUMN1, SUM(INSTR(ssn,'52',2,2)) COLUMN2 FROM emp2 WHERE name = INITCAP(name); What will be displayed for the output of COLUMN1 and COLUMN2? The value in COLUMN1 will be 3.6 and the value in COLUMN2 will be 26. The value in COLUMN1 will be 3.5 and the value in COLUMN2 will be 11. Question 16 Given following query: SELECT R1.SUPNAME, R1.SUPNR, COUNT(*) FROM PURCHASE_ORDER PO1, SUPPLIER R1 WHERE PO1.SUPNR = R1.SUPNR GROUP BY R1.SUPNR HAVING COUNT(*) >= ALL ( SELECT COUNT(*) FROM PURCHASE_ORDER PO2, SUPPLIER R2 WHERE PO2.SUPNR = R2.SUPNR GROUP BY R2.SUPNR) 0 out of 1 points 1 out of 1 points 0 out of 1 points 0 out of 1 pointsSelected Answer: Correct Answer: The query retrieves: The name, number and total outstanding orders of all suppliers that have outstanding orders, except for the supplier(s) with the least outstanding orders The name, number and total outstanding orders of the supplier with the most outstanding orders Question 17 Selected Answer: a. Correct Answer: a. The _____ statement in SQL combines rows from two queries and returns only the rows that appear in the first set but not in the second. MINUS MINUS Question 18 Selected Answer: Correct Answer: Given the following SQL query: SELECT P1.PRODNR FROM PRODUCT P1 WHERE 5 <= ( SELECT COUNT(*) FROM PRODUCT P2 WHERE P1.PRODNR < P2.PRODNR) This query selects: The 5 highest product numbers. All product numbers except for the 5 highest product numbers. Question 19 Selected Answer: Correct Answer: Response Feedback: The PHYSICIAN table contains these columns: PHYSICIAN_ID NUMBER NOT NULL PK LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(25) NOT NULL LICENSE_NO NUMBER(7) NOT NULL HIRE_DATE DATE When new physician records are added, the PHYSICIAN_ID is assigned a sequential value using the PHY_NUM_SEQ sequence. The state licensing board assigns license numbers with valid license numbers being from 1000000 to 9900000. You want to create an INSERT statement that will prompt the user for each physician's name and license number and insert the physician's record into the PHYSICIAN table with a hire date of today. The statement should generate an error if an invalid license number is entered. Which INSERT statement should you use? INSERT INTO (SELECT physician_id, last_name, first_name, license_no, hire_date FROM physician WITH CHECK OPTION WHERE license_no BETWEEN 1000000 and 9900000) VALUES (phy_num_seq.NEXTVAL, '&lname', '&fname', &lno, sysdate); INSERT INTO (SELECT physician_id, last_name, first_name, license_no, hire_date FROM physician WITH CHECK OPTION WHERE license_no BETWEEN 1000000 and 9900000) VALUES (phy_num_seq.NEXTVAL, '&lname', '&fname', &lno, sysdate); Toperformthenecessaryinsert,youshouldusetheINSERTstatementthatusesasubqueryincludingtheWITH CHECK OPTION keyword to identify the table for the insert and uses phy_num_seq.NEXTVAL as the value to be inserted for PHYSICIAN_ID. When using a subquery for the table of a 1 out of 1 points 0 out of 1 points 1 out of 1 pointsTuesday, March 5, 2019 3:35:12 PM EST Data Manipulation Language (DML) statement, the WITH CHECK OPTION keyword can be used to ensure that the DML statement is not allowed if the change would generate rows that are not included in the subquery. The INSERT statement that includes a WHERE clause is incorrect because a WHERE clause is not allowed with an INSERT statement. The INSERT statement that uses the BETWEEN operator in the VALUES clause is incorrect because the BETWEEN operator cannot be used in a VALUES clause. The INSERT statement that uses phy_num_seq.VALUE as the value inserted into the PHYSICIAN_ID column is incorrect and will cause an error. To generate the next sequence value from the PHY_NUM_SEQ sequence, you should use the NEXTVAL keyword. The INSERT statement that does not include single quotation marks around the &lname and &fname substitution variables is incorrect because character and date substitution variables should be enclosed in single quotation marks. The INSERT statement that uses &phy_num_seq as the value to be inserted for PHYSICIAN_ID will prompt the user for a value for PHYSICIAN_ID, rather than using the sequence as desired. Therefore, this option is incorrect. Question 20 Selected Answer: Correct Answer: What does the following query return? SELECT S.SUPNR, S.SUPNAME, (SELECT SUM(POL.QUANTITY) FROM PO_LINE POL, PURCHASE_ORDER P WHERE P.SUPNR=S.SUPNR AND POL.PONR=P.PONR) AS TOTALORDERED FROM SUPPLIER S The supplier number, name and total amount ordered for all suppliers, even if they have no orders outstanding. The supplier number, name and total amount ordered for all suppliers, even if they have no orders outstanding. ← OK 1 out of 1 points2019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791000_1&course_id=_310969_1&content_id=_9341638_1&… 1/6 201901_Database Management Systems_CSCI_6441_10 Tests Review Test Submission: Quiz04 Review Test Submission: Quiz04 User Ziyu Zhou Course 201901_Database Management Systems_CSCI_6441_10 Test Quiz04 Started 3/5/19 1:35 PM Submitted 3/5/19 2:23 PM Due Date 3/6/19 11:59 PM Status Completed Attempt Score 10 out of 20 points Time Elapsed 48 minutes out of 2 hours Results Displayed Submitted Answers, Correct Answers, Feedback, Incorrectly Answered Questions Question 1 Selected Answer: Correct Answer: SQL> SELECT * FROM cust_credit_v ORDER BY credit_limit DESC, last_name; You want to create a view that when queried will display the name, customer identification number, new balance, finance charge, and credit limit of all customers. When queried, the display should be sorted by credit limit from highest to lowest, then by last name alphabetically. The view definition should be created regardless of the existence of the CUSTOMER and ACCOUNT tables. No DML may be performed when using this view. Evaluate these statements: SQL> CREATE OR REPLACE FORCE VIEW CUST_CREDIT_V AS SELECT c.last_name, c.first_name, c.customer_id, a.new_balance, a.finance_charge, a.credit_limit FROM customer c, account a WHERE c.account_id = a.account_id WITH READ ONLY; Which statement is true? The CREATE VIEW statement will fail because a view may not be created on tables that do not exist or are not accessible by the user When both statements are executed, all of the desired results are achieved Question 2 Selected Answer: c. Correct Answer: c. 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 3 Selected Answer: Correct Answer: Given the following query: SELECT PRODNR, AVG(QUANTITY AS AVG_QUANTITY FROM PO_LINE GROUP BY PRODNR HAVING SUM(QUANTITY < 15 What is the result? The query returns the PRODNR and average QUANTITY of each purchase order that has less than 15 purchase order lines. The query returns the PRODNR and average QUANTITY of each product that has less than 15 purchase order lines. Question 4 Courses 0 out of 1 points 1 out of 1 points 0 out of 1 points 0 out of 1 points Ziyu Zhou 12019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791000_1&course_id=_310969_1&content_id=_9341638_1&… 2/6 Selected Answer: Correct Answer: PRODUCT_ID NUMBER PK NAME VARCHAR2(30) LIST_PRICE NUMBER(7,2) COST NUMBER(7,2) The PRODUCT table contains these columns: 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 the session. Which of the DML statements in this script performed either an INSERT, UPDATE, or DELETE that affected at least one row? all of the DML operations the INSERT statements, the first UPDATE statement, and the DELETE statement Question 5 Selected Answer: True Correct Answer: True One of the major advantages of stored procedures is that they can be used to encapsulate and represent business transactions. Question 6 Selected Answer: b. Correct Answer: b. The query to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE, and V_CONTACT fields from the VENDOR table where the values of V_CODE match is _____. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; Question 7 Selected Answer: Correct Answer: Response Feedback: Which SELECT statement will display the next value of the PARTS_ID_SEQ sequence by actually retrieving the value from the sequence? SELECT parts_id_seq.NEXTVAL FROM inventory; SELECT parts_id_seq.NEXTVAL FROM SYS.DUAL; Explanation: The f tement will display the next value of the SQL> SELECT parts_id_seq.NEXTVAL FROM SYS.DUAL; Because a sequence is not tied directly to a table, sequence numbers are stored and generated independently of tables. To view the next value of the PARTS_ID_SEQ sequence, you can query the NEXTVAL pseudocolumn using the DUAL dummy table in the SYS schema. You must reference the PARTS_ID_SEQ sequence and the NEXTVAL pseudocolumn as parts_id_seq.NEXTVAL in the select list. NEXTVAL is a pseudocolumn and not a function. Therefore, both SELECT statements that include the SELECT NEXTVAL (parts_id_seq) clause will return a syntax error. While the SELECT statement that includes SELECT parts_id_seq.NEXTVAL and queries the INVENTORY table will successfully access the sequence, it will return a sequence number for each row in the INVENTORY table. In this scenario, you only wanted to return the next value of the sequence so this option is incorrect. The SELECT statement that includes the SELECT parts_id_seq NEXTVAL clause is also incorrect because it contains invalid syntax for referencing the NEXTVAL pseudocolumn. Question 8 Selected Answer: b. Correct Answer: b. When using the Oracle TO_DATE function, the code _____ represents a three-letter month name. MON MON 1 out of 1 points 1 out of 1 points 0 out of 1 points 1 out of 1 points2019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791000_1&course_id=_310969_1&content_id=_9341638_1&… 3/6 Question 9 Selected Answer: False Correct Answer: False MySQL allows multiple triggering conditions per trigger. Question 10 Selected Answer: a. Correct Answer: a. The _____ statement combines rows from two queries and excludes duplicates. UNION UNION Question 11 Selected Answer: d. Correct Answer: d. The special operator used to check whether an attribute value is within a range of values is _____. BETWEEN BETWEEN Question 12 Selected Answer: Correct Answer: Response Review the structure and the data contained in the EMPLOYEE_MASTER table. The structure of the EMPLOYEE_MASTER table is as follows: The data in the EMPLOYEE_MASTER table is as follows: Employees are given performance reviews once a year and that results in a rating between 1 (lowest) and 5 (highest). This rating and the current salary are stored i EMPLOYEE_MASTER table. Proposed new salaries are based upon the current salary and the performance rating. If an employee received a performance rating of 5 proposed new salary would increase the current salary by 5%. Performance ratings of 4 would result in a 3% proposed increase over the current salary, and a rating increase the current salary by 2%. Employees with a rating of 2 or 1 would not receive an increase. The finance department has been requested to print a report showing each employee_ id, name, current_salary, proposed_salary, job_code, and mgr_id. This report NOT include employees who work in sales, nor should it include the CEO due to privacy concerns. Sales employees can be identified by a job_code of either 25 or 2 The CEO does not have a manager, so for the purpose of storing data into the table it appears the CEO's manager is himself. While all other employees have a mgr another employee in the company, the CEO has a mgr_id value identical to his employee_id. Which one of the following SELECT statements will produce the report as it has been defined? WHEN 5 THEN 1.05*current_salary WHEN 4 THEN 1.03*current_salary WHEN 3 then 1.02*current_salary ELSE current_salary END "Propos SQL> SELECT employee_id, name, current_salary, CASE performance_rating job_code, mgr_id FROM employee_master WHERE job_code NOT IN (25,27) AND NULLIF(employee_id,mgr_id) IS NOT NULL; WHEN 5 THEN 1.05*current_salary WHEN 4 THEN 1.03*current_salary WHEN 3 then 1.02*current_salary ELSE current_salary END "Propos SQL> SELECT employee_id, name, current_salary, CASE performance_rating job_code, mgr_id FROM employee_master WHERE job_code NOT IN (25,27) AND NULLIF(employee_id,mgr_id) IS NOT NULL; 1 out of 1 points 1 out of 1 points 1 out of 1 points 1 out of 1 points2019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791000_1&course_id=_310969_1&content_id=_9341638_1&… 4/6 Feedback: The correct answer contains the WHERE clause job_code NOT IN (25,27). This eliminates only job codes of 25 or 27. Because the employee_id and mg are equal only for the CEO, the NULLIF(employee_id,mgr_id) function returnsaNULLonlyfortheCEO,andeveryoneelsereturnsaNOT NULL.Thenetresultdisplayseveryonewhoisnota salesperson or a CEO. Two of the four choices contain SELECT clauses with incorrect syntax. The format of the CASE construct is to place the keyword CASE first, followed by column whose value you are testing, followed by the keyword WHEN. The following statements will return a syntax error because they do not have th order for SELECT . . . CASE . . . WHEN: SQL> SELECT employee_id, name, current_salary, performance_rating CASE WHEN 5 THEN 1.05*current_salary and SQL> SELECT employee_id, name, current_salary, CASE WHEN performance_rating = 5 THEN 1.05*current_salary The statement containing the clause WHERE job_code NOT BETWEEN (25,27) produces a syntax error because you cannot write the NOT BETWEEN cla NOT BETWEEN(25,27). The correct syntax would be WHERE job_code IS NOT BETWEEN 25 AND 27. However, this statement would still contain a logic job code 26 would be filtered out by the WHEN clause, which is inconsistent with the requirements of the scenario. Question 13 Selected Answers: Correct Answers: Response Feedback: CREATE TABLE customer ( customer_id NUMBER, company_id VARCHAR2(30), contact_name VARCHAR2(30), contact_title VARCHAR2(20), address VARCHAR2(3 Evaluate this CREATE TABLE statement: 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.) Company identification values could be either numbers or characters, or a combination of both. Phone number values can range from 0 to 20 characters so the column should be variable in length. The value 25 should be used if no value is provided for the country identification when a record is inserted. Company identification values could be either numbers or characters, or a combination of both. Phone number values can range from 0 to 20 characters so the column should be variable in length. The value 25 should be used if no value is provided for the country identification when a record is inserted. ThegivenCREATE TABLEstatementwillaccomplishthefollowingthreebusinessrequirements: � Company identification values could be either numbers or characters, or a combination of both. � Phone number values can range from 0 to 20 characters so the column should be variable in length. � The value 25 should be used if no value is provided for the country identification when a record is inserted. Defining the COMPANY_ID column as a VARCHAR2 datatype allows for character data, including A-Z, a-z, and 0-9. Defining the PHONE colum in the CUSTOMER table as a VARCHAR2 datatype provides for variable-length character data. In this example, phone number values can be up 20 characters in length, but the size of the column will vary depending on each row value. 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 preven 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 datatype 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 COMPANY_ID value of datatype VARCHAR2(30) creates a variable-length character column of 30 bytes. Therefore, the business ru requiring a six-byte fixed-length column is not met. Question 14 Selected Answer: True Correct Answer: False Stored procedures must have at least one argument. Question 15 Selected Answer: Which action will cause an automatic rollback? 1 out of 1 points 0 out of 1 points 0 out of 1 points2019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791000_1&course_id=_310969_1&content_id=_9341638_1&… 5/6 Tuesday, March 5, 2019 2:23:18 PM EST Correct Answer: a SAVEPOINT statement a system crash Question 16 Selected Answer: Correct Answer: 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? An error occurs because both the STU_TYPE_ID and ENROLL_DATE columns are not included in the subquery select list. An error occurs because the STU_TYPE_ID column is not included in the subquery select list. Question 17 Selected Answer: Correct Answer: The following table with purchase orders is created: CREATE TABEL PURCHASE_ORDER ( PONR CHAR(7) NOT NULL PRIMARY KEY, PODATE DATE, SUPNR CHAR(4) NOT NULL, FOREIGN KEY (SUPNR) REFERENCES SUPPLIER SUPNR ON DELETE CASCADE ON UPDATE CASCADE); What happens upon deletion of a supplier? The SUPNR of this supplier is replaced by a NULL value in PURCHASE_ORDER. All purchase order records tied to that supplier are also deleted. Question 18 Selected Answer: True Correct Answer: False Cursors are held in a reserved memory area in the client computer. Question 19 Selected Answer: Correct Answer: Given following query: SELECT R.SUPNAME, ( SELECT COUNT(PO.PODATE) FROM PURCHASE_ORDER PO WHERE R.SUPNR = PO.SUPNR) AS SUMMARY FROM SUPPLIER R The query selects: The supplier name and order date of each of his/her outstanding orders. If a supplier does not have an outstanding order, s/he will be included in the output with a null value for the "SUMMARY" column. The name and total number of outstanding orders of all suppliers Question 20 Selected Answer: False Correct Answer: False Mathematical operators cannot be used to place restrictions on character-based attributes. 0 out of 1 points 0 out of 1 points 0 out of 1 points 0 out of 1 points 1 out of 1 points2019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791000_1&course_id=_310969_1&content_id=_9341638_1&… 6/6 ← OK201901_Database Management Systems_CSCI_6441_10 Tests Review Test Submission: Quiz04 Review Test Submission: Quiz04 User Tianyu Ge Course 201901_Database Management Systems_CSCI_6441_10 Test Quiz04 Started 3/5/19 2:09 PM Submitted 3/5/19 2:34 PM Due Date 3/6/19 11:59 PM Status Completed Attempt Score 13 out of 20 points Time Elapsed 25 minutes out of 2 hours Results Displayed Submitted Answers, Correct Answers, Feedback, Incorrectly Answered Questions Question 1 Selected Answer: Correct Answer: For which column would you create an index? a column that is updated frequently a column containing a wide range of values Question 2 Examine the structure of the LINE_ITEM table. You attempt to query the database with this SQL statement: SELECT order_id "Order Number", product_id "Product", quantity "Amount" FROM line_item WHERE "Order Number" = 5570 ORDER BY "Amount"; This statement fails when executed. Which action should you take to correct the problem? Courses 0 out of 1 points 0 out of 1 points Tianyu GeSelected Answer: Correct Answer: Remove the column alias from the ORDER BY clause and use the column name. Remove the column alias from the WHERE clause and use the column name. Question 3 Selected Answer: True Correct Answer: True String comparisons are made from left to right. Question 4 Selected Answer: True Correct Answer: True A relational join operation merges rows from two tables. Question 5 Selected Answer: Correct Answer: SQL> CREATE PUBLIC SYNONYM part FOR linda.product; You issue this statement: Which task was accomplished by this statement? A new object privilege was assigned. The need to qualify an object name with its schema was eliminated. Question 6 Examine the data in the PRODUCT table. You query the PRODUCT table with this SQL statement: 1 out of 1 points 1 out of 1 points 0 out of 1 points 0 out of 1 pointsSelected Answer: Correct Answer: SELECT description FROM product ORDER BY manufacturer_id, quantity ASC; What is the DESCRIPTION value of the first row displayed? AA 2pk­battery C 2pk­battery Question 7 Selected Answer: a. Correct Answer: a. Which of the following queries is used to list a unique value for V_CODE, where the list will produce only a list of those values that are different from one another? SELECT DISTINCT V_CODE FROM PRODUCT; SELECT DISTINCT V_CODE FROM PRODUCT; Question 8 Selected Answers: Correct Answers: Which two statements about a column are true? (Choose two.) You cannot increase the width of a VARCHAR2 column. You cannot specify the column's position when adding a new column to a table. You cannot specify the column's position when adding a new column to a table. You cannot modify the datatype of a column if the column contains non­null data. Question 9 Selected Answer: False Correct Answer: False "Linked SQL" is a term used to refer to SQL statements that are contained within an application programming language such as COBOL, C++, ASP, Java, or ColdFusion. Question 10 Selected Answers: Which two DML statements could you use to modify the contents of the PRODUCT_NAME column of the existing PRODUCT table? (Choose two.) 1 out of 1 points 0 out of 1 points 1 out of 1 points 1 out of 1 pointsCorrect Answers: Response Feedback: 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 11 Selected Answer: True Correct Answer: True If you have not yet used the COMMIT command to store the changes permanently in the database, you can restore the database to its previous condition with the ROLLBACK command. Question 12 Selected Answer: d. Correct Answer: d. The special operator used to check whether a subquery returns any rows is _____. EXISTS EXISTS Question 13 Selected Answer: b. Correct Answer: b. The following SQL statement uses a(n) SELECT P_CODE, P_DESCRIPT, P_PRICE_V_ NAME FROM PRODUCT, VENDOR _ WHERE PRODUCT,V_CODE=VENDOR. V CODE "old­style" join "old­style" join 1 out of 1 points 1 out of 1 points 1 out of 1 pointsQuestion 14 Selected Answer: Correct Answer: Response Feedback: Which construct can be used to return data based on an unknown condition? a subquery a subquery A subquery can be used to return data based on an unknown condition. Often when the condition for a query cannot be stated directly, the query can be broken into two smaller queries to return the desired result. The subquery, or inner query, returns a value that is used by the main, or outer, query. AGROUP BYclausecreatesgroupsofdatasothataggregatecalculations,suchassumsandaverages,canbeperformed onthegroup.AnORDER BYclausesortstheresultsofaquerybasedonaspecifiedsortorder.AWHEREclause,whetherit includes a logical conditional operator or not, defines a condition that must be met for rows to be returned. None of these constructs will allow you to return data based on an unknown condition. Question 15 Selected Answer: Correct Answer: Response Feedback: 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? UNION UNION SELECT emp_id FROM emp WHERE hire_date > TO_DATE('10­JAN­2007') UNION SELECT emp_id FROM emp_hist; You should use the UNION operator 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. The following SQL statement will achieve this result set: Set operators allow the results of two or more queries to be combined into a single result set. SQL statements that include set operators are known as compound queries. The set operators are: � UNION ­ returns the result sets from all the queries after eliminating any duplicate records � UNION ALL ­ returns the result sets from all the queries in a statement including the duplicate records � INTERSECT ­ returns only the common result sets that are retrieved by all the queries � MINUS ­ returns only the results that are returned by the first query and not by the second query. Set operators of equal precedence are evaluated from left to right unless parentheses force the order of evaluation. When using a set operator, the columns in the SELECT list in each query must be the same in number and data type. Question 16 1 out of 1 points 1 out of 1 points 1 out of 1 pointsSelected Answers: Correct Answers: Response Feedback: Which statements about datatypes are true? (Choose all that apply.) The CHAR datatype should be used for fixed­length character data. The INTERVAL YEAR TO MONTH datatype allows time to be stored as an interval of years and months. The CHAR datatype should be used for fixed­length character data. The INTERVAL YEAR TO MONTH datatype allows time to be stored as an interval of years and months. The CHAR datatype should be used for fixed­length character data. The length of the column is specified in bytes. The minimum size is 1, and the maximum size is 2000. The INTERVAL YEAR TO MONTH datatype stores a period of time using the YEAR and MONTH datetime fields. For example, INTERVAL '315­5' YEAR(3) TO MONTH indicates an interval of 315 years and 5 months. The TIMESTAMP datatype is an extension of the DATE datatype and is used to store time as a date with fractional seconds. The BLOB datatype stores binary data up to four gigabytes, and the CLOB datatype stores character data up to four gigabytes. The VARCHAR2 datatype does not require that a minimum size be specified, but it does require that a maximum size be specified. Question 17 Selected Answers: Correct Answers: Which statements concerning the creation of a view are TRUE? (Choose all that apply.) A constraint name must be provided when using the WITH CHECK OPTION clause or the statement will fail. View columns that are the result of derived values must be given a column alias. A view may have column names that are different from the actual base table(s) column names by using column aliases. Question 18 Examine the data in the TEACHER table. 0 out of 1 points 0 out of 1 pointsSelected Answer: Correct Answer: Assume the user enters the following SELECT statement to retrieve data from the TEACHER table: SQL> SELECT * FROM teacher WHERE INSTR(subject_id, '&1') = 4 AND LOWER(subject_id) LIKE 'HST%'; When prompted for the WHERE clause value, you enter an underscore (_). Which result will this statement provide? It will display information on all teachers whose SUBJECT_ID begins with 'HST_', regardless of the case in which the SUBJECT_ID is stored. It will execute, but it will not retrieve any data. Question 19 Selected Answer: Correct Answer: Response SQL> CREATE TABLE georgia (id name location credit_limit number(5), varchar2(25), varchar2(20), number(9,2)); SQL> CREATE VIEW southeast AS SELECT * FROM georgia The following four tables are created and then a view is also created: The statement creates a composite non­unique index. Item: 119 (Ref:1Z0­051.11.1.3) SQL> CREATE TABLE florida AS SELECT * FROM georgia; SQL> CREATE TABLE alabama AS SELECT * FROM georgia; SQL> CREATE TABLE MISSISSIPPI AS SELECT * FROM georgia; UNION SELECT * FROM florida UNION SELECT * FROM alabama UNION SELECT * FROM mississippi; Is it possible to perform DML operations on the VIEW called southeast? No, because a view created with the UNION set operator will always be non­updateable. No, because a view created with the UNION set operator will always be non­updateable. 1 out of 1 pointsTuesday, March 5, 2019 2:34:46 PM EST Feedback: You cannot perform DML operations on the VIEW called southeast because a view created with any set operator (UNION, MINUS, INTERSECT, etc.) will always be a non­updateable view, and hence no DML operations will be possible. The WITH CHECK OPTION, when added to the CREATE VIEW statement, prohibits the user from inserting or updating a row in the underlying table(s) if that row would be invisible when queried from the view. However, in this command, this is moot since a view created with set operators is never updateable. The WITH READ ONLY, when added to the CREATE VIEW statement, prohibits the user from inserting, updating, or deleting a row in the underlying table(s). However, in this command, this is moot since a view created with set operators is never updateable. For tables in a SELECT statement to be combined together by set operators, the tables have the same number of columns and that the columns match in terms of datatype. However, in this command, this is moot since a view created with set operators is never updateable. Question 20 Selected Answer: a. Correct Answer: a. The SQL command that allows a user to permanently save data changes is _____. COMMIT COMMIT ← OK 1 out of 1 points2019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791512_1&course_id=_310969_1&content_id=_9341638_1&… 1/9 201901_Database Management Systems_CSCI_6441_10 Tests Review Test Submission: Quiz04 Review Test Submission: Quiz04 User Ziyu Zhou Course 201901_Database Management Systems_CSCI_6441_10 Test Quiz04 Started 3/5/19 2:24 PM Submitted 3/5/19 2:56 PM Due Date 3/6/19 11:59 PM Status Completed Attempt Score 13 out of 20 points Time Elapsed 32 minutes out of 2 hours Results Displayed Submitted Answers, Correct Answers, Feedback, Incorrectly Answered Questions Question 1 Selected Answer: Correct Answer: Response Feedback: In which situation would you use a natural join? The tables being joined each have two columns with the same name and compatible datatypes, and you want to join on both of the columns. The tables being joined each have two columns with the same name and compatible datatypes, and you want to join on both of the columns. Natural joins are used to join tables based on all columns in the tables that have the same name. When implementing a natural join, columns with the same names must have compatible datatypes. Natural joins would be appropriate if the tables being joined have two columns with the same name and compatible datatypes, and you want to join on both of the columns. Natural joins can be implemented regardless of whether the tables being joined have primary and foreign keys defined. Because this has no effect on natural joins, the option stating that primary and foreign key definitions are relevant is incorrect. The option stating that a natural join would be used to join tables having matching columns with different names but compatibledatatypesisincorrect.ThesyntaxoftheNATURAL JOINstatementdoesnotincludecolumnnames(onlytable names), and the join is created using all columns with the same names. Columns with different names cannot be joined using a natural join. Natural joins cannot be used to join columns with incompatible datatypes. All columns with the same name must have compatible datatypes, or an error occurs. Therefore, the option stating that a natural join would be used to join columns with incompatible datatypes is incorrect. The option stating that a natural join would be used when the tables being joined have two columns with the same name and compatible datatypes, and you want to join on one of the columns is incorrect. A natural join will use all common columns in the join condition. If you need to restrict the join condition to only one of the same-named Courses 1 out of 1 points Ziyu Zhou 12019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791512_1&course_id=_310969_1&content_id=_9341638_1&… 2/9 columns, you would not use a natural join. Rather, you would use a simple join with a USING clause, a simple join with an ON clause, or specify the join condition in the WHERE clause with Oracle proprietary syntax. Question 2 Selected Answer: Correct Answer: Response Feedback: The PHYSICIAN table contains these columns: PHYSICIAN_ID NUMBER NOT NULL PK LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(25) NOT NULL LICENSE_NO NUMBER(7) NOT NULL HIRE_DATE DATE When new physician records are added, the PHYSICIAN_ID is assigned a sequential value using the PHY_NUM_SEQ sequence. The state licensing board assigns license numbers with valid license numbers being from 1000000 to 9900000. You want to create an INSERT statement that will prompt the user for each physician's name and license number and insert the physician's record into the PHYSICIAN table with a hire date of today. The statement should generate an error if an invalid license number is entered. Which INSERT statement should you use? INSERT INTO (SELECT physician_id, last_name, first_name, license_no, hire_date FROM physician WITH CHECK OPTION WHERE license_no BETWEEN 1000000 and 9900000) VALUES (phy_num_seq.NEXTVAL, '&lname', '&fname', &lno, sysdate); INSERT INTO (SELECT physician_id, last_name, first_name, license_no, hire_date FROM physician WITH CHECK OPTION WHERE license_no BETWEEN 1000000 and 9900000) VALUES (phy_num_seq.NEXTVAL, '&lname', '&fname', &lno, sysdate); Toperformthenecessaryinsert,youshouldusetheINSERTstatementthatusesasubqueryincludingtheWITH CHECK OPTION keyword to identify the table for the insert and uses phy_num_seq.NEXTVAL as the value to be inserted for PHYSICIAN_ID. When using a subquery for the table of a Data Manipulation Language (DML) statement, the WITH CHECK OPTION keyword can be used to ensure that the DML statement is not allowed if the change would generate rows that are not included in the subquery. The INSERT statement that includes a WHERE clause is incorrect because a WHERE clause is not allowed with an INSERT statement. The INSERT statement that uses the BETWEEN operator in the VALUES clause is incorrect because the BETWEEN operator cannot be used in a VALUES clause. The INSERT statement that uses phy_num_seq.VALUE as the value inserted into the PHYSICIAN_ID column is incorrect and will cause an error. To generate the next sequence value from the PHY_NUM_SEQ sequence, you should use the NEXTVAL keyword. The INSERT statement that does not include single quotation marks around the &lname and &fname substitution variables is incorrect because character and date substitution variables should be enclosed in single quotation marks. The INSERT statement that uses &phy_num_seq as the value to be inserted for PHYSICIAN_ID will prompt the user for a value for PHYSICIAN_ID, rather than using the sequence as desired. Therefore, this option is incorrect. 1 out of 1 points2019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791512_1&course_id=_310969_1&content_id=_9341638_1&… 3/9 Question 3 Selected Answer: b. Correct Answer: b. The Oracle _____ function compares an attribute or expression with a series of values and returns an associated value or a default value if no match is found. DECODE DECODE Question 4 Selected Answer: b. Correct Answer: b. In the context of SELECT subquery types, a _____ is returned when an UPDATE subquery is used. single value single value Question 5 Selected Answer: Correct Answer: Which SELECT statement should you use to limit the display of account information to those accounts with a finance charge greater than $75.00? SELECT account_id, new_balance, finance_charge FROM account WHERE finance_charge > 75.00 GROUP BY finance_charge; SELECT account_id, new_balance, finance_charge FROM account WHERE finance_charge > 75.00; Question 6 Selected Answer: Correct Answer: Which statement regarding subqueries is true? A subquery cannot reference a table that is not included in the outer query's FROM clause. Subqueries can return multiple columns. Question 7 Evaluate this SQL statement: SELECT l.order_id, i.description, l.quantity WHERE i.id_number = l.product_id FROM inventory i, line_item l ORDER BY l.order_id, i.description; This statement fails when executed. Which change should you make to correct the problem? 1 out of 1 points 1 out of 1 points 0 out of 1 points 0 out of 1 points 0 out of 1 points2019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791512_1&course_id=_310969_1&content_id=_9341638_1&… 4/9 Selected Answer: Correct Answer: Remove the table alias from the ORDER BY clause, and use only the column name. Reorder the clauses in the statement. Question 8 Selected Answers: Correct Answers: Which statements about datatypes are true? (Choose all that apply.) The CHAR datatype should be used for fixed-length character data. The VARCHAR2 datatype requires that a minimum size be specified when defining a column of this type. The CHAR datatype should be used for fixed-length character data. The INTERVAL YEAR TO MONTH datatype allows time to be stored as an interval of years and months. Question 9 Selected Answer: b. Correct Answer: b. Which of the following queries will list all the rows in which the inventory stock dates occur on or after January 20, 2016? SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= '20-JAN-2016'; SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= '20-JAN-2016'; Question 10 Selected Answer: c. Correct Answer: c. A(n) _____ is a query that is embedded (or nested) inside another query. subquery subquery Question 11 Selected Answer: a. Correct Answer: a. The special operator used to check whether an attribute value is within a range of values is _____. BETWEEN BETWEEN Question 12 0 out of 1 points 1 out of 1 points 1 out of 1 points 1 out of 1 points 1 out of 1 points2019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791512_1&course_id=_310969_1&content_id=_9341638_1&… 5/9 Selected Answer: b. Correct Answer: b. The query to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE, and V_CONTACT fields from the VENDOR table where the values of V_CODE match is _____. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; Question 13 Selected Answer: True Correct Answer: True The most useful feature of PL/SQL blocks is that they let a designer create code that can be named, stored, and executed by the DBMS. Question 14 Selected Answer: Correct Answer: What does the following query return? SELECT PL.PRODNR, P.PRODNAME FROM po_line PL, PRODUCT P WHERE PL.PRODNR=P.PRODNR GROUP BY PL.PRODNR HAVING SUM(PL.QUANTITY) > 5 The product numbers and names of all products for which the total order quantity exceeds 5. The product numbers and names of all products for which the total order quantity exceeds 5. Question 15 Selected Answer: False Correct Answer: False String manipulation functions are rarely used in programming Question 16 Examine the structure of the EMPLOYEE table. 1 out of 1 points 1 out of 1 points 1 out of 1 points 0 out of 1 points2019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791512_1&course_id=_310969_1&content_id=_9341638_1&… 6/9 Selected Answer: Correct Answer: 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? SELECT employee_id, emp_lname, emp_fname, dept_id FROM employee_hist WHERE (employee_id, dept_id) = (SELECT employee_id, dept_id FROM employee_hist WHERE dept_id = 30 AND job_id = 1) AND job_id = 6; 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; Question 17 Selected Answers: Correct Answers: Which of the following statements are true about views? (Select all that apply.) A view does not store any data unless it is created by a SELECT statement joining a minimum of two tables together. The CREATE OR REPLACE clause will create a view if none exists with that name, or replace the definition of an existing view with the verbiage in the new SELECT statement. It is not permissible to create a view containing a grouping function or clause such as AVG, SUM, or GROUP BY. The wording of the SELECT statement which defines the view is stored in the data dictionary. You cannot change the definition of a view (the SELECT statement) by executing the ALTER VIEW command. The CREATE OR REPLACE clause will create a view if none exists with that name, or replace the definition of an existing view with the verbiage in the new SELECT statement. 0 out of 1 points2019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791512_1&course_id=_310969_1&content_id=_9341638_1&… 7/9 Question 18 Selected Answer: Correct Answer: Response Feedback: AND LENGTH(LOWER(job_title)); Review the structure and the rows in the emp50 table. The user issues the following statement: SQL>SELECT REPLACE(SUBSTR(name,1,6),'W','b') FROM emp50 WHERE LENGTH(name) BETWEEN job_code What will be the outcome of executing this statement? The SELECT statement will return three rows, namely: bilbur byona bobo S The SELECT statement will return three rows, namely: bilbur byona bobo S The best approach to this problem is to first determine which rows satisfy the WHERE condition. To do this, I calculated the LENGTH(name) as well as LENGTH(LOWER(job_title)). I included these two results along with the id and job_code for each row in the EMP50 table. This information appears in the following chart: id LENGTH(name) job_code LENGTH(LOWER(job_title)) --- ------------------------- ---------------- ---------------------------------------- 5012 6 10 5113 6 13 5210 4 12 5210 12 12 555 12 12 5610 5 15 For id 50, and for each subsequent row, the question is whether LENGTH(name) is between the job_code and the LENGTH (LOWER(job_title)) value. Here are the results for each row: For id=50, is 12 between 6 and 10? FALSE For id =51, is 13 between 6 and 13? TRUE (since Oracle's BETWEEN is inclusive at both ends) For id =52, is 10 between 4 and 12? TRUE For id =52, is 10 between 12 and 12? FALSE 1 out of 1 points2019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791512_1&course_id=_310969_1&content_id=_9341638_1&… 8/9 For id =55, is 5 between 12 and 12? FALSE For id =56, is 10 between 5 and 15? TRUE Only three rows meet the condition specified in the WHERE clause, so only three rows will be returned. For those three rows only, you need to calculate the results of the SUBSTR and REPLACE functions being performed on the name column. The first row in the table where the condition is TRUE has an id of 51. For an id of 51, SUBSTR('Wilbur Wright',1,6) returns 'Wilbur', and SUBSTR('Wilbur','W','b') returns 'bilbur'. The second row in the table where the condition is TRUE has an id of 52 (the first occurrence). For an id of 52: SUBSTR ('Wyona Judd',1,6) returns 'Wyona ', and SUBSTR('Wyona ','W','b') returns 'byona '. ThethirdrowinthetablewheretheconditionisTRUEhasanidof56.Foranidof56,SUBSTR('WoWo Smith',1,6) returns 'WoWo S', and SUBSTR('WoWo S','W','b') returns 'bobo S'. These three results represent the correct output to the SQL statement. The SUBSTR function can accept three arguments, so that is not an error. There is no mismatch in the number of parentheses. They are also properly nested. You can perform the LENGTH of the LOWER of job_title. The LOWER function would be performed first, which would convert any caps in the job_title column to their lower case equivalents. Then the LENGTH function would count the number of characters in that modified job_title value. TheSELECT statement will NOT return zero rows. As you can see from above, three rows produce a TRUE value from the WHERE clause, so three rows will be displayed. Question 19 Selected Answer: Correct Answer: Given the following SQL query: SELECT P1.PRODNR FROM PRODUCT P1 WHERE 5 <= ( SELECT COUNT(*) FROM PRODUCT P2 WHERE P1.PRODNR < P2.PRODNR) This query selects: All product numbers except for the 5 highest product numbers. All product numbers except for the 5 highest product numbers. Question 20 Selected Answer: Evaluate this SQL statement: SELECT line_item_id, order_id, product_id FROM line_item Which WHERE clause should you include to limit the query results to rows that have a QUANTITY column that has a null value? WHERE quantity != NULL; 1 out of 1 points 0 out of 1 points2019/3/5 Review Test Submission: Quiz04 – 201901_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_19791512_1&course_id=_310969_1&content_id=_9341638_1&… 9/9 Tuesday, March 5, 2019 2:56:32 PM EST Correct Answer: WHERE quantity IS NULL; [Show More]

Last updated: 2 years ago

Preview 1 out of 231 pages