ISE 222 ch2
In an SQL query, which of the following symbols is used by ANSI SQL to represent all the columns in a single table?
* (asterisk)
In an SQL query, which of the following symbols is used by Microsoft Access to represent a single unspecified character?
? (question mark)
In an SQL query, which SQL keyword is used to link two conditions that both must be true for the rows to be selected?
AND
In an SQL query, which built-in function is used to compute the average value of numeric columns?
AVG
Regarding the interchangeability of subqueries and joins, ________. A) a join can always be used as an alternative to a subquery, and a subquery can always be used as an alternative to a join B) a join can sometimes be used as an alternative to a subquery, and a subquery can sometimes be used as an alternative to a join C) a join can always be used as an alternative to a subquery, and a subquery can sometimes be used as an alternative to a join D) a join can sometimes be used as an alternative to a subquery, and a subquery can always be used as an alternative to a join
B
In an SQL query, which built-in function is used to compute the number of rows in a table?
COUNT
Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following is not a valid ANSI SQL command? A) SELECT * FROM EMPLOYEE WHERE Name LIKE 'Ja%'; B) SELECT COUNT(*) FROM EMPLOYEE WHERE Salary < 30000; C) SELECT HireDate, COUNT(*) FROM EMPLOYEE GROUP BY HireDate; D) SELECT HireDate, COUNT(*) FROM EMPLOYEE WHERE Salary < 30000;
D
In an SQL query, which SQL keyword must be used to remove duplicate rows from the result table?
DISTINCT
When making an SQL query, we are using SQL as a(n) ________.
DML
In an SQL query, which SQL keyword is used to specify the names of tables to be joined?
FROM
In an SQL query, which SQL keyword is used to specify the table(s) to be used?
FROM
In an SQL query, which SQL keyword is used with built-in functions to group together rows that have the same value in a specified column or columns?
GROUP BY
When one SQL query is embedded in another SQL query, the top level SQL query can still contain an SQL ________ clause.
GROUP BY
In an SQL query, which SQL keyword is used with GROUP BY to select groups meeting specified criteria?
HAVING
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?
IN
) In an SQL query, which built-in function is used to obtain the largest value of numeric columns?
MAX
In an SQL query, which built-in function is used to obtain the smallest value of numeric columns?
MIN
In an SQL query, which SQL keyword is used to sort the result table by the values in one or more columns?
ORDER BY
In an SQL query, which SQL keyword actually begins the query?
SELECT
In an SQL query, which SQL keyword is used to implement a subquery?
SELECT
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?
SELECT * FROM EMPLOYEE WHERE Name LIKE 'S%';
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 Microsoft Access?
SELECT * FROM EMPLOYEE WHERE Name LIKE 'S*';
In an SQL query, which built-in function is used to total numeric columns?
SUM
When one SQL query is embedded in another SQL query, this is referred to as a ________.
Subquery
SQL includes a data definition language, a data manipulation language, and SQL/Persistent stored modules.
TRUE
Assuming the "Quantity" column of an ORDER table contains integer data, what does COUNT(Quantity) compute?
The number of non-null values in the Quantity column
Suppose tables EMPLOYEE and CUSTOMER both store address information, and you want to send a letter to all employees and customers of your company to make a major announcement. Which SQL keyword would you most likely use here?
UNION
In an SQL query, which SQL keyword is used to specify the column names to be used in a join?
WHERE
In an SQL query, which SQL keyword is used to state the condition that specifies which rows are to be selected?
WHERE
When one SQL query is embedded in another SQL query to simulate a join, the second SQL query is embedded in the ________ of the first query.
WHERE
In an SQL query, which of the following symbols is used by ANSI SQL to represent a single unspecified character?
_ (underscore)
An ad-hoc query is ________.
a question that can be answered from the database using SQL
In an SQL query, the built-in functions SUM and AVG work with columns containing data of which of the following data types?
both a and b.. integer and numeric
In an SQL query of two tables, which SQL keyword indicates that we want data from all the rows of one table to be included in the result, even if the row does not correspond to any data in the other table?
both a and b... LEFT JOIN , RIGHT JOIN
A database extracted from the operational database for BI purposes typically ________.
contains just part of the operational database
SQL is a ________.
data sublanguage
A WHERE clause can contain only one condition.
false
An ad-hoc SQL query is typically written within an application program.
false
Arithmetic in SQL statements is limited to the operations provided by the built-in functions.
false
Every subquery can be alternatively expressed by a join.
false
In addition to being a data sublanguage, SQL is also a programming language, like Java or C#.
false
Only two tables can be queried by using a subquery.
false
Outer joins can be either up joins or down joins.
false
SQL can only query a single table.
false
SQL is only a data manipulation language (DML).
false
SQL stands for Standard Query Language.
false
SQL statements end with a colon.
false
SQL, although very popular, has never become a national standard
false
Sorting is specified by the use of the SORT BY phrase.
false
The INTERSECT of two relations A and B consists of all rows that are in relation B but are not in relation A.
false
The Microsoft Access wildcard character "*" (asterisk) indicates a sequence of one or more unspecified characters in a Microsoft Access SQL query.
false
The Microsoft Access wildcard character "_" (underscore) indicates a single, unspecified character in a specific location in a Microsoft Access SQL query.
false
The SQL built-in function ADDUP totals values in numeric columns.
false
The SQL built-in function MOST obtains the largest value in a numeric column.
false
The SQL keyword WHERE is used to specify the table(s) that contain(s) the data to be retrieved.
false
The SQL wildcard character "#" indicates a single, unspecified character in a specific location in an SQL query.
false
The SQL wildcard character "%" represents a series of one or more unspecified characters.
false
The WHERE clause contains the condition that specifies which columns are to be selected
false
The built-in function SUM can be used with any column
false
The columns to be obtained by an SQL command are listed after the FROM keyword.
false
To exclude one or more values using a condition, the SQL OUT keyword must be used.
false
To have SQL automatically eliminate duplicate rows from a result, use the keyword DISTINCT with the FROM keyword.
false
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.
false
A SELECT statement used in a WHERE clause is called a subquery
true
A WHERE clause can contain another SELECT statement enclosed in parentheses
true
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.
true
An alternative to combining tables by a subquery is to use a join.
true
An asterisk (*) following the SELECT verb means that all columns are to be displayed.
true
Business Intelligence (BI) systems typically store their data in data warehouses.
true
Columns can be sorted in descending sequence by using the SQL DESC keyword.
true
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.
true
Joins that show only matching rows from the joined tables in their results are called inner joins.
true
Joins that show the matching rows from the joined tables plus unmatched rows from one other table in their results are called outer joins.
true
SQL commands can be embedded in application programs.
true
SQL is not a complete programming language. Rather it is a data sublanguage
true
The American National Standards Institute (ANSI) maintains the standards for SQL
true
The SQL WHERE clause contains the condition that specifies which rows are to be selected.
true
The SQL built-in function AVG computes the average of values in numeric columns
true
The SQL built-in function COUNT computes the number of rows in a query.
true
The SQL built-in function MIN obtains the smallest value in a numeric column.
true
The SQL keyword FROM is used to specify the table to be used.
true
The SQL keyword GROUP BY instructs the DBMS to group together those rows that have the same value in a column.
true
The SQL keyword LIKE is used in SQL expressions to select partial string values
true
The SQL keyword SELECT is used to specify the columns to be listed in the query results.
true
The SQL syntax JOIN . . . ON can be used as an alternate way of writing an SQL join statement.
true
The UNION of two relations A and B consists of all rows that are either in relation A or in relation B or both.
true
The clause SELECT COUNT (*) results in a table with a single row and a single column.
true
The condition in WHERE clauses can refer to a set of values by using the IN operator.
true
The names of tables to be joined in an SQL query are listed in the FROM clause
true
The result of an SQL SELECT operation can contain duplicate rows.
true
The rows of the result table can be sorted by the values in one or more columns.
true
To obtain all columns, use an asterisk (*) wildcard character instead of listing all the column names.
true
To refer to a set of values in a condition, the values are placed inside parentheses ( ) and separated by commas.
true
To refer to a set of values needed for a condition, use the SQL IN operator.
true
To remove duplicate rows from the result of a query, specify the SQL DISTINCT keyword.
true
To sort the rows of the result table, the ORDER BY clause is specified.
true
When people use the term join they normally mean an equijoin
true
When two conditions must both be true for the rows to be selected, the conditions are separated by the SQL AND keyword.
true
While many subqueries can be alternatively written as joins, correlated subqueries do work that cannot be duplicated as a join.
true
SQL was developed by IBM in the late 1970s.
ture
