QUIZ IV

1. What two keywords must be used in the SELECT statement?

 The two keywords are:
 SELECT: Specifies the columns to be retrieved.
 FROM: Specifies the table from which to retrieve the data.
2. Records retrieved from the database are often referred to as what?
 Records retrieved from the database are often referred to as rows or tuples.
3. True or False. The TOP keyword is used to display records that fall in the middle of a range
specified by an ORDER BY clause.
 False, the TOP keyword is used to limit the number of rows returned from the beginning of a
result set, typically after applying sorting with the ORDER BY clause.
4. True or False. The AS keyword is used to create an alias.
 True, the AS keyword is used to create a temporary alias for a column or table, making it easier
to reference in a query.

5. True or False. The DISTINCT keyword is used to display the duplicate values in a column.
 False, the DISTINCT keyword is used to filter out duplicate values and display only unique
values in a column.
 QUIZ V

1. True or False. An expression is a special character used to match parts of a value.

 False, an expression is a combination of values, operators, and functions used to produce a
value. Special characters used to match parts of a value are called wildcards, often used in
patterns with LIKE or regular expressions.

2. True or False. The following queries are equivalent:

Query 1:
SELECT *
FROM Tools
WHERE ToolID > 3 AND ToolID < 10;
Query 2:
SELECT *
FROM Tools
WHERE ToolID BETWEEN 3 AND 10;
 False, the queries are not equivalent because BETWEEN 3 AND 10 is inclusive, meaning it
includes the values 3 and 10, whereas ToolID > 3 AND ToolID < 10 excludes 3 and 10.

3. Using the Friends table in Figure 5-16, what will the following query return?
Query:
SELECT FriendsID
FROM Friends
WHERE Lastname = ‘Jones’ AND Email IS NULL;

4. True or False. The exclamation mark (!) in the following WHERE clause means NOT:
 Query:

WHERE Location LIKE '[!A-C]';

 True, in SQL pattern matching, the ! inside square brackets ([!...]) negates the specified
range. This clause matches any character not in the range A to C.

5. True or False. The OR operator is processed before the AND operator in the order of evaluation.
 False, the AND operator has higher precedence than OR, meaning conditions joined by AND
are evaluated before those joined by OR. Parentheses can override this default order.
 QUIZ VI

1. True or False. The divide (/) operator is used to return the remainder in division.
 False, the divide (/) operator returns the quotient of a division operation. To return the
remainder, you would use the modulus operator (%).

2. True or False. Aggregate functions operate on only one row at a time.

 False, aggregate functions (e.g., SUM, AVG, COUNT, MAX, MIN) operate on multiple rows and
return a single value summarizing the data.

3. True or False. The ddd date format displays the full names of days.
 False, the ddd format typically displays an abbreviated name of the day (e.g., "Mon" for
Monday). To display the full name, you use dddd in most systems.

4. True or False. The CURRENTTIME () function is used to return the current time.
 False, the correct function to retrieve the current time is TIME(). You can also use NOW() to
return the current date and time.

5. True or False. The numeric representation of dates is called a Julian (or serial) date.
 True, Julian dates (or serial dates) are numeric representations of dates, often counting the
number of days since a specific reference date, such as January 1, 4713 BCE.
 QUIZ VII

1. True or False. The GROUP BY clause can only be used in queries that contain at least two
aggregate functions.
 False, the GROUP BY clause can be used in queries with one or more aggregate functions or
even without aggregate functions, as long as there are columns to group the data by.

2. Will the following query work?

SELECT DATE() AS TodaysDate
FROM Transactions
GROUP BY CustomerID;
 No, this query will not work, the DATE() function does not depend on the data grouped by
CustomerID. Any column or function in the SELECT statement that is not part of an aggregate
function must appear in the GROUP BY clause. Since DATE() is not an aggregate function, this
query violates Access SQL rules.

3. True or False. When using the GROUP BY clause with a WHERE clause, the GROUP BY clause
must appear before the WHERE clause.
 False, the WHERE clause filters rows before grouping occurs. Therefore, the WHERE clause
must appear before the GROUP BY clause.

4. True or False. The GROUP BY clause must appear before the ORDER BY clause.
 True, the GROUP BY clause must always come before the ORDER BY clause because data
must be grouped before it is ordered.

5. True or False. The HAVING clause filters rows before any data is grouped.
 False, the HAVING clause filters grouped data after the GROUP BY clause is applied. To filter
rows before grouping, you must use the WHERE clause.
 QUIZ VIII

1. True or False. A join enables you to use a single SELECT statement to query two or more tables
simultaneously.
 True, join allows you to combine data from two or more tables in a single SELECT statement
based on a related column.

2. True or False. The following shows the correct syntax to qualify a table and column name:
Tablename,Columnname.
 False, the correct syntax to qualify a table and column name is [Link] (with
a period, not a comma).

3. True or False. Table aliases are created just like column aliases.
 True, table aliases are created in the same way as column aliases using the AS keyword or by
directly specifying the alias after the table name. Example:
SELECT [Link]
FROM TableName AS t;

4. True or False. The UNION ALL keyword is used to combine records from two queries while
excluding duplicate records.
 False, the UNION ALL keyword combines records from two queries including duplicates. To
exclude duplicates, you use UNION without the ALL.

5. True or False. A left outer join is used to select every record from the table specified to the left
of the LEFT JOIN keywords.
 True, left outer join retrieves all records from the left (first) table and matches them with records
from the right table. If no match is found, the result will include NULL values for columns from
the right table.
 QUIZ IX

1. True or False. A correlated subquery executes once for each record a referenced query returns.
 True, correlated subquery depends on the outer query and executes once for every row
processed by the outer query.

2. True or False. The NOT operator is used to instruct Microsoft Access to match any condition
opposite of the one defined.
 True, the NOT operator reverses the condition specified. For example, NOT IN ('A', 'B') returns
rows where the value is not 'A' or 'B'.

3. True or False. The IN predicate is often used with the following comparison operators: =, <>, <,
>, <=, and >=.
 False, the IN predicate is used to match a value against a set of values (e.g., IN ('A', 'B')). It
does not work with operators like < or >. For those comparisons, you need separate conditions.

4. True or False. A subquery linked by the IN predicate can return two columns.
 False, subquery linked by the IN predicate can only return one column. If multiple columns are
required, you need a JOIN or other methods.

5. True or False. Subqueries nested within other queries are processed first, working outward.
 True, Subqueries are executed first, and their results are used by the outer queries. This is part
of the standard SQL query processing order.
 QUIZ X

1. True or False. The DISALLOW NULL option is used in the WITH clause.
 False, the DISALLOW NULL option is not used in the WITH clause. Instead, it is typically used
in column definitions to enforce those null values are not allowed.

2. Which option is used in the WITH clause to cause null data in a table to be ignored for an index?
 IGNORE NULL, the WITH IGNORE NULL option ensures that null values are ignored when
creating or modifying an index.

3. True or False. The DELETE TABLE keywords are used to delete or remove an index.
 False, the DELETE TABLE keywords are not used for indexes. To remove an index, you use
DROP INDEX.

4. True or False. The ALTER TABLE keywords are used to modify columns in an existing table.
 True, the ALTER TABLE statement is used to modify the structure of an existing table, including
adding, modifying, or deleting columns.

5. What keywords are used in the ALTER TABLE statement to change a column’s data type or field
size?
 ALTER COLUMN, the syntax to change a column's data type or field size is:
ALTER TABLE TableName
ALTER COLUMN ColumnName NewDataType(Size);