Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
CHAPTER 7: INTRODUCTION TO STRUCTURED QUERY LANGUAGE
(SQL)
1. A database language enables the user to create database and table structures to perform basic data
management chores.
a. True
b. False
2. A database language enables the user to perform complex queries designed to transform the raw data into
useful information.
a. True
b. False
3. The ANSI prescribes a standard SQL–the current fully approved version is known as SQL-07.
a. True
b. False
4. The ANSI SQL standards are also accepted by the ISO.
a. True
b. False
5. SQL is considered difficult to learn; its command set has a vocabulary of more than 300 words.
a. True
b. False
6. Data type selection is usually dictated by the nature of the data and by the intended use.
a. True
b. False
7. Only numeric data types can be added and subtracted in SQL.
a. True
b. False
Chapter 7: Introduction to Structured Query Language (SQL)
8. Entity integrity is enforced automatically when the primary key is specified in the CREATE TABLE
command sequence.
a. True
b. False
9. The CHECK constraint is used to define a condition for the values that the attribute domain cannot have.
a. True
b. False
10. SQL requires the use of the ADD command to enter data into a table.
a. True
b. False
11. You cannot insert a row containing a null attribute value using SQL.
a. True
b. False
12. To list the contents of a table, you must use the DISPLAY command.
a. True
b. False
13. Any changes made to the contents of a table are not physically saved on disk until you use the SAVE <table
name> command.
a. True
b. False
14. The COMMIT command does not permanently save all changes. In order to do that, you must use SAVE.
a. True
b. False
15. 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.
a. True
b. False
Chapter 7: Introduction to Structured Query Language (SQL)
16. All SQL commands must be issued on a single line.
a. True
b. False
17. Although SQL commands can be grouped together on a single line, complex command sequences are best
shown on separate lines, with space between the SQL command and the command’s components.
a. True
b. False
18. You can select partial table contents by naming the desired fields and by placing restrictions on the rows to be
included in the output.
a. True
b. False
19. Oracle users can use the Access QBE (query by example) query generator.
a. True
b. False
20. Mathematical operators cannot be used to place restrictions on character-based attributes.
a. True
b. False
21. String comparisons are made from left to right.
a. True
b. False
22. Date procedures are often more software-specific than other SQL procedures.
a. True
b. False
23. SQL allows the use of logical restrictions on its inquiries such as OR, AND, and NOT.
a. True
b. False
Chapter 7: Introduction to Structured Query Language (SQL)
24. ANSI-standard SQL allows the use of special operators in conjunction with the WHERE clause.
a. True
b. False
25. The conditional LIKE must be used in conjunction with wildcard characters.
a. True
b. False
26. Most SQL implementations yield case-insensitive searches.
a. True
b. False
27. Some RDBMSs, such as Microsoft Access, automatically make the necessary conversions to eliminate case
sensitivity.
a. True
b. False
28. The COUNT function is designed to tally the number of non-null "values" of an attribute, and is often used in
conjunction with the DISTINCT clause.
a. True
b. False
29. An alias cannot be used when a table is required to be joined to itself in a recursive query.
a. True
b. False
30. When joining three or more tables, you need to specify a join condition for one pair of tables.
a. True
b. False
31. The current fully approved version of standard SQL prescribed by the ANSI is .
a. SQL-99 b. SQL-2003
c. SQL-4 d. SQL-07
Chapter 7: Introduction to Structured Query Language (SQL)
32. The data format for SQL character is .
a. CHAR and VARCHAR b. VARCHAR only
c. alphanumeric d. CHAR only
33. The SQL command that allows a user to insert rows into a table is .
a. INSERT b. SELECT
c. COMMIT d. UPDATE
34. The SQL command that allows a user to permanently save data changes is .
a. INSERT b. SELECT
c. COMMIT d. UPDATE
35. The SQL command that allows a user to list the contents of a table is .
a. INSERT b. SELECT
c. COMMIT d. UPDATE
36. To list all the contents of the PRODUCT table, a user would use .
a. LIST * FROM PRODUCT b. SELECT * FROM PRODUCT
c. DISPLAY * FROM PRODUCT d. SELECT ALL FROM PRODUCT
37. In Oracle, the command is used to change the display for a column, for example, to place a $ in front of
a numeric value.
a. DISPLAY b. FORMAT
c. CHAR d. CONVERT
38. The SQL command that modifies an attribute’s values in one or more table’s rows is _____.
a. INSERT b. SELECT
c. COMMIT d. UPDATE
Chapter 7: Introduction to Structured Query Language (SQL)
39. UPDATE tablename
*****
[WHERE conditionlist];
The command replaces the ***** in the syntax of the UPDATE command, shown above.
a. SET columnname = expression b. columnname = expression
c. expression = columnname d. LET columnname = expression
40. An example of a command a user would use when making changes to a PRODUCT table is .
a. CHANGE PRODUCT
SET P_INDATE = ‘18-JAN-2004’
WHERE P_CODE = ‘13-Q2/P2’;
b. ROLLBACK PRODUCT
SET P_INDATE = ‘18-JAN-2004’
WHERE P_CODE = ‘13-Q2/P2’;
c. EDIT PRODUCT
SET P_INDATE = ‘18-JAN-2004’
WHERE P_CODE = ‘13-Q2/P2’;
d. UPDATE PRODUCT
SET P_INDATE = ‘18-JAN-2004’
WHERE P_CODE = ‘13-Q2/P2’;
41. The command is used to restore the database to its previous condition.
a. COMMIT; RESTORE; b. COMMIT; BACKUP;
c. COMMIT; ROLLBACK; d. ROLLBACK;
42. Some RDBMSs, such as Oracle, automatically data changes when issuing data definition commands.
a. COMMIT b. ROLLBACK
c. UNSAVE d. UPDATE
43. To remove a row from the PRODUCT table, one must use the command.
a. COMMIT b. DELETE
c. ERASE d. KILL
44. 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
Chapter 7: Introduction to Structured Query Language (SQL)
45. Which of the following is used to select partial table contents?
a. SELECT <column(s)>
FROM <Table name>
BY <Conditions>;
b. LIST <column(s)>
FROM <Table name>
BY <Conditions>;
c. SELECT <column(s)>
FROM <Table name>
WHERE <Conditions>;
d. LIST<column(s)>
FROM <Table name>
WHERE <Conditions>;
46. 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’;
47. A(n) is a query that is embedded (or nested) inside another query.
a. alias b. operator
c. subquery d. view
48. 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;
Chapter 7: Introduction to Structured Query Language (SQL)
49. 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;
50. Which of the following queries will output the table contents when the value of P_PRICE is less than or
equal to 10?
a. SELECT P_DESCRIPT, P_INDATE, P_PRICE,
V_CODE FROM PRODUCT
WHERE P_PRICE <> 10;
b. SELECT P_DESCRIPT, P_INDATE, P_PRICE,
V_CODE FROM PRODUCT
WHERE P_PRICE <= 10;
c. SELECT P_DESCRIPT, P_INDATE, P_PRICE,
V_CODE FROM PRODUCT
WHERE P_PRICE => 10;
d. SELECT P_DESCRIPT, P_INDATE, P_PRICE,
V_CODE FROM PRODUCT
WHERE P_PRICE = 10;
Chapter 7: Introduction to Structured Query Language (SQL)
51. 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};
52. Which of the following queries will list all the rows in which the inventory stock dates occur on or after
January 20, 2010?
a. SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE,
P_INDATE FROM PRODUCT
WHERE P_INDATE >= ‘20-JAN-2010’;
b. SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE,
P_INDATE FROM PRODUCT
WHERE P_INDATE >= $20-JAN-2010$;
c. SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE,
P_INDATE FROM PRODUCT
WHERE P_INDATE <= ‘20-JAN-2010’;
d. SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE,
P_INDATE FROM PRODUCT
WHERE P_INDATE >= {20-JAN-2010};
53. 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;
Chapter 7: Introduction to Structured Query Language (SQL)
54. A(n) is an alternate name given to a column or table in any SQL statement.
a. alias b. data type
c. stored function d. trigger
55. 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 and display the results in a column labeled TOTVALUE?
a. SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE AS
TOTVALUE FROM PRODUCT;
b. SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH=P_PRICE AS
TOTVALUE FROM PRODUCT;
c. SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH/P_PRICE AS
TOTVALUE FROM PRODUCT;
d. SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH-P_PRICE AS
TOTVALUE FROM PRODUCT;
56. 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;
Chapter 7: Introduction to Structured Query Language (SQL)
57. The special operator used to check whether an attribute value is within a range of values is .
a. BETWEEN b. NULL
c. LIKE d. IN
58. The special operator is used to check whether an attribute value is null.
a. BETWEEN b. IS NULL
c. LIKE d. NOT NULL
59. 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
60. The special operator used to check whether a subquery returns any rows is .
a. BETWEEN b. EXISTS
c. LIKE d. IN
61. The command is used with the ALTER TABLE command to modify the table by deleting a column.
a. DROP b. REMOVE
c. DELETE d. ERASE
62. A table can be deleted from the database by using the command.
a. DROP TABLE b. DELETE TABLE
c. MODIFY TABLE d. ERASE TABLE
Chapter 7: Introduction to Structured Query Language (SQL)
63. The query used to list the P_CODE, P_DESCRIPT, P_INDATE, and P_PRICE fields from the PRODUCT
table in ascending order by P_PRICE is .
a. SELECT P_CODE, P_DESCRIPT, P_INDATE,
P_PRICE FROM PRODUCT
SEQUENCE BY P_PRICE;
b. SELECT P_CODE, P_DESCRIPT, P_INDATE,
P_PRICE FROM PRODUCT
LIST BY P_PRICE;
c. SELECT P_CODE, P_DESCRIPT, P_INDATE,
P_PRICE FROM PRODUCT
ORDER BY P_PRICE;
d. SELECT P_CODE, P_DESCRIPT, P_INDATE,
P_PRICE FROM PRODUCT
ASCENDING BY P_PRICE;
64. 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;
Chapter 7: Introduction to Structured Query Language (SQL)
65. 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;
66. 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
67. The SQL aggregate function that gives the total of all values for a selected attribute in a given column is
.
a. COUNT b. MIN
c. MAX d. SUM
a. COUNT b. AVG
c. MAX d. SUM
Chapter 7: Introduction to Structured Query Language (SQL)
69. 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;
70. 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;
Chapter 7: Introduction to Structured Query Language (SQL)
71. The SQL data manipulation command HAVING:
a. restricts the selection of rows based on a conditional expression.
b. restricts the selection of grouped rows based on a condition.
c. modifies an attribute’s values in one or more table’s rows.
d. groups the selected rows based on one or more attributes.
72. The constraint assigns a value to an attribute when a new row is added to a table.
a. CHECK
b. UNIQUE
c. NOT NULL
d. DEFAULT
73. The syntax for creating an index is .
a. CREATE [NOT NULL] INDEX indexname FROM viewname(column1 [, column2]);
b. CREATE [UNIQUE] INDEX indexname ON tablename( column1 [, column2]);
c. CREATE [UNIQUE] INDEX indexname FROM tablename(column1 [, column2]);
d. CREATE [DEFAULT] INDEX indexname ON viewname(column1 [, column2]);
74. 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
75. 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
76. In the SQL environment, the word covers both questions and actions.
Chapter 7: Introduction to Structured Query Language (SQL)
77. The basic SQL vocabulary has fewer than words.
78. A(n) is a logical group of database objects, such as tables and indexes, that are related to each other.
79. With the exception of the database process, most RDBMS vendors use SQL that deviates little from
the ANSI standard SQL.
80. U.S. state abbreviations are always two characters, so (2) is a logical choice for the data type
representing a state column.
81. DATE() and SYSDATE are special functions that return today’s date in MS Access and , respectively.
82. If integer values are relatively small, must be used instead of INT.
83. In a 1:M relationship, a user must always create the table for the side first.
one
84. words are words used by SQL to perform specific functions.
85. Using the command, SQL indexes can be created on the basis of any selected attribute.
86. 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.
87. To delete an index, one must use the command.
88. In an INSERT command, a user can indicate just the attributes that have required values by listing the
inside parentheses after the table name.
Chapter 7: Introduction to Structured Query Language (SQL)
89. A(n) character is a symbol that can be used as a general substitute for other characters or commands.
90. A(n) , also known as a nested query or an inner query, is a query that is embedded (or nested) inside
another query.
91. The command, coupled with appropriate search conditions, is an incredibly powerful tool that enables
a user to transform data into information.
92. In SQL, all expressions evaluate to true or false.
93. A specialty field in mathematics, known as algebra, is dedicated to the use of logical operators.
94. 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.
95. A table can be deleted from the database by using the command.
96. 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.
97. Rows can be grouped into smaller collections quickly and easily using the clause within the SELECT
statement.
98. The clause of the GROUP BY statement operates very much like the WHERE clause in the SELECT
statement.
99. A(n) is performed when data are retrieved from more than one table at a time.
100. An alias is especially useful when a table must be joined to itself in a(n) query.
Chapter 7: Introduction to Structured Query Language (SQL)
101. is the process the DBMS uses to verify that only registered users access the database.
102. The SQL data type DATE stores date in the date format.
103. The specification is used to avoid having duplicated values in a column.
104. To make the output more readable, the SQL standard permits the use of aliases for any column in a
statement.
105. The condition is generally composed of an equality comparison between the foreign key and the
primary key of related tables.
106. What is a schema? How many schemas can be used in one database?
107. Which command is used to save changes to the database? What is the syntax for this command?
108. What is a subquery? When is it used? Does the RDBMS deal with subqueries any differently from normal
queries?
Chapter 7: Introduction to Structured Query Language (SQL)
109. What are the wildcard characters that are used with the LIKE command? Provide one or more examples of
each.
110. How can a table be deleted from the database? Provide an example.
111. Explain the two SQL functions.
