Database Processing, 15e (Kroenke)
Chapter 2: Introduction to Structured Query Language
1) Business Intelligence (BI) systems typically store their data in data warehouses.
2) An ad-hoc SQL query is typically written within an application program.
3) SQL stands for Standard Query Language.
4) SQL includes a data definition language, a data manipulation language, and SQL/Persistent
stored modules.
5) SQL is only a data manipulation language (DML).
6) The American National Standards Institute (ANSI) maintains the standards for SQL.
7) SQL was developed by IBM in the late 1970s.
8) SQL is not a complete programming language. Rather it is a data sublanguage.
9) In addition to being a data sublanguage, SQL is also a programming language, like Java or C#.
10) SQL commands can be embedded in application programs.
11) SQL, although very popular, has never become a national standard.
12) The SQL keyword SELECT is used to specify the columns to be listed in the query results.
13) The SQL keyword WHERE is used to specify the table(s) that contain(s) the data to be
retrieved.
14) The SQL keyword FROM is used to specify the table to be used.
15) SQL can only query a single table.
16) SQL statements end with a colon.
17) The columns to be obtained by an SQL command are listed after the FROM keyword.
18) To remove duplicate rows from the result of a query, specify the DISTINCT keyword.
19) To obtain all columns, use an asterisk (*) wildcard character instead of listing all the column
names.
20) The WHERE clause contains the condition that specifies which rows are to be selected.
21) The result of an SELECT operation can contain duplicate rows.
22) To have SQL automatically eliminate duplicate rows from a result, use the keyword
DISTINCT with the FROM keyword.
23) An asterisk (*) following the SELECT verb means that all columns are to be displayed.
24) The WHERE clause contains the condition that specifies which columns are to be selected.
25) The rows of the result table can be sorted by the values in one or more columns.
26) Sorting is specified using the SORT BY phrase.
27) To sort the rows of the result table, the ORDER BY clause is specified.
28) Columns can be sorted in descending sequence by using the DESC keyword.
29) A WHERE clause can contain only one condition.
30) When two conditions must both be true for the rows to be selected, the conditions are
separated by the SQL AND keyword.
31) To refer to a set of values needed for a condition, use the IN operator.
32) To exclude one or more values using a condition, the OUT keyword must be used.
33) To refer to a set of values in a condition, the values are placed inside parentheses () and
separated by commas.
34) The condition in WHERE clauses can refer to a set of values by using the IN operator.
35) The SQL keyword LIKE is used in SQL expressions to select partial string values.
36) The SQL wildcard character “%” represents a series of one or more unspecified characters.
37) The wildcard character “#” indicates a single, unspecified character.
38) The Microsoft Access wildcard character “*” (asterisk) indicates a sequence of one or more
unspecified characters in a Microsoft Access SQL query.
39) The Microsoft Access wildcard character “_” (underscore) indicates a single, unspecified
character in a specific location in a Microsoft Access SQL query.
40) The SQL built-in function ADDUP totals values in numeric columns.
41) The SQL built-in function AVG computes the average of values in numeric columns.
42) The SQL built-in function MOST obtains the largest value in a numeric column.
43) The SQL built-in function MIN obtains the smallest value in a numeric column.
44) The SQL built-in function COUNT computes the number of rows in a query.
45) The built-in function SUM can be used with any column.
46) The clause SELECT COUNT (*) results in a table with a single row and a single column.
47) When using the COUNT function, the result is the count of all rows including those that are
NULL.
48) The SQL keyword GROUP BY instructs the DBMS to group together those rows that have
the same value in a column.
49) When SQL statements are executed, the statements transform tables.
50) A SELECT statement used in a WHERE clause is called a subquery.
51) A nested SELECT statement, one that appears within the WHERE clause of another SQL
statement, is called a subquery and must be enclosed in parentheses.
52) SQL queries using subqueries still function like a single table query in the sense that columns
from both the top-level query and subquery can be displayed in the query results.
53) An alternative to combining tables by a subquery is to use a join.
54) When people use the term join they normally mean an equijoin.
55) Two or more tables are joined by giving the table names in the WHERE clause and
specifying the equality of the respective column names as a condition in the GROUP BY clause.
11
56) The names of tables to be joined in an SQL query are listed in the FROM clause.
57) In an SQL query, a join operation is achieved by specifying the equality of the respective
column names as a condition in the WHERE clause.
58) The JOIN operator is used to combine two or more tables by concatenating (sticking
together) the rows of one table with the rows of another table.
59) While many subqueries can be alternatively written as joins, correlated subqueries do work
that cannot be duplicated as a join.
60) The SQL syntax JOIN . . . ON can be used as an alternate way of writing an SQL join
statement.
61) Joins that show only matching rows from the joined tables in their results are called inner
joins.
62) Joins that show the matching rows from the joined tables, plus unmatched rows from one
other table in their results, are called outer joins.
63) Outer joins can be either up joins or down joins.
64) The UNION of two relations A and B consists of all rows that are either in relation A or in
relation B or both.
65) The INTERSECT of two relations A and B consists of all rows that are in relation B but are
not in relation A.
66) A database extracted from the operational database for BI purposes typically ________.
A) is an exact copy of the operational database
B) contains only the metadata from the operational database
C) combines all the data from the operational database into one large table
D) contains just part of the operational database
67) An ad-hoc query is ________.
A) a question about the data that requires a program to be written
B) a question that can be answered from the database using SQL
C) a question that can be answered only by combining several SQL queries
D) never useful in Business Intelligence scenarios
68) SQL is a ________.
A) data sublanguage
B) data placement source
C) data subjective source
D) a full-blown programming language
69) When making an SQL query, we are using SQL as a(n) ________.
A) DDL
B) DML
C) embedded language
D) SET
70) In an SQL query, which SQL keyword begins the query?
A) EXISTS
B) FROM
C) SELECT
D) WHERE
71) In an SQL query, which SQL keyword is used to specify the table(s) to be used?
A) EXISTS
B) FROM
C) SELECT
D) WHERE
72) In an SQL query, which SQL keyword must be used to remove duplicate rows from the
result table?
A) DELETE
B) DISTINCT
C) UNIQUE
D) KEY
73) In an SQL query, which of the following symbols is used by ANSI SQL to represent all the
columns in a single table?
A) _ (underscore)
B) ? (question mark)
C) * (asterisk)
D) % (percent)
74) In an SQL query, which SQL keyword is used to state the condition that specifies which
rows are to be selected?
A) SET
B) FROM
C) SELECT
D) WHERE
75) In an SQL query, which SQL keyword is used to link two conditions that both must be true
for the rows to be selected?
A) AND
B) EXISTS
C) OR
D) IN
76) In an SQL query, which SQL keyword is used to determine if a column value is equal to any
one of a set of values?
A) AND
B) EXISTS
C) OR
D) IN
77) In an SQL query, which of the following symbols is used by ANSI SQL to represent a single
unspecified character?
A) _ (underscore)
B) ? (question mark)
C) * (asterisk)
D) % (percent)
78) In an SQL query, which of the following symbols is used by Microsoft Access to represent a
single unspecified character?
A) _ (underscore)
B) ? (question mark)
C) * (asterisk)
D) % (percent)
79) In an SQL query, which SQL keyword is used to sort the result table by the values in one or
more columns?
A) GROUP BY
B) ORDER BY
C) SELECT
D) SORT BY
80) Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of
the following would find all employees whose name begins with the letter “S” using standard
SQL?
A) SELECT *
FROM EMPLOYEE
WHERE Name IN [‘S’];
B) SELECT EmpNo
FROM EMPLOYEE
WHERE Name LIKE ‘S’;
C) SELECT *
FROM Name
WHERE EMPLOYEE LIKE ‘S*’;
D) SELECT *
FROM EMPLOYEE
WHERE Name LIKE ‘S%’;