SQL Quiz 3 Review

1. What symbol is used to select all columns from a table?

*

1. Which two predicates are treated the same by the SQL standard?

ANY, SOME

What kind of literal value is a sequence of individual characters enclosed in single quotes?

Character String Literals

1. Which data type is usually based on the ASCII or EBCDIC character sets?

Character Type

1. What character is used to separate the columns referenced in the GROUP BY clause?

Comma

1. Which predicate tests whether the value of a given value expression matches an item in a given list of values?

IN

1. Which JOIN returns only those rows where the linking values match in both of the tables?

INNER JOIN

1. What type of JOIN returns all rows from the first table and any matching rows from the second table?

LEFT OUTER JOIN

Grouping data with aggregate functions enables you to see ____________ for categories of data.

subtotals

1. According to the SQL standard, what is the syntax for qualifying a column name?

AS

1. What keyword is used to provide a name for an expression in a select clause?

AS

1. Which aggregate function calculates the mean for a value expression?

AVG (Average)

1. The SQL standard provides a set of _____________ functions that enable you to calculate a single value from the rows in a result set or from the values returned by a value expression.

Aggregate

1. What data type is used to store images, videos, and sounds?

Binary Type

1. Which function is used to convert a value to a specific data type?

CAST Function

1. What character is used to separate multiple assignment statements in the SET clause?

COMMA

1. What keyword is used to permanently change the data in a transaction?

COMMIT

1. Which predicate uses one of six operators to compare one value expression to another value expression?

COMPARISON

1. Which aggregate function returns a count of all non-null values returned by a value expression?

COUNT (value expression)

What HAVING criteria can be used to eliminate rows for the multiple NOT criteria?

COUNT >= 1

1. The WHERE and HAVING clauses use a ________________ to determine whether a row will be included in the final result set.

Criteria, or Search Condition

1. Which statement is used to remove data from the database?

DELETE

1. What keyword is used to eliminate any duplicate rows?

DISTINCT

1. When the GROUP BY clause is used without aggregate functions it will give you the same result as what keyword?

DISTINCT

Columns referenced in the HAVING clause must also be referenced where?

Either in the GROUP BY clause or enclosed within an aggregate function

Which clause specifies the tables or views from which to retrieve the data?

FROM

1. What type of JOIN returns all rows from both tables?

FULL OUTER JOIN

Which of the following is a method to ensure only the desired rows will be updated?

First, write a SELECT statement to select the rows you want updated, and then transfer the WHERE clause to the UPDATE statement.

1. If you want to include a column in the output that is not a result of an aggregate calculation you must add it to what clause?

GROUP BY

1. When an aggregate function is used, which clause divides the data into distinct groups?

GROUP BY

1. Filters that use a predicate on an aggregate function must be placed in what clause?

HAVING

1. Which clause is used to filter the results of aggregate functions?

HAVING

1. Which of the following is a clause that is not used in an UPDATE statement?

HAVING

1. Which predicate keyword is a set membership predicate?

IN

1. What statement is used to add new rows of data into a database?

INSERT

1. What keyword is used to link two dissimilar tables on matching column values?

JOIN

Which keyword is optional to complete a FULL OUTER JOIN?

Join Outer Full

1. Which predicate enables you to test whether a character string value expression matches a specified character string pattern?

LIKE

1. Which aggregate function returns the maximum value of a value expression?

MAX

1. In an OUTER JOIN, what value does the database system return for any nonmatched columns?

NULL

1. What clause follows the second table in the JOIN and tells the database system how to perform the JOIN?

ON or USING

1. The way a database software sorts data depends on the database software's what?

ORDER BY

1. What SELECT statement clause cannot be used when embedded in a JOIN?

ORDER BY

1. To complete a LEFT OUTER JOIN, what keyword is optional?

OUTER

1. When using multiple tables, which clause enables you to include data that does not match across tables?

OUTER JOIN

1. ______ are used to embed a JOIN within a JOIN.

Parentheses

What is an expression that tests one or more value expressions and returns true, false, or unknown?

Predicate

The ANY, ALL, and SOME predicates are ________________.

Quantified Predicate

1. What type of JOIN returns all rows from the second table and any matching rows from the first table?

RIGHT OUTER JOIN

1. What keyword is used to undo the changes that were made during a transaction?

ROLLBACK

1. What type of subquery is an embedded SELECT expression that returns one or more columns and zero to many rows?

Row Subquery

1. What is the primary clause in a SELECT statement?

SELECT

1. What clause is used to specify the column to change and the new value of the column?

SET

1. Which aggregate function is used to compute a total of a value expression?

SUM

1. What type of subquery is an embedded SELECT expression that returns only one column and no more than one row?

Scalar Subquery

1. A SELECT expression that is embedded within one of the clauses of a SELECT statement is a(n) __________.

Subquery

1. When an aggregate function is used as a search condition it must be placed in (a) __________.

Subquery

1. What SQL feature enables you to undo a series of changes to the data?

TRANSACTION

Columns referenced in the WHERE clause must be a part of what?

The columns referenced in the ORDER BY clause

1. A FULL OUTER JOIN with the matching rows removed is known as a ___________.

UNION JOIN

1. Which clause is used to specify the table to be changed in an UPDATE statement?

UPDATE

1. The version of the INSERT statement that adds one row of data into the target table uses what keyword?

VALUES

1. Data can be inserted into a table or what other database item?

VIEW

1. In database systems that do not support the JOIN keyword, the search condition from the ON clause is moved into which clause?

WHERE

1. The GROUP BY clause is added after what clause?

WHERE

1. When you want to group only a subset of rows you should eliminate rows first in what clause?

WHERE

1. Which clause is used to filter nonaggregate data?

WHERE

1. Which two clauses are used to filter the result set?

WHERE & HAVING

1. Which of the following is a keyword used with the DELETE statement?

WHERE, FROM