Exam 2 Review

¡Supera tus tareas y exámenes ahora con Quizwiz!

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


Conjuntos de estudio relacionados

7.4 investment analysis and strategies

View Set

Cardiovascular Eye Ear Medications

View Set

NCLEX Prep II Gastrointestinal Medications

View Set

Series 7 Kaplan Class Notes - Day 3 / Post Unit 11 Notes

View Set

Management - Final Exam Study Sheet

View Set

APES Semester 2 Final Exam Review

View Set

CH 6 - Business Strategy: Differentiation, Cost Leadership, and Blue Oceans

View Set