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
... [Show More] 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 Dallasbased 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 Dallasbased 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 Dallasbased 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 2pkbattery C 2pkbattery 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 nonnull 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 "oldstyle" join "oldstyle" 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 [Show Less]