University:
Harvard University
Course:
CS50 | AP Computer Science
Academic year:

2021
Views:

93

Pages:

11
Author:

Kiara Gothe

37. The _____ command is used to restore the database to its previous condition. a. COMMIT; RESTORE; b. COMMIT; BACKUP; c. COMMIT; ROLLBACK; d. ROLLBACK; ANSWER: d 38. When a user issues the DELETE FROM tablename command without specifying a WHERE condition, _____. a. no rows will be deleted b. the first row will be deleted c. the last row will be deleted d. all rows will be deleted ANSWER: d 39. The _____ command would be used to delete the table row where the P_CODE is 'BRT-345'. a. DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345'; b. REMOVE FROM PRODUCT WHERE P_CODE = 'BRT-345'; c. ERASE FROM PRODUCT WHERE P_CODE = 'BRT-345'; d. ROLLBACK FROM PRODUCT WHERE P_CODE = 'BRT-345'; ANSWER: a 40. A(n) _____ is a query that is embedded (or nested) inside another query. a. alias b. operato r c. subquery d. view ANSWER: c 41. Which of the following queries will output the table contents when the value of V_CODE is equal to 21344? a. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344; b. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <= 21344; c. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344; d. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE => 21344; ANSWER: c 42. Which of the following is used to select partial table contents? a. SELECT FROM BY ; b. LIST FROM

BY ; c. SELECT FROM

WHERE ; d. LIST FROM

WHERE ; ANSWER: c 43. Which of the following queries will output the table contents when the value of V_CODE is not equal to 21344? a. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344; b. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <= 21344; c. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344; d. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE => 21344; ANSWER: a 44. Which of the following queries will output the table contents when the value of the character field P_CODE is alphabetically less than 1558-QW1? a. SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE <'1558-QW1'; b. SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE = [1558-QW1]; c. SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE = (1558-QW1); d. SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE = {1558-QW1}; ANSWER: a 45. Which of the following queries will list all the rows in which the inventory stock dates occur on or after January 20, 2016? a. SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= '20-JAN-2016'; b. SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= $20-JAN-2016; c. SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE <= '20-JAN-2016'; d. SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= {20-JAN-2016}; ANSWER: a 46. 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? a. SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH/P_PRICE FROM PRODUCT; b. SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH=P_PRICE FROM PRODUCT; c. SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE FROM PRODUCT; d. SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH-P_PRICE FROM PRODUCT; ANSWER: c 47. A(n) _____ is an alternate name given to a column or table in any SQL statement. a. alias b. data type d. trigger c. stored function ANSWER: a 48. Which of the following queries uses the correct SQL syntax to list the table contents for either V_CODE = 21344 or V_CODE = 24288? a. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE <= 24288; b. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE => 24288; c. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE > 24288; d. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288; ANSWER: d 49. According to the rules of precedence, which of the following computations should be completed first? a. Performing additions and subtractions b. Performing multiplications and divisions c. Performing operations within parentheses d. Performing power operations ANSWER: c 50. The special operator used to check whether an attribute value is within a range of values is _____. b. NULL a. BETWEE N c. LIKE d. IN ANSWER: a 51. The special operator used to check whether an attribute value matches a given string pattern is _____. a. BETWEEN b. IS NULL c. LIKE d. IN ANSWER: c 52. The special operator used to check whether a subquery returns any rows is _____. a. BETWEEN b. EXISTS c. LIKE d. IN ANSWER: b 53. All changes in a table structure are made using the _____ command, followed by a keyword that produces the specific changes a user wants to make. a. ALTER TABLE b. UPDATE TABLE c. COMMIT TABLE d. ROLLBACK TABLE ANSWER: a 54. The SQL aggregate function that gives the number of rows containing non-null values for a given column is _____. a. COUNT b. MIN c. MAX d. SUM ANSWER: a 55. 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 _____. a. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <> VENDOR.V_CODE; b. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; c. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <= VENDOR.V_CODE; d. SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE => VENDOR.V_CODE; ANSWER: b 56. The SQL query to output the contents of the EMPLOYEE table sorted by last name, first name, and initial is _____. a. SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE LIST BY EMP_LNAME, EMP_FNAME, EMP_INITIAL; b. SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL; c. SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE DISPLAY BY EMP_LNAME, EMP_FNAME, EMP_INITIAL; d. SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE SEQUENCE BY EMP_LNAME, EMP_FNAME, EMP_INITIAL; ANSWER: b 57. 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? a. SELECT ONLY V_CODE FROM PRODUCT; b. SELECT UNIQUE V_CODE FROM PRODUCT; c. SELECT DIFFERENT V_CODE FROM PRODUCT; d. SELECT DISTINCT V_CODE FROM PRODUCT; ANSWER: d 58. A table can be deleted from the database bu using the ___ command. a. DROP TABLE b. DELETE TABLE c. MODIFY TABLE d. ERASE TABLE ANSWER: a 59. 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 and the output is ordered by the price is _____. a. SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <> VENDOR.V_CODE; ORDER BY PRODUCT.P_PRICE; b. SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE => VENDOR.V_CODE; ORDER BY PRODUCT.P_PRICE; c. SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <= VENDOR.V_CODE; ORDER BY PRODUCT.P_PRICE; d. SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; ORDER BY PRODUCT.P_PRICE; ANSWER: d Completion 60. The basic SQL vocabulary has fewer than _____words. ANSWER: 100 one hundred a hundred 61. In the SQL environment, the word _____ covers both questions and actions. ANSWER: query 62. A(n) _____ is a logical group of database objects, such as tables and indexes, that are related to each other. ANSWER: schema 63. With the exception of the database _____ process, most RDBMS vendors use SQL that deviates little from the ANSI standard SQL. ANSWER: creation creating 64. ________ is the process the DBMS uses to verify that only registered users access the database ANSWER: Authentication 65. U.S. state abbreviations are always two characters, so _____(2) is a logical choice for the data type representing a state column. ANSWER: CHAR 66. The SQL data type DATE stores date in the _____ date format. ANSWER: Julian 67. To make SQL code more____ , most SQL programmers use one line per column (attribute) definition. ANSWER: readabl e 68. In a 1:M relationship, a user must always create the table for the _____ side first. ANSWER: 1 one 69. The _____ specification is used to avoid having duplicated values in a column. ANSWER: UNIQUE 70. _____ words are words used by SQL to perform specific functions. ANSWER: Reserved 71. A common practice is to create a(n) _____ on any field that is used as a search key, in comparison operations in a conditional expression, or when a user wants to list rows in a specific order. ANSWER: index 72. To delete an index, one must use the _____ command. ANSWER: DROP INDEX 73. In an INSERT command, a user can indicate just the attributes that have required values by listing the _____ inside parentheses after the table name. ANSWER: attribute names names 74. A(n) _____ character is a symbol that can be used as a general substitute for other characters or commands. ANSWER: wildcard wild card 75. A(n) _____, also known as a nested query or an inner query, is a query that is embedded (or nested) inside another query. ANSWER: subquery KEYWORDS: Bloom's: Knowledge 76. The _____ command, coupled with appropriate search conditions, is an incredibly powerful tool that enables a user to transform data into information. ANSWER: SELECT 77. DATE() and SYSDATE are special functions that return today’s date in MS Access and _____, respectively. ANSWER: Oracle 78. In SQL, all _____ expressions evaluate to true or false. ANSWER: conditional 79. A specialty field in mathematics, known as _____ algebra, is dedicated to the use of logical operators. ANSWER: Boolean 80. If a user adds a new column to a table that already has rows, the existing rows will default to a value of _____ for the new column. ANSWER: null 81. A table can be deleted from the database by using the _____ command. ANSWER: DROP TABLE 82. A(n) _____ order sequence is a multilevel ordered sequence that can be created easily by listing several attributes, separated by commas, after the ORDER BY clause. ANSWER: cascading 83. Rows can be grouped into smaller collections quickly and easily using the _____ clause within the SELECT statement. ANSWER: GROUP BY 84. The _____ clause of the GROUP BY statement operates very much like the WHERE clause in the SELECT statement. ANSWER: HAVING 85. A(n) _____ is performed when data are retrieved from more than one table at a time. ANSWER: join 86. The _____ condition is generally composed of an equality comparison between the foreign key and the primary key of related tables. ANSWER: join 87. An alias is especially useful when a table must be joined to itself in a(n) _____ query. ANSWER: recursive DDL data definition language DML data manipulation language CREATE TABLE: RDBMS creates physical files that will hold database, differs from one RDBMS to another Create schema authorization: create database schema CREATE TABLE SCHEMA: creates a new table in the user’s database schema ALTER TABLE: modifies a table’s definition (adds, modifies, or deletes attributes or constraints) DROP TABLE: permanently deletes a table (and its data) INSERT: insert row(s) into a table UPDATE: modifies an attribute’s values in one or more table’s rows DELETE: deletes one or more rows from a table SELECT: selects attributes from rows in one or more tables or views GROUP BY: groups the selected rows based on one or more attributes ORDER BY: orders the selected rows based on one or more attributes HAVING: restricts the selection of grouped rows based on a condition COMMIT: permanently saves date changes ROLLBACK: restores data to its original values Comment lines: DESCRIBE DISTINCT: produces list of values that are unique View: CREATE VIEW: creates a dynamic subset of rows and columns from one or more tables Aggregate functions: COUNT: returns the number of rows with non-null values for a given column MIN: returns the minimum attribute value found in a given column MAX: returns the maximum attribute value found in a given column SUM: returns the sum of all values for a given column AVG: returns a average of all values for a given column Data types: Numeric: NUMBER(L,D), NUMERIC(L,D), INTEGER, SMALLINT, DECIMAL(L,D) Character: CHAR(L), VARCHAR(L) or VARCHAR2(L) Date Set echo on/ echo off Spool/ spool off 1NF first normal form: table format, no repeating groups, and primary key identified 2NF second normal form: 1NF and no partial dependencies 3NF third normal form: 2NF an no transitive dependencies BCNF Boyce-Codd normal form: every determinant is a candidate key (special case of 3NF) 4NF fourth normal form: 3NF and no independent multivalued dependencies Functional dependence: the attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B Functional dependence (generalized definition): attribute A determines attribute B (that is B functionally dependent on A) if all of the rows in the table that agree in value for attribute A also agree in value for attribute B Fully functional dependence: if attribute B is functionally dependent on a composite key A but not on any subset of that composite key, the attribute B is fully functional dependent on A Partial: functional dependence in which the determinant is only part of the primary key. Assumption – one candidate key. Straight forward. Easy to identify Transitive dependency: an attribute functionally depends on another nonkey attribute