George Washington University CSCI 6441Quiz3_merged6 ( with all correct answers shown)

Document Content and Description Below

Which code returns the date in the format Friday, April 20th, 2019? DATE_FORMAT('2019-04-20', '%W, %M %D, %Y') DATE_FORMAT('2019-04-20', '%W, %M %D, %Y') Question 2 Selected Answer: Correct Ans... wer: Which statement is NOT CORRECT? The management of data storage is more expensive than the purchase of storage hardware, which is why businesses opt for enterprise storage subsystems. Although SCSI has been a popular medium for connecting storage devices to servers and/or processors in the past, nowadays, it is not used anymore for high performance and high capacity workstations and servers. Question 3 Selected Answer: b. Correct Answer: b. Which of the following types of SQL statements isn’t a DML statement? Create Table Create Table Question 4 Selected Answer: c. Correct Answer: c. Which of the following statements about sorting the rows in a result set is not true? A column that’s used for sorting must be included in the SELECT clause. A column that’s used for sorting must be included in the SELECT clause. Question 5 Selected Answer: d. Correct Answer: d. Which of the following can you not specify for a column when you create a table using the CREATE TABLE statement? value value Question 6 Selected Answer: True Correct Answer: True Some RDBMSs, such as Microsoft Access, automatically make the necessary conversions to eliminate case sensitivity. Question 7 Courses Hang Y1u 0.5 out of 0.5 points 0 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22222085_1&course_id=_331737_1&content_id=_10142480_1… 2/9 Selected Answer: c. Correct Answer: c. Assuming that a stored procedure starts with the code that follows, which of the following statements calls the procedure and passes the values 47B and 200 to it? CREATE OR REPLACE PROCEDURE update_credits ( in_param VARCHAR(50), cr_param DECIMAL(9,2) ) AS CALL update_credits('47B', 200); CALL update_credits('47B', 200); Question 8 Selected Answer: d. Correct Answer: d. Response Feedback: The total_gross column of the theater table can be derived from the gross_receipt table. To keep the value of this column consistent during modiÑcations to the gross_receipt table, which type of construct will you create? database trigger database trigger You will create a database trigger. A common and beneÑcial use for database triggers is maintaining derived columns. The total_gross column of the theater table is a derived column. It can be calculated with data queried from another table in the database. The problem with derived columns is that if a change is made to the data of the other table, the values of the derived column are not consistent. The total gross per theater can be calculated by querying the gross_receipt table. A database trigger can be created on this table to automatically update the total_gross column of the theater table. All of the other options are incorrect. Packaged subprograms must be executed explicitly and are not feasible for this situation. Question 9 Selected Answer: True Correct Answer: False The security, privacy, and integrity of data in a database are of minimal concern to DBAs who manage current DBMS installations. Question 10 Selected Answer: d. Correct Answer: d. To enforce referential integrity for a delete operation, a MySQL database can a, b, or c a, b, or c Question 11 Selected Answer: Correct Answer: Which statement is CORRECT? The I/O boundary speciÑes whether or not the CPU waits on the data being retrieved. The I/O boundary speciÑes whether or not the CPU waits on the data being retrieved. Question 12 Selected Answer: b. Correct Answer: b. A user who’s granted the EXECUTE privilege can execute stored procedures or functions stored procedures or functions 0.5 out of 0.5 points 0 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22222085_1&course_id=_331737_1&content_id=_10142480_1… 3/9 Question 13 Selected Answer: d. Correct Answer: d. _____ is a cursor attribute that returns TRUE if the last FETCH returned a row, and FALSE if not. %FOUND %FOUND Question 14 Selected Answer: c. Correct Answer: c. Which of the following does not violate the referential integrity of a database? deleting a row in a foreign key table without deleting the related row in the related primary key table deleting a row in a foreign key table without deleting the related row in the related primary key table Question 15 Selected Answer: d. Correct Answer: d. Response Feedback: Examine this package: CREATE OR REPLACE PACKAGE theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE Ñnd_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); FUNCTION get_budget (v_studio_id IN NUMBER) RETURN NUMBER; END theater_pck; You issue this statement in SQL*Plus: EXECUTE theater_pck.current_avg_cost_per_ticket := 1 What is true about the state of current_avg_cost_per_ticket ? It is set to 10 in the current session, but potentially can be modiÑed again by that same session It is set to 10 in the current session, but potentially can be modiÑed again by that same session The current_avg_cost_per_ticket variable is set to 10 in the current session, but potentially can be modiÑed again by that same session. Package variables are those deÑned in the speciÑcation or in the declaration of the body. The state of a package variable persists throughout the current user session. Therefore, current_avg_cost_per_ticket remains 10 for the session duration, unless it is subsequently changed by that same session or the package is invalidated. All of the other options are incorrect because in this scenario the current_avg_cost_per_ticket variable is 10 for the duration of the current session. Question 16 Selected Answer: c. Correct Answer: c. You can use MySQL Workbench to do all but one of the following. Which one is it? Change the table privileges for a user Change the table privileges for a user Question 17 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22222085_1&course_id=_331737_1&content_id=_10142480_1… 4/9 Selected Answer: A. Correct Answer: A. Response Feedback: A user view in which the WHERE line of the SELECT statement used to create the view contains some predicate is called value-dependent value-dependent Complexity: Moderate Section: 8.4 Question 18 Selected Answer: d. Correct Answer: d. "A password must have a minimum of five characters" is an example of a _____. standard standard Question 19 Selected Answer: d. Correct Answer: c. If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery) a column of one or more rows a single value Question 20 Selected Answer: a. Correct Answer: a. If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery) a single value a single value Question 21 Selected Answer: d. Correct Answer: d. The _____ administrator is responsible for strategic planning. data data Question 22 Selected Answer: a. Correct Answer: a. What is the result of the function that follows? REGEXP_SUBSTR('TR1-268-468R', '[1-9]-[1-9]*[A-Z]') 8-468R 8-468R Question 23 Selected Answer: c. Correct Answer: c. Response Feedback: The four phases that indicate the diÖerent phases of SQL statement execution are as follows: 1. execute 2. fetch 3. parse 4. bind Which option represents the correct sequence of events while executing a statement? 3, 4, 1, 2 3, 4, 1, 2 The option that represents the correct sequence is 3, 4, 1, 2. The correct sequence of events for executing a SQL statement is as follows: 1. Parse phase: The SQL statement is parsed to check for privileges and syntax used. After this phase, a 0.5 out of 0.5 points 0 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22222085_1&course_id=_331737_1&content_id=_10142480_1… 5/9 parse tree is generated for the statement. 2. Bind phase: The SQL statement goes through the bind phase in which the variables are bound to the values speciÑed. 3. Execute phase: The SQL statement is executed and the operation is performed. This is the last phase for DML operations, such as INSERT , UPDATE , and DELETE , because no rows are returned at the end of these statements. However, the number of rows aÖected by the DML statement is known at this point. 4. Fetch Phase: The last phase for the SELECT statement is the fetch phase in which the number of rows satisfying the condition are fetched and returned to the user. All the other options are incorrect because they do not represent the correct sequence of events during SQL statement execution. Question 24 Selected Answer: b. Correct Answer: b. The _____ pseudo-column is used to select the next value from a sequence. NEXTVAL NEXTVAL Question 25 Selected Answer: Correct Answer: What will the following query return? SELECT SUPNAME, SUPADDRESS, SUPCITY FROM SUPPLIER R WHERE EXISTS (SELECT * FROM PRODUCT P WHERE NOT EXISTS (SELECT * FROM SUPPLIES S WHERE R.SUPNR = S.SUPNR AND P.PRODNR = S.PRODNR)) The supplier name, supplier address and supplier city of all suppliers who cannot supply all products. The supplier name, supplier address and supplier city of all suppliers who cannot supply all products. Question 26 Selected Answers: Correct Answers: ASSET ----------- ASSET_ID NUMBER(9) NOT NULL ASSET_VALUE FLOAT ASSET_DESCRIPTION VARCHAR2(25) DEPT_ID NUMBER(9) Examine the structures of the DEPARTMENT and ASSET tables: DEPARTMENT ------------------------- DEPT_ID NUMBER(9) NOT NULL DEPT_ABBR VARCHAR2(4) DEPT_NAME VARCHAR2(25) NOT NULL MGR_ID NUMBER UPDATE asset SET dept_id = (SELECT dept_id FROM department WHERE dept_name = (SELECT dept_name FROM department WHERE dept_abbr = 'FINC')), asset_value = 1000 The DEPT_ID column of the ASSET table has a FOREIGN KEY constraint referencing the DEPARTMENT table. You attempt to update the ASSET table using this statement: Which two of the following statements must be true for this UPDATE statement to execute without generating an error? (Choose two.) An asset with an ASSET_ID value of 2 must exist in the ASSET table. Only one row in the DEPARTMENT table can have a DEPT_ABBR value of FINC. Only one row in the DEPARTMENT table can have a DEPT_ABBR value of FINC. 0.5 out of 0.5 points 0.5 out of 0.5 points 0 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22222085_1&course_id=_331737_1&content_id=_10142480_1… 6/9 Only one row in the DEPARTMENT table can have the same DEPT_NAME value as the department with DEPT_ABBR of FINC. Question 27 Selected Answer: Correct Answer: Which of the following statement on join implementation in physical database organization is correct? The performance of the hash join depends on the size of the hash Ñle. The performance of the hash join depends on the size of the hash Ñle. Question 28 Selected Answer: Correct Answer: Which of the following queries gives as a result all suppliers excluding the suppliers from Dallas? SELECT SUPNR FROM SUPPLIER EXCEPT SELECT SUPNR FROM supplier WHERE SUPCITY='Dallas' SELECT SUPNR FROM SUPPLIER EXCEPT SELECT SUPNR FROM supplier WHERE SUPCITY='Dallas' Question 29 Selected Answer: a. Correct Answer: a. The six clauses of the SELECT statement must be coded in the following order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY Question 30 Selected Answer: C. Correct Answer: C. Response Feedback: An access control matrix is a means of implementing authorization authorization Complexity: Moderate Section: 8.3 Question 31 Selected Answer: c. Correct Answer: d. Which of the following statements would you use to assign the value “Test” to a variable named “message”? VARCHAR(message, 'Test') SET message = 'Test' Question 32 Selected Answers: b. c. Correct Answers: b. c. Response Feedback: You decide to use packages to logically group related programming constructs. Which two types of constructs can be grouped within a package? (Choose two.) cursor variable cursor variable 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22222085_1&course_id=_331737_1&content_id=_10142480_1… 7/9 A PL/SQL package can contain cursors and variables. A PL/SQL package can also contain types, constants, exceptions, and other PL/SQL subprograms. Though a package may contain a construct that references a view, the view itself, cannot be part of a package. Database and application triggers are constructs that cannot be part of a package. Question 33 Selected Answers: a. b. Correct Answers: c. d. Response Feedback: You own a table named gross_receipt that contains sales data. You have disabled certain database triggers on the gross_receipt table. Which two data dictionary views can you query to determine the status of all the triggers you have created on this table? (Choose two.) USER_SOURCE USER_STATUS USER_OBJECTS USER_TRIGGERS You can find the status of a trigger that you own by querying the USER_OBJECTS or the USER_TRIGGERS data dictionary view. Both views contain a column called status . In USER_OBJECTS , the possible values for the status column are VALID , INVALID , and N/A . In USER_TRIGGERS , the possible values for the status column are ENABLED or DISABLED . You could also query the ALL_OBJECTS or the ALL_TRIGGERS data dictionary view to display all triggers that you or other users have created. Or, if you have appropriate privileges, you could query the DBA_OBJECTS or the DBA_TRIGGERS data dictionary view to display all triggers for the gross_receipt table. The USER_SOURCE data dictionary view contains a text column that provides the source for database objects. However, the USER_SOURCE view does not contain information on triggers. USER_STATUS and USER_CONSTRUCTS are not valid data dictionary views. Question 34 Selected Answer: True Correct Answer: True SQL allows the use of logical restrictions on its inquiries such as OR, AND, and NOT. Question 35 Selected Answer: b. Correct Answer: b. Which of the following can be used with an updatable view? a join a join Question 36 Selected Answer: c. Correct Answer: c. Which of the following statements would you use to check if a parameter contains a null value and set it to a default value if it does? IF IF Question 37 0 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22222085_1&course_id=_331737_1&content_id=_10142480_1… 8/9 Selected Answer: a. Correct Answer: a. Response Feedback: Examine this code: CREATE OR REPLACE PACKAGE prod_pack IS g_tax_rate NUMBER := .08; END prod_pack; Which statement about this code is true? This package speciÑcation can exist without a body. This package speciÑcation can exist without a body. This package speciÑcation can exist without a body. A package speciÑcation is created using the CREATE OR REPLACE PACKAGE statemen t. Package speciÑcations without a body are used to create public variables and do not contain subprograms. All of the other options are incorrect because they are not true for the given package. Question 38 Selected Answer: d. Correct Answer: d. When you define a foreign key constraint, which of the following can you not specify? that the insertion of a row in a foreign key table that has a foreign key that isn’t matched in the primary key table should be cascaded up to the primary key table that the insertion of a row in a foreign key table that has a foreign key that isn’t matched in the primary key table should be cascaded up to the primary key table Question 39 Selected Answer: Correct Answer: Response Feedback: Examine the data in the PRODUCT table. You query the PRODUCT table with this SQL statement: SELECT description FROM product ORDER BY manufacturer_id, quantity ASC; What is the DESCRIPTION value of the first row displayed? C 2pk-battery C 2pk-battery TheDESCRIPTIONvalueofthefirstrowdisplayedisC 2pk-battery.WhenusinganORDER BYclausetosorttherows returned from a query, the default sort order is ascending. An ascending sort order displays values from lowest to highest for numeric data, from earliest to latest for date data, and alphabetically for character data. Because ascending is the default sort order,itisnotnecessarytoappendtheASCkeywordtotheORDER BYclause. 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22222085_1&course_id=_331737_1&content_id=_10142480_1… 9/9 Monday, March 23, 2020 4:10:39 PM EDT BecausethereisanORDER BYclauseontheMANUFACTURER_IDcolumn,theMANUFACTURER_IDcolumnvalueswillbe used to order the results, even though only the DESCRIPTION column is displayed. The first MANUFACTURER_ID value alphabetically is EL7968. Of the two rows with a MANUFACTURER_ID value of EL7968 in the PRODUCT table, the DESCRIPTIONvalueoftherowwiththelowestQUANTITYwillbedisplayedfirstbecausethereisalsoanORDER BYon QUANTITY. This row has a DESCRIPTION value of C 2pk-battery. The DESCRIPTION value of the first row displayed would be D 2pk-battery if the ORDER BY clause were on the DESCRIPTION column with a descending sort order. TheDESCRIPTIONvalueofthefirstrowdisplayedwouldbeAA 2pk-batteryiftheORDER BYclausewereonthe DESCRIPTION column with the default ascending sort order. The DESCRIPTION value of the first row displayed would be AAA 6pk-battery if a descending ORDER BY clause were used on both the MANUFACTURER_ID and the QUANTITY columns. Question 40 Selected Answer: D. Correct Answer: C. Response Feedback: Which of the following is NOT true of optimistic concurrency control techniques? They can cause rollback They use locks Complexity: easy Section: 9.6 ← OK 0 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480_… 1/19 202001_Database Management Systems_CSCI_6441_10 Tests Review Test Submission: Quiz03 Review Test Submission: Quiz03 User Hang Yu Course 202001_Database Management Systems_CSCI_6441_10 Test Quiz03 Started 3/23/20 3:13 PM Submitted 3/23/20 3:42 PM Due Date 3/23/20 11:59 PM Status Completed Attempt Score 16 out of 20 points Time Elapsed 29 minutes out of 2 hours Results Displayed Submitted Answers, Correct Answers, Feedback, Incorrectly Answered Questions Question 1 Selected Answer: 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? SELECT DISTINCT a.patient_id FROM admission a, admission a2 WHERE a.patient_id = a2.patient_id AND a.physician_id <> a2.physician_id; Courses Hang Y1u 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480_… 2/19 Correct Answer: Response Feedback: SELECT DISTINCT a.patient_id FROM admission a, admission a2 WHERE a.patient_id = a2.patient_id AND a.physician_id <> a2.physician_id; The following SQL statement will produce the list of all patients who have more than one physician: SELECT DISTINCT a.patient_id FROM admission a, admission a2 WHERE a.patient_id = a2.patient_id AND a.physician_id <> a2.physician_id; The equal (=) condition in the WHERE clause matches each patient with itself, and the not equal (<>) condition restricts the results to only those rows where the physicians are different. This results in a list of patients with more than one physician. However, duplicates are included. Using the DISTINCT keyword eliminates these duplicates. The statement that uses a subquery in the WHERE clause is incorrect. In this statement, the inner query executes first and returns all patients who have been admitted more than once. The outer query then returns all patients who have been admitted more than once. A patient might have been admitted more than one time, but with the same physician. Therefore, this query does not accurately return all patients with more than one physician. The statement that includes the COUNT function in the WHERE clause is incorrect and will generate an error because aggregate functions cannot be used in a WHERE clause. The statement that implements a full outer join is incorrect. Full outer joins join tables based on a common value, but include null values from both of the joined tables. In this scenario, the PATIENT and PHYSICIAN tables have no common column, and no ON clause was specified for the join. Therefore, an error will result. Question 2 Selected Answer: a. Correct Answer: a. Which of the following is the default date format for MySQL? yyyy-mm-dd yyyy-mm-dd Question 3 Selected Answer: True Correct Answer: True A database language enables the user to perform complex queries designed to transform the raw data into useful information. 0.5 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480_… 3/19 Question 4 Selected Answers: a. b. c. Correct Answers: b. c. d. Response Feedback: Examine this procedure: CREATE PROCEDURE ADD_THEATER IS BEGIN INSERT INTO theater VALUES (35, 'Riverplace Theatre', '1222 River Drive, Austin, TX'); END; Which three statements about this procedure are true? (Choose three.) The ADD_THEATER procedure is written in SQL. The ADD_THEATER procedure can be shared by multiple programs. The ADD_THEATER procedure will be created in the database as a schema object. The ADD_THEATER procedure can be shared by multiple programs. The ADD_THEATER procedure will be created in the database as a schema object. The ADD_THEATER procedure will execute with the privileges of its owner by default. The ADD_THEATER procedure can be shared by multiple programs, the ADD_THEATER procedure will be stored in the database as a schema object, and the ADD_THEATER procedure will execute with the privileges of its owner, by default. 0 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480_… 4/19 Procedures execute with the privileges of its owner, by default. This allows indirect access to database objects and allows data security. Users only need to be granted the privilege to execute the procedure. Procedures are stored in the database as schema objects. Because procedures are stored in the database in one location, they can be shared by multiple programs. The option stating that the ADD_THEATER procedure has three parts is incorrect because this procedure does not have an exception handler. The option stating that the procedure is written in SQL is incorrect because the procedure is written in PL/SQL. Question 5 Selected Answer: c. Correct Answer: c. In Oracle, _____ retrieves the current value of a sequence. CURRVAL CURRVAL Question 6 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 7 Selected Answer: Correct Answer: What does DASD stand for? Directly accessible storage device Directly accessible storage device Question 8 Selected Answer: c. Correct Answer: c. Which of the following statements do you use to delete all data from a table without deleting the definition for the table? TRUNCATE TABLE TRUNCATE TABLE 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480_… 5/19 Question 9 Selected Answer: D. Correct Answer: D. Response Feedback: In the relational model, a view can be used all of these all of these Complexity: Easy Section: 5.4 Question 10 Selected Answer: A. Correct Answer: A. Response Feedback: The SQL ALTER TABLE command can be used to do all of the following except drop the table drop the table Complexity: Easy Section: 5.3 Question 11 Selected 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.5 and the value in COLUMN2 will be 11. 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480_… 6/19 Correct Answer: Response Feedback: The value in COLUMN1 will be 3.5 and the value in COLUMN2 will be 11. The value in COLUMN1 will be 3.5 and the value in COLUMN2 will be 11. To calculate these values, you should first realize that the row with the name fred should not be included in the calculations because the data for this row does not satisfy the condition of the WHERE clause. The comparison for that row is fred = Fred, which is not true. The lengths for the names of the remaining rows are 3, 4, 3, and 4, respectively. The average of those four values is 3.5. The value of column2 is calculated by summing the position number of the second occurrence of "52" in the ssn column, with that search beginning at the second position. Remembering to disregard the fred row, the values of the positions of the second occurrence of 52 are 5, 0, 6, and 0. The sum of those values is 11. All of the other choices are incorrect since the sum and average of a group of numbers only produces a single result for the sum and a single result for the average. Since those values have already been shown to equal 3.5 for the average and 11 for the sum, any other values must be incorrect. Question 12 Selected Answer: c. Correct Answer: c. When coded in a WHERE clause, which search condition will return invoices when payment_date isn’t null and invoice_total is greater than or equal to $500? payment_date IS NOT NULL AND invoice_total >= 500 payment_date IS NOT NULL AND invoice_total >= 500 Question 13 Selected Answer: b. Correct Answer: b. Response Feedback: Which statement can be used to call an external procedure from within a trigger body? CALL CALL The CALL statement can be used to call an external procedure from within a trigger body. The external procedure 0.5 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480_… 7/19 called using the CALL statement can be either a C or Java procedure. The statement will be executed if the condition associated with the trigger evaluates to true. The following code shows an example of a trigger that uses the CALL statement to call the user_logon procedure (which was written in either C or Java): CREATE OR REPLACE TRIGGER on_logon AFTER LOGON ON DATABASE CALL user_logon(user); The RUN statement is an incorrect option because it is an invalid statement and is not used in Oracle. The EXECUTE statement is an incorrect option because it cannot be used to call an external procedure from within a trigger body. The EXECUTE statement is used to invoke a procedure from SQL*Plus. The EXECUTE IMMEDIATE statement is an incorrect option because it cannot be used to call an external procedure from within a trigger body. The EXECUTE IMMEDIATE statement is used to process native dynamic SQL statements. Question 14 Selected Answer: d. Correct Answer: d. Each of the following statements about triggers is true except for one. Which one is it? A trigger can’t raise errors. A trigger can’t raise errors. Question 15 Selected Answer: C. Correct Answer: C. Response Feedback: Given an Employee table with columns for salary and dept, to raise the salaries of all employees in the sales department by 10%, we could write UPDATE Employee SET salary = salary*1.10 WHERE dept='sales'; UPDATE Employee SET salary = salary*1.10 WHERE dept='sales'; Complexity: Moderate Section: 5.4 0.5 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480_… 8/19 Question 16 Selected Answer: Correct Answer: Which of the following statements on list data organization is not correct? In a tree data structure, the physical position of the records may express the tree structure. The records are then stored from left to right, top to bottom. A tree structure can also be implemented using a linked list. In this way, the hierarchy is no longer physically represented but by means of pointers. Question 17 Selected Answer: Correct Answer: Response Feedback: ID NUMBER(9) LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) ENROLL_DATE DATE The STUDENT table contains these columns: Evaluate these two statements: Statement 1: SELECT CONCAT(INITCAP(first_name), INITCAP(last_name)) FROM student WHERE enroll_date < '31-DEC-2007'; Statement 2: SELECT INITCAP(first_name) || INITCAP(last_name) FROM student WHERE enroll_date < '31-DEC-2007'; Which of the following statements is TRUE? The two statements will display the same data. The two statements will display the same data. In this scenario, the two statements will return the same data and display the data in the same format, but the column headings will differ. If the CUSTOMER table contains a customer with a FIRST_NAME value of JACK and a LAST_NAME value of BROWN, the customer name would be displayed by both statements as follows: JackBrown The other options stating that the statements do not display the same data, fail, or retrieve the same data with different display formats of returned values, are all incorrect. The CONCAT character function and the concatenation operator (||) are used to concatenate, or combine, one value (column1|expression1) to another value (column2|expression2). When using the CONCAT function, only two values may be joined. When using the concatenation operator, any number of values may be joined. The syntax of the CONCAT function is: CONCAT(column1|expression1, column2|expression2) The syntax of the concatenation operator (||) is: 0 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480_… 9/19 INITCAP(column|expression) (column1|expression1)||(column2|expression2)[||(column3|expression3)]... The INITCAP character function converts a mixed case, uppercase, or lowercase value (a column or expression) to a value whose first character is uppercase with the remaining characters in lowercase. The syntax of the INITCAP function is: Question 18 Selected Answer: A. Correct Answer: C. Response Feedback: The SQL command to advance a cursor to obtain the next row of results is NEXT FETCH Complexity: Easy Section: 5.8 Question 19 Selected Answer: c. Correct Answer: c. Examine this procedure: CREATE OR REPLACE PROCEDURE Ñnd_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END; Which command will successfully invoke this procedure in SQL*Plus? EXECUTE Ñnd_seats_sold (v_theater_id => 500); EXECUTE Ñnd_seats_sold (v_theater_id => 500); 0 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480… 10/19 Response Feedback: The command that will successfully invoke this procedure in SQL*Plus is: EXECUTE Ñnd_seats_sold(v_theater_id => 500); The DEFAULT clause in the CREATE OR REPLACE PROCEDURE statement speciÑes the default values for the formal parameters. If you do not specify values for these parameters when invoking the procedure, the default values speciÑed while creating the procedure are used. In this scenario, the v_movie_id parameter is assigned a default value of 34, and the v_theater_id parameter is not assigned a default value. The EXECUTE Ñnd_seats_sold(v_theater_id => 500); statement is correct because the value 500 passed to the procedure will be assigned to the v_theater_id parameter that has not been assigned a default value. The v_movie_id parameter will use the default value of 34. Argument values may be speciÑed using the positional or the named method. However, the named method must be used when not all arguments are speciÑed or when arguments are speciÑed in a diÖerent order than the declaration. The named method requires the use of the association operator (=>). The options stating you would use the RUN command are incorrect because the EXECUTE command is used to invoke a procedure from SQL*Plus. The EXECUTE Ñnd_seats_sold(v_movie_id => 34); statement is incorrect because it does not pass a parameter value for the v_theater_id parameter and the v_theater_id parameter was not declared with a default value. Question 20 Code example 6-2 SELECT vendor_id, invoice_date, invoice_total, SUM(invoice_total) OVER() AS total_invoices SUM(invoice_total) OVER(PARTITION BY vendor_id 0 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480… 11/19 Selected Answer: a. Correct Answer: b. ORDER BY invoice_total) AS vendor_total FROM invoices (Refer to code example 6-2.) What rows make up the partitions for the first SUM function? The rows for each vendor are treated as a separate partition. All of the rows are treated as a single partion. Question 21 Selected Answer: Correct Answer: Which statement is NOT CORRECT? Using error correcting codes for fault tolerance requires extra storage space, almost as much as when opting for disk mirroring. Using error correcting codes for fault tolerance requires extra storage space, almost as much as when opting for disk mirroring. Question 22 Selected Answer: a. Correct Answer: a. Which of the following could be used to calculate the number of days (rounded) between the current date and the value in a future_ date column? ROUND(future_date - SYSDATE) ROUND(future_date - SYSDATE) Question 23 Take the following extract from SUPPLIES: SUPNR PRODNR PURCHASE_PRICE DELIV_PERIOD 37 0185 32.99 3 84 0185 33.00 5 94 0185 32.99 1 We want to retrieve the fastest delivery time for product 0185. We type the following query: SELECT SUPNR, MIN(DELIV_PERIOD) AS MIN_DELIV_PERIOD FROM SUPPLIES WHERE PRODNR = '0185' 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480… 12/19 Selected Answer: Correct Answer: What are the results? If you believe the query is correct, pick answer a, otherwise pick the result you believe will be returned. SUPNR MIN_DELIV_PERIOD 94 1 SUPNR MIN_DELIV_PERIOD 94 1 Question 24 Selected Answer: d. Correct Answer: d. _____ is a relational set operator. EXCEPT EXCEPT Question 25 Selected Answer: Correct Answer: Given the following query: SELECT P.PRODNR, P.PRODNAME FROM PRODUCT P WHERE NOT EXISTS (SELECT* FROM SUPPLIES S WHERE S.PRODNR=P.PRODNR) i. This query selects all products that cannot be supplied ii. The above query gives the same results as the following query: SELECT DISTINCT P.PRODNR, P.PRODNAME FROM PRODUCT P, supplies S WHERE P.PRODNR=S.PRODNR Which statement is CORRECT? Statement i is correct, but statement ii is not correct. Statement i is correct, but statement ii is not correct. Question 26 Selected Answer: c. Correct Answer: c. You can use a common table expression (CTE) to create a temporary result set result set Question 27 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480… 13/19 Selected Answers: c. e. Correct Answers: c. e. Response Feedback: Which two options can be associated with a trigger created for views? (Choose two.) INSTEAD OF FOR EACH ROW INSTEAD OF FOR EACH ROW The options that can be associated with a trigger based on views are INSTEAD OF and FOR EACH ROW . The views created on a table may or may not permit DML on the underlying tables. The views that cannot update the tables on which they are based can be enabled to do so by using the INSTEAD OF trigger. An INSTEAD OF trigger provides a method to perform DML operations, such as INSERT , UPDATE , and DELETE , on views that cannot be modiÑed directly. When a triggering statement associated with an INSTEAD OF trigger is executed, the INSTEAD OF trigger is Ñred, and the triggering statement is not executed. The INSTEAD OF trigger is designed to determine the operation that caused the trigger to be Ñred and will perform the required operation modifying the underlying tables. Using an INSTEAD OF trigger, you can perform DML operations on views, which cannot otherwise be modiÑed. These operations modify the underlying tables in the background. The two things that an INSTEAD OF trigger identiÑes are the view and the event which causes the trigger to Ñre. The FOR EACH ROW option is speciÑed to create a row-level trigger that will be Ñred for every row aÖected by the triggering statement. An INSTEAD OF trigger can be created only as a row-level trigger and not as a statementlevel trigger. The AFTER option cannot be used with triggers based on views. The AFTER option used in triggers speciÑes that3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480… 14/19 the trigger will be Ñred after an action is performed in the database. This information is not required in the INSTEAD OF trigger. The BEFORE option cannot be used with triggers based on views. The BEFORE option used in triggers speciÑes that the trigger will be Ñred before an action is performed in the database. This information is not required in the INSTEAD OF trigger. The IN PLACE OF option is incorrect because there is no such option in the CREATE TRIGGER statement in Oracle. Question 28 Selected Answer: b. Correct Answer: b. Which of the following statements about common table expressions (CTEs) is not true? A CTE can refer to any other CTE in the same WITH clause. A CTE can refer to any other CTE in the same WITH clause. Question 29 Selected Answer: B. Correct Answer: B. Response Feedback: The SQL function that returns the number of values in a column is COUNT COUNT Complexity: Easy Section: 5.4 Question 30 Evaluate this SELECT statement: SELECT order_num, &order_date FROM &&ordertbl WHERE order_date = '&order_date'; Which statement regarding the execution of this statement is true? 0.5 out of 0.5 points 0.5 out of 0.5 points 0 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480… 15/19 Selected Answer: Correct Answer: The user will be prompted for the table name each time the statement is executed in a session. The user will be prompted for the table name only the first time the statement is executed in a session. Question 31 Selected Answer: d. Correct Answer: d. Which of the following statements would you use to check if a parameter contains a null value and set it to a default value if it does? IF IF Question 32 Selected Answer: a. Correct Answer: a. Which program can you use to execute the statements that contain the changes that were made after the last full backup? mysqlbinlog mysqlbinlog Question 33 Selected Answer: b. Correct Answer: b. The processing that’s done by the DBMS is typically referred to as back-end processing back-end processing Question 34 Selected Answer: b. Correct Answer: d. Which of the following statements do you use in a stored procedure to raise an error? SET ERROR SIGNAL Question 35 Selected Answer: b. Which of the following statements do you use to turn the event scheduler on or off? SET 0.5 out of 0.5 points 0.5 out of 0.5 points 0.5 out of 0.5 points 0 out of 0.5 points 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480… 16/19 Correct Answer: b. SET Question 36 Selected Answer: Correct Answer: Response Feedback: Examine the structure of the PRODUCT table: Which SELECT statement displays the number of items whose LIST_PRICE is greater than $400.00? SELECT COUNT(*) FROM product WHERE list_price > 400; SELECT COUNT(*) FROM product WHERE list_price > 400; The following SELECT statement displays the number of items whose LIST_PRICE is greater than $400.00: SELECT COUNT(*) FROM product WHERE list_price > 400; COUNT(*) returns the number of rows in a table or in a particular group or rows in a table. Because no GROUP BY clause is provided in this statement, all rows in the table that meet the WHERE clause criteria are counted. Both SELECT statements that use the SUM group function are incorrect. The SUM group function returns the sum of a group of values, which is not what you desired. Additionally, SUM(*) is invalid syntax and will generate an error. The SELECT statement that uses the COUNT group function but does not include a WHERE clause is also incorrect. To restrict the rows counted to only those with a list price greater than $400.00, a WHERE clause would need to be included. Question 37 _______ techniques work under the assumption that conÒict will normally not 0.5 out of 0.5 points 0 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480… 17/19 Selected Answer: B. Correct Answer: C. Response Feedback: occur. Timestamping Validation Complexity: diÔcult Section: 9.6 Question 38 Selected Answers: Correct Answers: Response Feedback: FROM donation WHERE amount_pledged = 1000.00 OR pledge_dt = '05-JAN-2002'); Examine the data from the DONATION table. This statement fails when executed: SELECT amount_pledged, amount_paid FROM donation WHERE donor_id = (SELECT donor_id Which two changes could correct the problem? (Choose two. Each correct answer is a separate solution.) ChangetheouterqueryWHEREclausetoWHERE donor_id IN. Change the subquery WHERE clause to WHERE amount_pledged = 1000.00 AND pledge_dt = '05-JAN- 2002'. ChangetheouterqueryWHEREclausetoWHERE donor_id IN. Change the subquery WHERE clause to WHERE amount_pledged = 1000.00 AND pledge_dt = '05-JAN- 2002'. This statement fails because the subquery returns multiple rows, which cannot be compared to a single value using the equality operator (=) in the outer query. To correct the problem, you could change the outer query WHERE clause to WHERE donor_id IN. Changing the outer query WHERE clause to use the IN operator would allow the inner query to return multiple rows without generating an error. Alternatively, you could change the subquery WHERE clause to WHERE amount_pledged = 1000.00 AND pledge_dt = '05-JAN-2002'. Based on the given 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480… 18/19 data, this change would cause only one row to be returned and would also eliminate the error. Removing the subquery WHERE clause, changing the outer query WHERE clause to WHERE donor_id LIKE, or including the DONOR_ID column in the select list of the outer query would not correct the problem. The inner query would still return multiple rows and produce an error. Removing the single quotes around the date value in the inner query WHERE clause will not correct the problem. When dates values are used in a WHERE clause, they must be enclosed in single quotation marks. Question 39 Selected Answer: b. Correct Answer: b. Response Feedback: What should be placed after the IS keyword when creating a procedure? local variables only local variables only Local variables must be placed after the IS keyword but before the BEGIN statement. For example: CREATE OR REPLACE PROCEDURE check_sal IS v_sal emp.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE ename = 'SMITH'; END; The variable v_sal is declared and can be referenced in the PL/SQL block. The DECLARE keyword found in anonymous blocks must not be used. When creating a procedure, parameters are placed before the IS or AS keywords in the argument list. When creating a procedure, global variables are not declared after the IS or AS keywords. Variables declared 0.5 out of 0.5 points3/23/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22221408_1&course_id=_331737_1&content_id=_10142480… 19/19 Monday, March 23, 2020 3:42:59 PM EDT after the IS keyword have local scope. Question 40 Selected Answer: a. Correct Answer: d. Which of the following is not true about creating indexes? Oracle automatically creates indexes for primary keys Oracle automatically creates indexes for foreign keys ← OK 0 out of 0.5 points3/22/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22213484_1&course_id=_331737_1&content_id=_10142480_… 1/19 202001_Database Management Systems_CSCI_6441_10 Tests Review Test Submission: Quiz03 Review Test Submission: Quiz03 User Hang Yu Course 202001_Database Management Systems_CSCI_6441_10 Test Quiz03 Started 3/22/20 7:27 PM Submitted 3/22/20 7:41 PM Due Date 3/23/20 11:59 PM Status Completed Attempt Score 14 out of 20 points Time Elapsed 14 minutes out of 2 hours Results Displayed Submitted Answers, Correct Answers, Feedback, Incorrectly Answered Questions Question 1 Selected Answer: c. Correct Answer: c. To assign privileges to a user using one or more roles, you must do all but one of the following. Which one is it? Set the default role for the user. Set the default role for the user. Question 2 Selected Answer: C. Correct Answer: C. Response Feedback: Which of the following is the proper way to create an alias, E, for relational table Employee and retrieve all employee data? SELECT * FROM EMPLOYEE E; SELECT * FROM EMPLOYEE E; Complexity: Easy Section: 5.4 Question 3 Given following two statements: i. RPO is the recovery point objective and is the amount of downtime accepted after a calamity. ii. In theory, both the RTO and RPO should be as small as possible. However, this asks for a greater investment so the optimal TRO and RPO depends on the Courses Hang Y1u 0.5 out of 0.5 points 0.5 out of 0.5 points 0 out of 0.5 points3/22/2020 Review Test Submission: Quiz03 – 202001_Database ... https://blackboard.gwu.edu/webapps/assessment/review/review.jsp?attempt_id=_22213484_1&course_id=_331737_1&content_id=_10142480_… 2/19 Selected Answer: Correct Answer: actual organization. Which of the following statements is correct? Both statements are correct. Statement ii is correct but statement i is not correct. Question 4 Selected Answer: Correct Answer: In which of these cases can we make use of delimiters to separate attributes? In all of these cases In all of these cases Question 5 Selected Answer: a. Correct Answer: c. Response Feedback: The get_budget function is no longer needed and should be removed. Which statement will successfully remove this function from the database? DROP get_budget; DROP FUNCTION get_budget; The DROP FUNCTION get_budget; statement will successfully remove this function from the database. If the statement executes successfully, the object will cease to exist in the user's schema and will be physically deleted from the data dictionary. All of the other options are incorrect because they do not provide the correct syntax for removing a function from the database. [Show More]

Last updated: 1 year ago

Preview 1 out of  pages