Database Midterm

A full outer join includes rows that satisy 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 the

rows in the second table

In an UPDATE statement, the WHERE clause will

specify the condition a row must meet to be updated

To insert several rows from another table into a table, you can code an INSERY statemnt with a/an _______ in place of the VALUES clause.

subquery

A SELECT statement that includes aggregate functions is often called a/an _______ query.

summary

A common table expression(CTE) creates temporry _________ that can be used by a query that follows.

table

Which of the following is typically modeled after a real-world entity, such as an invoice or a vendor?

table

A relational database consists of one or more what?

tables

If you define a column with a default value, that value is used whenever a row

that doesn't include a value for that column is added

Since the MERGE operation often involves updating existing rows and inserting new rows, the MERGE statemtn is somtimes referred to as what?

the upsert statament

___________ names can be used when you want to assign a temporary name to a table.

Correlation

If you code a column list in an INSERT statement that includes a column that has a default value, which keyword can you code in the VALUES clause to use the default value?

DEFAULT

Which of the following statments deletes every rows in the Vendors table?

DELETE Vendors;

What keyword do you code at the end of the ORDER BY clause to sort the recors that are retrieved by a SELECT statment in descending sequence?

DESC

What keyword can you code in the SELECT clause to prevent duplicate rows from being returned by a SELECT statement

DISTINCT

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

When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers?

TOP 10 InvoiceNumber

You can use the UPDATE statement to modify one or more rows in the table named in the __________ clause

UPDATE

If you want to filter the result set that's returned by a SELECT statement, you must include which clause?

WHERE

Your code will be easier to read if you code the join condition in the ON expression, and the search conditions in which clause?

WHERE

To run a SELECT statement from an application program, you store the statement in the ________ object for the database connection.

command

The CUBE operator is similar to the ROLLUP operator except that

it adds summary rows for every combination of groups

Which of the following can you use to combine data from two or more tables into a single result set?

join

You can use a ______ in the ________ clause if you need to specify column values or search conditions other than the one named in the UPDATE clause.

join, FROM

What kind of join would you use 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?

left outer

Subqueries can be __________ within other subqueries

nested

The three main hardware components of a client/server system are the clients, the server, and the

network

When you code a column list in an INSERT statment, you can omit identify columns, columns that have default values, and columns that allow ________ values.

null

Which ORDER BY clause causes 10 rows to be retrieved from the result set, starting with the 20th row?

ORDER BY InvoiceTotal DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS

When coding a query with two columns in the GROUP BY clause, you can insert a summary row for each major group by coding which operator.

ROLLUP

Which of the following won't improve the readability of your SQL statements?

Use comments to describe what each statemtns does

When you code a DELETE statement for one or more rows, which clause specifies which row or rows are to be deleted?

WHERE

When you code an UPDATE statemtn for one or more rows, the SET clause specifies the new data for the specified columns and the _______ clause specifies which row or rows are to be updated

WHERE

When you use the implicit syntax for coding inner joins, the join conditions are coded in which clause

WHERE

A search condition in the __________ clause is applied before the rows are grouped while a search condition in the _______ clause isn't applied until after the grouping.

WHERE, HAVING

In a join, column names only need to be qualified where?

When the same names are used in both tables

When you use the SELECT INTO statement, the result set that's defined by the SELECT statment is ____________ a new table.

copied into

In the SQL statment shown beow, the name "v" is known as a?

correlation name or table alias

To overrride the order of precedence in an arithmetic expression, you can use

parentheses

What uniquely identifies each row in a table?

primary key

To retrieve or update the data in a database, the client sends a ______ to the database

query

What is the most comon type of reltionship between two tables?

one-to-many

Which wildcard character do you use in the SELECT clause to return all of the columns from the base table?

*

Which operator do you use to concatenate character strings in a string expression?

+

When coding search conditions, you can use which keyword to create compound search conditions?

AND

Which aggregate expression calculates the average value of the InvoiceTotal column, exculding null values?

AVG(InvoiceTotal)

The search condition of a WHERE clause consists of one or more

Boolean expressions

All of the aggregate functions ignore null values, except for which function?

COUNT(*)

Which aggregate expression gets the number of entries in the VendorName column, including null values?

COUNT(*)

Which aggregate expression gets the number of unique values in the VendorID column?

COUNT(DISTINCT VendorID)

Which operator can you use to test whether one or more rows are returned by a subquery

EXISTS

Which clause specifies the number of rows that should be retrieved after skipping the specifies number of rows?

FETCH

Correlation names are temporary table names assigned in which clause?

FROM

Which clause of the SELECT statement names the table that contains the data to be retrieved?

FROM

A subquery can be coded in a WHERE, FROM, SELECT, or _____________clause

HAVING

What keyword can you use to retrieve rows in which an expression matches a string pattern called a mask?

LIKE

Which aggregate expression finds the latest date in the InvoiceDate column?

MAX(InvoiceDate)

Which aggregate expression finds the VendorName column that's last in alphabetical order

MAX(VendorName)

The order of precedence for the logical operators in a WHERE clause is

Not, And, Or

Which clause specifies the number of rows that should be skipped before rows are returned from the result set

OFFSET

A subquery is a/an ______ statement that's coded within another SQL statement.

SELECT

Which of the following statemnts isn't an action query?

SELECT

A union combines the rows from two or more what?

SELECT statments, result tables, queries

The six clauses of the SELECT statment 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, FROM, WHERE, ORDER BY

To insert several rows into a table, you can code an INSERT statement with multiple value lists that are separated by what?

a comma

When a column in a table is defined, what determines the kind of data it can store?

a data type

In many cases, a subquery can be restated as

a join

If you define a column as an identity column,

a number is generated for that column whenever a row is added to the table

A join that joins a table with itself is called

a self-join

Insert, Update, and Delete statements can be referred to as _____ queries.

action

Which functions perform a calculation on the values of a column from selected rows?

aggregate

If you omit the WHERE clause from a DELETE statement

all rows in the table will be deleted

A combination of column names and operators that evaluate to a single value is called

an expression

You can't update

an identity column

The processing that's done by the DBMS is typically referred to as

back-end-processing

Expressions coded in the WHERE clause

can use non-aggregate search conditions but cna't use aggregate search conditions

If you use ______ in the select list of a SELECT INTO statement, you must name the column since that name is used in the definition of the new table

calculated values

Expressions coded in the HAVING clause

can use either aggregate search conditions or non-aggregate search conditions

The interface between an application program and the DBMS is usually provided by the

data access API

A view is a SELECT statement that is stored with the

database

To store and manage the databases of the client/server system, each server requires what?

database management system (DBMS)

Although the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor -has its own ______ of SQL

dialect/variant

In most cases, the join condition of an inner join compares the primary key of one table to the _______ key of another table.

foreign key

To relate one table to another, a/an _____ in one table is used to point to the primary key in another table

foreign key

You can use the OVER clause with an aggregate function to

include the rows used to calculate the summary in the result set

When you code a union with the INTERSECT keyword to combine two result sets, the union

includes only rows thaqt occur in both result sets

A correlated subquery is one that

is executed once for each row in the outer query

If you assign a correlation name to one table in a join,

you have to use that name for the table in the query

You specify the conditions that must be met for a row to be deleted in the which clause

WHERE

A subquery that's used in a WHERE or HAVING clause is called what?

a subquery search condition

Which of the following is not a SQL DML statment?

Create Table

You can use the DELETE statement to delete one or more rows from the table you name in the ________ clause

DELETE

By default, all duplicate values are included in the aggregate calculation, unless you specify which keyword

DISTINCT

SQL statements that define the tables in a database are referred to as _______ statements.

Data Definition Language (DDL)

What SQL dialect does Microsoft SQL Server use?

Transact-SQL

The intersection of a row and a column is commonly called what?

cell

When you need to code multiple conditions in join, it's best to

code only join conditions in the ON clause

Unless you assign a _____, the column name in a result set is the same as the colum nme in the base table

column alias

When you code an ORDER BY clause, you can specify a

column name, alias, expression, or column number

To work with the data in a SQL Server database from a .NET application, you can use ADO.NET objects like

commands, connection, and data readers