Exam 2 Review
The order of precedence for the logical operators in a WHERE clause is
Not, And, Or
The result of a query that includes a subquery in a WHERE clause can only include columns from the table named in the _____________ query.
OUTER MAIN
To override the order of precedence in an arithmetic expression, you can use __________________.
Parentheses
When coded in a WHERE clause, which search condition will return invoices when PaymentDate is not null and InvoiceTotal is greater than or equal to $500?
PaymentDate IS NOT NULL AND InvoiceTotal >= 500
A subquery is a/an ______________ statement that's coded within another SQL statement.
SELECT
To return all of the columns from the base table, you can code the ___________ operator in the SELECT clause
*
To concatenate character strings, you use the _____________ operator in a string expression.
+
When you code an ORDER BY clause, you can specify a
. column name, alias, expression, or column number
Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values:
AVG(InvoiceTotal)
Unless you assign a/an _____________________________, the column name in the result set is the same as the column name in the base table.
Alias Column Alias Substitute Name
All of the aggregate functions ignore null values, except for the __________________ function
COUNT(*)
Write an aggregate expression for the number of entries in the VendorName column, including null values:
COUNT(*)
Write an aggregate expression for the number of unique values in the VendorID column:
COUNT(DISTINCT VendorID)
When coding a query with two columns in the GROUP BY clause, you can insert a summary row for each major group by coding the ___________ operator
CUBE WITH CUBE
________ names can be used when you want to assign a temporary name to a table.
Correlation
To sort the records that are retrieved by a SELECT statement in descending sequence by InvoiceTotal, you code _________ after ORDER BY InvoiceTotal.
DESC
By default, all duplicate values are included in the aggregate calculation, unless you specify the ______ keyword.
DISTINCT
To prevent duplicate rows from being returned by a SELECT statement, you can code the __________________ keyword in the SELECT clause.
DISTINCT
To test whether one or more rows are returned by a subquery, you can use the ___________ operator.
EXISTS
The ________________ clause of the SELECT statement names the table that contains the data to be retrieved.
FROM
If you want to join all of the rows in two tables whether or not they are matched, you use a/an _______________ join.
Full Outer
A subquery can be coded in a WHERE, FROM, SELECT, or ______________ clause
HAVING
When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an InvoiceTotal value of $1000 or less?
InvoiceTotal IN (0,1000)
In many cases, a subquery can be restated as a/an ______________.
JOIN
To retrieve rows in which an expression matches a string pattern called a mask, you can use the ______________ keyword followed by the mask.
LIKE
If you want to join all of the rows in the first table of a SELECT statement with just the matched rows in a second table, you use a/an _________ join.
Left Outer
A union combines the rows from two or more _________
SELECT statements
The six clauses of the SELECT statement must be coded in the following order:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
When you code a SELECT statement, you must code the four main clauses in the following order
SELECT, WHERE, ORDER BY, FROM
Write an aggregate expression to find the VendorName column that's last in alphabetical order:
MAX(VendorName)
Write an aggregate expression to find the oldest date in the InvoiceDate column: ____________.
MIN(InvoiceDate)
Which of the following expressions will not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total?
TOP 10 InvoiceNumber
When you code a union that combines two result sets, which of the following is not true?
The result sets must be derived from different tables.
Which of the following is not a valid column alias name?
Total Sales
If you want to filter the result set that's returned by a SELECT statement, you must include a/an ___________________ clause.
WHERE
When you use the implicit syntax for coding joins, the join conditions are coded in the ______ clause.
WHERE
Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for joins? The explicit syntax
can be used for more than two tables
Expressions coded in the HAVING clause
can use either aggregate search conditions or non-aggregate search conditions
Expressions coded in the WHERE clause
can use non-aggregate search conditions but can't use aggregate search conditions
When you need to code multiple conditions in a join, its best to
code only join conditions in the ON clause
In most cases, the join condition of an inner join uses the ________ operator to compare two keys.
equal equals
In most cases, the join condition of an inner join compares the primary key of one table to the __________ key of another table.
foreign
When you code a union with the INTERSECT keyword to combine two result sets, the union
includes only rows that occur in both result sets
You don't ever need to code a right outer join because
right outer joins can be converted to left outer joins
A full outer join includes rows that satisfy the join condition, plus
rows in both tables that don't satisfy the join condition
In a cross join, all of the rows in the first table are joined with all of the
rows in the second table
A SELECT statement that includes aggregate functions is often called a/an ______________ query.
summary
A common table expression creates a temporary _____________ that can be used by a query that follows the CTE.
table result set
In a join, column names need to be qualified only
when the same names are used in both tables
If you assign a correlation name to one table in a join,
you have to use that name for the table